Edit on GitHub

sqlglot.dialects.presto

View Source

  1from __future__ import annotations
  2
  3from sqlglot import exp, generator, parser, tokens, transforms
  4from sqlglot.dialects.dialect import (
  5    Dialect,
  6    format_time_lambda,
  7    if_sql,
  8    no_ilike_sql,
  9    no_safe_divide_sql,
 10    rename_func,
 11    struct_extract_sql,
 12    timestrtotime_sql,
 13)
 14from sqlglot.dialects.mysql import MySQL
 15from sqlglot.errors import UnsupportedError
 16from sqlglot.helper import seq_get
 17from sqlglot.tokens import TokenType
 18
 19
 20def _approx_distinct_sql(self, expression):
 21    accuracy = expression.args.get("accuracy")
 22    accuracy = ", " + self.sql(accuracy) if accuracy else ""
 23    return f"APPROX_DISTINCT({self.sql(expression, 'this')}{accuracy})"
 24
 25
 26def _datatype_sql(self, expression):
 27    sql = self.datatype_sql(expression)
 28    if expression.this == exp.DataType.Type.TIMESTAMPTZ:
 29        sql = f"{sql} WITH TIME ZONE"
 30    return sql
 31
 32
 33def _explode_to_unnest_sql(self, expression):
 34    if isinstance(expression.this, (exp.Explode, exp.Posexplode)):
 35        return self.sql(
 36            exp.Join(
 37                this=exp.Unnest(
 38                    expressions=[expression.this.this],
 39                    alias=expression.args.get("alias"),
 40                    ordinality=isinstance(expression.this, exp.Posexplode),
 41                ),
 42                kind="cross",
 43            )
 44        )
 45    return self.lateral_sql(expression)
 46
 47
 48def _initcap_sql(self, expression):
 49    regex = r"(\w)(\w*)"
 50    return f"REGEXP_REPLACE({self.sql(expression, 'this')}, '{regex}', x -> UPPER(x[1]) || LOWER(x[2]))"
 51
 52
 53def _decode_sql(self, expression):
 54    _ensure_utf8(expression.args.get("charset"))
 55    return f"FROM_UTF8({self.format_args(expression.this, expression.args.get('replace'))})"
 56
 57
 58def _encode_sql(self, expression):
 59    _ensure_utf8(expression.args.get("charset"))
 60    return f"TO_UTF8({self.sql(expression, 'this')})"
 61
 62
 63def _no_sort_array(self, expression):
 64    if expression.args.get("asc") == exp.false():
 65        comparator = "(a, b) -> CASE WHEN a < b THEN 1 WHEN a > b THEN -1 ELSE 0 END"
 66    else:
 67        comparator = None
 68    args = self.format_args(expression.this, comparator)
 69    return f"ARRAY_SORT({args})"
 70
 71
 72def _schema_sql(self, expression):
 73    if isinstance(expression.parent, exp.Property):
 74        columns = ", ".join(f"'{c.name}'" for c in expression.expressions)
 75        return f"ARRAY[{columns}]"
 76
 77    for schema in expression.parent.find_all(exp.Schema):
 78        if isinstance(schema.parent, exp.Property):
 79            expression = expression.copy()
 80            expression.expressions.extend(schema.expressions)
 81
 82    return self.schema_sql(expression)
 83
 84
 85def _quantile_sql(self, expression):
 86    self.unsupported("Presto does not support exact quantiles")
 87    return f"APPROX_PERCENTILE({self.sql(expression, 'this')}, {self.sql(expression, 'quantile')})"
 88
 89
 90def _str_to_time_sql(self, expression):
 91    return f"DATE_PARSE({self.sql(expression, 'this')}, {self.format_time(expression)})"
 92
 93
 94def _ts_or_ds_to_date_sql(self, expression):
 95    time_format = self.format_time(expression)
 96    if time_format and time_format not in (Presto.time_format, Presto.date_format):
 97        return f"CAST({_str_to_time_sql(self, expression)} AS DATE)"
 98    return f"CAST(SUBSTR(CAST({self.sql(expression, 'this')} AS VARCHAR), 1, 10) AS DATE)"
 99
100
101def _ts_or_ds_add_sql(self, expression):
102    this = self.sql(expression, "this")
103    e = self.sql(expression, "expression")
104    unit = self.sql(expression, "unit") or "'day'"
105    return f"DATE_ADD({unit}, {e}, DATE_PARSE(SUBSTR({this}, 1, 10), {Presto.date_format}))"
106
107
108def _sequence_sql(self, expression):
109    start = expression.args["start"]
110    end = expression.args["end"]
111    step = expression.args.get("step", 1)  # Postgres defaults to 1 for generate_series
112
113    target_type = None
114
115    if isinstance(start, exp.Cast):
116        target_type = start.to
117    elif isinstance(end, exp.Cast):
118        target_type = end.to
119
120    if target_type and target_type.this == exp.DataType.Type.TIMESTAMP:
121        to = target_type.copy()
122
123        if target_type is start.to:
124            end = exp.Cast(this=end, to=to)
125        else:
126            start = exp.Cast(this=start, to=to)
127
128    return f"SEQUENCE({self.format_args(start, end, step)})"
129
130
131def _ensure_utf8(charset):
132    if charset.name.lower() != "utf-8":
133        raise UnsupportedError(f"Unsupported charset {charset}")
134
135
136def _approx_percentile(args):
137    if len(args) == 4:
138        return exp.ApproxQuantile(
139            this=seq_get(args, 0),
140            weight=seq_get(args, 1),
141            quantile=seq_get(args, 2),
142            accuracy=seq_get(args, 3),
143        )
144    if len(args) == 3:
145        return exp.ApproxQuantile(
146            this=seq_get(args, 0),
147            quantile=seq_get(args, 1),
148            accuracy=seq_get(args, 2),
149        )
150    return exp.ApproxQuantile.from_arg_list(args)
151
152
153def _from_unixtime(args):
154    if len(args) == 3:
155        return exp.UnixToTime(
156            this=seq_get(args, 0),
157            hours=seq_get(args, 1),
158            minutes=seq_get(args, 2),
159        )
160    if len(args) == 2:
161        return exp.UnixToTime(
162            this=seq_get(args, 0),
163            zone=seq_get(args, 1),
164        )
165    return exp.UnixToTime.from_arg_list(args)
166
167
168class Presto(Dialect):
169    index_offset = 1
170    null_ordering = "nulls_are_last"
171    time_format = MySQL.time_format  # type: ignore
172    time_mapping = MySQL.time_mapping  # type: ignore
173
174    class Tokenizer(tokens.Tokenizer):
175        KEYWORDS = {
176            **tokens.Tokenizer.KEYWORDS,
177            "START": TokenType.BEGIN,
178            "ROW": TokenType.STRUCT,
179        }
180
181    class Parser(parser.Parser):
182        FUNCTIONS = {
183            **parser.Parser.FUNCTIONS,  # type: ignore
184            "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list,
185            "CARDINALITY": exp.ArraySize.from_arg_list,
186            "CONTAINS": exp.ArrayContains.from_arg_list,
187            "DATE_ADD": lambda args: exp.DateAdd(
188                this=seq_get(args, 2),
189                expression=seq_get(args, 1),
190                unit=seq_get(args, 0),
191            ),
192            "DATE_DIFF": lambda args: exp.DateDiff(
193                this=seq_get(args, 2),
194                expression=seq_get(args, 1),
195                unit=seq_get(args, 0),
196            ),
197            "DATE_FORMAT": format_time_lambda(exp.TimeToStr, "presto"),
198            "DATE_PARSE": format_time_lambda(exp.StrToTime, "presto"),
199            "FROM_UNIXTIME": _from_unixtime,
200            "NOW": exp.CurrentTimestamp.from_arg_list,
201            "STRPOS": lambda args: exp.StrPosition(
202                this=seq_get(args, 0),
203                substr=seq_get(args, 1),
204                instance=seq_get(args, 2),
205            ),
206            "TO_UNIXTIME": exp.TimeToUnix.from_arg_list,
207            "APPROX_PERCENTILE": _approx_percentile,
208            "FROM_HEX": exp.Unhex.from_arg_list,
209            "TO_HEX": exp.Hex.from_arg_list,
210            "TO_UTF8": lambda args: exp.Encode(
211                this=seq_get(args, 0), charset=exp.Literal.string("utf-8")
212            ),
213            "FROM_UTF8": lambda args: exp.Decode(
214                this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8")
215            ),
216        }
217        FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy()
218        FUNCTION_PARSERS.pop("TRIM")
219
220    class Generator(generator.Generator):
221        STRUCT_DELIMITER = ("(", ")")
222
223        PROPERTIES_LOCATION = {
224            **generator.Generator.PROPERTIES_LOCATION,  # type: ignore
225            exp.LocationProperty: exp.Properties.Location.UNSUPPORTED,
226        }
227
228        TYPE_MAPPING = {
229            **generator.Generator.TYPE_MAPPING,  # type: ignore
230            exp.DataType.Type.INT: "INTEGER",
231            exp.DataType.Type.FLOAT: "REAL",
232            exp.DataType.Type.BINARY: "VARBINARY",
233            exp.DataType.Type.TEXT: "VARCHAR",
234            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
235            exp.DataType.Type.STRUCT: "ROW",
236        }
237
238        TRANSFORMS = {
239            **generator.Generator.TRANSFORMS,  # type: ignore
240            **transforms.UNALIAS_GROUP,  # type: ignore
241            exp.ApproxDistinct: _approx_distinct_sql,
242            exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]",
243            exp.ArrayConcat: rename_func("CONCAT"),
244            exp.ArrayContains: rename_func("CONTAINS"),
245            exp.ArraySize: rename_func("CARDINALITY"),
246            exp.BitwiseAnd: lambda self, e: f"BITWISE_AND({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
247            exp.BitwiseLeftShift: lambda self, e: f"BITWISE_ARITHMETIC_SHIFT_LEFT({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
248            exp.BitwiseNot: lambda self, e: f"BITWISE_NOT({self.sql(e, 'this')})",
249            exp.BitwiseOr: lambda self, e: f"BITWISE_OR({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
250            exp.BitwiseRightShift: lambda self, e: f"BITWISE_ARITHMETIC_SHIFT_RIGHT({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
251            exp.BitwiseXor: lambda self, e: f"BITWISE_XOR({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
252            exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
253            exp.DataType: _datatype_sql,
254            exp.DateAdd: lambda self, e: f"""DATE_ADD({self.sql(e, 'unit') or "'day'"}, {self.sql(e, 'expression')}, {self.sql(e, 'this')})""",
255            exp.DateDiff: lambda self, e: f"""DATE_DIFF({self.sql(e, 'unit') or "'day'"}, {self.sql(e, 'expression')}, {self.sql(e, 'this')})""",
256            exp.DateStrToDate: lambda self, e: f"CAST(DATE_PARSE({self.sql(e, 'this')}, {Presto.date_format}) AS DATE)",
257            exp.DateToDi: lambda self, e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.dateint_format}) AS INT)",
258            exp.Decode: _decode_sql,
259            exp.DiToDate: lambda self, e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.dateint_format}) AS DATE)",
260            exp.Encode: _encode_sql,
261            exp.GenerateSeries: _sequence_sql,
262            exp.Hex: rename_func("TO_HEX"),
263            exp.If: if_sql,
264            exp.ILike: no_ilike_sql,
265            exp.Initcap: _initcap_sql,
266            exp.Lateral: _explode_to_unnest_sql,
267            exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"),
268            exp.LogicalOr: rename_func("BOOL_OR"),
269            exp.Quantile: _quantile_sql,
270            exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"),
271            exp.SafeDivide: no_safe_divide_sql,
272            exp.Schema: _schema_sql,
273            exp.SortArray: _no_sort_array,
274            exp.StrPosition: rename_func("STRPOS"),
275            exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
276            exp.StrToTime: _str_to_time_sql,
277            exp.StrToUnix: lambda self, e: f"TO_UNIXTIME(DATE_PARSE({self.sql(e, 'this')}, {self.format_time(e)}))",
278            exp.StructExtract: struct_extract_sql,
279            exp.TableFormatProperty: lambda self, e: f"TABLE_FORMAT='{e.name.upper()}'",
280            exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'",
281            exp.TimeStrToDate: timestrtotime_sql,
282            exp.TimeStrToTime: timestrtotime_sql,
283            exp.TimeStrToUnix: lambda self, e: f"TO_UNIXTIME(DATE_PARSE({self.sql(e, 'this')}, {Presto.time_format}))",
284            exp.TimeToStr: lambda self, e: f"DATE_FORMAT({self.sql(e, 'this')}, {self.format_time(e)})",
285            exp.TimeToUnix: rename_func("TO_UNIXTIME"),
286            exp.TsOrDiToDi: lambda self, e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)",
287            exp.TsOrDsAdd: _ts_or_ds_add_sql,
288            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
289            exp.Unhex: rename_func("FROM_HEX"),
290            exp.UnixToStr: lambda self, e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})",
291            exp.UnixToTime: rename_func("FROM_UNIXTIME"),
292            exp.UnixToTimeStr: lambda self, e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)",
293            exp.VariancePop: rename_func("VAR_POP"),
294        }
295
296        def transaction_sql(self, expression):
297            modes = expression.args.get("modes")
298            modes = f" {', '.join(modes)}" if modes else ""
299            return f"START TRANSACTION{modes}"

class Presto(sqlglot.dialects.dialect.Dialect): View Source

169class Presto(Dialect):
170    index_offset = 1
171    null_ordering = "nulls_are_last"
172    time_format = MySQL.time_format  # type: ignore
173    time_mapping = MySQL.time_mapping  # type: ignore
174
175    class Tokenizer(tokens.Tokenizer):
176        KEYWORDS = {
177            **tokens.Tokenizer.KEYWORDS,
178            "START": TokenType.BEGIN,
179            "ROW": TokenType.STRUCT,
180        }
181
182    class Parser(parser.Parser):
183        FUNCTIONS = {
184            **parser.Parser.FUNCTIONS,  # type: ignore
185            "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list,
186            "CARDINALITY": exp.ArraySize.from_arg_list,
187            "CONTAINS": exp.ArrayContains.from_arg_list,
188            "DATE_ADD": lambda args: exp.DateAdd(
189                this=seq_get(args, 2),
190                expression=seq_get(args, 1),
191                unit=seq_get(args, 0),
192            ),
193            "DATE_DIFF": lambda args: exp.DateDiff(
194                this=seq_get(args, 2),
195                expression=seq_get(args, 1),
196                unit=seq_get(args, 0),
197            ),
198            "DATE_FORMAT": format_time_lambda(exp.TimeToStr, "presto"),
199            "DATE_PARSE": format_time_lambda(exp.StrToTime, "presto"),
200            "FROM_UNIXTIME": _from_unixtime,
201            "NOW": exp.CurrentTimestamp.from_arg_list,
202            "STRPOS": lambda args: exp.StrPosition(
203                this=seq_get(args, 0),
204                substr=seq_get(args, 1),
205                instance=seq_get(args, 2),
206            ),
207            "TO_UNIXTIME": exp.TimeToUnix.from_arg_list,
208            "APPROX_PERCENTILE": _approx_percentile,
209            "FROM_HEX": exp.Unhex.from_arg_list,
210            "TO_HEX": exp.Hex.from_arg_list,
211            "TO_UTF8": lambda args: exp.Encode(
212                this=seq_get(args, 0), charset=exp.Literal.string("utf-8")
213            ),
214            "FROM_UTF8": lambda args: exp.Decode(
215                this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8")
216            ),
217        }
218        FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy()
219        FUNCTION_PARSERS.pop("TRIM")
220
221    class Generator(generator.Generator):
222        STRUCT_DELIMITER = ("(", ")")
223
224        PROPERTIES_LOCATION = {
225            **generator.Generator.PROPERTIES_LOCATION,  # type: ignore
226            exp.LocationProperty: exp.Properties.Location.UNSUPPORTED,
227        }
228
229        TYPE_MAPPING = {
230            **generator.Generator.TYPE_MAPPING,  # type: ignore
231            exp.DataType.Type.INT: "INTEGER",
232            exp.DataType.Type.FLOAT: "REAL",
233            exp.DataType.Type.BINARY: "VARBINARY",
234            exp.DataType.Type.TEXT: "VARCHAR",
235            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
236            exp.DataType.Type.STRUCT: "ROW",
237        }
238
239        TRANSFORMS = {
240            **generator.Generator.TRANSFORMS,  # type: ignore
241            **transforms.UNALIAS_GROUP,  # type: ignore
242            exp.ApproxDistinct: _approx_distinct_sql,
243            exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]",
244            exp.ArrayConcat: rename_func("CONCAT"),
245            exp.ArrayContains: rename_func("CONTAINS"),
246            exp.ArraySize: rename_func("CARDINALITY"),
247            exp.BitwiseAnd: lambda self, e: f"BITWISE_AND({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
248            exp.BitwiseLeftShift: lambda self, e: f"BITWISE_ARITHMETIC_SHIFT_LEFT({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
249            exp.BitwiseNot: lambda self, e: f"BITWISE_NOT({self.sql(e, 'this')})",
250            exp.BitwiseOr: lambda self, e: f"BITWISE_OR({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
251            exp.BitwiseRightShift: lambda self, e: f"BITWISE_ARITHMETIC_SHIFT_RIGHT({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
252            exp.BitwiseXor: lambda self, e: f"BITWISE_XOR({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
253            exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
254            exp.DataType: _datatype_sql,
255            exp.DateAdd: lambda self, e: f"""DATE_ADD({self.sql(e, 'unit') or "'day'"}, {self.sql(e, 'expression')}, {self.sql(e, 'this')})""",
256            exp.DateDiff: lambda self, e: f"""DATE_DIFF({self.sql(e, 'unit') or "'day'"}, {self.sql(e, 'expression')}, {self.sql(e, 'this')})""",
257            exp.DateStrToDate: lambda self, e: f"CAST(DATE_PARSE({self.sql(e, 'this')}, {Presto.date_format}) AS DATE)",
258            exp.DateToDi: lambda self, e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.dateint_format}) AS INT)",
259            exp.Decode: _decode_sql,
260            exp.DiToDate: lambda self, e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.dateint_format}) AS DATE)",
261            exp.Encode: _encode_sql,
262            exp.GenerateSeries: _sequence_sql,
263            exp.Hex: rename_func("TO_HEX"),
264            exp.If: if_sql,
265            exp.ILike: no_ilike_sql,
266            exp.Initcap: _initcap_sql,
267            exp.Lateral: _explode_to_unnest_sql,
268            exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"),
269            exp.LogicalOr: rename_func("BOOL_OR"),
270            exp.Quantile: _quantile_sql,
271            exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"),
272            exp.SafeDivide: no_safe_divide_sql,
273            exp.Schema: _schema_sql,
274            exp.SortArray: _no_sort_array,
275            exp.StrPosition: rename_func("STRPOS"),
276            exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
277            exp.StrToTime: _str_to_time_sql,
278            exp.StrToUnix: lambda self, e: f"TO_UNIXTIME(DATE_PARSE({self.sql(e, 'this')}, {self.format_time(e)}))",
279            exp.StructExtract: struct_extract_sql,
280            exp.TableFormatProperty: lambda self, e: f"TABLE_FORMAT='{e.name.upper()}'",
281            exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'",
282            exp.TimeStrToDate: timestrtotime_sql,
283            exp.TimeStrToTime: timestrtotime_sql,
284            exp.TimeStrToUnix: lambda self, e: f"TO_UNIXTIME(DATE_PARSE({self.sql(e, 'this')}, {Presto.time_format}))",
285            exp.TimeToStr: lambda self, e: f"DATE_FORMAT({self.sql(e, 'this')}, {self.format_time(e)})",
286            exp.TimeToUnix: rename_func("TO_UNIXTIME"),
287            exp.TsOrDiToDi: lambda self, e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)",
288            exp.TsOrDsAdd: _ts_or_ds_add_sql,
289            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
290            exp.Unhex: rename_func("FROM_HEX"),
291            exp.UnixToStr: lambda self, e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})",
292            exp.UnixToTime: rename_func("FROM_UNIXTIME"),
293            exp.UnixToTimeStr: lambda self, e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)",
294            exp.VariancePop: rename_func("VAR_POP"),
295        }
296
297        def transaction_sql(self, expression):
298            modes = expression.args.get("modes")
299            modes = f" {', '.join(modes)}" if modes else ""
300            return f"START TRANSACTION{modes}"

Presto()

Inherited Members

sqlglot.dialects.dialect.Dialect: get_or_raise; format_time; parse; parse_into; generate; transpile; parser; generator

class Presto.Tokenizer(sqlglot.tokens.Tokenizer): View Source

175    class Tokenizer(tokens.Tokenizer):
176        KEYWORDS = {
177            **tokens.Tokenizer.KEYWORDS,
178            "START": TokenType.BEGIN,
179            "ROW": TokenType.STRUCT,
180        }

Inherited Members

sqlglot.tokens.Tokenizer: Tokenizer; reset; tokenize

class Presto.Parser(sqlglot.parser.Parser): View Source

182    class Parser(parser.Parser):
183        FUNCTIONS = {
184            **parser.Parser.FUNCTIONS,  # type: ignore
185            "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list,
186            "CARDINALITY": exp.ArraySize.from_arg_list,
187            "CONTAINS": exp.ArrayContains.from_arg_list,
188            "DATE_ADD": lambda args: exp.DateAdd(
189                this=seq_get(args, 2),
190                expression=seq_get(args, 1),
191                unit=seq_get(args, 0),
192            ),
193            "DATE_DIFF": lambda args: exp.DateDiff(
194                this=seq_get(args, 2),
195                expression=seq_get(args, 1),
196                unit=seq_get(args, 0),
197            ),
198            "DATE_FORMAT": format_time_lambda(exp.TimeToStr, "presto"),
199            "DATE_PARSE": format_time_lambda(exp.StrToTime, "presto"),
200            "FROM_UNIXTIME": _from_unixtime,
201            "NOW": exp.CurrentTimestamp.from_arg_list,
202            "STRPOS": lambda args: exp.StrPosition(
203                this=seq_get(args, 0),
204                substr=seq_get(args, 1),
205                instance=seq_get(args, 2),
206            ),
207            "TO_UNIXTIME": exp.TimeToUnix.from_arg_list,
208            "APPROX_PERCENTILE": _approx_percentile,
209            "FROM_HEX": exp.Unhex.from_arg_list,
210            "TO_HEX": exp.Hex.from_arg_list,
211            "TO_UTF8": lambda args: exp.Encode(
212                this=seq_get(args, 0), charset=exp.Literal.string("utf-8")
213            ),
214            "FROM_UTF8": lambda args: exp.Decode(
215                this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8")
216            ),
217        }
218        FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy()
219        FUNCTION_PARSERS.pop("TRIM")

Parser consumes a list of tokens produced by the sqlglot.tokens.Tokenizer and produces a parsed syntax tree.

Arguments:

error_level: the desired error level. Default: ErrorLevel.RAISE
error_message_context: determines the amount of context to capture from a query string when displaying the error message (in number of characters). Default: 50.
index_offset: Index offset for arrays eg ARRAY[0] vs ARRAY[1] as the head of a list. Default: 0
alias_post_tablesample: If the table alias comes after tablesample. Default: False
max_errors: Maximum number of error messages to include in a raised ParseError. This is only relevant if error_level is ErrorLevel.RAISE. Default: 3
null_ordering: Indicates the default null ordering method to use if not explicitly set. Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". Default: "nulls_are_small"

Inherited Members

sqlglot.parser.Parser: Parser; reset; parse; parse_into; check_errors; raise_error; expression; validate_expression

class Presto.Generator(sqlglot.generator.Generator): View Source

221    class Generator(generator.Generator):
222        STRUCT_DELIMITER = ("(", ")")
223
224        PROPERTIES_LOCATION = {
225            **generator.Generator.PROPERTIES_LOCATION,  # type: ignore
226            exp.LocationProperty: exp.Properties.Location.UNSUPPORTED,
227        }
228
229        TYPE_MAPPING = {
230            **generator.Generator.TYPE_MAPPING,  # type: ignore
231            exp.DataType.Type.INT: "INTEGER",
232            exp.DataType.Type.FLOAT: "REAL",
233            exp.DataType.Type.BINARY: "VARBINARY",
234            exp.DataType.Type.TEXT: "VARCHAR",
235            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
236            exp.DataType.Type.STRUCT: "ROW",
237        }
238
239        TRANSFORMS = {
240            **generator.Generator.TRANSFORMS,  # type: ignore
241            **transforms.UNALIAS_GROUP,  # type: ignore
242            exp.ApproxDistinct: _approx_distinct_sql,
243            exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]",
244            exp.ArrayConcat: rename_func("CONCAT"),
245            exp.ArrayContains: rename_func("CONTAINS"),
246            exp.ArraySize: rename_func("CARDINALITY"),
247            exp.BitwiseAnd: lambda self, e: f"BITWISE_AND({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
248            exp.BitwiseLeftShift: lambda self, e: f"BITWISE_ARITHMETIC_SHIFT_LEFT({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
249            exp.BitwiseNot: lambda self, e: f"BITWISE_NOT({self.sql(e, 'this')})",
250            exp.BitwiseOr: lambda self, e: f"BITWISE_OR({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
251            exp.BitwiseRightShift: lambda self, e: f"BITWISE_ARITHMETIC_SHIFT_RIGHT({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
252            exp.BitwiseXor: lambda self, e: f"BITWISE_XOR({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
253            exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
254            exp.DataType: _datatype_sql,
255            exp.DateAdd: lambda self, e: f"""DATE_ADD({self.sql(e, 'unit') or "'day'"}, {self.sql(e, 'expression')}, {self.sql(e, 'this')})""",
256            exp.DateDiff: lambda self, e: f"""DATE_DIFF({self.sql(e, 'unit') or "'day'"}, {self.sql(e, 'expression')}, {self.sql(e, 'this')})""",
257            exp.DateStrToDate: lambda self, e: f"CAST(DATE_PARSE({self.sql(e, 'this')}, {Presto.date_format}) AS DATE)",
258            exp.DateToDi: lambda self, e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.dateint_format}) AS INT)",
259            exp.Decode: _decode_sql,
260            exp.DiToDate: lambda self, e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.dateint_format}) AS DATE)",
261            exp.Encode: _encode_sql,
262            exp.GenerateSeries: _sequence_sql,
263            exp.Hex: rename_func("TO_HEX"),
264            exp.If: if_sql,
265            exp.ILike: no_ilike_sql,
266            exp.Initcap: _initcap_sql,
267            exp.Lateral: _explode_to_unnest_sql,
268            exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"),
269            exp.LogicalOr: rename_func("BOOL_OR"),
270            exp.Quantile: _quantile_sql,
271            exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"),
272            exp.SafeDivide: no_safe_divide_sql,
273            exp.Schema: _schema_sql,
274            exp.SortArray: _no_sort_array,
275            exp.StrPosition: rename_func("STRPOS"),
276            exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
277            exp.StrToTime: _str_to_time_sql,
278            exp.StrToUnix: lambda self, e: f"TO_UNIXTIME(DATE_PARSE({self.sql(e, 'this')}, {self.format_time(e)}))",
279            exp.StructExtract: struct_extract_sql,
280            exp.TableFormatProperty: lambda self, e: f"TABLE_FORMAT='{e.name.upper()}'",
281            exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'",
282            exp.TimeStrToDate: timestrtotime_sql,
283            exp.TimeStrToTime: timestrtotime_sql,
284            exp.TimeStrToUnix: lambda self, e: f"TO_UNIXTIME(DATE_PARSE({self.sql(e, 'this')}, {Presto.time_format}))",
285            exp.TimeToStr: lambda self, e: f"DATE_FORMAT({self.sql(e, 'this')}, {self.format_time(e)})",
286            exp.TimeToUnix: rename_func("TO_UNIXTIME"),
287            exp.TsOrDiToDi: lambda self, e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)",
288            exp.TsOrDsAdd: _ts_or_ds_add_sql,
289            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
290            exp.Unhex: rename_func("FROM_HEX"),
291            exp.UnixToStr: lambda self, e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})",
292            exp.UnixToTime: rename_func("FROM_UNIXTIME"),
293            exp.UnixToTimeStr: lambda self, e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)",
294            exp.VariancePop: rename_func("VAR_POP"),
295        }
296
297        def transaction_sql(self, expression):
298            modes = expression.args.get("modes")
299            modes = f" {', '.join(modes)}" if modes else ""
300            return f"START TRANSACTION{modes}"

Generator interprets the given syntax tree and produces a SQL string as an output.

Arguments:

time_mapping (dict): the dictionary of custom time mappings in which the key represents a python time format and the output the target time format
time_trie (trie): a trie of the time_mapping keys
pretty (bool): if set to True the returned string will be formatted. Default: False.
quote_start (str): specifies which starting character to use to delimit quotes. Default: '.
quote_end (str): specifies which ending character to use to delimit quotes. Default: '.
identifier_start (str): specifies which starting character to use to delimit identifiers. Default: ".
identifier_end (str): specifies which ending character to use to delimit identifiers. Default: ".
identify (bool): if set to True all identifiers will be delimited by the corresponding character.
normalize (bool): if set to True all identifiers will lower cased
string_escape (str): specifies a string escape character. Default: '.
identifier_escape (str): specifies an identifier escape character. Default: ".
pad (int): determines padding in a formatted string. Default: 2.
indent (int): determines the size of indentation in a formatted string. Default: 4.
unnest_column_only (bool): if true unnest table aliases are considered only as column aliases
normalize_functions (str): normalize function names, "upper", "lower", or None Default: "upper"
alias_post_tablesample (bool): if the table alias comes after tablesample Default: False
unsupported_level (ErrorLevel): determines the generator's behavior when it encounters unsupported expressions. Default ErrorLevel.WARN.
null_ordering (str): Indicates the default null ordering method to use if not explicitly set. Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". Default: "nulls_are_small"
max_unsupported (int): Maximum number of unsupported messages to include in a raised UnsupportedError. This is only relevant if unsupported_level is ErrorLevel.RAISE. Default: 3
leading_comma (bool): if the the comma is leading or trailing in select statements Default: False
max_text_width: The max number of characters in a segment before creating new lines in pretty mode. The default is on the smaller end because the length only represents a segment and not the true line length. Default: 80
comments: Whether or not to preserve comments in the output SQL code. Default: True

def transaction_sql(self, expression): View Source

297        def transaction_sql(self, expression):
298            modes = expression.args.get("modes")
299            modes = f" {', '.join(modes)}" if modes else ""
300            return f"START TRANSACTION{modes}"

Inherited Members

sqlglot.generator.Generator: Generator; generate; unsupported; sep; seg; pad_comment; maybe_comment; wrap; no_identify; normalize_func; indent; sql; uncache_sql; cache_sql; characterset_sql; column_sql; columndef_sql; columnconstraint_sql; autoincrementcolumnconstraint_sql; checkcolumnconstraint_sql; commentcolumnconstraint_sql; collatecolumnconstraint_sql; encodecolumnconstraint_sql; defaultcolumnconstraint_sql; generatedasidentitycolumnconstraint_sql; notnullcolumnconstraint_sql; primarykeycolumnconstraint_sql; uniquecolumnconstraint_sql; create_sql; describe_sql; prepend_ctes; with_sql; cte_sql; tablealias_sql; bitstring_sql; hexstring_sql; datatype_sql; directory_sql; delete_sql; drop_sql; except_sql; except_op; fetch_sql; filter_sql; hint_sql; index_sql; identifier_sql; national_sql; partition_sql; properties_sql; root_properties; properties; with_properties; locate_properties; property_sql; likeproperty_sql; fallbackproperty_sql; journalproperty_sql; freespaceproperty_sql; afterjournalproperty_sql; checksumproperty_sql; mergeblockratioproperty_sql; datablocksizeproperty_sql; blockcompressionproperty_sql; isolatedloadingproperty_sql; insert_sql; intersect_sql; intersect_op; introducer_sql; pseudotype_sql; rowformatdelimitedproperty_sql; table_sql; tablesample_sql; pivot_sql; tuple_sql; update_sql; values_sql; var_sql; into_sql; from_sql; group_sql; having_sql; join_sql; lambda_sql; lateral_sql; limit_sql; offset_sql; lock_sql; literal_sql; loaddata_sql; null_sql; boolean_sql; order_sql; cluster_sql; distribute_sql; sort_sql; ordered_sql; matchrecognize_sql; query_modifiers; select_sql; schema_sql; star_sql; structkwarg_sql; parameter_sql; sessionparameter_sql; placeholder_sql; subquery_sql; qualify_sql; union_sql; union_op; unnest_sql; where_sql; window_sql; partition_by_sql; window_spec_sql; withingroup_sql; between_sql; bracket_sql; all_sql; any_sql; exists_sql; case_sql; constraint_sql; extract_sql; trim_sql; concat_sql; check_sql; foreignkey_sql; primarykey_sql; unique_sql; if_sql; in_sql; in_unnest_op; interval_sql; return_sql; reference_sql; anonymous_sql; paren_sql; neg_sql; not_sql; alias_sql; aliases_sql; attimezone_sql; add_sql; and_sql; connector_sql; bitwiseand_sql; bitwiseleftshift_sql; bitwisenot_sql; bitwiseor_sql; bitwiserightshift_sql; bitwisexor_sql; cast_sql; currentdate_sql; collate_sql; command_sql; commit_sql; rollback_sql; altercolumn_sql; renametable_sql; altertable_sql; droppartition_sql; addconstraint_sql; distinct_sql; ignorenulls_sql; respectnulls_sql; intdiv_sql; dpipe_sql; div_sql; distance_sql; dot_sql; eq_sql; escape_sql; glob_sql; gt_sql; gte_sql; ilike_sql; is_sql; like_sql; similarto_sql; lt_sql; lte_sql; mod_sql; mul_sql; neq_sql; nullsafeeq_sql; nullsafeneq_sql; or_sql; slice_sql; sub_sql; trycast_sql; use_sql; binary; function_fallback_sql; format_args; text_width; format_time; expressions; op_expressions; naked_property; set_operation; tag_sql; token_sql; userdefinedfunction_sql; userdefinedfunctionkwarg_sql; joinhint_sql; kwarg_sql; when_sql; merge_sql