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:
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:
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 ;:
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:
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
catalogManagedtable feature. Create it withTBLPROPERTIES ('delta.feature.catalogManaged' = 'supported'), or add the feature to an existing table withALTER TABLE ... SET TBLPROPERTIES. Without it, the block fails withTRANSACTION_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 asIF. 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 aresource_group, for exampleadd-orders-$CI_COMMIT_REF_SLUGfor 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:
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
RAWandraware 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:
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.
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
ignoreNullFieldstofalse. By default the JSON writer silently drops every key whose value is null. - Write each run to its own path.
INSERT OVERWRITE DIRECTORYdeletes 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.
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.
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
modetoFAILFAST. By default a malformed record becomes a row of nulls and the statement succeeds. WithFAILFASTit fails withMALFORMED_RECORD_IN_PARSING, andCREATE OR REPLACEleaves the table as it was. - Each line is one record, so with
FAILFASTan object written over several lines fails the statement too. For such files, addmultiLine => trueand 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:
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.