diff options
Diffstat (limited to 'sqlglot/dialects/bigquery.py')
| -rw-r--r-- | sqlglot/dialects/bigquery.py | 51 |
1 files changed, 37 insertions, 14 deletions
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py index 1a88654..9705b35 100644 --- a/sqlglot/dialects/bigquery.py +++ b/sqlglot/dialects/bigquery.py @@ -39,18 +39,26 @@ def _date_add_sql( def _derived_table_values_to_unnest(self: generator.Generator, expression: exp.Values) -> str: if not isinstance(expression.unnest().parent, exp.From): - expression = t.cast(exp.Values, transforms.remove_precision_parameterized_types(expression)) return self.values_sql(expression) - rows = [tuple_exp.expressions for tuple_exp in expression.find_all(exp.Tuple)] - structs = [] - for row in rows: - aliases = [ - exp.alias_(value, column_name) - for value, column_name in zip(row, expression.args["alias"].args["columns"]) - ] - structs.append(exp.Struct(expressions=aliases)) - unnest_exp = exp.Unnest(expressions=[exp.Array(expressions=structs)]) - return self.unnest_sql(unnest_exp) + + alias = expression.args.get("alias") + + structs = [ + exp.Struct( + expressions=[ + exp.alias_(value, column_name) + for value, column_name in zip( + t.expressions, + alias.columns + if alias and alias.columns + else (f"_c{i}" for i in range(len(t.expressions))), + ) + ] + ) + for t in expression.find_all(exp.Tuple) + ] + + return self.unnest_sql(exp.Unnest(expressions=[exp.Array(expressions=structs)])) def _returnsproperty_sql(self: generator.Generator, expression: exp.ReturnsProperty) -> str: @@ -128,6 +136,7 @@ class BigQuery(Dialect): IDENTIFIERS = ["`"] STRING_ESCAPES = ["\\"] HEX_STRINGS = [("0x", ""), ("0X", "")] + BYTE_STRINGS = [("b'", "'"), ("B'", "'")] KEYWORDS = { **tokens.Tokenizer.KEYWORDS, @@ -139,6 +148,7 @@ class BigQuery(Dialect): "GEOGRAPHY": TokenType.GEOGRAPHY, "FLOAT64": TokenType.DOUBLE, "INT64": TokenType.BIGINT, + "BYTES": TokenType.BINARY, "NOT DETERMINISTIC": TokenType.VOLATILE, "UNKNOWN": TokenType.NULL, } @@ -153,7 +163,7 @@ class BigQuery(Dialect): FUNCTIONS = { **parser.Parser.FUNCTIONS, # type: ignore "DATE_TRUNC": lambda args: exp.DateTrunc( - unit=exp.Literal.string(seq_get(args, 1).name), # type: ignore + unit=exp.Literal.string(str(seq_get(args, 1))), this=seq_get(args, 0), ), "DATE_ADD": parse_date_delta_with_interval(exp.DateAdd), @@ -206,6 +216,12 @@ class BigQuery(Dialect): "NOT DETERMINISTIC": lambda self: self.expression( exp.StabilityProperty, this=exp.Literal.string("VOLATILE") ), + "OPTIONS": lambda self: self._parse_with_property(), + } + + CONSTRAINT_PARSERS = { + **parser.Parser.CONSTRAINT_PARSERS, # type: ignore + "OPTIONS": lambda self: exp.Properties(expressions=self._parse_with_property()), } class Generator(generator.Generator): @@ -217,11 +233,11 @@ class BigQuery(Dialect): TRANSFORMS = { **generator.Generator.TRANSFORMS, # type: ignore - **transforms.REMOVE_PRECISION_PARAMETERIZED_TYPES, # type: ignore exp.ArraySize: rename_func("ARRAY_LENGTH"), exp.AtTimeZone: lambda self, e: self.func( "TIMESTAMP", self.func("DATETIME", e.this, e.args.get("zone")) ), + exp.Cast: transforms.preprocess([transforms.remove_precision_parameterized_types]), exp.DateAdd: _date_add_sql("DATE", "ADD"), exp.DateSub: _date_add_sql("DATE", "SUB"), exp.DatetimeAdd: _date_add_sql("DATETIME", "ADD"), @@ -234,7 +250,9 @@ class BigQuery(Dialect): exp.IntDiv: rename_func("DIV"), exp.Max: max_or_greatest, exp.Min: min_or_least, - exp.Select: transforms.preprocess([_unqualify_unnest]), + exp.Select: transforms.preprocess( + [_unqualify_unnest, transforms.eliminate_distinct_on] + ), exp.StrToTime: lambda self, e: f"PARSE_TIMESTAMP({self.format_time(e)}, {self.sql(e, 'this')})", exp.TimeAdd: _date_add_sql("TIME", "ADD"), exp.TimeSub: _date_add_sql("TIME", "SUB"), @@ -259,6 +277,7 @@ class BigQuery(Dialect): **generator.Generator.TYPE_MAPPING, # type: ignore exp.DataType.Type.BIGDECIMAL: "BIGNUMERIC", exp.DataType.Type.BIGINT: "INT64", + exp.DataType.Type.BINARY: "BYTES", exp.DataType.Type.BOOLEAN: "BOOL", exp.DataType.Type.CHAR: "STRING", exp.DataType.Type.DECIMAL: "NUMERIC", @@ -272,6 +291,7 @@ class BigQuery(Dialect): exp.DataType.Type.TIMESTAMP: "DATETIME", exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", exp.DataType.Type.TINYINT: "INT64", + exp.DataType.Type.VARBINARY: "BYTES", exp.DataType.Type.VARCHAR: "STRING", exp.DataType.Type.VARIANT: "ANY TYPE", } @@ -310,3 +330,6 @@ class BigQuery(Dialect): if not expression.args.get("distinct", False): self.unsupported("INTERSECT without DISTINCT is not supported in BigQuery") return f"INTERSECT{' DISTINCT' if expression.args.get('distinct') else ' ALL'}" + + def with_properties(self, properties: exp.Properties) -> str: + return self.properties(properties, prefix=self.seg("OPTIONS")) |