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