sqlglot.dialects.clickhouse

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