Table Schema Definition Generation

Table schema

To be able to use the DSL, one has to set up a scala representation of the tables that are available to be used with the DSL.

The DSL supports all possibilities that clickhouse has to offer as well as the generation of "CREATE" statements from your table definitions.

Basic setup

package my.reporting.schema

import java.util.UUID

import com.crobox.clickhouse.dsl.schemabuilder._
import com.crobox.clickhouse.dsl._

object ActionsTable extends Table  {
    //Table name 
    override val name: String = "action"

    //Table columns
    val actionId              = NativeColumn[UUID]("action_id")
    val actionType            = NativeColumn[String]("action_type")
    val subActions            = NativeColumn[Seq[String]]("child_ids", ColumnType.Array(ColumnType.String))

    //Its important to have a timestamp, to be used as a partitioning key
    val timeStamp             = NativeColumn[Long]("time_stamp", ColumnType.Long)
    val date                  = NativeColumn[LocalDate]("ts_date", ColumnType.Date, DefaultValue.Materialized(fromMillisToDate(timeStamp)))
    
    

    override val columns: List[NativeColumn[_]] =  List(actionId,
                                                        actionType,
                                                        subActions,
                                                        timeStamp, 
                                                        date)
}

Generating CREATE STATEMENTS

For validating your schema, initialising your database and also integration testing purposes you might want to generate a database schema on the fly.

The clickhouse DSL offers a "CreateTable" keyword that expands your table schema definition into a create statement. All you have to do is add the table engine and optional extra properties.

This is currently an alternative path (note the .toString() at the end) and will be integrated into the clickhouse dsl tokenizer in the future.

import my.reporting.schema._

clickhouseClient.execute(
    CreateTable(ActionsTable,
        MergeTree(ActionsTable.timeStamp, ActionsTable.actionId),
                  ifNotExists = true, // CREATE IF NOT EXIST
                  databaseName = clickhouseClient.database).toString())
)