sqlglot.dialects.clickhouse

   1from __future__ import annotations
   2import typing as t
   3import datetime
   4from sqlglot import exp, generator, parser, tokens
   5from sqlglot._typing import E
   6from sqlglot.dialects.dialect import (
   7    Dialect,
   8    NormalizationStrategy,
   9    arg_max_or_min_no_count,
  10    build_date_delta,
  11    build_formatted_time,
  12    build_like,
  13    inline_array_sql,
  14    jarowinkler_similarity,
  15    json_extract_segments,
  16    json_path_key_only_name,
  17    length_or_char_length_sql,
  18    no_pivot_sql,
  19    build_json_extract_path,
  20    rename_func,
  21    remove_from_array_using_filter,
  22    sha256_sql,
  23    strposition_sql,
  24    var_map_sql,
  25    timestamptrunc_sql,
  26    unit_to_var,
  27    trim_sql,
  28    sha2_digest_sql,
  29)
  30from sqlglot.generator import Generator
  31from sqlglot.helper import is_int, seq_get
  32from sqlglot.tokens import Token, TokenType
  33from sqlglot.generator import unsupported_args
  34from sqlglot.typing.clickhouse import EXPRESSION_METADATA
  35
  36DATEΤΙΜΕ_DELTA = t.Union[exp.DateAdd, exp.DateDiff, exp.DateSub, exp.TimestampSub, exp.TimestampAdd]
  37
  38
  39def _build_datetime_format(
  40    expr_type: t.Type[E],
  41) -> t.Callable[[t.List], E]:
  42    def _builder(args: t.List) -> E:
  43        expr = build_formatted_time(expr_type, "clickhouse")(args)
  44
  45        timezone = seq_get(args, 2)
  46        if timezone:
  47            expr.set("zone", timezone)
  48
  49        return expr
  50
  51    return _builder
  52
  53
  54def _unix_to_time_sql(self: ClickHouse.Generator, expression: exp.UnixToTime) -> str:
  55    scale = expression.args.get("scale")
  56    timestamp = expression.this
  57
  58    if scale in (None, exp.UnixToTime.SECONDS):
  59        return self.func("fromUnixTimestamp", exp.cast(timestamp, exp.DataType.Type.BIGINT))
  60    if scale == exp.UnixToTime.MILLIS:
  61        return self.func("fromUnixTimestamp64Milli", exp.cast(timestamp, exp.DataType.Type.BIGINT))
  62    if scale == exp.UnixToTime.MICROS:
  63        return self.func("fromUnixTimestamp64Micro", exp.cast(timestamp, exp.DataType.Type.BIGINT))
  64    if scale == exp.UnixToTime.NANOS:
  65        return self.func("fromUnixTimestamp64Nano", exp.cast(timestamp, exp.DataType.Type.BIGINT))
  66
  67    return self.func(
  68        "fromUnixTimestamp",
  69        exp.cast(
  70            exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)), exp.DataType.Type.BIGINT
  71        ),
  72    )
  73
  74
  75def _lower_func(sql: str) -> str:
  76    index = sql.index("(")
  77    return sql[:index].lower() + sql[index:]
  78
  79
  80def _quantile_sql(self: ClickHouse.Generator, expression: exp.Quantile) -> str:
  81    quantile = expression.args["quantile"]
  82    args = f"({self.sql(expression, 'this')})"
  83
  84    if isinstance(quantile, exp.Array):
  85        func = self.func("quantiles", *quantile)
  86    else:
  87        func = self.func("quantile", quantile)
  88
  89    return func + args
  90
  91
  92def _build_count_if(args: t.List) -> exp.CountIf | exp.CombinedAggFunc:
  93    if len(args) == 1:
  94        return exp.CountIf(this=seq_get(args, 0))
  95
  96    return exp.CombinedAggFunc(this="countIf", expressions=args)
  97
  98
  99def _build_str_to_date(args: t.List) -> exp.Cast | exp.Anonymous:
 100    if len(args) == 3:
 101        return exp.Anonymous(this="STR_TO_DATE", expressions=args)
 102
 103    strtodate = exp.StrToDate.from_arg_list(args)
 104    return exp.cast(strtodate, exp.DataType.build(exp.DataType.Type.DATETIME))
 105
 106
 107def _datetime_delta_sql(name: str) -> t.Callable[[Generator, DATEΤΙΜΕ_DELTA], str]:
 108    def _delta_sql(self: Generator, expression: DATEΤΙΜΕ_DELTA) -> str:
 109        if not expression.unit:
 110            return rename_func(name)(self, expression)
 111
 112        return self.func(
 113            name,
 114            unit_to_var(expression),
 115            expression.expression,
 116            expression.this,
 117            expression.args.get("zone"),
 118        )
 119
 120    return _delta_sql
 121
 122
 123def _timestrtotime_sql(self: ClickHouse.Generator, expression: exp.TimeStrToTime):
 124    ts = expression.this
 125
 126    tz = expression.args.get("zone")
 127    if tz and isinstance(ts, exp.Literal):
 128        # Clickhouse will not accept timestamps that include a UTC offset, so we must remove them.
 129        # The first step to removing is parsing the string with `datetime.datetime.fromisoformat`.
 130        #
 131        # In python <3.11, `fromisoformat()` can only parse timestamps of millisecond (3 digit)
 132        # or microsecond (6 digit) precision. It will error if passed any other number of fractional
 133        # digits, so we extract the fractional seconds and pad to 6 digits before parsing.
 134        ts_string = ts.name.strip()
 135
 136        # separate [date and time] from [fractional seconds and UTC offset]
 137        ts_parts = ts_string.split(".")
 138        if len(ts_parts) == 2:
 139            # separate fractional seconds and UTC offset
 140            offset_sep = "+" if "+" in ts_parts[1] else "-"
 141            ts_frac_parts = ts_parts[1].split(offset_sep)
 142            num_frac_parts = len(ts_frac_parts)
 143
 144            # pad to 6 digits if fractional seconds present
 145            ts_frac_parts[0] = ts_frac_parts[0].ljust(6, "0")
 146            ts_string = "".join(
 147                [
 148                    ts_parts[0],  # date and time
 149                    ".",
 150                    ts_frac_parts[0],  # fractional seconds
 151                    offset_sep if num_frac_parts > 1 else "",
 152                    ts_frac_parts[1] if num_frac_parts > 1 else "",  # utc offset (if present)
 153                ]
 154            )
 155
 156        # return literal with no timezone, eg turn '2020-01-01 12:13:14-08:00' into '2020-01-01 12:13:14'
 157        # this is because Clickhouse encodes the timezone as a data type parameter and throws an error if
 158        # it's part of the timestamp string
 159        ts_without_tz = (
 160            datetime.datetime.fromisoformat(ts_string).replace(tzinfo=None).isoformat(sep=" ")
 161        )
 162        ts = exp.Literal.string(ts_without_tz)
 163
 164    # Non-nullable DateTime64 with microsecond precision
 165    expressions = [exp.DataTypeParam(this=tz)] if tz else []
 166    datatype = exp.DataType.build(
 167        exp.DataType.Type.DATETIME64,
 168        expressions=[exp.DataTypeParam(this=exp.Literal.number(6)), *expressions],
 169        nullable=False,
 170    )
 171
 172    return self.sql(exp.cast(ts, datatype, dialect=self.dialect))
 173
 174
 175def _map_sql(self: ClickHouse.Generator, expression: exp.Map | exp.VarMap) -> str:
 176    if not (expression.parent and expression.parent.arg_key == "settings"):
 177        return _lower_func(var_map_sql(self, expression))
 178
 179    keys = expression.args.get("keys")
 180    values = expression.args.get("values")
 181
 182    if not isinstance(keys, exp.Array) or not isinstance(values, exp.Array):
 183        self.unsupported("Cannot convert array columns into map.")
 184        return ""
 185
 186    args = []
 187    for key, value in zip(keys.expressions, values.expressions):
 188        args.append(f"{self.sql(key)}: {self.sql(value)}")
 189
 190    csv_args = ", ".join(args)
 191
 192    return f"{{{csv_args}}}"
 193
 194
 195def _build_timestamp_trunc(unit: str) -> t.Callable[[t.List], exp.TimestampTrunc]:
 196    return lambda args: exp.TimestampTrunc(
 197        this=seq_get(args, 0), unit=exp.var(unit), zone=seq_get(args, 1)
 198    )
 199
 200
 201def _build_split_by_char(args: t.List) -> exp.Split | exp.Anonymous:
 202    sep = seq_get(args, 0)
 203    if isinstance(sep, exp.Literal):
 204        sep_value = sep.to_py()
 205        if isinstance(sep_value, str) and len(sep_value.encode("utf-8")) == 1:
 206            return _build_split(exp.Split)(args)
 207
 208    return exp.Anonymous(this="splitByChar", expressions=args)
 209
 210
 211def _build_split(exp_class: t.Type[E]) -> t.Callable[[t.List], E]:
 212    return lambda args: exp_class(
 213        this=seq_get(args, 1), expression=seq_get(args, 0), limit=seq_get(args, 2)
 214    )
 215
 216
 217# Skip the 'week' unit since ClickHouse's toStartOfWeek
 218# uses an extra mode argument to specify the first day of the week
 219TIMESTAMP_TRUNC_UNITS = {
 220    "MICROSECOND",
 221    "MILLISECOND",
 222    "SECOND",
 223    "MINUTE",
 224    "HOUR",
 225    "DAY",
 226    "MONTH",
 227    "QUARTER",
 228    "YEAR",
 229}
 230
 231
 232class ClickHouse(Dialect):
 233    INDEX_OFFSET = 1
 234    NORMALIZE_FUNCTIONS: bool | str = False
 235    NULL_ORDERING = "nulls_are_last"
 236    SUPPORTS_USER_DEFINED_TYPES = False
 237    SAFE_DIVISION = True
 238    LOG_BASE_FIRST: t.Optional[bool] = None
 239    FORCE_EARLY_ALIAS_REF_EXPANSION = True
 240    PRESERVE_ORIGINAL_NAMES = True
 241    NUMBERS_CAN_BE_UNDERSCORE_SEPARATED = True
 242    IDENTIFIERS_CAN_START_WITH_DIGIT = True
 243    HEX_STRING_IS_INTEGER_TYPE = True
 244
 245    # https://github.com/ClickHouse/ClickHouse/issues/33935#issue-1112165779
 246    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_SENSITIVE
 247
 248    EXPRESSION_METADATA = EXPRESSION_METADATA.copy()
 249
 250    UNESCAPED_SEQUENCES = {
 251        "\\0": "\0",
 252    }
 253
 254    CREATABLE_KIND_MAPPING = {"DATABASE": "SCHEMA"}
 255
 256    SET_OP_DISTINCT_BY_DEFAULT: t.Dict[t.Type[exp.Expression], t.Optional[bool]] = {
 257        exp.Except: False,
 258        exp.Intersect: False,
 259        exp.Union: None,
 260    }
 261
 262    def generate_values_aliases(self, expression: exp.Values) -> t.List[exp.Identifier]:
 263        # Clickhouse allows VALUES to have an embedded structure e.g:
 264        # VALUES('person String, place String', ('Noah', 'Paris'), ...)
 265        # In this case, we don't want to qualify the columns
 266        values = expression.expressions[0].expressions
 267
 268        structure = (
 269            values[0]
 270            if (len(values) > 1 and values[0].is_string and isinstance(values[1], exp.Tuple))
 271            else None
 272        )
 273        if structure:
 274            # Split each column definition into the column name e.g:
 275            # 'person String, place String' -> ['person', 'place']
 276            structure_coldefs = [coldef.strip() for coldef in structure.name.split(",")]
 277            column_aliases = [
 278                exp.to_identifier(coldef.split(" ")[0]) for coldef in structure_coldefs
 279            ]
 280        else:
 281            # Default column aliases in CH are "c1", "c2", etc.
 282            column_aliases = [
 283                exp.to_identifier(f"c{i + 1}") for i in range(len(values[0].expressions))
 284            ]
 285
 286        return column_aliases
 287
 288    class Tokenizer(tokens.Tokenizer):
 289        COMMENTS = ["--", "#", "#!", ("/*", "*/")]
 290        IDENTIFIERS = ['"', "`"]
 291        IDENTIFIER_ESCAPES = ["\\"]
 292        STRING_ESCAPES = ["'", "\\"]
 293        BIT_STRINGS = [("0b", "")]
 294        HEX_STRINGS = [("0x", ""), ("0X", "")]
 295        HEREDOC_STRINGS = ["$"]
 296
 297        KEYWORDS = {
 298            **tokens.Tokenizer.KEYWORDS,
 299            ".:": TokenType.DOTCOLON,
 300            "ATTACH": TokenType.COMMAND,
 301            "DATE32": TokenType.DATE32,
 302            "DATETIME64": TokenType.DATETIME64,
 303            "DICTIONARY": TokenType.DICTIONARY,
 304            "DYNAMIC": TokenType.DYNAMIC,
 305            "ENUM8": TokenType.ENUM8,
 306            "ENUM16": TokenType.ENUM16,
 307            "EXCHANGE": TokenType.COMMAND,
 308            "FINAL": TokenType.FINAL,
 309            "FIXEDSTRING": TokenType.FIXEDSTRING,
 310            "FLOAT32": TokenType.FLOAT,
 311            "FLOAT64": TokenType.DOUBLE,
 312            "GLOBAL": TokenType.GLOBAL,
 313            "LOWCARDINALITY": TokenType.LOWCARDINALITY,
 314            "MAP": TokenType.MAP,
 315            "NESTED": TokenType.NESTED,
 316            "NOTHING": TokenType.NOTHING,
 317            "SAMPLE": TokenType.TABLE_SAMPLE,
 318            "TUPLE": TokenType.STRUCT,
 319            "UINT16": TokenType.USMALLINT,
 320            "UINT32": TokenType.UINT,
 321            "UINT64": TokenType.UBIGINT,
 322            "UINT8": TokenType.UTINYINT,
 323            "IPV4": TokenType.IPV4,
 324            "IPV6": TokenType.IPV6,
 325            "POINT": TokenType.POINT,
 326            "RING": TokenType.RING,
 327            "LINESTRING": TokenType.LINESTRING,
 328            "MULTILINESTRING": TokenType.MULTILINESTRING,
 329            "POLYGON": TokenType.POLYGON,
 330            "MULTIPOLYGON": TokenType.MULTIPOLYGON,
 331            "AGGREGATEFUNCTION": TokenType.AGGREGATEFUNCTION,
 332            "SIMPLEAGGREGATEFUNCTION": TokenType.SIMPLEAGGREGATEFUNCTION,
 333            "SYSTEM": TokenType.COMMAND,
 334            "PREWHERE": TokenType.PREWHERE,
 335        }
 336        KEYWORDS.pop("/*+")
 337
 338        SINGLE_TOKENS = {
 339            **tokens.Tokenizer.SINGLE_TOKENS,
 340            "$": TokenType.HEREDOC_STRING,
 341        }
 342
 343    class Parser(parser.Parser):
 344        # Tested in ClickHouse's playground, it seems that the following two queries do the same thing
 345        # * select x from t1 union all select x from t2 limit 1;
 346        # * select x from t1 union all (select x from t2 limit 1);
 347        MODIFIERS_ATTACHED_TO_SET_OP = False
 348        INTERVAL_SPANS = False
 349        OPTIONAL_ALIAS_TOKEN_CTE = False
 350        JOINS_HAVE_EQUAL_PRECEDENCE = True
 351
 352        FUNCTIONS = {
 353            **parser.Parser.FUNCTIONS,
 354            **{
 355                f"TOSTARTOF{unit}": _build_timestamp_trunc(unit=unit)
 356                for unit in TIMESTAMP_TRUNC_UNITS
 357            },
 358            "ANY": exp.AnyValue.from_arg_list,
 359            "ARRAYCOMPACT": exp.ArrayCompact.from_arg_list,
 360            "ARRAYCONCAT": exp.ArrayConcat.from_arg_list,
 361            "ARRAYDISTINCT": exp.ArrayDistinct.from_arg_list,
 362            "ARRAYSUM": exp.ArraySum.from_arg_list,
 363            "ARRAYMAX": exp.ArrayMax.from_arg_list,
 364            "ARRAYMIN": exp.ArrayMin.from_arg_list,
 365            "ARRAYREVERSE": exp.ArrayReverse.from_arg_list,
 366            "ARRAYSLICE": exp.ArraySlice.from_arg_list,
 367            "CURRENTDATABASE": exp.CurrentDatabase.from_arg_list,
 368            "CURRENTSCHEMAS": exp.CurrentSchemas.from_arg_list,
 369            "COUNTIF": _build_count_if,
 370            "COSINEDISTANCE": exp.CosineDistance.from_arg_list,
 371            "VERSION": exp.CurrentVersion.from_arg_list,
 372            "DATE_ADD": build_date_delta(exp.DateAdd, default_unit=None),
 373            "DATEADD": build_date_delta(exp.DateAdd, default_unit=None),
 374            "DATE_DIFF": build_date_delta(exp.DateDiff, default_unit=None, supports_timezone=True),
 375            "DATEDIFF": build_date_delta(exp.DateDiff, default_unit=None, supports_timezone=True),
 376            "DATE_FORMAT": _build_datetime_format(exp.TimeToStr),
 377            "DATE_SUB": build_date_delta(exp.DateSub, default_unit=None),
 378            "DATESUB": build_date_delta(exp.DateSub, default_unit=None),
 379            "FORMATDATETIME": _build_datetime_format(exp.TimeToStr),
 380            "HAS": exp.ArrayContains.from_arg_list,
 381            "ILIKE": build_like(exp.ILike),
 382            "JSONEXTRACTSTRING": build_json_extract_path(
 383                exp.JSONExtractScalar, zero_based_indexing=False
 384            ),
 385            "LENGTH": lambda args: exp.Length(this=seq_get(args, 0), binary=True),
 386            "LIKE": build_like(exp.Like),
 387            "L2Distance": exp.EuclideanDistance.from_arg_list,
 388            "MAP": parser.build_var_map,
 389            "MATCH": exp.RegexpLike.from_arg_list,
 390            "NOTLIKE": build_like(exp.Like, not_like=True),
 391            "PARSEDATETIME": _build_datetime_format(exp.ParseDatetime),
 392            "RANDCANONICAL": exp.Rand.from_arg_list,
 393            "STR_TO_DATE": _build_str_to_date,
 394            "TIMESTAMP_SUB": build_date_delta(exp.TimestampSub, default_unit=None),
 395            "TIMESTAMPSUB": build_date_delta(exp.TimestampSub, default_unit=None),
 396            "TIMESTAMP_ADD": build_date_delta(exp.TimestampAdd, default_unit=None),
 397            "TIMESTAMPADD": build_date_delta(exp.TimestampAdd, default_unit=None),
 398            "TOMONDAY": _build_timestamp_trunc("WEEK"),
 399            "UNIQ": exp.ApproxDistinct.from_arg_list,
 400            "XOR": lambda args: exp.Xor(expressions=args),
 401            "MD5": exp.MD5Digest.from_arg_list,
 402            "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)),
 403            "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)),
 404            "SPLITBYCHAR": _build_split_by_char,
 405            "SPLITBYREGEXP": _build_split(exp.RegexpSplit),
 406            "SPLITBYSTRING": _build_split(exp.Split),
 407            "SUBSTRINGINDEX": exp.SubstringIndex.from_arg_list,
 408            "TOTYPENAME": exp.Typeof.from_arg_list,
 409            "EDITDISTANCE": exp.Levenshtein.from_arg_list,
 410            "JAROWINKLERSIMILARITY": exp.JarowinklerSimilarity.from_arg_list,
 411            "LEVENSHTEINDISTANCE": exp.Levenshtein.from_arg_list,
 412            "UTCTIMESTAMP": exp.UtcTimestamp.from_arg_list,
 413        }
 414        FUNCTIONS.pop("TRANSFORM")
 415        FUNCTIONS.pop("APPROX_TOP_SUM")
 416
 417        AGG_FUNCTIONS = {
 418            "count",
 419            "min",
 420            "max",
 421            "sum",
 422            "avg",
 423            "any",
 424            "stddevPop",
 425            "stddevSamp",
 426            "varPop",
 427            "varSamp",
 428            "corr",
 429            "covarPop",
 430            "covarSamp",
 431            "entropy",
 432            "exponentialMovingAverage",
 433            "intervalLengthSum",
 434            "kolmogorovSmirnovTest",
 435            "mannWhitneyUTest",
 436            "median",
 437            "rankCorr",
 438            "sumKahan",
 439            "studentTTest",
 440            "welchTTest",
 441            "anyHeavy",
 442            "anyLast",
 443            "boundingRatio",
 444            "first_value",
 445            "last_value",
 446            "argMin",
 447            "argMax",
 448            "avgWeighted",
 449            "topK",
 450            "approx_top_sum",
 451            "topKWeighted",
 452            "deltaSum",
 453            "deltaSumTimestamp",
 454            "groupArray",
 455            "groupArrayLast",
 456            "groupUniqArray",
 457            "groupArrayInsertAt",
 458            "groupArrayMovingAvg",
 459            "groupArrayMovingSum",
 460            "groupArraySample",
 461            "groupBitAnd",
 462            "groupBitOr",
 463            "groupBitXor",
 464            "groupBitmap",
 465            "groupBitmapAnd",
 466            "groupBitmapOr",
 467            "groupBitmapXor",
 468            "sumWithOverflow",
 469            "sumMap",
 470            "minMap",
 471            "maxMap",
 472            "skewSamp",
 473            "skewPop",
 474            "kurtSamp",
 475            "kurtPop",
 476            "uniq",
 477            "uniqExact",
 478            "uniqCombined",
 479            "uniqCombined64",
 480            "uniqHLL12",
 481            "uniqTheta",
 482            "quantile",
 483            "quantiles",
 484            "quantileExact",
 485            "quantilesExact",
 486            "quantilesExactExclusive",
 487            "quantileExactLow",
 488            "quantilesExactLow",
 489            "quantileExactHigh",
 490            "quantilesExactHigh",
 491            "quantileExactWeighted",
 492            "quantilesExactWeighted",
 493            "quantileTiming",
 494            "quantilesTiming",
 495            "quantileTimingWeighted",
 496            "quantilesTimingWeighted",
 497            "quantileDeterministic",
 498            "quantilesDeterministic",
 499            "quantileTDigest",
 500            "quantilesTDigest",
 501            "quantileTDigestWeighted",
 502            "quantilesTDigestWeighted",
 503            "quantileBFloat16",
 504            "quantilesBFloat16",
 505            "quantileBFloat16Weighted",
 506            "quantilesBFloat16Weighted",
 507            "simpleLinearRegression",
 508            "stochasticLinearRegression",
 509            "stochasticLogisticRegression",
 510            "categoricalInformationValue",
 511            "contingency",
 512            "cramersV",
 513            "cramersVBiasCorrected",
 514            "theilsU",
 515            "maxIntersections",
 516            "maxIntersectionsPosition",
 517            "meanZTest",
 518            "quantileInterpolatedWeighted",
 519            "quantilesInterpolatedWeighted",
 520            "quantileGK",
 521            "quantilesGK",
 522            "sparkBar",
 523            "sumCount",
 524            "largestTriangleThreeBuckets",
 525            "histogram",
 526            "sequenceMatch",
 527            "sequenceCount",
 528            "windowFunnel",
 529            "retention",
 530            "uniqUpTo",
 531            "sequenceNextNode",
 532            "exponentialTimeDecayedAvg",
 533        }
 534
 535        AGG_FUNCTIONS_SUFFIXES = [
 536            "If",
 537            "Array",
 538            "ArrayIf",
 539            "Map",
 540            "SimpleState",
 541            "State",
 542            "Merge",
 543            "MergeState",
 544            "ForEach",
 545            "Distinct",
 546            "OrDefault",
 547            "OrNull",
 548            "Resample",
 549            "ArgMin",
 550            "ArgMax",
 551        ]
 552
 553        FUNC_TOKENS = {
 554            *parser.Parser.FUNC_TOKENS,
 555            TokenType.AND,
 556            TokenType.OR,
 557            TokenType.SET,
 558        }
 559
 560        RESERVED_TOKENS = parser.Parser.RESERVED_TOKENS - {TokenType.SELECT}
 561
 562        ID_VAR_TOKENS = {
 563            *parser.Parser.ID_VAR_TOKENS,
 564            TokenType.LIKE,
 565        }
 566
 567        AGG_FUNC_MAPPING = (
 568            lambda functions, suffixes: {
 569                f"{f}{sfx}": (f, sfx) for sfx in (suffixes + [""]) for f in functions
 570            }
 571        )(AGG_FUNCTIONS, AGG_FUNCTIONS_SUFFIXES)
 572
 573        FUNCTION_PARSERS = {
 574            **parser.Parser.FUNCTION_PARSERS,
 575            "ARRAYJOIN": lambda self: self.expression(exp.Explode, this=self._parse_expression()),
 576            "QUANTILE": lambda self: self._parse_quantile(),
 577            "MEDIAN": lambda self: self._parse_quantile(),
 578            "COLUMNS": lambda self: self._parse_columns(),
 579            "TUPLE": lambda self: exp.Struct.from_arg_list(self._parse_function_args(alias=True)),
 580            "AND": lambda self: exp.and_(*self._parse_function_args(alias=False)),
 581            "OR": lambda self: exp.or_(*self._parse_function_args(alias=False)),
 582        }
 583
 584        FUNCTION_PARSERS.pop("MATCH")
 585
 586        PROPERTY_PARSERS = {
 587            **parser.Parser.PROPERTY_PARSERS,
 588            "ENGINE": lambda self: self._parse_engine_property(),
 589        }
 590        PROPERTY_PARSERS.pop("DYNAMIC")
 591
 592        NO_PAREN_FUNCTION_PARSERS = parser.Parser.NO_PAREN_FUNCTION_PARSERS.copy()
 593        NO_PAREN_FUNCTION_PARSERS.pop("ANY")
 594
 595        NO_PAREN_FUNCTIONS = parser.Parser.NO_PAREN_FUNCTIONS.copy()
 596        NO_PAREN_FUNCTIONS.pop(TokenType.CURRENT_TIMESTAMP)
 597
 598        RANGE_PARSERS = {
 599            **parser.Parser.RANGE_PARSERS,
 600            TokenType.GLOBAL: lambda self, this: self._parse_global_in(this),
 601        }
 602
 603        # The PLACEHOLDER entry is popped because 1) it doesn't affect Clickhouse (it corresponds to
 604        # the postgres-specific JSONBContains parser) and 2) it makes parsing the ternary op simpler.
 605        COLUMN_OPERATORS = parser.Parser.COLUMN_OPERATORS.copy()
 606        COLUMN_OPERATORS.pop(TokenType.PLACEHOLDER)
 607
 608        JOIN_KINDS = {
 609            *parser.Parser.JOIN_KINDS,
 610            TokenType.ANY,
 611            TokenType.ASOF,
 612            TokenType.ARRAY,
 613        }
 614
 615        TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS - {
 616            TokenType.ANY,
 617            TokenType.ARRAY,
 618            TokenType.FINAL,
 619            TokenType.FORMAT,
 620            TokenType.SETTINGS,
 621        }
 622
 623        ALIAS_TOKENS = parser.Parser.ALIAS_TOKENS - {
 624            TokenType.FORMAT,
 625        }
 626
 627        LOG_DEFAULTS_TO_LN = True
 628
 629        QUERY_MODIFIER_PARSERS = {
 630            **parser.Parser.QUERY_MODIFIER_PARSERS,
 631            TokenType.SETTINGS: lambda self: (
 632                "settings",
 633                self._advance() or self._parse_csv(self._parse_assignment),
 634            ),
 635            TokenType.FORMAT: lambda self: ("format", self._advance() or self._parse_id_var()),
 636        }
 637
 638        CONSTRAINT_PARSERS = {
 639            **parser.Parser.CONSTRAINT_PARSERS,
 640            "INDEX": lambda self: self._parse_index_constraint(),
 641            "CODEC": lambda self: self._parse_compress(),
 642        }
 643
 644        ALTER_PARSERS = {
 645            **parser.Parser.ALTER_PARSERS,
 646            "REPLACE": lambda self: self._parse_alter_table_replace(),
 647        }
 648
 649        SCHEMA_UNNAMED_CONSTRAINTS = {
 650            *parser.Parser.SCHEMA_UNNAMED_CONSTRAINTS,
 651            "INDEX",
 652        }
 653
 654        PLACEHOLDER_PARSERS = {
 655            **parser.Parser.PLACEHOLDER_PARSERS,
 656            TokenType.L_BRACE: lambda self: self._parse_query_parameter(),
 657        }
 658
 659        def _parse_engine_property(self) -> exp.EngineProperty:
 660            self._match(TokenType.EQ)
 661            return self.expression(
 662                exp.EngineProperty,
 663                this=self._parse_field(any_token=True, anonymous_func=True),
 664            )
 665
 666        # https://clickhouse.com/docs/en/sql-reference/statements/create/function
 667        def _parse_user_defined_function_expression(self) -> t.Optional[exp.Expression]:
 668            return self._parse_lambda()
 669
 670        def _parse_types(
 671            self, check_func: bool = False, schema: bool = False, allow_identifiers: bool = True
 672        ) -> t.Optional[exp.Expression]:
 673            dtype = super()._parse_types(
 674                check_func=check_func, schema=schema, allow_identifiers=allow_identifiers
 675            )
 676            if isinstance(dtype, exp.DataType) and dtype.args.get("nullable") is not True:
 677                # Mark every type as non-nullable which is ClickHouse's default, unless it's
 678                # already marked as nullable. This marker helps us transpile types from other
 679                # dialects to ClickHouse, so that we can e.g. produce `CAST(x AS Nullable(String))`
 680                # from `CAST(x AS TEXT)`. If there is a `NULL` value in `x`, the former would
 681                # fail in ClickHouse without the `Nullable` type constructor.
 682                dtype.set("nullable", False)
 683
 684            return dtype
 685
 686        def _parse_extract(self) -> exp.Extract | exp.Anonymous:
 687            index = self._index
 688            this = self._parse_bitwise()
 689            if self._match(TokenType.FROM):
 690                self._retreat(index)
 691                return super()._parse_extract()
 692
 693            # We return Anonymous here because extract and regexpExtract have different semantics,
 694            # so parsing extract(foo, bar) into RegexpExtract can potentially break queries. E.g.,
 695            # `extract('foobar', 'b')` works, but ClickHouse crashes for `regexpExtract('foobar', 'b')`.
 696            #
 697            # TODO: can we somehow convert the former into an equivalent `regexpExtract` call?
 698            self._match(TokenType.COMMA)
 699            return self.expression(
 700                exp.Anonymous, this="extract", expressions=[this, self._parse_bitwise()]
 701            )
 702
 703        def _parse_assignment(self) -> t.Optional[exp.Expression]:
 704            this = super()._parse_assignment()
 705
 706            if self._match(TokenType.PLACEHOLDER):
 707                return self.expression(
 708                    exp.If,
 709                    this=this,
 710                    true=self._parse_assignment(),
 711                    false=self._match(TokenType.COLON) and self._parse_assignment(),
 712                )
 713
 714            return this
 715
 716        def _parse_query_parameter(self) -> t.Optional[exp.Expression]:
 717            """
 718            Parse a placeholder expression like SELECT {abc: UInt32} or FROM {table: Identifier}
 719            https://clickhouse.com/docs/en/sql-reference/syntax#defining-and-using-query-parameters
 720            """
 721            index = self._index
 722
 723            this = self._parse_id_var()
 724            self._match(TokenType.COLON)
 725            kind = self._parse_types(check_func=False, allow_identifiers=False) or (
 726                self._match_text_seq("IDENTIFIER") and "Identifier"
 727            )
 728
 729            if not kind:
 730                self._retreat(index)
 731                return None
 732            elif not self._match(TokenType.R_BRACE):
 733                self.raise_error("Expecting }")
 734
 735            if isinstance(this, exp.Identifier) and not this.quoted:
 736                this = exp.var(this.name)
 737
 738            return self.expression(exp.Placeholder, this=this, kind=kind)
 739
 740        def _parse_bracket(
 741            self, this: t.Optional[exp.Expression] = None
 742        ) -> t.Optional[exp.Expression]:
 743            l_brace = self._match(TokenType.L_BRACE, advance=False)
 744            bracket = super()._parse_bracket(this)
 745
 746            if l_brace and isinstance(bracket, exp.Struct):
 747                varmap = exp.VarMap(keys=exp.Array(), values=exp.Array())
 748                for expression in bracket.expressions:
 749                    if not isinstance(expression, exp.PropertyEQ):
 750                        break
 751
 752                    varmap.args["keys"].append("expressions", exp.Literal.string(expression.name))
 753                    varmap.args["values"].append("expressions", expression.expression)
 754
 755                return varmap
 756
 757            return bracket
 758
 759        def _parse_in(self, this: t.Optional[exp.Expression], is_global: bool = False) -> exp.In:
 760            this = super()._parse_in(this)
 761            this.set("is_global", is_global)
 762            return this
 763
 764        def _parse_global_in(self, this: t.Optional[exp.Expression]) -> exp.Not | exp.In:
 765            is_negated = self._match(TokenType.NOT)
 766            in_expr = self._parse_in(this, is_global=True) if self._match(TokenType.IN) else None
 767            return self.expression(exp.Not, this=in_expr) if is_negated else t.cast(exp.In, in_expr)
 768
 769        def _parse_table(
 770            self,
 771            schema: bool = False,
 772            joins: bool = False,
 773            alias_tokens: t.Optional[t.Collection[TokenType]] = None,
 774            parse_bracket: bool = False,
 775            is_db_reference: bool = False,
 776            parse_partition: bool = False,
 777            consume_pipe: bool = False,
 778        ) -> t.Optional[exp.Expression]:
 779            this = super()._parse_table(
 780                schema=schema,
 781                joins=joins,
 782                alias_tokens=alias_tokens,
 783                parse_bracket=parse_bracket,
 784                is_db_reference=is_db_reference,
 785            )
 786
 787            if isinstance(this, exp.Table):
 788                inner = this.this
 789                alias = this.args.get("alias")
 790
 791                if isinstance(inner, exp.GenerateSeries) and alias and not alias.columns:
 792                    alias.set("columns", [exp.to_identifier("generate_series")])
 793
 794            if self._match(TokenType.FINAL):
 795                this = self.expression(exp.Final, this=this)
 796
 797            return this
 798
 799        def _parse_position(self, haystack_first: bool = False) -> exp.StrPosition:
 800            return super()._parse_position(haystack_first=True)
 801
 802        # https://clickhouse.com/docs/en/sql-reference/statements/select/with/
 803        def _parse_cte(self) -> t.Optional[exp.CTE]:
 804            # WITH <identifier> AS <subquery expression>
 805            cte: t.Optional[exp.CTE] = self._try_parse(super()._parse_cte)
 806
 807            if not cte:
 808                # WITH <expression> AS <identifier>
 809                cte = self.expression(
 810                    exp.CTE,
 811                    this=self._parse_assignment(),
 812                    alias=self._parse_table_alias(),
 813                    scalar=True,
 814                )
 815
 816            return cte
 817
 818        def _parse_join_parts(
 819            self,
 820        ) -> t.Tuple[t.Optional[Token], t.Optional[Token], t.Optional[Token]]:
 821            is_global = self._prev if self._match(TokenType.GLOBAL) else None
 822            kind_pre = self._prev if self._match_set(self.JOIN_KINDS, advance=False) else None
 823
 824            if kind_pre:
 825                kind = self._prev if self._match_set(self.JOIN_KINDS) else None
 826                side = self._prev if self._match_set(self.JOIN_SIDES) else None
 827                return is_global, side, kind
 828
 829            return (
 830                is_global,
 831                self._prev if self._match_set(self.JOIN_SIDES) else None,
 832                self._prev if self._match_set(self.JOIN_KINDS) else None,
 833            )
 834
 835        def _parse_join(
 836            self, skip_join_token: bool = False, parse_bracket: bool = False
 837        ) -> t.Optional[exp.Join]:
 838            join = super()._parse_join(skip_join_token=skip_join_token, parse_bracket=True)
 839            if join:
 840                method = join.args.get("method")
 841                join.set("method", None)
 842                join.set("global_", method)
 843
 844                # tbl ARRAY JOIN arr <-- this should be a `Column` reference, not a `Table`
 845                # https://clickhouse.com/docs/en/sql-reference/statements/select/array-join
 846                if join.kind == "ARRAY":
 847                    for table in join.find_all(exp.Table):
 848                        table.replace(table.to_column())
 849
 850            return join
 851
 852        def _parse_function(
 853            self,
 854            functions: t.Optional[t.Dict[str, t.Callable]] = None,
 855            anonymous: bool = False,
 856            optional_parens: bool = True,
 857            any_token: bool = False,
 858        ) -> t.Optional[exp.Expression]:
 859            expr = super()._parse_function(
 860                functions=functions,
 861                anonymous=anonymous,
 862                optional_parens=optional_parens,
 863                any_token=any_token,
 864            )
 865
 866            func = expr.this if isinstance(expr, exp.Window) else expr
 867
 868            # Aggregate functions can be split in 2 parts: <func_name><suffix>
 869            parts = (
 870                self.AGG_FUNC_MAPPING.get(func.this) if isinstance(func, exp.Anonymous) else None
 871            )
 872
 873            if parts:
 874                anon_func: exp.Anonymous = t.cast(exp.Anonymous, func)
 875                params = self._parse_func_params(anon_func)
 876
 877                kwargs = {
 878                    "this": anon_func.this,
 879                    "expressions": anon_func.expressions,
 880                }
 881                if parts[1]:
 882                    exp_class: t.Type[exp.Expression] = (
 883                        exp.CombinedParameterizedAgg if params else exp.CombinedAggFunc
 884                    )
 885                else:
 886                    exp_class = exp.ParameterizedAgg if params else exp.AnonymousAggFunc
 887
 888                kwargs["exp_class"] = exp_class
 889                if params:
 890                    kwargs["params"] = params
 891
 892                func = self.expression(**kwargs)
 893
 894                if isinstance(expr, exp.Window):
 895                    # The window's func was parsed as Anonymous in base parser, fix its
 896                    # type to be ClickHouse style CombinedAnonymousAggFunc / AnonymousAggFunc
 897                    expr.set("this", func)
 898                elif params:
 899                    # Params have blocked super()._parse_function() from parsing the following window
 900                    # (if that exists) as they're standing between the function call and the window spec
 901                    expr = self._parse_window(func)
 902                else:
 903                    expr = func
 904
 905            return expr
 906
 907        def _parse_func_params(
 908            self, this: t.Optional[exp.Func] = None
 909        ) -> t.Optional[t.List[exp.Expression]]:
 910            if self._match_pair(TokenType.R_PAREN, TokenType.L_PAREN):
 911                return self._parse_csv(self._parse_lambda)
 912
 913            if self._match(TokenType.L_PAREN):
 914                params = self._parse_csv(self._parse_lambda)
 915                self._match_r_paren(this)
 916                return params
 917
 918            return None
 919
 920        def _parse_quantile(self) -> exp.Quantile:
 921            this = self._parse_lambda()
 922            params = self._parse_func_params()
 923            if params:
 924                return self.expression(exp.Quantile, this=params[0], quantile=this)
 925            return self.expression(exp.Quantile, this=this, quantile=exp.Literal.number(0.5))
 926
 927        def _parse_wrapped_id_vars(self, optional: bool = False) -> t.List[exp.Expression]:
 928            return super()._parse_wrapped_id_vars(optional=True)
 929
 930        def _parse_primary_key(
 931            self,
 932            wrapped_optional: bool = False,
 933            in_props: bool = False,
 934            named_primary_key: bool = False,
 935        ) -> exp.PrimaryKeyColumnConstraint | exp.PrimaryKey:
 936            return super()._parse_primary_key(
 937                wrapped_optional=wrapped_optional or in_props,
 938                in_props=in_props,
 939                named_primary_key=named_primary_key,
 940            )
 941
 942        def _parse_on_property(self) -> t.Optional[exp.Expression]:
 943            index = self._index
 944            if self._match_text_seq("CLUSTER"):
 945                this = self._parse_string() or self._parse_id_var()
 946                if this:
 947                    return self.expression(exp.OnCluster, this=this)
 948                else:
 949                    self._retreat(index)
 950            return None
 951
 952        def _parse_index_constraint(
 953            self, kind: t.Optional[str] = None
 954        ) -> exp.IndexColumnConstraint:
 955            # INDEX name1 expr TYPE type1(args) GRANULARITY value
 956            this = self._parse_id_var()
 957            expression = self._parse_assignment()
 958
 959            index_type = self._match_text_seq("TYPE") and (
 960                self._parse_function() or self._parse_var()
 961            )
 962
 963            granularity = self._match_text_seq("GRANULARITY") and self._parse_term()
 964
 965            return self.expression(
 966                exp.IndexColumnConstraint,
 967                this=this,
 968                expression=expression,
 969                index_type=index_type,
 970                granularity=granularity,
 971            )
 972
 973        def _parse_partition(self) -> t.Optional[exp.Partition]:
 974            # https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#how-to-set-partition-expression
 975            if not self._match(TokenType.PARTITION):
 976                return None
 977
 978            if self._match_text_seq("ID"):
 979                # Corresponds to the PARTITION ID <string_value> syntax
 980                expressions: t.List[exp.Expression] = [
 981                    self.expression(exp.PartitionId, this=self._parse_string())
 982                ]
 983            else:
 984                expressions = self._parse_expressions()
 985
 986            return self.expression(exp.Partition, expressions=expressions)
 987
 988        def _parse_alter_table_replace(self) -> t.Optional[exp.Expression]:
 989            partition = self._parse_partition()
 990
 991            if not partition or not self._match(TokenType.FROM):
 992                return None
 993
 994            return self.expression(
 995                exp.ReplacePartition, expression=partition, source=self._parse_table_parts()
 996            )
 997
 998        def _parse_projection_def(self) -> t.Optional[exp.ProjectionDef]:
 999            if not self._match_text_seq("PROJECTION"):
1000                return None
1001
1002            return self.expression(
1003                exp.ProjectionDef,
1004                this=self._parse_id_var(),
1005                expression=self._parse_wrapped(self._parse_statement),
1006            )
1007
1008        def _parse_constraint(self) -> t.Optional[exp.Expression]:
1009            return super()._parse_constraint() or self._parse_projection_def()
1010
1011        def _parse_alias(
1012            self, this: t.Optional[exp.Expression], explicit: bool = False
1013        ) -> t.Optional[exp.Expression]:
1014            # In clickhouse "SELECT <expr> APPLY(...)" is a query modifier,
1015            # so "APPLY" shouldn't be parsed as <expr>'s alias. However, "SELECT <expr> apply" is a valid alias
1016            if self._match_pair(TokenType.APPLY, TokenType.L_PAREN, advance=False):
1017                return this
1018
1019            return super()._parse_alias(this=this, explicit=explicit)
1020
1021        def _parse_expression(self) -> t.Optional[exp.Expression]:
1022            this = super()._parse_expression()
1023
1024            # Clickhouse allows "SELECT <expr> [APPLY(func)] [...]]" modifier
1025            while self._match_pair(TokenType.APPLY, TokenType.L_PAREN):
1026                this = exp.Apply(this=this, expression=self._parse_var(any_token=True))
1027                self._match(TokenType.R_PAREN)
1028
1029            return this
1030
1031        def _parse_columns(self) -> exp.Expression:
1032            this: exp.Expression = self.expression(exp.Columns, this=self._parse_lambda())
1033
1034            while self._next and self._match_text_seq(")", "APPLY", "("):
1035                self._match(TokenType.R_PAREN)
1036                this = exp.Apply(this=this, expression=self._parse_var(any_token=True))
1037            return this
1038
1039        def _parse_value(self, values: bool = True) -> t.Optional[exp.Tuple]:
1040            value = super()._parse_value(values=values)
1041            if not value:
1042                return None
1043
1044            # In Clickhouse "SELECT * FROM VALUES (1, 2, 3)" generates a table with a single column, in contrast
1045            # to other dialects. For this case, we canonicalize the values into a tuple-of-tuples AST if it's not already one.
1046            # In INSERT INTO statements the same clause actually references multiple columns (opposite semantics),
1047            # but the final result is not altered by the extra parentheses.
1048            # Note: Clickhouse allows VALUES([structure], value, ...) so the branch checks for the last expression
1049            expressions = value.expressions
1050            if values and not isinstance(expressions[-1], exp.Tuple):
1051                value.set(
1052                    "expressions",
1053                    [self.expression(exp.Tuple, expressions=[expr]) for expr in expressions],
1054                )
1055
1056            return value
1057
1058        def _parse_partitioned_by(self) -> exp.PartitionedByProperty:
1059            # ClickHouse allows custom expressions as partition key
1060            # https://clickhouse.com/docs/engines/table-engines/mergetree-family/custom-partitioning-key
1061            return self.expression(
1062                exp.PartitionedByProperty,
1063                this=self._parse_assignment(),
1064            )
1065
1066    class Generator(generator.Generator):
1067        QUERY_HINTS = False
1068        STRUCT_DELIMITER = ("(", ")")
1069        NVL2_SUPPORTED = False
1070        TABLESAMPLE_REQUIRES_PARENS = False
1071        TABLESAMPLE_SIZE_IS_ROWS = False
1072        TABLESAMPLE_KEYWORDS = "SAMPLE"
1073        LAST_DAY_SUPPORTS_DATE_PART = False
1074        CAN_IMPLEMENT_ARRAY_ANY = True
1075        SUPPORTS_TO_NUMBER = False
1076        JOIN_HINTS = False
1077        TABLE_HINTS = False
1078        GROUPINGS_SEP = ""
1079        SET_OP_MODIFIERS = False
1080        ARRAY_SIZE_NAME = "LENGTH"
1081        WRAP_DERIVED_VALUES = False
1082
1083        STRING_TYPE_MAPPING = {
1084            exp.DataType.Type.BLOB: "String",
1085            exp.DataType.Type.CHAR: "String",
1086            exp.DataType.Type.LONGBLOB: "String",
1087            exp.DataType.Type.LONGTEXT: "String",
1088            exp.DataType.Type.MEDIUMBLOB: "String",
1089            exp.DataType.Type.MEDIUMTEXT: "String",
1090            exp.DataType.Type.TINYBLOB: "String",
1091            exp.DataType.Type.TINYTEXT: "String",
1092            exp.DataType.Type.TEXT: "String",
1093            exp.DataType.Type.VARBINARY: "String",
1094            exp.DataType.Type.VARCHAR: "String",
1095        }
1096
1097        SUPPORTED_JSON_PATH_PARTS = {
1098            exp.JSONPathKey,
1099            exp.JSONPathRoot,
1100            exp.JSONPathSubscript,
1101        }
1102
1103        TYPE_MAPPING = {
1104            **generator.Generator.TYPE_MAPPING,
1105            **STRING_TYPE_MAPPING,
1106            exp.DataType.Type.ARRAY: "Array",
1107            exp.DataType.Type.BOOLEAN: "Bool",
1108            exp.DataType.Type.BIGINT: "Int64",
1109            exp.DataType.Type.DATE32: "Date32",
1110            exp.DataType.Type.DATETIME: "DateTime",
1111            exp.DataType.Type.DATETIME2: "DateTime",
1112            exp.DataType.Type.SMALLDATETIME: "DateTime",
1113            exp.DataType.Type.DATETIME64: "DateTime64",
1114            exp.DataType.Type.DECIMAL: "Decimal",
1115            exp.DataType.Type.DECIMAL32: "Decimal32",
1116            exp.DataType.Type.DECIMAL64: "Decimal64",
1117            exp.DataType.Type.DECIMAL128: "Decimal128",
1118            exp.DataType.Type.DECIMAL256: "Decimal256",
1119            exp.DataType.Type.TIMESTAMP: "DateTime",
1120            exp.DataType.Type.TIMESTAMPNTZ: "DateTime",
1121            exp.DataType.Type.TIMESTAMPTZ: "DateTime",
1122            exp.DataType.Type.DOUBLE: "Float64",
1123            exp.DataType.Type.ENUM: "Enum",
1124            exp.DataType.Type.ENUM8: "Enum8",
1125            exp.DataType.Type.ENUM16: "Enum16",
1126            exp.DataType.Type.FIXEDSTRING: "FixedString",
1127            exp.DataType.Type.FLOAT: "Float32",
1128            exp.DataType.Type.INT: "Int32",
1129            exp.DataType.Type.MEDIUMINT: "Int32",
1130            exp.DataType.Type.INT128: "Int128",
1131            exp.DataType.Type.INT256: "Int256",
1132            exp.DataType.Type.LOWCARDINALITY: "LowCardinality",
1133            exp.DataType.Type.MAP: "Map",
1134            exp.DataType.Type.NESTED: "Nested",
1135            exp.DataType.Type.NOTHING: "Nothing",
1136            exp.DataType.Type.SMALLINT: "Int16",
1137            exp.DataType.Type.STRUCT: "Tuple",
1138            exp.DataType.Type.TINYINT: "Int8",
1139            exp.DataType.Type.UBIGINT: "UInt64",
1140            exp.DataType.Type.UINT: "UInt32",
1141            exp.DataType.Type.UINT128: "UInt128",
1142            exp.DataType.Type.UINT256: "UInt256",
1143            exp.DataType.Type.USMALLINT: "UInt16",
1144            exp.DataType.Type.UTINYINT: "UInt8",
1145            exp.DataType.Type.IPV4: "IPv4",
1146            exp.DataType.Type.IPV6: "IPv6",
1147            exp.DataType.Type.POINT: "Point",
1148            exp.DataType.Type.RING: "Ring",
1149            exp.DataType.Type.LINESTRING: "LineString",
1150            exp.DataType.Type.MULTILINESTRING: "MultiLineString",
1151            exp.DataType.Type.POLYGON: "Polygon",
1152            exp.DataType.Type.MULTIPOLYGON: "MultiPolygon",
1153            exp.DataType.Type.AGGREGATEFUNCTION: "AggregateFunction",
1154            exp.DataType.Type.SIMPLEAGGREGATEFUNCTION: "SimpleAggregateFunction",
1155            exp.DataType.Type.DYNAMIC: "Dynamic",
1156        }
1157
1158        TRANSFORMS = {
1159            **generator.Generator.TRANSFORMS,
1160            exp.AnyValue: rename_func("any"),
1161            exp.ApproxDistinct: rename_func("uniq"),
1162            exp.ArrayConcat: rename_func("arrayConcat"),
1163            exp.ArrayContains: rename_func("has"),
1164            exp.ArrayFilter: lambda self, e: self.func("arrayFilter", e.expression, e.this),
1165            exp.ArrayRemove: remove_from_array_using_filter,
1166            exp.ArrayReverse: rename_func("arrayReverse"),
1167            exp.ArraySlice: rename_func("arraySlice"),
1168            exp.ArraySum: rename_func("arraySum"),
1169            exp.ArrayMax: rename_func("arrayMax"),
1170            exp.ArrayMin: rename_func("arrayMin"),
1171            exp.ArgMax: arg_max_or_min_no_count("argMax"),
1172            exp.ArgMin: arg_max_or_min_no_count("argMin"),
1173            exp.Array: inline_array_sql,
1174            exp.CastToStrType: rename_func("CAST"),
1175            exp.CurrentDatabase: rename_func("CURRENT_DATABASE"),
1176            exp.CurrentSchemas: rename_func("CURRENT_SCHEMAS"),
1177            exp.CountIf: rename_func("countIf"),
1178            exp.CosineDistance: rename_func("cosineDistance"),
1179            exp.CompressColumnConstraint: lambda self,
1180            e: f"CODEC({self.expressions(e, key='this', flat=True)})",
1181            exp.ComputedColumnConstraint: lambda self,
1182            e: f"{'MATERIALIZED' if e.args.get('persisted') else 'ALIAS'} {self.sql(e, 'this')}",
1183            exp.CurrentDate: lambda self, e: self.func("CURRENT_DATE"),
1184            exp.CurrentVersion: rename_func("VERSION"),
1185            exp.DateAdd: _datetime_delta_sql("DATE_ADD"),
1186            exp.DateDiff: _datetime_delta_sql("DATE_DIFF"),
1187            exp.DateStrToDate: rename_func("toDate"),
1188            exp.DateSub: _datetime_delta_sql("DATE_SUB"),
1189            exp.Explode: rename_func("arrayJoin"),
1190            exp.FarmFingerprint: rename_func("farmFingerprint64"),
1191            exp.Final: lambda self, e: f"{self.sql(e, 'this')} FINAL",
1192            exp.IsNan: rename_func("isNaN"),
1193            exp.JarowinklerSimilarity: jarowinkler_similarity("jaroWinklerSimilarity"),
1194            exp.JSONCast: lambda self, e: f"{self.sql(e, 'this')}.:{self.sql(e, 'to')}",
1195            exp.JSONExtract: json_extract_segments("JSONExtractString", quoted_index=False),
1196            exp.JSONExtractScalar: json_extract_segments("JSONExtractString", quoted_index=False),
1197            exp.JSONPathKey: json_path_key_only_name,
1198            exp.JSONPathRoot: lambda *_: "",
1199            exp.Length: length_or_char_length_sql,
1200            exp.Map: _map_sql,
1201            exp.Median: rename_func("median"),
1202            exp.Nullif: rename_func("nullIf"),
1203            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
1204            exp.Pivot: no_pivot_sql,
1205            exp.Quantile: _quantile_sql,
1206            exp.RegexpLike: lambda self, e: self.func("match", e.this, e.expression),
1207            exp.Rand: rename_func("randCanonical"),
1208            exp.StartsWith: rename_func("startsWith"),
1209            exp.Struct: rename_func("tuple"),
1210            exp.Trunc: rename_func("trunc"),
1211            exp.EndsWith: rename_func("endsWith"),
1212            exp.EuclideanDistance: rename_func("L2Distance"),
1213            exp.StrPosition: lambda self, e: strposition_sql(
1214                self,
1215                e,
1216                func_name="POSITION",
1217                supports_position=True,
1218                use_ansi_position=False,
1219            ),
1220            exp.TimeToStr: lambda self, e: self.func(
1221                "formatDateTime", e.this, self.format_time(e), e.args.get("zone")
1222            ),
1223            exp.TimeStrToTime: _timestrtotime_sql,
1224            exp.TimestampAdd: _datetime_delta_sql("TIMESTAMP_ADD"),
1225            exp.TimestampSub: _datetime_delta_sql("TIMESTAMP_SUB"),
1226            exp.Typeof: rename_func("toTypeName"),
1227            exp.VarMap: _map_sql,
1228            exp.Xor: lambda self, e: self.func("xor", e.this, e.expression, *e.expressions),
1229            exp.MD5Digest: rename_func("MD5"),
1230            exp.MD5: lambda self, e: self.func("LOWER", self.func("HEX", self.func("MD5", e.this))),
1231            exp.SHA: rename_func("SHA1"),
1232            exp.SHA1Digest: rename_func("SHA1"),
1233            exp.SHA2: sha256_sql,
1234            exp.SHA2Digest: sha2_digest_sql,
1235            exp.Split: lambda self, e: self.func(
1236                "splitByString", e.args.get("expression"), e.this, e.args.get("limit")
1237            ),
1238            exp.RegexpSplit: lambda self, e: self.func(
1239                "splitByRegexp", e.args.get("expression"), e.this, e.args.get("limit")
1240            ),
1241            exp.UnixToTime: _unix_to_time_sql,
1242            exp.TimestampTrunc: timestamptrunc_sql(func="dateTrunc", zone=True),
1243            exp.Trim: lambda self, e: trim_sql(self, e, default_trim_type="BOTH"),
1244            exp.Variance: rename_func("varSamp"),
1245            exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
1246            exp.Stddev: rename_func("stddevSamp"),
1247            exp.Chr: rename_func("CHAR"),
1248            exp.Lag: lambda self, e: self.func(
1249                "lagInFrame", e.this, e.args.get("offset"), e.args.get("default")
1250            ),
1251            exp.Lead: lambda self, e: self.func(
1252                "leadInFrame", e.this, e.args.get("offset"), e.args.get("default")
1253            ),
1254            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
1255                rename_func("editDistance")
1256            ),
1257            exp.ParseDatetime: rename_func("parseDateTime"),
1258        }
1259
1260        PROPERTIES_LOCATION = {
1261            **generator.Generator.PROPERTIES_LOCATION,
1262            exp.OnCluster: exp.Properties.Location.POST_NAME,
1263            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
1264            exp.ToTableProperty: exp.Properties.Location.POST_NAME,
1265            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
1266        }
1267
1268        # There's no list in docs, but it can be found in Clickhouse code
1269        # see `ClickHouse/src/Parsers/ParserCreate*.cpp`
1270        ON_CLUSTER_TARGETS = {
1271            "SCHEMA",  # Transpiled CREATE SCHEMA may have OnCluster property set
1272            "DATABASE",
1273            "TABLE",
1274            "VIEW",
1275            "DICTIONARY",
1276            "INDEX",
1277            "FUNCTION",
1278            "NAMED COLLECTION",
1279        }
1280
1281        # https://clickhouse.com/docs/en/sql-reference/data-types/nullable
1282        NON_NULLABLE_TYPES = {
1283            exp.DataType.Type.ARRAY,
1284            exp.DataType.Type.MAP,
1285            exp.DataType.Type.STRUCT,
1286            exp.DataType.Type.POINT,
1287            exp.DataType.Type.RING,
1288            exp.DataType.Type.LINESTRING,
1289            exp.DataType.Type.MULTILINESTRING,
1290            exp.DataType.Type.POLYGON,
1291            exp.DataType.Type.MULTIPOLYGON,
1292        }
1293
1294        def offset_sql(self, expression: exp.Offset) -> str:
1295            offset = super().offset_sql(expression)
1296
1297            # OFFSET ... FETCH syntax requires a "ROW" or "ROWS" keyword
1298            # https://clickhouse.com/docs/sql-reference/statements/select/offset
1299            parent = expression.parent
1300            if isinstance(parent, exp.Select) and isinstance(parent.args.get("limit"), exp.Fetch):
1301                offset = f"{offset} ROWS"
1302
1303            return offset
1304
1305        def strtodate_sql(self, expression: exp.StrToDate) -> str:
1306            strtodate_sql = self.function_fallback_sql(expression)
1307
1308            if not isinstance(expression.parent, exp.Cast):
1309                # StrToDate returns DATEs in other dialects (eg. postgres), so
1310                # this branch aims to improve the transpilation to clickhouse
1311                return self.cast_sql(exp.cast(expression, "DATE"))
1312
1313            return strtodate_sql
1314
1315        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
1316            this = expression.this
1317
1318            if isinstance(this, exp.StrToDate) and expression.to == exp.DataType.build("datetime"):
1319                return self.sql(this)
1320
1321            return super().cast_sql(expression, safe_prefix=safe_prefix)
1322
1323        def trycast_sql(self, expression: exp.TryCast) -> str:
1324            dtype = expression.to
1325            if not dtype.is_type(*self.NON_NULLABLE_TYPES, check_nullable=True):
1326                # Casting x into Nullable(T) appears to behave similarly to TRY_CAST(x AS T)
1327                dtype.set("nullable", True)
1328
1329            return super().cast_sql(expression)
1330
1331        def _jsonpathsubscript_sql(self, expression: exp.JSONPathSubscript) -> str:
1332            this = self.json_path_part(expression.this)
1333            return str(int(this) + 1) if is_int(this) else this
1334
1335        def likeproperty_sql(self, expression: exp.LikeProperty) -> str:
1336            return f"AS {self.sql(expression, 'this')}"
1337
1338        def _any_to_has(
1339            self,
1340            expression: exp.EQ | exp.NEQ,
1341            default: t.Callable[[t.Any], str],
1342            prefix: str = "",
1343        ) -> str:
1344            if isinstance(expression.left, exp.Any):
1345                arr = expression.left
1346                this = expression.right
1347            elif isinstance(expression.right, exp.Any):
1348                arr = expression.right
1349                this = expression.left
1350            else:
1351                return default(expression)
1352
1353            return prefix + self.func("has", arr.this.unnest(), this)
1354
1355        def eq_sql(self, expression: exp.EQ) -> str:
1356            return self._any_to_has(expression, super().eq_sql)
1357
1358        def neq_sql(self, expression: exp.NEQ) -> str:
1359            return self._any_to_has(expression, super().neq_sql, "NOT ")
1360
1361        def regexpilike_sql(self, expression: exp.RegexpILike) -> str:
1362            # Manually add a flag to make the search case-insensitive
1363            regex = self.func("CONCAT", "'(?i)'", expression.expression)
1364            return self.func("match", expression.this, regex)
1365
1366        def datatype_sql(self, expression: exp.DataType) -> str:
1367            # String is the standard ClickHouse type, every other variant is just an alias.
1368            # Additionally, any supplied length parameter will be ignored.
1369            #
1370            # https://clickhouse.com/docs/en/sql-reference/data-types/string
1371            if expression.this in self.STRING_TYPE_MAPPING:
1372                dtype = "String"
1373            else:
1374                dtype = super().datatype_sql(expression)
1375
1376            # This section changes the type to `Nullable(...)` if the following conditions hold:
1377            # - It's marked as nullable - this ensures we won't wrap ClickHouse types with `Nullable`
1378            #   and change their semantics
1379            # - It's not the key type of a `Map`. This is because ClickHouse enforces the following
1380            #   constraint: "Type of Map key must be a type, that can be represented by integer or
1381            #   String or FixedString (possibly LowCardinality) or UUID or IPv6"
1382            # - It's not a composite type, e.g. `Nullable(Array(...))` is not a valid type
1383            parent = expression.parent
1384            nullable = expression.args.get("nullable")
1385            if nullable is True or (
1386                nullable is None
1387                and not (
1388                    isinstance(parent, exp.DataType)
1389                    and parent.is_type(exp.DataType.Type.MAP, check_nullable=True)
1390                    and expression.index in (None, 0)
1391                )
1392                and not expression.is_type(*self.NON_NULLABLE_TYPES, check_nullable=True)
1393            ):
1394                dtype = f"Nullable({dtype})"
1395
1396            return dtype
1397
1398        def cte_sql(self, expression: exp.CTE) -> str:
1399            if expression.args.get("scalar"):
1400                this = self.sql(expression, "this")
1401                alias = self.sql(expression, "alias")
1402                return f"{this} AS {alias}"
1403
1404            return super().cte_sql(expression)
1405
1406        def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
1407            return super().after_limit_modifiers(expression) + [
1408                (
1409                    self.seg("SETTINGS ") + self.expressions(expression, key="settings", flat=True)
1410                    if expression.args.get("settings")
1411                    else ""
1412                ),
1413                (
1414                    self.seg("FORMAT ") + self.sql(expression, "format")
1415                    if expression.args.get("format")
1416                    else ""
1417                ),
1418            ]
1419
1420        def placeholder_sql(self, expression: exp.Placeholder) -> str:
1421            return f"{{{expression.name}: {self.sql(expression, 'kind')}}}"
1422
1423        def oncluster_sql(self, expression: exp.OnCluster) -> str:
1424            return f"ON CLUSTER {self.sql(expression, 'this')}"
1425
1426        def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str:
1427            if expression.kind in self.ON_CLUSTER_TARGETS and locations.get(
1428                exp.Properties.Location.POST_NAME
1429            ):
1430                this_name = self.sql(
1431                    expression.this if isinstance(expression.this, exp.Schema) else expression,
1432                    "this",
1433                )
1434                this_properties = " ".join(
1435                    [self.sql(prop) for prop in locations[exp.Properties.Location.POST_NAME]]
1436                )
1437                this_schema = self.schema_columns_sql(expression.this)
1438                this_schema = f"{self.sep()}{this_schema}" if this_schema else ""
1439
1440                return f"{this_name}{self.sep()}{this_properties}{this_schema}"
1441
1442            return super().createable_sql(expression, locations)
1443
1444        def create_sql(self, expression: exp.Create) -> str:
1445            # The comment property comes last in CTAS statements, i.e. after the query
1446            query = expression.expression
1447            if isinstance(query, exp.Query):
1448                comment_prop = expression.find(exp.SchemaCommentProperty)
1449                if comment_prop:
1450                    comment_prop.pop()
1451                    query.replace(exp.paren(query))
1452            else:
1453                comment_prop = None
1454
1455            create_sql = super().create_sql(expression)
1456
1457            comment_sql = self.sql(comment_prop)
1458            comment_sql = f" {comment_sql}" if comment_sql else ""
1459
1460            return f"{create_sql}{comment_sql}"
1461
1462        def prewhere_sql(self, expression: exp.PreWhere) -> str:
1463            this = self.indent(self.sql(expression, "this"))
1464            return f"{self.seg('PREWHERE')}{self.sep()}{this}"
1465
1466        def indexcolumnconstraint_sql(self, expression: exp.IndexColumnConstraint) -> str:
1467            this = self.sql(expression, "this")
1468            this = f" {this}" if this else ""
1469            expr = self.sql(expression, "expression")
1470            expr = f" {expr}" if expr else ""
1471            index_type = self.sql(expression, "index_type")
1472            index_type = f" TYPE {index_type}" if index_type else ""
1473            granularity = self.sql(expression, "granularity")
1474            granularity = f" GRANULARITY {granularity}" if granularity else ""
1475
1476            return f"INDEX{this}{expr}{index_type}{granularity}"
1477
1478        def partition_sql(self, expression: exp.Partition) -> str:
1479            return f"PARTITION {self.expressions(expression, flat=True)}"
1480
1481        def partitionid_sql(self, expression: exp.PartitionId) -> str:
1482            return f"ID {self.sql(expression.this)}"
1483
1484        def replacepartition_sql(self, expression: exp.ReplacePartition) -> str:
1485            return (
1486                f"REPLACE {self.sql(expression.expression)} FROM {self.sql(expression, 'source')}"
1487            )
1488
1489        def projectiondef_sql(self, expression: exp.ProjectionDef) -> str:
1490            return f"PROJECTION {self.sql(expression.this)} {self.wrap(expression.expression)}"
1491
1492        def is_sql(self, expression: exp.Is) -> str:
1493            is_sql = super().is_sql(expression)
1494
1495            if isinstance(expression.parent, exp.Not):
1496                # value IS NOT NULL -> NOT (value IS NULL)
1497                is_sql = self.wrap(is_sql)
1498
1499            return is_sql
1500
1501        def in_sql(self, expression: exp.In) -> str:
1502            in_sql = super().in_sql(expression)
1503
1504            if isinstance(expression.parent, exp.Not) and expression.args.get("is_global"):
1505                in_sql = in_sql.replace("GLOBAL IN", "GLOBAL NOT IN", 1)
1506
1507            return in_sql
1508
1509        def not_sql(self, expression: exp.Not) -> str:
1510            if isinstance(expression.this, exp.In):
1511                if expression.this.args.get("is_global"):
1512                    # let `GLOBAL IN` child interpose `NOT`
1513                    return self.sql(expression, "this")
1514
1515                expression.set("this", exp.paren(expression.this, copy=False))
1516
1517            return super().not_sql(expression)
1518
1519        def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str:
1520            # If the VALUES clause contains tuples of expressions, we need to treat it
1521            # as a table since Clickhouse will automatically alias it as such.
1522            alias = expression.args.get("alias")
1523
1524            if alias and alias.args.get("columns") and expression.expressions:
1525                values = expression.expressions[0].expressions
1526                values_as_table = any(isinstance(value, exp.Tuple) for value in values)
1527            else:
1528                values_as_table = True
1529
1530            return super().values_sql(expression, values_as_table=values_as_table)