Edit on GitHub

sqlglot.dialects.clickhouse

View Source

  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.parser import parse_var_map
 15from sqlglot.tokens import Token, TokenType
 16
 17
 18def _lower_func(sql: str) -> str:
 19    index = sql.index("(")
 20    return sql[:index].lower() + sql[index:]
 21
 22
 23class ClickHouse(Dialect):
 24    normalize_functions = None
 25    null_ordering = "nulls_are_last"
 26
 27    class Tokenizer(tokens.Tokenizer):
 28        COMMENTS = ["--", "#", "#!", ("/*", "*/")]
 29        IDENTIFIERS = ['"', "`"]
 30        BIT_STRINGS = [("0b", "")]
 31        HEX_STRINGS = [("0x", ""), ("0X", "")]
 32
 33        KEYWORDS = {
 34            **tokens.Tokenizer.KEYWORDS,
 35            "ASOF": TokenType.ASOF,
 36            "ATTACH": TokenType.COMMAND,
 37            "DATETIME64": TokenType.DATETIME64,
 38            "FINAL": TokenType.FINAL,
 39            "FLOAT32": TokenType.FLOAT,
 40            "FLOAT64": TokenType.DOUBLE,
 41            "GLOBAL": TokenType.GLOBAL,
 42            "INT128": TokenType.INT128,
 43            "INT16": TokenType.SMALLINT,
 44            "INT256": TokenType.INT256,
 45            "INT32": TokenType.INT,
 46            "INT64": TokenType.BIGINT,
 47            "INT8": TokenType.TINYINT,
 48            "MAP": TokenType.MAP,
 49            "TUPLE": TokenType.STRUCT,
 50            "UINT128": TokenType.UINT128,
 51            "UINT16": TokenType.USMALLINT,
 52            "UINT256": TokenType.UINT256,
 53            "UINT32": TokenType.UINT,
 54            "UINT64": TokenType.UBIGINT,
 55            "UINT8": TokenType.UTINYINT,
 56        }
 57
 58    class Parser(parser.Parser):
 59        FUNCTIONS = {
 60            **parser.Parser.FUNCTIONS,
 61            "ANY": exp.AnyValue.from_arg_list,
 62            "MAP": parse_var_map,
 63            "MATCH": exp.RegexpLike.from_arg_list,
 64            "UNIQ": exp.ApproxDistinct.from_arg_list,
 65        }
 66
 67        FUNCTIONS_WITH_ALIASED_ARGS = {*parser.Parser.FUNCTIONS_WITH_ALIASED_ARGS, "TUPLE"}
 68
 69        FUNCTION_PARSERS = {
 70            **parser.Parser.FUNCTION_PARSERS,
 71            "QUANTILE": lambda self: self._parse_quantile(),
 72        }
 73
 74        FUNCTION_PARSERS.pop("MATCH")
 75
 76        NO_PAREN_FUNCTION_PARSERS = parser.Parser.NO_PAREN_FUNCTION_PARSERS.copy()
 77        NO_PAREN_FUNCTION_PARSERS.pop(TokenType.ANY)
 78
 79        RANGE_PARSERS = {
 80            **parser.Parser.RANGE_PARSERS,
 81            TokenType.GLOBAL: lambda self, this: self._match(TokenType.IN)
 82            and self._parse_in(this, is_global=True),
 83        }
 84
 85        # The PLACEHOLDER entry is popped because 1) it doesn't affect Clickhouse (it corresponds to
 86        # the postgres-specific JSONBContains parser) and 2) it makes parsing the ternary op simpler.
 87        COLUMN_OPERATORS = parser.Parser.COLUMN_OPERATORS.copy()
 88        COLUMN_OPERATORS.pop(TokenType.PLACEHOLDER)
 89
 90        JOIN_KINDS = {
 91            *parser.Parser.JOIN_KINDS,
 92            TokenType.ANY,
 93            TokenType.ASOF,
 94            TokenType.ANTI,
 95            TokenType.SEMI,
 96        }
 97
 98        TABLE_ALIAS_TOKENS = {*parser.Parser.TABLE_ALIAS_TOKENS} - {
 99            TokenType.ANY,
100            TokenType.ASOF,
101            TokenType.SEMI,
102            TokenType.ANTI,
103            TokenType.SETTINGS,
104            TokenType.FORMAT,
105        }
106
107        LOG_DEFAULTS_TO_LN = True
108
109        QUERY_MODIFIER_PARSERS = {
110            **parser.Parser.QUERY_MODIFIER_PARSERS,
111            "settings": lambda self: self._parse_csv(self._parse_conjunction)
112            if self._match(TokenType.SETTINGS)
113            else None,
114            "format": lambda self: self._parse_id_var() if self._match(TokenType.FORMAT) else None,
115        }
116
117        def _parse_conjunction(self) -> t.Optional[exp.Expression]:
118            this = super()._parse_conjunction()
119
120            if self._match(TokenType.PLACEHOLDER):
121                return self.expression(
122                    exp.If,
123                    this=this,
124                    true=self._parse_conjunction(),
125                    false=self._match(TokenType.COLON) and self._parse_conjunction(),
126                )
127
128            return this
129
130        def _parse_placeholder(self) -> t.Optional[exp.Expression]:
131            """
132            Parse a placeholder expression like SELECT {abc: UInt32} or FROM {table: Identifier}
133            https://clickhouse.com/docs/en/sql-reference/syntax#defining-and-using-query-parameters
134            """
135            if not self._match(TokenType.L_BRACE):
136                return None
137
138            this = self._parse_id_var()
139            self._match(TokenType.COLON)
140            kind = self._parse_types(check_func=False) or (
141                self._match_text_seq("IDENTIFIER") and "Identifier"
142            )
143
144            if not kind:
145                self.raise_error("Expecting a placeholder type or 'Identifier' for tables")
146            elif not self._match(TokenType.R_BRACE):
147                self.raise_error("Expecting }")
148
149            return self.expression(exp.Placeholder, this=this, kind=kind)
150
151        def _parse_in(
152            self, this: t.Optional[exp.Expression], is_global: bool = False
153        ) -> exp.Expression:
154            this = super()._parse_in(this)
155            this.set("is_global", is_global)
156            return this
157
158        def _parse_table(
159            self, schema: bool = False, alias_tokens: t.Optional[t.Collection[TokenType]] = None
160        ) -> t.Optional[exp.Expression]:
161            this = super()._parse_table(schema=schema, alias_tokens=alias_tokens)
162
163            if self._match(TokenType.FINAL):
164                this = self.expression(exp.Final, this=this)
165
166            return this
167
168        def _parse_position(self, haystack_first: bool = False) -> exp.Expression:
169            return super()._parse_position(haystack_first=True)
170
171        # https://clickhouse.com/docs/en/sql-reference/statements/select/with/
172        def _parse_cte(self) -> exp.Expression:
173            index = self._index
174            try:
175                # WITH <identifier> AS <subquery expression>
176                return super()._parse_cte()
177            except ParseError:
178                # WITH <expression> AS <identifier>
179                self._retreat(index)
180                statement = self._parse_statement()
181
182                if statement and isinstance(statement.this, exp.Alias):
183                    self.raise_error("Expected CTE to have alias")
184
185                return self.expression(exp.CTE, this=statement, alias=statement and statement.this)
186
187        def _parse_join_side_and_kind(
188            self,
189        ) -> t.Tuple[t.Optional[Token], t.Optional[Token], t.Optional[Token]]:
190            is_global = self._match(TokenType.GLOBAL) and self._prev
191            kind_pre = self._match_set(self.JOIN_KINDS, advance=False) and self._prev
192            if kind_pre:
193                kind = self._match_set(self.JOIN_KINDS) and self._prev
194                side = self._match_set(self.JOIN_SIDES) and self._prev
195                return is_global, side, kind
196            return (
197                is_global,
198                self._match_set(self.JOIN_SIDES) and self._prev,
199                self._match_set(self.JOIN_KINDS) and self._prev,
200            )
201
202        def _parse_join(self, skip_join_token: bool = False) -> t.Optional[exp.Expression]:
203            join = super()._parse_join(skip_join_token)
204
205            if join:
206                join.set("global", join.args.pop("natural", None))
207            return join
208
209        def _parse_function(
210            self, functions: t.Optional[t.Dict[str, t.Callable]] = None, anonymous: bool = False
211        ) -> t.Optional[exp.Expression]:
212            func = super()._parse_function(functions, anonymous)
213
214            if isinstance(func, exp.Anonymous):
215                params = self._parse_func_params(func)
216
217                if params:
218                    return self.expression(
219                        exp.ParameterizedAgg,
220                        this=func.this,
221                        expressions=func.expressions,
222                        params=params,
223                    )
224
225            return func
226
227        def _parse_func_params(
228            self, this: t.Optional[exp.Func] = None
229        ) -> t.Optional[t.List[t.Optional[exp.Expression]]]:
230            if self._match_pair(TokenType.R_PAREN, TokenType.L_PAREN):
231                return self._parse_csv(self._parse_lambda)
232            if self._match(TokenType.L_PAREN):
233                params = self._parse_csv(self._parse_lambda)
234                self._match_r_paren(this)
235                return params
236            return None
237
238        def _parse_quantile(self) -> exp.Quantile:
239            this = self._parse_lambda()
240            params = self._parse_func_params()
241            if params:
242                return self.expression(exp.Quantile, this=params[0], quantile=this)
243            return self.expression(exp.Quantile, this=this, quantile=exp.Literal.number(0.5))
244
245        def _parse_wrapped_id_vars(
246            self, optional: bool = False
247        ) -> t.List[t.Optional[exp.Expression]]:
248            return super()._parse_wrapped_id_vars(optional=True)
249
250    class Generator(generator.Generator):
251        STRUCT_DELIMITER = ("(", ")")
252
253        TYPE_MAPPING = {
254            **generator.Generator.TYPE_MAPPING,
255            exp.DataType.Type.ARRAY: "Array",
256            exp.DataType.Type.BIGINT: "Int64",
257            exp.DataType.Type.DATETIME64: "DateTime64",
258            exp.DataType.Type.DOUBLE: "Float64",
259            exp.DataType.Type.FLOAT: "Float32",
260            exp.DataType.Type.INT: "Int32",
261            exp.DataType.Type.INT128: "Int128",
262            exp.DataType.Type.INT256: "Int256",
263            exp.DataType.Type.MAP: "Map",
264            exp.DataType.Type.NULLABLE: "Nullable",
265            exp.DataType.Type.SMALLINT: "Int16",
266            exp.DataType.Type.STRUCT: "Tuple",
267            exp.DataType.Type.TINYINT: "Int8",
268            exp.DataType.Type.UBIGINT: "UInt64",
269            exp.DataType.Type.UINT: "UInt32",
270            exp.DataType.Type.UINT128: "UInt128",
271            exp.DataType.Type.UINT256: "UInt256",
272            exp.DataType.Type.USMALLINT: "UInt16",
273            exp.DataType.Type.UTINYINT: "UInt8",
274        }
275
276        TRANSFORMS = {
277            **generator.Generator.TRANSFORMS,
278            exp.AnyValue: rename_func("any"),
279            exp.ApproxDistinct: rename_func("uniq"),
280            exp.Array: inline_array_sql,
281            exp.CastToStrType: rename_func("CAST"),
282            exp.Final: lambda self, e: f"{self.sql(e, 'this')} FINAL",
283            exp.Map: lambda self, e: _lower_func(var_map_sql(self, e)),
284            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
285            exp.Pivot: no_pivot_sql,
286            exp.Quantile: lambda self, e: self.func("quantile", e.args.get("quantile"))
287            + f"({self.sql(e, 'this')})",
288            exp.RegexpLike: lambda self, e: f"match({self.format_args(e.this, e.expression)})",
289            exp.StrPosition: lambda self, e: f"position({self.format_args(e.this, e.args.get('substr'), e.args.get('position'))})",
290            exp.VarMap: lambda self, e: _lower_func(var_map_sql(self, e)),
291        }
292
293        PROPERTIES_LOCATION = {
294            **generator.Generator.PROPERTIES_LOCATION,
295            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
296            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
297        }
298
299        JOIN_HINTS = False
300        TABLE_HINTS = False
301        EXPLICIT_UNION = True
302        GROUPINGS_SEP = ""
303
304        def cte_sql(self, expression: exp.CTE) -> str:
305            if isinstance(expression.this, exp.Alias):
306                return self.sql(expression, "this")
307
308            return super().cte_sql(expression)
309
310        def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
311            return super().after_limit_modifiers(expression) + [
312                self.seg("SETTINGS ") + self.expressions(expression, key="settings", flat=True)
313                if expression.args.get("settings")
314                else "",
315                self.seg("FORMAT ") + self.sql(expression, "format")
316                if expression.args.get("format")
317                else "",
318            ]
319
320        def parameterizedagg_sql(self, expression: exp.Anonymous) -> str:
321            params = self.expressions(expression, "params", flat=True)
322            return self.func(expression.name, *expression.expressions) + f"({params})"
323
324        def placeholder_sql(self, expression: exp.Placeholder) -> str:
325            return f"{{{expression.name}: {self.sql(expression, 'kind')}}}"

class ClickHouse(sqlglot.dialects.dialect.Dialect): View Source

 24class ClickHouse(Dialect):
 25    normalize_functions = None
 26    null_ordering = "nulls_are_last"
 27
 28    class Tokenizer(tokens.Tokenizer):
 29        COMMENTS = ["--", "#", "#!", ("/*", "*/")]
 30        IDENTIFIERS = ['"', "`"]
 31        BIT_STRINGS = [("0b", "")]
 32        HEX_STRINGS = [("0x", ""), ("0X", "")]
 33
 34        KEYWORDS = {
 35            **tokens.Tokenizer.KEYWORDS,
 36            "ASOF": TokenType.ASOF,
 37            "ATTACH": TokenType.COMMAND,
 38            "DATETIME64": TokenType.DATETIME64,
 39            "FINAL": TokenType.FINAL,
 40            "FLOAT32": TokenType.FLOAT,
 41            "FLOAT64": TokenType.DOUBLE,
 42            "GLOBAL": TokenType.GLOBAL,
 43            "INT128": TokenType.INT128,
 44            "INT16": TokenType.SMALLINT,
 45            "INT256": TokenType.INT256,
 46            "INT32": TokenType.INT,
 47            "INT64": TokenType.BIGINT,
 48            "INT8": TokenType.TINYINT,
 49            "MAP": TokenType.MAP,
 50            "TUPLE": TokenType.STRUCT,
 51            "UINT128": TokenType.UINT128,
 52            "UINT16": TokenType.USMALLINT,
 53            "UINT256": TokenType.UINT256,
 54            "UINT32": TokenType.UINT,
 55            "UINT64": TokenType.UBIGINT,
 56            "UINT8": TokenType.UTINYINT,
 57        }
 58
 59    class Parser(parser.Parser):
 60        FUNCTIONS = {
 61            **parser.Parser.FUNCTIONS,
 62            "ANY": exp.AnyValue.from_arg_list,
 63            "MAP": parse_var_map,
 64            "MATCH": exp.RegexpLike.from_arg_list,
 65            "UNIQ": exp.ApproxDistinct.from_arg_list,
 66        }
 67
 68        FUNCTIONS_WITH_ALIASED_ARGS = {*parser.Parser.FUNCTIONS_WITH_ALIASED_ARGS, "TUPLE"}
 69
 70        FUNCTION_PARSERS = {
 71            **parser.Parser.FUNCTION_PARSERS,
 72            "QUANTILE": lambda self: self._parse_quantile(),
 73        }
 74
 75        FUNCTION_PARSERS.pop("MATCH")
 76
 77        NO_PAREN_FUNCTION_PARSERS = parser.Parser.NO_PAREN_FUNCTION_PARSERS.copy()
 78        NO_PAREN_FUNCTION_PARSERS.pop(TokenType.ANY)
 79
 80        RANGE_PARSERS = {
 81            **parser.Parser.RANGE_PARSERS,
 82            TokenType.GLOBAL: lambda self, this: self._match(TokenType.IN)
 83            and self._parse_in(this, is_global=True),
 84        }
 85
 86        # The PLACEHOLDER entry is popped because 1) it doesn't affect Clickhouse (it corresponds to
 87        # the postgres-specific JSONBContains parser) and 2) it makes parsing the ternary op simpler.
 88        COLUMN_OPERATORS = parser.Parser.COLUMN_OPERATORS.copy()
 89        COLUMN_OPERATORS.pop(TokenType.PLACEHOLDER)
 90
 91        JOIN_KINDS = {
 92            *parser.Parser.JOIN_KINDS,
 93            TokenType.ANY,
 94            TokenType.ASOF,
 95            TokenType.ANTI,
 96            TokenType.SEMI,
 97        }
 98
 99        TABLE_ALIAS_TOKENS = {*parser.Parser.TABLE_ALIAS_TOKENS} - {
100            TokenType.ANY,
101            TokenType.ASOF,
102            TokenType.SEMI,
103            TokenType.ANTI,
104            TokenType.SETTINGS,
105            TokenType.FORMAT,
106        }
107
108        LOG_DEFAULTS_TO_LN = True
109
110        QUERY_MODIFIER_PARSERS = {
111            **parser.Parser.QUERY_MODIFIER_PARSERS,
112            "settings": lambda self: self._parse_csv(self._parse_conjunction)
113            if self._match(TokenType.SETTINGS)
114            else None,
115            "format": lambda self: self._parse_id_var() if self._match(TokenType.FORMAT) else None,
116        }
117
118        def _parse_conjunction(self) -> t.Optional[exp.Expression]:
119            this = super()._parse_conjunction()
120
121            if self._match(TokenType.PLACEHOLDER):
122                return self.expression(
123                    exp.If,
124                    this=this,
125                    true=self._parse_conjunction(),
126                    false=self._match(TokenType.COLON) and self._parse_conjunction(),
127                )
128
129            return this
130
131        def _parse_placeholder(self) -> t.Optional[exp.Expression]:
132            """
133            Parse a placeholder expression like SELECT {abc: UInt32} or FROM {table: Identifier}
134            https://clickhouse.com/docs/en/sql-reference/syntax#defining-and-using-query-parameters
135            """
136            if not self._match(TokenType.L_BRACE):
137                return None
138
139            this = self._parse_id_var()
140            self._match(TokenType.COLON)
141            kind = self._parse_types(check_func=False) or (
142                self._match_text_seq("IDENTIFIER") and "Identifier"
143            )
144
145            if not kind:
146                self.raise_error("Expecting a placeholder type or 'Identifier' for tables")
147            elif not self._match(TokenType.R_BRACE):
148                self.raise_error("Expecting }")
149
150            return self.expression(exp.Placeholder, this=this, kind=kind)
151
152        def _parse_in(
153            self, this: t.Optional[exp.Expression], is_global: bool = False
154        ) -> exp.Expression:
155            this = super()._parse_in(this)
156            this.set("is_global", is_global)
157            return this
158
159        def _parse_table(
160            self, schema: bool = False, alias_tokens: t.Optional[t.Collection[TokenType]] = None
161        ) -> t.Optional[exp.Expression]:
162            this = super()._parse_table(schema=schema, alias_tokens=alias_tokens)
163
164            if self._match(TokenType.FINAL):
165                this = self.expression(exp.Final, this=this)
166
167            return this
168
169        def _parse_position(self, haystack_first: bool = False) -> exp.Expression:
170            return super()._parse_position(haystack_first=True)
171
172        # https://clickhouse.com/docs/en/sql-reference/statements/select/with/
173        def _parse_cte(self) -> exp.Expression:
174            index = self._index
175            try:
176                # WITH <identifier> AS <subquery expression>
177                return super()._parse_cte()
178            except ParseError:
179                # WITH <expression> AS <identifier>
180                self._retreat(index)
181                statement = self._parse_statement()
182
183                if statement and isinstance(statement.this, exp.Alias):
184                    self.raise_error("Expected CTE to have alias")
185
186                return self.expression(exp.CTE, this=statement, alias=statement and statement.this)
187
188        def _parse_join_side_and_kind(
189            self,
190        ) -> t.Tuple[t.Optional[Token], t.Optional[Token], t.Optional[Token]]:
191            is_global = self._match(TokenType.GLOBAL) and self._prev
192            kind_pre = self._match_set(self.JOIN_KINDS, advance=False) and self._prev
193            if kind_pre:
194                kind = self._match_set(self.JOIN_KINDS) and self._prev
195                side = self._match_set(self.JOIN_SIDES) and self._prev
196                return is_global, side, kind
197            return (
198                is_global,
199                self._match_set(self.JOIN_SIDES) and self._prev,
200                self._match_set(self.JOIN_KINDS) and self._prev,
201            )
202
203        def _parse_join(self, skip_join_token: bool = False) -> t.Optional[exp.Expression]:
204            join = super()._parse_join(skip_join_token)
205
206            if join:
207                join.set("global", join.args.pop("natural", None))
208            return join
209
210        def _parse_function(
211            self, functions: t.Optional[t.Dict[str, t.Callable]] = None, anonymous: bool = False
212        ) -> t.Optional[exp.Expression]:
213            func = super()._parse_function(functions, anonymous)
214
215            if isinstance(func, exp.Anonymous):
216                params = self._parse_func_params(func)
217
218                if params:
219                    return self.expression(
220                        exp.ParameterizedAgg,
221                        this=func.this,
222                        expressions=func.expressions,
223                        params=params,
224                    )
225
226            return func
227
228        def _parse_func_params(
229            self, this: t.Optional[exp.Func] = None
230        ) -> t.Optional[t.List[t.Optional[exp.Expression]]]:
231            if self._match_pair(TokenType.R_PAREN, TokenType.L_PAREN):
232                return self._parse_csv(self._parse_lambda)
233            if self._match(TokenType.L_PAREN):
234                params = self._parse_csv(self._parse_lambda)
235                self._match_r_paren(this)
236                return params
237            return None
238
239        def _parse_quantile(self) -> exp.Quantile:
240            this = self._parse_lambda()
241            params = self._parse_func_params()
242            if params:
243                return self.expression(exp.Quantile, this=params[0], quantile=this)
244            return self.expression(exp.Quantile, this=this, quantile=exp.Literal.number(0.5))
245
246        def _parse_wrapped_id_vars(
247            self, optional: bool = False
248        ) -> t.List[t.Optional[exp.Expression]]:
249            return super()._parse_wrapped_id_vars(optional=True)
250
251    class Generator(generator.Generator):
252        STRUCT_DELIMITER = ("(", ")")
253
254        TYPE_MAPPING = {
255            **generator.Generator.TYPE_MAPPING,
256            exp.DataType.Type.ARRAY: "Array",
257            exp.DataType.Type.BIGINT: "Int64",
258            exp.DataType.Type.DATETIME64: "DateTime64",
259            exp.DataType.Type.DOUBLE: "Float64",
260            exp.DataType.Type.FLOAT: "Float32",
261            exp.DataType.Type.INT: "Int32",
262            exp.DataType.Type.INT128: "Int128",
263            exp.DataType.Type.INT256: "Int256",
264            exp.DataType.Type.MAP: "Map",
265            exp.DataType.Type.NULLABLE: "Nullable",
266            exp.DataType.Type.SMALLINT: "Int16",
267            exp.DataType.Type.STRUCT: "Tuple",
268            exp.DataType.Type.TINYINT: "Int8",
269            exp.DataType.Type.UBIGINT: "UInt64",
270            exp.DataType.Type.UINT: "UInt32",
271            exp.DataType.Type.UINT128: "UInt128",
272            exp.DataType.Type.UINT256: "UInt256",
273            exp.DataType.Type.USMALLINT: "UInt16",
274            exp.DataType.Type.UTINYINT: "UInt8",
275        }
276
277        TRANSFORMS = {
278            **generator.Generator.TRANSFORMS,
279            exp.AnyValue: rename_func("any"),
280            exp.ApproxDistinct: rename_func("uniq"),
281            exp.Array: inline_array_sql,
282            exp.CastToStrType: rename_func("CAST"),
283            exp.Final: lambda self, e: f"{self.sql(e, 'this')} FINAL",
284            exp.Map: lambda self, e: _lower_func(var_map_sql(self, e)),
285            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
286            exp.Pivot: no_pivot_sql,
287            exp.Quantile: lambda self, e: self.func("quantile", e.args.get("quantile"))
288            + f"({self.sql(e, 'this')})",
289            exp.RegexpLike: lambda self, e: f"match({self.format_args(e.this, e.expression)})",
290            exp.StrPosition: lambda self, e: f"position({self.format_args(e.this, e.args.get('substr'), e.args.get('position'))})",
291            exp.VarMap: lambda self, e: _lower_func(var_map_sql(self, e)),
292        }
293
294        PROPERTIES_LOCATION = {
295            **generator.Generator.PROPERTIES_LOCATION,
296            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
297            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
298        }
299
300        JOIN_HINTS = False
301        TABLE_HINTS = False
302        EXPLICIT_UNION = True
303        GROUPINGS_SEP = ""
304
305        def cte_sql(self, expression: exp.CTE) -> str:
306            if isinstance(expression.this, exp.Alias):
307                return self.sql(expression, "this")
308
309            return super().cte_sql(expression)
310
311        def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
312            return super().after_limit_modifiers(expression) + [
313                self.seg("SETTINGS ") + self.expressions(expression, key="settings", flat=True)
314                if expression.args.get("settings")
315                else "",
316                self.seg("FORMAT ") + self.sql(expression, "format")
317                if expression.args.get("format")
318                else "",
319            ]
320
321        def parameterizedagg_sql(self, expression: exp.Anonymous) -> str:
322            params = self.expressions(expression, "params", flat=True)
323            return self.func(expression.name, *expression.expressions) + f"({params})"
324
325        def placeholder_sql(self, expression: exp.Placeholder) -> str:
326            return f"{{{expression.name}: {self.sql(expression, 'kind')}}}"

Inherited Members

sqlglot.dialects.dialect.Dialect: get_or_raise; format_time; parse; parse_into; generate; transpile; tokenize; parser; generator

class ClickHouse.Tokenizer(sqlglot.tokens.Tokenizer): View Source

28    class Tokenizer(tokens.Tokenizer):
29        COMMENTS = ["--", "#", "#!", ("/*", "*/")]
30        IDENTIFIERS = ['"', "`"]
31        BIT_STRINGS = [("0b", "")]
32        HEX_STRINGS = [("0x", ""), ("0X", "")]
33
34        KEYWORDS = {
35            **tokens.Tokenizer.KEYWORDS,
36            "ASOF": TokenType.ASOF,
37            "ATTACH": TokenType.COMMAND,
38            "DATETIME64": TokenType.DATETIME64,
39            "FINAL": TokenType.FINAL,
40            "FLOAT32": TokenType.FLOAT,
41            "FLOAT64": TokenType.DOUBLE,
42            "GLOBAL": TokenType.GLOBAL,
43            "INT128": TokenType.INT128,
44            "INT16": TokenType.SMALLINT,
45            "INT256": TokenType.INT256,
46            "INT32": TokenType.INT,
47            "INT64": TokenType.BIGINT,
48            "INT8": TokenType.TINYINT,
49            "MAP": TokenType.MAP,
50            "TUPLE": TokenType.STRUCT,
51            "UINT128": TokenType.UINT128,
52            "UINT16": TokenType.USMALLINT,
53            "UINT256": TokenType.UINT256,
54            "UINT32": TokenType.UINT,
55            "UINT64": TokenType.UBIGINT,
56            "UINT8": TokenType.UTINYINT,
57        }

Inherited Members

sqlglot.tokens.Tokenizer: reset; tokenize

class ClickHouse.Parser(sqlglot.parser.Parser): View Source

 59    class Parser(parser.Parser):
 60        FUNCTIONS = {
 61            **parser.Parser.FUNCTIONS,
 62            "ANY": exp.AnyValue.from_arg_list,
 63            "MAP": parse_var_map,
 64            "MATCH": exp.RegexpLike.from_arg_list,
 65            "UNIQ": exp.ApproxDistinct.from_arg_list,
 66        }
 67
 68        FUNCTIONS_WITH_ALIASED_ARGS = {*parser.Parser.FUNCTIONS_WITH_ALIASED_ARGS, "TUPLE"}
 69
 70        FUNCTION_PARSERS = {
 71            **parser.Parser.FUNCTION_PARSERS,
 72            "QUANTILE": lambda self: self._parse_quantile(),
 73        }
 74
 75        FUNCTION_PARSERS.pop("MATCH")
 76
 77        NO_PAREN_FUNCTION_PARSERS = parser.Parser.NO_PAREN_FUNCTION_PARSERS.copy()
 78        NO_PAREN_FUNCTION_PARSERS.pop(TokenType.ANY)
 79
 80        RANGE_PARSERS = {
 81            **parser.Parser.RANGE_PARSERS,
 82            TokenType.GLOBAL: lambda self, this: self._match(TokenType.IN)
 83            and self._parse_in(this, is_global=True),
 84        }
 85
 86        # The PLACEHOLDER entry is popped because 1) it doesn't affect Clickhouse (it corresponds to
 87        # the postgres-specific JSONBContains parser) and 2) it makes parsing the ternary op simpler.
 88        COLUMN_OPERATORS = parser.Parser.COLUMN_OPERATORS.copy()
 89        COLUMN_OPERATORS.pop(TokenType.PLACEHOLDER)
 90
 91        JOIN_KINDS = {
 92            *parser.Parser.JOIN_KINDS,
 93            TokenType.ANY,
 94            TokenType.ASOF,
 95            TokenType.ANTI,
 96            TokenType.SEMI,
 97        }
 98
 99        TABLE_ALIAS_TOKENS = {*parser.Parser.TABLE_ALIAS_TOKENS} - {
100            TokenType.ANY,
101            TokenType.ASOF,
102            TokenType.SEMI,
103            TokenType.ANTI,
104            TokenType.SETTINGS,
105            TokenType.FORMAT,
106        }
107
108        LOG_DEFAULTS_TO_LN = True
109
110        QUERY_MODIFIER_PARSERS = {
111            **parser.Parser.QUERY_MODIFIER_PARSERS,
112            "settings": lambda self: self._parse_csv(self._parse_conjunction)
113            if self._match(TokenType.SETTINGS)
114            else None,
115            "format": lambda self: self._parse_id_var() if self._match(TokenType.FORMAT) else None,
116        }
117
118        def _parse_conjunction(self) -> t.Optional[exp.Expression]:
119            this = super()._parse_conjunction()
120
121            if self._match(TokenType.PLACEHOLDER):
122                return self.expression(
123                    exp.If,
124                    this=this,
125                    true=self._parse_conjunction(),
126                    false=self._match(TokenType.COLON) and self._parse_conjunction(),
127                )
128
129            return this
130
131        def _parse_placeholder(self) -> t.Optional[exp.Expression]:
132            """
133            Parse a placeholder expression like SELECT {abc: UInt32} or FROM {table: Identifier}
134            https://clickhouse.com/docs/en/sql-reference/syntax#defining-and-using-query-parameters
135            """
136            if not self._match(TokenType.L_BRACE):
137                return None
138
139            this = self._parse_id_var()
140            self._match(TokenType.COLON)
141            kind = self._parse_types(check_func=False) or (
142                self._match_text_seq("IDENTIFIER") and "Identifier"
143            )
144
145            if not kind:
146                self.raise_error("Expecting a placeholder type or 'Identifier' for tables")
147            elif not self._match(TokenType.R_BRACE):
148                self.raise_error("Expecting }")
149
150            return self.expression(exp.Placeholder, this=this, kind=kind)
151
152        def _parse_in(
153            self, this: t.Optional[exp.Expression], is_global: bool = False
154        ) -> exp.Expression:
155            this = super()._parse_in(this)
156            this.set("is_global", is_global)
157            return this
158
159        def _parse_table(
160            self, schema: bool = False, alias_tokens: t.Optional[t.Collection[TokenType]] = None
161        ) -> t.Optional[exp.Expression]:
162            this = super()._parse_table(schema=schema, alias_tokens=alias_tokens)
163
164            if self._match(TokenType.FINAL):
165                this = self.expression(exp.Final, this=this)
166
167            return this
168
169        def _parse_position(self, haystack_first: bool = False) -> exp.Expression:
170            return super()._parse_position(haystack_first=True)
171
172        # https://clickhouse.com/docs/en/sql-reference/statements/select/with/
173        def _parse_cte(self) -> exp.Expression:
174            index = self._index
175            try:
176                # WITH <identifier> AS <subquery expression>
177                return super()._parse_cte()
178            except ParseError:
179                # WITH <expression> AS <identifier>
180                self._retreat(index)
181                statement = self._parse_statement()
182
183                if statement and isinstance(statement.this, exp.Alias):
184                    self.raise_error("Expected CTE to have alias")
185
186                return self.expression(exp.CTE, this=statement, alias=statement and statement.this)
187
188        def _parse_join_side_and_kind(
189            self,
190        ) -> t.Tuple[t.Optional[Token], t.Optional[Token], t.Optional[Token]]:
191            is_global = self._match(TokenType.GLOBAL) and self._prev
192            kind_pre = self._match_set(self.JOIN_KINDS, advance=False) and self._prev
193            if kind_pre:
194                kind = self._match_set(self.JOIN_KINDS) and self._prev
195                side = self._match_set(self.JOIN_SIDES) and self._prev
196                return is_global, side, kind
197            return (
198                is_global,
199                self._match_set(self.JOIN_SIDES) and self._prev,
200                self._match_set(self.JOIN_KINDS) and self._prev,
201            )
202
203        def _parse_join(self, skip_join_token: bool = False) -> t.Optional[exp.Expression]:
204            join = super()._parse_join(skip_join_token)
205
206            if join:
207                join.set("global", join.args.pop("natural", None))
208            return join
209
210        def _parse_function(
211            self, functions: t.Optional[t.Dict[str, t.Callable]] = None, anonymous: bool = False
212        ) -> t.Optional[exp.Expression]:
213            func = super()._parse_function(functions, anonymous)
214
215            if isinstance(func, exp.Anonymous):
216                params = self._parse_func_params(func)
217
218                if params:
219                    return self.expression(
220                        exp.ParameterizedAgg,
221                        this=func.this,
222                        expressions=func.expressions,
223                        params=params,
224                    )
225
226            return func
227
228        def _parse_func_params(
229            self, this: t.Optional[exp.Func] = None
230        ) -> t.Optional[t.List[t.Optional[exp.Expression]]]:
231            if self._match_pair(TokenType.R_PAREN, TokenType.L_PAREN):
232                return self._parse_csv(self._parse_lambda)
233            if self._match(TokenType.L_PAREN):
234                params = self._parse_csv(self._parse_lambda)
235                self._match_r_paren(this)
236                return params
237            return None
238
239        def _parse_quantile(self) -> exp.Quantile:
240            this = self._parse_lambda()
241            params = self._parse_func_params()
242            if params:
243                return self.expression(exp.Quantile, this=params[0], quantile=this)
244            return self.expression(exp.Quantile, this=this, quantile=exp.Literal.number(0.5))
245
246        def _parse_wrapped_id_vars(
247            self, optional: bool = False
248        ) -> t.List[t.Optional[exp.Expression]]:
249            return super()._parse_wrapped_id_vars(optional=True)

Parser consumes a list of tokens produced by the sqlglot.tokens.Tokenizer and produces a parsed syntax tree.

Arguments:

error_level: the desired error level. Default: ErrorLevel.RAISE
error_message_context: determines the amount of context to capture from a query string when displaying the error message (in number of characters). Default: 50.
index_offset: Index offset for arrays eg ARRAY[0] vs ARRAY[1] as the head of a list. Default: 0
alias_post_tablesample: If the table alias comes after tablesample. Default: False
max_errors: Maximum number of error messages to include in a raised ParseError. This is only relevant if error_level is ErrorLevel.RAISE. Default: 3
null_ordering: Indicates the default null ordering method to use if not explicitly set. Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". Default: "nulls_are_small"

Inherited Members

sqlglot.parser.Parser: Parser; reset; parse; parse_into; check_errors; raise_error; expression; validate_expression

class ClickHouse.Generator(sqlglot.generator.Generator): View Source

251    class Generator(generator.Generator):
252        STRUCT_DELIMITER = ("(", ")")
253
254        TYPE_MAPPING = {
255            **generator.Generator.TYPE_MAPPING,
256            exp.DataType.Type.ARRAY: "Array",
257            exp.DataType.Type.BIGINT: "Int64",
258            exp.DataType.Type.DATETIME64: "DateTime64",
259            exp.DataType.Type.DOUBLE: "Float64",
260            exp.DataType.Type.FLOAT: "Float32",
261            exp.DataType.Type.INT: "Int32",
262            exp.DataType.Type.INT128: "Int128",
263            exp.DataType.Type.INT256: "Int256",
264            exp.DataType.Type.MAP: "Map",
265            exp.DataType.Type.NULLABLE: "Nullable",
266            exp.DataType.Type.SMALLINT: "Int16",
267            exp.DataType.Type.STRUCT: "Tuple",
268            exp.DataType.Type.TINYINT: "Int8",
269            exp.DataType.Type.UBIGINT: "UInt64",
270            exp.DataType.Type.UINT: "UInt32",
271            exp.DataType.Type.UINT128: "UInt128",
272            exp.DataType.Type.UINT256: "UInt256",
273            exp.DataType.Type.USMALLINT: "UInt16",
274            exp.DataType.Type.UTINYINT: "UInt8",
275        }
276
277        TRANSFORMS = {
278            **generator.Generator.TRANSFORMS,
279            exp.AnyValue: rename_func("any"),
280            exp.ApproxDistinct: rename_func("uniq"),
281            exp.Array: inline_array_sql,
282            exp.CastToStrType: rename_func("CAST"),
283            exp.Final: lambda self, e: f"{self.sql(e, 'this')} FINAL",
284            exp.Map: lambda self, e: _lower_func(var_map_sql(self, e)),
285            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
286            exp.Pivot: no_pivot_sql,
287            exp.Quantile: lambda self, e: self.func("quantile", e.args.get("quantile"))
288            + f"({self.sql(e, 'this')})",
289            exp.RegexpLike: lambda self, e: f"match({self.format_args(e.this, e.expression)})",
290            exp.StrPosition: lambda self, e: f"position({self.format_args(e.this, e.args.get('substr'), e.args.get('position'))})",
291            exp.VarMap: lambda self, e: _lower_func(var_map_sql(self, e)),
292        }
293
294        PROPERTIES_LOCATION = {
295            **generator.Generator.PROPERTIES_LOCATION,
296            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
297            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
298        }
299
300        JOIN_HINTS = False
301        TABLE_HINTS = False
302        EXPLICIT_UNION = True
303        GROUPINGS_SEP = ""
304
305        def cte_sql(self, expression: exp.CTE) -> str:
306            if isinstance(expression.this, exp.Alias):
307                return self.sql(expression, "this")
308
309            return super().cte_sql(expression)
310
311        def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
312            return super().after_limit_modifiers(expression) + [
313                self.seg("SETTINGS ") + self.expressions(expression, key="settings", flat=True)
314                if expression.args.get("settings")
315                else "",
316                self.seg("FORMAT ") + self.sql(expression, "format")
317                if expression.args.get("format")
318                else "",
319            ]
320
321        def parameterizedagg_sql(self, expression: exp.Anonymous) -> str:
322            params = self.expressions(expression, "params", flat=True)
323            return self.func(expression.name, *expression.expressions) + f"({params})"
324
325        def placeholder_sql(self, expression: exp.Placeholder) -> str:
326            return f"{{{expression.name}: {self.sql(expression, 'kind')}}}"

Generator interprets the given syntax tree and produces a SQL string as an output.

Arguments:

time_mapping (dict): the dictionary of custom time mappings in which the key represents a python time format and the output the target time format
time_trie (trie): a trie of the time_mapping keys
pretty (bool): if set to True the returned string will be formatted. Default: False.
quote_start (str): specifies which starting character to use to delimit quotes. Default: '.
quote_end (str): specifies which ending character to use to delimit quotes. Default: '.
identifier_start (str): specifies which starting character to use to delimit identifiers. Default: ".
identifier_end (str): specifies which ending character to use to delimit identifiers. Default: ".
bit_start (str): specifies which starting character to use to delimit bit literals. Default: None.
bit_end (str): specifies which ending character to use to delimit bit literals. Default: None.
hex_start (str): specifies which starting character to use to delimit hex literals. Default: None.
hex_end (str): specifies which ending character to use to delimit hex literals. Default: None.
byte_start (str): specifies which starting character to use to delimit byte literals. Default: None.
byte_end (str): specifies which ending character to use to delimit byte literals. Default: None.
raw_start (str): specifies which starting character to use to delimit raw literals. Default: None.
raw_end (str): specifies which ending character to use to delimit raw literals. Default: None.
identify (bool | str): 'always': always quote, 'safe': quote identifiers if they don't contain an upcase, True defaults to always.
normalize (bool): if set to True all identifiers will lower cased
string_escape (str): specifies a string escape character. Default: '.
identifier_escape (str): specifies an identifier escape character. Default: ".
pad (int): determines padding in a formatted string. Default: 2.
indent (int): determines the size of indentation in a formatted string. Default: 4.
unnest_column_only (bool): if true unnest table aliases are considered only as column aliases
normalize_functions (str): normalize function names, "upper", "lower", or None Default: "upper"
alias_post_tablesample (bool): if the table alias comes after tablesample Default: False
unsupported_level (ErrorLevel): determines the generator's behavior when it encounters unsupported expressions. Default ErrorLevel.WARN.
null_ordering (str): Indicates the default null ordering method to use if not explicitly set. Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". Default: "nulls_are_small"
max_unsupported (int): Maximum number of unsupported messages to include in a raised UnsupportedError. This is only relevant if unsupported_level is ErrorLevel.RAISE. Default: 3
leading_comma (bool): if the the comma is leading or trailing in select statements Default: False
max_text_width: The max number of characters in a segment before creating new lines in pretty mode. The default is on the smaller end because the length only represents a segment and not the true line length. Default: 80
comments: Whether or not to preserve comments in the output SQL code. Default: True

def cte_sql(self, expression: sqlglot.expressions.CTE) -> str: View Source

305        def cte_sql(self, expression: exp.CTE) -> str:
306            if isinstance(expression.this, exp.Alias):
307                return self.sql(expression, "this")
308
309            return super().cte_sql(expression)

def after_limit_modifiers(self, expression: sqlglot.expressions.Expression) -> List[str]: View Source

311        def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
312            return super().after_limit_modifiers(expression) + [
313                self.seg("SETTINGS ") + self.expressions(expression, key="settings", flat=True)
314                if expression.args.get("settings")
315                else "",
316                self.seg("FORMAT ") + self.sql(expression, "format")
317                if expression.args.get("format")
318                else "",
319            ]

def parameterizedagg_sql(self, expression: sqlglot.expressions.Anonymous) -> str: View Source

321        def parameterizedagg_sql(self, expression: exp.Anonymous) -> str:
322            params = self.expressions(expression, "params", flat=True)
323            return self.func(expression.name, *expression.expressions) + f"({params})"

def placeholder_sql(self, expression: sqlglot.expressions.Placeholder) -> str: View Source

325        def placeholder_sql(self, expression: exp.Placeholder) -> str:
326            return f"{{{expression.name}: {self.sql(expression, 'kind')}}}"

Inherited Members

sqlglot.generator.Generator: Generator; generate; unsupported; sep; seg; pad_comment; maybe_comment; wrap; no_identify; normalize_func; indent; sql; uncache_sql; cache_sql; characterset_sql; column_sql; columnposition_sql; columndef_sql; columnconstraint_sql; autoincrementcolumnconstraint_sql; compresscolumnconstraint_sql; generatedasidentitycolumnconstraint_sql; notnullcolumnconstraint_sql; primarykeycolumnconstraint_sql; uniquecolumnconstraint_sql; create_sql; clone_sql; describe_sql; prepend_ctes; with_sql; tablealias_sql; bitstring_sql; hexstring_sql; bytestring_sql; rawstring_sql; datatypesize_sql; datatype_sql; directory_sql; delete_sql; drop_sql; except_sql; except_op; fetch_sql; filter_sql; hint_sql; index_sql; identifier_sql; inputoutputformat_sql; national_sql; partition_sql; properties_sql; root_properties; properties; with_properties; locate_properties; property_sql; likeproperty_sql; fallbackproperty_sql; journalproperty_sql; freespaceproperty_sql; checksumproperty_sql; mergeblockratioproperty_sql; datablocksizeproperty_sql; blockcompressionproperty_sql; isolatedloadingproperty_sql; lockingproperty_sql; withdataproperty_sql; insert_sql; intersect_sql; intersect_op; introducer_sql; pseudotype_sql; onconflict_sql; returning_sql; rowformatdelimitedproperty_sql; table_sql; tablesample_sql; pivot_sql; tuple_sql; update_sql; values_sql; var_sql; into_sql; from_sql; group_sql; having_sql; join_sql; lambda_sql; lateral_sql; limit_sql; offset_sql; setitem_sql; set_sql; pragma_sql; lock_sql; literal_sql; loaddata_sql; null_sql; boolean_sql; order_sql; cluster_sql; distribute_sql; sort_sql; ordered_sql; matchrecognize_sql; query_modifiers; after_having_modifiers; select_sql; schema_sql; star_sql; parameter_sql; sessionparameter_sql; subquery_sql; qualify_sql; union_sql; union_op; unnest_sql; where_sql; window_sql; partition_by_sql; windowspec_sql; withingroup_sql; between_sql; bracket_sql; all_sql; any_sql; exists_sql; case_sql; constraint_sql; nextvaluefor_sql; extract_sql; trim_sql; concat_sql; check_sql; foreignkey_sql; primarykey_sql; unique_sql; if_sql; matchagainst_sql; jsonkeyvalue_sql; jsonobject_sql; openjsoncolumndef_sql; openjson_sql; in_sql; in_unnest_op; interval_sql; return_sql; reference_sql; anonymous_sql; paren_sql; neg_sql; not_sql; alias_sql; aliases_sql; attimezone_sql; add_sql; and_sql; connector_sql; bitwiseand_sql; bitwiseleftshift_sql; bitwisenot_sql; bitwiseor_sql; bitwiserightshift_sql; bitwisexor_sql; cast_sql; currentdate_sql; collate_sql; command_sql; comment_sql; mergetreettlaction_sql; mergetreettl_sql; transaction_sql; commit_sql; rollback_sql; altercolumn_sql; renametable_sql; altertable_sql; droppartition_sql; addconstraint_sql; distinct_sql; ignorenulls_sql; respectnulls_sql; intdiv_sql; dpipe_sql; div_sql; overlaps_sql; distance_sql; dot_sql; eq_sql; escape_sql; glob_sql; gt_sql; gte_sql; ilike_sql; ilikeany_sql; is_sql; like_sql; likeany_sql; similarto_sql; lt_sql; lte_sql; mod_sql; mul_sql; neq_sql; nullsafeeq_sql; nullsafeneq_sql; or_sql; slice_sql; sub_sql; trycast_sql; use_sql; binary; function_fallback_sql; func; format_args; text_width; format_time; expressions; op_expressions; naked_property; set_operation; tag_sql; token_sql; userdefinedfunction_sql; joinhint_sql; kwarg_sql; when_sql; merge_sql; tochar_sql