Export: SQL

Converts a data contract into a SQL data definition language (DDL) file.

datacontract export sql orders.odcs.yaml --output orders.sql

Running this against the example orders contract produces:

-- Data Contract: urn:datacontract:checkout:orders
-- SQL Dialect: snowflake
CREATE TABLE orders (
  order_id VARCHAR not null primary key COMMENT 'Unique identifier of the order.',
  order_timestamp TIMESTAMP_TZ not null COMMENT 'Timestamp when the order was placed.',
  customer_id VARCHAR not null COMMENT 'Reference to the customer who placed the order.',
  order_total NUMBER not null COMMENT 'Total amount of the order in cents.',
  status VARCHAR not null COMMENT 'Current fulfilment status of the order.'
) COMMENT='One row per customer order.';
CREATE TABLE line_items (
  line_item_id VARCHAR not null primary key COMMENT 'Unique identifier of the line item.',
  order_id VARCHAR not null COMMENT 'Reference to the parent order.',
  sku VARCHAR not null COMMENT 'Stock keeping unit of the purchased product.',
  quantity NUMBER not null COMMENT 'Number of units purchased.'
) COMMENT='One row per line item within an order.';

The SQL dialect is determined from the servers block in the data contract (e.g. type: postgres, type: snowflake). Alternatively, pass it explicitly:

datacontract export sql datacontract.yaml --dialect postgres --output output.sql

Supported dialects: postgres, mysql, snowflake, databricks, sqlserver, trino, oracle, clickhouse (and auto, the default, which detects the dialect from the server type).

Databricks variant columns

If an error is thrown when deploying SQL DDLs with variant columns on Databricks, set:

spark.conf.set("spark.databricks.delta.schema.typeCheck.enabled", "false")

ClickHouse

datacontract export sql datacontract.yaml --dialect clickhouse --output ddl.sql

ClickHouse requires a table engine (default ENGINE = MergeTree()) and uses ORDER BY as its primary key mechanism. Primary key fields in the contract are translated to the ORDER BY clause.

# Custom engine
datacontract export sql datacontract.yaml --dialect clickhouse \
  --clickhouse-engine "ReplicatedMergeTree('/clickhouse/tables/{shard}/table', '{replica}')"

# Custom ORDER BY (defaults to primary key columns if defined)
datacontract export sql datacontract.yaml --dialect clickhouse \
  --clickhouse-order-by "event_date DESC, event_id"

Option	Description
`--clickhouse-engine`	The table engine. Default: `MergeTree()`.
`--clickhouse-order-by`	Comma-separated `ORDER BY` columns. Defaults to primary key columns.

Override any field's ClickHouse type individually by setting the custom property clickhouseType in the data contract.

ClickHouse​

ClickHouse