diff options
Diffstat (limited to 'sqlglot/dialects/bigquery.py')
| -rw-r--r-- | sqlglot/dialects/bigquery.py | 65 |
1 files changed, 47 insertions, 18 deletions
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py index 5b10852..2166e65 100644 --- a/sqlglot/dialects/bigquery.py +++ b/sqlglot/dialects/bigquery.py @@ -7,6 +7,7 @@ from sqlglot import exp, generator, parser, tokens, transforms from sqlglot.dialects.dialect import ( Dialect, datestrtodate_sql, + format_time_lambda, inline_array_sql, max_or_greatest, min_or_least, @@ -103,16 +104,26 @@ def _unqualify_unnest(expression: exp.Expression) -> exp.Expression: class BigQuery(Dialect): - unnest_column_only = True - time_mapping = { - "%M": "%-M", - "%d": "%-d", - "%m": "%-m", - "%y": "%-y", - "%H": "%-H", - "%I": "%-I", - "%S": "%-S", - "%j": "%-j", + UNNEST_COLUMN_ONLY = True + + TIME_MAPPING = { + "%D": "%m/%d/%y", + } + + FORMAT_MAPPING = { + "DD": "%d", + "MM": "%m", + "MON": "%b", + "MONTH": "%B", + "YYYY": "%Y", + "YY": "%y", + "HH": "%I", + "HH12": "%I", + "HH24": "%H", + "MI": "%M", + "SS": "%S", + "SSSSS": "%f", + "TZH": "%z", } class Tokenizer(tokens.Tokenizer): @@ -142,6 +153,7 @@ class BigQuery(Dialect): "FLOAT64": TokenType.DOUBLE, "INT64": TokenType.BIGINT, "RECORD": TokenType.STRUCT, + "TIMESTAMP": TokenType.TIMESTAMPTZ, "NOT DETERMINISTIC": TokenType.VOLATILE, "UNKNOWN": TokenType.NULL, } @@ -155,13 +167,21 @@ class BigQuery(Dialect): FUNCTIONS = { **parser.Parser.FUNCTIONS, + "DATE_ADD": parse_date_delta_with_interval(exp.DateAdd), + "DATE_SUB": parse_date_delta_with_interval(exp.DateSub), "DATE_TRUNC": lambda args: exp.DateTrunc( unit=exp.Literal.string(str(seq_get(args, 1))), this=seq_get(args, 0), ), - "DATE_ADD": parse_date_delta_with_interval(exp.DateAdd), "DATETIME_ADD": parse_date_delta_with_interval(exp.DatetimeAdd), + "DATETIME_SUB": parse_date_delta_with_interval(exp.DatetimeSub), "DIV": lambda args: exp.IntDiv(this=seq_get(args, 0), expression=seq_get(args, 1)), + "PARSE_DATE": lambda args: format_time_lambda(exp.StrToDate, "bigquery")( + [seq_get(args, 1), seq_get(args, 0)] + ), + "PARSE_TIMESTAMP": lambda args: format_time_lambda(exp.StrToTime, "bigquery")( + [seq_get(args, 1), seq_get(args, 0)] + ), "REGEXP_CONTAINS": exp.RegexpLike.from_arg_list, "REGEXP_EXTRACT": lambda args: exp.RegexpExtract( this=seq_get(args, 0), @@ -172,15 +192,15 @@ class BigQuery(Dialect): if re.compile(str(seq_get(args, 1))).groups == 1 else None, ), + "SPLIT": lambda args: exp.Split( + # https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split + this=seq_get(args, 0), + expression=seq_get(args, 1) or exp.Literal.string(","), + ), "TIME_ADD": parse_date_delta_with_interval(exp.TimeAdd), - "TIMESTAMP_ADD": parse_date_delta_with_interval(exp.TimestampAdd), - "DATE_SUB": parse_date_delta_with_interval(exp.DateSub), - "DATETIME_SUB": parse_date_delta_with_interval(exp.DatetimeSub), "TIME_SUB": parse_date_delta_with_interval(exp.TimeSub), + "TIMESTAMP_ADD": parse_date_delta_with_interval(exp.TimestampAdd), "TIMESTAMP_SUB": parse_date_delta_with_interval(exp.TimestampSub), - "PARSE_TIMESTAMP": lambda args: exp.StrToTime( - this=seq_get(args, 1), format=seq_get(args, 0) - ), } FUNCTION_PARSERS = { @@ -274,9 +294,18 @@ class BigQuery(Dialect): exp.IntDiv: rename_func("DIV"), exp.Max: max_or_greatest, exp.Min: min_or_least, + exp.RegexpExtract: lambda self, e: self.func( + "REGEXP_EXTRACT", + e.this, + e.expression, + e.args.get("position"), + e.args.get("occurrence"), + ), + exp.RegexpLike: rename_func("REGEXP_CONTAINS"), exp.Select: transforms.preprocess( [_unqualify_unnest, transforms.eliminate_distinct_on] ), + exp.StrToDate: lambda self, e: f"PARSE_DATE({self.format_time(e)}, {self.sql(e, 'this')})", 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"), @@ -295,7 +324,6 @@ class BigQuery(Dialect): exp.StabilityProperty: lambda self, e: f"DETERMINISTIC" if e.name == "IMMUTABLE" else "NOT DETERMINISTIC", - exp.RegexpLike: rename_func("REGEXP_CONTAINS"), } TYPE_MAPPING = { @@ -315,6 +343,7 @@ class BigQuery(Dialect): exp.DataType.Type.TEXT: "STRING", exp.DataType.Type.TIMESTAMP: "DATETIME", exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", + exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP", exp.DataType.Type.TINYINT: "INT64", exp.DataType.Type.VARBINARY: "BYTES", exp.DataType.Type.VARCHAR: "STRING", |