Skip to content

Support ClickHouse CREATE VIEW #1277

Closed
Closed
@bombsimon

Description

@bombsimon

The CREATE VIEW syntax for ClickHouse is a bit different from many warehouses in the sense that you can specify the type of the columns when creating the view instead of them being deferred. This makes parse_view_columns fail since it's very different from parse_columns that supports the types (ref.). I noticed that by just changing CREATE VIEW to CREATE TABLE the examples below work so hopefully there should be enough pieces to reuse.

ClickHouse also support the Atomic engine which will have persistent UUID and store data in a directory with that name. Ref.. This UUID can be specified manually and I noticed it is used by e.g. dbt.

Example query without UUID:

CREATE VIEW demo.stg_customers (
  `customer_id` Int32,
  `first_name` String,
  `last_name` String
) AS
WITH
  source AS (
    SELECT
      *
    FROM
      demo.raw_customers
  ),
  renamed AS (
    SELECT
      id AS customer_id,
      first_name,
      last_name
    FROM
      source
  )
SELECT
  *
FROM
  renamed

Example query using UUID:

CREATE VIEW demo.stg_payments UUID 'e2c22490-54e1-49da-b2d7-603efbe72ae4' (
  `payment_id` Int32,
  `order_id` Int32,
  `payment_method` String,
  `amount` Float64
) AS
WITH
  source AS (
    SELECT
      *
    FROM
      demo.raw_payments
  ),
  renamed AS (
    SELECT
      id AS payment_id,
      order_id,
      payment_method,
      amount / 100 AS amount
    FROM
      source
  )
SELECT
  *
FROM
  renamed

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions