Skip to content

Databricks Orchestrator

Image$DATAOPS_DATABRICKS_ORCHESTRATOR_IMAGE

The Databricks orchestrator runs SQL against a Databricks SQL warehouse, and creates the Unity Catalog catalogs and schemas your project declares in YAML. It authenticates as a service principal with OAuth machine-to-machine (M2M) credentials stored in the DataOps Vault.

You can run SQL either through job variables or with the dataops-databricks command-line tool. To set up the workspace, the service principal and the vault, see the Databricks Setup Checklist, which also lists the differences from Snowflake.

Usage

A job runs SQL when it sets DATAOPS_RUN_SQL, and creates catalogs and schemas when it sets DATAOPS_DATABRICKS_CONFIGURATION_DIR. Neither kind of job needs credential variables: the orchestrator reads DATABRICKS.HOST and DATABRICKS.CONNECTION.* from the vault for any connection variable the job leaves unset.

Unqualified names resolve in the workspace's default catalog unless DATAOPS_DATABRICKS_CATALOG is set, for example to $DATAOPS_DATABASE.

Running SQL

The following example runs a query and prints the result in the job log:

pipelines/includes/local_includes/databricks/run_sql.yml
Run Databricks SQL:
  extends:
    - .agent_tag
  stage: "Additional Configuration"
  image: $DATAOPS_DATABRICKS_ORCHESTRATOR_IMAGE
  variables:
    DATAOPS_RUN_SQL: SELECT current_catalog(), current_user();
  script:
    - /dataops
  icon: ${DATABRICKS_ICON}

A job that calls dataops-databricks itself, without DATAOPS_RUN_SQL, sets the connection variables with DATAOPS_VAULT(...) and sources $DATAOPS_SOURCE_FILE after /dataops:

pipelines/includes/local_includes/databricks/run_sql.yml
Run Databricks SQL:
  extends:
    - .agent_tag
  stage: "Additional Configuration"
  image: $DATAOPS_DATABRICKS_ORCHESTRATOR_IMAGE
  variables:
    DATAOPS_DATABRICKS_HOST: DATAOPS_VAULT(DATABRICKS.HOST)
    DATAOPS_DATABRICKS_HTTP_PATH: DATAOPS_VAULT(DATABRICKS.CONNECTION.HTTP_PATH)
    DATAOPS_DATABRICKS_CLIENT_ID: DATAOPS_VAULT(DATABRICKS.CONNECTION.CLIENT_ID)
    DATAOPS_DATABRICKS_CLIENT_SECRET: DATAOPS_VAULT(DATABRICKS.CONNECTION.CLIENT_SECRET)
    DATAOPS_SOURCE_FILE: $CI_PROJECT_DIR/env.sh
  script:
    - /dataops
    - source $DATAOPS_SOURCE_FILE
    # emit the current catalog and the service principal's id
    - dataops-databricks "SELECT current_catalog(), current_user();"
  icon: ${DATABRICKS_ICON}

DATAOPS_RUN_SQL also accepts the path of a .sql file, for example $CI_PROJECT_DIR/dataops/sql/my_script.sql. A path ending in .sql that doesn't exist fails the job rather than being sent to the warehouse as SQL.

The orchestrator waits up to an hour for a statement to finish, then cancels it and fails the job. Each statement carries query tags naming the pipeline, job, branch and project that ran it, so you can find it in the workspace's query history.

Running several statements

The orchestrator sends the SQL to the warehouse as one request, exactly as written, and Databricks runs one statement per request. To run several statements, wrap them in a BEGIN ... END compound statement and end every statement, including the last, with ;:

dataops/sql/my_script.sql
BEGIN
  USE CATALOG my_catalog;
  USE SCHEMA reporting;
  CREATE OR REPLACE TABLE daily_totals AS
    SELECT order_date, sum(amount) AS total FROM orders GROUP BY order_date;
  SELECT count(*) AS days FROM daily_totals;
END

The block runs in one session, returns only its last statement's result, and isn't a transaction: if a statement fails, the ones before it have already run. SET inside a block assigns a SQL variable, not a configuration parameter.

Changing several tables together

For changes that must all happen or none, start the block with BEGIN ATOMIC instead of BEGIN. An atomic block can't sit inside another block. If a statement fails, or the block raises an error with SIGNAL, everything the block wrote is rolled back and the job fails:

dataops/sql/add_orders.sql
BEGIN ATOMIC
  INSERT INTO sales.orders SELECT * FROM staging.new_orders;
  UPDATE sales.order_totals SET total = (SELECT sum(amount) FROM sales.orders);
  IF (SELECT min(amount) FROM sales.orders) < 0 THEN
    SIGNAL SQLSTATE '22000' SET MESSAGE_TEXT = 'Negative order amount';
  END IF;
END
  • Every table the block writes needs the catalogManaged table feature. Create it with TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported'), or add the feature to an existing table with ALTER TABLE ... SET TBLPROPERTIES. Without it, the block fails with TRANSACTION_NOT_SUPPORTED.WRITE_NON_CATALOG_MANAGED_TABLE.
  • The block can only change rows. Inside it, Databricks accepts INSERT, UPDATE, DELETE, MERGE, queries and control flow such as IF. Create tables, or add the feature, in an earlier job.
  • Two blocks that run at once can conflict. Databricks doesn't lock tables, so both run. If one reads data the other changes, the one that commits second fails with a concurrent-write error such as DELTA_CONCURRENT_APPEND.WHOLE_TABLE_READ. To make a job's runs take turns, give it a resource_group, for example add-orders-$CI_COMMIT_REF_SLUG for one at a time on each branch.

Using pipeline values in SQL

Databricks SQL can't read environment variables. To use a value such as the catalog name or the pipeline id, put the SQL in a file under dataops/ whose name contains .template., for example export_orders.template.sql. Before the job's SQL runs, template rendering writes a copy without .template. beside it, with {{ env.DATAOPS_DATABASE }} and other placeholders filled in. Point DATAOPS_RUN_SQL at the rendered copy. Rendering covers SQL comments too, so don't write {{ ... }} in a comment.

Cancelled jobs

A cancelled or timed-out job leaves its statement running until the warehouse's STATEMENT_TIMEOUT, two days by default; see Limit how long a statement can run.

Creating catalogs and schemas

The Set Up Databricks job in the DataOps Reference Project creates the catalogs and schemas declared in dataops/databricks/. It runs in the Databricks Setup stage, with DATAOPS_DATABRICKS_CONFIGURATION_DIR set to $CI_PROJECT_DIR/dataops/databricks. To include it in a pipeline, see Lay out the project.

Declare the catalogs and schemas in one or more YAML files directly in that directory:

dataops/databricks/catalogs.template.yml
config-version: 1
catalogs:
  "{{ env.DATAOPS_DATABASE }}":
    comment: Main catalog for environment {{ env.DATAOPS_ENV_NAME }}
    schemas:
      RAW:
        comment: Source tables
      STAGING:
Key Level Description
config-version File Optional. Defaults to 1, the only version accepted
catalogs File REQUIRED. A mapping of catalog names
comment Catalog Optional. The catalog's comment
schemas Catalog Optional. A mapping of schema names
comment Schema Optional. The schema's comment

The job validates every file and fails, listing every problem, before anything reaches the workspace. It then prints the DDL it generated and runs it as one BEGIN ... END block of CREATE CATALOG IF NOT EXISTS and CREATE SCHEMA IF NOT EXISTS statements.

  • Existing objects are left as they are. A second run changes nothing, and a comment changed in the file isn't applied to an existing catalog or schema.
  • Nothing is removed. A catalog or schema removed from the files, or renamed, stays in the workspace. See Dropping a feature branch's catalog.
  • Each name is given once. Unity Catalog stores names in lower case, so RAW and raw are the same name. A catalog name may appear once across all the files, and a schema name once within its catalog.

The service principal needs CREATE CATALOG on the metastore to create a catalog.

Supported parameters

Parameter Required/Default Description
DATAOPS_RUN_SQL REQUIRED—to run SQL A SQL string, or the path of a .sql file
DATAOPS_DATABRICKS_CONFIGURATION_DIR REQUIRED—to create catalogs and schemas The directory of catalog and schema YAML. Set Up Databricks sets it to $CI_PROJECT_DIR/dataops/databricks
DATAOPS_DATABRICKS_LIFECYCLE_ACTION Optional. Defaults to AGGREGATE Only AGGREGATE is supported. Any other value fails the job
DATAOPS_DATABRICKS_HOST Optional. When unset, read from vault key DATABRICKS.HOST Workspace hostname without https://, for example dbc-xxxxxxxx-xxxx.cloud.databricks.com
DATAOPS_DATABRICKS_HTTP_PATH Optional. When unset, read from DATABRICKS.CONNECTION.HTTP_PATH The SQL warehouse's HTTP path, /sql/1.0/warehouses/<warehouse-id>
DATAOPS_DATABRICKS_CLIENT_ID Optional. When unset, read from DATABRICKS.CONNECTION.CLIENT_ID The service principal's OAuth client id
DATAOPS_DATABRICKS_CLIENT_SECRET Optional. When unset, read from DATABRICKS.CONNECTION.CLIENT_SECRET The service principal's OAuth secret
DATAOPS_DATABRICKS_SCOPES Optional. Defaults to sql unity-catalog The OAuth scopes requested, separated by spaces. all-apis is not necessary
DATAOPS_DATABRICKS_CATALOG Optional The session's default catalog, for unqualified names
DATAOPS_DATABRICKS_SCHEMA Optional The session's default schema, for unqualified names
DATAOPS_DATABRICKS_FORMAT Optional. Defaults to table The result format: table, csv or json
DATAOPS_DATABRICKS_OUTFILE Optional Also writes the result to this file. Parent directories are created
DATAOPS_DEBUG Optional If set, logs each statement's full text and id

A variable that is set, as a job variable, a DATAOPS_VAULT(...) value or a CI/CD variable, takes precedence over the vault.

dataops-databricks command-line arguments

$ dataops-databricks --help
usage: dataops-databricks [-h] [--host HOST] [--http-path HTTP_PATH] [-c CATALOG]
                          [-s SCHEMA] [-f {table,csv,json}] [-o OUTFILE]
                          [--scopes SCOPES]
                          [sql]

Run SQL against a Databricks SQL warehouse.

positional arguments:
  sql                   path to a .sql file, or a SQL string (default: DATAOPS_RUN_SQL,
                        then stdin)

options:
  -h, --help            show this help message and exit
  --host HOST           workspace hostname, no scheme
  --http-path HTTP_PATH
                        /sql/1.0/warehouses/<id>
  -c CATALOG, --catalog CATALOG
                        session default catalog
  -s SCHEMA, --schema SCHEMA
                        session default schema
  -f {table,csv,json}, --format {table,csv,json}
                        output format (default: table)
  -o OUTFILE, --outfile OUTFILE
                        also write results to this file
  --scopes SCOPES       OAuth scopes to request (default: "sql unity-catalog")

Every flag defaults to the DATAOPS_DATABRICKS_* variable of the same name.

Example jobs

Saving a query result as an artifact

This example lists the tables in the environment's catalog, writes the result to a CSV file and attaches it to the job:

pipelines/includes/local_includes/databricks/list_tables.yml
List Tables:
  extends:
    - .agent_tag
  stage: "Additional Configuration"
  image: $DATAOPS_DATABRICKS_ORCHESTRATOR_IMAGE
  variables:
    DATAOPS_DATABRICKS_CATALOG: $DATAOPS_DATABASE
    DATAOPS_RUN_SQL: SELECT table_schema, table_name FROM information_schema.tables;
    DATAOPS_DATABRICKS_FORMAT: csv
    DATAOPS_DATABRICKS_OUTFILE: $CI_PROJECT_DIR/tables.csv
  script:
    - /dataops
  artifacts:
    paths:
      - tables.csv
  icon: ${DATABRICKS_ICON}

Writing a table to cloud storage as JSON

This example writes a table to an S3 path as newline-delimited JSON with INSERT OVERWRITE DIRECTORY, then lists the files it wrote. The service principal needs access to the path through a Unity Catalog external location; see Cloud storage access.

dataops/sql/export_orders.template.sql
BEGIN
  INSERT OVERWRITE DIRECTORY 's3://<bucket>/exports/orders/run={{ env.CI_PIPELINE_ID }}/'
    USING JSON
    OPTIONS ('ignoreNullFields' = 'false')
    SELECT * FROM `{{ env.DATAOPS_DATABASE }}`.sales.orders;
  SELECT DISTINCT _metadata.file_path AS file_path, _metadata.file_size AS file_size
  FROM json.`s3://<bucket>/exports/orders/run={{ env.CI_PIPELINE_ID }}/`;
END
  • Set ignoreNullFields to false. By default the JSON writer silently drops every key whose value is null.
  • Write each run to its own path. INSERT OVERWRITE DIRECTORY deletes everything at its path before writing.

The job saves the list of files as an artifact. If your project's stages.yml has no Publish Data stage, add one after Transformation Testing, before Generate Docs.

pipelines/includes/local_includes/databricks/export_orders.yml
Export Orders:
  extends:
    - .agent_tag
  stage: "Publish Data"
  image: $DATAOPS_DATABRICKS_ORCHESTRATOR_IMAGE
  variables:
    DATAOPS_RUN_SQL: $CI_PROJECT_DIR/dataops/sql/export_orders.sql
    DATAOPS_DATABRICKS_FORMAT: csv
    DATAOPS_DATABRICKS_OUTFILE: $CI_PROJECT_DIR/export_manifest.csv
  script:
    - /dataops
  artifacts:
    when: always
    paths:
      - export_manifest.csv
  icon: ${DATABRICKS_ICON}

Loading JSON files from cloud storage

This example loads the JSON files under an S3 path into a table with read_files, then counts the records from each file. The service principal needs READ FILES on the path's external location; see Cloud storage access.

dataops/sql/load_orders.template.sql
BEGIN
  CREATE OR REPLACE TABLE `{{ env.DATAOPS_DATABASE }}`.raw.orders AS
    SELECT *, _metadata.file_path AS source_file
    FROM read_files('s3://<bucket>/imports/orders/',
      format => 'json',
      mode => 'FAILFAST');
  SELECT source_file, count(*) AS records
  FROM `{{ env.DATAOPS_DATABASE }}`.raw.orders
  GROUP BY source_file
  ORDER BY source_file;
END
  • Set mode to FAILFAST. By default a malformed record becomes a row of nulls and the statement succeeds. With FAILFAST it fails with MALFORMED_RECORD_IN_PARSING, and CREATE OR REPLACE leaves the table as it was.
  • Each line is one record, so with FAILFAST an object written over several lines fails the statement too. For such files, add multiLine => true and wrap the file's objects in one JSON array. Otherwise, Databricks silently skips values depending on the file extension and the format of the contents.

Run it from a job like Export Orders above, with DATAOPS_RUN_SQL: $CI_PROJECT_DIR/dataops/sql/load_orders.sql, in the Batch Ingestion stage. If your project's stages.yml has no Batch Ingestion stage, add one before Data Ingestion.

Dropping a feature branch's catalog

Only the catalog's owner (the service principal) or a user with MANAGE can drop it, so drop a finished feature branch's catalog from a job on that branch:

pipelines/includes/local_includes/databricks/drop_catalog.yml
Drop Feature Branch Catalog:
  extends:
    - .agent_tag
  stage: "Clean Up"
  image: $DATAOPS_DATABRICKS_ORCHESTRATOR_IMAGE
  variables:
    DATAOPS_RUN_SQL: DROP CATALOG IF EXISTS `${DATAOPS_DATABASE}` CASCADE;
  script:
    - '[[ $DATAOPS_ENV_NAME == FB_* ]] || { echo "Not a feature branch"; exit 1; }'
    - /dataops
  icon: ${DATABRICKS_ICON}
  rules:
    - if: $CI_COMMIT_BRANCH == $CI_DEFAULT_BRANCH
      when: never
    - when: manual

The job has two guards. Its first script line fails the job unless DATAOPS_ENV_NAME starts with FB_, the prefix feature branches get. Its rule also keeps the job out of the default branch's pipelines, since that branch is treated as a feature branch when DATAOPS_BRANCH_NAME_PROD doesn't name it.