Repository: tobymao/sqlglot

Issue:
Extend support CREATE TABLE statement for clickhouse
In clickhouse, I cannot parse CREATE TABLE statements when there are:

* [ALIAS](https://clickhouse.com/docs/en/sql-reference/statements/create/table#alias)
* [Column Compression Codecs](https://clickhouse.com/docs/en/sql-reference/statements/create/table#column-compression-codecs)
* INDEXes - Although this is the most important for me, I am unable to find the relevant specification. Only source code [1](https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646), [2](https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.cpp#L175)

Examples of valid CREATE TABLE statements:

```
CREATE TABLE x
(
    `a` String,
    `b` ALIAS a
)
ENGINE = MergeTree
ORDER BY tuple()
```

```
CREATE TABLE y
(
    `a` Float64 CODEC(LZ4HC(9)),
    `b` Float32 CODEC(Delta, ZSTD),
    `c` Float32 CODEC(NONE)
)
ENGINE = MergeTree
ORDER BY tuple()
```


```
CREATE TABLE z
(
    `a` String,
    INDEX a_idx1 a TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX a_idx2 a TYPE set(100) GRANULARITY 2,
    INDEX a_idx3 a TYPE minmax GRANULARITY 2
)
ENGINE = MergeTree
ORDER BY tuple()
```

Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 5c953188..874ce901 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -261,6 +261,11 @@ class ClickHouse(Dialect):
             "ArgMax",
         ]
 
+        FUNC_TOKENS = {
+            *parser.Parser.FUNC_TOKENS,
+            TokenType.SET,
+        }
+
         AGG_FUNC_MAPPING = (
             lambda functions, suffixes: {
                 f"{f}{sfx}": (f, sfx) for sfx in (suffixes + [""]) for f in functions
@@ -321,6 +326,17 @@ class ClickHouse(Dialect):
             TokenType.FORMAT: lambda self: ("format", self._advance() or self._parse_id_var()),
         }
 
+        CONSTRAINT_PARSERS = {
+            **parser.Parser.CONSTRAINT_PARSERS,
+            "INDEX": lambda self: self._parse_index_constraint(),
+            "CODEC": lambda self: self._parse_compress(),
+        }
+
+        SCHEMA_UNNAMED_CONSTRAINTS = {
+            *parser.Parser.SCHEMA_UNNAMED_CONSTRAINTS,
+            "INDEX",
+        }
+
         def _parse_conjunction(self) -> t.Optional[exp.Expression]:
             this = super()._parse_conjunction()
 
@@ -512,6 +528,27 @@ class ClickHouse(Dialect):
                     self._retreat(index)
             return None
 
+        def _parse_index_constraint(
+            self, kind: t.Optional[str] = None
+        ) -> exp.IndexColumnConstraint:
+            # INDEX name1 expr TYPE type1(args) GRANULARITY value
+            this = self._parse_id_var()
+            expression = self._parse_conjunction()
+
+            index_type = self._match_text_seq("TYPE") and (
+                self._parse_function() or self._parse_var()
+            )
+
+            granularity = self._match_text_seq("GRANULARITY") and self._parse_term()
+
+            return self.expression(
+                exp.IndexColumnConstraint,
+                this=this,
+                expression=expression,
+                index_type=index_type,
+                granularity=granularity,
+            )
+
     class Generator(generator.Generator):
         QUERY_HINTS = False
         STRUCT_DELIMITER = ("(", ")")
@@ -590,6 +627,9 @@ class ClickHouse(Dialect):
             exp.Array: inline_array_sql,
             exp.CastToStrType: rename_func("CAST"),
             exp.CountIf: rename_func("countIf"),
+            exp.CompressColumnConstraint: lambda self,
+            e: f"CODEC({self.expressions(e, key='this', flat=True)})",
+            exp.ComputedColumnConstraint: lambda self, e: f"ALIAS {self.sql(e, 'this')}",
             exp.CurrentDate: lambda self, e: self.func("CURRENT_DATE"),
             exp.DateAdd: date_delta_sql("DATE_ADD"),
             exp.DateDiff: date_delta_sql("DATE_DIFF"),
@@ -742,3 +782,15 @@ class ClickHouse(Dialect):
         def prewhere_sql(self, expression: exp.PreWhere) -> str:
             this = self.indent(self.sql(expression, "this"))
             return f"{self.seg('PREWHERE')}{self.sep()}{this}"
+
+        def indexcolumnconstraint_sql(self, expression: exp.IndexColumnConstraint) -> str:
+            this = self.sql(expression, "this")
+            this = f" {this}" if this else ""
+            expr = self.sql(expression, "expression")
+            expr = f" {expr}" if expr else ""
+            index_type = self.sql(expression, "index_type")
+            index_type = f" TYPE {index_type}" if index_type else ""
+            granularity = self.sql(expression, "granularity")
+            granularity = f" GRANULARITY {granularity}" if granularity else ""
+
+            return f"INDEX{this}{expr}{index_type}{granularity}"
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index aef2a759..66302fc4 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -480,9 +480,6 @@ class MySQL(Dialect):
                 elif self._match_text_seq("ENGINE_ATTRIBUTE"):
                     self._match(TokenType.EQ)
                     opt = exp.IndexConstraintOption(engine_attr=self._parse_string())
-                elif self._match_text_seq("ENGINE_ATTRIBUTE"):
-                    self._match(TokenType.EQ)
-                    opt = exp.IndexConstraintOption(engine_attr=self._parse_string())
                 elif self._match_text_seq("SECONDARY_ENGINE_ATTRIBUTE"):
                     self._match(TokenType.EQ)
                     opt = exp.IndexConstraintOption(secondary_engine_attr=self._parse_string())
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 22f92b75..9f72c290 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1665,13 +1665,16 @@ class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
 
 
 # https://dev.mysql.com/doc/refman/8.0/en/create-table.html
+# https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646
 class IndexColumnConstraint(ColumnConstraintKind):
     arg_types = {
         "this": False,
-        "schema": True,
+        "schema": False,
         "kind": False,
         "index_type": False,
         "options": False,
+        "expression": False,  # Clickhouse
+        "granularity": False,
     }
 
 
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index a2fcf3d5..474ba050 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -4504,7 +4504,7 @@ class Parser(metaclass=_Parser):
 
         constraints: t.List[exp.Expression] = []
 
-        if not kind and self._match(TokenType.ALIAS):
+        if (not kind and self._match(TokenType.ALIAS)) or self._match_text_seq("ALIAS"):
             constraints.append(
                 self.expression(
                     exp.ComputedColumnConstraint,


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index f7165846..c5f9847c 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -154,7 +154,9 @@ class TestClickhouse(Validator):
         self.validate_identity("TRUNCATE TABLE t1 ON CLUSTER test_cluster")
         self.validate_identity("TRUNCATE DATABASE db")
         self.validate_identity("TRUNCATE DATABASE db ON CLUSTER test_cluster")
-
+        self.validate_identity(
+            "CREATE TABLE t (foo String CODEC(LZ4HC(9), ZSTD, DELTA), size String ALIAS formatReadableSize(size_bytes), INDEX idx1 a TYPE bloom_filter(0.001) GRANULARITY 1, INDEX idx2 a TYPE set(100) GRANULARITY 2, INDEX idx3 a TYPE minmax GRANULARITY 3)"
+        )
         self.validate_all(
             "SELECT arrayJoin([1,2,3])",
             write={