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._typing import E
  9from sqlglot.dialects.dialect import (
 10    Dialect,
 11    binary_from_function,
 12    date_add_interval_sql,
 13    datestrtodate_sql,
 14    format_time_lambda,
 15    if_sql,
 16    inline_array_sql,
 17    json_keyvalue_comma_sql,
 18    max_or_greatest,
 19    min_or_least,
 20    no_ilike_sql,
 21    parse_date_delta_with_interval,
 22    regexp_replace_sql,
 23    rename_func,
 24    timestrtotime_sql,
 25    ts_or_ds_to_date_sql,
 26)
 27from sqlglot.helper import seq_get, split_num_words
 28from sqlglot.tokens import TokenType
 29
 30logger = logging.getLogger("sqlglot")
 31
 32
 33def _derived_table_values_to_unnest(self: BigQuery.Generator, expression: exp.Values) -> str:
 34    if not expression.find_ancestor(exp.From, exp.Join):
 35        return self.values_sql(expression)
 36
 37    alias = expression.args.get("alias")
 38
 39    structs = [
 40        exp.Struct(
 41            expressions=[
 42                exp.alias_(value, column_name)
 43                for value, column_name in zip(
 44                    t.expressions,
 45                    alias.columns
 46                    if alias and alias.columns
 47                    else (f"_c{i}" for i in range(len(t.expressions))),
 48                )
 49            ]
 50        )
 51        for t in expression.find_all(exp.Tuple)
 52    ]
 53
 54    return self.unnest_sql(exp.Unnest(expressions=[exp.Array(expressions=structs)]))
 55
 56
 57def _returnsproperty_sql(self: BigQuery.Generator, expression: exp.ReturnsProperty) -> str:
 58    this = expression.this
 59    if isinstance(this, exp.Schema):
 60        this = f"{this.this} <{self.expressions(this)}>"
 61    else:
 62        this = self.sql(this)
 63    return f"RETURNS {this}"
 64
 65
 66def _create_sql(self: BigQuery.Generator, expression: exp.Create) -> str:
 67    kind = expression.args["kind"]
 68    returns = expression.find(exp.ReturnsProperty)
 69
 70    if kind.upper() == "FUNCTION" and returns and returns.args.get("is_table"):
 71        expression = expression.copy()
 72        expression.set("kind", "TABLE FUNCTION")
 73
 74        if isinstance(expression.expression, (exp.Subquery, exp.Literal)):
 75            expression.set("expression", expression.expression.this)
 76
 77        return self.create_sql(expression)
 78
 79    return self.create_sql(expression)
 80
 81
 82def _unqualify_unnest(expression: exp.Expression) -> exp.Expression:
 83    """Remove references to unnest table aliases since bigquery doesn't allow them.
 84
 85    These are added by the optimizer's qualify_column step.
 86    """
 87    from sqlglot.optimizer.scope import find_all_in_scope
 88
 89    if isinstance(expression, exp.Select):
 90        unnest_aliases = {
 91            unnest.alias
 92            for unnest in find_all_in_scope(expression, exp.Unnest)
 93            if isinstance(unnest.parent, (exp.From, exp.Join))
 94        }
 95        if unnest_aliases:
 96            for column in expression.find_all(exp.Column):
 97                if column.table in unnest_aliases:
 98                    column.set("table", None)
 99                elif column.db in unnest_aliases:
100                    column.set("db", None)
101
102    return expression
103
104
105# https://issuetracker.google.com/issues/162294746
106# workaround for bigquery bug when grouping by an expression and then ordering
107# WITH x AS (SELECT 1 y)
108# SELECT y + 1 z
109# FROM x
110# GROUP BY x + 1
111# ORDER by z
112def _alias_ordered_group(expression: exp.Expression) -> exp.Expression:
113    if isinstance(expression, exp.Select):
114        group = expression.args.get("group")
115        order = expression.args.get("order")
116
117        if group and order:
118            aliases = {
119                select.this: select.args["alias"]
120                for select in expression.selects
121                if isinstance(select, exp.Alias)
122            }
123
124            for e in group.expressions:
125                alias = aliases.get(e)
126
127                if alias:
128                    e.replace(exp.column(alias))
129
130    return expression
131
132
133def _pushdown_cte_column_names(expression: exp.Expression) -> exp.Expression:
134    """BigQuery doesn't allow column names when defining a CTE, so we try to push them down."""
135    if isinstance(expression, exp.CTE) and expression.alias_column_names:
136        cte_query = expression.this
137
138        if cte_query.is_star:
139            logger.warning(
140                "Can't push down CTE column names for star queries. Run the query through"
141                " the optimizer or use 'qualify' to expand the star projections first."
142            )
143            return expression
144
145        column_names = expression.alias_column_names
146        expression.args["alias"].set("columns", None)
147
148        for name, select in zip(column_names, cte_query.selects):
149            to_replace = select
150
151            if isinstance(select, exp.Alias):
152                select = select.this
153
154            # Inner aliases are shadowed by the CTE column names
155            to_replace.replace(exp.alias_(select, name))
156
157    return expression
158
159
160def _parse_timestamp(args: t.List) -> exp.StrToTime:
161    this = format_time_lambda(exp.StrToTime, "bigquery")([seq_get(args, 1), seq_get(args, 0)])
162    this.set("zone", seq_get(args, 2))
163    return this
164
165
166def _parse_date(args: t.List) -> exp.Date | exp.DateFromParts:
167    expr_type = exp.DateFromParts if len(args) == 3 else exp.Date
168    return expr_type.from_arg_list(args)
169
170
171def _parse_to_hex(args: t.List) -> exp.Hex | exp.MD5:
172    # TO_HEX(MD5(..)) is common in BigQuery, so it's parsed into MD5 to simplify its transpilation
173    arg = seq_get(args, 0)
174    return exp.MD5(this=arg.this) if isinstance(arg, exp.MD5Digest) else exp.Hex(this=arg)
175
176
177class BigQuery(Dialect):
178    UNNEST_COLUMN_ONLY = True
179    SUPPORTS_USER_DEFINED_TYPES = False
180    SUPPORTS_SEMI_ANTI_JOIN = False
181    LOG_BASE_FIRST = False
182
183    # https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#case_sensitivity
184    RESOLVES_IDENTIFIERS_AS_UPPERCASE = None
185
186    # bigquery udfs are case sensitive
187    NORMALIZE_FUNCTIONS = False
188
189    TIME_MAPPING = {
190        "%D": "%m/%d/%y",
191    }
192
193    ESCAPE_SEQUENCES = {
194        "\\a": "\a",
195        "\\b": "\b",
196        "\\f": "\f",
197        "\\n": "\n",
198        "\\r": "\r",
199        "\\t": "\t",
200        "\\v": "\v",
201    }
202
203    FORMAT_MAPPING = {
204        "DD": "%d",
205        "MM": "%m",
206        "MON": "%b",
207        "MONTH": "%B",
208        "YYYY": "%Y",
209        "YY": "%y",
210        "HH": "%I",
211        "HH12": "%I",
212        "HH24": "%H",
213        "MI": "%M",
214        "SS": "%S",
215        "SSSSS": "%f",
216        "TZH": "%z",
217    }
218
219    # The _PARTITIONTIME and _PARTITIONDATE pseudo-columns are not returned by a SELECT * statement
220    # https://cloud.google.com/bigquery/docs/querying-partitioned-tables#query_an_ingestion-time_partitioned_table
221    PSEUDOCOLUMNS = {"_PARTITIONTIME", "_PARTITIONDATE"}
222
223    @classmethod
224    def normalize_identifier(cls, expression: E) -> E:
225        if isinstance(expression, exp.Identifier):
226            parent = expression.parent
227            while isinstance(parent, exp.Dot):
228                parent = parent.parent
229
230            # In BigQuery, CTEs aren't case-sensitive, but table names are (by default, at least).
231            # The following check is essentially a heuristic to detect tables based on whether or
232            # not they're qualified. It also avoids normalizing UDFs, because they're case-sensitive.
233            if (
234                not isinstance(parent, exp.UserDefinedFunction)
235                and not (isinstance(parent, exp.Table) and parent.db)
236                and not expression.meta.get("is_table")
237            ):
238                expression.set("this", expression.this.lower())
239
240        return expression
241
242    class Tokenizer(tokens.Tokenizer):
243        QUOTES = ["'", '"', '"""', "'''"]
244        COMMENTS = ["--", "#", ("/*", "*/")]
245        IDENTIFIERS = ["`"]
246        STRING_ESCAPES = ["\\"]
247
248        HEX_STRINGS = [("0x", ""), ("0X", "")]
249
250        BYTE_STRINGS = [
251            (prefix + q, q) for q in t.cast(t.List[str], QUOTES) for prefix in ("b", "B")
252        ]
253
254        RAW_STRINGS = [
255            (prefix + q, q) for q in t.cast(t.List[str], QUOTES) for prefix in ("r", "R")
256        ]
257
258        KEYWORDS = {
259            **tokens.Tokenizer.KEYWORDS,
260            "ANY TYPE": TokenType.VARIANT,
261            "BEGIN": TokenType.COMMAND,
262            "BEGIN TRANSACTION": TokenType.BEGIN,
263            "CURRENT_DATETIME": TokenType.CURRENT_DATETIME,
264            "BYTES": TokenType.BINARY,
265            "DECLARE": TokenType.COMMAND,
266            "FLOAT64": TokenType.DOUBLE,
267            "INT64": TokenType.BIGINT,
268            "RECORD": TokenType.STRUCT,
269            "TIMESTAMP": TokenType.TIMESTAMPTZ,
270            "NOT DETERMINISTIC": TokenType.VOLATILE,
271            "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT,
272        }
273        KEYWORDS.pop("DIV")
274
275    class Parser(parser.Parser):
276        PREFIXED_PIVOT_COLUMNS = True
277
278        LOG_DEFAULTS_TO_LN = True
279
280        FUNCTIONS = {
281            **parser.Parser.FUNCTIONS,
282            "DATE": _parse_date,
283            "DATE_ADD": parse_date_delta_with_interval(exp.DateAdd),
284            "DATE_SUB": parse_date_delta_with_interval(exp.DateSub),
285            "DATE_TRUNC": lambda args: exp.DateTrunc(
286                unit=exp.Literal.string(str(seq_get(args, 1))),
287                this=seq_get(args, 0),
288            ),
289            "DATETIME_ADD": parse_date_delta_with_interval(exp.DatetimeAdd),
290            "DATETIME_SUB": parse_date_delta_with_interval(exp.DatetimeSub),
291            "DIV": binary_from_function(exp.IntDiv),
292            "GENERATE_ARRAY": exp.GenerateSeries.from_arg_list,
293            "MD5": exp.MD5Digest.from_arg_list,
294            "TO_HEX": _parse_to_hex,
295            "PARSE_DATE": lambda args: format_time_lambda(exp.StrToDate, "bigquery")(
296                [seq_get(args, 1), seq_get(args, 0)]
297            ),
298            "PARSE_TIMESTAMP": _parse_timestamp,
299            "REGEXP_CONTAINS": exp.RegexpLike.from_arg_list,
300            "REGEXP_EXTRACT": lambda args: exp.RegexpExtract(
301                this=seq_get(args, 0),
302                expression=seq_get(args, 1),
303                position=seq_get(args, 2),
304                occurrence=seq_get(args, 3),
305                group=exp.Literal.number(1) if re.compile(args[1].name).groups == 1 else None,
306            ),
307            "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)),
308            "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)),
309            "SPLIT": lambda args: exp.Split(
310                # https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split
311                this=seq_get(args, 0),
312                expression=seq_get(args, 1) or exp.Literal.string(","),
313            ),
314            "TIME_ADD": parse_date_delta_with_interval(exp.TimeAdd),
315            "TIME_SUB": parse_date_delta_with_interval(exp.TimeSub),
316            "TIMESTAMP_ADD": parse_date_delta_with_interval(exp.TimestampAdd),
317            "TIMESTAMP_SUB": parse_date_delta_with_interval(exp.TimestampSub),
318            "TO_JSON_STRING": exp.JSONFormat.from_arg_list,
319        }
320
321        FUNCTION_PARSERS = {
322            **parser.Parser.FUNCTION_PARSERS,
323            "ARRAY": lambda self: self.expression(exp.Array, expressions=[self._parse_statement()]),
324        }
325        FUNCTION_PARSERS.pop("TRIM")
326
327        NO_PAREN_FUNCTIONS = {
328            **parser.Parser.NO_PAREN_FUNCTIONS,
329            TokenType.CURRENT_DATETIME: exp.CurrentDatetime,
330        }
331
332        NESTED_TYPE_TOKENS = {
333            *parser.Parser.NESTED_TYPE_TOKENS,
334            TokenType.TABLE,
335        }
336
337        ID_VAR_TOKENS = {
338            *parser.Parser.ID_VAR_TOKENS,
339            TokenType.VALUES,
340        }
341
342        PROPERTY_PARSERS = {
343            **parser.Parser.PROPERTY_PARSERS,
344            "NOT DETERMINISTIC": lambda self: self.expression(
345                exp.StabilityProperty, this=exp.Literal.string("VOLATILE")
346            ),
347            "OPTIONS": lambda self: self._parse_with_property(),
348        }
349
350        CONSTRAINT_PARSERS = {
351            **parser.Parser.CONSTRAINT_PARSERS,
352            "OPTIONS": lambda self: exp.Properties(expressions=self._parse_with_property()),
353        }
354
355        RANGE_PARSERS = parser.Parser.RANGE_PARSERS.copy()
356        RANGE_PARSERS.pop(TokenType.OVERLAPS, None)
357
358        NULL_TOKENS = {TokenType.NULL, TokenType.UNKNOWN}
359
360        def _parse_table_part(self, schema: bool = False) -> t.Optional[exp.Expression]:
361            this = super()._parse_table_part(schema=schema) or self._parse_number()
362
363            # https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#table_names
364            if isinstance(this, exp.Identifier):
365                table_name = this.name
366                while self._match(TokenType.DASH, advance=False) and self._next:
367                    self._advance(2)
368                    table_name += f"-{self._prev.text}"
369
370                this = exp.Identifier(this=table_name, quoted=this.args.get("quoted"))
371            elif isinstance(this, exp.Literal):
372                table_name = this.name
373
374                if (
375                    self._curr
376                    and self._prev.end == self._curr.start - 1
377                    and self._parse_var(any_token=True)
378                ):
379                    table_name += self._prev.text
380
381                this = exp.Identifier(this=table_name, quoted=True)
382
383            return this
384
385        def _parse_table_parts(self, schema: bool = False) -> exp.Table:
386            table = super()._parse_table_parts(schema=schema)
387            if isinstance(table.this, exp.Identifier) and "." in table.name:
388                catalog, db, this, *rest = (
389                    t.cast(t.Optional[exp.Expression], exp.to_identifier(x))
390                    for x in split_num_words(table.name, ".", 3)
391                )
392
393                if rest and this:
394                    this = exp.Dot.build(t.cast(t.List[exp.Expression], [this, *rest]))
395
396                table = exp.Table(this=this, db=db, catalog=catalog)
397
398            return table
399
400        def _parse_json_object(self) -> exp.JSONObject:
401            json_object = super()._parse_json_object()
402            array_kv_pair = seq_get(json_object.expressions, 0)
403
404            # Converts BQ's "signature 2" of JSON_OBJECT into SQLGlot's canonical representation
405            # https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_object_signature2
406            if (
407                array_kv_pair
408                and isinstance(array_kv_pair.this, exp.Array)
409                and isinstance(array_kv_pair.expression, exp.Array)
410            ):
411                keys = array_kv_pair.this.expressions
412                values = array_kv_pair.expression.expressions
413
414                json_object.set(
415                    "expressions",
416                    [exp.JSONKeyValue(this=k, expression=v) for k, v in zip(keys, values)],
417                )
418
419            return json_object
420
421    class Generator(generator.Generator):
422        EXPLICIT_UNION = True
423        INTERVAL_ALLOWS_PLURAL_FORM = False
424        JOIN_HINTS = False
425        QUERY_HINTS = False
426        TABLE_HINTS = False
427        LIMIT_FETCH = "LIMIT"
428        RENAME_TABLE_WITH_DB = False
429        NVL2_SUPPORTED = False
430        UNNEST_WITH_ORDINALITY = False
431        COLLATE_IS_FUNC = True
432
433        TRANSFORMS = {
434            **generator.Generator.TRANSFORMS,
435            exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"),
436            exp.ArraySize: rename_func("ARRAY_LENGTH"),
437            exp.Cast: transforms.preprocess([transforms.remove_precision_parameterized_types]),
438            exp.Create: _create_sql,
439            exp.CTE: transforms.preprocess([_pushdown_cte_column_names]),
440            exp.DateAdd: date_add_interval_sql("DATE", "ADD"),
441            exp.DateDiff: lambda self, e: f"DATE_DIFF({self.sql(e, 'this')}, {self.sql(e, 'expression')}, {self.sql(e.args.get('unit', 'DAY'))})",
442            exp.DateFromParts: rename_func("DATE"),
443            exp.DateStrToDate: datestrtodate_sql,
444            exp.DateSub: date_add_interval_sql("DATE", "SUB"),
445            exp.DatetimeAdd: date_add_interval_sql("DATETIME", "ADD"),
446            exp.DatetimeSub: date_add_interval_sql("DATETIME", "SUB"),
447            exp.DateTrunc: lambda self, e: self.func("DATE_TRUNC", e.this, e.text("unit")),
448            exp.GenerateSeries: rename_func("GENERATE_ARRAY"),
449            exp.GroupConcat: rename_func("STRING_AGG"),
450            exp.Hex: rename_func("TO_HEX"),
451            exp.If: if_sql(false_value="NULL"),
452            exp.ILike: no_ilike_sql,
453            exp.IntDiv: rename_func("DIV"),
454            exp.JSONFormat: rename_func("TO_JSON_STRING"),
455            exp.JSONKeyValue: json_keyvalue_comma_sql,
456            exp.Max: max_or_greatest,
457            exp.MD5: lambda self, e: self.func("TO_HEX", self.func("MD5", e.this)),
458            exp.MD5Digest: rename_func("MD5"),
459            exp.Min: min_or_least,
460            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
461            exp.RegexpExtract: lambda self, e: self.func(
462                "REGEXP_EXTRACT",
463                e.this,
464                e.expression,
465                e.args.get("position"),
466                e.args.get("occurrence"),
467            ),
468            exp.RegexpReplace: regexp_replace_sql,
469            exp.RegexpLike: rename_func("REGEXP_CONTAINS"),
470            exp.ReturnsProperty: _returnsproperty_sql,
471            exp.Select: transforms.preprocess(
472                [
473                    transforms.explode_to_unnest(),
474                    _unqualify_unnest,
475                    transforms.eliminate_distinct_on,
476                    _alias_ordered_group,
477                    transforms.eliminate_semi_and_anti_joins,
478                ]
479            ),
480            exp.SHA2: lambda self, e: self.func(
481                f"SHA256" if e.text("length") == "256" else "SHA512", e.this
482            ),
483            exp.StabilityProperty: lambda self, e: f"DETERMINISTIC"
484            if e.name == "IMMUTABLE"
485            else "NOT DETERMINISTIC",
486            exp.StrToDate: lambda self, e: f"PARSE_DATE({self.format_time(e)}, {self.sql(e, 'this')})",
487            exp.StrToTime: lambda self, e: self.func(
488                "PARSE_TIMESTAMP", self.format_time(e), e.this, e.args.get("zone")
489            ),
490            exp.TimeAdd: date_add_interval_sql("TIME", "ADD"),
491            exp.TimeSub: date_add_interval_sql("TIME", "SUB"),
492            exp.TimestampAdd: date_add_interval_sql("TIMESTAMP", "ADD"),
493            exp.TimestampSub: date_add_interval_sql("TIMESTAMP", "SUB"),
494            exp.TimeStrToTime: timestrtotime_sql,
495            exp.Trim: lambda self, e: self.func(f"TRIM", e.this, e.expression),
496            exp.TsOrDsAdd: date_add_interval_sql("DATE", "ADD"),
497            exp.TsOrDsToDate: ts_or_ds_to_date_sql("bigquery"),
498            exp.Unhex: rename_func("FROM_HEX"),
499            exp.Values: _derived_table_values_to_unnest,
500            exp.VariancePop: rename_func("VAR_POP"),
501        }
502
503        TYPE_MAPPING = {
504            **generator.Generator.TYPE_MAPPING,
505            exp.DataType.Type.BIGDECIMAL: "BIGNUMERIC",
506            exp.DataType.Type.BIGINT: "INT64",
507            exp.DataType.Type.BINARY: "BYTES",
508            exp.DataType.Type.BOOLEAN: "BOOL",
509            exp.DataType.Type.CHAR: "STRING",
510            exp.DataType.Type.DECIMAL: "NUMERIC",
511            exp.DataType.Type.DOUBLE: "FLOAT64",
512            exp.DataType.Type.FLOAT: "FLOAT64",
513            exp.DataType.Type.INT: "INT64",
514            exp.DataType.Type.NCHAR: "STRING",
515            exp.DataType.Type.NVARCHAR: "STRING",
516            exp.DataType.Type.SMALLINT: "INT64",
517            exp.DataType.Type.TEXT: "STRING",
518            exp.DataType.Type.TIMESTAMP: "DATETIME",
519            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
520            exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
521            exp.DataType.Type.TINYINT: "INT64",
522            exp.DataType.Type.VARBINARY: "BYTES",
523            exp.DataType.Type.VARCHAR: "STRING",
524            exp.DataType.Type.VARIANT: "ANY TYPE",
525        }
526
527        PROPERTIES_LOCATION = {
528            **generator.Generator.PROPERTIES_LOCATION,
529            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
530            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
531        }
532
533        # from: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#reserved_keywords
534        RESERVED_KEYWORDS = {
535            *generator.Generator.RESERVED_KEYWORDS,
536            "all",
537            "and",
538            "any",
539            "array",
540            "as",
541            "asc",
542            "assert_rows_modified",
543            "at",
544            "between",
545            "by",
546            "case",
547            "cast",
548            "collate",
549            "contains",
550            "create",
551            "cross",
552            "cube",
553            "current",
554            "default",
555            "define",
556            "desc",
557            "distinct",
558            "else",
559            "end",
560            "enum",
561            "escape",
562            "except",
563            "exclude",
564            "exists",
565            "extract",
566            "false",
567            "fetch",
568            "following",
569            "for",
570            "from",
571            "full",
572            "group",
573            "grouping",
574            "groups",
575            "hash",
576            "having",
577            "if",
578            "ignore",
579            "in",
580            "inner",
581            "intersect",
582            "interval",
583            "into",
584            "is",
585            "join",
586            "lateral",
587            "left",
588            "like",
589            "limit",
590            "lookup",
591            "merge",
592            "natural",
593            "new",
594            "no",
595            "not",
596            "null",
597            "nulls",
598            "of",
599            "on",
600            "or",
601            "order",
602            "outer",
603            "over",
604            "partition",
605            "preceding",
606            "proto",
607            "qualify",
608            "range",
609            "recursive",
610            "respect",
611            "right",
612            "rollup",
613            "rows",
614            "select",
615            "set",
616            "some",
617            "struct",
618            "tablesample",
619            "then",
620            "to",
621            "treat",
622            "true",
623            "unbounded",
624            "union",
625            "unnest",
626            "using",
627            "when",
628            "where",
629            "window",
630            "with",
631            "within",
632        }
633
634        def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
635            parent = expression.parent
636
637            # BigQuery allows CAST(.. AS {STRING|TIMESTAMP} [FORMAT <fmt> [AT TIME ZONE <tz>]]).
638            # Only the TIMESTAMP one should use the below conversion, when AT TIME ZONE is included.
639            if not isinstance(parent, exp.Cast) or not parent.to.is_type("text"):
640                return self.func(
641                    "TIMESTAMP", self.func("DATETIME", expression.this, expression.args.get("zone"))
642                )
643
644            return super().attimezone_sql(expression)
645
646        def trycast_sql(self, expression: exp.TryCast) -> str:
647            return self.cast_sql(expression, safe_prefix="SAFE_")
648
649        def cte_sql(self, expression: exp.CTE) -> str:
650            if expression.alias_column_names:
651                self.unsupported("Column names in CTE definition are not supported.")
652            return super().cte_sql(expression)
653
654        def array_sql(self, expression: exp.Array) -> str:
655            first_arg = seq_get(expression.expressions, 0)
656            if isinstance(first_arg, exp.Subqueryable):
657                return f"ARRAY{self.wrap(self.sql(first_arg))}"
658
659            return inline_array_sql(self, expression)
660
661        def transaction_sql(self, *_) -> str:
662            return "BEGIN TRANSACTION"
663
664        def commit_sql(self, *_) -> str:
665            return "COMMIT TRANSACTION"
666
667        def rollback_sql(self, *_) -> str:
668            return "ROLLBACK TRANSACTION"
669
670        def in_unnest_op(self, expression: exp.Unnest) -> str:
671            return self.sql(expression)
672
673        def except_op(self, expression: exp.Except) -> str:
674            if not expression.args.get("distinct", False):
675                self.unsupported("EXCEPT without DISTINCT is not supported in BigQuery")
676            return f"EXCEPT{' DISTINCT' if expression.args.get('distinct') else ' ALL'}"
677
678        def intersect_op(self, expression: exp.Intersect) -> str:
679            if not expression.args.get("distinct", False):
680                self.unsupported("INTERSECT without DISTINCT is not supported in BigQuery")
681            return f"INTERSECT{' DISTINCT' if expression.args.get('distinct') else ' ALL'}"
682
683        def with_properties(self, properties: exp.Properties) -> str:
684            return self.properties(properties, prefix=self.seg("OPTIONS"))
685
686        def version_sql(self, expression: exp.Version) -> str:
687            if expression.name == "TIMESTAMP":
688                expression = expression.copy()
689                expression.set("this", "SYSTEM_TIME")
690            return super().version_sql(expression)