In this article

Overview

You can use the ELT Database Account to connect ELT Snaps with Databricks Lakehouse Platform (DLP) target instance. This account enables you to write transformed data to a target DLP instance hosted in a Microsoft Azure cloud location. The cloud location where the database is hosted is indicated in the JDBC URL for DLP—jdbc:spark://<your_instance_code>.cloud.databricks.com for AWS or jdbc:spark://<your_instance_code>.azuredatabricks.net for Microsoft Azure.

Prerequisites

• A valid DLP account.

• Certified JDBC JAR File: databricks-jdbc-2.6.29.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

• With the basic authentication type for Databricks Lakehouse Platform (DLP) reaching its end of life on July 10, 2024, SnapLogic ELT pipelines designed to use this authentication type to connect to DLP instances would cease to succeed. We recommend that you reconfigure the corresponding Snap accounts to use the Personal access tokens (PAT) authentication type.

Known Issues

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

• Due to an issue with DLP, aborting an ELT Pipeline validation (with preview data enabled) causes only those SQL statements that retrieve data using bind parameters to get aborted while all other static statements (that use values instead of bind parameters) persist.

  • For example, select * from a_table where id = 10 will not be aborted while select * from test where id = ? gets aborted.

  To avoid this issue, ensure that you always configure your Snap settings to use bind parameters inside its SQL queries.

Account Settings

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

Default Properties set internally

No properties are passed in the URL internally for DLP, by default.

Spark settings required for supporting ELT on DLP

Ensure to configure the following Spark configuration properties/settings for your Azure Databricks cluster. Refer Configuring Clusters for Azure Databricks for information on defining Spark Configuration Properties.

The following image depicts the Spark configuration properties defined for a sample DLP instance.

Defining mount points

You can use the following external (cloud storage) buckets/containers and their folder paths for reading, writing, and loading Databricks delta tables.

• AWS S3 (accessed using S3A protocol)

• Azure Blob Storage (accessed using WASBS protocol)

• ADLS Gen 2 (accessed using ABFSS protocol)

To enable Databricks runtime to access externally located data files from the Databricks cluster, you need to mount your bucket/container on to DBFS path. Once the ‘mount point’ is created through a cluster, the corresponding DBFS path allows accessing the external data location (cloud storage).

Security considerations

• The mount point is only a pointer to your cloud storage location, so the data is never synced locally (i.e data is never moved into DBFS)

• Once a mount point is created through a Python Notebook attached to a cluster, users of that cluster can immediately access the mount point.

• To use the mount point in another running cluster, you must run dbutils.fs.refreshMounts() on that running cluster to make the newly created mount point available.

• All users have read and write access to all the objects in the buckets/containers mounted to DBFS.

• Azure Blob storage supports three blob types: block, append, and page. You can only mount block blobs to DBFS.

Mount an S3 Bucket on DBFS Path

• Use Databricks Python Notebook to run the fsutils.mount() command with your access credentials, as follows: 
  
• Verify that the mount point is created by running the display(dbutils.fs.mounts()) command.

Mount an Azure Blob Storage’s Container on DBFS Path

• Use Databricks Python Notebook to run the fsutils.mount() command with your access credentials, as follows: 
  
• Verify that the mount point is created by running the display(dbutils.fs.mounts()) command.

Mount an ADLS Gen2 Container on DBFS Path

The steps below assume you have created the OAuth2 application to access your containers.

• Use Databricks Python Notebook to run the fsutils.mount() command with your access credentials, as follows: 
  

  Optionally, you can add a <directory-name> to the source URI of your mount point.
• Verify that the mount point is created by running the display(dbutils.fs.mounts()) command.

To specify the table names from your cloud storage in an ELT Snap:

• Specify the mount point as DBFS Folder path in the Snap's account and specify the table name in the Snap's (Target) Table Name field.
  Example: `/mnt/my-mount-pt/input/csv/employee` as DBFS Folder path in the Snap's account and file_name in the Snap.
  OR
  
• Specify the entire mount point path including the table name in the Snap's (Target) Table Name field.
  Example: `/mnt/my-mount-pt/input/csv/employee/file_name` as (Target) Table Name in the Snap.
  

In both the above cases, the DBFS folder paths resolve to the corresponding table name in the cloud storage, as follows:

• s3a://<bucket_name>/delta/<table_name>
• wasbs://<mycontainer>@<mystorageaccountt>.blob.core.windows.net/input/csv/people/<table_name>
• abfss://<mycontainer>@myadlsgen2.dfs.core.windows.net/input/csv/people/<table_name>

Troubleshooting

None.

Example

Configuring the ELT Database Account for Connecting to a DLP Instance

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 DLP instance.

Snap Settings

The Pipeline uses an ELT Load Snap to load data from a CSV file located in a DBFS location and is configured to perform the following functions.

• Insert new rows from the CSV file (org10.csv) to an existing target table `/mnt/elt/delta01`.
• In the CSV file, there is a header row, and the delimiter is comma (,).
• The Snap shall infer the schema from the source data.
• The Snap is allowed to enforce the copy operation even when an idempotent row exists (loaded earlier).

Account Settings

The ELT Load Snap uses an account that:

• Downloads the JDBC driver automatically,
• Optimizes the write operation for efficiency, and
• Performs an auto-compact operation on the source data before loading.

DLP instance hosted in Microsoft Azure Cloud	DLP instance hosted in AWS Cloud

See Also

• ELT Snap Pack
• JDBC and ODBC drivers and configuration parameters
• Getting started with SnapLogic