Multiexcerpt include macro

name	ME_Chrome_Saving_Account_Credentials
page	Configuring Spark SQL 2.x Accounts

Account Settings

ParameterData TypeDescriptionDefault ValueExample LabelString

Click the = (Expression) button, if available, to define the corresponding field value using expression language and Pipeline parameters.

Fields marked with an asterisk ( * ) in the table below are mandatory.

Parameter

Field Dependency

Description

Label*

None.

Required. Unique user-provided label for the account.

Default Value: N/A

Example: ELT Account

Account Properties*

Required.

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

This field set consists of the following fields:

Database Type
Download JDBC Driver Automatically
JDBC JARs
JDBC Driver Class
JDBC URL
Username
Password
Warehouse
Default Database Name

Database Type*

String

None.

Required. Select the target data warehouse into which the queries must be loaded.

Available options are:

Snowflake. Activates

the

the Warehouse

and

and Source Location fields.
Redshift. Activates the Source Location field.
Azure Synapse. Activates the following fields

.

:
- External Location
- External Storage Endpoint
- Storage Account
- Azure Container
- Azure Folder
- Azure Auth Type
- Azure Identity
- Azure Secret

N

Databricks Lakehouse Platform. Activates the following fields:
- Use Token Based Authentication
- Username
- Password
- Token
- Default Database Name
- DBFS Folder path (source for loading Databricks table)

Default Value: N/A

Example: Snowflake

Download JDBC Driver Automatically

Checkbox

Select this checkbox to allow the Snap account to download the certified JDBC Driver based on

the

the Database Type you have selected. The following fields are disabled when this checkbox is selected.

JDBC

JARs

JAR(s) and/or ZIP(s) : JDBC Driver
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 JARs:

JDBC JAR(s) and/or ZIP(s): JDBC Driver

fieldtitle

field.

Note

Use of Custom JDBC JAR version

You can use different JAR file versions than those listed here. We recommend that you use the listed JAR file versions. See the

latest

latest Release Notes

for

for more details.

Default Value: Not Selected

Example: Selected

JDBC

JARs: JDBC DriverStringRequired when you do not select

JAR(s) and/or ZIP(s): JDBC Driver

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

Click Image Modified

to

to add a new row. Add each JDBC JAR file in a separate row.

See

See Downloading the JDBC Driver

for

for more information about JDBC drivers and downloading the appropriate driver for your account.

N

Default Value: N/A

Example: snowflake-jdbc-3.9.1.jar

JDBC driver class

StringRequired when you do not select the

*

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

Specify the driver class to use for your application.

Note

We recommend that you use the following classes to suit your database type as other classes and methods may change due to future enhancements:

Snowflake: net.snowflake.client.jdbc.SnowflakeDriver
Redshift: com.amazon.redshift.jdbc42.Driver
Azure Synapse: com.microsoft.sqlserver.jdbc.SQLServerDriver

N

Databricks Lakehouse Platform: com.simba.spark.jdbc.Driver

Default Value: N/A

Example: net.snowflake.client.jdbc.Snowflake

Driver
JDBC URL*

URL

None.

Required. Enter the JDBC driver connection string that you want to use, based on the Database you are connecting to. Use the appropriate syntax provided below.

Snowflake:
jdbc:snowflake://<full_account_name>.snowflakecomputing.com
See Snowflake'

s

s JDBC Connection Parameters

for

for more information.
Redshift:
jdbc:redshift://endpoint:port/<databaseName>?<parameter1=value1>&<parameter2=

value2>

value2>
For example, jdbc:redshift://endpoint:port/<databaseName>?ssl=true&UID=your_username&PWD=your_password
See Redshift'

s

s Configure JDBC Connection

for

for more information.
Azure Synapse:
jdbc:sqlserver://endpoint:port;database=<databaseName>;<parameter1=value1>;<parameter2=value2>;
For 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;
See Microsoft's Connection strings for SQL pools in Azure Synapse

for N/A

for more information.

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

Note

title	Avoid passing Password inside the JDBC URL

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

Databricks Lakehouse Platform:
jdbc:spark://dbc-ede87531-a2ce.cloud.databricks.com:443/default;transportMode=http;ssl=1;httpPath=sql/protocolv1/o/6968995337014351/0521-394181-guess934;AuthMech=3;UID=token;PWD=<personal-access-token>
See Microsoft's JDBC and ODBC drivers and configuration parameters for more information.

Alternatively, for Redshift, Azure Synapse, and Databricks Lakehouse Platform, you can make use of the Username, Password, and Database Name fields, along with the Advanced Properties > URL Properties field set to provide the parameters required for building your JDBC URL. See Passing your JDBC URL for more information.

Note

title	Avoid passing Password inside the JDBC URL

If you specify the password inside the JDBC URL, it is saved as it is and is 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:snowflake://xy12345.us-east-2.

aws.snowflakecomputing.com

, jdbc:snowflake://xy12345.east-us-2.

azure.aws.snowflakecomputing.com

Use Token Based Authentication

Database Type is Databricks Lakehouse Platform

Select this checkbox to use token-based authentication for connecting to the target database (DLP) instance. Activates the Token field.

Default value: Selected

Example: Not selected

Username

StringRequired.

*

When Use Token Based Authentication checkbox is not selected.

Enter the username provided for your database account.

N/A

Note
In case the Database Type is Databricks Lakehouse Platform and the Use Token Based Authentication checkbox is not selected, you can enter the value token in this field and provide the token's value in the Password field.

Default value: N/A

Example: Bigdatasnaplogic

Password

StringRequired.

*

When Use Token Based Authentication checkbox is not selected.

Enter the password for the username specified above.

N/A<Encrypted>WarehouseString

Required. Activates when you select the Snowflake option in the Database Type field.

Enter the name of the Snowflake warehouse that you want to use.

N/A12345abcdefghikj1km2345noDefault Database NameString

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

N/AEMPLOYEEDBSource LocationString

Required. Activates when you select the Snowflake or Redshift option in the Database Type field.

Select the source data warehouse 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

S3. Activates the Authentication, S3 Bucket, and S3 Folder fields.

Azure. Activates the Authentication, SAS Token, Storage Account, Azure Container, and Azure folder fields.

Note
Azure is not applicable to the Redshift database. Source Location field is not applicable when you select Azure Synapse option in the Database Type field. In other words, configure the External Storage (source) details separately, as S3 is not applicable to Azure Synapse.

Google Cloud Storage. (GCS) Activates the Authentication, Google Cloud Storage (GCS) Bucket, and Google Cloud Storage (GCS) Folder fields.

None

S3

Azure

Google Cloud Storage

AuthenticationString

Activates when you select S3, Azure or Google Cloud Storage in the Source Location field.

Select the authentication method to use for the loaded data.

Available options are:

Source Location Credentials: Select this option when you do not have a storage integration setup in your S3 or Azure data storage. Activates the Access Key and Secret Key fields for S3 or the SAS Token field for Azure.
Storage Integration: Select this option when you want to use the storage integration in your target database for the selected source location. Activates the Storage Integration field.
IAM Role: Select this option if you want to use the IAM Role authentication. Activates the IAM Role field. You must provide the IAM role associated with the target Redshift cluster in the IAM Role field. See Create an IAM Role for details on creating and associating IAM Role with your Redshift cluster.

Note
Storage integration is not applicable to the Redshift database. IAM role authentication is not applicable to the Snowflake database. Authentication field is not applicable when you select Azure Synapse option in the Database Type field.

Source Location CredentialsStorage IntegrationStorage IntegrationString

Activates when you select the S3 or Google Cloud Storage in the Source Location field and Storage Integration option in the Authentication field.

Enter the name of the storage integration in your Snowflake database. Navigate to one of the following links for more information on setting up your Storage Integration in Snowflake.

Note
Storage integration is not applicable to the Redshift and Azure Synapse databases.

N/A

my_s3_integration

my_azure_integration

my_sf_gcs_integration

IAM RoleString

Activates when you select the S3 option in the Source Location field and IAM Role option in the Authentication field.

Enter

Note
In case the Database Type is Databricks Lakehouse Platform and the Use Token Based Authentication checkbox is not selected, you can enter the value token in the Username field and provide the token's value in this field.

Default value: N/A

Example: <Encrypted>

Token*

When Use Token Based Authentication checkbox is selected.

Enter the token value for accessing the target database / folder path.

Default value: N/A

Example: <Encrypted>

Warehouse*

Database Type is Snowflake

Enter the name of the Snowflake warehouse that you want to use.

Default value: N/A

Example: 12345abcdefghikj1km2345no

Default Database Name*

String

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

Default value: N/A

Example: EMPLOYEEDB

DBFS Folder path (source for loading Databricks table)

Database Type is Databricks Lakehouse Platform

Specify the fully qualified path to a target folder in your DBFS instance. It should begin with / which denotes the DBFS Root folder.

Note

If DBFS path has a mount point included (to a folder in S3 or Azure Blob Storage or ADLS Gen2 Storage), this field assumes that the specified external data folder is mounted on to DBFS path already. For example, if /mnt/mnt_point _to_s3/csv is the specified path to DBFS folder, this field assumes that you have already created mnt_point_to_s3 using DBFS mount command in Databricks Python Notebook.

Default value: N/A

Example: /my_DBFS/mounted0408_folder

Source Location*

Database Type is Snowflake or Redshift

Select the source data warehouse 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
S3. Activates the Authentication, S3 Bucket, and S3 Folder fields.
Azure. Activates the Authentication, SAS Token, Storage Account, Azure Container, and Azure folder fields.
- Azure is not applicable to the Redshift database.
- Source Location field is not applicable when you select Azure Synapse option in the Database Type field. In other words, configure the External Storage (source) details separately, as S3 is not applicable to Azure Synapse.
Google Cloud Storage. (GCS) Activates the Authentication, Google Cloud Storage (GCS) Bucket, and Google Cloud Storage (GCS) Folder fields.

Default value: None

Example: S3, Azure, Google Cloud Storage

Authentication

Source Location is S3, Azure, or Google Cloud Storage

Select the authentication method to use for the loaded data.

Available options are:

Source Location Credentials. Select this option when you do not have a storage integration setup in your S3 or Azure data storage. Activates the Access Key and Secret Key fields for S3 or the SAS Token field for Azure.
Source Location Session Credentials. Select this option if you have session credentials to access the source location. Activates the Session Access Key, Session Secret Key, and Session Token fields for S3 or the SAS Token field for Azure.
Storage Integration. Select this option when you want to use the storage integration in your target database for the selected source location. Activates the Storage Integration field.
IAM Role. Select this option if you want to use the IAM Role authentication. Activates the IAM Role field. You must provide the IAM role associated with the target Redshift cluster in the IAM Role field.

See

See Create an IAM Role

for

for details on creating and associating IAM Role

with your Redshift cluster.

Note
Applicable to the Redshift database only.

N/AmyiamroleAccess KeyStringActivates when you select the S3 option in the Source Location field and Source Location Credentials option in the Authentication field

with your Redshift cluster.

Note
Storage integration is not applicable to the Redshift database. IAM role authentication is not applicable to the Snowflake database. Authentication field is not applicable when you select Azure Synapse option in the Database Type field.

Default value: Source Location Credentials

Example: Storage Integration

Storage Integration

Source Location is S3, Azure, or Google Cloud Storage and
Authentication is
Storage Integration.

Enter the name of the storage integration in your Snowflake database. Navigate to one of the following links for more information on setting up your Storage Integration in Snowflake.

Note
Storage integration is not applicable to the Redshift and Azure Synapse databases.

Default value: N/A

Example: my_s3_integration, my_azure_integration, my_sf_gcs_integration

IAM Role

Database Type is Redshift, Source Location is S3, and
Authentication is
IAM Role.

Enter the IAM role associated with the target Redshift cluster. See Create an IAM Role for details on creating and associating IAM Role with your Redshift cluster.

Default value: N/A

Example: myiamrole

Access Key

Source Location is S3, and Authentication is
Source Location Credentials.

Enter the access key ID associated with your AWS S3 account.

Default value: N/A

Example: ABCDEFGHIJKL1MNOPQRS

Secret Key

Source Location is S3, and Authentication is
Source Location Credentials.

Enter the client secret key associated with your AWS S3 account.

Default value: N/A

Example: aBcdeFGhiJKLM/N1OPQRS/tUvWxyZABCDEFGHIJKLMN

SAS Token

Source Location is Azure, and Authentication is
Source Location Credentials.

Enter the SAS token part of the SAS URI associated with your Azure storage account. See Getting Started with SAS for details.

Default value: N/A

Example: ?sv=2020-08-05&st=2020-08-29T22%3A18%3A26Z&se=2020-08-30T02%3A23%3A26Z&sr=b&sp=rw&sip=198.1.2.60-198.1.2.70&spr=https&sig=A%1DEFGH1Ijk2Lm3noI3OlWTjEg2tYkboXr1P9ZUXDtkk%3D

Session Access Key

Source Location is S3, Database Type is Snowflake, Redshift or Databricks Lakehouse Platform, and Authentication is
Source Location Session Credentials.

Enter the access key ID associated with your AWS S3 account.

N

Default value: N/A

Example: ABCDEFGHIJKL1MNOPQRS

Session Secret Key

StringActivates when you select the S3 option in the Source Location field and Source Location Credentials option in the Authentication field

Source Location is S3, Database Type is Snowflake, Redshift or Databricks Lakehouse Platform, and Authentication is
Source Location Session Credentials.

Enter the client secret key associated with your AWS S3 account.

N

Default value: N/A

Example: aBcdeFGhiJKLM/N1OPQRS/t

UvWxyZABCDEFGHIJKLMN

SAS TokenStringActivates when you select the Azure option in the Source Location field and Source Location Credentials option in the Authentication field


Session Token	Source Location is S3, Database Type* is Snowflake, Redshift or Databricks Lakehouse Platform,* and Authentication is Source Location Session Credentials.	Enter the

SAS token part of the SAS URI associated with your Azure storage account. See Getting Started with SAS for details.N/A?sv=2020-08-05&st=2020-08-29T22%3A18%3A26Z&se=2020-
08-30T02%3A23%3A26Z&sr=b&
sp=rw&sip=198.1.2.60-198.1.2.70&spr=https&sig=
A%1DEFGH1Ijk2Lm3noI3OlW
TjEg2tYkboXr1P9ZUXDtkk%3DStorage AccountString

session token value when the authorization to access the S3 folder is based on Security Token Service (STS) based temporary credentials.

Default value: N/A

Example: A%1DEFGH1Ijk2Lm3noI3OlWTjEg2tYkboXr1P9ZUXDtkk%3D

Storage Account

Source Location is S3.

Enter the name of your Azure storage account.

Note

Not

Storage Account is not applicable to the Redshift database.

N

Default value: N/A

Example: employeedata

S3 Bucket

StringActivates when you select the S3 option in the

Source Location

field

is S3.

Enter the name of the bucket from which to load the data.

NActivates when you select Redshift as the Database Type and S3 option in the Source Location field

Default value: N/A

Example: employeedata

S3 Bucket Region

String

Database Type is Redshift and Source Location is S3.

Select the region of your Redshift cluster if it is different from the region of the S3 bucket.

Available options are:

Default
us-east-1
us-east-2
us-west-1
us-west-2
af-south-1
ap-east-1
ap-south-1
ap-northeast-1
ap-northeast-2
ap-northeast-3
ap-southeast-1
ap-southeast-2
ca-central-1
cn-north-1
cn-northwest-1
eu-central-1
eu-west-1
eu-west-2
eu-west-3
eu-south-1
eu-north-1
me-south-1
sa-east-1

Default value: Default

Example: us-east-1

, us-east-2
S3 Folder

String

Activates when you select the S3 option in the

Source Location

field

is S3.

Enter the name of the folder in the S3 bucket specified above where the source files are located.

NActivates when you select the Azure Synapse option in the Database Type field

Default value: N/A

Example: hrdepartment

External Location
(For Azure Synapse only)

Drop-down list

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

SnapActivates when you select the Azure Synapse option in the Database Type field

Snap.

Available options are:

Blob Storage
Azure Data Lake Gen2

Default value: Blob Storage

Example: Azure Data Lake Gen2

External Storage Endpoint
(For Azure Synapse only)

String

Database Type is Azure Synapse.

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

Snap.N

Snap.

Default value: N/A

Example: .blob.core.windows.

net

net (for Blob Storage)

, .dfs.core.windows.

net

net (for ADLS Gen2)
Storage Account (For Azure Synapse only)

StringActivates when you select the Azure Synapse option in the Database Type field

Database Type is Azure Synapse.

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

Snap.

N

Default value: N/A

Example: adlsgen2v02

Azure Container

String

Activates when you select the Azure Synapse option in the Database Type field. Also when you choose to use Azure as Source Location with the Snowflake Database Type

Source Location is Azure.

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

Note

Not

Azure Container is not applicable to the Redshift database.

N

Default value: N/A

Example: hrdepartment

Azure Folder

String

Activates when you select the Azure Synapse option in the Database Type field. Also when you choose to use Azure as Source Location with the Snowflake Database Type

Source Location is Azure.

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.

Note

Not

Azure Folder is not applicable to the Redshift database.

NActivates when you select

Default value: N/A Example: salaries
Google Cloud Storage (GCS) Bucket

String

Source Location is Google Cloud Storage

in the Source Location field and

and Authentication is
Storage Integration

option in the Authentication field

.

Enter the name of the GCS bucket from which to load the data to your Snowflake database.

NActivates when you select

Default value: N/A

Example: elt_gcs_bucket_1

Google Cloud Storage (GCS) Folder

String

Source Location is Google Cloud Storage

in the Source Location field and

and Authentication is
Storage Integration

option in the Authentication field

.

Enter the name of the folder in the GCS bucket where the source files are located.

NActivates when you select the Azure Synapse option in the Database Type field

Default value: N/A

Example: elt_gcs_bucket_1_CSV_Files

Azure Auth Type
(For Azure Synapse only)

Drop-down list

Database Type is Azure Synapse.

Select the authentication type to use for accessing the selected Azure external storage location. Then, provide the values for Azure Identity, Azure Secret, Storage Key

fields Activates when you select an option other than AAD User in the Azure Auth Type field

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 Identity
(For Azure Synapse only)

String

Azure Auth Type is AAD User.

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

NActivates when you select

Default value: N/A

Example: Storage Account Key

Azure Secret
(For Azure Synapse only)

String

Azure Auth Type is Storage Account Key, Shared Access Signature, or Service Principals

option in the Azure Auth Type field

.

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.

NActivates when you select

Default value: N/A

Example: UvhVF65Bukk9hVF65BuUvhVF

Storage Key
(For Azure Synapse only)

String

Azure Auth Type is Shared Access Signature, Managed Identity, or Service Principals

option in the Azure Auth Type field

.

Enter the storage access key value corresponding to the Azure account mentioned in Storage Account

field

field. See COPY INTO (Transact-SQL) in Microsoft Docs for more information on the expected values for this field.

Info
This field is needed only when you want to define

the

the File Name Pattern in the ELT Load

Snap to

Snap to locate and read the file/s from

the

the Azure Folder path. Else, leave it blank.

NActivates when you select AAD User option in the Azure Auth Type field

Default value: N/A

Example: ufv!befDIuf#fnb$KH&_hweuf

Client ID

String

Azure Auth Type is AAD User.

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

Info
This field is needed only when you want to define

the

the File Name Pattern

in

in the ELT Load

Snap to

Snap to locate and read the file/s from

the

the Azure Folder path. Else, leave it blank.

N

Default value: N/A

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

Client Secret

String

Activates when you select AAD User option in the

Azure Auth

Type field

Type is AAD User.

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

Info
This field is needed only when you want to define

the

the File Name Pattern in the ELT Load

Snap to

Snap to locate and read the file/s from

the

the Azure Folder path. Else, leave it blank.

NActivates when you select AAD User option in the Azure Auth Type field

Default value: N/A

Example: jhweufv!befDIufwb*dsfwujfnb$KH&_

Tenant ID

String

Azure Auth Type is AAD User.

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

Info
This field is needed only when you want to define

the

the File Name Pattern in the ELT Load

Snap to

Snap to locate and read the file/s from

the

the Azure Folder path. Else, leave it blank.

N

Default value: N/A

Example: b342bnas-he38-apms-dhjd-pd95wr423280

Advanced Properties

Other parameters that you want to specify as URL properties. See the following resources for a list of parameters that can be specified in this field set.

Note

In case of Snowflake, DO NOT configure parameters that are already present in the Account Properties field set. For example:

db=<name>
warehouse=<name>

This field set consists of the following fields:

URL Properties
- URL Property Name
- URL Property Value

URL Properties

String/Integer

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

the URL Property Name

field

field and its value in

the

the URL Property Value

field

field.

N/A

MAX_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.

Passing your JDBC URL

Order of Precedence

...

Versions Compared

Old Version 47

New Version 48

Key

Account Settings

Passing your JDBC URL

Order of Precedence

Page Comparison

Versions Compared

Old Version 47

New Version 48

Key

Account Settings

Passing your JDBC URL

Order of Precedence