sqlglot.dialects.bigquery

   1from __future__ import annotations
   2
   3import logging
   4import re
   5import typing as t
   6
   7from sqlglot import exp, generator, parser, tokens, transforms
   8from sqlglot.dialects.dialect import (
   9    Dialect,
  10    NormalizationStrategy,
  11    annotate_with_type_lambda,
  12    arg_max_or_min_no_count,
  13    binary_from_function,
  14    date_add_interval_sql,
  15    datestrtodate_sql,
  16    build_formatted_time,
  17    filter_array_using_unnest,
  18    if_sql,
  19    inline_array_unless_query,
  20    max_or_greatest,
  21    min_or_least,
  22    no_ilike_sql,
  23    build_date_delta_with_interval,
  24    regexp_replace_sql,
  25    rename_func,
  26    sha256_sql,
  27    timestrtotime_sql,
  28    ts_or_ds_add_cast,
  29    unit_to_var,
  30    strposition_sql,
  31    groupconcat_sql,
  32)
  33from sqlglot.helper import seq_get, split_num_words
  34from sqlglot.tokens import TokenType
  35from sqlglot.generator import unsupported_args
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot._typing import E, Lit
  39
  40    from sqlglot.optimizer.annotate_types import TypeAnnotator
  41
  42logger = logging.getLogger("sqlglot")
  43
  44
  45JSON_EXTRACT_TYPE = t.Union[exp.JSONExtract, exp.JSONExtractScalar, exp.JSONExtractArray]
  46
  47DQUOTES_ESCAPING_JSON_FUNCTIONS = ("JSON_QUERY", "JSON_VALUE", "JSON_QUERY_ARRAY")
  48
  49
  50def _derived_table_values_to_unnest(self: BigQuery.Generator, expression: exp.Values) -> str:
  51    if not expression.find_ancestor(exp.From, exp.Join):
  52        return self.values_sql(expression)
  53
  54    structs = []
  55    alias = expression.args.get("alias")
  56    for tup in expression.find_all(exp.Tuple):
  57        field_aliases = (
  58            alias.columns
  59            if alias and alias.columns
  60            else (f"_c{i}" for i in range(len(tup.expressions)))
  61        )
  62        expressions = [
  63            exp.PropertyEQ(this=exp.to_identifier(name), expression=fld)
  64            for name, fld in zip(field_aliases, tup.expressions)
  65        ]
  66        structs.append(exp.Struct(expressions=expressions))
  67
  68    # Due to `UNNEST_COLUMN_ONLY`, it is expected that the table alias be contained in the columns expression
  69    alias_name_only = exp.TableAlias(columns=[alias.this]) if alias else None
  70    return self.unnest_sql(
  71        exp.Unnest(expressions=[exp.array(*structs, copy=False)], alias=alias_name_only)
  72    )
  73
  74
  75def _returnsproperty_sql(self: BigQuery.Generator, expression: exp.ReturnsProperty) -> str:
  76    this = expression.this
  77    if isinstance(this, exp.Schema):
  78        this = f"{self.sql(this, 'this')} <{self.expressions(this)}>"
  79    else:
  80        this = self.sql(this)
  81    return f"RETURNS {this}"
  82
  83
  84def _create_sql(self: BigQuery.Generator, expression: exp.Create) -> str:
  85    returns = expression.find(exp.ReturnsProperty)
  86    if expression.kind == "FUNCTION" and returns and returns.args.get("is_table"):
  87        expression.set("kind", "TABLE FUNCTION")
  88
  89        if isinstance(expression.expression, (exp.Subquery, exp.Literal)):
  90            expression.set("expression", expression.expression.this)
  91
  92    return self.create_sql(expression)
  93
  94
  95# https://issuetracker.google.com/issues/162294746
  96# workaround for bigquery bug when grouping by an expression and then ordering
  97# WITH x AS (SELECT 1 y)
  98# SELECT y + 1 z
  99# FROM x
 100# GROUP BY x + 1
 101# ORDER by z
 102def _alias_ordered_group(expression: exp.Expression) -> exp.Expression:
 103    if isinstance(expression, exp.Select):
 104        group = expression.args.get("group")
 105        order = expression.args.get("order")
 106
 107        if group and order:
 108            aliases = {
 109                select.this: select.args["alias"]
 110                for select in expression.selects
 111                if isinstance(select, exp.Alias)
 112            }
 113
 114            for grouped in group.expressions:
 115                if grouped.is_int:
 116                    continue
 117                alias = aliases.get(grouped)
 118                if alias:
 119                    grouped.replace(exp.column(alias))
 120
 121    return expression
 122
 123
 124def _pushdown_cte_column_names(expression: exp.Expression) -> exp.Expression:
 125    """BigQuery doesn't allow column names when defining a CTE, so we try to push them down."""
 126    if isinstance(expression, exp.CTE) and expression.alias_column_names:
 127        cte_query = expression.this
 128
 129        if cte_query.is_star:
 130            logger.warning(
 131                "Can't push down CTE column names for star queries. Run the query through"
 132                " the optimizer or use 'qualify' to expand the star projections first."
 133            )
 134            return expression
 135
 136        column_names = expression.alias_column_names
 137        expression.args["alias"].set("columns", None)
 138
 139        for name, select in zip(column_names, cte_query.selects):
 140            to_replace = select
 141
 142            if isinstance(select, exp.Alias):
 143                select = select.this
 144
 145            # Inner aliases are shadowed by the CTE column names
 146            to_replace.replace(exp.alias_(select, name))
 147
 148    return expression
 149
 150
 151def _build_parse_timestamp(args: t.List) -> exp.StrToTime:
 152    this = build_formatted_time(exp.StrToTime, "bigquery")([seq_get(args, 1), seq_get(args, 0)])
 153    this.set("zone", seq_get(args, 2))
 154    return this
 155
 156
 157def _build_timestamp(args: t.List) -> exp.Timestamp:
 158    timestamp = exp.Timestamp.from_arg_list(args)
 159    timestamp.set("with_tz", True)
 160    return timestamp
 161
 162
 163def _build_date(args: t.List) -> exp.Date | exp.DateFromParts:
 164    expr_type = exp.DateFromParts if len(args) == 3 else exp.Date
 165    return expr_type.from_arg_list(args)
 166
 167
 168def _build_to_hex(args: t.List) -> exp.Hex | exp.MD5:
 169    # TO_HEX(MD5(..)) is common in BigQuery, so it's parsed into MD5 to simplify its transpilation
 170    arg = seq_get(args, 0)
 171    return exp.MD5(this=arg.this) if isinstance(arg, exp.MD5Digest) else exp.LowerHex(this=arg)
 172
 173
 174def _array_contains_sql(self: BigQuery.Generator, expression: exp.ArrayContains) -> str:
 175    return self.sql(
 176        exp.Exists(
 177            this=exp.select("1")
 178            .from_(exp.Unnest(expressions=[expression.left]).as_("_unnest", table=["_col"]))
 179            .where(exp.column("_col").eq(expression.right))
 180        )
 181    )
 182
 183
 184def _ts_or_ds_add_sql(self: BigQuery.Generator, expression: exp.TsOrDsAdd) -> str:
 185    return date_add_interval_sql("DATE", "ADD")(self, ts_or_ds_add_cast(expression))
 186
 187
 188def _ts_or_ds_diff_sql(self: BigQuery.Generator, expression: exp.TsOrDsDiff) -> str:
 189    expression.this.replace(exp.cast(expression.this, exp.DataType.Type.TIMESTAMP))
 190    expression.expression.replace(exp.cast(expression.expression, exp.DataType.Type.TIMESTAMP))
 191    unit = unit_to_var(expression)
 192    return self.func("DATE_DIFF", expression.this, expression.expression, unit)
 193
 194
 195def _unix_to_time_sql(self: BigQuery.Generator, expression: exp.UnixToTime) -> str:
 196    scale = expression.args.get("scale")
 197    timestamp = expression.this
 198
 199    if scale in (None, exp.UnixToTime.SECONDS):
 200        return self.func("TIMESTAMP_SECONDS", timestamp)
 201    if scale == exp.UnixToTime.MILLIS:
 202        return self.func("TIMESTAMP_MILLIS", timestamp)
 203    if scale == exp.UnixToTime.MICROS:
 204        return self.func("TIMESTAMP_MICROS", timestamp)
 205
 206    unix_seconds = exp.cast(
 207        exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)), exp.DataType.Type.BIGINT
 208    )
 209    return self.func("TIMESTAMP_SECONDS", unix_seconds)
 210
 211
 212def _build_time(args: t.List) -> exp.Func:
 213    if len(args) == 1:
 214        return exp.TsOrDsToTime(this=args[0])
 215    if len(args) == 2:
 216        return exp.Time.from_arg_list(args)
 217    return exp.TimeFromParts.from_arg_list(args)
 218
 219
 220def _build_datetime(args: t.List) -> exp.Func:
 221    if len(args) == 1:
 222        return exp.TsOrDsToDatetime.from_arg_list(args)
 223    if len(args) == 2:
 224        return exp.Datetime.from_arg_list(args)
 225    return exp.TimestampFromParts.from_arg_list(args)
 226
 227
 228def _build_regexp_extract(
 229    expr_type: t.Type[E], default_group: t.Optional[exp.Expression] = None
 230) -> t.Callable[[t.List], E]:
 231    def _builder(args: t.List) -> E:
 232        try:
 233            group = re.compile(args[1].name).groups == 1
 234        except re.error:
 235            group = False
 236
 237        # Default group is used for the transpilation of REGEXP_EXTRACT_ALL
 238        return expr_type(
 239            this=seq_get(args, 0),
 240            expression=seq_get(args, 1),
 241            position=seq_get(args, 2),
 242            occurrence=seq_get(args, 3),
 243            group=exp.Literal.number(1) if group else default_group,
 244        )
 245
 246    return _builder
 247
 248
 249def _build_extract_json_with_default_path(expr_type: t.Type[E]) -> t.Callable[[t.List, Dialect], E]:
 250    def _builder(args: t.List, dialect: Dialect) -> E:
 251        if len(args) == 1:
 252            # The default value for the JSONPath is '$' i.e all of the data
 253            args.append(exp.Literal.string("$"))
 254        return parser.build_extract_json_with_path(expr_type)(args, dialect)
 255
 256    return _builder
 257
 258
 259def _str_to_datetime_sql(
 260    self: BigQuery.Generator, expression: exp.StrToDate | exp.StrToTime
 261) -> str:
 262    this = self.sql(expression, "this")
 263    dtype = "DATE" if isinstance(expression, exp.StrToDate) else "TIMESTAMP"
 264
 265    if expression.args.get("safe"):
 266        fmt = self.format_time(
 267            expression,
 268            self.dialect.INVERSE_FORMAT_MAPPING,
 269            self.dialect.INVERSE_FORMAT_TRIE,
 270        )
 271        return f"SAFE_CAST({this} AS {dtype} FORMAT {fmt})"
 272
 273    fmt = self.format_time(expression)
 274    return self.func(f"PARSE_{dtype}", fmt, this, expression.args.get("zone"))
 275
 276
 277def _annotate_math_functions(self: TypeAnnotator, expression: E) -> E:
 278    """
 279    Many BigQuery math functions such as CEIL, FLOOR etc follow this return type convention:
 280    +---------+---------+---------+------------+---------+
 281    |  INPUT  | INT64   | NUMERIC | BIGNUMERIC | FLOAT64 |
 282    +---------+---------+---------+------------+---------+
 283    |  OUTPUT | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
 284    +---------+---------+---------+------------+---------+
 285    """
 286    self._annotate_args(expression)
 287
 288    this: exp.Expression = expression.this
 289
 290    self._set_type(
 291        expression,
 292        exp.DataType.Type.DOUBLE if this.is_type(*exp.DataType.INTEGER_TYPES) else this.type,
 293    )
 294    return expression
 295
 296
 297@unsupported_args("ins_cost", "del_cost", "sub_cost")
 298def _levenshtein_sql(self: BigQuery.Generator, expression: exp.Levenshtein) -> str:
 299    max_dist = expression.args.get("max_dist")
 300    if max_dist:
 301        max_dist = exp.Kwarg(this=exp.var("max_distance"), expression=max_dist)
 302
 303    return self.func("EDIT_DISTANCE", expression.this, expression.expression, max_dist)
 304
 305
 306def _build_levenshtein(args: t.List) -> exp.Levenshtein:
 307    max_dist = seq_get(args, 2)
 308    return exp.Levenshtein(
 309        this=seq_get(args, 0),
 310        expression=seq_get(args, 1),
 311        max_dist=max_dist.expression if max_dist else None,
 312    )
 313
 314
 315def _build_format_time(expr_type: t.Type[exp.Expression]) -> t.Callable[[t.List], exp.TimeToStr]:
 316    def _builder(args: t.List) -> exp.TimeToStr:
 317        return exp.TimeToStr(
 318            this=expr_type(this=seq_get(args, 1)),
 319            format=seq_get(args, 0),
 320            zone=seq_get(args, 2),
 321        )
 322
 323    return _builder
 324
 325
 326def _build_contains_substring(args: t.List) -> exp.Contains | exp.Anonymous:
 327    if len(args) == 3:
 328        return exp.Anonymous(this="CONTAINS_SUBSTR", expressions=args)
 329
 330    # Lowercase the operands in case of transpilation, as exp.Contains
 331    # is case-sensitive on other dialects
 332    this = exp.Lower(this=seq_get(args, 0))
 333    expr = exp.Lower(this=seq_get(args, 1))
 334
 335    return exp.Contains(this=this, expression=expr)
 336
 337
 338def _json_extract_sql(self: BigQuery.Generator, expression: JSON_EXTRACT_TYPE) -> str:
 339    name = (expression._meta and expression.meta.get("name")) or expression.sql_name()
 340    upper = name.upper()
 341
 342    dquote_escaping = upper in DQUOTES_ESCAPING_JSON_FUNCTIONS
 343
 344    if dquote_escaping:
 345        self._quote_json_path_key_using_brackets = False
 346
 347    sql = rename_func(upper)(self, expression)
 348
 349    if dquote_escaping:
 350        self._quote_json_path_key_using_brackets = True
 351
 352    return sql
 353
 354
 355class BigQuery(Dialect):
 356    WEEK_OFFSET = -1
 357    UNNEST_COLUMN_ONLY = True
 358    SUPPORTS_USER_DEFINED_TYPES = False
 359    SUPPORTS_SEMI_ANTI_JOIN = False
 360    LOG_BASE_FIRST = False
 361    HEX_LOWERCASE = True
 362    FORCE_EARLY_ALIAS_REF_EXPANSION = True
 363    PRESERVE_ORIGINAL_NAMES = True
 364    HEX_STRING_IS_INTEGER_TYPE = True
 365
 366    # https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#case_sensitivity
 367    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
 368
 369    # bigquery udfs are case sensitive
 370    NORMALIZE_FUNCTIONS = False
 371
 372    # https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time
 373    TIME_MAPPING = {
 374        "%D": "%m/%d/%y",
 375        "%E6S": "%S.%f",
 376        "%e": "%-d",
 377    }
 378
 379    FORMAT_MAPPING = {
 380        "DD": "%d",
 381        "MM": "%m",
 382        "MON": "%b",
 383        "MONTH": "%B",
 384        "YYYY": "%Y",
 385        "YY": "%y",
 386        "HH": "%I",
 387        "HH12": "%I",
 388        "HH24": "%H",
 389        "MI": "%M",
 390        "SS": "%S",
 391        "SSSSS": "%f",
 392        "TZH": "%z",
 393    }
 394
 395    # The _PARTITIONTIME and _PARTITIONDATE pseudo-columns are not returned by a SELECT * statement
 396    # https://cloud.google.com/bigquery/docs/querying-partitioned-tables#query_an_ingestion-time_partitioned_table
 397    PSEUDOCOLUMNS = {"_PARTITIONTIME", "_PARTITIONDATE"}
 398
 399    # All set operations require either a DISTINCT or ALL specifier
 400    SET_OP_DISTINCT_BY_DEFAULT = dict.fromkeys((exp.Except, exp.Intersect, exp.Union), None)
 401
 402    # BigQuery maps Type.TIMESTAMP to DATETIME, so we need to amend the inferred types
 403    TYPE_TO_EXPRESSIONS = {
 404        **Dialect.TYPE_TO_EXPRESSIONS,
 405        exp.DataType.Type.TIMESTAMPTZ: Dialect.TYPE_TO_EXPRESSIONS[exp.DataType.Type.TIMESTAMP],
 406    }
 407    TYPE_TO_EXPRESSIONS.pop(exp.DataType.Type.TIMESTAMP)
 408
 409    ANNOTATORS = {
 410        **Dialect.ANNOTATORS,
 411        **{
 412            expr_type: annotate_with_type_lambda(data_type)
 413            for data_type, expressions in TYPE_TO_EXPRESSIONS.items()
 414            for expr_type in expressions
 415        },
 416        **{
 417            expr_type: lambda self, e: _annotate_math_functions(self, e)
 418            for expr_type in (exp.Floor, exp.Ceil, exp.Log, exp.Ln, exp.Sqrt, exp.Exp, exp.Round)
 419        },
 420        **{
 421            expr_type: lambda self, e: self._annotate_by_args(e, "this")
 422            for expr_type in (
 423                exp.Left,
 424                exp.Right,
 425                exp.Lower,
 426                exp.Upper,
 427                exp.Pad,
 428                exp.Trim,
 429                exp.RegexpExtract,
 430                exp.RegexpReplace,
 431                exp.Repeat,
 432                exp.Substring,
 433            )
 434        },
 435        exp.Concat: lambda self, e: self._annotate_by_args(e, "expressions"),
 436        exp.Sign: lambda self, e: self._annotate_by_args(e, "this"),
 437        exp.Split: lambda self, e: self._annotate_by_args(e, "this", array=True),
 438    }
 439
 440    def normalize_identifier(self, expression: E) -> E:
 441        if (
 442            isinstance(expression, exp.Identifier)
 443            and self.normalization_strategy is not NormalizationStrategy.CASE_SENSITIVE
 444        ):
 445            parent = expression.parent
 446            while isinstance(parent, exp.Dot):
 447                parent = parent.parent
 448
 449            # In BigQuery, CTEs are case-insensitive, but UDF and table names are case-sensitive
 450            # by default. The following check uses a heuristic to detect tables based on whether
 451            # they are qualified. This should generally be correct, because tables in BigQuery
 452            # must be qualified with at least a dataset, unless @@dataset_id is set.
 453            case_sensitive = (
 454                isinstance(parent, exp.UserDefinedFunction)
 455                or (
 456                    isinstance(parent, exp.Table)
 457                    and parent.db
 458                    and (parent.meta.get("quoted_table") or not parent.meta.get("maybe_column"))
 459                )
 460                or expression.meta.get("is_table")
 461            )
 462            if not case_sensitive:
 463                expression.set("this", expression.this.lower())
 464
 465        return expression
 466
 467    class Tokenizer(tokens.Tokenizer):
 468        QUOTES = ["'", '"', '"""', "'''"]
 469        COMMENTS = ["--", "#", ("/*", "*/")]
 470        IDENTIFIERS = ["`"]
 471        STRING_ESCAPES = ["\\"]
 472
 473        HEX_STRINGS = [("0x", ""), ("0X", "")]
 474
 475        BYTE_STRINGS = [
 476            (prefix + q, q) for q in t.cast(t.List[str], QUOTES) for prefix in ("b", "B")
 477        ]
 478
 479        RAW_STRINGS = [
 480            (prefix + q, q) for q in t.cast(t.List[str], QUOTES) for prefix in ("r", "R")
 481        ]
 482
 483        KEYWORDS = {
 484            **tokens.Tokenizer.KEYWORDS,
 485            "ANY TYPE": TokenType.VARIANT,
 486            "BEGIN": TokenType.COMMAND,
 487            "BEGIN TRANSACTION": TokenType.BEGIN,
 488            "BYTEINT": TokenType.INT,
 489            "BYTES": TokenType.BINARY,
 490            "CURRENT_DATETIME": TokenType.CURRENT_DATETIME,
 491            "DATETIME": TokenType.TIMESTAMP,
 492            "DECLARE": TokenType.COMMAND,
 493            "ELSEIF": TokenType.COMMAND,
 494            "EXCEPTION": TokenType.COMMAND,
 495            "EXPORT": TokenType.EXPORT,
 496            "FLOAT64": TokenType.DOUBLE,
 497            "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT,
 498            "MODEL": TokenType.MODEL,
 499            "NOT DETERMINISTIC": TokenType.VOLATILE,
 500            "RECORD": TokenType.STRUCT,
 501            "TIMESTAMP": TokenType.TIMESTAMPTZ,
 502        }
 503        KEYWORDS.pop("DIV")
 504        KEYWORDS.pop("VALUES")
 505        KEYWORDS.pop("/*+")
 506
 507    class Parser(parser.Parser):
 508        PREFIXED_PIVOT_COLUMNS = True
 509        LOG_DEFAULTS_TO_LN = True
 510        SUPPORTS_IMPLICIT_UNNEST = True
 511
 512        FUNCTIONS = {
 513            **parser.Parser.FUNCTIONS,
 514            "CONTAINS_SUBSTR": _build_contains_substring,
 515            "DATE": _build_date,
 516            "DATE_ADD": build_date_delta_with_interval(exp.DateAdd),
 517            "DATE_SUB": build_date_delta_with_interval(exp.DateSub),
 518            "DATE_TRUNC": lambda args: exp.DateTrunc(
 519                unit=exp.Literal.string(str(seq_get(args, 1))),
 520                this=seq_get(args, 0),
 521                zone=seq_get(args, 2),
 522            ),
 523            "DATETIME": _build_datetime,
 524            "DATETIME_ADD": build_date_delta_with_interval(exp.DatetimeAdd),
 525            "DATETIME_SUB": build_date_delta_with_interval(exp.DatetimeSub),
 526            "DIV": binary_from_function(exp.IntDiv),
 527            "EDIT_DISTANCE": _build_levenshtein,
 528            "FORMAT_DATE": _build_format_time(exp.TsOrDsToDate),
 529            "GENERATE_ARRAY": exp.GenerateSeries.from_arg_list,
 530            "JSON_EXTRACT_SCALAR": _build_extract_json_with_default_path(exp.JSONExtractScalar),
 531            "JSON_EXTRACT_ARRAY": _build_extract_json_with_default_path(exp.JSONExtractArray),
 532            "JSON_QUERY": parser.build_extract_json_with_path(exp.JSONExtract),
 533            "JSON_QUERY_ARRAY": _build_extract_json_with_default_path(exp.JSONExtractArray),
 534            "JSON_VALUE": _build_extract_json_with_default_path(exp.JSONExtractScalar),
 535            "JSON_VALUE_ARRAY": _build_extract_json_with_default_path(exp.JSONValueArray),
 536            "LENGTH": lambda args: exp.Length(this=seq_get(args, 0), binary=True),
 537            "MD5": exp.MD5Digest.from_arg_list,
 538            "TO_HEX": _build_to_hex,
 539            "PARSE_DATE": lambda args: build_formatted_time(exp.StrToDate, "bigquery")(
 540                [seq_get(args, 1), seq_get(args, 0)]
 541            ),
 542            "PARSE_TIMESTAMP": _build_parse_timestamp,
 543            "REGEXP_CONTAINS": exp.RegexpLike.from_arg_list,
 544            "REGEXP_EXTRACT": _build_regexp_extract(exp.RegexpExtract),
 545            "REGEXP_SUBSTR": _build_regexp_extract(exp.RegexpExtract),
 546            "REGEXP_EXTRACT_ALL": _build_regexp_extract(
 547                exp.RegexpExtractAll, default_group=exp.Literal.number(0)
 548            ),
 549            "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)),
 550            "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)),
 551            "SPLIT": lambda args: exp.Split(
 552                # https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split
 553                this=seq_get(args, 0),
 554                expression=seq_get(args, 1) or exp.Literal.string(","),
 555            ),
 556            "STRPOS": exp.StrPosition.from_arg_list,
 557            "TIME": _build_time,
 558            "TIME_ADD": build_date_delta_with_interval(exp.TimeAdd),
 559            "TIME_SUB": build_date_delta_with_interval(exp.TimeSub),
 560            "TIMESTAMP": _build_timestamp,
 561            "TIMESTAMP_ADD": build_date_delta_with_interval(exp.TimestampAdd),
 562            "TIMESTAMP_SUB": build_date_delta_with_interval(exp.TimestampSub),
 563            "TIMESTAMP_MICROS": lambda args: exp.UnixToTime(
 564                this=seq_get(args, 0), scale=exp.UnixToTime.MICROS
 565            ),
 566            "TIMESTAMP_MILLIS": lambda args: exp.UnixToTime(
 567                this=seq_get(args, 0), scale=exp.UnixToTime.MILLIS
 568            ),
 569            "TIMESTAMP_SECONDS": lambda args: exp.UnixToTime(this=seq_get(args, 0)),
 570            "TO_JSON_STRING": exp.JSONFormat.from_arg_list,
 571            "FORMAT_DATETIME": _build_format_time(exp.TsOrDsToDatetime),
 572            "FORMAT_TIMESTAMP": _build_format_time(exp.TsOrDsToTimestamp),
 573        }
 574
 575        FUNCTION_PARSERS = {
 576            **parser.Parser.FUNCTION_PARSERS,
 577            "ARRAY": lambda self: self.expression(exp.Array, expressions=[self._parse_statement()]),
 578            "MAKE_INTERVAL": lambda self: self._parse_make_interval(),
 579            "FEATURES_AT_TIME": lambda self: self._parse_features_at_time(),
 580        }
 581        FUNCTION_PARSERS.pop("TRIM")
 582
 583        NO_PAREN_FUNCTIONS = {
 584            **parser.Parser.NO_PAREN_FUNCTIONS,
 585            TokenType.CURRENT_DATETIME: exp.CurrentDatetime,
 586        }
 587
 588        NESTED_TYPE_TOKENS = {
 589            *parser.Parser.NESTED_TYPE_TOKENS,
 590            TokenType.TABLE,
 591        }
 592
 593        PROPERTY_PARSERS = {
 594            **parser.Parser.PROPERTY_PARSERS,
 595            "NOT DETERMINISTIC": lambda self: self.expression(
 596                exp.StabilityProperty, this=exp.Literal.string("VOLATILE")
 597            ),
 598            "OPTIONS": lambda self: self._parse_with_property(),
 599        }
 600
 601        CONSTRAINT_PARSERS = {
 602            **parser.Parser.CONSTRAINT_PARSERS,
 603            "OPTIONS": lambda self: exp.Properties(expressions=self._parse_with_property()),
 604        }
 605
 606        RANGE_PARSERS = parser.Parser.RANGE_PARSERS.copy()
 607        RANGE_PARSERS.pop(TokenType.OVERLAPS)
 608
 609        NULL_TOKENS = {TokenType.NULL, TokenType.UNKNOWN}
 610
 611        DASHED_TABLE_PART_FOLLOW_TOKENS = {TokenType.DOT, TokenType.L_PAREN, TokenType.R_PAREN}
 612
 613        STATEMENT_PARSERS = {
 614            **parser.Parser.STATEMENT_PARSERS,
 615            TokenType.ELSE: lambda self: self._parse_as_command(self._prev),
 616            TokenType.END: lambda self: self._parse_as_command(self._prev),
 617            TokenType.FOR: lambda self: self._parse_for_in(),
 618            TokenType.EXPORT: lambda self: self._parse_export_data(),
 619        }
 620
 621        BRACKET_OFFSETS = {
 622            "OFFSET": (0, False),
 623            "ORDINAL": (1, False),
 624            "SAFE_OFFSET": (0, True),
 625            "SAFE_ORDINAL": (1, True),
 626        }
 627
 628        def _parse_for_in(self) -> exp.ForIn:
 629            this = self._parse_range()
 630            self._match_text_seq("DO")
 631            return self.expression(exp.ForIn, this=this, expression=self._parse_statement())
 632
 633        def _parse_table_part(self, schema: bool = False) -> t.Optional[exp.Expression]:
 634            this = super()._parse_table_part(schema=schema) or self._parse_number()
 635
 636            # https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#table_names
 637            if isinstance(this, exp.Identifier):
 638                table_name = this.name
 639                while self._match(TokenType.DASH, advance=False) and self._next:
 640                    start = self._curr
 641                    while self._is_connected() and not self._match_set(
 642                        self.DASHED_TABLE_PART_FOLLOW_TOKENS, advance=False
 643                    ):
 644                        self._advance()
 645
 646                    table_name += self._find_sql(start, self._prev)
 647
 648                this = exp.Identifier(
 649                    this=table_name, quoted=this.args.get("quoted")
 650                ).update_positions(this)
 651            elif isinstance(this, exp.Literal):
 652                table_name = this.name
 653
 654                if self._is_connected() and self._parse_var(any_token=True):
 655                    table_name += self._prev.text
 656
 657                this = exp.Identifier(this=table_name, quoted=True).update_positions(this)
 658
 659            return this
 660
 661        def _parse_table_parts(
 662            self, schema: bool = False, is_db_reference: bool = False, wildcard: bool = False
 663        ) -> exp.Table:
 664            table = super()._parse_table_parts(
 665                schema=schema, is_db_reference=is_db_reference, wildcard=True
 666            )
 667
 668            # proj-1.db.tbl -- `1.` is tokenized as a float so we need to unravel it here
 669            if not table.catalog:
 670                if table.db:
 671                    previous_db = table.args["db"]
 672                    parts = table.db.split(".")
 673                    if len(parts) == 2 and not table.args["db"].quoted:
 674                        table.set(
 675                            "catalog", exp.Identifier(this=parts[0]).update_positions(previous_db)
 676                        )
 677                        table.set("db", exp.Identifier(this=parts[1]).update_positions(previous_db))
 678                else:
 679                    previous_this = table.this
 680                    parts = table.name.split(".")
 681                    if len(parts) == 2 and not table.this.quoted:
 682                        table.set(
 683                            "db", exp.Identifier(this=parts[0]).update_positions(previous_this)
 684                        )
 685                        table.set(
 686                            "this", exp.Identifier(this=parts[1]).update_positions(previous_this)
 687                        )
 688
 689            if isinstance(table.this, exp.Identifier) and any("." in p.name for p in table.parts):
 690                alias = table.this
 691                catalog, db, this, *rest = (
 692                    exp.to_identifier(p, quoted=True)
 693                    for p in split_num_words(".".join(p.name for p in table.parts), ".", 3)
 694                )
 695
 696                for part in (catalog, db, this):
 697                    if part:
 698                        part.update_positions(table.this)
 699
 700                if rest and this:
 701                    this = exp.Dot.build([this, *rest])  # type: ignore
 702
 703                table = exp.Table(
 704                    this=this, db=db, catalog=catalog, pivots=table.args.get("pivots")
 705                )
 706                table.meta["quoted_table"] = True
 707            else:
 708                alias = None
 709
 710            # The `INFORMATION_SCHEMA` views in BigQuery need to be qualified by a region or
 711            # dataset, so if the project identifier is omitted we need to fix the ast so that
 712            # the `INFORMATION_SCHEMA.X` bit is represented as a single (quoted) Identifier.
 713            # Otherwise, we wouldn't correctly qualify a `Table` node that references these
 714            # views, because it would seem like the "catalog" part is set, when it'd actually
 715            # be the region/dataset. Merging the two identifiers into a single one is done to
 716            # avoid producing a 4-part Table reference, which would cause issues in the schema
 717            # module, when there are 3-part table names mixed with information schema views.
 718            #
 719            # See: https://cloud.google.com/bigquery/docs/information-schema-intro#syntax
 720            table_parts = table.parts
 721            if len(table_parts) > 1 and table_parts[-2].name.upper() == "INFORMATION_SCHEMA":
 722                # We need to alias the table here to avoid breaking existing qualified columns.
 723                # This is expected to be safe, because if there's an actual alias coming up in
 724                # the token stream, it will overwrite this one. If there isn't one, we are only
 725                # exposing the name that can be used to reference the view explicitly (a no-op).
 726                exp.alias_(
 727                    table,
 728                    t.cast(exp.Identifier, alias or table_parts[-1]),
 729                    table=True,
 730                    copy=False,
 731                )
 732
 733                info_schema_view = f"{table_parts[-2].name}.{table_parts[-1].name}"
 734                new_this = exp.Identifier(this=info_schema_view, quoted=True).update_positions(
 735                    line=table_parts[-2].meta.get("line"),
 736                    col=table_parts[-1].meta.get("col"),
 737                    start=table_parts[-2].meta.get("start"),
 738                    end=table_parts[-1].meta.get("end"),
 739                )
 740                table.set("this", new_this)
 741                table.set("db", seq_get(table_parts, -3))
 742                table.set("catalog", seq_get(table_parts, -4))
 743
 744            return table
 745
 746        def _parse_column(self) -> t.Optional[exp.Expression]:
 747            column = super()._parse_column()
 748            if isinstance(column, exp.Column):
 749                parts = column.parts
 750                if any("." in p.name for p in parts):
 751                    catalog, db, table, this, *rest = (
 752                        exp.to_identifier(p, quoted=True)
 753                        for p in split_num_words(".".join(p.name for p in parts), ".", 4)
 754                    )
 755
 756                    if rest and this:
 757                        this = exp.Dot.build([this, *rest])  # type: ignore
 758
 759                    column = exp.Column(this=this, table=table, db=db, catalog=catalog)
 760                    column.meta["quoted_column"] = True
 761
 762            return column
 763
 764        @t.overload
 765        def _parse_json_object(self, agg: Lit[False]) -> exp.JSONObject: ...
 766
 767        @t.overload
 768        def _parse_json_object(self, agg: Lit[True]) -> exp.JSONObjectAgg: ...
 769
 770        def _parse_json_object(self, agg=False):
 771            json_object = super()._parse_json_object()
 772            array_kv_pair = seq_get(json_object.expressions, 0)
 773
 774            # Converts BQ's "signature 2" of JSON_OBJECT into SQLGlot's canonical representation
 775            # https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_object_signature2
 776            if (
 777                array_kv_pair
 778                and isinstance(array_kv_pair.this, exp.Array)
 779                and isinstance(array_kv_pair.expression, exp.Array)
 780            ):
 781                keys = array_kv_pair.this.expressions
 782                values = array_kv_pair.expression.expressions
 783
 784                json_object.set(
 785                    "expressions",
 786                    [exp.JSONKeyValue(this=k, expression=v) for k, v in zip(keys, values)],
 787                )
 788
 789            return json_object
 790
 791        def _parse_bracket(
 792            self, this: t.Optional[exp.Expression] = None
 793        ) -> t.Optional[exp.Expression]:
 794            bracket = super()._parse_bracket(this)
 795
 796            if this is bracket:
 797                return bracket
 798
 799            if isinstance(bracket, exp.Bracket):
 800                for expression in bracket.expressions:
 801                    name = expression.name.upper()
 802
 803                    if name not in self.BRACKET_OFFSETS:
 804                        break
 805
 806                    offset, safe = self.BRACKET_OFFSETS[name]
 807                    bracket.set("offset", offset)
 808                    bracket.set("safe", safe)
 809                    expression.replace(expression.expressions[0])
 810
 811            return bracket
 812
 813        def _parse_unnest(self, with_alias: bool = True) -> t.Optional[exp.Unnest]:
 814            unnest = super()._parse_unnest(with_alias=with_alias)
 815
 816            if not unnest:
 817                return None
 818
 819            unnest_expr = seq_get(unnest.expressions, 0)
 820            if unnest_expr:
 821                from sqlglot.optimizer.annotate_types import annotate_types
 822
 823                unnest_expr = annotate_types(unnest_expr, dialect=self.dialect)
 824
 825                # Unnesting a nested array (i.e array of structs) explodes the top-level struct fields,
 826                # in contrast to other dialects such as DuckDB which flattens only the array by default
 827                if unnest_expr.is_type(exp.DataType.Type.ARRAY) and any(
 828                    array_elem.is_type(exp.DataType.Type.STRUCT)
 829                    for array_elem in unnest_expr._type.expressions
 830                ):
 831                    unnest.set("explode_array", True)
 832
 833            return unnest
 834
 835        def _parse_make_interval(self) -> exp.MakeInterval:
 836            expr = exp.MakeInterval()
 837
 838            for arg_key in expr.arg_types:
 839                value = self._parse_lambda()
 840
 841                if not value:
 842                    break
 843
 844                # Non-named arguments are filled sequentially, (optionally) followed by named arguments
 845                # that can appear in any order e.g MAKE_INTERVAL(1, minute => 5, day => 2)
 846                if isinstance(value, exp.Kwarg):
 847                    arg_key = value.this.name
 848
 849                expr.set(arg_key, value)
 850
 851                self._match(TokenType.COMMA)
 852
 853            return expr
 854
 855        def _parse_features_at_time(self) -> exp.FeaturesAtTime:
 856            expr = self.expression(
 857                exp.FeaturesAtTime,
 858                this=(self._match(TokenType.TABLE) and self._parse_table())
 859                or self._parse_select(nested=True),
 860            )
 861
 862            while self._match(TokenType.COMMA):
 863                arg = self._parse_lambda()
 864
 865                # Get the LHS of the Kwarg and set the arg to that value, e.g
 866                # "num_rows => 1" sets the expr's `num_rows` arg
 867                if arg:
 868                    expr.set(arg.this.name, arg)
 869
 870            return expr
 871
 872        def _parse_export_data(self) -> exp.Export:
 873            self._match_text_seq("DATA")
 874
 875            return self.expression(
 876                exp.Export,
 877                connection=self._match_text_seq("WITH", "CONNECTION") and self._parse_table_parts(),
 878                options=self._parse_properties(),
 879                this=self._match_text_seq("AS") and self._parse_select(),
 880            )
 881
 882    class Generator(generator.Generator):
 883        INTERVAL_ALLOWS_PLURAL_FORM = False
 884        JOIN_HINTS = False
 885        QUERY_HINTS = False
 886        TABLE_HINTS = False
 887        LIMIT_FETCH = "LIMIT"
 888        RENAME_TABLE_WITH_DB = False
 889        NVL2_SUPPORTED = False
 890        UNNEST_WITH_ORDINALITY = False
 891        COLLATE_IS_FUNC = True
 892        LIMIT_ONLY_LITERALS = True
 893        SUPPORTS_TABLE_ALIAS_COLUMNS = False
 894        UNPIVOT_ALIASES_ARE_IDENTIFIERS = False
 895        JSON_KEY_VALUE_PAIR_SEP = ","
 896        NULL_ORDERING_SUPPORTED = False
 897        IGNORE_NULLS_IN_FUNC = True
 898        JSON_PATH_SINGLE_QUOTE_ESCAPE = True
 899        CAN_IMPLEMENT_ARRAY_ANY = True
 900        SUPPORTS_TO_NUMBER = False
 901        NAMED_PLACEHOLDER_TOKEN = "@"
 902        HEX_FUNC = "TO_HEX"
 903        WITH_PROPERTIES_PREFIX = "OPTIONS"
 904        SUPPORTS_EXPLODING_PROJECTIONS = False
 905        EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
 906        SUPPORTS_UNIX_SECONDS = True
 907
 908        TRANSFORMS = {
 909            **generator.Generator.TRANSFORMS,
 910            exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"),
 911            exp.ArgMax: arg_max_or_min_no_count("MAX_BY"),
 912            exp.ArgMin: arg_max_or_min_no_count("MIN_BY"),
 913            exp.Array: inline_array_unless_query,
 914            exp.ArrayContains: _array_contains_sql,
 915            exp.ArrayFilter: filter_array_using_unnest,
 916            exp.Cast: transforms.preprocess([transforms.remove_precision_parameterized_types]),
 917            exp.CollateProperty: lambda self, e: (
 918                f"DEFAULT COLLATE {self.sql(e, 'this')}"
 919                if e.args.get("default")
 920                else f"COLLATE {self.sql(e, 'this')}"
 921            ),
 922            exp.Commit: lambda *_: "COMMIT TRANSACTION",
 923            exp.CountIf: rename_func("COUNTIF"),
 924            exp.Create: _create_sql,
 925            exp.CTE: transforms.preprocess([_pushdown_cte_column_names]),
 926            exp.DateAdd: date_add_interval_sql("DATE", "ADD"),
 927            exp.DateDiff: lambda self, e: self.func(
 928                "DATE_DIFF", e.this, e.expression, unit_to_var(e)
 929            ),
 930            exp.DateFromParts: rename_func("DATE"),
 931            exp.DateStrToDate: datestrtodate_sql,
 932            exp.DateSub: date_add_interval_sql("DATE", "SUB"),
 933            exp.DatetimeAdd: date_add_interval_sql("DATETIME", "ADD"),
 934            exp.DatetimeSub: date_add_interval_sql("DATETIME", "SUB"),
 935            exp.DateTrunc: lambda self, e: self.func(
 936                "DATE_TRUNC", e.this, e.text("unit"), e.args.get("zone")
 937            ),
 938            exp.FromTimeZone: lambda self, e: self.func(
 939                "DATETIME", self.func("TIMESTAMP", e.this, e.args.get("zone")), "'UTC'"
 940            ),
 941            exp.GenerateSeries: rename_func("GENERATE_ARRAY"),
 942            exp.GroupConcat: lambda self, e: groupconcat_sql(
 943                self, e, func_name="STRING_AGG", within_group=False
 944            ),
 945            exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))),
 946            exp.HexString: lambda self, e: self.hexstring_sql(e, binary_function_repr="FROM_HEX"),
 947            exp.If: if_sql(false_value="NULL"),
 948            exp.ILike: no_ilike_sql,
 949            exp.IntDiv: rename_func("DIV"),
 950            exp.Int64: rename_func("INT64"),
 951            exp.JSONExtract: _json_extract_sql,
 952            exp.JSONExtractArray: _json_extract_sql,
 953            exp.JSONExtractScalar: _json_extract_sql,
 954            exp.JSONFormat: rename_func("TO_JSON_STRING"),
 955            exp.Levenshtein: _levenshtein_sql,
 956            exp.Max: max_or_greatest,
 957            exp.MD5: lambda self, e: self.func("TO_HEX", self.func("MD5", e.this)),
 958            exp.MD5Digest: rename_func("MD5"),
 959            exp.Min: min_or_least,
 960            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
 961            exp.RegexpExtract: lambda self, e: self.func(
 962                "REGEXP_EXTRACT",
 963                e.this,
 964                e.expression,
 965                e.args.get("position"),
 966                e.args.get("occurrence"),
 967            ),
 968            exp.RegexpExtractAll: lambda self, e: self.func(
 969                "REGEXP_EXTRACT_ALL", e.this, e.expression
 970            ),
 971            exp.RegexpReplace: regexp_replace_sql,
 972            exp.RegexpLike: rename_func("REGEXP_CONTAINS"),
 973            exp.ReturnsProperty: _returnsproperty_sql,
 974            exp.Rollback: lambda *_: "ROLLBACK TRANSACTION",
 975            exp.Select: transforms.preprocess(
 976                [
 977                    transforms.explode_projection_to_unnest(),
 978                    transforms.unqualify_unnest,
 979                    transforms.eliminate_distinct_on,
 980                    _alias_ordered_group,
 981                    transforms.eliminate_semi_and_anti_joins,
 982                ]
 983            ),
 984            exp.SHA: rename_func("SHA1"),
 985            exp.SHA2: sha256_sql,
 986            exp.StabilityProperty: lambda self, e: (
 987                "DETERMINISTIC" if e.name == "IMMUTABLE" else "NOT DETERMINISTIC"
 988            ),
 989            exp.String: rename_func("STRING"),
 990            exp.StrPosition: lambda self, e: (
 991                strposition_sql(
 992                    self, e, func_name="INSTR", supports_position=True, supports_occurrence=True
 993                )
 994            ),
 995            exp.StrToDate: _str_to_datetime_sql,
 996            exp.StrToTime: _str_to_datetime_sql,
 997            exp.TimeAdd: date_add_interval_sql("TIME", "ADD"),
 998            exp.TimeFromParts: rename_func("TIME"),
 999            exp.TimestampFromParts: rename_func("DATETIME"),
1000            exp.TimeSub: date_add_interval_sql("TIME", "SUB"),
1001            exp.TimestampAdd: date_add_interval_sql("TIMESTAMP", "ADD"),
1002            exp.TimestampDiff: rename_func("TIMESTAMP_DIFF"),
1003            exp.TimestampSub: date_add_interval_sql("TIMESTAMP", "SUB"),
1004            exp.TimeStrToTime: timestrtotime_sql,
1005            exp.Transaction: lambda *_: "BEGIN TRANSACTION",
1006            exp.TsOrDsAdd: _ts_or_ds_add_sql,
1007            exp.TsOrDsDiff: _ts_or_ds_diff_sql,
1008            exp.TsOrDsToTime: rename_func("TIME"),
1009            exp.TsOrDsToDatetime: rename_func("DATETIME"),
1010            exp.TsOrDsToTimestamp: rename_func("TIMESTAMP"),
1011            exp.Unhex: rename_func("FROM_HEX"),
1012            exp.UnixDate: rename_func("UNIX_DATE"),
1013            exp.UnixToTime: _unix_to_time_sql,
1014            exp.Uuid: lambda *_: "GENERATE_UUID()",
1015            exp.Values: _derived_table_values_to_unnest,
1016            exp.VariancePop: rename_func("VAR_POP"),
1017            exp.SafeDivide: rename_func("SAFE_DIVIDE"),
1018        }
1019
1020        SUPPORTED_JSON_PATH_PARTS = {
1021            exp.JSONPathKey,
1022            exp.JSONPathRoot,
1023            exp.JSONPathSubscript,
1024        }
1025
1026        TYPE_MAPPING = {
1027            **generator.Generator.TYPE_MAPPING,
1028            exp.DataType.Type.BIGDECIMAL: "BIGNUMERIC",
1029            exp.DataType.Type.BIGINT: "INT64",
1030            exp.DataType.Type.BINARY: "BYTES",
1031            exp.DataType.Type.BLOB: "BYTES",
1032            exp.DataType.Type.BOOLEAN: "BOOL",
1033            exp.DataType.Type.CHAR: "STRING",
1034            exp.DataType.Type.DECIMAL: "NUMERIC",
1035            exp.DataType.Type.DOUBLE: "FLOAT64",
1036            exp.DataType.Type.FLOAT: "FLOAT64",
1037            exp.DataType.Type.INT: "INT64",
1038            exp.DataType.Type.NCHAR: "STRING",
1039            exp.DataType.Type.NVARCHAR: "STRING",
1040            exp.DataType.Type.SMALLINT: "INT64",
1041            exp.DataType.Type.TEXT: "STRING",
1042            exp.DataType.Type.TIMESTAMP: "DATETIME",
1043            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
1044            exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
1045            exp.DataType.Type.TINYINT: "INT64",
1046            exp.DataType.Type.ROWVERSION: "BYTES",
1047            exp.DataType.Type.UUID: "STRING",
1048            exp.DataType.Type.VARBINARY: "BYTES",
1049            exp.DataType.Type.VARCHAR: "STRING",
1050            exp.DataType.Type.VARIANT: "ANY TYPE",
1051        }
1052
1053        PROPERTIES_LOCATION = {
1054            **generator.Generator.PROPERTIES_LOCATION,
1055            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
1056            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
1057        }
1058
1059        # WINDOW comes after QUALIFY
1060        # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#window_clause
1061        AFTER_HAVING_MODIFIER_TRANSFORMS = {
1062            "qualify": generator.Generator.AFTER_HAVING_MODIFIER_TRANSFORMS["qualify"],
1063            "windows": generator.Generator.AFTER_HAVING_MODIFIER_TRANSFORMS["windows"],
1064        }
1065
1066        # from: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#reserved_keywords
1067        RESERVED_KEYWORDS = {
1068            "all",
1069            "and",
1070            "any",
1071            "array",
1072            "as",
1073            "asc",
1074            "assert_rows_modified",
1075            "at",
1076            "between",
1077            "by",
1078            "case",
1079            "cast",
1080            "collate",
1081            "contains",
1082            "create",
1083            "cross",
1084            "cube",
1085            "current",
1086            "default",
1087            "define",
1088            "desc",
1089            "distinct",
1090            "else",
1091            "end",
1092            "enum",
1093            "escape",
1094            "except",
1095            "exclude",
1096            "exists",
1097            "extract",
1098            "false",
1099            "fetch",
1100            "following",
1101            "for",
1102            "from",
1103            "full",
1104            "group",
1105            "grouping",
1106            "groups",
1107            "hash",
1108            "having",
1109            "if",
1110            "ignore",
1111            "in",
1112            "inner",
1113            "intersect",
1114            "interval",
1115            "into",
1116            "is",
1117            "join",
1118            "lateral",
1119            "left",
1120            "like",
1121            "limit",
1122            "lookup",
1123            "merge",
1124            "natural",
1125            "new",
1126            "no",
1127            "not",
1128            "null",
1129            "nulls",
1130            "of",
1131            "on",
1132            "or",
1133            "order",
1134            "outer",
1135            "over",
1136            "partition",
1137            "preceding",
1138            "proto",
1139            "qualify",
1140            "range",
1141            "recursive",
1142            "respect",
1143            "right",
1144            "rollup",
1145            "rows",
1146            "select",
1147            "set",
1148            "some",
1149            "struct",
1150            "tablesample",
1151            "then",
1152            "to",
1153            "treat",
1154            "true",
1155            "unbounded",
1156            "union",
1157            "unnest",
1158            "using",
1159            "when",
1160            "where",
1161            "window",
1162            "with",
1163            "within",
1164        }
1165
1166        def mod_sql(self, expression: exp.Mod) -> str:
1167            this = expression.this
1168            expr = expression.expression
1169            return self.func(
1170                "MOD",
1171                this.unnest() if isinstance(this, exp.Paren) else this,
1172                expr.unnest() if isinstance(expr, exp.Paren) else expr,
1173            )
1174
1175        def column_parts(self, expression: exp.Column) -> str:
1176            if expression.meta.get("quoted_column"):
1177                # If a column reference is of the form `dataset.table`.name, we need
1178                # to preserve the quoted table path, otherwise the reference breaks
1179                table_parts = ".".join(p.name for p in expression.parts[:-1])
1180                table_path = self.sql(exp.Identifier(this=table_parts, quoted=True))
1181                return f"{table_path}.{self.sql(expression, 'this')}"
1182
1183            return super().column_parts(expression)
1184
1185        def table_parts(self, expression: exp.Table) -> str:
1186            # Depending on the context, `x.y` may not resolve to the same data source as `x`.`y`, so
1187            # we need to make sure the correct quoting is used in each case.
1188            #
1189            # For example, if there is a CTE x that clashes with a schema name, then the former will
1190            # return the table y in that schema, whereas the latter will return the CTE's y column:
1191            #
1192            # - WITH x AS (SELECT [1, 2] AS y) SELECT * FROM x, `x.y`   -> cross join
1193            # - WITH x AS (SELECT [1, 2] AS y) SELECT * FROM x, `x`.`y` -> implicit unnest
1194            if expression.meta.get("quoted_table"):
1195                table_parts = ".".join(p.name for p in expression.parts)
1196                return self.sql(exp.Identifier(this=table_parts, quoted=True))
1197
1198            return super().table_parts(expression)
1199
1200        def timetostr_sql(self, expression: exp.TimeToStr) -> str:
1201            this = expression.this
1202            if isinstance(this, exp.TsOrDsToDatetime):
1203                func_name = "FORMAT_DATETIME"
1204            elif isinstance(this, exp.TsOrDsToTimestamp):
1205                func_name = "FORMAT_TIMESTAMP"
1206            else:
1207                func_name = "FORMAT_DATE"
1208
1209            time_expr = (
1210                this
1211                if isinstance(this, (exp.TsOrDsToDatetime, exp.TsOrDsToTimestamp, exp.TsOrDsToDate))
1212                else expression
1213            )
1214            return self.func(
1215                func_name, self.format_time(expression), time_expr.this, expression.args.get("zone")
1216            )
1217
1218        def eq_sql(self, expression: exp.EQ) -> str:
1219            # Operands of = cannot be NULL in BigQuery
1220            if isinstance(expression.left, exp.Null) or isinstance(expression.right, exp.Null):
1221                if not isinstance(expression.parent, exp.Update):
1222                    return "NULL"
1223
1224            return self.binary(expression, "=")
1225
1226        def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
1227            parent = expression.parent
1228
1229            # BigQuery allows CAST(.. AS {STRING|TIMESTAMP} [FORMAT <fmt> [AT TIME ZONE <tz>]]).
1230            # Only the TIMESTAMP one should use the below conversion, when AT TIME ZONE is included.
1231            if not isinstance(parent, exp.Cast) or not parent.to.is_type("text"):
1232                return self.func(
1233                    "TIMESTAMP", self.func("DATETIME", expression.this, expression.args.get("zone"))
1234                )
1235
1236            return super().attimezone_sql(expression)
1237
1238        def trycast_sql(self, expression: exp.TryCast) -> str:
1239            return self.cast_sql(expression, safe_prefix="SAFE_")
1240
1241        def bracket_sql(self, expression: exp.Bracket) -> str:
1242            this = expression.this
1243            expressions = expression.expressions
1244
1245            if len(expressions) == 1 and this and this.is_type(exp.DataType.Type.STRUCT):
1246                arg = expressions[0]
1247                if arg.type is None:
1248                    from sqlglot.optimizer.annotate_types import annotate_types
1249
1250                    arg = annotate_types(arg, dialect=self.dialect)
1251
1252                if arg.type and arg.type.this in exp.DataType.TEXT_TYPES:
1253                    # BQ doesn't support bracket syntax with string values for structs
1254                    return f"{self.sql(this)}.{arg.name}"
1255
1256            expressions_sql = self.expressions(expression, flat=True)
1257            offset = expression.args.get("offset")
1258
1259            if offset == 0:
1260                expressions_sql = f"OFFSET({expressions_sql})"
1261            elif offset == 1:
1262                expressions_sql = f"ORDINAL({expressions_sql})"
1263            elif offset is not None:
1264                self.unsupported(f"Unsupported array offset: {offset}")
1265
1266            if expression.args.get("safe"):
1267                expressions_sql = f"SAFE_{expressions_sql}"
1268
1269            return f"{self.sql(this)}[{expressions_sql}]"
1270
1271        def in_unnest_op(self, expression: exp.Unnest) -> str:
1272            return self.sql(expression)
1273
1274        def version_sql(self, expression: exp.Version) -> str:
1275            if expression.name == "TIMESTAMP":
1276                expression.set("this", "SYSTEM_TIME")
1277            return super().version_sql(expression)
1278
1279        def contains_sql(self, expression: exp.Contains) -> str:
1280            this = expression.this
1281            expr = expression.expression
1282
1283            if isinstance(this, exp.Lower) and isinstance(expr, exp.Lower):
1284                this = this.this
1285                expr = expr.this
1286
1287            return self.func("CONTAINS_SUBSTR", this, expr)
1288
1289        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
1290            this = expression.this
1291
1292            # This ensures that inline type-annotated ARRAY literals like ARRAY<INT64>[1, 2, 3]
1293            # are roundtripped unaffected. The inner check excludes ARRAY(SELECT ...) expressions,
1294            # because they aren't literals and so the above syntax is invalid BigQuery.
1295            if isinstance(this, exp.Array):
1296                elem = seq_get(this.expressions, 0)
1297                if not (elem and elem.find(exp.Query)):
1298                    return f"{self.sql(expression, 'to')}{self.sql(this)}"
1299
1300            return super().cast_sql(expression, safe_prefix=safe_prefix)