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 _array_contains_sql(self: BigQueryGenerator, expression: exp.ArrayContains) -> str:
141    return self.sql(
142        exp.Exists(
143            this=exp.select("1")
144            .from_(exp.Unnest(expressions=[expression.left]).as_("_unnest", table=["_col"]))
145            .where(exp.column("_col").eq(expression.right))
146        )
147    )
148
149
150def _ts_or_ds_add_sql(self: BigQueryGenerator, expression: exp.TsOrDsAdd) -> str:
151    return date_add_interval_sql("DATE", "ADD")(self, ts_or_ds_add_cast(expression))
152
153
154def _ts_or_ds_diff_sql(self: BigQueryGenerator, expression: exp.TsOrDsDiff) -> str:
155    expression.this.replace(exp.cast(expression.this, exp.DType.TIMESTAMP))
156    expression.expression.replace(exp.cast(expression.expression, exp.DType.TIMESTAMP))
157    unit = unit_to_var(expression)
158    return self.func("DATE_DIFF", expression.this, expression.expression, unit)
159
160
161def _unix_to_time_sql(self: BigQueryGenerator, expression: exp.UnixToTime) -> str:
162    scale = expression.args.get("scale")
163    timestamp = expression.this
164
165    if scale in (None, exp.UnixToTime.SECONDS):
166        return self.func("TIMESTAMP_SECONDS", timestamp)
167    if scale == exp.UnixToTime.MILLIS:
168        return self.func("TIMESTAMP_MILLIS", timestamp)
169    if scale == exp.UnixToTime.MICROS:
170        return self.func("TIMESTAMP_MICROS", timestamp)
171
172    unix_seconds = exp.cast(
173        exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)), exp.DType.BIGINT
174    )
175    return self.func("TIMESTAMP_SECONDS", unix_seconds)
176
177
178def _str_to_datetime_sql(self: BigQueryGenerator, expression: exp.StrToDate | exp.StrToTime) -> str:
179    this = self.sql(expression, "this")
180    dtype = "DATE" if isinstance(expression, exp.StrToDate) else "TIMESTAMP"
181
182    if expression.args.get("safe"):
183        fmt = self.format_time(
184            expression,
185            self.dialect.INVERSE_FORMAT_MAPPING,
186            self.dialect.INVERSE_FORMAT_TRIE,
187        )
188        return f"SAFE_CAST({this} AS {dtype} FORMAT {fmt})"
189
190    fmt = self.format_time(expression)
191    return self.func(f"PARSE_{dtype}", fmt, this, expression.args.get("zone"))
192
193
194@unsupported_args("ins_cost", "del_cost", "sub_cost")
195def _levenshtein_sql(self: BigQueryGenerator, expression: exp.Levenshtein) -> str:
196    max_dist = expression.args.get("max_dist")
197    if max_dist:
198        max_dist = exp.Kwarg(this=exp.var("max_distance"), expression=max_dist)
199
200    return self.func("EDIT_DISTANCE", expression.this, expression.expression, max_dist)
201
202
203def _json_extract_sql(self: BigQueryGenerator, expression: JSON_EXTRACT_TYPE) -> str:
204    name = (expression._meta and expression.meta.get("name")) or expression.sql_name()
205    upper = name.upper()
206
207    dquote_escaping = upper in DQUOTES_ESCAPING_JSON_FUNCTIONS
208
209    if dquote_escaping:
210        self._quote_json_path_key_using_brackets = False
211
212    sql = rename_func(upper)(self, expression)
213
214    if dquote_escaping:
215        self._quote_json_path_key_using_brackets = True
216
217    return sql
218
219
220class BigQueryGenerator(generator.Generator):
221    TRY_SUPPORTED = False
222    SUPPORTS_UESCAPE = False
223    SUPPORTS_DECODE_CASE = False
224    INTERVAL_ALLOWS_PLURAL_FORM = False
225    JOIN_HINTS = False
226    QUERY_HINTS = False
227    TABLE_HINTS = False
228    LIMIT_FETCH = "LIMIT"
229    RENAME_TABLE_WITH_DB = False
230    NVL2_SUPPORTED = False
231    UNNEST_WITH_ORDINALITY = False
232    COLLATE_IS_FUNC = True
233    LIMIT_ONLY_LITERALS = True
234    SUPPORTS_TABLE_ALIAS_COLUMNS = False
235    UNPIVOT_ALIASES_ARE_IDENTIFIERS = False
236    JSON_KEY_VALUE_PAIR_SEP = ","
237    NULL_ORDERING_SUPPORTED: bool | None = False
238    IGNORE_NULLS_IN_FUNC = True
239    JSON_PATH_SINGLE_QUOTE_ESCAPE = True
240    CAN_IMPLEMENT_ARRAY_ANY = True
241    SUPPORTS_TO_NUMBER = False
242    NAMED_PLACEHOLDER_TOKEN = "@"
243    HEX_FUNC = "TO_HEX"
244    WITH_PROPERTIES_PREFIX = "OPTIONS"
245    SUPPORTS_EXPLODING_PROJECTIONS = False
246    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
247    SUPPORTS_UNIX_SECONDS = True
248    DECLARE_DEFAULT_ASSIGNMENT = "DEFAULT"
249
250    SAFE_JSON_PATH_KEY_RE = re.compile(r"^[\-\w]*$")
251
252    WINDOW_FUNCS_WITH_NULL_ORDERING = (
253        exp.CumeDist,
254        exp.DenseRank,
255        exp.FirstValue,
256        exp.Lag,
257        exp.LastValue,
258        exp.Lead,
259        exp.NthValue,
260        exp.Ntile,
261        exp.PercentRank,
262        exp.Rank,
263        exp.RowNumber,
264    )
265
266    TS_OR_DS_TYPES = (
267        exp.TsOrDsToDatetime,
268        exp.TsOrDsToTimestamp,
269        exp.TsOrDsToTime,
270        exp.TsOrDsToDate,
271    )
272
273    TRANSFORMS = {
274        **generator.Generator.TRANSFORMS,
275        exp.AIEmbed: rename_func("EMBED"),
276        exp.AIGenerate: rename_func("GENERATE"),
277        exp.AISimilarity: rename_func("SIMILARITY"),
278        exp.ApproxTopK: rename_func("APPROX_TOP_COUNT"),
279        exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"),
280        exp.ArgMax: arg_max_or_min_no_count("MAX_BY"),
281        exp.ArgMin: arg_max_or_min_no_count("MIN_BY"),
282        exp.Array: inline_array_unless_query,
283        exp.ArrayContains: _array_contains_sql,
284        exp.ArrayFilter: filter_array_using_unnest,
285        exp.ArrayRemove: filter_array_using_unnest,
286        exp.BitwiseAndAgg: rename_func("BIT_AND"),
287        exp.BitwiseOrAgg: rename_func("BIT_OR"),
288        exp.BitwiseXorAgg: rename_func("BIT_XOR"),
289        exp.BitwiseCount: rename_func("BIT_COUNT"),
290        exp.ByteLength: rename_func("BYTE_LENGTH"),
291        exp.Cast: transforms.preprocess([transforms.remove_precision_parameterized_types]),
292        exp.CollateProperty: lambda self, e: (
293            f"DEFAULT COLLATE {self.sql(e, 'this')}"
294            if e.args.get("default")
295            else f"COLLATE {self.sql(e, 'this')}"
296        ),
297        exp.Commit: lambda *_: "COMMIT TRANSACTION",
298        exp.CountIf: rename_func("COUNTIF"),
299        exp.Create: _create_sql,
300        exp.CTE: transforms.preprocess([_pushdown_cte_column_names]),
301        exp.DateAdd: date_add_interval_sql("DATE", "ADD"),
302        exp.DateDiff: lambda self, e: self.func("DATE_DIFF", e.this, e.expression, unit_to_var(e)),
303        exp.DateFromParts: rename_func("DATE"),
304        exp.DateStrToDate: datestrtodate_sql,
305        exp.DateSub: date_add_interval_sql("DATE", "SUB"),
306        exp.DatetimeAdd: date_add_interval_sql("DATETIME", "ADD"),
307        exp.DatetimeSub: date_add_interval_sql("DATETIME", "SUB"),
308        exp.DateFromUnixDate: rename_func("DATE_FROM_UNIX_DATE"),
309        exp.FromTimeZone: lambda self, e: self.func(
310            "DATETIME", self.func("TIMESTAMP", e.this, e.args.get("zone")), "'UTC'"
311        ),
312        exp.GenerateSeries: generate_series_sql("GENERATE_ARRAY"),
313        exp.GroupConcat: lambda self, e: groupconcat_sql(
314            self, e, func_name="STRING_AGG", within_group=False, sep=None
315        ),
316        exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))),
317        exp.HexString: lambda self, e: self.hexstring_sql(e, binary_function_repr="FROM_HEX"),
318        exp.If: if_sql(false_value="NULL"),
319        exp.ILike: no_ilike_sql,
320        exp.IntDiv: rename_func("DIV"),
321        exp.Int64: rename_func("INT64"),
322        exp.JSONBool: rename_func("BOOL"),
323        exp.JSONExtract: _json_extract_sql,
324        exp.JSONExtractArray: _json_extract_sql,
325        exp.JSONExtractScalar: _json_extract_sql,
326        exp.JSONFormat: lambda self, e: self.func(
327            "TO_JSON" if e.args.get("to_json") else "TO_JSON_STRING",
328            e.this,
329            e.args.get("options"),
330        ),
331        exp.JSONKeysAtDepth: rename_func("JSON_KEYS"),
332        exp.JSONValueArray: rename_func("JSON_VALUE_ARRAY"),
333        exp.Levenshtein: _levenshtein_sql,
334        exp.Max: max_or_greatest,
335        exp.MD5: lambda self, e: self.func("TO_HEX", self.func("MD5", e.this)),
336        exp.MD5Digest: rename_func("MD5"),
337        exp.Min: min_or_least,
338        exp.Normalize: lambda self, e: self.func(
339            "NORMALIZE_AND_CASEFOLD" if e.args.get("is_casefold") else "NORMALIZE",
340            e.this,
341            e.args.get("form"),
342        ),
343        exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
344        exp.RegexpExtract: lambda self, e: self.func(
345            "REGEXP_EXTRACT",
346            e.this,
347            e.expression,
348            e.args.get("position"),
349            e.args.get("occurrence"),
350        ),
351        exp.RegexpExtractAll: lambda self, e: self.func("REGEXP_EXTRACT_ALL", e.this, e.expression),
352        exp.RegexpReplace: regexp_replace_sql,
353        exp.RegexpLike: rename_func("REGEXP_CONTAINS"),
354        exp.ReturnsProperty: _returnsproperty_sql,
355        exp.Rollback: lambda *_: "ROLLBACK TRANSACTION",
356        exp.ParseTime: lambda self, e: self.func("PARSE_TIME", self.format_time(e), e.this),
357        exp.ParseDatetime: lambda self, e: self.func("PARSE_DATETIME", self.format_time(e), e.this),
358        exp.Select: transforms.preprocess(
359            [
360                transforms.explode_projection_to_unnest(),
361                transforms.unqualify_unnest,
362                transforms.eliminate_distinct_on,
363                _alias_ordered_group,
364                transforms.eliminate_semi_and_anti_joins,
365            ]
366        ),
367        exp.SHA: rename_func("SHA1"),
368        exp.SHA2: sha256_sql,
369        exp.SHA1Digest: rename_func("SHA1"),
370        exp.SHA2Digest: sha2_digest_sql,
371        exp.StabilityProperty: lambda self, e: (
372            "DETERMINISTIC" if e.name == "IMMUTABLE" else "NOT DETERMINISTIC"
373        ),
374        exp.String: rename_func("STRING"),
375        exp.StrPosition: lambda self, e: strposition_sql(
376            self, e, func_name="INSTR", supports_position=True, supports_occurrence=True
377        ),
378        exp.StrToDate: _str_to_datetime_sql,
379        exp.StrToTime: _str_to_datetime_sql,
380        exp.SessionUser: lambda *_: "SESSION_USER()",
381        exp.TimeAdd: date_add_interval_sql("TIME", "ADD"),
382        exp.TimeFromParts: rename_func("TIME"),
383        exp.TimestampFromParts: rename_func("DATETIME"),
384        exp.TimeSub: date_add_interval_sql("TIME", "SUB"),
385        exp.TimestampAdd: date_add_interval_sql("TIMESTAMP", "ADD"),
386        exp.TimestampDiff: rename_func("TIMESTAMP_DIFF"),
387        exp.TimestampSub: date_add_interval_sql("TIMESTAMP", "SUB"),
388        exp.TimeStrToTime: timestrtotime_sql,
389        exp.Transaction: lambda *_: "BEGIN TRANSACTION",
390        exp.TsOrDsAdd: _ts_or_ds_add_sql,
391        exp.TsOrDsDiff: _ts_or_ds_diff_sql,
392        exp.TsOrDsToTime: rename_func("TIME"),
393        exp.TsOrDsToDatetime: rename_func("DATETIME"),
394        exp.TsOrDsToTimestamp: rename_func("TIMESTAMP"),
395        exp.Unhex: rename_func("FROM_HEX"),
396        exp.UnixDate: rename_func("UNIX_DATE"),
397        exp.UnixToTime: _unix_to_time_sql,
398        exp.Uuid: lambda *_: "GENERATE_UUID()",
399        exp.Values: _derived_table_values_to_unnest,
400        exp.VariancePop: rename_func("VAR_POP"),
401        exp.SafeDivide: rename_func("SAFE_DIVIDE"),
402    }
403
404    SUPPORTED_JSON_PATH_PARTS = {
405        exp.JSONPathKey,
406        exp.JSONPathRoot,
407        exp.JSONPathSubscript,
408    }
409
410    TYPE_MAPPING = {
411        **generator.Generator.TYPE_MAPPING,
412        exp.DType.BIGDECIMAL: "BIGNUMERIC",
413        exp.DType.BIGINT: "INT64",
414        exp.DType.BINARY: "BYTES",
415        exp.DType.BLOB: "BYTES",
416        exp.DType.BOOLEAN: "BOOL",
417        exp.DType.CHAR: "STRING",
418        exp.DType.DECIMAL: "NUMERIC",
419        exp.DType.DOUBLE: "FLOAT64",
420        exp.DType.FLOAT: "FLOAT64",
421        exp.DType.INT: "INT64",
422        exp.DType.NCHAR: "STRING",
423        exp.DType.NVARCHAR: "STRING",
424        exp.DType.SMALLINT: "INT64",
425        exp.DType.TEXT: "STRING",
426        exp.DType.TIMESTAMP: "DATETIME",
427        exp.DType.TIMESTAMPNTZ: "DATETIME",
428        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
429        exp.DType.TIMESTAMPLTZ: "TIMESTAMP",
430        exp.DType.TINYINT: "INT64",
431        exp.DType.ROWVERSION: "BYTES",
432        exp.DType.UUID: "STRING",
433        exp.DType.VARBINARY: "BYTES",
434        exp.DType.VARCHAR: "STRING",
435        exp.DType.VARIANT: "ANY TYPE",
436    }
437
438    PROPERTIES_LOCATION = {
439        **generator.Generator.PROPERTIES_LOCATION,
440        exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
441        exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
442    }
443
444    # WINDOW comes after QUALIFY
445    # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#window_clause
446    # BigQuery requires QUALIFY before WINDOW
447    AFTER_HAVING_MODIFIER_TRANSFORMS = {
448        "qualify": generator.AFTER_HAVING_MODIFIER_TRANSFORMS["qualify"],
449        "windows": generator.AFTER_HAVING_MODIFIER_TRANSFORMS["windows"],
450    }
451
452    # from: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#reserved_keywords
453    RESERVED_KEYWORDS = {
454        "all",
455        "and",
456        "any",
457        "array",
458        "as",
459        "asc",
460        "assert_rows_modified",
461        "at",
462        "between",
463        "by",
464        "case",
465        "cast",
466        "collate",
467        "contains",
468        "create",
469        "cross",
470        "cube",
471        "current",
472        "default",
473        "define",
474        "desc",
475        "distinct",
476        "else",
477        "end",
478        "enum",
479        "escape",
480        "except",
481        "exclude",
482        "exists",
483        "extract",
484        "false",
485        "fetch",
486        "following",
487        "for",
488        "from",
489        "full",
490        "group",
491        "grouping",
492        "groups",
493        "hash",
494        "having",
495        "if",
496        "ignore",
497        "in",
498        "inner",
499        "intersect",
500        "interval",
501        "into",
502        "is",
503        "join",
504        "lateral",
505        "left",
506        "like",
507        "limit",
508        "lookup",
509        "merge",
510        "natural",
511        "new",
512        "no",
513        "not",
514        "null",
515        "nulls",
516        "of",
517        "on",
518        "or",
519        "order",
520        "outer",
521        "over",
522        "partition",
523        "preceding",
524        "proto",
525        "qualify",
526        "range",
527        "recursive",
528        "respect",
529        "right",
530        "rollup",
531        "rows",
532        "select",
533        "set",
534        "some",
535        "struct",
536        "tablesample",
537        "then",
538        "to",
539        "treat",
540        "true",
541        "unbounded",
542        "union",
543        "unnest",
544        "using",
545        "when",
546        "where",
547        "window",
548        "with",
549        "within",
550    }
551
552    def datetrunc_sql(self, expression: exp.DateTrunc) -> str:
553        unit = expression.unit
554        unit_sql = unit.name if unit.is_string else self.sql(unit)
555        return self.func("DATE_TRUNC", expression.this, unit_sql, expression.args.get("zone"))
556
557    def mod_sql(self, expression: exp.Mod) -> str:
558        this = expression.this
559        expr = expression.expression
560        return self.func(
561            "MOD",
562            this.unnest() if isinstance(this, exp.Paren) else this,
563            expr.unnest() if isinstance(expr, exp.Paren) else expr,
564        )
565
566    def column_parts(self, expression: exp.Column) -> str:
567        if expression.meta.get("quoted_column"):
568            # If a column reference is of the form `dataset.table`.name, we need
569            # to preserve the quoted table path, otherwise the reference breaks
570            table_parts = ".".join(p.name for p in expression.parts[:-1])
571            table_path = self.sql(exp.Identifier(this=table_parts, quoted=True))
572            return f"{table_path}.{self.sql(expression, 'this')}"
573
574        return super().column_parts(expression)
575
576    def table_parts(self, expression: exp.Table) -> str:
577        # Depending on the context, `x.y` may not resolve to the same data source as `x`.`y`, so
578        # we need to make sure the correct quoting is used in each case.
579        #
580        # For example, if there is a CTE x that clashes with a schema name, then the former will
581        # return the table y in that schema, whereas the latter will return the CTE's y column:
582        #
583        # - WITH x AS (SELECT [1, 2] AS y) SELECT * FROM x, `x.y`   -> cross join
584        # - WITH x AS (SELECT [1, 2] AS y) SELECT * FROM x, `x`.`y` -> implicit unnest
585        if expression.meta.get("quoted_table"):
586            table_parts = ".".join(p.name for p in expression.parts)
587            return self.sql(exp.Identifier(this=table_parts, quoted=True))
588
589        return super().table_parts(expression)
590
591    def timetostr_sql(self, expression: exp.TimeToStr) -> str:
592        this = expression.this
593        if isinstance(this, exp.TsOrDsToDatetime):
594            func_name = "FORMAT_DATETIME"
595        elif isinstance(this, exp.TsOrDsToTimestamp):
596            func_name = "FORMAT_TIMESTAMP"
597        elif isinstance(this, exp.TsOrDsToTime):
598            func_name = "FORMAT_TIME"
599        else:
600            func_name = "FORMAT_DATE"
601
602        time_expr = this if isinstance(this, self.TS_OR_DS_TYPES) else expression
603        return self.func(
604            func_name, self.format_time(expression), time_expr.this, expression.args.get("zone")
605        )
606
607    def eq_sql(self, expression: exp.EQ) -> str:
608        # Operands of = cannot be NULL in BigQuery
609        if isinstance(expression.left, exp.Null) or isinstance(expression.right, exp.Null):
610            if not isinstance(expression.parent, exp.Update):
611                return "NULL"
612
613        return self.binary(expression, "=")
614
615    def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
616        parent = expression.parent
617
618        # BigQuery allows CAST(.. AS {STRING|TIMESTAMP} [FORMAT <fmt> [AT TIME ZONE <tz>]]).
619        # Only the TIMESTAMP one should use the below conversion, when AT TIME ZONE is included.
620        if not isinstance(parent, exp.Cast) or not parent.to.is_type("text"):
621            return self.func(
622                "TIMESTAMP", self.func("DATETIME", expression.this, expression.args.get("zone"))
623            )
624
625        return super().attimezone_sql(expression)
626
627    def trycast_sql(self, expression: exp.TryCast) -> str:
628        return self.cast_sql(expression, safe_prefix="SAFE_")
629
630    def bracket_sql(self, expression: exp.Bracket) -> str:
631        this = expression.this
632        expressions = expression.expressions
633
634        if len(expressions) == 1 and this and this.is_type(exp.DType.STRUCT):
635            arg = expressions[0]
636            if arg.type is None:
637                from sqlglot.optimizer.annotate_types import annotate_types
638
639                arg = annotate_types(arg, dialect=self.dialect)
640
641            if arg.type and arg.type.this in exp.DataType.TEXT_TYPES:
642                # BQ doesn't support bracket syntax with string values for structs
643                return f"{self.sql(this)}.{arg.name}"
644
645        expressions_sql = self.expressions(expression, flat=True)
646        offset = expression.args.get("offset")
647
648        if offset == 0:
649            expressions_sql = f"OFFSET({expressions_sql})"
650        elif offset == 1:
651            expressions_sql = f"ORDINAL({expressions_sql})"
652        elif offset is not None:
653            self.unsupported(f"Unsupported array offset: {offset}")
654
655        if expression.args.get("safe"):
656            expressions_sql = f"SAFE_{expressions_sql}"
657
658        return f"{self.sql(this)}[{expressions_sql}]"
659
660    def in_unnest_op(self, expression: exp.Unnest) -> str:
661        return self.sql(expression)
662
663    def version_sql(self, expression: exp.Version) -> str:
664        if expression.name == "TIMESTAMP":
665            expression.set("this", "SYSTEM_TIME")
666        return super().version_sql(expression)
667
668    def contains_sql(self, expression: exp.Contains) -> str:
669        this = expression.this
670        expr = expression.expression
671
672        if isinstance(this, exp.Lower) and isinstance(expr, exp.Lower):
673            this = this.this
674            expr = expr.this
675
676        return self.func("CONTAINS_SUBSTR", this, expr, expression.args.get("json_scope"))
677
678    def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
679        this = expression.this
680
681        # This ensures that inline type-annotated ARRAY literals like ARRAY<INT64>[1, 2, 3]
682        # are roundtripped unaffected. The inner check excludes ARRAY(SELECT ...) expressions,
683        # because they aren't literals and so the above syntax is invalid BigQuery.
684        if isinstance(this, exp.Array):
685            elem = seq_get(this.expressions, 0)
686            if not (elem and elem.find(exp.Query)):
687                return f"{self.sql(expression, 'to')}{self.sql(this)}"
688
689        return super().cast_sql(expression, safe_prefix=safe_prefix)