Databricks Writer Parameters

Version 2.1.0 of this package added Database Connections support for Databricks Credentials and Cloud Upload Credentials.

To define a new connection from the Connection parameter in a Databricks format: Select Add Database Connection, and scroll to Databricks.

See database-specific parameters below, as well as the section Adding a Database Connection in a workspace in Using Database Connections.

The new connection can be made visible only to the current user, or can be shared among multiple users. To select an existing, previously defined connection, see the section Reusing a Database Connection in Using Database Connections.

About Database Connections

Database formats include a Database Connection parameter that defines and stores authentication information. For general information about sharing database connections, please see Using Database Connections. Using Database Connections.

Note that Database Connection parameters differ slightly, depending on context and/or database format.

Connection

From the Connection parameter in a database format, you can do one of the following:

  • Select an existing, previously defined connection. See the section Reusing a Database Connection in Using Database Connections
  • Select Add Database Connection to define a new connection. See database-specific parameters below, as well as the section Adding a Database Connection in a Workspace in Using Database Connections. The new connection can be made visible only to the current user, or can be shared among multiple users.

Databricks Credentials

Server Hostname: The URL of the Databricks workspace. This will take the format https://<workspace_id>.cloud.databricks.com/ or https://adb-<id>.azuredatabricks.net/.

Cluster ID: The ID of the cluster to run Databricks commands with. This cluster should be configured with access to the cloud storage location specified in the writer parameters.

Authentication Method: The method used for authentication.

Personal Access Token

(default)

Allows you to connect using a personal access token from Databricks.

Personal Access Token

Used only when the Authentication Method parameter is set to Personal Access Token.

The personal access token to connect to the specified cluster.

OAuth

Uses an OAuth web connection to a service principal that accesses the Databricks service.

Adding a Databricks OAuth Connection

Used only when the OAuth authentication method is selected and you click Add Web Connection.

The OAuth web connection used to connect to the specified cluster.

  • Token Endpoint URL – The URL used to request an OAuth connection token.
  • Client ID – The client ID for the OAuth secret for the service principal.
  • Client Secret – The value of the OAuth secret for the service principal.
  • Scope – The scope of the requested OAuth access token. For Microsoft Azure-hosted Databricks clusters, the scope value must be set to 2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default. See Get a Microsoft Entra ID access token with the Microsoft identity platform REST API from the Azure Databricks documentation website for more information on the required scope value.
More Information

Catalog: The catalog to write to. Each writer can only write to a single catalog. Click the [...] button to see a list of accessible catalogs in the workspace.

Cloud Upload Credentials

Storage Type: Amazon S3 | Microsoft Azure Data Lake Gen 2 | Databricks Unity Catalog Volume

Select the form of cloud storage to use as a staging area. The Databricks cluster selected should be configured with its own access to this location. The cloud storage credentials provided here will strictly be used by FME to upload data to the cloud storage location and will not be passed through in any Databricks commands.

When Storage Type is Amazon S3, these parameters are enabled:

  • S3 Connection: An AWS web connection containing access key credentials for an AWS bucket.
  • S3 Bucket: The name of an S3 bucket to upload staging files to. If the Amazon S3 FME Package is installed in Workbench, clicking the browse [...] button will display a list of buckets. Select only the buckets that are accessible by the Databricks workspace.
Note  All files uploaded by the writer will begin with an fme-databricks/tmp key. Once the translation is complete, you can remove the files.

When Storage Type is Microsoft Azure Data Lake Gen 2, these parameters are enabled:

  • Cloud Authentication Method: Provide Azure credentials through a Web Connection or a Microsoft SAS token.
  • Azure Storage Connection: An Azure Storage Web connection containing an Azure storage account name and either an account shared key or an SAS token.
  • Azure Storage Account: The name of the Azure storage account to use.
  • Shared Access Signature (SAS): A valid Azure SAS token.
  • Azure Data Lake Container: The name of the Azure Data Lake container. If the Azure Storage Connector FME Package is installed in Workbench, clicking the browse [...] button will display a list of containers. Select only the containers that are accessible by the Databricks workspace.
Note  Files will be uploaded to a folder under fme-databricks/tmp. Once the translation is complete, you can remove the files.

When Storage Type is Databricks Unity Catalog Volume, these parameters are enabled:

  • Volume Catalog: The Databricks Catalog where the Unity Catalog (UC) Volume is located.
  • Volume Schema: The Schema (or Database) within the catalog where the UC Volume exists.
  • Volume Name: The Volume Name where files will be staged before being loaded into Databricks tables.

Unlike S3 and Azure, files uploaded to UC Volumes remain inside the Databricks workspace, eliminating the need for external cloud storage credentials.

Note  Files will be uploaded to a folder under /Volumes/{catalog}/{schema}/{volume}/fme-databricks/tmp. Once the translation is complete, you can remove the files.

Advanced

How many features to include in each write request. If this value is not specified, the default is 100,000.

This parameter allows for the execution of SQL statements before writing to a table. For example, it may be necessary to clean up a table before attempting to write to it. The statements will be executed only when the first feature arrives at the writer.

For detailed information about SQL functions, click the corresponding menu item in the SQL to Run editor helpClosed.

Available menu options depend on the format.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors ;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

This parameter allows for the execution of SQL statements after a set of tables has been written. For example, it may be necessary to clean up a temporary view after creating it.

For detailed information about SQL functions, click the corresponding menu item in the SQL to Run editor helpClosed.

Available menu options depend on the format.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors ;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

This setting enables the creation of tables without data features.

  • No - Tables are only created when receiving data features.
  • Yes - Tables are created without data features if possible.
Note  See Creating Tables Without Data Features for more information.