Azure Synapse
Guide to integrate your Azure Synapse with Sprinkle
This page covers the details about integrating Azure Synapse with Sprinkle.
If you don't have an existing Azure Synapse cluster. You can refer Azure documentation. Create a dedicated sql-pool while setting up the warehouse (refer- docs).
When setting up Synapse connection, Sprinkle additionally requires Azure Storage Container. This guide covers the role of all the components and steps to setup.
Integrating Synapse: All analytical data is stored and queried from Synapse warehouse
Create Azure Storage Container: Sprinkle stores all intermediate data and report caches in this bucket
Step by Step Guide
Integrating Synapse
STEP-1: Allow Synapse to accept connection from Sprinkle
Allow inbound connection on Synapse jdbc port (default is 1433) from Sprinkle IPs (34.93.254.126, 34.93.106.136).
STEP-2: Configure Synapse Connection
Log into Sprinkle application
Navigate to Admin -> Warehouse -> New Warehouse Connection
Select Azure Synapse
Provide all the mandatory details
Distinct Name: Name to identify this connection
Host : Provide IP address or Host name.
Port :Provide the Port number.
Database : Provide database name if there is any, it should be an existing database.
Username
Password
Advanced Settings: It can be turned on/off based on the requirements. if yes:
Maximum Error Count : If the load returns the error_count number of errors or greater, the load fails. If the load returns fewer errors, it continues and returns an INFO message that states the number of rows that could not be loaded. Use this parameter to allow loads to continue when certain rows fail to load into the table because of formatting errors or other inconsistencies in the data. For reference, see this
Minimum Idle Connections : This property controls the minimum number of idle connections in the pool.
Maximum Pool Size : This property controls the maximum size that the pool is allowed to reach.
Test Connection
Create
Create Azure Storage Container
Sprinkle requires Azure Storage container to store intermediate data and report caches. Follow the below steps to create and configure storage container:
STEP-1: Create a Storage Account
Login to the Azure Portal and select All Services.
Select Storage Account and choose Add.
Select the subscription in which you want to create the account.
Select the desired resource group or create a new resource group. You can refer to the Azure Documentation of resource management.
Provide the storage name, this name should be unique across all the Azure storages. The name also must be between 3 and 24 characters in length, and may include only numbers and lowercase letters.
Select a location for your storage account, or use the default location.
Select a performance tier, recommended is Standard.
Set the Account kind field to Storage V2 (general-purpose v2).
Specify other settings if anything is required
Select Review + Create to review your storage account settings and create the account.
Click on Create, it will create the account
STEP-2: Create Storage Container
Login and go to the new storage account in the Azure portal.
In the left menu for the storage account, select Containers from the Blob service section.
Click on the + Container button.
Provide a name for your new container. The container name must be lowercase, must start with a letter or number, and can include only letters, numbers, and the dash (-) character. Refer to the Azure Documentation for more information.
Set the level of public access to the container or you can whitelist the Sprinkle IP shown in Driver Setup.
Select OK to create the container.
You can refer to the Azure quick start guide for Storage containers.
STEP-3: Get Access Key
Navigate to your storage account in the Azure portal.
Under Settings, select Access keys. Your account access keys appear, as well as the complete connection string for each key.
Locate the Key value under key1, and click the Copy button to copy the account key.
STEP-4: Configure Azure Storage connection in Sprinkle
Log into Sprinkle application
Navigate to Admin -> Warehouse -> New Warehouse Connection -> Add Storage
Select Azure Blob Store
Provide all the mandatory details
Distinct Name: Name to identify this connection
Storage Account Name: Created in STEP-1
Container Name: Created in STEP-2
Access Key: Copied in STEP-3
Test Connection
Create
Get JDBC URL
On the Azure portal go to synapse workspace, then on the left side under the Analytics pools tab select sql-pools.
Select the sql-pool for which you want to integrate to sprinkle.
On the left side under the Settings tab select connection strings. Select JDBC, here copy JDBC URL(remove username and password from JDBC URL).
Last updated