# Export: SQL DDL

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

#  Export: SQL

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

{/* AUTOGENERATED EXAMPLE: do not edit by hand; regenerate with the update script. */}

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

Running this against the [example `orders` contract](https://github.com/datacontract/datacontract-cli/blob/main/examples/orders/orders.odcs.yaml) produces:

```sql
-- 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.';
```

{/* END AUTOGENERATED EXAMPLE */}

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

```bash
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).

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

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

## ClickHouse

```bash
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.

```bash
# 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.

All options: **[`datacontract export sql`](../commands/export/sql.md)**.