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