summaryrefslogtreecommitdiffstats
path: root/sqlglot/dialects/bigquery.py
diff options
context:
space:
mode:
Diffstat (limited to 'sqlglot/dialects/bigquery.py')
-rw-r--r--sqlglot/dialects/bigquery.py54
1 files changed, 29 insertions, 25 deletions
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 5bfc3ea..2167ba2 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -24,6 +24,7 @@ from sqlglot.dialects.dialect import (
rename_func,
timestrtotime_sql,
ts_or_ds_add_cast,
+ unit_to_var,
)
from sqlglot.helper import seq_get, split_num_words
from sqlglot.tokens import TokenType
@@ -41,14 +42,22 @@ def _derived_table_values_to_unnest(self: BigQuery.Generator, expression: exp.Va
structs = []
alias = expression.args.get("alias")
for tup in expression.find_all(exp.Tuple):
- field_aliases = alias.columns if alias else (f"_c{i}" for i in range(len(tup.expressions)))
+ field_aliases = (
+ alias.columns
+ if alias and alias.columns
+ else (f"_c{i}" for i in range(len(tup.expressions)))
+ )
expressions = [
exp.PropertyEQ(this=exp.to_identifier(name), expression=fld)
for name, fld in zip(field_aliases, tup.expressions)
]
structs.append(exp.Struct(expressions=expressions))
- return self.unnest_sql(exp.Unnest(expressions=[exp.array(*structs, copy=False)]))
+ # Due to `UNNEST_COLUMN_ONLY`, it is expected that the table alias be contained in the columns expression
+ alias_name_only = exp.TableAlias(columns=[alias.this]) if alias else None
+ return self.unnest_sql(
+ exp.Unnest(expressions=[exp.array(*structs, copy=False)], alias=alias_name_only)
+ )
def _returnsproperty_sql(self: BigQuery.Generator, expression: exp.ReturnsProperty) -> str:
@@ -190,7 +199,7 @@ def _ts_or_ds_add_sql(self: BigQuery.Generator, expression: exp.TsOrDsAdd) -> st
def _ts_or_ds_diff_sql(self: BigQuery.Generator, expression: exp.TsOrDsDiff) -> str:
expression.this.replace(exp.cast(expression.this, "TIMESTAMP", copy=True))
expression.expression.replace(exp.cast(expression.expression, "TIMESTAMP", copy=True))
- unit = expression.args.get("unit") or "DAY"
+ unit = unit_to_var(expression)
return self.func("DATE_DIFF", expression.this, expression.expression, unit)
@@ -238,16 +247,6 @@ class BigQuery(Dialect):
"%E6S": "%S.%f",
}
- ESCAPE_SEQUENCES = {
- "\\a": "\a",
- "\\b": "\b",
- "\\f": "\f",
- "\\n": "\n",
- "\\r": "\r",
- "\\t": "\t",
- "\\v": "\v",
- }
-
FORMAT_MAPPING = {
"DD": "%d",
"MM": "%m",
@@ -315,6 +314,7 @@ class BigQuery(Dialect):
"BEGIN TRANSACTION": TokenType.BEGIN,
"BYTES": TokenType.BINARY,
"CURRENT_DATETIME": TokenType.CURRENT_DATETIME,
+ "DATETIME": TokenType.TIMESTAMP,
"DECLARE": TokenType.COMMAND,
"ELSEIF": TokenType.COMMAND,
"EXCEPTION": TokenType.COMMAND,
@@ -486,14 +486,14 @@ class BigQuery(Dialect):
table.set("db", exp.Identifier(this=parts[0]))
table.set("this", exp.Identifier(this=parts[1]))
- if isinstance(table.this, exp.Identifier) and "." in table.name:
+ if any("." in p.name for p in table.parts):
catalog, db, this, *rest = (
- t.cast(t.Optional[exp.Expression], exp.to_identifier(x, quoted=True))
- for x in split_num_words(table.name, ".", 3)
+ exp.to_identifier(p, quoted=True)
+ for p in split_num_words(".".join(p.name for p in table.parts), ".", 3)
)
if rest and this:
- this = exp.Dot.build(t.cast(t.List[exp.Expression], [this, *rest]))
+ this = exp.Dot.build([this, *rest]) # type: ignore
table = exp.Table(this=this, db=db, catalog=catalog)
table.meta["quoted_table"] = True
@@ -527,7 +527,9 @@ class BigQuery(Dialect):
return json_object
- def _parse_bracket(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]:
+ def _parse_bracket(
+ self, this: t.Optional[exp.Expression] = None
+ ) -> t.Optional[exp.Expression]:
bracket = super()._parse_bracket(this)
if this is bracket:
@@ -566,6 +568,7 @@ class BigQuery(Dialect):
IGNORE_NULLS_IN_FUNC = True
JSON_PATH_SINGLE_QUOTE_ESCAPE = True
CAN_IMPLEMENT_ARRAY_ANY = True
+ SUPPORTS_TO_NUMBER = False
NAMED_PLACEHOLDER_TOKEN = "@"
TRANSFORMS = {
@@ -588,7 +591,7 @@ class BigQuery(Dialect):
exp.CTE: transforms.preprocess([_pushdown_cte_column_names]),
exp.DateAdd: date_add_interval_sql("DATE", "ADD"),
exp.DateDiff: lambda self, e: self.func(
- "DATE_DIFF", e.this, e.expression, e.unit or "DAY"
+ "DATE_DIFF", e.this, e.expression, unit_to_var(e)
),
exp.DateFromParts: rename_func("DATE"),
exp.DateStrToDate: datestrtodate_sql,
@@ -607,6 +610,7 @@ class BigQuery(Dialect):
exp.IntDiv: rename_func("DIV"),
exp.JSONFormat: rename_func("TO_JSON_STRING"),
exp.Max: max_or_greatest,
+ exp.Mod: rename_func("MOD"),
exp.MD5: lambda self, e: self.func("TO_HEX", self.func("MD5", e.this)),
exp.MD5Digest: rename_func("MD5"),
exp.Min: min_or_least,
@@ -847,10 +851,10 @@ class BigQuery(Dialect):
return inline_array_sql(self, expression)
def bracket_sql(self, expression: exp.Bracket) -> str:
- this = self.sql(expression, "this")
+ this = expression.this
expressions = expression.expressions
- if len(expressions) == 1:
+ if len(expressions) == 1 and this and this.is_type(exp.DataType.Type.STRUCT):
arg = expressions[0]
if arg.type is None:
from sqlglot.optimizer.annotate_types import annotate_types
@@ -858,10 +862,10 @@ class BigQuery(Dialect):
arg = annotate_types(arg)
if arg.type and arg.type.this in exp.DataType.TEXT_TYPES:
- # BQ doesn't support bracket syntax with string values
- return f"{this}.{arg.name}"
+ # BQ doesn't support bracket syntax with string values for structs
+ return f"{self.sql(this)}.{arg.name}"
- expressions_sql = ", ".join(self.sql(e) for e in expressions)
+ expressions_sql = self.expressions(expression, flat=True)
offset = expression.args.get("offset")
if offset == 0:
@@ -874,7 +878,7 @@ class BigQuery(Dialect):
if expression.args.get("safe"):
expressions_sql = f"SAFE_{expressions_sql}"
- return f"{this}[{expressions_sql}]"
+ return f"{self.sql(this)}[{expressions_sql}]"
def in_unnest_op(self, expression: exp.Unnest) -> str:
return self.sql(expression)