sqlglot.generators.bigquery

  1from __future__ import annotations
  2
  3import logging
  4import re
  5import typing as t
  6
  7from sqlglot import exp, generator, transforms
  8from sqlglot.dialects.dialect import (
  9    arg_max_or_min_no_count,
 10    date_add_interval_sql,
 11    datestrtodate_sql,
 12    filter_array_using_unnest,
 13    generate_series_sql,
 14    if_sql,
 15    inline_array_unless_query,
 16    max_or_greatest,
 17    min_or_least,
 18    no_ilike_sql,
 19    regexp_replace_sql,
 20    rename_func,
 21    sha256_sql,
 22    timestrtotime_sql,
 23    ts_or_ds_add_cast,
 24    unit_to_var,
 25    strposition_sql,
 26    groupconcat_sql,
 27    sha2_digest_sql,
 28)
 29from sqlglot.generator import unsupported_args
 30from sqlglot.helper import seq_get
 31
 32logger = logging.getLogger("sqlglot")
 33
 34JSON_EXTRACT_TYPE = t.Union[exp.JSONExtract, exp.JSONExtractScalar, exp.JSONExtractArray]
 35
 36DQUOTES_ESCAPING_JSON_FUNCTIONS = ("JSON_QUERY", "JSON_VALUE", "JSON_QUERY_ARRAY")
 37
 38
 39def _derived_table_values_to_unnest(self: BigQueryGenerator, expression: exp.Values) -> str:
 40    if not expression.find_ancestor(exp.From, exp.Join):
 41        return self.values_sql(expression)
 42
 43    structs = []
 44    alias = expression.args.get("alias")
 45    for tup in expression.find_all(exp.Tuple):
 46        field_aliases = (
 47            alias.columns
 48            if alias and alias.columns
 49            else (f"_c{i}" for i in range(len(tup.expressions)))
 50        )
 51        expressions = [
 52            exp.PropertyEQ(this=exp.to_identifier(name), expression=fld)
 53            for name, fld in zip(field_aliases, tup.expressions)
 54        ]
 55        structs.append(exp.Struct(expressions=expressions))
 56
 57    # Due to `UNNEST_COLUMN_ONLY`, it is expected that the table alias be contained in the columns expression
 58    alias_name_only = exp.TableAlias(columns=[alias.this]) if alias else None
 59    return self.unnest_sql(
 60        exp.Unnest(expressions=[exp.array(*structs, copy=False)], alias=alias_name_only)
 61    )
 62
 63
 64def _returnsproperty_sql(self: BigQueryGenerator, expression: exp.ReturnsProperty) -> str:
 65    this = expression.this
 66    if isinstance(this, exp.Schema):
 67        this = f"{self.sql(this, 'this')} <{self.expressions(this)}>"
 68    else:
 69        this = self.sql(this)
 70    return f"RETURNS {this}"
 71
 72
 73def _create_sql(self: BigQueryGenerator, expression: exp.Create) -> str:
 74    returns = expression.find(exp.ReturnsProperty)
 75    if expression.kind == "FUNCTION" and returns and returns.args.get("is_table"):
 76        expression.set("kind", "TABLE FUNCTION")
 77
 78        if isinstance(expression.expression, (exp.Subquery, exp.Literal)):
 79            expression.set("expression", expression.expression.this)
 80
 81    return self.create_sql(expression)
 82
 83
 84# https://issuetracker.google.com/issues/162294746
 85# workaround for bigquery bug when grouping by an expression and then ordering
 86# WITH x AS (SELECT 1 y)
 87# SELECT y + 1 z
 88# FROM x
 89# GROUP BY x + 1
 90# ORDER by z
 91def _alias_ordered_group(expression: exp.Expr) -> exp.Expr:
 92    if isinstance(expression, exp.Select):
 93        group = expression.args.get("group")
 94        order = expression.args.get("order")
 95
 96        if group and order:
 97            aliases = {
 98                select.this: select.args["alias"]
 99                for select in expression.selects
100                if isinstance(select, exp.Alias)
101            }
102
103            for grouped in group.expressions:
104                if grouped.is_int:
105                    continue
106                alias = aliases.get(grouped)
107                if alias:
108                    grouped.replace(exp.column(alias))
109
110    return expression
111
112
113def _pushdown_cte_column_names(expression: exp.Expr) -> exp.Expr:
114    """BigQuery doesn't allow column names when defining a CTE, so we try to push them down."""
115    if isinstance(expression, exp.CTE) and expression.alias_column_names:
116        cte_query = expression.this
117
118        if cte_query.is_star:
119            logger.warning(
120                "Can't push down CTE column names for star queries. Run the query through"
121                " the optimizer or use 'qualify' to expand the star projections first."
122            )
123            return expression
124
125        column_names = expression.alias_column_names
126        expression.args["alias"].set("columns", None)
127
128        for name, select in zip(column_names, cte_query.selects):
129            to_replace = select
130
131            if isinstance(select, exp.Alias):
132                select = select.this
133
134            # Inner aliases are shadowed by the CTE column names
135            to_replace.replace(exp.alias_(select, name))
136
137    return expression
138
139
140def _unnest_explode_generate_series(expression: exp.Expr) -> exp.Expr:
141    """
142    Rewrites exploding GENERATE_SERIES projections into table references, e.g.
143
144        SELECT GENERATE_SERIES(1, 2) AS x           -> SELECT x FROM GENERATE_SERIES(1, 2) AS x
145        SELECT y, GENERATE_SERIES(1, 2) AS x FROM t -> SELECT y, x FROM t CROSS JOIN GENERATE_SERIES(1, 2) AS x
146
147    since BigQuery can't explode in the projection and must unnest it in the FROM clause instead.
148    The resulting table reference is unnested downstream by `transforms.unnest_generate_series`.
149    """
150    if isinstance(expression, exp.Select):
151        for projection in expression.selects:
152            if isinstance(series := projection.unalias(), exp.ExplodingGenerateSeries):
153                column_name = projection.output_name or "_gen_series_value"
154
155                projection.replace(exp.column(column_name))
156                table = exp.Table(
157                    this=series, alias=exp.TableAlias(this=exp.to_identifier(column_name))
158                )
159
160                if expression.args.get("from_"):
161                    expression.join(table, copy=False, join_type="CROSS")
162                else:
163                    expression.set("from_", exp.From(this=table))
164
165    return expression
166
167
168def _array_contains_sql(self: BigQueryGenerator, expression: exp.ArrayContains) -> str:
169    return self.sql(
170        exp.Exists(
171            this=exp.select("1")
172            .from_(exp.Unnest(expressions=[expression.left]).as_("_unnest", table=["_col"]))
173            .where(exp.column("_col").eq(expression.right))
174        )
175    )
176
177
178def _ts_or_ds_add_sql(self: BigQueryGenerator, expression: exp.TsOrDsAdd) -> str:
179    return date_add_interval_sql("DATE", "ADD")(self, ts_or_ds_add_cast(expression))
180
181
182def _ts_or_ds_diff_sql(self: BigQueryGenerator, expression: exp.TsOrDsDiff) -> str:
183    expression.this.replace(exp.cast(expression.this, exp.DType.TIMESTAMP))
184    expression.expression.replace(exp.cast(expression.expression, exp.DType.TIMESTAMP))
185    unit = unit_to_var(expression)
186    return self.func("DATE_DIFF", expression.this, expression.expression, unit)
187
188
189def _unix_to_time_sql(self: BigQueryGenerator, expression: exp.UnixToTime) -> str:
190    scale = expression.args.get("scale")
191    timestamp = expression.this
192
193    if scale in (None, exp.UnixToTime.SECONDS):
194        return self.func("TIMESTAMP_SECONDS", timestamp)
195    if scale == exp.UnixToTime.MILLIS:
196        return self.func("TIMESTAMP_MILLIS", timestamp)
197    if scale == exp.UnixToTime.MICROS:
198        return self.func("TIMESTAMP_MICROS", timestamp)
199
200    unix_seconds = exp.cast(
201        exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)), exp.DType.BIGINT
202    )
203    return self.func("TIMESTAMP_SECONDS", unix_seconds)
204
205
206def _str_to_datetime_sql(self: BigQueryGenerator, expression: exp.StrToDate | exp.StrToTime) -> str:
207    this = self.sql(expression, "this")
208    dtype = "DATE" if isinstance(expression, exp.StrToDate) else "TIMESTAMP"
209
210    if expression.args.get("safe"):
211        fmt = self.format_time(
212            expression,
213            self.dialect.INVERSE_FORMAT_MAPPING,
214            self.dialect.INVERSE_FORMAT_TRIE,
215        )
216        return f"SAFE_CAST({this} AS {dtype} FORMAT {fmt})"
217
218    fmt = self.format_time(expression)
219    return self.func(f"PARSE_{dtype}", fmt, this, expression.args.get("zone"))
220
221
222@unsupported_args("ins_cost", "del_cost", "sub_cost")
223def _levenshtein_sql(self: BigQueryGenerator, expression: exp.Levenshtein) -> str:
224    max_dist = expression.args.get("max_dist")
225    if max_dist:
226        max_dist = exp.Kwarg(this=exp.var("max_distance"), expression=max_dist)
227
228    return self.func("EDIT_DISTANCE", expression.this, expression.expression, max_dist)
229
230
231def _json_extract_sql(self: BigQueryGenerator, expression: JSON_EXTRACT_TYPE) -> str:
232    name = expression.meta_get("name") or expression.sql_name()
233    upper = name.upper()
234
235    dquote_escaping = upper in DQUOTES_ESCAPING_JSON_FUNCTIONS
236
237    if dquote_escaping:
238        self._quote_json_path_key_using_brackets = False
239
240    sql = rename_func(upper)(self, expression)
241
242    if dquote_escaping:
243        self._quote_json_path_key_using_brackets = True
244
245    return sql
246
247
248class BigQueryGenerator(generator.Generator):
249    TRY_SUPPORTED = False
250    SUPPORTS_UESCAPE = False
251    SUPPORTS_DECODE_CASE = False
252    INTERVAL_ALLOWS_PLURAL_FORM = False
253    JOIN_HINTS = False
254    QUERY_HINTS = False
255    TABLE_HINTS = False
256    LIMIT_FETCH = "LIMIT"
257    RENAME_TABLE_WITH_DB = False
258    NVL2_SUPPORTED = False
259    UNNEST_WITH_ORDINALITY = False
260    COLLATE_IS_FUNC = True
261    LIMIT_ONLY_LITERALS = True
262    SUPPORTS_TABLE_ALIAS_COLUMNS = False
263    SUPPORTS_NAMED_CTE_COLUMNS = False
264    UNPIVOT_ALIASES_ARE_IDENTIFIERS = False
265    JSON_KEY_VALUE_PAIR_SEP = ","
266    NULL_ORDERING_SUPPORTED: bool | None = False
267    IGNORE_NULLS_IN_FUNC = True
268    JSON_PATH_SINGLE_QUOTE_ESCAPE = True
269    CAN_IMPLEMENT_ARRAY_ANY = True
270    SUPPORTS_TO_NUMBER = False
271    NAMED_PLACEHOLDER_TOKEN = "@"
272    HEX_FUNC = "TO_HEX"
273    WITH_PROPERTIES_PREFIX = "OPTIONS"
274    SUPPORTS_EXPLODING_PROJECTIONS = False
275    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
276    SUPPORTS_UNIX_SECONDS = True
277    DECLARE_DEFAULT_ASSIGNMENT = "DEFAULT"
278
279    SAFE_JSON_PATH_KEY_RE = re.compile(r"^[\-\w]*$")
280
281    WINDOW_FUNCS_WITH_NULL_ORDERING = (
282        exp.CumeDist,
283        exp.DenseRank,
284        exp.FirstValue,
285        exp.Lag,
286        exp.LastValue,
287        exp.Lead,
288        exp.NthValue,
289        exp.Ntile,
290        exp.PercentRank,
291        exp.Rank,
292        exp.RowNumber,
293    )
294
295    TS_OR_DS_TYPES = (
296        exp.TsOrDsToDatetime,
297        exp.TsOrDsToTimestamp,
298        exp.TsOrDsToTime,
299        exp.TsOrDsToDate,
300    )
301
302    TRANSFORMS = {
303        **generator.Generator.TRANSFORMS,
304        exp.AIEmbed: rename_func("EMBED"),
305        exp.AIGenerate: rename_func("GENERATE"),
306        exp.AISimilarity: rename_func("SIMILARITY"),
307        exp.ApproxTopK: rename_func("APPROX_TOP_COUNT"),
308        exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"),
309        exp.ArgMax: arg_max_or_min_no_count("MAX_BY"),
310        exp.ArgMin: arg_max_or_min_no_count("MIN_BY"),
311        exp.Array: inline_array_unless_query,
312        exp.ArrayContains: _array_contains_sql,
313        exp.ArrayFilter: filter_array_using_unnest,
314        exp.ArrayRemove: filter_array_using_unnest,
315        exp.BitwiseAndAgg: rename_func("BIT_AND"),
316        exp.BitwiseOrAgg: rename_func("BIT_OR"),
317        exp.BitwiseXorAgg: rename_func("BIT_XOR"),
318        exp.BitwiseCount: rename_func("BIT_COUNT"),
319        exp.ByteLength: rename_func("BYTE_LENGTH"),
320        exp.Cast: transforms.preprocess([transforms.remove_precision_parameterized_types]),
321        exp.CollateProperty: lambda self, e: (
322            f"DEFAULT COLLATE {self.sql(e, 'this')}"
323            if e.args.get("default")
324            else f"COLLATE {self.sql(e, 'this')}"
325        ),
326        exp.Commit: lambda *_: "COMMIT TRANSACTION",
327        exp.CountIf: rename_func("COUNTIF"),
328        exp.Create: _create_sql,
329        exp.CTE: transforms.preprocess([_pushdown_cte_column_names]),
330        exp.DateAdd: date_add_interval_sql("DATE", "ADD"),
331        exp.DateDiff: lambda self, e: self.func("DATE_DIFF", e.this, e.expression, unit_to_var(e)),
332        exp.DateFromParts: rename_func("DATE"),
333        exp.DateStrToDate: datestrtodate_sql,
334        exp.DateSub: date_add_interval_sql("DATE", "SUB"),
335        exp.DatetimeAdd: date_add_interval_sql("DATETIME", "ADD"),
336        exp.DatetimeSub: date_add_interval_sql("DATETIME", "SUB"),
337        exp.DateFromUnixDate: rename_func("DATE_FROM_UNIX_DATE"),
338        exp.FromTimeZone: lambda self, e: self.func(
339            "DATETIME", self.func("TIMESTAMP", e.this, e.args.get("zone")), "'UTC'"
340        ),
341        exp.GenerateSeries: generate_series_sql("GENERATE_ARRAY"),
342        exp.GroupConcat: lambda self, e: groupconcat_sql(
343            self, e, func_name="STRING_AGG", within_group=False, sep=None
344        ),
345        exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))),
346        exp.HexString: lambda self, e: self.hexstring_sql(e, binary_function_repr="FROM_HEX"),
347        exp.If: if_sql(false_value="NULL"),
348        exp.ILike: no_ilike_sql,
349        exp.IntDiv: rename_func("DIV"),
350        exp.Int64: rename_func("INT64"),
351        exp.JSONBool: rename_func("BOOL"),
352        exp.JSONExtract: _json_extract_sql,
353        exp.JSONExtractArray: _json_extract_sql,
354        exp.JSONExtractScalar: _json_extract_sql,
355        exp.JSONFormat: lambda self, e: self.func(
356            "TO_JSON" if e.args.get("to_json") else "TO_JSON_STRING",
357            e.this,
358            e.args.get("options"),
359        ),
360        exp.JSONKeysAtDepth: rename_func("JSON_KEYS"),
361        exp.JSONValueArray: rename_func("JSON_VALUE_ARRAY"),
362        exp.Levenshtein: _levenshtein_sql,
363        exp.Max: max_or_greatest,
364        exp.MD5: lambda self, e: self.func("TO_HEX", self.func("MD5", e.this)),
365        exp.MD5Digest: rename_func("MD5"),
366        exp.Min: min_or_least,
367        exp.Normalize: lambda self, e: self.func(
368            "NORMALIZE_AND_CASEFOLD" if e.args.get("is_casefold") else "NORMALIZE",
369            e.this,
370            e.args.get("form"),
371        ),
372        exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
373        exp.RegexpExtract: lambda self, e: self.func(
374            "REGEXP_EXTRACT",
375            e.this,
376            e.expression,
377            e.args.get("position"),
378            e.args.get("occurrence"),
379        ),
380        exp.RegexpExtractAll: lambda self, e: self.func("REGEXP_EXTRACT_ALL", e.this, e.expression),
381        exp.RegexpReplace: regexp_replace_sql,
382        exp.RegexpLike: rename_func("REGEXP_CONTAINS"),
383        exp.ReturnsProperty: _returnsproperty_sql,
384        exp.Rollback: lambda *_: "ROLLBACK TRANSACTION",
385        exp.ParseTime: lambda self, e: self.func("PARSE_TIME", self.format_time(e), e.this),
386        exp.ParseDatetime: lambda self, e: self.func("PARSE_DATETIME", self.format_time(e), e.this),
387        exp.Select: transforms.preprocess(
388            [
389                _unnest_explode_generate_series,
390                transforms.explode_projection_to_unnest(),
391                transforms.unqualify_unnest,
392                transforms.eliminate_distinct_on,
393                _alias_ordered_group,
394                transforms.eliminate_semi_and_anti_joins,
395            ]
396        ),
397        exp.SHA: rename_func("SHA1"),
398        exp.SHA2: sha256_sql,
399        exp.SHA1Digest: rename_func("SHA1"),
400        exp.SHA2Digest: sha2_digest_sql,
401        exp.StabilityProperty: lambda self, e: (
402            "DETERMINISTIC" if e.name == "IMMUTABLE" else "NOT DETERMINISTIC"
403        ),
404        exp.String: rename_func("STRING"),
405        exp.StrPosition: lambda self, e: strposition_sql(
406            self, e, func_name="INSTR", supports_position=True, supports_occurrence=True
407        ),
408        exp.StrToDate: _str_to_datetime_sql,
409        exp.StrToTime: _str_to_datetime_sql,
410        exp.SessionUser: lambda *_: "SESSION_USER()",
411        exp.Table: transforms.preprocess([transforms.unnest_generate_series]),
412        exp.TimeAdd: date_add_interval_sql("TIME", "ADD"),
413        exp.TimeFromParts: rename_func("TIME"),
414        exp.TimestampFromParts: rename_func("DATETIME"),
415        exp.TimeSub: date_add_interval_sql("TIME", "SUB"),
416        exp.TimestampAdd: date_add_interval_sql("TIMESTAMP", "ADD"),
417        exp.TimestampDiff: rename_func("TIMESTAMP_DIFF"),
418        exp.TimestampSub: date_add_interval_sql("TIMESTAMP", "SUB"),
419        exp.TimeStrToTime: timestrtotime_sql,
420        exp.Transaction: lambda *_: "BEGIN TRANSACTION",
421        exp.TsOrDsAdd: _ts_or_ds_add_sql,
422        exp.TsOrDsDiff: _ts_or_ds_diff_sql,
423        exp.TsOrDsToTime: rename_func("TIME"),
424        exp.TsOrDsToDatetime: rename_func("DATETIME"),
425        exp.TsOrDsToTimestamp: rename_func("TIMESTAMP"),
426        exp.Unhex: rename_func("FROM_HEX"),
427        exp.UnixDate: rename_func("UNIX_DATE"),
428        exp.UnixToTime: _unix_to_time_sql,
429        exp.Uuid: lambda *_: "GENERATE_UUID()",
430        exp.Values: _derived_table_values_to_unnest,
431        exp.VariancePop: rename_func("VAR_POP"),
432        exp.SafeDivide: rename_func("SAFE_DIVIDE"),
433    }
434
435    SUPPORTED_JSON_PATH_PARTS = {
436        exp.JSONPathKey,
437        exp.JSONPathRoot,
438        exp.JSONPathSubscript,
439    }
440
441    TYPE_MAPPING = {
442        **generator.Generator.TYPE_MAPPING,
443        exp.DType.BIGDECIMAL: "BIGNUMERIC",
444        exp.DType.BIGINT: "INT64",
445        exp.DType.BINARY: "BYTES",
446        exp.DType.BLOB: "BYTES",
447        exp.DType.BOOLEAN: "BOOL",
448        exp.DType.CHAR: "STRING",
449        exp.DType.DECIMAL: "NUMERIC",
450        exp.DType.DOUBLE: "FLOAT64",
451        exp.DType.FLOAT: "FLOAT64",
452        exp.DType.INT: "INT64",
453        exp.DType.NCHAR: "STRING",
454        exp.DType.NVARCHAR: "STRING",
455        exp.DType.SMALLINT: "INT64",
456        exp.DType.TEXT: "STRING",
457        exp.DType.TIMESTAMP: "DATETIME",
458        exp.DType.TIMESTAMPNTZ: "DATETIME",
459        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
460        exp.DType.TIMESTAMPLTZ: "TIMESTAMP",
461        exp.DType.TINYINT: "INT64",
462        exp.DType.ROWVERSION: "BYTES",
463        exp.DType.UUID: "STRING",
464        exp.DType.VARBINARY: "BYTES",
465        exp.DType.VARCHAR: "STRING",
466        exp.DType.VARIANT: "ANY TYPE",
467    }
468
469    PROPERTIES_LOCATION = {
470        **generator.Generator.PROPERTIES_LOCATION,
471        exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
472        exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
473    }
474
475    # WINDOW comes after QUALIFY
476    # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#window_clause
477    # BigQuery requires QUALIFY before WINDOW
478    AFTER_HAVING_MODIFIER_TRANSFORMS = {
479        "qualify": generator.AFTER_HAVING_MODIFIER_TRANSFORMS["qualify"],
480        "windows": generator.AFTER_HAVING_MODIFIER_TRANSFORMS["windows"],
481    }
482
483    # from: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#reserved_keywords
484    RESERVED_KEYWORDS = {
485        "all",
486        "and",
487        "any",
488        "array",
489        "as",
490        "asc",
491        "assert_rows_modified",
492        "at",
493        "between",
494        "by",
495        "case",
496        "cast",
497        "collate",
498        "contains",
499        "create",
500        "cross",
501        "cube",
502        "current",
503        "default",
504        "define",
505        "desc",
506        "distinct",
507        "else",
508        "end",
509        "enum",
510        "escape",
511        "except",
512        "exclude",
513        "exists",
514        "extract",
515        "false",
516        "fetch",
517        "following",
518        "for",
519        "from",
520        "full",
521        "group",
522        "grouping",
523        "groups",
524        "hash",
525        "having",
526        "if",
527        "ignore",
528        "in",
529        "inner",
530        "intersect",
531        "interval",
532        "into",
533        "is",
534        "join",
535        "lateral",
536        "left",
537        "like",
538        "limit",
539        "lookup",
540        "merge",
541        "natural",
542        "new",
543        "no",
544        "not",
545        "null",
546        "nulls",
547        "of",
548        "on",
549        "or",
550        "order",
551        "outer",
552        "over",
553        "partition",
554        "preceding",
555        "proto",
556        "qualify",
557        "range",
558        "recursive",
559        "respect",
560        "right",
561        "rollup",
562        "rows",
563        "select",
564        "set",
565        "some",
566        "struct",
567        "tablesample",
568        "then",
569        "to",
570        "treat",
571        "true",
572        "unbounded",
573        "union",
574        "unnest",
575        "using",
576        "when",
577        "where",
578        "window",
579        "with",
580        "within",
581    }
582
583    def weekstart_sql(self, expression: exp.WeekStart) -> str:
584        if expression.this.name.upper() == "SUNDAY":
585            # BigQuery specific optimization since WEEK(SUNDAY) == WEEK
586            return "WEEK"
587
588        return self.func("WEEK", expression.this)
589
590    def datetrunc_sql(self, expression: exp.DateTrunc) -> str:
591        unit = expression.unit
592        unit_sql = unit.name if unit.is_string else self.sql(unit)
593        return self.func("DATE_TRUNC", expression.this, unit_sql, expression.args.get("zone"))
594
595    def mod_sql(self, expression: exp.Mod) -> str:
596        this = expression.this
597        expr = expression.expression
598        return self.func(
599            "MOD",
600            this.unnest() if isinstance(this, exp.Paren) else this,
601            expr.unnest() if isinstance(expr, exp.Paren) else expr,
602        )
603
604    def column_parts(self, expression: exp.Column) -> str:
605        if expression.meta_get("quoted_column"):
606            # If a column reference is of the form `dataset.table`.name, we need
607            # to preserve the quoted table path, otherwise the reference breaks
608            table_parts = ".".join(p.name for p in expression.parts[:-1])
609            table_path = self.sql(exp.Identifier(this=table_parts, quoted=True))
610            return f"{table_path}.{self.sql(expression, 'this')}"
611
612        return super().column_parts(expression)
613
614    def table_parts(self, expression: exp.Table) -> str:
615        # Depending on the context, `x.y` may not resolve to the same data source as `x`.`y`, so
616        # we need to make sure the correct quoting is used in each case.
617        #
618        # For example, if there is a CTE x that clashes with a schema name, then the former will
619        # return the table y in that schema, whereas the latter will return the CTE's y column:
620        #
621        # - WITH x AS (SELECT [1, 2] AS y) SELECT * FROM x, `x.y`   -> cross join
622        # - WITH x AS (SELECT [1, 2] AS y) SELECT * FROM x, `x`.`y` -> implicit unnest
623        if expression.meta_get("quoted_table"):
624            table_parts = ".".join(p.name for p in expression.parts)
625            return self.sql(exp.Identifier(this=table_parts, quoted=True))
626
627        return super().table_parts(expression)
628
629    def timetostr_sql(self, expression: exp.TimeToStr) -> str:
630        this = expression.this
631        if isinstance(this, exp.TsOrDsToDatetime):
632            func_name = "FORMAT_DATETIME"
633        elif isinstance(this, exp.TsOrDsToTimestamp):
634            func_name = "FORMAT_TIMESTAMP"
635        elif isinstance(this, exp.TsOrDsToTime):
636            func_name = "FORMAT_TIME"
637        else:
638            func_name = "FORMAT_DATE"
639
640        time_expr = this if isinstance(this, self.TS_OR_DS_TYPES) else expression
641        return self.func(
642            func_name, self.format_time(expression), time_expr.this, expression.args.get("zone")
643        )
644
645    def eq_sql(self, expression: exp.EQ) -> str:
646        # Operands of = cannot be NULL in BigQuery
647        if isinstance(expression.left, exp.Null) or isinstance(expression.right, exp.Null):
648            if not isinstance(expression.parent, exp.Update):
649                return "NULL"
650
651        return self.binary(expression, "=")
652
653    def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
654        parent = expression.parent
655
656        # BigQuery allows CAST(.. AS {STRING|TIMESTAMP} [FORMAT <fmt> [AT TIME ZONE <tz>]]).
657        # Only the TIMESTAMP one should use the below conversion, when AT TIME ZONE is included.
658        if not isinstance(parent, exp.Cast) or not parent.to.is_type("text"):
659            return self.func(
660                "TIMESTAMP", self.func("DATETIME", expression.this, expression.args.get("zone"))
661            )
662
663        return super().attimezone_sql(expression)
664
665    def trycast_sql(self, expression: exp.TryCast) -> str:
666        return self.cast_sql(expression, safe_prefix="SAFE_")
667
668    def bracket_sql(self, expression: exp.Bracket) -> str:
669        this = expression.this
670        expressions = expression.expressions
671
672        if len(expressions) == 1 and this and this.is_type(exp.DType.STRUCT):
673            arg = expressions[0]
674            if arg.type is None:
675                from sqlglot.optimizer.annotate_types import annotate_types
676
677                arg = annotate_types(arg, dialect=self.dialect)
678
679            if arg.type and arg.type.this in exp.DataType.TEXT_TYPES:
680                # BQ doesn't support bracket syntax with string values for structs
681                return f"{self.sql(this)}.{arg.name}"
682
683        expressions_sql = self.expressions(expression, flat=True)
684        offset = expression.args.get("offset")
685
686        if offset == 0:
687            expressions_sql = f"OFFSET({expressions_sql})"
688        elif offset == 1:
689            expressions_sql = f"ORDINAL({expressions_sql})"
690        elif offset is not None:
691            self.unsupported(f"Unsupported array offset: {offset}")
692
693        if expression.args.get("safe"):
694            expressions_sql = f"SAFE_{expressions_sql}"
695
696        return f"{self.sql(this)}[{expressions_sql}]"
697
698    def in_unnest_op(self, expression: exp.Unnest) -> str:
699        return self.sql(expression)
700
701    def version_sql(self, expression: exp.Version) -> str:
702        if expression.name == "TIMESTAMP":
703            expression.set("this", "SYSTEM_TIME")
704        return super().version_sql(expression)
705
706    def contains_sql(self, expression: exp.Contains) -> str:
707        this = expression.this
708        expr = expression.expression
709
710        if isinstance(this, exp.Lower) and isinstance(expr, exp.Lower):
711            this = this.this
712            expr = expr.this
713
714        return self.func("CONTAINS_SUBSTR", this, expr, expression.args.get("json_scope"))
715
716    def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
717        this = expression.this
718
719        # This ensures that inline type-annotated ARRAY literals like ARRAY<INT64>[1, 2, 3]
720        # are roundtripped unaffected. The inner check excludes ARRAY(SELECT ...) expressions,
721        # because they aren't literals and so the above syntax is invalid BigQuery.
722        if isinstance(this, exp.Array):
723            elem = seq_get(this.expressions, 0)
724            if not (elem and elem.find(exp.Query)):
725                return f"{self.sql(expression, 'to')}{self.sql(this)}"
726
727        return super().cast_sql(expression, safe_prefix=safe_prefix)
728
729    def clusterproperty_sql(self, expression: exp.ClusterProperty) -> str:
730        if expression.this:
731            self.unsupported(f"Unsupported CLUSTER BY {self.sql(expression, 'this')}")
732            return ""
733        return self.op_expressions("CLUSTER BY", expression)