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            "BYTES": TokenType.BINARY,
264            "CURRENT_DATETIME": TokenType.CURRENT_DATETIME,
265            "DECLARE": TokenType.COMMAND,
266            "FLOAT64": TokenType.DOUBLE,
267            "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT,
268            "INT64": TokenType.BIGINT,
269            "MODEL": TokenType.MODEL,
270            "NOT DETERMINISTIC": TokenType.VOLATILE,
271            "RECORD": TokenType.STRUCT,
272            "TIMESTAMP": TokenType.TIMESTAMPTZ,
273        }
274        KEYWORDS.pop("DIV")
275
276    class Parser(parser.Parser):
277        PREFIXED_PIVOT_COLUMNS = True
278
279        LOG_DEFAULTS_TO_LN = True
280
281        FUNCTIONS = {
282            **parser.Parser.FUNCTIONS,
283            "DATE": _parse_date,
284            "DATE_ADD": parse_date_delta_with_interval(exp.DateAdd),
285            "DATE_SUB": parse_date_delta_with_interval(exp.DateSub),
286            "DATE_TRUNC": lambda args: exp.DateTrunc(
287                unit=exp.Literal.string(str(seq_get(args, 1))),
288                this=seq_get(args, 0),
289            ),
290            "DATETIME_ADD": parse_date_delta_with_interval(exp.DatetimeAdd),
291            "DATETIME_SUB": parse_date_delta_with_interval(exp.DatetimeSub),
292            "DIV": binary_from_function(exp.IntDiv),
293            "GENERATE_ARRAY": exp.GenerateSeries.from_arg_list,
294            "MD5": exp.MD5Digest.from_arg_list,
295            "TO_HEX": _parse_to_hex,
296            "PARSE_DATE": lambda args: format_time_lambda(exp.StrToDate, "bigquery")(
297                [seq_get(args, 1), seq_get(args, 0)]
298            ),
299            "PARSE_TIMESTAMP": _parse_timestamp,
300            "REGEXP_CONTAINS": exp.RegexpLike.from_arg_list,
301            "REGEXP_EXTRACT": lambda args: exp.RegexpExtract(
302                this=seq_get(args, 0),
303                expression=seq_get(args, 1),
304                position=seq_get(args, 2),
305                occurrence=seq_get(args, 3),
306                group=exp.Literal.number(1) if re.compile(args[1].name).groups == 1 else None,
307            ),
308            "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)),
309            "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)),
310            "SPLIT": lambda args: exp.Split(
311                # https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split
312                this=seq_get(args, 0),
313                expression=seq_get(args, 1) or exp.Literal.string(","),
314            ),
315            "TIME_ADD": parse_date_delta_with_interval(exp.TimeAdd),
316            "TIME_SUB": parse_date_delta_with_interval(exp.TimeSub),
317            "TIMESTAMP_ADD": parse_date_delta_with_interval(exp.TimestampAdd),
318            "TIMESTAMP_SUB": parse_date_delta_with_interval(exp.TimestampSub),
319            "TO_JSON_STRING": exp.JSONFormat.from_arg_list,
320        }
321
322        FUNCTION_PARSERS = {
323            **parser.Parser.FUNCTION_PARSERS,
324            "ARRAY": lambda self: self.expression(exp.Array, expressions=[self._parse_statement()]),
325        }
326        FUNCTION_PARSERS.pop("TRIM")
327
328        NO_PAREN_FUNCTIONS = {
329            **parser.Parser.NO_PAREN_FUNCTIONS,
330            TokenType.CURRENT_DATETIME: exp.CurrentDatetime,
331        }
332
333        NESTED_TYPE_TOKENS = {
334            *parser.Parser.NESTED_TYPE_TOKENS,
335            TokenType.TABLE,
336        }
337
338        ID_VAR_TOKENS = {
339            *parser.Parser.ID_VAR_TOKENS,
340            TokenType.VALUES,
341        }
342
343        PROPERTY_PARSERS = {
344            **parser.Parser.PROPERTY_PARSERS,
345            "NOT DETERMINISTIC": lambda self: self.expression(
346                exp.StabilityProperty, this=exp.Literal.string("VOLATILE")
347            ),
348            "OPTIONS": lambda self: self._parse_with_property(),
349        }
350
351        CONSTRAINT_PARSERS = {
352            **parser.Parser.CONSTRAINT_PARSERS,
353            "OPTIONS": lambda self: exp.Properties(expressions=self._parse_with_property()),
354        }
355
356        RANGE_PARSERS = parser.Parser.RANGE_PARSERS.copy()
357        RANGE_PARSERS.pop(TokenType.OVERLAPS, None)
358
359        NULL_TOKENS = {TokenType.NULL, TokenType.UNKNOWN}
360
361        def _parse_table_part(self, schema: bool = False) -> t.Optional[exp.Expression]:
362            this = super()._parse_table_part(schema=schema) or self._parse_number()
363
364            # https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#table_names
365            if isinstance(this, exp.Identifier):
366                table_name = this.name
367                while self._match(TokenType.DASH, advance=False) and self._next:
368                    self._advance(2)
369                    table_name += f"-{self._prev.text}"
370
371                this = exp.Identifier(this=table_name, quoted=this.args.get("quoted"))
372            elif isinstance(this, exp.Literal):
373                table_name = this.name
374
375                if (
376                    self._curr
377                    and self._prev.end == self._curr.start - 1
378                    and self._parse_var(any_token=True)
379                ):
380                    table_name += self._prev.text
381
382                this = exp.Identifier(this=table_name, quoted=True)
383
384            return this
385
386        def _parse_table_parts(self, schema: bool = False) -> exp.Table:
387            table = super()._parse_table_parts(schema=schema)
388            if isinstance(table.this, exp.Identifier) and "." in table.name:
389                catalog, db, this, *rest = (
390                    t.cast(t.Optional[exp.Expression], exp.to_identifier(x))
391                    for x in split_num_words(table.name, ".", 3)
392                )
393
394                if rest and this:
395                    this = exp.Dot.build(t.cast(t.List[exp.Expression], [this, *rest]))
396
397                table = exp.Table(this=this, db=db, catalog=catalog)
398
399            return table
400
401        def _parse_json_object(self) -> exp.JSONObject:
402            json_object = super()._parse_json_object()
403            array_kv_pair = seq_get(json_object.expressions, 0)
404
405            # Converts BQ's "signature 2" of JSON_OBJECT into SQLGlot's canonical representation
406            # https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_object_signature2
407            if (
408                array_kv_pair
409                and isinstance(array_kv_pair.this, exp.Array)
410                and isinstance(array_kv_pair.expression, exp.Array)
411            ):
412                keys = array_kv_pair.this.expressions
413                values = array_kv_pair.expression.expressions
414
415                json_object.set(
416                    "expressions",
417                    [exp.JSONKeyValue(this=k, expression=v) for k, v in zip(keys, values)],
418                )
419
420            return json_object
421
422    class Generator(generator.Generator):
423        EXPLICIT_UNION = True
424        INTERVAL_ALLOWS_PLURAL_FORM = False
425        JOIN_HINTS = False
426        QUERY_HINTS = False
427        TABLE_HINTS = False
428        LIMIT_FETCH = "LIMIT"
429        RENAME_TABLE_WITH_DB = False
430        NVL2_SUPPORTED = False
431        UNNEST_WITH_ORDINALITY = False
432        COLLATE_IS_FUNC = True
433
434        TRANSFORMS = {
435            **generator.Generator.TRANSFORMS,
436            exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"),
437            exp.ArraySize: rename_func("ARRAY_LENGTH"),
438            exp.Cast: transforms.preprocess([transforms.remove_precision_parameterized_types]),
439            exp.CollateProperty: lambda self, e: f"DEFAULT COLLATE {self.sql(e, 'this')}"
440            if e.args.get("default")
441            else f"COLLATE {self.sql(e, 'this')}",
442            exp.Create: _create_sql,
443            exp.CTE: transforms.preprocess([_pushdown_cte_column_names]),
444            exp.DateAdd: date_add_interval_sql("DATE", "ADD"),
445            exp.DateDiff: lambda self, e: f"DATE_DIFF({self.sql(e, 'this')}, {self.sql(e, 'expression')}, {self.sql(e.args.get('unit', 'DAY'))})",
446            exp.DateFromParts: rename_func("DATE"),
447            exp.DateStrToDate: datestrtodate_sql,
448            exp.DateSub: date_add_interval_sql("DATE", "SUB"),
449            exp.DatetimeAdd: date_add_interval_sql("DATETIME", "ADD"),
450            exp.DatetimeSub: date_add_interval_sql("DATETIME", "SUB"),
451            exp.DateTrunc: lambda self, e: self.func("DATE_TRUNC", e.this, e.text("unit")),
452            exp.GenerateSeries: rename_func("GENERATE_ARRAY"),
453            exp.GroupConcat: rename_func("STRING_AGG"),
454            exp.Hex: rename_func("TO_HEX"),
455            exp.If: if_sql(false_value="NULL"),
456            exp.ILike: no_ilike_sql,
457            exp.IntDiv: rename_func("DIV"),
458            exp.JSONFormat: rename_func("TO_JSON_STRING"),
459            exp.JSONKeyValue: json_keyvalue_comma_sql,
460            exp.Max: max_or_greatest,
461            exp.MD5: lambda self, e: self.func("TO_HEX", self.func("MD5", e.this)),
462            exp.MD5Digest: rename_func("MD5"),
463            exp.Min: min_or_least,
464            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
465            exp.RegexpExtract: lambda self, e: self.func(
466                "REGEXP_EXTRACT",
467                e.this,
468                e.expression,
469                e.args.get("position"),
470                e.args.get("occurrence"),
471            ),
472            exp.RegexpReplace: regexp_replace_sql,
473            exp.RegexpLike: rename_func("REGEXP_CONTAINS"),
474            exp.ReturnsProperty: _returnsproperty_sql,
475            exp.Select: transforms.preprocess(
476                [
477                    transforms.explode_to_unnest(),
478                    _unqualify_unnest,
479                    transforms.eliminate_distinct_on,
480                    _alias_ordered_group,
481                    transforms.eliminate_semi_and_anti_joins,
482                ]
483            ),
484            exp.SHA2: lambda self, e: self.func(
485                f"SHA256" if e.text("length") == "256" else "SHA512", e.this
486            ),
487            exp.StabilityProperty: lambda self, e: f"DETERMINISTIC"
488            if e.name == "IMMUTABLE"
489            else "NOT DETERMINISTIC",
490            exp.StrToDate: lambda self, e: f"PARSE_DATE({self.format_time(e)}, {self.sql(e, 'this')})",
491            exp.StrToTime: lambda self, e: self.func(
492                "PARSE_TIMESTAMP", self.format_time(e), e.this, e.args.get("zone")
493            ),
494            exp.TimeAdd: date_add_interval_sql("TIME", "ADD"),
495            exp.TimeSub: date_add_interval_sql("TIME", "SUB"),
496            exp.TimestampAdd: date_add_interval_sql("TIMESTAMP", "ADD"),
497            exp.TimestampSub: date_add_interval_sql("TIMESTAMP", "SUB"),
498            exp.TimeStrToTime: timestrtotime_sql,
499            exp.Trim: lambda self, e: self.func(f"TRIM", e.this, e.expression),
500            exp.TsOrDsAdd: date_add_interval_sql("DATE", "ADD"),
501            exp.TsOrDsToDate: ts_or_ds_to_date_sql("bigquery"),
502            exp.Unhex: rename_func("FROM_HEX"),
503            exp.Values: _derived_table_values_to_unnest,
504            exp.VariancePop: rename_func("VAR_POP"),
505        }
506
507        TYPE_MAPPING = {
508            **generator.Generator.TYPE_MAPPING,
509            exp.DataType.Type.BIGDECIMAL: "BIGNUMERIC",
510            exp.DataType.Type.BIGINT: "INT64",
511            exp.DataType.Type.BINARY: "BYTES",
512            exp.DataType.Type.BOOLEAN: "BOOL",
513            exp.DataType.Type.CHAR: "STRING",
514            exp.DataType.Type.DECIMAL: "NUMERIC",
515            exp.DataType.Type.DOUBLE: "FLOAT64",
516            exp.DataType.Type.FLOAT: "FLOAT64",
517            exp.DataType.Type.INT: "INT64",
518            exp.DataType.Type.NCHAR: "STRING",
519            exp.DataType.Type.NVARCHAR: "STRING",
520            exp.DataType.Type.SMALLINT: "INT64",
521            exp.DataType.Type.TEXT: "STRING",
522            exp.DataType.Type.TIMESTAMP: "DATETIME",
523            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
524            exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
525            exp.DataType.Type.TINYINT: "INT64",
526            exp.DataType.Type.VARBINARY: "BYTES",
527            exp.DataType.Type.VARCHAR: "STRING",
528            exp.DataType.Type.VARIANT: "ANY TYPE",
529        }
530
531        PROPERTIES_LOCATION = {
532            **generator.Generator.PROPERTIES_LOCATION,
533            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
534            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
535        }
536
537        # from: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#reserved_keywords
538        RESERVED_KEYWORDS = {
539            *generator.Generator.RESERVED_KEYWORDS,
540            "all",
541            "and",
542            "any",
543            "array",
544            "as",
545            "asc",
546            "assert_rows_modified",
547            "at",
548            "between",
549            "by",
550            "case",
551            "cast",
552            "collate",
553            "contains",
554            "create",
555            "cross",
556            "cube",
557            "current",
558            "default",
559            "define",
560            "desc",
561            "distinct",
562            "else",
563            "end",
564            "enum",
565            "escape",
566            "except",
567            "exclude",
568            "exists",
569            "extract",
570            "false",
571            "fetch",
572            "following",
573            "for",
574            "from",
575            "full",
576            "group",
577            "grouping",
578            "groups",
579            "hash",
580            "having",
581            "if",
582            "ignore",
583            "in",
584            "inner",
585            "intersect",
586            "interval",
587            "into",
588            "is",
589            "join",
590            "lateral",
591            "left",
592            "like",
593            "limit",
594            "lookup",
595            "merge",
596            "natural",
597            "new",
598            "no",
599            "not",
600            "null",
601            "nulls",
602            "of",
603            "on",
604            "or",
605            "order",
606            "outer",
607            "over",
608            "partition",
609            "preceding",
610            "proto",
611            "qualify",
612            "range",
613            "recursive",
614            "respect",
615            "right",
616            "rollup",
617            "rows",
618            "select",
619            "set",
620            "some",
621            "struct",
622            "tablesample",
623            "then",
624            "to",
625            "treat",
626            "true",
627            "unbounded",
628            "union",
629            "unnest",
630            "using",
631            "when",
632            "where",
633            "window",
634            "with",
635            "within",
636        }
637
638        def eq_sql(self, expression: exp.EQ) -> str:
639            # Operands of = cannot be NULL in BigQuery
640            if isinstance(expression.left, exp.Null) or isinstance(expression.right, exp.Null):
641                return "NULL"
642
643            return self.binary(expression, "=")
644
645        def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
646            parent = expression.parent
647
648            # BigQuery allows CAST(.. AS {STRING|TIMESTAMP} [FORMAT <fmt> [AT TIME ZONE <tz>]]).
649            # Only the TIMESTAMP one should use the below conversion, when AT TIME ZONE is included.
650            if not isinstance(parent, exp.Cast) or not parent.to.is_type("text"):
651                return self.func(
652                    "TIMESTAMP", self.func("DATETIME", expression.this, expression.args.get("zone"))
653                )
654
655            return super().attimezone_sql(expression)
656
657        def trycast_sql(self, expression: exp.TryCast) -> str:
658            return self.cast_sql(expression, safe_prefix="SAFE_")
659
660        def cte_sql(self, expression: exp.CTE) -> str:
661            if expression.alias_column_names:
662                self.unsupported("Column names in CTE definition are not supported.")
663            return super().cte_sql(expression)
664
665        def array_sql(self, expression: exp.Array) -> str:
666            first_arg = seq_get(expression.expressions, 0)
667            if isinstance(first_arg, exp.Subqueryable):
668                return f"ARRAY{self.wrap(self.sql(first_arg))}"
669
670            return inline_array_sql(self, expression)
671
672        def transaction_sql(self, *_) -> str:
673            return "BEGIN TRANSACTION"
674
675        def commit_sql(self, *_) -> str:
676            return "COMMIT TRANSACTION"
677
678        def rollback_sql(self, *_) -> str:
679            return "ROLLBACK TRANSACTION"
680
681        def in_unnest_op(self, expression: exp.Unnest) -> str:
682            return self.sql(expression)
683
684        def except_op(self, expression: exp.Except) -> str:
685            if not expression.args.get("distinct", False):
686                self.unsupported("EXCEPT without DISTINCT is not supported in BigQuery")
687            return f"EXCEPT{' DISTINCT' if expression.args.get('distinct') else ' ALL'}"
688
689        def intersect_op(self, expression: exp.Intersect) -> str:
690            if not expression.args.get("distinct", False):
691                self.unsupported("INTERSECT without DISTINCT is not supported in BigQuery")
692            return f"INTERSECT{' DISTINCT' if expression.args.get('distinct') else ' ALL'}"
693
694        def with_properties(self, properties: exp.Properties) -> str:
695            return self.properties(properties, prefix=self.seg("OPTIONS"))
696
697        def version_sql(self, expression: exp.Version) -> str:
698            if expression.name == "TIMESTAMP":
699                expression = expression.copy()
700                expression.set("this", "SYSTEM_TIME")
701            return super().version_sql(expression)