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.py65
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",