Databricks - Target environment with the Hive metastore

Applies to
Platform: Databricks

A Databricks target environment with the Hive metastore needs a workspace, a data lake storage account, an all-purpose cluster and the Databricks CLI. Prepare it before you replace the placeholders and deploy the artifacts of a Databricks generator. Afterwards deploy.ps1 can upload the notebooks and run the deployment on the cluster.

This article applies when the project property Use Unity Catalog is cleared. For projects with Unity Catalog, see Databricks - Target environment with Unity Catalog. biGENIUS support does not cover the installation and configuration of Databricks beyond the example in this article.

Prerequisites

An Azure Databricks workspace and a user who can create an all-purpose cluster.
An Azure storage account with hierarchical namespace for the data lake, and a service principal that can read and write it. Its client ID and secret are stored in a Databricks secret scope.
Use Unity Catalog is cleared in Project Settings:
PowerShell 7 on the deploying machine. deploy.ps1 requires it.

Prepare the data lake

Create a container or folder for the data lake in the storage account, for example datalake.
Enter one folder per layer in the database_directory placeholders, for example abfss://datalake@<account>.dfs.core.windows.net/rawvault/. See Databricks - Placeholder values.

The deployment creates each layer database with CREATE DATABASE IF NOT EXISTS and the location of its database_directory. The database names are the database_name placeholders, prefilled with the layer names of the project, for example stage, rawvault, businessvault and mart.

Create the cluster

In the workspace, open Compute and choose Create compute.
Enter a name, for example dwh_cluster, and choose the node type, for example Standard_DS3_v2 as a single node.

Under Advanced, Spark, enter the Spark configuration for each storage account the cluster reads or writes. With a service principal, the lines are:

fs.azure.account.auth.type.<account>.dfs.core.windows.net OAuth
fs.azure.account.oauth.provider.type.<account>.dfs.core.windows.net org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider
fs.azure.account.oauth2.client.id.<account>.dfs.core.windows.net {{secrets/<scope>/clientid}}
fs.azure.account.oauth2.client.secret.<account>.dfs.core.windows.net {{secrets/<scope>/clientsecret}}
fs.azure.account.oauth2.client.endpoint.<account>.dfs.core.windows.net https://login.microsoftonline.com/<tenant ID>/oauth2/token

Here <account> is the storage account name and <scope> the secret scope with the secrets clientid and clientsecret. Details are in Connect to Azure Data Lake Storage.

Choose Create compute and wait until the cluster is running.

Install the Databricks CLI

Install the Databricks CLI, for example with winget install Databricks.DatabricksCLI.
Open a new PowerShell 7 window and run databricks -v. The CLI prints its version.

On its first run, deploy.ps1 creates a CLI profile and signs you in through the browser, see Databricks - Deployment.

Provide the source data

Which source system types a generator configuration accepts is listed in Source systems per generator configuration. A Data Mart project has no discovery; it uses model objects of linked projects, see Manage Linked Projects.

Files with Stage Files

Parquet, Delta or CSV files are loaded by a separate Databricks Stage Files project. A second project, for example a Data Vault project, then uses its Stage model objects as a linked project.

Upload the files into a storage account the cluster can read, one folder per source model object, for example abfss://source@<account>.dfs.core.windows.net/sales/customer/.
Enter the parent folder with a trailing slash as file_path, here abfss://source@<account>.dfs.core.windows.net/sales/. The loader reads the folder named like the source model object below it.

To create the source model objects from the files, see Discover Parquet or Delta files on Azure.

Databases with Stage JDBC

Tables of MariaDB, Microsoft SQL Server, MySQL, Oracle or PostgreSQL are loaded by a separate Databricks Stage JDBC project, used as a linked project in the same way. The cluster must reach the database. For Azure SQL Database, select Allow Azure services and resources to access this server in the networking settings of the server:

The cluster has the JDBC drivers of Microsoft SQL Server, MariaDB and PostgreSQL. For Oracle and MySQL, install the JDBC driver as a library of the cluster, for example from Maven: com.oracle.database.jdbc:ojdbc11 or com.mysql:mysql-connector-j, and enter its driver class in jdbc_driver (oracle.jdbc.OracleDriver, com.mysql.cj.jdbc.Driver).

Enter the connection in the jdbc_url, jdbc_driver, jdbc_user and jdbc_password placeholders, and the source schema in schema_name of the source system.

Check the result

In a notebook attached to the cluster, list the data lake and, for Stage Files, the source folder:

dbutils.fs.ls("abfss://datalake@<account>.dfs.core.windows.net/")
dbutils.fs.ls("<file_path>")

The first call lists the container content without an access error. The second lists one folder per source model object.

Failure symptoms and recovery

The deployment script does not find the Databricks CLI

Message: Databricks CLI is not installed, not recognized or the PATH environment variable is not properly configured.
Cause: The CLI is not installed, or its folder is not in PATH of the PowerShell window.
Fix: Install the CLI, open a new PowerShell 7 window and check databricks -v.

For failures in other steps, see Troubleshoot generation, deployment and load failures.

Release differences

Up to Generator 1.10, Use Unity Catalog was cleared by default. Since Generator 2.0 it is selected by default; clear it for the Hive metastore.
Up to Generator 1.9, a Stage JDBC project took the source schema from the model object name, for example Sales.CreditCard. Since Generator 1.10 it comes from schema_name of the source system.