Skip to main content

Using SQL Expressions in ClickHouse HCL Schema

While Atlas supports defining engines as simple enums in HCL (like MergeTree or SharedMergeTree), many ClickHouse features require more complex configurations. Atlas provides the sql() function to handle these advanced scenarios.

Database & Table Engines with Parameters​

For engines that require parameters, you can use SQL expressions:

schema "default" {
  engine = sql("Replicated('/clickhouse/schemas/{cluster}', '{replica}')")
}

table "distributed_users" {
  schema = schema.default
  engine = sql("Distributed('{cluster}', 'my_database', 'users')")
  column "id" {
    type = UInt64
  }
  column "name" {
    type = String
  }
}

table "replicated_users" {
  schema = schema.default
  engine = sql("ReplicatedMergeTree('/clickhouse/tables/{cluster}/users', '{replica}')")
  column "id" {
    type = UInt64
  }
}

Other Attributes Supporting SQL Expressions​

Beyond engines, several other attributes support sql() expressions:

Column Types and Defaults​

table "advanced_example" {
  schema = schema.default
  engine = MergeTree
  
  column "data" {
    type = sql("Nested(name String, value UInt32)")  # Complex nested type
  }
  column "created_at" {
    type = DateTime
    default = sql("now()")  # Function-based default
  }
  column "computed_field" {
    type = String
    default = sql("concat('prefix_', toString(id))")
    default_kind = MATERIALIZED
  }
}

TTL Expressions​

table "time_series_data" {
  schema = schema.default
  engine = MergeTree
  ttl = sql("created_at + INTERVAL 30 DAY")  # Table-level TTL
  
  column "metadata" {
    type = String
    ttl = sql("timestamp + INTERVAL 7 DAY")  # Column-level TTL
  }
}

Use sql() expressions when you need complex ClickHouse configurations that go beyond basic enums and types.

Additional Resources​