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[[generator.Generator, exp.Expression], str]:
 34    def func(self, expression):
 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: generator.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: generator.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: generator.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)
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
372            return this
373
374        def _parse_table_parts(self, schema: bool = False) -> exp.Table:
375            table = super()._parse_table_parts(schema=schema)
376            if isinstance(table.this, exp.Identifier) and "." in table.name:
377                catalog, db, this, *rest = (
378                    t.cast(t.Optional[exp.Expression], exp.to_identifier(x))
379                    for x in split_num_words(table.name, ".", 3)
380                )
381
382                if rest and this:
383                    this = exp.Dot.build(t.cast(t.List[exp.Expression], [this, *rest]))
384
385                table = exp.Table(this=this, db=db, catalog=catalog)
386
387            return table
388
389    class Generator(generator.Generator):
390        EXPLICIT_UNION = True
391        INTERVAL_ALLOWS_PLURAL_FORM = False
392        JOIN_HINTS = False
393        QUERY_HINTS = False
394        TABLE_HINTS = False
395        LIMIT_FETCH = "LIMIT"
396        RENAME_TABLE_WITH_DB = False
397        ESCAPE_LINE_BREAK = True
398        NVL2_SUPPORTED = False
399
400        TRANSFORMS = {
401            **generator.Generator.TRANSFORMS,
402            exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"),
403            exp.ArraySize: rename_func("ARRAY_LENGTH"),
404            exp.Cast: transforms.preprocess([transforms.remove_precision_parameterized_types]),
405            exp.Create: _create_sql,
406            exp.CTE: transforms.preprocess([_pushdown_cte_column_names]),
407            exp.DateAdd: _date_add_sql("DATE", "ADD"),
408            exp.DateDiff: lambda self, e: f"DATE_DIFF({self.sql(e, 'this')}, {self.sql(e, 'expression')}, {self.sql(e.args.get('unit', 'DAY'))})",
409            exp.DateFromParts: rename_func("DATE"),
410            exp.DateStrToDate: datestrtodate_sql,
411            exp.DateSub: _date_add_sql("DATE", "SUB"),
412            exp.DatetimeAdd: _date_add_sql("DATETIME", "ADD"),
413            exp.DatetimeSub: _date_add_sql("DATETIME", "SUB"),
414            exp.DateTrunc: lambda self, e: self.func("DATE_TRUNC", e.this, e.text("unit")),
415            exp.GenerateSeries: rename_func("GENERATE_ARRAY"),
416            exp.GroupConcat: rename_func("STRING_AGG"),
417            exp.Hex: rename_func("TO_HEX"),
418            exp.ILike: no_ilike_sql,
419            exp.IntDiv: rename_func("DIV"),
420            exp.JSONFormat: rename_func("TO_JSON_STRING"),
421            exp.JSONKeyValue: json_keyvalue_comma_sql,
422            exp.Max: max_or_greatest,
423            exp.MD5: lambda self, e: self.func("TO_HEX", self.func("MD5", e.this)),
424            exp.MD5Digest: rename_func("MD5"),
425            exp.Min: min_or_least,
426            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
427            exp.RegexpExtract: lambda self, e: self.func(
428                "REGEXP_EXTRACT",
429                e.this,
430                e.expression,
431                e.args.get("position"),
432                e.args.get("occurrence"),
433            ),
434            exp.RegexpReplace: regexp_replace_sql,
435            exp.RegexpLike: rename_func("REGEXP_CONTAINS"),
436            exp.ReturnsProperty: _returnsproperty_sql,
437            exp.Select: transforms.preprocess(
438                [
439                    transforms.explode_to_unnest,
440                    _unqualify_unnest,
441                    transforms.eliminate_distinct_on,
442                    _alias_ordered_group,
443                ]
444            ),
445            exp.SHA2: lambda self, e: self.func(
446                f"SHA256" if e.text("length") == "256" else "SHA512", e.this
447            ),
448            exp.StabilityProperty: lambda self, e: f"DETERMINISTIC"
449            if e.name == "IMMUTABLE"
450            else "NOT DETERMINISTIC",
451            exp.StrToDate: lambda self, e: f"PARSE_DATE({self.format_time(e)}, {self.sql(e, 'this')})",
452            exp.StrToTime: lambda self, e: self.func(
453                "PARSE_TIMESTAMP", self.format_time(e), e.this, e.args.get("zone")
454            ),
455            exp.TimeAdd: _date_add_sql("TIME", "ADD"),
456            exp.TimeSub: _date_add_sql("TIME", "SUB"),
457            exp.TimestampAdd: _date_add_sql("TIMESTAMP", "ADD"),
458            exp.TimestampSub: _date_add_sql("TIMESTAMP", "SUB"),
459            exp.TimeStrToTime: timestrtotime_sql,
460            exp.Trim: lambda self, e: self.func(f"TRIM", e.this, e.expression),
461            exp.TsOrDsAdd: _date_add_sql("DATE", "ADD"),
462            exp.TsOrDsToDate: ts_or_ds_to_date_sql("bigquery"),
463            exp.Unhex: rename_func("FROM_HEX"),
464            exp.Values: _derived_table_values_to_unnest,
465            exp.VariancePop: rename_func("VAR_POP"),
466        }
467
468        TYPE_MAPPING = {
469            **generator.Generator.TYPE_MAPPING,
470            exp.DataType.Type.BIGDECIMAL: "BIGNUMERIC",
471            exp.DataType.Type.BIGINT: "INT64",
472            exp.DataType.Type.BINARY: "BYTES",
473            exp.DataType.Type.BOOLEAN: "BOOL",
474            exp.DataType.Type.CHAR: "STRING",
475            exp.DataType.Type.DECIMAL: "NUMERIC",
476            exp.DataType.Type.DOUBLE: "FLOAT64",
477            exp.DataType.Type.FLOAT: "FLOAT64",
478            exp.DataType.Type.INT: "INT64",
479            exp.DataType.Type.NCHAR: "STRING",
480            exp.DataType.Type.NVARCHAR: "STRING",
481            exp.DataType.Type.SMALLINT: "INT64",
482            exp.DataType.Type.TEXT: "STRING",
483            exp.DataType.Type.TIMESTAMP: "DATETIME",
484            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
485            exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
486            exp.DataType.Type.TINYINT: "INT64",
487            exp.DataType.Type.VARBINARY: "BYTES",
488            exp.DataType.Type.VARCHAR: "STRING",
489            exp.DataType.Type.VARIANT: "ANY TYPE",
490        }
491
492        PROPERTIES_LOCATION = {
493            **generator.Generator.PROPERTIES_LOCATION,
494            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
495            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
496        }
497
498        # from: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#reserved_keywords
499        RESERVED_KEYWORDS = {
500            *generator.Generator.RESERVED_KEYWORDS,
501            "all",
502            "and",
503            "any",
504            "array",
505            "as",
506            "asc",
507            "assert_rows_modified",
508            "at",
509            "between",
510            "by",
511            "case",
512            "cast",
513            "collate",
514            "contains",
515            "create",
516            "cross",
517            "cube",
518            "current",
519            "default",
520            "define",
521            "desc",
522            "distinct",
523            "else",
524            "end",
525            "enum",
526            "escape",
527            "except",
528            "exclude",
529            "exists",
530            "extract",
531            "false",
532            "fetch",
533            "following",
534            "for",
535            "from",
536            "full",
537            "group",
538            "grouping",
539            "groups",
540            "hash",
541            "having",
542            "if",
543            "ignore",
544            "in",
545            "inner",
546            "intersect",
547            "interval",
548            "into",
549            "is",
550            "join",
551            "lateral",
552            "left",
553            "like",
554            "limit",
555            "lookup",
556            "merge",
557            "natural",
558            "new",
559            "no",
560            "not",
561            "null",
562            "nulls",
563            "of",
564            "on",
565            "or",
566            "order",
567            "outer",
568            "over",
569            "partition",
570            "preceding",
571            "proto",
572            "qualify",
573            "range",
574            "recursive",
575            "respect",
576            "right",
577            "rollup",
578            "rows",
579            "select",
580            "set",
581            "some",
582            "struct",
583            "tablesample",
584            "then",
585            "to",
586            "treat",
587            "true",
588            "unbounded",
589            "union",
590            "unnest",
591            "using",
592            "when",
593            "where",
594            "window",
595            "with",
596            "within",
597        }
598
599        def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
600            parent = expression.parent
601
602            # BigQuery allows CAST(.. AS {STRING|TIMESTAMP} [FORMAT <fmt> [AT TIME ZONE <tz>]]).
603            # Only the TIMESTAMP one should use the below conversion, when AT TIME ZONE is included.
604            if not isinstance(parent, exp.Cast) or not parent.to.is_type("text"):
605                return self.func(
606                    "TIMESTAMP", self.func("DATETIME", expression.this, expression.args.get("zone"))
607                )
608
609            return super().attimezone_sql(expression)
610
611        def trycast_sql(self, expression: exp.TryCast) -> str:
612            return self.cast_sql(expression, safe_prefix="SAFE_")
613
614        def cte_sql(self, expression: exp.CTE) -> str:
615            if expression.alias_column_names:
616                self.unsupported("Column names in CTE definition are not supported.")
617            return super().cte_sql(expression)
618
619        def array_sql(self, expression: exp.Array) -> str:
620            first_arg = seq_get(expression.expressions, 0)
621            if isinstance(first_arg, exp.Subqueryable):
622                return f"ARRAY{self.wrap(self.sql(first_arg))}"
623
624            return inline_array_sql(self, expression)
625
626        def transaction_sql(self, *_) -> str:
627            return "BEGIN TRANSACTION"
628
629        def commit_sql(self, *_) -> str:
630            return "COMMIT TRANSACTION"
631
632        def rollback_sql(self, *_) -> str:
633            return "ROLLBACK TRANSACTION"
634
635        def in_unnest_op(self, expression: exp.Unnest) -> str:
636            return self.sql(expression)
637
638        def except_op(self, expression: exp.Except) -> str:
639            if not expression.args.get("distinct", False):
640                self.unsupported("EXCEPT without DISTINCT is not supported in BigQuery")
641            return f"EXCEPT{' DISTINCT' if expression.args.get('distinct') else ' ALL'}"
642
643        def intersect_op(self, expression: exp.Intersect) -> str:
644            if not expression.args.get("distinct", False):
645                self.unsupported("INTERSECT without DISTINCT is not supported in BigQuery")
646            return f"INTERSECT{' DISTINCT' if expression.args.get('distinct') else ' ALL'}"
647
648        def with_properties(self, properties: exp.Properties) -> str:
649            return self.properties(properties, prefix=self.seg("OPTIONS"))
650
651        def version_sql(self, expression: exp.Version) -> str:
652            if expression.name == "TIMESTAMP":
653                expression = expression.copy()
654                expression.set("this", "SYSTEM_TIME")
655            return super().version_sql(expression)