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