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