diff options
Diffstat (limited to 'sqlglot/dialects')
| -rw-r--r-- | sqlglot/dialects/__init__.py | 1 | ||||
| -rw-r--r-- | sqlglot/dialects/clickhouse.py | 13 | ||||
| -rw-r--r-- | sqlglot/dialects/dialect.py | 25 | ||||
| -rw-r--r-- | sqlglot/dialects/doris.py | 65 | ||||
| -rw-r--r-- | sqlglot/dialects/duckdb.py | 26 | ||||
| -rw-r--r-- | sqlglot/dialects/hive.py | 14 | ||||
| -rw-r--r-- | sqlglot/dialects/mysql.py | 8 | ||||
| -rw-r--r-- | sqlglot/dialects/postgres.py | 5 | ||||
| -rw-r--r-- | sqlglot/dialects/presto.py | 10 | ||||
| -rw-r--r-- | sqlglot/dialects/redshift.py | 7 | ||||
| -rw-r--r-- | sqlglot/dialects/spark.py | 3 | ||||
| -rw-r--r-- | sqlglot/dialects/starrocks.py | 5 |
12 files changed, 149 insertions, 33 deletions
diff --git a/sqlglot/dialects/__init__.py b/sqlglot/dialects/__init__.py index fc34262..8212669 100644 --- a/sqlglot/dialects/__init__.py +++ b/sqlglot/dialects/__init__.py @@ -60,6 +60,7 @@ from sqlglot.dialects.bigquery import BigQuery from sqlglot.dialects.clickhouse import ClickHouse from sqlglot.dialects.databricks import Databricks from sqlglot.dialects.dialect import Dialect, Dialects +from sqlglot.dialects.doris import Doris from sqlglot.dialects.drill import Drill from sqlglot.dialects.duckdb import DuckDB from sqlglot.dialects.hive import Hive diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py index e6b7743..cfde5fd 100644 --- a/sqlglot/dialects/clickhouse.py +++ b/sqlglot/dialects/clickhouse.py @@ -37,17 +37,22 @@ class ClickHouse(Dialect): "ATTACH": TokenType.COMMAND, "DATETIME64": TokenType.DATETIME64, "DICTIONARY": TokenType.DICTIONARY, + "ENUM": TokenType.ENUM, + "ENUM8": TokenType.ENUM8, + "ENUM16": TokenType.ENUM16, "FINAL": TokenType.FINAL, + "FIXEDSTRING": TokenType.FIXEDSTRING, "FLOAT32": TokenType.FLOAT, "FLOAT64": TokenType.DOUBLE, "GLOBAL": TokenType.GLOBAL, - "INT128": TokenType.INT128, "INT16": TokenType.SMALLINT, "INT256": TokenType.INT256, "INT32": TokenType.INT, "INT64": TokenType.BIGINT, "INT8": TokenType.TINYINT, + "LOWCARDINALITY": TokenType.LOWCARDINALITY, "MAP": TokenType.MAP, + "NESTED": TokenType.NESTED, "TUPLE": TokenType.STRUCT, "UINT128": TokenType.UINT128, "UINT16": TokenType.USMALLINT, @@ -294,11 +299,17 @@ class ClickHouse(Dialect): exp.DataType.Type.BIGINT: "Int64", exp.DataType.Type.DATETIME64: "DateTime64", exp.DataType.Type.DOUBLE: "Float64", + exp.DataType.Type.ENUM: "Enum", + exp.DataType.Type.ENUM8: "Enum8", + exp.DataType.Type.ENUM16: "Enum16", + exp.DataType.Type.FIXEDSTRING: "FixedString", exp.DataType.Type.FLOAT: "Float32", exp.DataType.Type.INT: "Int32", exp.DataType.Type.INT128: "Int128", exp.DataType.Type.INT256: "Int256", + exp.DataType.Type.LOWCARDINALITY: "LowCardinality", exp.DataType.Type.MAP: "Map", + exp.DataType.Type.NESTED: "Nested", exp.DataType.Type.NULLABLE: "Nullable", exp.DataType.Type.SMALLINT: "Int16", exp.DataType.Type.STRUCT: "Tuple", diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py index 1d0584c..132496f 100644 --- a/sqlglot/dialects/dialect.py +++ b/sqlglot/dialects/dialect.py @@ -39,6 +39,7 @@ class Dialects(str, Enum): TERADATA = "teradata" TRINO = "trino" TSQL = "tsql" + Doris = "doris" class _Dialect(type): @@ -121,7 +122,7 @@ class _Dialect(type): if hasattr(subclass, name): setattr(subclass, name, value) - if not klass.STRICT_STRING_CONCAT: + if not klass.STRICT_STRING_CONCAT and klass.DPIPE_IS_STRING_CONCAT: klass.parser_class.BITWISE[TokenType.DPIPE] = exp.SafeDPipe klass.generator_class.can_identify = klass.can_identify @@ -146,6 +147,9 @@ class Dialect(metaclass=_Dialect): # Determines whether or not an unquoted identifier can start with a digit IDENTIFIERS_CAN_START_WITH_DIGIT = False + # Determines whether or not the DPIPE token ('||') is a string concatenation operator + DPIPE_IS_STRING_CONCAT = True + # Determines whether or not CONCAT's arguments must be strings STRICT_STRING_CONCAT = False @@ -460,6 +464,20 @@ def format_time_lambda( return _format_time +def time_format( + dialect: DialectType = None, +) -> t.Callable[[Generator, exp.UnixToStr | exp.StrToUnix], t.Optional[str]]: + def _time_format(self: Generator, expression: exp.UnixToStr | exp.StrToUnix) -> t.Optional[str]: + """ + Returns the time format for a given expression, unless it's equivalent + to the default time format of the dialect of interest. + """ + time_format = self.format_time(expression) + return time_format if time_format != Dialect.get_or_raise(dialect).TIME_FORMAT else None + + return _time_format + + def create_with_partitions_sql(self: Generator, expression: exp.Create) -> str: """ In Hive and Spark, the PARTITIONED BY property acts as an extension of a table's schema. When the @@ -699,3 +717,8 @@ def simplify_literal(expression: E) -> E: def binary_from_function(expr_type: t.Type[B]) -> t.Callable[[t.List], B]: return lambda args: expr_type(this=seq_get(args, 0), expression=seq_get(args, 1)) + + +# Used to represent DATE_TRUNC in Doris, Postgres and Starrocks dialects +def parse_timestamp_trunc(args: t.List) -> exp.TimestampTrunc: + return exp.TimestampTrunc(this=seq_get(args, 1), unit=seq_get(args, 0)) diff --git a/sqlglot/dialects/doris.py b/sqlglot/dialects/doris.py new file mode 100644 index 0000000..160c23c --- /dev/null +++ b/sqlglot/dialects/doris.py @@ -0,0 +1,65 @@ +from __future__ import annotations + +from sqlglot import exp +from sqlglot.dialects.dialect import ( + approx_count_distinct_sql, + arrow_json_extract_sql, + parse_timestamp_trunc, + rename_func, + time_format, +) +from sqlglot.dialects.mysql import MySQL + + +class Doris(MySQL): + DATE_FORMAT = "'yyyy-MM-dd'" + DATEINT_FORMAT = "'yyyyMMdd'" + TIME_FORMAT = "'yyyy-MM-dd HH:mm:ss'" + + class Parser(MySQL.Parser): + FUNCTIONS = { + **MySQL.Parser.FUNCTIONS, + "DATE_TRUNC": parse_timestamp_trunc, + "REGEXP": exp.RegexpLike.from_arg_list, + } + + class Generator(MySQL.Generator): + CAST_MAPPING = {} + + TYPE_MAPPING = { + **MySQL.Generator.TYPE_MAPPING, + exp.DataType.Type.TEXT: "STRING", + exp.DataType.Type.TIMESTAMP: "DATETIME", + exp.DataType.Type.TIMESTAMPTZ: "DATETIME", + } + + TRANSFORMS = { + **MySQL.Generator.TRANSFORMS, + exp.ApproxDistinct: approx_count_distinct_sql, + exp.ArrayAgg: rename_func("COLLECT_LIST"), + exp.Coalesce: rename_func("NVL"), + exp.CurrentTimestamp: lambda *_: "NOW()", + exp.DateTrunc: lambda self, e: self.func( + "DATE_TRUNC", e.this, "'" + e.text("unit") + "'" + ), + exp.JSONExtractScalar: arrow_json_extract_sql, + exp.JSONExtract: arrow_json_extract_sql, + exp.RegexpLike: rename_func("REGEXP"), + exp.RegexpSplit: rename_func("SPLIT_BY_STRING"), + exp.SetAgg: rename_func("COLLECT_SET"), + exp.StrToUnix: lambda self, e: f"UNIX_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})", + exp.Split: rename_func("SPLIT_BY_STRING"), + exp.TimeStrToDate: rename_func("TO_DATE"), + exp.ToChar: lambda self, e: f"DATE_FORMAT({self.sql(e, 'this')}, {self.format_time(e)})", + exp.TsOrDsAdd: lambda self, e: f"DATE_ADD({self.sql(e, 'this')}, {self.sql(e, 'expression')})", # Only for day level + exp.TsOrDsToDate: lambda self, e: self.func("TO_DATE", e.this), + exp.TimeToUnix: rename_func("UNIX_TIMESTAMP"), + exp.TimestampTrunc: lambda self, e: self.func( + "DATE_TRUNC", e.this, "'" + e.text("unit") + "'" + ), + exp.UnixToStr: lambda self, e: self.func( + "FROM_UNIXTIME", e.this, time_format("doris")(self, e) + ), + exp.UnixToTime: rename_func("FROM_UNIXTIME"), + exp.Map: rename_func("ARRAY_MAP"), + } diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py index 5428e86..8253b52 100644 --- a/sqlglot/dialects/duckdb.py +++ b/sqlglot/dialects/duckdb.py @@ -89,6 +89,11 @@ def _struct_sql(self: generator.Generator, expression: exp.Struct) -> str: def _datatype_sql(self: generator.Generator, expression: exp.DataType) -> str: if expression.is_type("array"): return f"{self.expressions(expression, flat=True)}[]" + + # Type TIMESTAMP / TIME WITH TIME ZONE does not support any modifiers + if expression.is_type("timestamptz", "timetz"): + return expression.this.value + return self.datatype_sql(expression) @@ -110,14 +115,14 @@ class DuckDB(Dialect): "//": TokenType.DIV, "ATTACH": TokenType.COMMAND, "BINARY": TokenType.VARBINARY, - "BPCHAR": TokenType.TEXT, "BITSTRING": TokenType.BIT, + "BPCHAR": TokenType.TEXT, "CHAR": TokenType.TEXT, "CHARACTER VARYING": TokenType.TEXT, "EXCLUDE": TokenType.EXCEPT, + "HUGEINT": TokenType.INT128, "INT1": TokenType.TINYINT, "LOGICAL": TokenType.BOOLEAN, - "NUMERIC": TokenType.DOUBLE, "PIVOT_WIDER": TokenType.PIVOT, "SIGNED": TokenType.INT, "STRING": TokenType.VARCHAR, @@ -186,6 +191,22 @@ class DuckDB(Dialect): TokenType.UTINYINT, } + def _parse_types( + self, check_func: bool = False, schema: bool = False + ) -> t.Optional[exp.Expression]: + this = super()._parse_types(check_func=check_func, schema=schema) + + # DuckDB treats NUMERIC and DECIMAL without precision as DECIMAL(18, 3) + # See: https://duckdb.org/docs/sql/data_types/numeric + if ( + isinstance(this, exp.DataType) + and this.is_type("numeric", "decimal") + and not this.expressions + ): + return exp.DataType.build("DECIMAL(18, 3)") + + return this + def _pivot_column_names(self, aggregations: t.List[exp.Expression]) -> t.List[str]: if len(aggregations) == 1: return super()._pivot_column_names(aggregations) @@ -231,6 +252,7 @@ class DuckDB(Dialect): exp.Encode: lambda self, e: encode_decode_sql(self, e, "ENCODE", replace=False), exp.Explode: rename_func("UNNEST"), exp.IntDiv: lambda self, e: self.binary(e, "//"), + exp.IsNan: rename_func("ISNAN"), exp.JSONExtract: arrow_json_extract_sql, exp.JSONExtractScalar: arrow_json_extract_scalar_sql, exp.JSONFormat: _json_format_sql, diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py index aa4d845..584acc6 100644 --- a/sqlglot/dialects/hive.py +++ b/sqlglot/dialects/hive.py @@ -23,6 +23,7 @@ from sqlglot.dialects.dialect import ( right_to_substring_sql, strposition_to_locate_sql, struct_extract_sql, + time_format, timestrtotime_sql, var_map_sql, ) @@ -113,7 +114,7 @@ def _property_sql(self: generator.Generator, expression: exp.Property) -> str: def _str_to_unix_sql(self: generator.Generator, expression: exp.StrToUnix) -> str: - return self.func("UNIX_TIMESTAMP", expression.this, _time_format(self, expression)) + return self.func("UNIX_TIMESTAMP", expression.this, time_format("hive")(self, expression)) def _str_to_date_sql(self: generator.Generator, expression: exp.StrToDate) -> str: @@ -132,15 +133,6 @@ def _str_to_time_sql(self: generator.Generator, expression: exp.StrToTime) -> st return f"CAST({this} AS TIMESTAMP)" -def _time_format( - self: generator.Generator, expression: exp.UnixToStr | exp.StrToUnix -) -> t.Optional[str]: - time_format = self.format_time(expression) - if time_format == Hive.TIME_FORMAT: - return None - return time_format - - def _time_to_str(self: generator.Generator, expression: exp.TimeToStr) -> str: this = self.sql(expression, "this") time_format = self.format_time(expression) @@ -439,7 +431,7 @@ class Hive(Dialect): exp.TsOrDsToDate: _to_date_sql, exp.TryCast: no_trycast_sql, exp.UnixToStr: lambda self, e: self.func( - "FROM_UNIXTIME", e.this, _time_format(self, e) + "FROM_UNIXTIME", e.this, time_format("hive")(self, e) ), exp.UnixToTime: rename_func("FROM_UNIXTIME"), exp.UnixToTimeStr: rename_func("FROM_UNIXTIME"), diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py index 3cd99e7..9ab4ce8 100644 --- a/sqlglot/dialects/mysql.py +++ b/sqlglot/dialects/mysql.py @@ -94,6 +94,7 @@ def _date_add_sql(kind: str) -> t.Callable[[generator.Generator, exp.DateAdd | e class MySQL(Dialect): TIME_FORMAT = "'%Y-%m-%d %T'" + DPIPE_IS_STRING_CONCAT = False # https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions TIME_MAPPING = { @@ -103,7 +104,6 @@ class MySQL(Dialect): "%h": "%I", "%i": "%M", "%s": "%S", - "%S": "%S", "%u": "%W", "%k": "%-H", "%l": "%-I", @@ -196,8 +196,14 @@ class MySQL(Dialect): **parser.Parser.CONJUNCTION, TokenType.DAMP: exp.And, TokenType.XOR: exp.Xor, + TokenType.DPIPE: exp.Or, } + # MySQL uses || as a synonym to the logical OR operator + # https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html#operator_or + BITWISE = parser.Parser.BITWISE.copy() + BITWISE.pop(TokenType.DPIPE) + TABLE_ALIAS_TOKENS = ( parser.Parser.TABLE_ALIAS_TOKENS - parser.Parser.TABLE_INDEX_HINT_TOKENS ) diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py index ca44b70..73ca4e5 100644 --- a/sqlglot/dialects/postgres.py +++ b/sqlglot/dialects/postgres.py @@ -16,6 +16,7 @@ from sqlglot.dialects.dialect import ( no_pivot_sql, no_tablesample_sql, no_trycast_sql, + parse_timestamp_trunc, rename_func, simplify_literal, str_position_sql, @@ -286,9 +287,7 @@ class Postgres(Dialect): FUNCTIONS = { **parser.Parser.FUNCTIONS, - "DATE_TRUNC": lambda args: exp.TimestampTrunc( - this=seq_get(args, 1), unit=seq_get(args, 0) - ), + "DATE_TRUNC": parse_timestamp_trunc, "GENERATE_SERIES": _generate_series, "NOW": exp.CurrentTimestamp.from_arg_list, "TO_CHAR": format_time_lambda(exp.TimeToStr, "postgres"), diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py index 291b478..078da0b 100644 --- a/sqlglot/dialects/presto.py +++ b/sqlglot/dialects/presto.py @@ -32,13 +32,6 @@ def _approx_distinct_sql(self: generator.Generator, expression: exp.ApproxDistin return f"APPROX_DISTINCT({self.sql(expression, 'this')}{accuracy})" -def _datatype_sql(self: generator.Generator, expression: exp.DataType) -> str: - sql = self.datatype_sql(expression) - if expression.is_type("timestamptz"): - sql = f"{sql} WITH TIME ZONE" - return sql - - def _explode_to_unnest_sql(self: generator.Generator, expression: exp.Lateral) -> str: if isinstance(expression.this, (exp.Explode, exp.Posexplode)): expression = expression.copy() @@ -231,6 +224,7 @@ class Presto(Dialect): TABLE_HINTS = False QUERY_HINTS = False IS_BOOL_ALLOWED = False + TZ_TO_WITH_TIME_ZONE = True STRUCT_DELIMITER = ("(", ")") PROPERTIES_LOCATION = { @@ -245,6 +239,7 @@ class Presto(Dialect): exp.DataType.Type.FLOAT: "REAL", exp.DataType.Type.BINARY: "VARBINARY", exp.DataType.Type.TEXT: "VARCHAR", + exp.DataType.Type.TIMETZ: "TIME", exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", exp.DataType.Type.STRUCT: "ROW", } @@ -265,7 +260,6 @@ class Presto(Dialect): exp.BitwiseXor: lambda self, e: f"BITWISE_XOR({self.sql(e, 'this')}, {self.sql(e, 'expression')})", exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", - exp.DataType: _datatype_sql, exp.DateAdd: lambda self, e: self.func( "DATE_ADD", exp.Literal.string(e.text("unit") or "day"), e.expression, e.this ), diff --git a/sqlglot/dialects/redshift.py b/sqlglot/dialects/redshift.py index cdb8d0d..30731e1 100644 --- a/sqlglot/dialects/redshift.py +++ b/sqlglot/dialects/redshift.py @@ -85,8 +85,6 @@ class Redshift(Postgres): "HLLSKETCH": TokenType.HLLSKETCH, "SUPER": TokenType.SUPER, "SYSDATE": TokenType.CURRENT_TIMESTAMP, - "TIME": TokenType.TIMESTAMP, - "TIMETZ": TokenType.TIMESTAMPTZ, "TOP": TokenType.TOP, "UNLOAD": TokenType.COMMAND, "VARBYTE": TokenType.VARBINARY, @@ -101,12 +99,15 @@ class Redshift(Postgres): RENAME_TABLE_WITH_DB = False QUERY_HINTS = False VALUES_AS_TABLE = False + TZ_TO_WITH_TIME_ZONE = True TYPE_MAPPING = { **Postgres.Generator.TYPE_MAPPING, exp.DataType.Type.BINARY: "VARBYTE", - exp.DataType.Type.VARBINARY: "VARBYTE", exp.DataType.Type.INT: "INTEGER", + exp.DataType.Type.TIMETZ: "TIME", + exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", + exp.DataType.Type.VARBINARY: "VARBYTE", } PROPERTIES_LOCATION = { diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py index b9aaa66..7c8982b 100644 --- a/sqlglot/dialects/spark.py +++ b/sqlglot/dialects/spark.py @@ -52,6 +52,9 @@ class Spark(Spark2): TRANSFORMS = { **Spark2.Generator.TRANSFORMS, exp.StartsWith: rename_func("STARTSWITH"), + exp.TimestampAdd: lambda self, e: self.func( + "DATEADD", e.args.get("unit") or "DAY", e.expression, e.this + ), } TRANSFORMS.pop(exp.DateDiff) TRANSFORMS.pop(exp.Group) diff --git a/sqlglot/dialects/starrocks.py b/sqlglot/dialects/starrocks.py index 4f6183c..2dba1c1 100644 --- a/sqlglot/dialects/starrocks.py +++ b/sqlglot/dialects/starrocks.py @@ -4,6 +4,7 @@ from sqlglot import exp from sqlglot.dialects.dialect import ( approx_count_distinct_sql, arrow_json_extract_sql, + parse_timestamp_trunc, rename_func, ) from sqlglot.dialects.mysql import MySQL @@ -14,9 +15,7 @@ class StarRocks(MySQL): class Parser(MySQL.Parser): FUNCTIONS = { **MySQL.Parser.FUNCTIONS, - "DATE_TRUNC": lambda args: exp.TimestampTrunc( - this=seq_get(args, 1), unit=seq_get(args, 0) - ), + "DATE_TRUNC": parse_timestamp_trunc, "DATEDIFF": lambda args: exp.DateDiff( this=seq_get(args, 0), expression=seq_get(args, 1), unit=exp.Literal.string("DAY") ), |