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