Databricks as Target

Fivetran HVR supports integrating changes into Databricks location. This section describes the configuration requirements for integrating changes using Integrate and Refresh into Databricks location. For the list of supported Databricks versions into which HVR can integrate changes, see Integrate changes into location in Capabilities.

The preferred methods for writing data into Databricks are using Burst Integrate and Bulk Refresh as they provide better performance. However, it is required to create staging files on a temporary location to perform Burst Integrate and Bulk Refresh. For more information about staging, see section Staging for Databricks.

Due to technical limitations, Databricks on Azure is not supported in the HVR releases since 6.1.5/3 to 6.1.5/9.

Grants for Integrate and Refresh

This section lists the grants/permissions required for integrating changes into Databricks.

Following are the permissions required for a cluster that is part of a Unity Catalog:

1. To create target tables using Refresh, the HVR database user must be granted CREATE TABLE, USE SCHEMA, and USE CATALOG privileges:

   GRANT CREATE TABLE ON SCHEMA schema_name TO `user or group`;
   
   GRANT USE SCHEMA ON SCHEMA schema_name TO `user or group`;
   
   GRANT USE CATALOG ON CATALOG catalog_name TO `user or group`;
   

2. To Integrate changes or Refresh data into target tables, the HVR database user must be granted MODIFY privilege:

   GRANT MODIFY ON TABLE table_name TO `user or group`;
   

3. To query a table, the HVR database user must be granted SELECT, USE SCHEMA, and USE CATALOG privileges:

   GRANT SELECT ON TABLE table_name TO `user or group`;
   
   GRANT USE SCHEMA ON SCHEMA schema_name TO `user or group`;
   
   GRANT USE CATALOG ON CATALOG catalog_name TO `user or group`;
   

For more information about these privileges, refer to the Databricks documentation - Azure, AWS.

Additional Considerations or Permissions

• Cluster access to cloud store: The Databricks cluster must have access to the cloud store configured as the staging area. This is crucial for the COPY INTO command to function without errors when using Staging for Databricks.

• External tables and locations: When using Unity Catalog and external tables, configure an external location for the root location of external tables for Azure Databricks or AWS Databricks platforms. This ensures external tables are correctly accessed and managed within Databricks. For more information about external locations, refer to the Databricks documentation - Azure, AWS.

Dropping Columns from Delta Tables

Databricks Runtime version 11 introduced support for the ALTER TABLE DROP COLUMN command. If you have action AdaptDDL configured and want to avoid a full Refresh of the target table when using ALTER TABLE DROP COLUMN on a source table, you must enable the Delta Lake column mapping on the target table in Databricks.

For more information about enabling Delta Lake column mapping, refer to the Databricks documentation.

Prior to Databricks Runtime version 11, only the ALTER TABLE ADD COLUMN operation was supported by Databricks. HVR managed any other DDL changes through an extensive process, which involved copying the target table in Databricks to a temporary table, dropping and re-creating the target table, and then transferring the rows from the temporary table back to the new target table.

To enable column mapping for the existing tables, you must use the following command:

  ALTER TABLE table_name SET TBLPROPERTIES (
    'delta.minReaderVersion' = '2',
    'delta.minWriterVersion' = '5',
    'delta.columnMapping.mode' = 'name'
  )

For new tables, Delta Lake column mapping is not enabled by default. When creating target tables in Databricks using Refresh, you must set the Delta table properties to enable column mapping. This setting affects only new tables and does not override or replace properties set on existing tables.

For more information about Delta table properties, refer to the Databricks documentation.

To enable column mapping for new tables, use the following SparkSession configuration commands:

SET spark.databricks.delta.properties.defaults.autoOptimize.optimizeWrite = true
SET spark.databricks.delta.properties.defaults.autoOptimize.autoCompact = true
SET spark.databricks.delta.properties.defaults.minReaderVersion = 2
SET spark.databricks.delta.properties.defaults.minWriterVersion = 5
SET spark.databricks.delta.properties.defaults.columnMapping.mode = name

Alternatively, you can set these Delta table properties by defining the action Environment for the Databricks location:

Column Names Forced Case

Since v6.1.0/37

This option allows you to manage the case sensitivity of column names created in the target Databricks tables. This applies to Activating Replication, Refresh, and Compare.

Available options are:

• No default: Create column names in the same case as received from the source, which can be either lowercase or uppercase.
• Upper: Create column names in uppercase.
• Lower: Create column names in lowercase.

This option is equivalent to the location property ForceCase.

Location Time Zone

Since v6.1.5/5

The time zone of the HVR Agent and the Databricks database must be the same. This option allows you to specify the timezone for Databricks location. In the HVR User Interface, you can specify the timezone using this option while creating a location (under the Configure Capture/Integrate for Databricks Location section) or by editing the existing location's source and target properties.

The equivalent location property for this field is Connection_Timezone_Name.

Limitations

For Databricks runtime version earlier than 11.2, the DEFAULT constraint is not supported. As a result, it cannot automatically provide a default value for a column when changing its nullability. Therefore, if you need to alter a nullable column to NOT NULL in the target, you must first ensure that all existing NULL values in the source are updated with non-NULL values.