sqlglot.dialects.clickhouse

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, parser, tokens
  6from sqlglot.dialects.dialect import (
  7    Dialect,
  8    inline_array_sql,
  9    no_pivot_sql,
 10    rename_func,
 11    var_map_sql,
 12)
 13from sqlglot.errors import ParseError
 14from sqlglot.helper import seq_get
 15from sqlglot.parser import parse_var_map
 16from sqlglot.tokens import Token, TokenType
 17
 18
 19def _lower_func(sql: str) -> str:
 20    index = sql.index("(")
 21    return sql[:index].lower() + sql[index:]
 22
 23
 24class ClickHouse(Dialect):
 25    NORMALIZE_FUNCTIONS: bool | str = False
 26    NULL_ORDERING = "nulls_are_last"
 27    STRICT_STRING_CONCAT = True
 28
 29    class Tokenizer(tokens.Tokenizer):
 30        COMMENTS = ["--", "#", "#!", ("/*", "*/")]
 31        IDENTIFIERS = ['"', "`"]
 32        STRING_ESCAPES = ["'", "\\"]
 33        BIT_STRINGS = [("0b", "")]
 34        HEX_STRINGS = [("0x", ""), ("0X", "")]
 35
 36        KEYWORDS = {
 37            **tokens.Tokenizer.KEYWORDS,
 38            "ATTACH": TokenType.COMMAND,
 39            "DATETIME64": TokenType.DATETIME64,
 40            "DICTIONARY": TokenType.DICTIONARY,
 41            "ENUM": TokenType.ENUM,
 42            "ENUM8": TokenType.ENUM8,
 43            "ENUM16": TokenType.ENUM16,
 44            "FINAL": TokenType.FINAL,
 45            "FIXEDSTRING": TokenType.FIXEDSTRING,
 46            "FLOAT32": TokenType.FLOAT,
 47            "FLOAT64": TokenType.DOUBLE,
 48            "GLOBAL": TokenType.GLOBAL,
 49            "INT16": TokenType.SMALLINT,
 50            "INT256": TokenType.INT256,
 51            "INT32": TokenType.INT,
 52            "INT64": TokenType.BIGINT,
 53            "INT8": TokenType.TINYINT,
 54            "LOWCARDINALITY": TokenType.LOWCARDINALITY,
 55            "MAP": TokenType.MAP,
 56            "NESTED": TokenType.NESTED,
 57            "TUPLE": TokenType.STRUCT,
 58            "UINT128": TokenType.UINT128,
 59            "UINT16": TokenType.USMALLINT,
 60            "UINT256": TokenType.UINT256,
 61            "UINT32": TokenType.UINT,
 62            "UINT64": TokenType.UBIGINT,
 63            "UINT8": TokenType.UTINYINT,
 64        }
 65
 66    class Parser(parser.Parser):
 67        SUPPORTS_USER_DEFINED_TYPES = False
 68
 69        FUNCTIONS = {
 70            **parser.Parser.FUNCTIONS,
 71            "ANY": exp.AnyValue.from_arg_list,
 72            "DATE_ADD": lambda args: exp.DateAdd(
 73                this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0)
 74            ),
 75            "DATEADD": lambda args: exp.DateAdd(
 76                this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0)
 77            ),
 78            "DATE_DIFF": lambda args: exp.DateDiff(
 79                this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0)
 80            ),
 81            "DATEDIFF": lambda args: exp.DateDiff(
 82                this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0)
 83            ),
 84            "MAP": parse_var_map,
 85            "MATCH": exp.RegexpLike.from_arg_list,
 86            "UNIQ": exp.ApproxDistinct.from_arg_list,
 87            "XOR": lambda args: exp.Xor(expressions=args),
 88        }
 89
 90        FUNCTIONS_WITH_ALIASED_ARGS = {*parser.Parser.FUNCTIONS_WITH_ALIASED_ARGS, "TUPLE"}
 91
 92        FUNCTION_PARSERS = {
 93            **parser.Parser.FUNCTION_PARSERS,
 94            "QUANTILE": lambda self: self._parse_quantile(),
 95        }
 96
 97        FUNCTION_PARSERS.pop("MATCH")
 98
 99        NO_PAREN_FUNCTION_PARSERS = parser.Parser.NO_PAREN_FUNCTION_PARSERS.copy()
100        NO_PAREN_FUNCTION_PARSERS.pop("ANY")
101
102        RANGE_PARSERS = {
103            **parser.Parser.RANGE_PARSERS,
104            TokenType.GLOBAL: lambda self, this: self._match(TokenType.IN)
105            and self._parse_in(this, is_global=True),
106        }
107
108        # The PLACEHOLDER entry is popped because 1) it doesn't affect Clickhouse (it corresponds to
109        # the postgres-specific JSONBContains parser) and 2) it makes parsing the ternary op simpler.
110        COLUMN_OPERATORS = parser.Parser.COLUMN_OPERATORS.copy()
111        COLUMN_OPERATORS.pop(TokenType.PLACEHOLDER)
112
113        JOIN_KINDS = {
114            *parser.Parser.JOIN_KINDS,
115            TokenType.ANY,
116            TokenType.ASOF,
117            TokenType.ANTI,
118            TokenType.SEMI,
119            TokenType.ARRAY,
120        }
121
122        TABLE_ALIAS_TOKENS = {*parser.Parser.TABLE_ALIAS_TOKENS} - {
123            TokenType.ANY,
124            TokenType.SEMI,
125            TokenType.ANTI,
126            TokenType.SETTINGS,
127            TokenType.FORMAT,
128            TokenType.ARRAY,
129        }
130
131        LOG_DEFAULTS_TO_LN = True
132
133        QUERY_MODIFIER_PARSERS = {
134            **parser.Parser.QUERY_MODIFIER_PARSERS,
135            TokenType.SETTINGS: lambda self: (
136                "settings",
137                self._advance() or self._parse_csv(self._parse_conjunction),
138            ),
139            TokenType.FORMAT: lambda self: ("format", self._advance() or self._parse_id_var()),
140        }
141
142        def _parse_conjunction(self) -> t.Optional[exp.Expression]:
143            this = super()._parse_conjunction()
144
145            if self._match(TokenType.PLACEHOLDER):
146                return self.expression(
147                    exp.If,
148                    this=this,
149                    true=self._parse_conjunction(),
150                    false=self._match(TokenType.COLON) and self._parse_conjunction(),
151                )
152
153            return this
154
155        def _parse_placeholder(self) -> t.Optional[exp.Expression]:
156            """
157            Parse a placeholder expression like SELECT {abc: UInt32} or FROM {table: Identifier}
158            https://clickhouse.com/docs/en/sql-reference/syntax#defining-and-using-query-parameters
159            """
160            if not self._match(TokenType.L_BRACE):
161                return None
162
163            this = self._parse_id_var()
164            self._match(TokenType.COLON)
165            kind = self._parse_types(check_func=False, allow_identifiers=False) or (
166                self._match_text_seq("IDENTIFIER") and "Identifier"
167            )
168
169            if not kind:
170                self.raise_error("Expecting a placeholder type or 'Identifier' for tables")
171            elif not self._match(TokenType.R_BRACE):
172                self.raise_error("Expecting }")
173
174            return self.expression(exp.Placeholder, this=this, kind=kind)
175
176        def _parse_in(self, this: t.Optional[exp.Expression], is_global: bool = False) -> exp.In:
177            this = super()._parse_in(this)
178            this.set("is_global", is_global)
179            return this
180
181        def _parse_table(
182            self,
183            schema: bool = False,
184            joins: bool = False,
185            alias_tokens: t.Optional[t.Collection[TokenType]] = None,
186            parse_bracket: bool = False,
187        ) -> t.Optional[exp.Expression]:
188            this = super()._parse_table(
189                schema=schema, joins=joins, alias_tokens=alias_tokens, parse_bracket=parse_bracket
190            )
191
192            if self._match(TokenType.FINAL):
193                this = self.expression(exp.Final, this=this)
194
195            return this
196
197        def _parse_position(self, haystack_first: bool = False) -> exp.StrPosition:
198            return super()._parse_position(haystack_first=True)
199
200        # https://clickhouse.com/docs/en/sql-reference/statements/select/with/
201        def _parse_cte(self) -> exp.CTE:
202            index = self._index
203            try:
204                # WITH <identifier> AS <subquery expression>
205                return super()._parse_cte()
206            except ParseError:
207                # WITH <expression> AS <identifier>
208                self._retreat(index)
209                statement = self._parse_statement()
210
211                if statement and isinstance(statement.this, exp.Alias):
212                    self.raise_error("Expected CTE to have alias")
213
214                return self.expression(exp.CTE, this=statement, alias=statement and statement.this)
215
216        def _parse_join_parts(
217            self,
218        ) -> t.Tuple[t.Optional[Token], t.Optional[Token], t.Optional[Token]]:
219            is_global = self._match(TokenType.GLOBAL) and self._prev
220            kind_pre = self._match_set(self.JOIN_KINDS, advance=False) and self._prev
221
222            if kind_pre:
223                kind = self._match_set(self.JOIN_KINDS) and self._prev
224                side = self._match_set(self.JOIN_SIDES) and self._prev
225                return is_global, side, kind
226
227            return (
228                is_global,
229                self._match_set(self.JOIN_SIDES) and self._prev,
230                self._match_set(self.JOIN_KINDS) and self._prev,
231            )
232
233        def _parse_join(
234            self, skip_join_token: bool = False, parse_bracket: bool = False
235        ) -> t.Optional[exp.Join]:
236            join = super()._parse_join(skip_join_token=skip_join_token, parse_bracket=True)
237
238            if join:
239                join.set("global", join.args.pop("method", None))
240            return join
241
242        def _parse_function(
243            self,
244            functions: t.Optional[t.Dict[str, t.Callable]] = None,
245            anonymous: bool = False,
246            optional_parens: bool = True,
247        ) -> t.Optional[exp.Expression]:
248            func = super()._parse_function(
249                functions=functions, anonymous=anonymous, optional_parens=optional_parens
250            )
251
252            if isinstance(func, exp.Anonymous):
253                params = self._parse_func_params(func)
254
255                if params:
256                    return self.expression(
257                        exp.ParameterizedAgg,
258                        this=func.this,
259                        expressions=func.expressions,
260                        params=params,
261                    )
262
263            return func
264
265        def _parse_func_params(
266            self, this: t.Optional[exp.Func] = None
267        ) -> t.Optional[t.List[exp.Expression]]:
268            if self._match_pair(TokenType.R_PAREN, TokenType.L_PAREN):
269                return self._parse_csv(self._parse_lambda)
270
271            if self._match(TokenType.L_PAREN):
272                params = self._parse_csv(self._parse_lambda)
273                self._match_r_paren(this)
274                return params
275
276            return None
277
278        def _parse_quantile(self) -> exp.Quantile:
279            this = self._parse_lambda()
280            params = self._parse_func_params()
281            if params:
282                return self.expression(exp.Quantile, this=params[0], quantile=this)
283            return self.expression(exp.Quantile, this=this, quantile=exp.Literal.number(0.5))
284
285        def _parse_wrapped_id_vars(self, optional: bool = False) -> t.List[exp.Expression]:
286            return super()._parse_wrapped_id_vars(optional=True)
287
288        def _parse_primary_key(
289            self, wrapped_optional: bool = False, in_props: bool = False
290        ) -> exp.PrimaryKeyColumnConstraint | exp.PrimaryKey:
291            return super()._parse_primary_key(
292                wrapped_optional=wrapped_optional or in_props, in_props=in_props
293            )
294
295        def _parse_on_property(self) -> t.Optional[exp.Expression]:
296            index = self._index
297            if self._match_text_seq("CLUSTER"):
298                this = self._parse_id_var()
299                if this:
300                    return self.expression(exp.OnCluster, this=this)
301                else:
302                    self._retreat(index)
303            return None
304
305    class Generator(generator.Generator):
306        QUERY_HINTS = False
307        STRUCT_DELIMITER = ("(", ")")
308        NVL2_SUPPORTED = False
309
310        STRING_TYPE_MAPPING = {
311            exp.DataType.Type.CHAR: "String",
312            exp.DataType.Type.LONGBLOB: "String",
313            exp.DataType.Type.LONGTEXT: "String",
314            exp.DataType.Type.MEDIUMBLOB: "String",
315            exp.DataType.Type.MEDIUMTEXT: "String",
316            exp.DataType.Type.TEXT: "String",
317            exp.DataType.Type.VARBINARY: "String",
318            exp.DataType.Type.VARCHAR: "String",
319        }
320
321        TYPE_MAPPING = {
322            **generator.Generator.TYPE_MAPPING,
323            **STRING_TYPE_MAPPING,
324            exp.DataType.Type.ARRAY: "Array",
325            exp.DataType.Type.BIGINT: "Int64",
326            exp.DataType.Type.DATETIME64: "DateTime64",
327            exp.DataType.Type.DOUBLE: "Float64",
328            exp.DataType.Type.ENUM: "Enum",
329            exp.DataType.Type.ENUM8: "Enum8",
330            exp.DataType.Type.ENUM16: "Enum16",
331            exp.DataType.Type.FIXEDSTRING: "FixedString",
332            exp.DataType.Type.FLOAT: "Float32",
333            exp.DataType.Type.INT: "Int32",
334            exp.DataType.Type.INT128: "Int128",
335            exp.DataType.Type.INT256: "Int256",
336            exp.DataType.Type.LOWCARDINALITY: "LowCardinality",
337            exp.DataType.Type.MAP: "Map",
338            exp.DataType.Type.NESTED: "Nested",
339            exp.DataType.Type.NULLABLE: "Nullable",
340            exp.DataType.Type.SMALLINT: "Int16",
341            exp.DataType.Type.STRUCT: "Tuple",
342            exp.DataType.Type.TINYINT: "Int8",
343            exp.DataType.Type.UBIGINT: "UInt64",
344            exp.DataType.Type.UINT: "UInt32",
345            exp.DataType.Type.UINT128: "UInt128",
346            exp.DataType.Type.UINT256: "UInt256",
347            exp.DataType.Type.USMALLINT: "UInt16",
348            exp.DataType.Type.UTINYINT: "UInt8",
349        }
350
351        TRANSFORMS = {
352            **generator.Generator.TRANSFORMS,
353            exp.AnyValue: rename_func("any"),
354            exp.ApproxDistinct: rename_func("uniq"),
355            exp.Array: inline_array_sql,
356            exp.CastToStrType: rename_func("CAST"),
357            exp.DateAdd: lambda self, e: self.func(
358                "DATE_ADD", exp.Literal.string(e.text("unit") or "day"), e.expression, e.this
359            ),
360            exp.DateDiff: lambda self, e: self.func(
361                "DATE_DIFF", exp.Literal.string(e.text("unit") or "day"), e.expression, e.this
362            ),
363            exp.Final: lambda self, e: f"{self.sql(e, 'this')} FINAL",
364            exp.Map: lambda self, e: _lower_func(var_map_sql(self, e)),
365            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
366            exp.Pivot: no_pivot_sql,
367            exp.Quantile: lambda self, e: self.func("quantile", e.args.get("quantile"))
368            + f"({self.sql(e, 'this')})",
369            exp.RegexpLike: lambda self, e: f"match({self.format_args(e.this, e.expression)})",
370            exp.StrPosition: lambda self, e: f"position({self.format_args(e.this, e.args.get('substr'), e.args.get('position'))})",
371            exp.VarMap: lambda self, e: _lower_func(var_map_sql(self, e)),
372            exp.Xor: lambda self, e: self.func("xor", e.this, e.expression, *e.expressions),
373        }
374
375        PROPERTIES_LOCATION = {
376            **generator.Generator.PROPERTIES_LOCATION,
377            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
378            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
379            exp.OnCluster: exp.Properties.Location.POST_NAME,
380        }
381
382        JOIN_HINTS = False
383        TABLE_HINTS = False
384        EXPLICIT_UNION = True
385        GROUPINGS_SEP = ""
386
387        # there's no list in docs, but it can be found in Clickhouse code
388        # see `ClickHouse/src/Parsers/ParserCreate*.cpp`
389        ON_CLUSTER_TARGETS = {
390            "DATABASE",
391            "TABLE",
392            "VIEW",
393            "DICTIONARY",
394            "INDEX",
395            "FUNCTION",
396            "NAMED COLLECTION",
397        }
398
399        def datatype_sql(self, expression: exp.DataType) -> str:
400            # String is the standard ClickHouse type, every other variant is just an alias.
401            # Additionally, any supplied length parameter will be ignored.
402            #
403            # https://clickhouse.com/docs/en/sql-reference/data-types/string
404            if expression.this in self.STRING_TYPE_MAPPING:
405                return "String"
406
407            return super().datatype_sql(expression)
408
409        def safeconcat_sql(self, expression: exp.SafeConcat) -> str:
410            # Clickhouse errors out if we try to cast a NULL value to TEXT
411            expression = expression.copy()
412            return self.func(
413                "CONCAT",
414                *[
415                    exp.func("if", e.is_(exp.null()), e, exp.cast(e, "text"))
416                    for e in t.cast(t.List[exp.Condition], expression.expressions)
417                ],
418            )
419
420        def cte_sql(self, expression: exp.CTE) -> str:
421            if isinstance(expression.this, exp.Alias):
422                return self.sql(expression, "this")
423
424            return super().cte_sql(expression)
425
426        def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
427            return super().after_limit_modifiers(expression) + [
428                self.seg("SETTINGS ") + self.expressions(expression, key="settings", flat=True)
429                if expression.args.get("settings")
430                else "",
431                self.seg("FORMAT ") + self.sql(expression, "format")
432                if expression.args.get("format")
433                else "",
434            ]
435
436        def parameterizedagg_sql(self, expression: exp.Anonymous) -> str:
437            params = self.expressions(expression, key="params", flat=True)
438            return self.func(expression.name, *expression.expressions) + f"({params})"
439
440        def placeholder_sql(self, expression: exp.Placeholder) -> str:
441            return f"{{{expression.name}: {self.sql(expression, 'kind')}}}"
442
443        def oncluster_sql(self, expression: exp.OnCluster) -> str:
444            return f"ON CLUSTER {self.sql(expression, 'this')}"
445
446        def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str:
447            kind = self.sql(expression, "kind").upper()
448            if kind in self.ON_CLUSTER_TARGETS and locations.get(exp.Properties.Location.POST_NAME):
449                this_name = self.sql(expression.this, "this")
450                this_properties = " ".join(
451                    [self.sql(prop) for prop in locations[exp.Properties.Location.POST_NAME]]
452                )
453                this_schema = self.schema_columns_sql(expression.this)
454                return f"{this_name}{self.sep()}{this_properties}{self.sep()}{this_schema}"
455
456            return super().createable_sql(expression, locations)