sqlglot.dialects.bigquery

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