ELT Azure Synapse Account

Owned by Anand Vedam
Last updated: Apr 05, 2023 by Amritesh Singh
8 min read

1In this article

Overview

You can use the ELT Database Account to connect ELT Snaps with an Azure Synapse target instance. This account enables you to write transformed data to a target Azure Synapse database hosted in a Microsoft Azure cloud location. The cloud location where the database is hosted is indicated in the JDBC URL for Azure Synapse—jdbc:sqlserver://<yourserver>.database.windows.net.

The ELT Snap Pack does not support mixed accounts from different types of databases in the same Pipeline. For example, a Pipeline in which some Snaps are connecting to the Azure Synapse database cannot have other Snaps connecting to a Redshift database.

Prerequisites

  • A valid Azure Synapse account.

  • Certified JDBC JAR File: mssql-jdbc-11.2.1.jre11.jar

    Using Alternate JDBC JAR File Versions

    We recommend that you let the ELT Snaps use this JAR file version. However, you may use a different JAR file version of your choice.

Limitations

The ELT Azure Synapse Account supports the following:

  • Only provisioned clusters of Azure Synapse, but not in Serverless mode.
  • External locations: 
    • Blob Storage: https://.blob.core.windows.net//
    • ADLS Gen2: https://.dfs.core.windows.net// 

Known Issue

  • When you use the auto-fill feature in the Google Chrome browser to fill ELT Snap account credentials—such as user names, passwords, client secrets, auth codes and tokens, secret keys, and keystores, the accounts, and hence the Pipelines fail. This is because the browser overwrites the field values with its own encrypted values that the SnapLogic Platform cannot read. SnapLogic recommends that you do not auto-save your Snap account credentials in the Chrome browser, delete any credentials that the browser has already saved for elastic.snaplogic.com, and then perform ONE of the following actions:
    • Option 1: Click  that appears in the address bar after you submit your login credentials at elastic.snaplogic.com, and then click Never. 
    • Option 2: Disable the Offer to save Passwords option at chrome://settings/passwords while working with your SnapLogic Pipelines. If you disable this option, your Chrome browser will not remember your passwords on any other website.

Account Settings

While you configure your Snap Account...

  • Asterisk ( * ) indicates a mandatory field.

  • Suggestion icon ( ) indicates a list that is dynamically populated based on the configuration.

  • Expression icon ( ) indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon () indicates that you can add fields in the fieldset.

  • Remove icon () indicates that you can remove fields from the fieldset.

ParameterField Dependency

Description

Label*None.

Required. Unique user-provided label for the account.

Default Value: N/A

Example: ELT AZ SY Blob Account

Account Properties*

Use this fieldset to configure the information required to establish a JDBC connection with the account.

This fieldset consists of the following fields:

  • Database Type
  • Download JDBC Driver Automatically
  • JDBC JAR(s) and/or ZIP(s)
  • JDBC Driver Class
  • JDBC URL
  • Username
  • Password
  • Default Database Name
Database Type*None.

Select the target data warehouse into which the queries must be loaded, that is Azure Synapse. This activates the following fields:

  • External Location

Default Value: N/A

Example: Azure Synapse

Download JDBC Driver AutomaticallyCheckbox

Select this checkbox to allow the Snap account to download the certified JDBC Driver for Azure Synapse. The following fields are disabled when you select this checkbox.

  • JDBC JAR(s) and/or ZIP(s)
  • JDBC driver class

To use a JDBC Driver of your choice, clear this checkbox, upload (to SLDB), and choose the required JAR files in the JDBC JAR(s) and/or ZIP(s): JDBC Driver field. 

Use of Custom JDBC JAR version

You can use a different JAR file version outside of the recommended listed JAR file versions.

Default Value: Not Selected

Example: Selected

JDBC JAR(s) and/or ZIP(s)Required when the Download JDBC Driver Automatically checkbox is not selected.

Upload the JDBC driver and other JAR files that you want to use into SLDB. Click  to add a new row. Add each JDBC JAR file in a separate row. See Downloading Microsoft JDBC Driver for more information about JDBC drivers and download the appropriate driver for your account.

If you are using Azure Active Directory password based authentication on the older Snap Pack version, you could encounter an error while using ELT based pipelines in Azure Synapse. This error is occurring due to absence of the following two jars in the ELT Snap Pack. Add these two JAR files to the list that you want to use to rectify the issue:

  • MSAL4J (Microsoft Authentication Library for Java) during compile time

  • Nimbusds oauth for runtime.

However, this error would not occur if you are on the 430patches18196 Snap Pack version.


Default Value: N/A

Example: mssql-jdbc-8.4.1.jre8.jar

JDBC driver class*Required when the Download JDBC Driver Automatically checkbox is not selected.

Specify the driver class to use for your application. 

We recommend that you use com.microsoft.sqlserver.jdbc.SQLServerDriver to suit your Azure Synapse database, as other classes and methods may change due to future enhancements.

Default Value: N/A

Example: com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC URL*None.

Enter the JDBC driver connection string that you want to use in the syntax provided below. See Microsoft's Connection strings for SQL pools in Azure Synapse for more information. 

  • jdbc:sqlserver://endpoint:port;database=<databaseName>;<parameter1=value1>;<parameter2=value2>;

Alternatively, you can make use of the Username, Password, and Database Name fields, along with the Advanced Properties > URL Properties fieldset to provide the parameters required for building your JDBC URL. See Passing your JDBC URL for more information.

Avoid passing Password inside the JDBC URL

If you specify the password inside the JDBC URL, it is saved as-is and not encrypted. We recommend passing your password using the Password field provided, instead, to ensure that your password is encrypted.

Default Value: N/A

Example: jdbc:sqlserver://yourserver.database.windows.net:1433;database=yourdatabase;user={your_user_name};password=your_password_here};encrypt=true;trustServerCertificate
=false;hostNameInCertificate=*.database.windows.net;loginTimeout=300;

Default Database Name*None.

Enter the name of the database to use by default. This database is used if you do not specify one in the ELT Load, ELT Select, ELT Insert-Select, or ELT Merge Into Snaps. 

Default value: N/A

Example: EMPLOYEEDB

External Location
Database Type is Azure Synapse.

Select the source data location from which to load data into the target database. You must configure this field if you want to use the ELT Load Snap. 

Available options are:

  • None
  • Blob Storage
  • Azure Data Lake Gen2

Selecting an option other than None in this field activates the following fields:

  • External Storage Endpoint
  • Storage Account
  • Azure Container
  • Azure Folder
  • Azure Auth Type
  • Azure Identity
  • Azure Secret

Default value: None

Example: Azure Data Lake Gen2

External Storage Endpoint
Database Type is Azure Synapse.

Enter the endpoint path for the selected external location. You must configure this field if you want to use the ELT Load Snap.

Default value: N/A

Example: .blob.core.windows.net (for Blob Storage), .dfs.core.windows.net (for ADLS Gen2)

Storage Account
Database Type is Azure Synapse. External Location is Blob Storage or Azure Data Lake Gen2.

Enter your Account name to access the selected Azure external storage location. You must configure this field if you want to use the ELT Load Snap.

Default value: N/A

Example: adlsgen2v02

Azure ContainerDatabase Type is Azure Synapse. External Location is Blob Storage or Azure Data Lake Gen2.

Enter the name of the container in the Azure storage account.

Azure Container is not applicable to the Redshift database.

Default value: N/A

Example: hrdepartment

Azure FolderDatabase Type is Azure Synapse. External Location is Blob Storage or Azure Data Lake Gen2.

Enter the relative path for the folder within the container specified above. Leave this field blank to use all the folders in the specified container. 

Azure Folder is not applicable to the Redshift database.

Default value: N/A

Example: salaries

Azure Auth Type
Database Type is Azure Synapse. External Location is Blob Storage or Azure Data Lake Gen2.

Select the authentication type to use for accessing the selected Azure external storage location. Then, provide the values for Azure Secret, Storage Key fields for the selected authentication type. See COPY INTO (Transact-SQL) in Microsoft Docs for more information on these authentication types.

Available options are:

  • Storage Account Key
  • Shared Access Signature
  • Managed Identity
  • <application_ID>@<OAuth_2.0_Token_EndPoint> (Service Principals)
  • AAD User (Azure Active Directory User)

Default value: Storage Account Key

Example: AAD User

Azure IdentityDatabase Type is Azure Synapse. External Location is Blob Storage or Azure Data Lake Gen2. Azure Auth Type is Storage Account Key, Shared Access Signature, Managed Identity, or Service Principals..

Enter the Azure Identity needed to access the selected external storage location.

Default value: N/A

Example: Storage Account Key

Azure Secret
Database Type is Azure Synapse. External Location is Blob Storage or Azure Data Lake Gen2. Azure Auth Type is Storage Account Key, Shared Access Signature, or Service Principals.

Enter the Azure Secret corresponding to the selected authentication type. See COPY INTO (Transact-SQL) in Microsoft Docs for more information on the expected values for this field.

Default value: N/A

Example: UvhVF65Bukk9hVF65BuUvhVF

Storage Key

Database Type is Azure Synapse. External Location is Blob Storage or Azure Data Lake Gen2. Azure Auth Type is Shared Access Signature, Managed Identity, or Service Principals.

Enter the storage access key value corresponding to the Azure account mentioned in Storage Account field. See COPY INTO (Transact-SQL) in Microsoft Docs for more information on the expected values for this field.

This field is needed only when you want to define the File Name Pattern in the ELT Load Snap to locate and read the file/s from the Azure Folder path. Else, leave it blank.

Default value: N/A

Example: ufv!befDIuf#fnb$KH&_hweuf

Client IDDatabase Type is Azure Synapse. External Location is Blob Storage or Azure Data Lake Gen2. Azure Auth Type is AAD User.

Enter the Client ID of the application created in the Azure Active Directory portal -https://portal.azure.com.

This field is needed only when you want to define the File Name Pattern in the ELT Load Snap to locate and read the file/s from the Azure Folder path. Else, leave it blank.

Default value: N/A

Example: hewr42ap-32jd-pd95-ms38-b342bnasdh80

Client SecretDatabase Type is Azure Synapse. External Location is Blob Storage or Azure Data Lake Gen2. Azure Auth Type is AAD User.

Enter the Client ID of the application created in the Azure Active Directory portal - https://portal.azure.com.

This field is needed only when you want to define the File Name Pattern in the ELT Load Snap to locate and read the file/s from the Azure Folder path. Else, leave it blank.

Default value: N/A

Example: jhweufv!befDIufwb*dsfwujfnb$KH&_

Tenant IDDatabase Type is Azure Synapse. External Location is Blob Storage or Azure Data Lake Gen2. Azure Auth Type is AAD User.

Enter the Tenant ID of the application created in the Azure Active Directory portal - https://portal.azure.com. 

This field is needed only when you want to define the File Name Pattern in the ELT Load Snap to locate and read the file/s from the Azure Folder path. Else, leave it blank.

Default value: N/A

Example: b342bnas-he38-apms-dhjd-pd95wr423280

Advanced Properties

Other parameters that you want to specify as URL properties. See Azure Synapse - JDBC Driver Connection Parameters for a list of parameters that can be specified in this fieldset.

This fieldset consists of the following fields:

  • URL Properties
    • URL Property Name
    • URL Property Value
URL PropertiesNone.

The account parameter's name and its corresponding value. Click + to add more rows. Add each URL property-value pair in a separate row. Specify the name of the parameter in the URL Property Name field and its value in the URL Property Value field.

N/AMAX_CONCURRENCY_LEVEL: 3

Click Validate after entering the required details to ensure that all fields have been filled accurately. Click Apply to save the settings.

Account Validation when using Pipeline parameters

If you have used Pipeline parameters or expressions to define values for the account fields above, the account validation (done by clicking the Validate button) is not supported. However, the Snaps that use this account may connect to the endpoint successfully depending on the accuracy and validity of the expressions and parameters used.

Passing your JDBC URL

Order of Precedence

The parameter values for the URL used to connect to your target database are governed by the following order of precedence:

  • JDBC URL field
  • Snap Properties (including Advanced Properties)
  • Default values (listed below)

Default Properties set internally

The following properties are passed in the URL internally, by default:

ParameterValue
encrypttrue
trustServerCertificatefalse
hostNameInCertificate*.database.windows.net
loginTimeout300

Specific Scenarios

When setting default values for the Database Name, Username, and Password fields:

  • As a best practice, ensure that the Default Database Name provided in the Snap's properties and the database name in the JDBC URL field match.

  • Else, make sure that both the database names exist.

However, the username and password from the JDBC URL take precedence over the values provided in the respective fields.

Locating the JDBC URL for Azure Synapse database

Perform the following steps to locate and use the JDBC URL for your Azure Synapse database instance.

  1. Log into portal.azure.com.


  2. Click SQL Databases.


  3. Click on the Database configured for your organization. 


  4. Click Show Database Connection Strings  and click JDBC to get the actual connection string.


  5. Click  to copy the JDBC URL displayed. Paste this URL in your Snap's account settings. 


    Alternatively, you can separately pass the base URL in the JDBC URL field and the remaining parameters as a combination of the Snap's account fields and Advanced Properties > URL Properties.


Troubleshooting

None.

Example

Configuring the ELT Database Account for connecting to a Azure Synapse Database

The following Pipeline is designed to extract data from a Parquet file residing in the ADLS Gen2 storage and load it into a new table in the Azure Synapse database.

Snap Settings

The Pipeline uses an ELT Load Snap to define the source Parquet file in the Azure Data Lake Storage and is configured to perform the following functions.

  • Create a new table out_sy_elt_load_02 in the specified target database (Azure Synapse).
  • Load the data from Parquet file to this newly-created target table based on the table columns specified.

Account Settings

The Pipeline accesses the Parquet file using the Storage Account Key mode of authentication.

See Also

Have feedback? Email documentation@snaplogic.com | Ask a question in the SnapLogic Community
© 2017-2024 SnapLogic, Inc.