sqlglot.dialects.presto
1from __future__ import annotations 2 3import typing as t 4 5from sqlglot import exp, generator, parser, tokens, transforms 6from sqlglot.dialects.dialect import ( 7 Dialect, 8 NormalizationStrategy, 9 binary_from_function, 10 bool_xor_sql, 11 date_trunc_to_time, 12 datestrtodate_sql, 13 encode_decode_sql, 14 build_formatted_time, 15 if_sql, 16 left_to_substring_sql, 17 no_ilike_sql, 18 no_pivot_sql, 19 no_safe_divide_sql, 20 no_timestamp_sql, 21 regexp_extract_sql, 22 rename_func, 23 right_to_substring_sql, 24 sha256_sql, 25 struct_extract_sql, 26 str_position_sql, 27 timestamptrunc_sql, 28 timestrtotime_sql, 29 ts_or_ds_add_cast, 30 unit_to_str, 31 sequence_sql, 32 build_regexp_extract, 33) 34from sqlglot.dialects.hive import Hive 35from sqlglot.dialects.mysql import MySQL 36from sqlglot.helper import apply_index_offset, seq_get 37from sqlglot.tokens import TokenType 38from sqlglot.transforms import unqualify_columns 39 40DATE_ADD_OR_SUB = t.Union[exp.DateAdd, exp.TimestampAdd, exp.DateSub] 41 42 43def _explode_to_unnest_sql(self: Presto.Generator, expression: exp.Lateral) -> str: 44 if isinstance(expression.this, exp.Explode): 45 return self.sql( 46 exp.Join( 47 this=exp.Unnest( 48 expressions=[expression.this.this], 49 alias=expression.args.get("alias"), 50 offset=isinstance(expression.this, exp.Posexplode), 51 ), 52 kind="cross", 53 ) 54 ) 55 return self.lateral_sql(expression) 56 57 58def _initcap_sql(self: Presto.Generator, expression: exp.Initcap) -> str: 59 regex = r"(\w)(\w*)" 60 return f"REGEXP_REPLACE({self.sql(expression, 'this')}, '{regex}', x -> UPPER(x[1]) || LOWER(x[2]))" 61 62 63def _no_sort_array(self: Presto.Generator, expression: exp.SortArray) -> str: 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 return self.func("ARRAY_SORT", expression.this, comparator) 69 70 71def _schema_sql(self: Presto.Generator, expression: exp.Schema) -> str: 72 if isinstance(expression.parent, exp.Property): 73 columns = ", ".join(f"'{c.name}'" for c in expression.expressions) 74 return f"ARRAY[{columns}]" 75 76 if expression.parent: 77 for schema in expression.parent.find_all(exp.Schema): 78 column_defs = schema.find_all(exp.ColumnDef) 79 if column_defs and isinstance(schema.parent, exp.Property): 80 expression.expressions.extend(column_defs) 81 82 return self.schema_sql(expression) 83 84 85def _quantile_sql(self: Presto.Generator, expression: exp.Quantile) -> str: 86 self.unsupported("Presto does not support exact quantiles") 87 return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile")) 88 89 90def _str_to_time_sql( 91 self: Presto.Generator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate 92) -> str: 93 return self.func("DATE_PARSE", expression.this, self.format_time(expression)) 94 95 96def _ts_or_ds_to_date_sql(self: Presto.Generator, expression: exp.TsOrDsToDate) -> str: 97 time_format = self.format_time(expression) 98 if time_format and time_format not in (Presto.TIME_FORMAT, Presto.DATE_FORMAT): 99 return self.sql(exp.cast(_str_to_time_sql(self, expression), exp.DataType.Type.DATE)) 100 return self.sql( 101 exp.cast(exp.cast(expression.this, exp.DataType.Type.TIMESTAMP), exp.DataType.Type.DATE) 102 ) 103 104 105def _ts_or_ds_add_sql(self: Presto.Generator, expression: exp.TsOrDsAdd) -> str: 106 expression = ts_or_ds_add_cast(expression) 107 unit = unit_to_str(expression) 108 return self.func("DATE_ADD", unit, expression.expression, expression.this) 109 110 111def _ts_or_ds_diff_sql(self: Presto.Generator, expression: exp.TsOrDsDiff) -> str: 112 this = exp.cast(expression.this, exp.DataType.Type.TIMESTAMP) 113 expr = exp.cast(expression.expression, exp.DataType.Type.TIMESTAMP) 114 unit = unit_to_str(expression) 115 return self.func("DATE_DIFF", unit, expr, this) 116 117 118def _build_approx_percentile(args: t.List) -> exp.Expression: 119 if len(args) == 4: 120 return exp.ApproxQuantile( 121 this=seq_get(args, 0), 122 weight=seq_get(args, 1), 123 quantile=seq_get(args, 2), 124 accuracy=seq_get(args, 3), 125 ) 126 if len(args) == 3: 127 return exp.ApproxQuantile( 128 this=seq_get(args, 0), quantile=seq_get(args, 1), accuracy=seq_get(args, 2) 129 ) 130 return exp.ApproxQuantile.from_arg_list(args) 131 132 133def _build_from_unixtime(args: t.List) -> exp.Expression: 134 if len(args) == 3: 135 return exp.UnixToTime( 136 this=seq_get(args, 0), 137 hours=seq_get(args, 1), 138 minutes=seq_get(args, 2), 139 ) 140 if len(args) == 2: 141 return exp.UnixToTime(this=seq_get(args, 0), zone=seq_get(args, 1)) 142 143 return exp.UnixToTime.from_arg_list(args) 144 145 146def _first_last_sql(self: Presto.Generator, expression: exp.Func) -> str: 147 """ 148 Trino doesn't support FIRST / LAST as functions, but they're valid in the context 149 of MATCH_RECOGNIZE, so we need to preserve them in that case. In all other cases 150 they're converted into an ARBITRARY call. 151 152 Reference: https://trino.io/docs/current/sql/match-recognize.html#logical-navigation-functions 153 """ 154 if isinstance(expression.find_ancestor(exp.MatchRecognize, exp.Select), exp.MatchRecognize): 155 return self.function_fallback_sql(expression) 156 157 return rename_func("ARBITRARY")(self, expression) 158 159 160def _unix_to_time_sql(self: Presto.Generator, expression: exp.UnixToTime) -> str: 161 scale = expression.args.get("scale") 162 timestamp = self.sql(expression, "this") 163 if scale in (None, exp.UnixToTime.SECONDS): 164 return rename_func("FROM_UNIXTIME")(self, expression) 165 166 return f"FROM_UNIXTIME(CAST({timestamp} AS DOUBLE) / POW(10, {scale}))" 167 168 169def _jsonextract_sql(self: Presto.Generator, expression: exp.JSONExtract) -> str: 170 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 171 172 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 173 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 174 if not expression.args.get("variant_extract") or is_json_extract: 175 return self.func( 176 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 177 ) 178 179 this = self.sql(expression, "this") 180 181 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 182 segments = [] 183 for path_key in expression.expression.expressions[1:]: 184 if not isinstance(path_key, exp.JSONPathKey): 185 # Cannot transpile subscripts, wildcards etc to dot notation 186 self.unsupported(f"Cannot transpile JSONPath segment '{path_key}' to ROW access") 187 continue 188 key = path_key.this 189 if not exp.SAFE_IDENTIFIER_RE.match(key): 190 key = f'"{key}"' 191 segments.append(f".{key}") 192 193 expr = "".join(segments) 194 195 return f"{this}{expr}" 196 197 198def _to_int(self: Presto.Generator, expression: exp.Expression) -> exp.Expression: 199 if not expression.type: 200 from sqlglot.optimizer.annotate_types import annotate_types 201 202 annotate_types(expression, dialect=self.dialect) 203 if expression.type and expression.type.this not in exp.DataType.INTEGER_TYPES: 204 return exp.cast(expression, to=exp.DataType.Type.BIGINT) 205 return expression 206 207 208def _build_to_char(args: t.List) -> exp.TimeToStr: 209 fmt = seq_get(args, 1) 210 if isinstance(fmt, exp.Literal): 211 # We uppercase this to match Teradata's format mapping keys 212 fmt.set("this", fmt.this.upper()) 213 214 # We use "teradata" on purpose here, because the time formats are different in Presto. 215 # See https://prestodb.io/docs/current/functions/teradata.html?highlight=to_char#to_char 216 return build_formatted_time(exp.TimeToStr, "teradata")(args) 217 218 219def _date_delta_sql( 220 name: str, negate_interval: bool = False 221) -> t.Callable[[Presto.Generator, DATE_ADD_OR_SUB], str]: 222 def _delta_sql(self: Presto.Generator, expression: DATE_ADD_OR_SUB) -> str: 223 interval = _to_int(self, expression.expression) 224 return self.func( 225 name, 226 unit_to_str(expression), 227 interval * (-1) if negate_interval else interval, 228 expression.this, 229 ) 230 231 return _delta_sql 232 233 234class Presto(Dialect): 235 INDEX_OFFSET = 1 236 NULL_ORDERING = "nulls_are_last" 237 TIME_FORMAT = MySQL.TIME_FORMAT 238 STRICT_STRING_CONCAT = True 239 SUPPORTS_SEMI_ANTI_JOIN = False 240 TYPED_DIVISION = True 241 TABLESAMPLE_SIZE_IS_PERCENT = True 242 LOG_BASE_FIRST: t.Optional[bool] = None 243 244 TIME_MAPPING = MySQL.TIME_MAPPING 245 246 # https://github.com/trinodb/trino/issues/17 247 # https://github.com/trinodb/trino/issues/12289 248 # https://github.com/prestodb/presto/issues/2863 249 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 250 251 # The result of certain math functions in Presto/Trino is of type 252 # equal to the input type e.g: FLOOR(5.5/2) -> DECIMAL, FLOOR(5/2) -> BIGINT 253 ANNOTATORS = { 254 **Dialect.ANNOTATORS, 255 exp.Floor: lambda self, e: self._annotate_by_args(e, "this"), 256 exp.Ceil: lambda self, e: self._annotate_by_args(e, "this"), 257 exp.Mod: lambda self, e: self._annotate_by_args(e, "this", "expression"), 258 exp.Round: lambda self, e: self._annotate_by_args(e, "this"), 259 exp.Sign: lambda self, e: self._annotate_by_args(e, "this"), 260 exp.Abs: lambda self, e: self._annotate_by_args(e, "this"), 261 exp.Rand: lambda self, e: self._annotate_by_args(e, "this") 262 if e.this 263 else self._set_type(e, exp.DataType.Type.DOUBLE), 264 } 265 266 class Tokenizer(tokens.Tokenizer): 267 UNICODE_STRINGS = [ 268 (prefix + q, q) 269 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 270 for prefix in ("U&", "u&") 271 ] 272 273 KEYWORDS = { 274 **tokens.Tokenizer.KEYWORDS, 275 "START": TokenType.BEGIN, 276 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 277 "ROW": TokenType.STRUCT, 278 "IPADDRESS": TokenType.IPADDRESS, 279 "IPPREFIX": TokenType.IPPREFIX, 280 "TDIGEST": TokenType.TDIGEST, 281 "HYPERLOGLOG": TokenType.HLLSKETCH, 282 } 283 KEYWORDS.pop("/*+") 284 KEYWORDS.pop("QUALIFY") 285 286 class Parser(parser.Parser): 287 VALUES_FOLLOWED_BY_PAREN = False 288 289 FUNCTIONS = { 290 **parser.Parser.FUNCTIONS, 291 "ARBITRARY": exp.AnyValue.from_arg_list, 292 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 293 "APPROX_PERCENTILE": _build_approx_percentile, 294 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 295 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 296 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 297 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 298 "CARDINALITY": exp.ArraySize.from_arg_list, 299 "CONTAINS": exp.ArrayContains.from_arg_list, 300 "DATE_ADD": lambda args: exp.DateAdd( 301 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 302 ), 303 "DATE_DIFF": lambda args: exp.DateDiff( 304 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 305 ), 306 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 307 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 308 "DATE_TRUNC": date_trunc_to_time, 309 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 310 "ELEMENT_AT": lambda args: exp.Bracket( 311 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 312 ), 313 "FROM_HEX": exp.Unhex.from_arg_list, 314 "FROM_UNIXTIME": _build_from_unixtime, 315 "FROM_UTF8": lambda args: exp.Decode( 316 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 317 ), 318 "NOW": exp.CurrentTimestamp.from_arg_list, 319 "REGEXP_EXTRACT": build_regexp_extract, 320 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 321 this=seq_get(args, 0), 322 expression=seq_get(args, 1), 323 replacement=seq_get(args, 2) or exp.Literal.string(""), 324 ), 325 "ROW": exp.Struct.from_arg_list, 326 "SEQUENCE": exp.GenerateSeries.from_arg_list, 327 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 328 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 329 "STRPOS": lambda args: exp.StrPosition( 330 this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2) 331 ), 332 "TO_CHAR": _build_to_char, 333 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 334 "TO_UTF8": lambda args: exp.Encode( 335 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 336 ), 337 "MD5": exp.MD5Digest.from_arg_list, 338 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 339 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 340 } 341 342 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 343 FUNCTION_PARSERS.pop("TRIM") 344 345 class Generator(generator.Generator): 346 INTERVAL_ALLOWS_PLURAL_FORM = False 347 JOIN_HINTS = False 348 TABLE_HINTS = False 349 QUERY_HINTS = False 350 IS_BOOL_ALLOWED = False 351 TZ_TO_WITH_TIME_ZONE = True 352 NVL2_SUPPORTED = False 353 STRUCT_DELIMITER = ("(", ")") 354 LIMIT_ONLY_LITERALS = True 355 SUPPORTS_SINGLE_ARG_CONCAT = False 356 LIKE_PROPERTY_INSIDE_SCHEMA = True 357 MULTI_ARG_DISTINCT = False 358 SUPPORTS_TO_NUMBER = False 359 HEX_FUNC = "TO_HEX" 360 PARSE_JSON_NAME = "JSON_PARSE" 361 PAD_FILL_PATTERN_IS_REQUIRED = True 362 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 363 364 PROPERTIES_LOCATION = { 365 **generator.Generator.PROPERTIES_LOCATION, 366 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 367 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 368 } 369 370 TYPE_MAPPING = { 371 **generator.Generator.TYPE_MAPPING, 372 exp.DataType.Type.INT: "INTEGER", 373 exp.DataType.Type.FLOAT: "REAL", 374 exp.DataType.Type.BINARY: "VARBINARY", 375 exp.DataType.Type.TEXT: "VARCHAR", 376 exp.DataType.Type.TIMETZ: "TIME", 377 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 378 exp.DataType.Type.STRUCT: "ROW", 379 exp.DataType.Type.DATETIME: "TIMESTAMP", 380 exp.DataType.Type.DATETIME64: "TIMESTAMP", 381 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 382 } 383 384 TRANSFORMS = { 385 **generator.Generator.TRANSFORMS, 386 exp.AnyValue: rename_func("ARBITRARY"), 387 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 388 exp.ArgMax: rename_func("MAX_BY"), 389 exp.ArgMin: rename_func("MIN_BY"), 390 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 391 exp.ArrayAny: rename_func("ANY_MATCH"), 392 exp.ArrayConcat: rename_func("CONCAT"), 393 exp.ArrayContains: rename_func("CONTAINS"), 394 exp.ArraySize: rename_func("CARDINALITY"), 395 exp.ArrayToString: rename_func("ARRAY_JOIN"), 396 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 397 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 398 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 399 exp.BitwiseLeftShift: lambda self, e: self.func( 400 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 401 ), 402 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 403 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 404 exp.BitwiseRightShift: lambda self, e: self.func( 405 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 406 ), 407 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 408 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 409 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 410 exp.DateAdd: _date_delta_sql("DATE_ADD"), 411 exp.DateDiff: lambda self, e: self.func( 412 "DATE_DIFF", unit_to_str(e), e.expression, e.this 413 ), 414 exp.DateStrToDate: datestrtodate_sql, 415 exp.DateToDi: lambda self, 416 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 417 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 418 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 419 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 420 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 421 exp.DiToDate: lambda self, 422 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 423 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 424 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 425 exp.First: _first_last_sql, 426 exp.FirstValue: _first_last_sql, 427 exp.FromTimeZone: lambda self, 428 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 429 exp.GenerateSeries: sequence_sql, 430 exp.GenerateDateArray: sequence_sql, 431 exp.Group: transforms.preprocess([transforms.unalias_group]), 432 exp.GroupConcat: lambda self, e: self.func( 433 "ARRAY_JOIN", self.func("ARRAY_AGG", e.this), e.args.get("separator") 434 ), 435 exp.If: if_sql(), 436 exp.ILike: no_ilike_sql, 437 exp.Initcap: _initcap_sql, 438 exp.JSONExtract: _jsonextract_sql, 439 exp.Last: _first_last_sql, 440 exp.LastValue: _first_last_sql, 441 exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this), 442 exp.Lateral: _explode_to_unnest_sql, 443 exp.Left: left_to_substring_sql, 444 exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"), 445 exp.LogicalAnd: rename_func("BOOL_AND"), 446 exp.LogicalOr: rename_func("BOOL_OR"), 447 exp.Pivot: no_pivot_sql, 448 exp.Quantile: _quantile_sql, 449 exp.RegexpExtract: regexp_extract_sql, 450 exp.Right: right_to_substring_sql, 451 exp.SafeDivide: no_safe_divide_sql, 452 exp.Schema: _schema_sql, 453 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 454 exp.Select: transforms.preprocess( 455 [ 456 transforms.eliminate_qualify, 457 transforms.eliminate_distinct_on, 458 transforms.explode_to_unnest(1), 459 transforms.eliminate_semi_and_anti_joins, 460 ] 461 ), 462 exp.SortArray: _no_sort_array, 463 exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True), 464 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 465 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 466 exp.StrToTime: _str_to_time_sql, 467 exp.StructExtract: struct_extract_sql, 468 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 469 exp.Timestamp: no_timestamp_sql, 470 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 471 exp.TimestampTrunc: timestamptrunc_sql(), 472 exp.TimeStrToDate: timestrtotime_sql, 473 exp.TimeStrToTime: timestrtotime_sql, 474 exp.TimeStrToUnix: lambda self, e: self.func( 475 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 476 ), 477 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 478 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 479 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 480 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 481 exp.TsOrDiToDi: lambda self, 482 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 483 exp.TsOrDsAdd: _ts_or_ds_add_sql, 484 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 485 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 486 exp.Unhex: rename_func("FROM_HEX"), 487 exp.UnixToStr: lambda self, 488 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 489 exp.UnixToTime: _unix_to_time_sql, 490 exp.UnixToTimeStr: lambda self, 491 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 492 exp.VariancePop: rename_func("VAR_POP"), 493 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 494 exp.WithinGroup: transforms.preprocess( 495 [transforms.remove_within_group_for_percentiles] 496 ), 497 exp.Xor: bool_xor_sql, 498 exp.MD5Digest: rename_func("MD5"), 499 exp.SHA: rename_func("SHA1"), 500 exp.SHA2: sha256_sql, 501 } 502 503 RESERVED_KEYWORDS = { 504 "alter", 505 "and", 506 "as", 507 "between", 508 "by", 509 "case", 510 "cast", 511 "constraint", 512 "create", 513 "cross", 514 "current_time", 515 "current_timestamp", 516 "deallocate", 517 "delete", 518 "describe", 519 "distinct", 520 "drop", 521 "else", 522 "end", 523 "escape", 524 "except", 525 "execute", 526 "exists", 527 "extract", 528 "false", 529 "for", 530 "from", 531 "full", 532 "group", 533 "having", 534 "in", 535 "inner", 536 "insert", 537 "intersect", 538 "into", 539 "is", 540 "join", 541 "left", 542 "like", 543 "natural", 544 "not", 545 "null", 546 "on", 547 "or", 548 "order", 549 "outer", 550 "prepare", 551 "right", 552 "select", 553 "table", 554 "then", 555 "true", 556 "union", 557 "using", 558 "values", 559 "when", 560 "where", 561 "with", 562 } 563 564 def md5_sql(self, expression: exp.MD5) -> str: 565 this = expression.this 566 567 if not this.type: 568 from sqlglot.optimizer.annotate_types import annotate_types 569 570 this = annotate_types(this) 571 572 if this.is_type(*exp.DataType.TEXT_TYPES): 573 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 574 575 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 576 577 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 578 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 579 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 580 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 581 # which seems to be using the same time mapping as Hive, as per: 582 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 583 this = expression.this 584 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 585 value_as_timestamp = ( 586 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 587 ) 588 589 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 590 591 formatted_value = self.func( 592 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 593 ) 594 parse_with_tz = self.func( 595 "PARSE_DATETIME", 596 formatted_value, 597 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 598 ) 599 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 600 return self.func("TO_UNIXTIME", coalesced) 601 602 def bracket_sql(self, expression: exp.Bracket) -> str: 603 if expression.args.get("safe"): 604 return self.func( 605 "ELEMENT_AT", 606 expression.this, 607 seq_get( 608 apply_index_offset( 609 expression.this, 610 expression.expressions, 611 1 - expression.args.get("offset", 0), 612 ), 613 0, 614 ), 615 ) 616 return super().bracket_sql(expression) 617 618 def struct_sql(self, expression: exp.Struct) -> str: 619 from sqlglot.optimizer.annotate_types import annotate_types 620 621 expression = annotate_types(expression) 622 values: t.List[str] = [] 623 schema: t.List[str] = [] 624 unknown_type = False 625 626 for e in expression.expressions: 627 if isinstance(e, exp.PropertyEQ): 628 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 629 unknown_type = True 630 else: 631 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 632 values.append(self.sql(e, "expression")) 633 else: 634 values.append(self.sql(e)) 635 636 size = len(expression.expressions) 637 638 if not size or len(schema) != size: 639 if unknown_type: 640 self.unsupported( 641 "Cannot convert untyped key-value definitions (try annotate_types)." 642 ) 643 return self.func("ROW", *values) 644 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 645 646 def interval_sql(self, expression: exp.Interval) -> str: 647 if expression.this and expression.text("unit").upper().startswith("WEEK"): 648 return f"({expression.this.name} * INTERVAL '7' DAY)" 649 return super().interval_sql(expression) 650 651 def transaction_sql(self, expression: exp.Transaction) -> str: 652 modes = expression.args.get("modes") 653 modes = f" {', '.join(modes)}" if modes else "" 654 return f"START TRANSACTION{modes}" 655 656 def offset_limit_modifiers( 657 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 658 ) -> t.List[str]: 659 return [ 660 self.sql(expression, "offset"), 661 self.sql(limit), 662 ] 663 664 def create_sql(self, expression: exp.Create) -> str: 665 """ 666 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 667 so we need to remove them 668 """ 669 kind = expression.args["kind"] 670 schema = expression.this 671 if kind == "VIEW" and schema.expressions: 672 expression.this.set("expressions", None) 673 return super().create_sql(expression) 674 675 def delete_sql(self, expression: exp.Delete) -> str: 676 """ 677 Presto only supports DELETE FROM for a single table without an alias, so we need 678 to remove the unnecessary parts. If the original DELETE statement contains more 679 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 680 """ 681 tables = expression.args.get("tables") or [expression.this] 682 if len(tables) > 1: 683 return super().delete_sql(expression) 684 685 table = tables[0] 686 expression.set("this", table) 687 expression.set("tables", None) 688 689 if isinstance(table, exp.Table): 690 table_alias = table.args.get("alias") 691 if table_alias: 692 table_alias.pop() 693 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 694 695 return super().delete_sql(expression)
235class Presto(Dialect): 236 INDEX_OFFSET = 1 237 NULL_ORDERING = "nulls_are_last" 238 TIME_FORMAT = MySQL.TIME_FORMAT 239 STRICT_STRING_CONCAT = True 240 SUPPORTS_SEMI_ANTI_JOIN = False 241 TYPED_DIVISION = True 242 TABLESAMPLE_SIZE_IS_PERCENT = True 243 LOG_BASE_FIRST: t.Optional[bool] = None 244 245 TIME_MAPPING = MySQL.TIME_MAPPING 246 247 # https://github.com/trinodb/trino/issues/17 248 # https://github.com/trinodb/trino/issues/12289 249 # https://github.com/prestodb/presto/issues/2863 250 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 251 252 # The result of certain math functions in Presto/Trino is of type 253 # equal to the input type e.g: FLOOR(5.5/2) -> DECIMAL, FLOOR(5/2) -> BIGINT 254 ANNOTATORS = { 255 **Dialect.ANNOTATORS, 256 exp.Floor: lambda self, e: self._annotate_by_args(e, "this"), 257 exp.Ceil: lambda self, e: self._annotate_by_args(e, "this"), 258 exp.Mod: lambda self, e: self._annotate_by_args(e, "this", "expression"), 259 exp.Round: lambda self, e: self._annotate_by_args(e, "this"), 260 exp.Sign: lambda self, e: self._annotate_by_args(e, "this"), 261 exp.Abs: lambda self, e: self._annotate_by_args(e, "this"), 262 exp.Rand: lambda self, e: self._annotate_by_args(e, "this") 263 if e.this 264 else self._set_type(e, exp.DataType.Type.DOUBLE), 265 } 266 267 class Tokenizer(tokens.Tokenizer): 268 UNICODE_STRINGS = [ 269 (prefix + q, q) 270 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 271 for prefix in ("U&", "u&") 272 ] 273 274 KEYWORDS = { 275 **tokens.Tokenizer.KEYWORDS, 276 "START": TokenType.BEGIN, 277 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 278 "ROW": TokenType.STRUCT, 279 "IPADDRESS": TokenType.IPADDRESS, 280 "IPPREFIX": TokenType.IPPREFIX, 281 "TDIGEST": TokenType.TDIGEST, 282 "HYPERLOGLOG": TokenType.HLLSKETCH, 283 } 284 KEYWORDS.pop("/*+") 285 KEYWORDS.pop("QUALIFY") 286 287 class Parser(parser.Parser): 288 VALUES_FOLLOWED_BY_PAREN = False 289 290 FUNCTIONS = { 291 **parser.Parser.FUNCTIONS, 292 "ARBITRARY": exp.AnyValue.from_arg_list, 293 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 294 "APPROX_PERCENTILE": _build_approx_percentile, 295 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 296 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 297 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 298 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 299 "CARDINALITY": exp.ArraySize.from_arg_list, 300 "CONTAINS": exp.ArrayContains.from_arg_list, 301 "DATE_ADD": lambda args: exp.DateAdd( 302 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 303 ), 304 "DATE_DIFF": lambda args: exp.DateDiff( 305 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 306 ), 307 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 308 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 309 "DATE_TRUNC": date_trunc_to_time, 310 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 311 "ELEMENT_AT": lambda args: exp.Bracket( 312 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 313 ), 314 "FROM_HEX": exp.Unhex.from_arg_list, 315 "FROM_UNIXTIME": _build_from_unixtime, 316 "FROM_UTF8": lambda args: exp.Decode( 317 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 318 ), 319 "NOW": exp.CurrentTimestamp.from_arg_list, 320 "REGEXP_EXTRACT": build_regexp_extract, 321 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 322 this=seq_get(args, 0), 323 expression=seq_get(args, 1), 324 replacement=seq_get(args, 2) or exp.Literal.string(""), 325 ), 326 "ROW": exp.Struct.from_arg_list, 327 "SEQUENCE": exp.GenerateSeries.from_arg_list, 328 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 329 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 330 "STRPOS": lambda args: exp.StrPosition( 331 this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2) 332 ), 333 "TO_CHAR": _build_to_char, 334 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 335 "TO_UTF8": lambda args: exp.Encode( 336 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 337 ), 338 "MD5": exp.MD5Digest.from_arg_list, 339 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 340 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 341 } 342 343 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 344 FUNCTION_PARSERS.pop("TRIM") 345 346 class Generator(generator.Generator): 347 INTERVAL_ALLOWS_PLURAL_FORM = False 348 JOIN_HINTS = False 349 TABLE_HINTS = False 350 QUERY_HINTS = False 351 IS_BOOL_ALLOWED = False 352 TZ_TO_WITH_TIME_ZONE = True 353 NVL2_SUPPORTED = False 354 STRUCT_DELIMITER = ("(", ")") 355 LIMIT_ONLY_LITERALS = True 356 SUPPORTS_SINGLE_ARG_CONCAT = False 357 LIKE_PROPERTY_INSIDE_SCHEMA = True 358 MULTI_ARG_DISTINCT = False 359 SUPPORTS_TO_NUMBER = False 360 HEX_FUNC = "TO_HEX" 361 PARSE_JSON_NAME = "JSON_PARSE" 362 PAD_FILL_PATTERN_IS_REQUIRED = True 363 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 364 365 PROPERTIES_LOCATION = { 366 **generator.Generator.PROPERTIES_LOCATION, 367 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 368 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 369 } 370 371 TYPE_MAPPING = { 372 **generator.Generator.TYPE_MAPPING, 373 exp.DataType.Type.INT: "INTEGER", 374 exp.DataType.Type.FLOAT: "REAL", 375 exp.DataType.Type.BINARY: "VARBINARY", 376 exp.DataType.Type.TEXT: "VARCHAR", 377 exp.DataType.Type.TIMETZ: "TIME", 378 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 379 exp.DataType.Type.STRUCT: "ROW", 380 exp.DataType.Type.DATETIME: "TIMESTAMP", 381 exp.DataType.Type.DATETIME64: "TIMESTAMP", 382 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 383 } 384 385 TRANSFORMS = { 386 **generator.Generator.TRANSFORMS, 387 exp.AnyValue: rename_func("ARBITRARY"), 388 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 389 exp.ArgMax: rename_func("MAX_BY"), 390 exp.ArgMin: rename_func("MIN_BY"), 391 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 392 exp.ArrayAny: rename_func("ANY_MATCH"), 393 exp.ArrayConcat: rename_func("CONCAT"), 394 exp.ArrayContains: rename_func("CONTAINS"), 395 exp.ArraySize: rename_func("CARDINALITY"), 396 exp.ArrayToString: rename_func("ARRAY_JOIN"), 397 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 398 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 399 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 400 exp.BitwiseLeftShift: lambda self, e: self.func( 401 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 402 ), 403 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 404 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 405 exp.BitwiseRightShift: lambda self, e: self.func( 406 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 407 ), 408 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 409 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 410 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 411 exp.DateAdd: _date_delta_sql("DATE_ADD"), 412 exp.DateDiff: lambda self, e: self.func( 413 "DATE_DIFF", unit_to_str(e), e.expression, e.this 414 ), 415 exp.DateStrToDate: datestrtodate_sql, 416 exp.DateToDi: lambda self, 417 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 418 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 419 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 420 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 421 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 422 exp.DiToDate: lambda self, 423 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 424 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 425 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 426 exp.First: _first_last_sql, 427 exp.FirstValue: _first_last_sql, 428 exp.FromTimeZone: lambda self, 429 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 430 exp.GenerateSeries: sequence_sql, 431 exp.GenerateDateArray: sequence_sql, 432 exp.Group: transforms.preprocess([transforms.unalias_group]), 433 exp.GroupConcat: lambda self, e: self.func( 434 "ARRAY_JOIN", self.func("ARRAY_AGG", e.this), e.args.get("separator") 435 ), 436 exp.If: if_sql(), 437 exp.ILike: no_ilike_sql, 438 exp.Initcap: _initcap_sql, 439 exp.JSONExtract: _jsonextract_sql, 440 exp.Last: _first_last_sql, 441 exp.LastValue: _first_last_sql, 442 exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this), 443 exp.Lateral: _explode_to_unnest_sql, 444 exp.Left: left_to_substring_sql, 445 exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"), 446 exp.LogicalAnd: rename_func("BOOL_AND"), 447 exp.LogicalOr: rename_func("BOOL_OR"), 448 exp.Pivot: no_pivot_sql, 449 exp.Quantile: _quantile_sql, 450 exp.RegexpExtract: regexp_extract_sql, 451 exp.Right: right_to_substring_sql, 452 exp.SafeDivide: no_safe_divide_sql, 453 exp.Schema: _schema_sql, 454 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 455 exp.Select: transforms.preprocess( 456 [ 457 transforms.eliminate_qualify, 458 transforms.eliminate_distinct_on, 459 transforms.explode_to_unnest(1), 460 transforms.eliminate_semi_and_anti_joins, 461 ] 462 ), 463 exp.SortArray: _no_sort_array, 464 exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True), 465 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 466 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 467 exp.StrToTime: _str_to_time_sql, 468 exp.StructExtract: struct_extract_sql, 469 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 470 exp.Timestamp: no_timestamp_sql, 471 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 472 exp.TimestampTrunc: timestamptrunc_sql(), 473 exp.TimeStrToDate: timestrtotime_sql, 474 exp.TimeStrToTime: timestrtotime_sql, 475 exp.TimeStrToUnix: lambda self, e: self.func( 476 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 477 ), 478 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 479 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 480 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 481 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 482 exp.TsOrDiToDi: lambda self, 483 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 484 exp.TsOrDsAdd: _ts_or_ds_add_sql, 485 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 486 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 487 exp.Unhex: rename_func("FROM_HEX"), 488 exp.UnixToStr: lambda self, 489 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 490 exp.UnixToTime: _unix_to_time_sql, 491 exp.UnixToTimeStr: lambda self, 492 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 493 exp.VariancePop: rename_func("VAR_POP"), 494 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 495 exp.WithinGroup: transforms.preprocess( 496 [transforms.remove_within_group_for_percentiles] 497 ), 498 exp.Xor: bool_xor_sql, 499 exp.MD5Digest: rename_func("MD5"), 500 exp.SHA: rename_func("SHA1"), 501 exp.SHA2: sha256_sql, 502 } 503 504 RESERVED_KEYWORDS = { 505 "alter", 506 "and", 507 "as", 508 "between", 509 "by", 510 "case", 511 "cast", 512 "constraint", 513 "create", 514 "cross", 515 "current_time", 516 "current_timestamp", 517 "deallocate", 518 "delete", 519 "describe", 520 "distinct", 521 "drop", 522 "else", 523 "end", 524 "escape", 525 "except", 526 "execute", 527 "exists", 528 "extract", 529 "false", 530 "for", 531 "from", 532 "full", 533 "group", 534 "having", 535 "in", 536 "inner", 537 "insert", 538 "intersect", 539 "into", 540 "is", 541 "join", 542 "left", 543 "like", 544 "natural", 545 "not", 546 "null", 547 "on", 548 "or", 549 "order", 550 "outer", 551 "prepare", 552 "right", 553 "select", 554 "table", 555 "then", 556 "true", 557 "union", 558 "using", 559 "values", 560 "when", 561 "where", 562 "with", 563 } 564 565 def md5_sql(self, expression: exp.MD5) -> str: 566 this = expression.this 567 568 if not this.type: 569 from sqlglot.optimizer.annotate_types import annotate_types 570 571 this = annotate_types(this) 572 573 if this.is_type(*exp.DataType.TEXT_TYPES): 574 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 575 576 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 577 578 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 579 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 580 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 581 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 582 # which seems to be using the same time mapping as Hive, as per: 583 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 584 this = expression.this 585 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 586 value_as_timestamp = ( 587 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 588 ) 589 590 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 591 592 formatted_value = self.func( 593 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 594 ) 595 parse_with_tz = self.func( 596 "PARSE_DATETIME", 597 formatted_value, 598 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 599 ) 600 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 601 return self.func("TO_UNIXTIME", coalesced) 602 603 def bracket_sql(self, expression: exp.Bracket) -> str: 604 if expression.args.get("safe"): 605 return self.func( 606 "ELEMENT_AT", 607 expression.this, 608 seq_get( 609 apply_index_offset( 610 expression.this, 611 expression.expressions, 612 1 - expression.args.get("offset", 0), 613 ), 614 0, 615 ), 616 ) 617 return super().bracket_sql(expression) 618 619 def struct_sql(self, expression: exp.Struct) -> str: 620 from sqlglot.optimizer.annotate_types import annotate_types 621 622 expression = annotate_types(expression) 623 values: t.List[str] = [] 624 schema: t.List[str] = [] 625 unknown_type = False 626 627 for e in expression.expressions: 628 if isinstance(e, exp.PropertyEQ): 629 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 630 unknown_type = True 631 else: 632 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 633 values.append(self.sql(e, "expression")) 634 else: 635 values.append(self.sql(e)) 636 637 size = len(expression.expressions) 638 639 if not size or len(schema) != size: 640 if unknown_type: 641 self.unsupported( 642 "Cannot convert untyped key-value definitions (try annotate_types)." 643 ) 644 return self.func("ROW", *values) 645 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 646 647 def interval_sql(self, expression: exp.Interval) -> str: 648 if expression.this and expression.text("unit").upper().startswith("WEEK"): 649 return f"({expression.this.name} * INTERVAL '7' DAY)" 650 return super().interval_sql(expression) 651 652 def transaction_sql(self, expression: exp.Transaction) -> str: 653 modes = expression.args.get("modes") 654 modes = f" {', '.join(modes)}" if modes else "" 655 return f"START TRANSACTION{modes}" 656 657 def offset_limit_modifiers( 658 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 659 ) -> t.List[str]: 660 return [ 661 self.sql(expression, "offset"), 662 self.sql(limit), 663 ] 664 665 def create_sql(self, expression: exp.Create) -> str: 666 """ 667 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 668 so we need to remove them 669 """ 670 kind = expression.args["kind"] 671 schema = expression.this 672 if kind == "VIEW" and schema.expressions: 673 expression.this.set("expressions", None) 674 return super().create_sql(expression) 675 676 def delete_sql(self, expression: exp.Delete) -> str: 677 """ 678 Presto only supports DELETE FROM for a single table without an alias, so we need 679 to remove the unnecessary parts. If the original DELETE statement contains more 680 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 681 """ 682 tables = expression.args.get("tables") or [expression.this] 683 if len(tables) > 1: 684 return super().delete_sql(expression) 685 686 table = tables[0] 687 expression.set("this", table) 688 expression.set("tables", None) 689 690 if isinstance(table, exp.Table): 691 table_alias = table.args.get("alias") 692 if table_alias: 693 table_alias.pop() 694 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 695 696 return super().delete_sql(expression)
Default NULL
ordering method to use if not explicitly set.
Possible values: "nulls_are_small"
, "nulls_are_large"
, "nulls_are_last"
Whether the behavior of a / b
depends on the types of a
and b
.
False means a / b
is always float division.
True means a / b
is integer division if both a
and b
are integers.
Whether the base comes first in the LOG
function.
Possible values: True
, False
, None
(two arguments are not supported by LOG
)
Associates this dialect's time formats with their equivalent Python strftime
formats.
Specifies the strategy according to which identifiers should be normalized.
Inherited Members
- sqlglot.dialects.dialect.Dialect
- Dialect
- WEEK_OFFSET
- UNNEST_COLUMN_ONLY
- ALIAS_POST_TABLESAMPLE
- IDENTIFIERS_CAN_START_WITH_DIGIT
- DPIPE_IS_STRING_CONCAT
- SUPPORTS_USER_DEFINED_TYPES
- COPY_PARAMS_ARE_CSV
- NORMALIZE_FUNCTIONS
- SAFE_DIVISION
- CONCAT_COALESCE
- HEX_LOWERCASE
- DATE_FORMAT
- DATEINT_FORMAT
- FORMAT_MAPPING
- UNESCAPED_SEQUENCES
- PSEUDOCOLUMNS
- PREFER_CTE_ALIAS_COLUMN
- FORCE_EARLY_ALIAS_REF_EXPANSION
- EXPAND_ALIAS_REFS_EARLY_ONLY_IN_GROUP_BY
- SUPPORTS_ORDER_BY_ALL
- HAS_DISTINCT_ARRAY_CONSTRUCTORS
- SUPPORTS_FIXED_SIZE_ARRAYS
- STRICT_JSON_PATH_SYNTAX
- ON_CONDITION_EMPTY_BEFORE_ERROR
- ARRAY_AGG_INCLUDES_NULLS
- REGEXP_EXTRACT_DEFAULT_GROUP
- SET_OP_DISTINCT_BY_DEFAULT
- CREATABLE_KIND_MAPPING
- DATE_PART_MAPPING
- TYPE_TO_EXPRESSIONS
- get_or_raise
- format_time
- settings
- normalize_identifier
- case_sensitive
- can_identify
- quote_identifier
- to_json_path
- parse
- parse_into
- generate
- transpile
- tokenize
- tokenizer
- jsonpath_tokenizer
- parser
- generator
267 class Tokenizer(tokens.Tokenizer): 268 UNICODE_STRINGS = [ 269 (prefix + q, q) 270 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 271 for prefix in ("U&", "u&") 272 ] 273 274 KEYWORDS = { 275 **tokens.Tokenizer.KEYWORDS, 276 "START": TokenType.BEGIN, 277 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 278 "ROW": TokenType.STRUCT, 279 "IPADDRESS": TokenType.IPADDRESS, 280 "IPPREFIX": TokenType.IPPREFIX, 281 "TDIGEST": TokenType.TDIGEST, 282 "HYPERLOGLOG": TokenType.HLLSKETCH, 283 } 284 KEYWORDS.pop("/*+") 285 KEYWORDS.pop("QUALIFY")
Inherited Members
- sqlglot.tokens.Tokenizer
- Tokenizer
- SINGLE_TOKENS
- BIT_STRINGS
- BYTE_STRINGS
- HEX_STRINGS
- RAW_STRINGS
- HEREDOC_STRINGS
- IDENTIFIERS
- IDENTIFIER_ESCAPES
- QUOTES
- STRING_ESCAPES
- VAR_SINGLE_TOKENS
- HEREDOC_TAG_IS_IDENTIFIER
- HEREDOC_STRING_ALTERNATIVE
- STRING_ESCAPES_ALLOWED_IN_RAW_STRINGS
- NESTED_COMMENTS
- WHITE_SPACE
- COMMANDS
- COMMAND_PREFIX_TOKENS
- NUMERIC_LITERALS
- COMMENTS
- dialect
- reset
- tokenize
- tokenize_rs
- size
- sql
- tokens
287 class Parser(parser.Parser): 288 VALUES_FOLLOWED_BY_PAREN = False 289 290 FUNCTIONS = { 291 **parser.Parser.FUNCTIONS, 292 "ARBITRARY": exp.AnyValue.from_arg_list, 293 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 294 "APPROX_PERCENTILE": _build_approx_percentile, 295 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 296 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 297 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 298 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 299 "CARDINALITY": exp.ArraySize.from_arg_list, 300 "CONTAINS": exp.ArrayContains.from_arg_list, 301 "DATE_ADD": lambda args: exp.DateAdd( 302 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 303 ), 304 "DATE_DIFF": lambda args: exp.DateDiff( 305 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 306 ), 307 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 308 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 309 "DATE_TRUNC": date_trunc_to_time, 310 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 311 "ELEMENT_AT": lambda args: exp.Bracket( 312 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 313 ), 314 "FROM_HEX": exp.Unhex.from_arg_list, 315 "FROM_UNIXTIME": _build_from_unixtime, 316 "FROM_UTF8": lambda args: exp.Decode( 317 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 318 ), 319 "NOW": exp.CurrentTimestamp.from_arg_list, 320 "REGEXP_EXTRACT": build_regexp_extract, 321 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 322 this=seq_get(args, 0), 323 expression=seq_get(args, 1), 324 replacement=seq_get(args, 2) or exp.Literal.string(""), 325 ), 326 "ROW": exp.Struct.from_arg_list, 327 "SEQUENCE": exp.GenerateSeries.from_arg_list, 328 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 329 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 330 "STRPOS": lambda args: exp.StrPosition( 331 this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2) 332 ), 333 "TO_CHAR": _build_to_char, 334 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 335 "TO_UTF8": lambda args: exp.Encode( 336 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 337 ), 338 "MD5": exp.MD5Digest.from_arg_list, 339 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 340 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 341 } 342 343 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 344 FUNCTION_PARSERS.pop("TRIM")
Parser consumes a list of tokens produced by the Tokenizer and produces a parsed syntax tree.
Arguments:
- error_level: The desired error level. Default: ErrorLevel.IMMEDIATE
- error_message_context: The amount of context to capture from a query string when displaying the error message (in number of characters). Default: 100
- 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
Inherited Members
- sqlglot.parser.Parser
- Parser
- NO_PAREN_FUNCTIONS
- STRUCT_TYPE_TOKENS
- NESTED_TYPE_TOKENS
- ENUM_TYPE_TOKENS
- AGGREGATE_TYPE_TOKENS
- TYPE_TOKENS
- SIGNED_TO_UNSIGNED_TYPE_TOKEN
- SUBQUERY_PREDICATES
- RESERVED_TOKENS
- DB_CREATABLES
- CREATABLES
- ALTERABLES
- INTERVAL_VARS
- ALIAS_TOKENS
- ARRAY_CONSTRUCTORS
- COMMENT_TABLE_ALIAS_TOKENS
- UPDATE_ALIAS_TOKENS
- TRIM_TYPES
- FUNC_TOKENS
- CONJUNCTION
- ASSIGNMENT
- DISJUNCTION
- EQUALITY
- COMPARISON
- BITWISE
- TERM
- FACTOR
- EXPONENT
- TIMES
- TIMESTAMPS
- SET_OPERATIONS
- JOIN_METHODS
- JOIN_SIDES
- JOIN_KINDS
- JOIN_HINTS
- LAMBDAS
- COLUMN_OPERATORS
- EXPRESSION_PARSERS
- STATEMENT_PARSERS
- UNARY_PARSERS
- STRING_PARSERS
- NUMERIC_PARSERS
- PRIMARY_PARSERS
- PLACEHOLDER_PARSERS
- RANGE_PARSERS
- PROPERTY_PARSERS
- CONSTRAINT_PARSERS
- ALTER_PARSERS
- ALTER_ALTER_PARSERS
- SCHEMA_UNNAMED_CONSTRAINTS
- NO_PAREN_FUNCTION_PARSERS
- INVALID_FUNC_NAME_TOKENS
- FUNCTIONS_WITH_ALIASED_ARGS
- KEY_VALUE_DEFINITIONS
- QUERY_MODIFIER_PARSERS
- SET_PARSERS
- SHOW_PARSERS
- TYPE_LITERAL_PARSERS
- TYPE_CONVERTERS
- DDL_SELECT_TOKENS
- PRE_VOLATILE_TOKENS
- TRANSACTION_KIND
- TRANSACTION_CHARACTERISTICS
- CONFLICT_ACTIONS
- CREATE_SEQUENCE
- ISOLATED_LOADING_OPTIONS
- USABLES
- CAST_ACTIONS
- SCHEMA_BINDING_OPTIONS
- KEY_CONSTRAINT_OPTIONS
- INSERT_ALTERNATIVES
- CLONE_KEYWORDS
- HISTORICAL_DATA_PREFIX
- HISTORICAL_DATA_KIND
- OPCLASS_FOLLOW_KEYWORDS
- OPTYPE_FOLLOW_TOKENS
- TABLE_INDEX_HINT_TOKENS
- VIEW_ATTRIBUTES
- WINDOW_ALIAS_TOKENS
- WINDOW_BEFORE_PAREN_TOKENS
- WINDOW_SIDES
- JSON_KEY_VALUE_SEPARATOR_TOKENS
- FETCH_TOKENS
- ADD_CONSTRAINT_TOKENS
- DISTINCT_TOKENS
- NULL_TOKENS
- UNNEST_OFFSET_ALIAS_TOKENS
- SELECT_START_TOKENS
- COPY_INTO_VARLEN_OPTIONS
- IS_JSON_PREDICATE_KIND
- ODBC_DATETIME_LITERALS
- ON_CONDITION_TOKENS
- STRICT_CAST
- PREFIXED_PIVOT_COLUMNS
- IDENTIFY_PIVOT_STRINGS
- LOG_DEFAULTS_TO_LN
- ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN
- TABLESAMPLE_CSV
- DEFAULT_SAMPLING_METHOD
- SET_REQUIRES_ASSIGNMENT_DELIMITER
- TRIM_PATTERN_FIRST
- STRING_ALIASES
- MODIFIERS_ATTACHED_TO_SET_OP
- SET_OP_MODIFIERS
- NO_PAREN_IF_COMMANDS
- JSON_ARROWS_REQUIRE_JSON_TYPE
- COLON_IS_VARIANT_EXTRACT
- SUPPORTS_IMPLICIT_UNNEST
- INTERVAL_SPANS
- SUPPORTS_PARTITION_SELECTION
- error_level
- error_message_context
- max_errors
- dialect
- reset
- parse
- parse_into
- check_errors
- raise_error
- expression
- validate_expression
- errors
- sql
346 class Generator(generator.Generator): 347 INTERVAL_ALLOWS_PLURAL_FORM = False 348 JOIN_HINTS = False 349 TABLE_HINTS = False 350 QUERY_HINTS = False 351 IS_BOOL_ALLOWED = False 352 TZ_TO_WITH_TIME_ZONE = True 353 NVL2_SUPPORTED = False 354 STRUCT_DELIMITER = ("(", ")") 355 LIMIT_ONLY_LITERALS = True 356 SUPPORTS_SINGLE_ARG_CONCAT = False 357 LIKE_PROPERTY_INSIDE_SCHEMA = True 358 MULTI_ARG_DISTINCT = False 359 SUPPORTS_TO_NUMBER = False 360 HEX_FUNC = "TO_HEX" 361 PARSE_JSON_NAME = "JSON_PARSE" 362 PAD_FILL_PATTERN_IS_REQUIRED = True 363 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 364 365 PROPERTIES_LOCATION = { 366 **generator.Generator.PROPERTIES_LOCATION, 367 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 368 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 369 } 370 371 TYPE_MAPPING = { 372 **generator.Generator.TYPE_MAPPING, 373 exp.DataType.Type.INT: "INTEGER", 374 exp.DataType.Type.FLOAT: "REAL", 375 exp.DataType.Type.BINARY: "VARBINARY", 376 exp.DataType.Type.TEXT: "VARCHAR", 377 exp.DataType.Type.TIMETZ: "TIME", 378 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 379 exp.DataType.Type.STRUCT: "ROW", 380 exp.DataType.Type.DATETIME: "TIMESTAMP", 381 exp.DataType.Type.DATETIME64: "TIMESTAMP", 382 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 383 } 384 385 TRANSFORMS = { 386 **generator.Generator.TRANSFORMS, 387 exp.AnyValue: rename_func("ARBITRARY"), 388 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 389 exp.ArgMax: rename_func("MAX_BY"), 390 exp.ArgMin: rename_func("MIN_BY"), 391 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 392 exp.ArrayAny: rename_func("ANY_MATCH"), 393 exp.ArrayConcat: rename_func("CONCAT"), 394 exp.ArrayContains: rename_func("CONTAINS"), 395 exp.ArraySize: rename_func("CARDINALITY"), 396 exp.ArrayToString: rename_func("ARRAY_JOIN"), 397 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 398 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 399 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 400 exp.BitwiseLeftShift: lambda self, e: self.func( 401 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 402 ), 403 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 404 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 405 exp.BitwiseRightShift: lambda self, e: self.func( 406 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 407 ), 408 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 409 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 410 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 411 exp.DateAdd: _date_delta_sql("DATE_ADD"), 412 exp.DateDiff: lambda self, e: self.func( 413 "DATE_DIFF", unit_to_str(e), e.expression, e.this 414 ), 415 exp.DateStrToDate: datestrtodate_sql, 416 exp.DateToDi: lambda self, 417 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 418 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 419 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 420 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 421 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 422 exp.DiToDate: lambda self, 423 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 424 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 425 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 426 exp.First: _first_last_sql, 427 exp.FirstValue: _first_last_sql, 428 exp.FromTimeZone: lambda self, 429 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 430 exp.GenerateSeries: sequence_sql, 431 exp.GenerateDateArray: sequence_sql, 432 exp.Group: transforms.preprocess([transforms.unalias_group]), 433 exp.GroupConcat: lambda self, e: self.func( 434 "ARRAY_JOIN", self.func("ARRAY_AGG", e.this), e.args.get("separator") 435 ), 436 exp.If: if_sql(), 437 exp.ILike: no_ilike_sql, 438 exp.Initcap: _initcap_sql, 439 exp.JSONExtract: _jsonextract_sql, 440 exp.Last: _first_last_sql, 441 exp.LastValue: _first_last_sql, 442 exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this), 443 exp.Lateral: _explode_to_unnest_sql, 444 exp.Left: left_to_substring_sql, 445 exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"), 446 exp.LogicalAnd: rename_func("BOOL_AND"), 447 exp.LogicalOr: rename_func("BOOL_OR"), 448 exp.Pivot: no_pivot_sql, 449 exp.Quantile: _quantile_sql, 450 exp.RegexpExtract: regexp_extract_sql, 451 exp.Right: right_to_substring_sql, 452 exp.SafeDivide: no_safe_divide_sql, 453 exp.Schema: _schema_sql, 454 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 455 exp.Select: transforms.preprocess( 456 [ 457 transforms.eliminate_qualify, 458 transforms.eliminate_distinct_on, 459 transforms.explode_to_unnest(1), 460 transforms.eliminate_semi_and_anti_joins, 461 ] 462 ), 463 exp.SortArray: _no_sort_array, 464 exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True), 465 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 466 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 467 exp.StrToTime: _str_to_time_sql, 468 exp.StructExtract: struct_extract_sql, 469 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 470 exp.Timestamp: no_timestamp_sql, 471 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 472 exp.TimestampTrunc: timestamptrunc_sql(), 473 exp.TimeStrToDate: timestrtotime_sql, 474 exp.TimeStrToTime: timestrtotime_sql, 475 exp.TimeStrToUnix: lambda self, e: self.func( 476 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 477 ), 478 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 479 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 480 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 481 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 482 exp.TsOrDiToDi: lambda self, 483 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 484 exp.TsOrDsAdd: _ts_or_ds_add_sql, 485 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 486 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 487 exp.Unhex: rename_func("FROM_HEX"), 488 exp.UnixToStr: lambda self, 489 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 490 exp.UnixToTime: _unix_to_time_sql, 491 exp.UnixToTimeStr: lambda self, 492 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 493 exp.VariancePop: rename_func("VAR_POP"), 494 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 495 exp.WithinGroup: transforms.preprocess( 496 [transforms.remove_within_group_for_percentiles] 497 ), 498 exp.Xor: bool_xor_sql, 499 exp.MD5Digest: rename_func("MD5"), 500 exp.SHA: rename_func("SHA1"), 501 exp.SHA2: sha256_sql, 502 } 503 504 RESERVED_KEYWORDS = { 505 "alter", 506 "and", 507 "as", 508 "between", 509 "by", 510 "case", 511 "cast", 512 "constraint", 513 "create", 514 "cross", 515 "current_time", 516 "current_timestamp", 517 "deallocate", 518 "delete", 519 "describe", 520 "distinct", 521 "drop", 522 "else", 523 "end", 524 "escape", 525 "except", 526 "execute", 527 "exists", 528 "extract", 529 "false", 530 "for", 531 "from", 532 "full", 533 "group", 534 "having", 535 "in", 536 "inner", 537 "insert", 538 "intersect", 539 "into", 540 "is", 541 "join", 542 "left", 543 "like", 544 "natural", 545 "not", 546 "null", 547 "on", 548 "or", 549 "order", 550 "outer", 551 "prepare", 552 "right", 553 "select", 554 "table", 555 "then", 556 "true", 557 "union", 558 "using", 559 "values", 560 "when", 561 "where", 562 "with", 563 } 564 565 def md5_sql(self, expression: exp.MD5) -> str: 566 this = expression.this 567 568 if not this.type: 569 from sqlglot.optimizer.annotate_types import annotate_types 570 571 this = annotate_types(this) 572 573 if this.is_type(*exp.DataType.TEXT_TYPES): 574 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 575 576 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 577 578 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 579 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 580 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 581 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 582 # which seems to be using the same time mapping as Hive, as per: 583 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 584 this = expression.this 585 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 586 value_as_timestamp = ( 587 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 588 ) 589 590 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 591 592 formatted_value = self.func( 593 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 594 ) 595 parse_with_tz = self.func( 596 "PARSE_DATETIME", 597 formatted_value, 598 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 599 ) 600 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 601 return self.func("TO_UNIXTIME", coalesced) 602 603 def bracket_sql(self, expression: exp.Bracket) -> str: 604 if expression.args.get("safe"): 605 return self.func( 606 "ELEMENT_AT", 607 expression.this, 608 seq_get( 609 apply_index_offset( 610 expression.this, 611 expression.expressions, 612 1 - expression.args.get("offset", 0), 613 ), 614 0, 615 ), 616 ) 617 return super().bracket_sql(expression) 618 619 def struct_sql(self, expression: exp.Struct) -> str: 620 from sqlglot.optimizer.annotate_types import annotate_types 621 622 expression = annotate_types(expression) 623 values: t.List[str] = [] 624 schema: t.List[str] = [] 625 unknown_type = False 626 627 for e in expression.expressions: 628 if isinstance(e, exp.PropertyEQ): 629 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 630 unknown_type = True 631 else: 632 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 633 values.append(self.sql(e, "expression")) 634 else: 635 values.append(self.sql(e)) 636 637 size = len(expression.expressions) 638 639 if not size or len(schema) != size: 640 if unknown_type: 641 self.unsupported( 642 "Cannot convert untyped key-value definitions (try annotate_types)." 643 ) 644 return self.func("ROW", *values) 645 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 646 647 def interval_sql(self, expression: exp.Interval) -> str: 648 if expression.this and expression.text("unit").upper().startswith("WEEK"): 649 return f"({expression.this.name} * INTERVAL '7' DAY)" 650 return super().interval_sql(expression) 651 652 def transaction_sql(self, expression: exp.Transaction) -> str: 653 modes = expression.args.get("modes") 654 modes = f" {', '.join(modes)}" if modes else "" 655 return f"START TRANSACTION{modes}" 656 657 def offset_limit_modifiers( 658 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 659 ) -> t.List[str]: 660 return [ 661 self.sql(expression, "offset"), 662 self.sql(limit), 663 ] 664 665 def create_sql(self, expression: exp.Create) -> str: 666 """ 667 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 668 so we need to remove them 669 """ 670 kind = expression.args["kind"] 671 schema = expression.this 672 if kind == "VIEW" and schema.expressions: 673 expression.this.set("expressions", None) 674 return super().create_sql(expression) 675 676 def delete_sql(self, expression: exp.Delete) -> str: 677 """ 678 Presto only supports DELETE FROM for a single table without an alias, so we need 679 to remove the unnecessary parts. If the original DELETE statement contains more 680 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 681 """ 682 tables = expression.args.get("tables") or [expression.this] 683 if len(tables) > 1: 684 return super().delete_sql(expression) 685 686 table = tables[0] 687 expression.set("this", table) 688 expression.set("tables", None) 689 690 if isinstance(table, exp.Table): 691 table_alias = table.args.get("alias") 692 if table_alias: 693 table_alias.pop() 694 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 695 696 return super().delete_sql(expression)
Generator converts a given syntax tree to the corresponding SQL string.
Arguments:
- pretty: Whether to format the produced SQL string. Default: False.
- identify: Determines when an identifier should be quoted. Possible values are: False (default): Never quote, except in cases where it's mandatory by the dialect. True or 'always': Always quote. 'safe': Only quote identifiers that are case insensitive.
- normalize: Whether to normalize identifiers to lowercase. Default: False.
- pad: The pad size in a formatted string. For example, this affects the indentation of a projection in a query, relative to its nesting level. Default: 2.
- indent: The indentation size in a formatted string. For example, this affects the
indentation of subqueries and filters under a
WHERE
clause. Default: 2. - normalize_functions: How to normalize function names. Possible values are: "upper" or True (default): Convert names to uppercase. "lower": Convert names to lowercase. False: Disables function name normalization.
- unsupported_level: Determines the generator's behavior when it encounters unsupported expressions. Default ErrorLevel.WARN.
- max_unsupported: 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: Whether the comma is leading or trailing in select expressions. This is only relevant when generating in pretty mode. 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 to preserve comments in the output SQL code. Default: True
565 def md5_sql(self, expression: exp.MD5) -> str: 566 this = expression.this 567 568 if not this.type: 569 from sqlglot.optimizer.annotate_types import annotate_types 570 571 this = annotate_types(this) 572 573 if this.is_type(*exp.DataType.TEXT_TYPES): 574 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 575 576 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
578 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 579 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 580 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 581 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 582 # which seems to be using the same time mapping as Hive, as per: 583 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 584 this = expression.this 585 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 586 value_as_timestamp = ( 587 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 588 ) 589 590 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 591 592 formatted_value = self.func( 593 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 594 ) 595 parse_with_tz = self.func( 596 "PARSE_DATETIME", 597 formatted_value, 598 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 599 ) 600 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 601 return self.func("TO_UNIXTIME", coalesced)
603 def bracket_sql(self, expression: exp.Bracket) -> str: 604 if expression.args.get("safe"): 605 return self.func( 606 "ELEMENT_AT", 607 expression.this, 608 seq_get( 609 apply_index_offset( 610 expression.this, 611 expression.expressions, 612 1 - expression.args.get("offset", 0), 613 ), 614 0, 615 ), 616 ) 617 return super().bracket_sql(expression)
619 def struct_sql(self, expression: exp.Struct) -> str: 620 from sqlglot.optimizer.annotate_types import annotate_types 621 622 expression = annotate_types(expression) 623 values: t.List[str] = [] 624 schema: t.List[str] = [] 625 unknown_type = False 626 627 for e in expression.expressions: 628 if isinstance(e, exp.PropertyEQ): 629 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 630 unknown_type = True 631 else: 632 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 633 values.append(self.sql(e, "expression")) 634 else: 635 values.append(self.sql(e)) 636 637 size = len(expression.expressions) 638 639 if not size or len(schema) != size: 640 if unknown_type: 641 self.unsupported( 642 "Cannot convert untyped key-value definitions (try annotate_types)." 643 ) 644 return self.func("ROW", *values) 645 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))"
665 def create_sql(self, expression: exp.Create) -> str: 666 """ 667 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 668 so we need to remove them 669 """ 670 kind = expression.args["kind"] 671 schema = expression.this 672 if kind == "VIEW" and schema.expressions: 673 expression.this.set("expressions", None) 674 return super().create_sql(expression)
Presto doesn't support CREATE VIEW with expressions (ex: CREATE VIEW x (cola)
then (cola)
is the expression),
so we need to remove them
676 def delete_sql(self, expression: exp.Delete) -> str: 677 """ 678 Presto only supports DELETE FROM for a single table without an alias, so we need 679 to remove the unnecessary parts. If the original DELETE statement contains more 680 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 681 """ 682 tables = expression.args.get("tables") or [expression.this] 683 if len(tables) > 1: 684 return super().delete_sql(expression) 685 686 table = tables[0] 687 expression.set("this", table) 688 expression.set("tables", None) 689 690 if isinstance(table, exp.Table): 691 table_alias = table.args.get("alias") 692 if table_alias: 693 table_alias.pop() 694 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 695 696 return super().delete_sql(expression)
Presto only supports DELETE FROM for a single table without an alias, so we need to remove the unnecessary parts. If the original DELETE statement contains more than one table to be deleted, we can't safely map it 1-1 to a Presto statement.
Inherited Members
- sqlglot.generator.Generator
- Generator
- NULL_ORDERING_SUPPORTED
- IGNORE_NULLS_IN_FUNC
- LOCKING_READS_SUPPORTED
- WRAP_DERIVED_VALUES
- CREATE_FUNCTION_RETURN_AS
- MATCHED_BY_SOURCE
- SINGLE_STRING_INTERVAL
- LIMIT_FETCH
- RENAME_TABLE_WITH_DB
- GROUPINGS_SEP
- INDEX_ON
- QUERY_HINT_SEP
- DUPLICATE_KEY_UPDATE_WITH_SET
- LIMIT_IS_TOP
- RETURNING_END
- EXTRACT_ALLOWS_QUOTES
- VALUES_AS_TABLE
- ALTER_TABLE_INCLUDE_COLUMN_KEYWORD
- UNNEST_WITH_ORDINALITY
- AGGREGATE_FILTER_SUPPORTED
- SEMI_ANTI_JOIN_WITH_SIDE
- COMPUTED_COLUMN_WITH_TYPE
- SUPPORTS_TABLE_COPY
- TABLESAMPLE_REQUIRES_PARENS
- TABLESAMPLE_SIZE_IS_ROWS
- TABLESAMPLE_KEYWORDS
- TABLESAMPLE_WITH_METHOD
- TABLESAMPLE_SEED_KEYWORD
- COLLATE_IS_FUNC
- DATA_TYPE_SPECIFIERS_ALLOWED
- ENSURE_BOOLS
- CTE_RECURSIVE_KEYWORD_REQUIRED
- LAST_DAY_SUPPORTS_DATE_PART
- SUPPORTS_TABLE_ALIAS_COLUMNS
- UNPIVOT_ALIASES_ARE_IDENTIFIERS
- JSON_KEY_VALUE_PAIR_SEP
- INSERT_OVERWRITE
- SUPPORTS_SELECT_INTO
- SUPPORTS_UNLOGGED_TABLES
- SUPPORTS_CREATE_TABLE_LIKE
- JSON_TYPE_REQUIRED_FOR_EXTRACTION
- JSON_PATH_BRACKETED_KEY_SUPPORTED
- JSON_PATH_SINGLE_QUOTE_ESCAPE
- SUPPORTED_JSON_PATH_PARTS
- CAN_IMPLEMENT_ARRAY_ANY
- SET_OP_MODIFIERS
- COPY_PARAMS_ARE_WRAPPED
- COPY_PARAMS_EQ_REQUIRED
- COPY_HAS_INTO_KEYWORD
- TRY_SUPPORTED
- SUPPORTS_UESCAPE
- STAR_EXCEPT
- WITH_PROPERTIES_PREFIX
- QUOTE_JSON_PATH
- SUPPORTS_EXPLODING_PROJECTIONS
- ARRAY_CONCAT_IS_VAR_LEN
- SUPPORTS_CONVERT_TIMEZONE
- TIME_PART_SINGULARS
- TOKEN_MAPPING
- PARAMETER_TOKEN
- NAMED_PLACEHOLDER_TOKEN
- WITH_SEPARATED_COMMENTS
- EXCLUDE_COMMENTS
- UNWRAPPED_INTERVAL_VALUES
- PARAMETERIZABLE_TEXT_TYPES
- EXPRESSIONS_WITHOUT_NESTED_CTES
- SENTINEL_LINE_BREAK
- pretty
- identify
- normalize
- pad
- unsupported_level
- max_unsupported
- leading_comma
- max_text_width
- comments
- dialect
- normalize_functions
- unsupported_messages
- generate
- preprocess
- unsupported
- sep
- seg
- pad_comment
- maybe_comment
- wrap
- no_identify
- normalize_func
- indent
- sql
- uncache_sql
- cache_sql
- characterset_sql
- column_parts
- column_sql
- columnposition_sql
- columndef_sql
- columnconstraint_sql
- computedcolumnconstraint_sql
- autoincrementcolumnconstraint_sql
- compresscolumnconstraint_sql
- generatedasidentitycolumnconstraint_sql
- generatedasrowcolumnconstraint_sql
- periodforsystemtimeconstraint_sql
- notnullcolumnconstraint_sql
- transformcolumnconstraint_sql
- primarykeycolumnconstraint_sql
- uniquecolumnconstraint_sql
- createable_sql
- sequenceproperties_sql
- clone_sql
- describe_sql
- heredoc_sql
- prepend_ctes
- with_sql
- cte_sql
- tablealias_sql
- bitstring_sql
- hexstring_sql
- bytestring_sql
- unicodestring_sql
- rawstring_sql
- datatypeparam_sql
- datatype_sql
- directory_sql
- drop_sql
- set_operation
- set_operations
- fetch_sql
- filter_sql
- hint_sql
- indexparameters_sql
- index_sql
- identifier_sql
- hex_sql
- lowerhex_sql
- inputoutputformat_sql
- national_sql
- partition_sql
- properties_sql
- root_properties
- properties
- with_properties
- locate_properties
- property_name
- property_sql
- likeproperty_sql
- fallbackproperty_sql
- journalproperty_sql
- freespaceproperty_sql
- checksumproperty_sql
- mergeblockratioproperty_sql
- datablocksizeproperty_sql
- blockcompressionproperty_sql
- isolatedloadingproperty_sql
- partitionboundspec_sql
- partitionedofproperty_sql
- lockingproperty_sql
- withdataproperty_sql
- withsystemversioningproperty_sql
- insert_sql
- introducer_sql
- kill_sql
- pseudotype_sql
- objectidentifier_sql
- onconflict_sql
- returning_sql
- rowformatdelimitedproperty_sql
- withtablehint_sql
- indextablehint_sql
- historicaldata_sql
- table_parts
- table_sql
- tablesample_sql
- pivot_sql
- version_sql
- tuple_sql
- update_sql
- values_sql
- var_sql
- into_sql
- from_sql
- groupingsets_sql
- rollup_sql
- cube_sql
- group_sql
- having_sql
- connect_sql
- prior_sql
- join_sql
- lambda_sql
- lateral_op
- lateral_sql
- limit_sql
- offset_sql
- setitem_sql
- set_sql
- pragma_sql
- lock_sql
- literal_sql
- escape_str
- loaddata_sql
- null_sql
- boolean_sql
- order_sql
- withfill_sql
- cluster_sql
- distribute_sql
- sort_sql
- ordered_sql
- matchrecognizemeasure_sql
- matchrecognize_sql
- query_modifiers
- options_modifier
- queryoption_sql
- after_limit_modifiers
- select_sql
- schema_sql
- schema_columns_sql
- star_sql
- parameter_sql
- sessionparameter_sql
- placeholder_sql
- subquery_sql
- qualify_sql
- unnest_sql
- prewhere_sql
- where_sql
- window_sql
- partition_by_sql
- windowspec_sql
- withingroup_sql
- between_sql
- bracket_offset_expressions
- all_sql
- any_sql
- exists_sql
- case_sql
- constraint_sql
- nextvaluefor_sql
- extract_sql
- trim_sql
- convert_concat_args
- concat_sql
- concatws_sql
- check_sql
- foreignkey_sql
- primarykey_sql
- if_sql
- matchagainst_sql
- jsonkeyvalue_sql
- jsonpath_sql
- json_path_part
- formatjson_sql
- jsonobject_sql
- jsonobjectagg_sql
- jsonarray_sql
- jsonarrayagg_sql
- jsoncolumndef_sql
- jsonschema_sql
- jsontable_sql
- openjsoncolumndef_sql
- openjson_sql
- in_sql
- in_unnest_op
- return_sql
- reference_sql
- anonymous_sql
- paren_sql
- neg_sql
- not_sql
- alias_sql
- pivotalias_sql
- aliases_sql
- atindex_sql
- attimezone_sql
- fromtimezone_sql
- add_sql
- and_sql
- or_sql
- xor_sql
- connector_sql
- bitwiseand_sql
- bitwiseleftshift_sql
- bitwisenot_sql
- bitwiseor_sql
- bitwiserightshift_sql
- bitwisexor_sql
- cast_sql
- currentdate_sql
- collate_sql
- command_sql
- comment_sql
- mergetreettlaction_sql
- mergetreettl_sql
- commit_sql
- rollback_sql
- altercolumn_sql
- alterdiststyle_sql
- altersortkey_sql
- renametable_sql
- renamecolumn_sql
- alterset_sql
- alter_sql
- add_column_sql
- droppartition_sql
- addconstraint_sql
- distinct_sql
- ignorenulls_sql
- respectnulls_sql
- havingmax_sql
- intdiv_sql
- dpipe_sql
- div_sql
- overlaps_sql
- distance_sql
- dot_sql
- eq_sql
- propertyeq_sql
- escape_sql
- glob_sql
- gt_sql
- gte_sql
- ilike_sql
- ilikeany_sql
- is_sql
- like_sql
- likeany_sql
- similarto_sql
- lt_sql
- lte_sql
- mod_sql
- mul_sql
- neq_sql
- nullsafeeq_sql
- nullsafeneq_sql
- slice_sql
- sub_sql
- trycast_sql
- try_sql
- log_sql
- use_sql
- binary
- function_fallback_sql
- func
- format_args
- too_wide
- format_time
- expressions
- op_expressions
- naked_property
- tag_sql
- token_sql
- userdefinedfunction_sql
- joinhint_sql
- kwarg_sql
- when_sql
- merge_sql
- tochar_sql
- tonumber_sql
- dictproperty_sql
- dictrange_sql
- dictsubproperty_sql
- duplicatekeyproperty_sql
- distributedbyproperty_sql
- oncluster_sql
- clusteredbyproperty_sql
- anyvalue_sql
- querytransform_sql
- indexconstraintoption_sql
- checkcolumnconstraint_sql
- indexcolumnconstraint_sql
- nvl2_sql
- comprehension_sql
- columnprefix_sql
- opclass_sql
- predict_sql
- forin_sql
- refresh_sql
- toarray_sql
- tsordstotime_sql
- tsordstotimestamp_sql
- tsordstodate_sql
- unixdate_sql
- lastday_sql
- dateadd_sql
- arrayany_sql
- partitionrange_sql
- truncatetable_sql
- convert_sql
- copyparameter_sql
- credentials_sql
- copy_sql
- semicolon_sql
- datadeletionproperty_sql
- maskingpolicycolumnconstraint_sql
- gapfill_sql
- scope_resolution
- scoperesolution_sql
- parsejson_sql
- rand_sql
- changes_sql
- pad_sql
- summarize_sql
- explodinggenerateseries_sql
- arrayconcat_sql
- converttimezone_sql
- json_sql
- jsonvalue_sql
- conditionalinsert_sql
- multitableinserts_sql
- oncondition_sql
- jsonexists_sql
- arrayagg_sql