In 1In this article
Table of Contents | ||||
---|---|---|---|---|
|
...
- A valid Azure Synapse account.
Certified JDBC JAR File: mssql-jdbc-1011.2.01.jre8jre11.jar
Note title 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.
...
- Only provisioned clusters of Azure Synapse, but not in Serverless mode.
- External locations:
- Blob Storage: https https://.blob.core.windows.net//
- ADLS Gen2: https://.dfs.core.windows.net//
...
Multiexcerpt macro | ||||
---|---|---|---|---|
| ||||
|
Account Settings
Panel | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||
|
Parameter | Field 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* | None. | Select the target data warehouse into which the queries must be loaded, that is Azure Synapse. This activates the following fields:
Default Value: N/A Example: Azure Synapse | |||||||
Download JDBC Driver Automatically | Checkbox | 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.
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.
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 separate row. See to add a new row. Add each JDBC JAR file in a Downloading Microsoft JDBC Driver for more information about JDBC drivers and download the appropriate driver for your accountseparate row. See Downloading Microsoft JDBC Driver for more information about JDBC drivers and download the appropriate driver for your account.
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.
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.
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 | |||||||
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:
Selecting an option other than None in this field activates the following fields:
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 net (for Blob Storage), .dfs.core.windows.net 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 Container | Database 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.
Default value: N/A Example: hrdepartment | |||||||
Azure Folder | Database 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.
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:
Default value: Storage Account Key Example: AAD User | |||||||
Azure Identity | 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, 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.
Default value: N/A Example: ufv!befDIuf#fnb$KH&_hweuf | |||||||
Client ID | Database 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.
Default value: N/A Example: hewr42ap-32jd-pd95-ms38-b342bnasdh80 | |||||||
Client Secret | Database 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.
Default value: N/A Example: jhweufv!befDIufwb*dsfwujfnb$KH&_ | |||||||
Tenant ID | Database 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.
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 | None. | 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. |
Click Validate after entering the required details to ensure that all fields have been filled accurately. Click Apply to save the settings.
Note | ||
---|---|---|
| ||
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. |
...
Perform the following steps to locate and use the JDBC URL for your Azure Synapse database instance.
- Log into portal.azure.com.
- Click SQL Databases.
- Click on the Database configured for your organization.
- Click Show Database Connection Strings and click JDBC to get the actual connection string.
- 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.
...
The Pipeline accesses the Parquet file using the Storage Account Key mode of authentication.
...