sqlglot.dialects.redshift
1from __future__ import annotations 2 3import typing as t 4 5from sqlglot import exp, transforms 6from sqlglot.dialects.dialect import ( 7 NormalizationStrategy, 8 concat_to_dpipe_sql, 9 concat_ws_to_dpipe_sql, 10 date_delta_sql, 11 generatedasidentitycolumnconstraint_sql, 12 json_extract_segments, 13 no_tablesample_sql, 14 rename_func, 15 map_date_part, 16) 17from sqlglot.dialects.postgres import Postgres 18from sqlglot.helper import seq_get 19from sqlglot.tokens import TokenType 20from sqlglot.parser import build_convert_timezone 21 22if t.TYPE_CHECKING: 23 from sqlglot._typing import E 24 25 26def _build_date_delta(expr_type: t.Type[E]) -> t.Callable[[t.List], E]: 27 def _builder(args: t.List) -> E: 28 expr = expr_type( 29 this=seq_get(args, 2), 30 expression=seq_get(args, 1), 31 unit=map_date_part(seq_get(args, 0)), 32 ) 33 if expr_type is exp.TsOrDsAdd: 34 expr.set("return_type", exp.DataType.build("TIMESTAMP")) 35 36 return expr 37 38 return _builder 39 40 41class Redshift(Postgres): 42 # https://docs.aws.amazon.com/redshift/latest/dg/r_names.html 43 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 44 45 SUPPORTS_USER_DEFINED_TYPES = False 46 INDEX_OFFSET = 0 47 COPY_PARAMS_ARE_CSV = False 48 HEX_LOWERCASE = True 49 HAS_DISTINCT_ARRAY_CONSTRUCTORS = True 50 51 # ref: https://docs.aws.amazon.com/redshift/latest/dg/r_FORMAT_strings.html 52 TIME_FORMAT = "'YYYY-MM-DD HH24:MI:SS'" 53 TIME_MAPPING = {**Postgres.TIME_MAPPING, "MON": "%b", "HH24": "%H", "HH": "%I"} 54 55 class Parser(Postgres.Parser): 56 FUNCTIONS = { 57 **Postgres.Parser.FUNCTIONS, 58 "ADD_MONTHS": lambda args: exp.TsOrDsAdd( 59 this=seq_get(args, 0), 60 expression=seq_get(args, 1), 61 unit=exp.var("month"), 62 return_type=exp.DataType.build("TIMESTAMP"), 63 ), 64 "CONVERT_TIMEZONE": lambda args: build_convert_timezone(args, "UTC"), 65 "DATEADD": _build_date_delta(exp.TsOrDsAdd), 66 "DATE_ADD": _build_date_delta(exp.TsOrDsAdd), 67 "DATEDIFF": _build_date_delta(exp.TsOrDsDiff), 68 "DATE_DIFF": _build_date_delta(exp.TsOrDsDiff), 69 "GETDATE": exp.CurrentTimestamp.from_arg_list, 70 "LISTAGG": exp.GroupConcat.from_arg_list, 71 "SPLIT_TO_ARRAY": lambda args: exp.StringToArray( 72 this=seq_get(args, 0), expression=seq_get(args, 1) or exp.Literal.string(",") 73 ), 74 "STRTOL": exp.FromBase.from_arg_list, 75 } 76 77 NO_PAREN_FUNCTION_PARSERS = { 78 **Postgres.Parser.NO_PAREN_FUNCTION_PARSERS, 79 "APPROXIMATE": lambda self: self._parse_approximate_count(), 80 "SYSDATE": lambda self: self.expression(exp.CurrentTimestamp, sysdate=True), 81 } 82 83 SUPPORTS_IMPLICIT_UNNEST = True 84 85 def _parse_table( 86 self, 87 schema: bool = False, 88 joins: bool = False, 89 alias_tokens: t.Optional[t.Collection[TokenType]] = None, 90 parse_bracket: bool = False, 91 is_db_reference: bool = False, 92 parse_partition: bool = False, 93 ) -> t.Optional[exp.Expression]: 94 # Redshift supports UNPIVOTing SUPER objects, e.g. `UNPIVOT foo.obj[0] AS val AT attr` 95 unpivot = self._match(TokenType.UNPIVOT) 96 table = super()._parse_table( 97 schema=schema, 98 joins=joins, 99 alias_tokens=alias_tokens, 100 parse_bracket=parse_bracket, 101 is_db_reference=is_db_reference, 102 ) 103 104 return self.expression(exp.Pivot, this=table, unpivot=True) if unpivot else table 105 106 def _parse_convert( 107 self, strict: bool, safe: t.Optional[bool] = None 108 ) -> t.Optional[exp.Expression]: 109 to = self._parse_types() 110 self._match(TokenType.COMMA) 111 this = self._parse_bitwise() 112 return self.expression(exp.TryCast, this=this, to=to, safe=safe) 113 114 def _parse_approximate_count(self) -> t.Optional[exp.ApproxDistinct]: 115 index = self._index - 1 116 func = self._parse_function() 117 118 if isinstance(func, exp.Count) and isinstance(func.this, exp.Distinct): 119 return self.expression(exp.ApproxDistinct, this=seq_get(func.this.expressions, 0)) 120 self._retreat(index) 121 return None 122 123 class Tokenizer(Postgres.Tokenizer): 124 BIT_STRINGS = [] 125 HEX_STRINGS = [] 126 STRING_ESCAPES = ["\\", "'"] 127 128 KEYWORDS = { 129 **Postgres.Tokenizer.KEYWORDS, 130 "(+)": TokenType.JOIN_MARKER, 131 "HLLSKETCH": TokenType.HLLSKETCH, 132 "MINUS": TokenType.EXCEPT, 133 "SUPER": TokenType.SUPER, 134 "TOP": TokenType.TOP, 135 "UNLOAD": TokenType.COMMAND, 136 "VARBYTE": TokenType.VARBINARY, 137 } 138 KEYWORDS.pop("VALUES") 139 140 # Redshift allows # to appear as a table identifier prefix 141 SINGLE_TOKENS = Postgres.Tokenizer.SINGLE_TOKENS.copy() 142 SINGLE_TOKENS.pop("#") 143 144 class Generator(Postgres.Generator): 145 LOCKING_READS_SUPPORTED = False 146 QUERY_HINTS = False 147 VALUES_AS_TABLE = False 148 TZ_TO_WITH_TIME_ZONE = True 149 NVL2_SUPPORTED = True 150 LAST_DAY_SUPPORTS_DATE_PART = False 151 CAN_IMPLEMENT_ARRAY_ANY = False 152 MULTI_ARG_DISTINCT = True 153 COPY_PARAMS_ARE_WRAPPED = False 154 HEX_FUNC = "TO_HEX" 155 PARSE_JSON_NAME = "JSON_PARSE" 156 ARRAY_CONCAT_IS_VAR_LEN = False 157 SUPPORTS_CONVERT_TIMEZONE = True 158 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 159 160 # Redshift doesn't have `WITH` as part of their with_properties so we remove it 161 WITH_PROPERTIES_PREFIX = " " 162 163 TYPE_MAPPING = { 164 **Postgres.Generator.TYPE_MAPPING, 165 exp.DataType.Type.BINARY: "VARBYTE", 166 exp.DataType.Type.INT: "INTEGER", 167 exp.DataType.Type.TIMETZ: "TIME", 168 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 169 exp.DataType.Type.VARBINARY: "VARBYTE", 170 exp.DataType.Type.ROWVERSION: "VARBYTE", 171 } 172 173 TRANSFORMS = { 174 **Postgres.Generator.TRANSFORMS, 175 exp.ArrayConcat: lambda self, e: self.arrayconcat_sql(e, name="ARRAY_CONCAT"), 176 exp.Concat: concat_to_dpipe_sql, 177 exp.ConcatWs: concat_ws_to_dpipe_sql, 178 exp.ApproxDistinct: lambda self, 179 e: f"APPROXIMATE COUNT(DISTINCT {self.sql(e, 'this')})", 180 exp.CurrentTimestamp: lambda self, e: ( 181 "SYSDATE" if e.args.get("sysdate") else "GETDATE()" 182 ), 183 exp.DateAdd: date_delta_sql("DATEADD"), 184 exp.DateDiff: date_delta_sql("DATEDIFF"), 185 exp.DistKeyProperty: lambda self, e: self.func("DISTKEY", e.this), 186 exp.DistStyleProperty: lambda self, e: self.naked_property(e), 187 exp.Explode: lambda self, e: self.explode_sql(e), 188 exp.FromBase: rename_func("STRTOL"), 189 exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql, 190 exp.JSONExtract: json_extract_segments("JSON_EXTRACT_PATH_TEXT"), 191 exp.JSONExtractScalar: json_extract_segments("JSON_EXTRACT_PATH_TEXT"), 192 exp.GroupConcat: rename_func("LISTAGG"), 193 exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))), 194 exp.Select: transforms.preprocess( 195 [ 196 transforms.eliminate_distinct_on, 197 transforms.eliminate_semi_and_anti_joins, 198 transforms.unqualify_unnest, 199 transforms.unnest_generate_date_array_using_recursive_cte, 200 ] 201 ), 202 exp.SortKeyProperty: lambda self, 203 e: f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})", 204 exp.StartsWith: lambda self, 205 e: f"{self.sql(e.this)} LIKE {self.sql(e.expression)} || '%'", 206 exp.StringToArray: rename_func("SPLIT_TO_ARRAY"), 207 exp.TableSample: no_tablesample_sql, 208 exp.TsOrDsAdd: date_delta_sql("DATEADD"), 209 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 210 exp.UnixToTime: lambda self, 211 e: f"(TIMESTAMP 'epoch' + {self.sql(e.this)} * INTERVAL '1 SECOND')", 212 } 213 214 # Postgres maps exp.Pivot to no_pivot_sql, but Redshift support pivots 215 TRANSFORMS.pop(exp.Pivot) 216 217 # Postgres doesn't support JSON_PARSE, but Redshift does 218 TRANSFORMS.pop(exp.ParseJSON) 219 220 # Redshift uses the POW | POWER (expr1, expr2) syntax instead of expr1 ^ expr2 (postgres) 221 TRANSFORMS.pop(exp.Pow) 222 223 # Redshift supports these functions 224 TRANSFORMS.pop(exp.AnyValue) 225 TRANSFORMS.pop(exp.LastDay) 226 TRANSFORMS.pop(exp.SHA2) 227 228 RESERVED_KEYWORDS = { 229 "aes128", 230 "aes256", 231 "all", 232 "allowoverwrite", 233 "analyse", 234 "analyze", 235 "and", 236 "any", 237 "array", 238 "as", 239 "asc", 240 "authorization", 241 "az64", 242 "backup", 243 "between", 244 "binary", 245 "blanksasnull", 246 "both", 247 "bytedict", 248 "bzip2", 249 "case", 250 "cast", 251 "check", 252 "collate", 253 "column", 254 "constraint", 255 "create", 256 "credentials", 257 "cross", 258 "current_date", 259 "current_time", 260 "current_timestamp", 261 "current_user", 262 "current_user_id", 263 "default", 264 "deferrable", 265 "deflate", 266 "defrag", 267 "delta", 268 "delta32k", 269 "desc", 270 "disable", 271 "distinct", 272 "do", 273 "else", 274 "emptyasnull", 275 "enable", 276 "encode", 277 "encrypt ", 278 "encryption", 279 "end", 280 "except", 281 "explicit", 282 "false", 283 "for", 284 "foreign", 285 "freeze", 286 "from", 287 "full", 288 "globaldict256", 289 "globaldict64k", 290 "grant", 291 "group", 292 "gzip", 293 "having", 294 "identity", 295 "ignore", 296 "ilike", 297 "in", 298 "initially", 299 "inner", 300 "intersect", 301 "interval", 302 "into", 303 "is", 304 "isnull", 305 "join", 306 "leading", 307 "left", 308 "like", 309 "limit", 310 "localtime", 311 "localtimestamp", 312 "lun", 313 "luns", 314 "lzo", 315 "lzop", 316 "minus", 317 "mostly16", 318 "mostly32", 319 "mostly8", 320 "natural", 321 "new", 322 "not", 323 "notnull", 324 "null", 325 "nulls", 326 "off", 327 "offline", 328 "offset", 329 "oid", 330 "old", 331 "on", 332 "only", 333 "open", 334 "or", 335 "order", 336 "outer", 337 "overlaps", 338 "parallel", 339 "partition", 340 "percent", 341 "permissions", 342 "pivot", 343 "placing", 344 "primary", 345 "raw", 346 "readratio", 347 "recover", 348 "references", 349 "rejectlog", 350 "resort", 351 "respect", 352 "restore", 353 "right", 354 "select", 355 "session_user", 356 "similar", 357 "snapshot", 358 "some", 359 "sysdate", 360 "system", 361 "table", 362 "tag", 363 "tdes", 364 "text255", 365 "text32k", 366 "then", 367 "timestamp", 368 "to", 369 "top", 370 "trailing", 371 "true", 372 "truncatecolumns", 373 "type", 374 "union", 375 "unique", 376 "unnest", 377 "unpivot", 378 "user", 379 "using", 380 "verbose", 381 "wallet", 382 "when", 383 "where", 384 "with", 385 "without", 386 } 387 388 def unnest_sql(self, expression: exp.Unnest) -> str: 389 args = expression.expressions 390 num_args = len(args) 391 392 if num_args != 1: 393 self.unsupported(f"Unsupported number of arguments in UNNEST: {num_args}") 394 return "" 395 396 if isinstance(expression.find_ancestor(exp.From, exp.Join, exp.Select), exp.Select): 397 self.unsupported("Unsupported UNNEST when not used in FROM/JOIN clauses") 398 return "" 399 400 arg = self.sql(seq_get(args, 0)) 401 402 alias = self.expressions(expression.args.get("alias"), key="columns", flat=True) 403 return f"{arg} AS {alias}" if alias else arg 404 405 def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str: 406 if expression.is_type(exp.DataType.Type.JSON): 407 # Redshift doesn't support a JSON type, so casting to it is treated as a noop 408 return self.sql(expression, "this") 409 410 return super().cast_sql(expression, safe_prefix=safe_prefix) 411 412 def datatype_sql(self, expression: exp.DataType) -> str: 413 """ 414 Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean 415 VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type 416 without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert 417 `TEXT` to `VARCHAR`. 418 """ 419 if expression.is_type("text"): 420 expression.set("this", exp.DataType.Type.VARCHAR) 421 precision = expression.args.get("expressions") 422 423 if not precision: 424 expression.append("expressions", exp.var("MAX")) 425 426 return super().datatype_sql(expression) 427 428 def alterset_sql(self, expression: exp.AlterSet) -> str: 429 exprs = self.expressions(expression, flat=True) 430 exprs = f" TABLE PROPERTIES ({exprs})" if exprs else "" 431 location = self.sql(expression, "location") 432 location = f" LOCATION {location}" if location else "" 433 file_format = self.expressions(expression, key="file_format", flat=True, sep=" ") 434 file_format = f" FILE FORMAT {file_format}" if file_format else "" 435 436 return f"SET{exprs}{location}{file_format}" 437 438 def array_sql(self, expression: exp.Array) -> str: 439 if expression.args.get("bracket_notation"): 440 return super().array_sql(expression) 441 442 return rename_func("ARRAY")(self, expression) 443 444 def explode_sql(self, expression: exp.Explode) -> str: 445 self.unsupported("Unsupported EXPLODE() function") 446 return ""
42class Redshift(Postgres): 43 # https://docs.aws.amazon.com/redshift/latest/dg/r_names.html 44 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 45 46 SUPPORTS_USER_DEFINED_TYPES = False 47 INDEX_OFFSET = 0 48 COPY_PARAMS_ARE_CSV = False 49 HEX_LOWERCASE = True 50 HAS_DISTINCT_ARRAY_CONSTRUCTORS = True 51 52 # ref: https://docs.aws.amazon.com/redshift/latest/dg/r_FORMAT_strings.html 53 TIME_FORMAT = "'YYYY-MM-DD HH24:MI:SS'" 54 TIME_MAPPING = {**Postgres.TIME_MAPPING, "MON": "%b", "HH24": "%H", "HH": "%I"} 55 56 class Parser(Postgres.Parser): 57 FUNCTIONS = { 58 **Postgres.Parser.FUNCTIONS, 59 "ADD_MONTHS": lambda args: exp.TsOrDsAdd( 60 this=seq_get(args, 0), 61 expression=seq_get(args, 1), 62 unit=exp.var("month"), 63 return_type=exp.DataType.build("TIMESTAMP"), 64 ), 65 "CONVERT_TIMEZONE": lambda args: build_convert_timezone(args, "UTC"), 66 "DATEADD": _build_date_delta(exp.TsOrDsAdd), 67 "DATE_ADD": _build_date_delta(exp.TsOrDsAdd), 68 "DATEDIFF": _build_date_delta(exp.TsOrDsDiff), 69 "DATE_DIFF": _build_date_delta(exp.TsOrDsDiff), 70 "GETDATE": exp.CurrentTimestamp.from_arg_list, 71 "LISTAGG": exp.GroupConcat.from_arg_list, 72 "SPLIT_TO_ARRAY": lambda args: exp.StringToArray( 73 this=seq_get(args, 0), expression=seq_get(args, 1) or exp.Literal.string(",") 74 ), 75 "STRTOL": exp.FromBase.from_arg_list, 76 } 77 78 NO_PAREN_FUNCTION_PARSERS = { 79 **Postgres.Parser.NO_PAREN_FUNCTION_PARSERS, 80 "APPROXIMATE": lambda self: self._parse_approximate_count(), 81 "SYSDATE": lambda self: self.expression(exp.CurrentTimestamp, sysdate=True), 82 } 83 84 SUPPORTS_IMPLICIT_UNNEST = True 85 86 def _parse_table( 87 self, 88 schema: bool = False, 89 joins: bool = False, 90 alias_tokens: t.Optional[t.Collection[TokenType]] = None, 91 parse_bracket: bool = False, 92 is_db_reference: bool = False, 93 parse_partition: bool = False, 94 ) -> t.Optional[exp.Expression]: 95 # Redshift supports UNPIVOTing SUPER objects, e.g. `UNPIVOT foo.obj[0] AS val AT attr` 96 unpivot = self._match(TokenType.UNPIVOT) 97 table = super()._parse_table( 98 schema=schema, 99 joins=joins, 100 alias_tokens=alias_tokens, 101 parse_bracket=parse_bracket, 102 is_db_reference=is_db_reference, 103 ) 104 105 return self.expression(exp.Pivot, this=table, unpivot=True) if unpivot else table 106 107 def _parse_convert( 108 self, strict: bool, safe: t.Optional[bool] = None 109 ) -> t.Optional[exp.Expression]: 110 to = self._parse_types() 111 self._match(TokenType.COMMA) 112 this = self._parse_bitwise() 113 return self.expression(exp.TryCast, this=this, to=to, safe=safe) 114 115 def _parse_approximate_count(self) -> t.Optional[exp.ApproxDistinct]: 116 index = self._index - 1 117 func = self._parse_function() 118 119 if isinstance(func, exp.Count) and isinstance(func.this, exp.Distinct): 120 return self.expression(exp.ApproxDistinct, this=seq_get(func.this.expressions, 0)) 121 self._retreat(index) 122 return None 123 124 class Tokenizer(Postgres.Tokenizer): 125 BIT_STRINGS = [] 126 HEX_STRINGS = [] 127 STRING_ESCAPES = ["\\", "'"] 128 129 KEYWORDS = { 130 **Postgres.Tokenizer.KEYWORDS, 131 "(+)": TokenType.JOIN_MARKER, 132 "HLLSKETCH": TokenType.HLLSKETCH, 133 "MINUS": TokenType.EXCEPT, 134 "SUPER": TokenType.SUPER, 135 "TOP": TokenType.TOP, 136 "UNLOAD": TokenType.COMMAND, 137 "VARBYTE": TokenType.VARBINARY, 138 } 139 KEYWORDS.pop("VALUES") 140 141 # Redshift allows # to appear as a table identifier prefix 142 SINGLE_TOKENS = Postgres.Tokenizer.SINGLE_TOKENS.copy() 143 SINGLE_TOKENS.pop("#") 144 145 class Generator(Postgres.Generator): 146 LOCKING_READS_SUPPORTED = False 147 QUERY_HINTS = False 148 VALUES_AS_TABLE = False 149 TZ_TO_WITH_TIME_ZONE = True 150 NVL2_SUPPORTED = True 151 LAST_DAY_SUPPORTS_DATE_PART = False 152 CAN_IMPLEMENT_ARRAY_ANY = False 153 MULTI_ARG_DISTINCT = True 154 COPY_PARAMS_ARE_WRAPPED = False 155 HEX_FUNC = "TO_HEX" 156 PARSE_JSON_NAME = "JSON_PARSE" 157 ARRAY_CONCAT_IS_VAR_LEN = False 158 SUPPORTS_CONVERT_TIMEZONE = True 159 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 160 161 # Redshift doesn't have `WITH` as part of their with_properties so we remove it 162 WITH_PROPERTIES_PREFIX = " " 163 164 TYPE_MAPPING = { 165 **Postgres.Generator.TYPE_MAPPING, 166 exp.DataType.Type.BINARY: "VARBYTE", 167 exp.DataType.Type.INT: "INTEGER", 168 exp.DataType.Type.TIMETZ: "TIME", 169 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 170 exp.DataType.Type.VARBINARY: "VARBYTE", 171 exp.DataType.Type.ROWVERSION: "VARBYTE", 172 } 173 174 TRANSFORMS = { 175 **Postgres.Generator.TRANSFORMS, 176 exp.ArrayConcat: lambda self, e: self.arrayconcat_sql(e, name="ARRAY_CONCAT"), 177 exp.Concat: concat_to_dpipe_sql, 178 exp.ConcatWs: concat_ws_to_dpipe_sql, 179 exp.ApproxDistinct: lambda self, 180 e: f"APPROXIMATE COUNT(DISTINCT {self.sql(e, 'this')})", 181 exp.CurrentTimestamp: lambda self, e: ( 182 "SYSDATE" if e.args.get("sysdate") else "GETDATE()" 183 ), 184 exp.DateAdd: date_delta_sql("DATEADD"), 185 exp.DateDiff: date_delta_sql("DATEDIFF"), 186 exp.DistKeyProperty: lambda self, e: self.func("DISTKEY", e.this), 187 exp.DistStyleProperty: lambda self, e: self.naked_property(e), 188 exp.Explode: lambda self, e: self.explode_sql(e), 189 exp.FromBase: rename_func("STRTOL"), 190 exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql, 191 exp.JSONExtract: json_extract_segments("JSON_EXTRACT_PATH_TEXT"), 192 exp.JSONExtractScalar: json_extract_segments("JSON_EXTRACT_PATH_TEXT"), 193 exp.GroupConcat: rename_func("LISTAGG"), 194 exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))), 195 exp.Select: transforms.preprocess( 196 [ 197 transforms.eliminate_distinct_on, 198 transforms.eliminate_semi_and_anti_joins, 199 transforms.unqualify_unnest, 200 transforms.unnest_generate_date_array_using_recursive_cte, 201 ] 202 ), 203 exp.SortKeyProperty: lambda self, 204 e: f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})", 205 exp.StartsWith: lambda self, 206 e: f"{self.sql(e.this)} LIKE {self.sql(e.expression)} || '%'", 207 exp.StringToArray: rename_func("SPLIT_TO_ARRAY"), 208 exp.TableSample: no_tablesample_sql, 209 exp.TsOrDsAdd: date_delta_sql("DATEADD"), 210 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 211 exp.UnixToTime: lambda self, 212 e: f"(TIMESTAMP 'epoch' + {self.sql(e.this)} * INTERVAL '1 SECOND')", 213 } 214 215 # Postgres maps exp.Pivot to no_pivot_sql, but Redshift support pivots 216 TRANSFORMS.pop(exp.Pivot) 217 218 # Postgres doesn't support JSON_PARSE, but Redshift does 219 TRANSFORMS.pop(exp.ParseJSON) 220 221 # Redshift uses the POW | POWER (expr1, expr2) syntax instead of expr1 ^ expr2 (postgres) 222 TRANSFORMS.pop(exp.Pow) 223 224 # Redshift supports these functions 225 TRANSFORMS.pop(exp.AnyValue) 226 TRANSFORMS.pop(exp.LastDay) 227 TRANSFORMS.pop(exp.SHA2) 228 229 RESERVED_KEYWORDS = { 230 "aes128", 231 "aes256", 232 "all", 233 "allowoverwrite", 234 "analyse", 235 "analyze", 236 "and", 237 "any", 238 "array", 239 "as", 240 "asc", 241 "authorization", 242 "az64", 243 "backup", 244 "between", 245 "binary", 246 "blanksasnull", 247 "both", 248 "bytedict", 249 "bzip2", 250 "case", 251 "cast", 252 "check", 253 "collate", 254 "column", 255 "constraint", 256 "create", 257 "credentials", 258 "cross", 259 "current_date", 260 "current_time", 261 "current_timestamp", 262 "current_user", 263 "current_user_id", 264 "default", 265 "deferrable", 266 "deflate", 267 "defrag", 268 "delta", 269 "delta32k", 270 "desc", 271 "disable", 272 "distinct", 273 "do", 274 "else", 275 "emptyasnull", 276 "enable", 277 "encode", 278 "encrypt ", 279 "encryption", 280 "end", 281 "except", 282 "explicit", 283 "false", 284 "for", 285 "foreign", 286 "freeze", 287 "from", 288 "full", 289 "globaldict256", 290 "globaldict64k", 291 "grant", 292 "group", 293 "gzip", 294 "having", 295 "identity", 296 "ignore", 297 "ilike", 298 "in", 299 "initially", 300 "inner", 301 "intersect", 302 "interval", 303 "into", 304 "is", 305 "isnull", 306 "join", 307 "leading", 308 "left", 309 "like", 310 "limit", 311 "localtime", 312 "localtimestamp", 313 "lun", 314 "luns", 315 "lzo", 316 "lzop", 317 "minus", 318 "mostly16", 319 "mostly32", 320 "mostly8", 321 "natural", 322 "new", 323 "not", 324 "notnull", 325 "null", 326 "nulls", 327 "off", 328 "offline", 329 "offset", 330 "oid", 331 "old", 332 "on", 333 "only", 334 "open", 335 "or", 336 "order", 337 "outer", 338 "overlaps", 339 "parallel", 340 "partition", 341 "percent", 342 "permissions", 343 "pivot", 344 "placing", 345 "primary", 346 "raw", 347 "readratio", 348 "recover", 349 "references", 350 "rejectlog", 351 "resort", 352 "respect", 353 "restore", 354 "right", 355 "select", 356 "session_user", 357 "similar", 358 "snapshot", 359 "some", 360 "sysdate", 361 "system", 362 "table", 363 "tag", 364 "tdes", 365 "text255", 366 "text32k", 367 "then", 368 "timestamp", 369 "to", 370 "top", 371 "trailing", 372 "true", 373 "truncatecolumns", 374 "type", 375 "union", 376 "unique", 377 "unnest", 378 "unpivot", 379 "user", 380 "using", 381 "verbose", 382 "wallet", 383 "when", 384 "where", 385 "with", 386 "without", 387 } 388 389 def unnest_sql(self, expression: exp.Unnest) -> str: 390 args = expression.expressions 391 num_args = len(args) 392 393 if num_args != 1: 394 self.unsupported(f"Unsupported number of arguments in UNNEST: {num_args}") 395 return "" 396 397 if isinstance(expression.find_ancestor(exp.From, exp.Join, exp.Select), exp.Select): 398 self.unsupported("Unsupported UNNEST when not used in FROM/JOIN clauses") 399 return "" 400 401 arg = self.sql(seq_get(args, 0)) 402 403 alias = self.expressions(expression.args.get("alias"), key="columns", flat=True) 404 return f"{arg} AS {alias}" if alias else arg 405 406 def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str: 407 if expression.is_type(exp.DataType.Type.JSON): 408 # Redshift doesn't support a JSON type, so casting to it is treated as a noop 409 return self.sql(expression, "this") 410 411 return super().cast_sql(expression, safe_prefix=safe_prefix) 412 413 def datatype_sql(self, expression: exp.DataType) -> str: 414 """ 415 Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean 416 VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type 417 without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert 418 `TEXT` to `VARCHAR`. 419 """ 420 if expression.is_type("text"): 421 expression.set("this", exp.DataType.Type.VARCHAR) 422 precision = expression.args.get("expressions") 423 424 if not precision: 425 expression.append("expressions", exp.var("MAX")) 426 427 return super().datatype_sql(expression) 428 429 def alterset_sql(self, expression: exp.AlterSet) -> str: 430 exprs = self.expressions(expression, flat=True) 431 exprs = f" TABLE PROPERTIES ({exprs})" if exprs else "" 432 location = self.sql(expression, "location") 433 location = f" LOCATION {location}" if location else "" 434 file_format = self.expressions(expression, key="file_format", flat=True, sep=" ") 435 file_format = f" FILE FORMAT {file_format}" if file_format else "" 436 437 return f"SET{exprs}{location}{file_format}" 438 439 def array_sql(self, expression: exp.Array) -> str: 440 if expression.args.get("bracket_notation"): 441 return super().array_sql(expression) 442 443 return rename_func("ARRAY")(self, expression) 444 445 def explode_sql(self, expression: exp.Explode) -> str: 446 self.unsupported("Unsupported EXPLODE() function") 447 return ""
Specifies the strategy according to which identifiers should be normalized.
Whether the ARRAY constructor is context-sensitive, i.e in Redshift ARRAY[1, 2, 3] != ARRAY(1, 2, 3) as the former is of type INT[] vs the latter which is SUPER
Associates this dialect's time formats with their equivalent Python strftime
formats.
Mapping of an escaped sequence (\n
) to its unescaped version (
).
Inherited Members
- sqlglot.dialects.dialect.Dialect
- Dialect
- WEEK_OFFSET
- UNNEST_COLUMN_ONLY
- ALIAS_POST_TABLESAMPLE
- IDENTIFIERS_CAN_START_WITH_DIGIT
- DPIPE_IS_STRING_CONCAT
- STRICT_STRING_CONCAT
- SUPPORTS_SEMI_ANTI_JOIN
- NORMALIZE_FUNCTIONS
- LOG_BASE_FIRST
- SAFE_DIVISION
- DATE_FORMAT
- DATEINT_FORMAT
- FORMAT_MAPPING
- PSEUDOCOLUMNS
- PREFER_CTE_ALIAS_COLUMN
- FORCE_EARLY_ALIAS_REF_EXPANSION
- EXPAND_ALIAS_REFS_EARLY_ONLY_IN_GROUP_BY
- SUPPORTS_ORDER_BY_ALL
- 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
- ANNOTATORS
- 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
56 class Parser(Postgres.Parser): 57 FUNCTIONS = { 58 **Postgres.Parser.FUNCTIONS, 59 "ADD_MONTHS": lambda args: exp.TsOrDsAdd( 60 this=seq_get(args, 0), 61 expression=seq_get(args, 1), 62 unit=exp.var("month"), 63 return_type=exp.DataType.build("TIMESTAMP"), 64 ), 65 "CONVERT_TIMEZONE": lambda args: build_convert_timezone(args, "UTC"), 66 "DATEADD": _build_date_delta(exp.TsOrDsAdd), 67 "DATE_ADD": _build_date_delta(exp.TsOrDsAdd), 68 "DATEDIFF": _build_date_delta(exp.TsOrDsDiff), 69 "DATE_DIFF": _build_date_delta(exp.TsOrDsDiff), 70 "GETDATE": exp.CurrentTimestamp.from_arg_list, 71 "LISTAGG": exp.GroupConcat.from_arg_list, 72 "SPLIT_TO_ARRAY": lambda args: exp.StringToArray( 73 this=seq_get(args, 0), expression=seq_get(args, 1) or exp.Literal.string(",") 74 ), 75 "STRTOL": exp.FromBase.from_arg_list, 76 } 77 78 NO_PAREN_FUNCTION_PARSERS = { 79 **Postgres.Parser.NO_PAREN_FUNCTION_PARSERS, 80 "APPROXIMATE": lambda self: self._parse_approximate_count(), 81 "SYSDATE": lambda self: self.expression(exp.CurrentTimestamp, sysdate=True), 82 } 83 84 SUPPORTS_IMPLICIT_UNNEST = True 85 86 def _parse_table( 87 self, 88 schema: bool = False, 89 joins: bool = False, 90 alias_tokens: t.Optional[t.Collection[TokenType]] = None, 91 parse_bracket: bool = False, 92 is_db_reference: bool = False, 93 parse_partition: bool = False, 94 ) -> t.Optional[exp.Expression]: 95 # Redshift supports UNPIVOTing SUPER objects, e.g. `UNPIVOT foo.obj[0] AS val AT attr` 96 unpivot = self._match(TokenType.UNPIVOT) 97 table = super()._parse_table( 98 schema=schema, 99 joins=joins, 100 alias_tokens=alias_tokens, 101 parse_bracket=parse_bracket, 102 is_db_reference=is_db_reference, 103 ) 104 105 return self.expression(exp.Pivot, this=table, unpivot=True) if unpivot else table 106 107 def _parse_convert( 108 self, strict: bool, safe: t.Optional[bool] = None 109 ) -> t.Optional[exp.Expression]: 110 to = self._parse_types() 111 self._match(TokenType.COMMA) 112 this = self._parse_bitwise() 113 return self.expression(exp.TryCast, this=this, to=to, safe=safe) 114 115 def _parse_approximate_count(self) -> t.Optional[exp.ApproxDistinct]: 116 index = self._index - 1 117 func = self._parse_function() 118 119 if isinstance(func, exp.Count) and isinstance(func.this, exp.Distinct): 120 return self.expression(exp.ApproxDistinct, this=seq_get(func.this.expressions, 0)) 121 self._retreat(index) 122 return None
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
- TERM
- FACTOR
- TIMES
- TIMESTAMPS
- SET_OPERATIONS
- JOIN_METHODS
- JOIN_SIDES
- JOIN_KINDS
- JOIN_HINTS
- LAMBDAS
- EXPRESSION_PARSERS
- UNARY_PARSERS
- STRING_PARSERS
- NUMERIC_PARSERS
- PRIMARY_PARSERS
- PLACEHOLDER_PARSERS
- CONSTRAINT_PARSERS
- ALTER_PARSERS
- ALTER_ALTER_PARSERS
- SCHEMA_UNNAMED_CONSTRAINTS
- 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
- PRIVILEGE_FOLLOW_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
- COLON_IS_VARIANT_EXTRACT
- VALUES_FOLLOWED_BY_PAREN
- 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
124 class Tokenizer(Postgres.Tokenizer): 125 BIT_STRINGS = [] 126 HEX_STRINGS = [] 127 STRING_ESCAPES = ["\\", "'"] 128 129 KEYWORDS = { 130 **Postgres.Tokenizer.KEYWORDS, 131 "(+)": TokenType.JOIN_MARKER, 132 "HLLSKETCH": TokenType.HLLSKETCH, 133 "MINUS": TokenType.EXCEPT, 134 "SUPER": TokenType.SUPER, 135 "TOP": TokenType.TOP, 136 "UNLOAD": TokenType.COMMAND, 137 "VARBYTE": TokenType.VARBINARY, 138 } 139 KEYWORDS.pop("VALUES") 140 141 # Redshift allows # to appear as a table identifier prefix 142 SINGLE_TOKENS = Postgres.Tokenizer.SINGLE_TOKENS.copy() 143 SINGLE_TOKENS.pop("#")
Inherited Members
145 class Generator(Postgres.Generator): 146 LOCKING_READS_SUPPORTED = False 147 QUERY_HINTS = False 148 VALUES_AS_TABLE = False 149 TZ_TO_WITH_TIME_ZONE = True 150 NVL2_SUPPORTED = True 151 LAST_DAY_SUPPORTS_DATE_PART = False 152 CAN_IMPLEMENT_ARRAY_ANY = False 153 MULTI_ARG_DISTINCT = True 154 COPY_PARAMS_ARE_WRAPPED = False 155 HEX_FUNC = "TO_HEX" 156 PARSE_JSON_NAME = "JSON_PARSE" 157 ARRAY_CONCAT_IS_VAR_LEN = False 158 SUPPORTS_CONVERT_TIMEZONE = True 159 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 160 161 # Redshift doesn't have `WITH` as part of their with_properties so we remove it 162 WITH_PROPERTIES_PREFIX = " " 163 164 TYPE_MAPPING = { 165 **Postgres.Generator.TYPE_MAPPING, 166 exp.DataType.Type.BINARY: "VARBYTE", 167 exp.DataType.Type.INT: "INTEGER", 168 exp.DataType.Type.TIMETZ: "TIME", 169 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 170 exp.DataType.Type.VARBINARY: "VARBYTE", 171 exp.DataType.Type.ROWVERSION: "VARBYTE", 172 } 173 174 TRANSFORMS = { 175 **Postgres.Generator.TRANSFORMS, 176 exp.ArrayConcat: lambda self, e: self.arrayconcat_sql(e, name="ARRAY_CONCAT"), 177 exp.Concat: concat_to_dpipe_sql, 178 exp.ConcatWs: concat_ws_to_dpipe_sql, 179 exp.ApproxDistinct: lambda self, 180 e: f"APPROXIMATE COUNT(DISTINCT {self.sql(e, 'this')})", 181 exp.CurrentTimestamp: lambda self, e: ( 182 "SYSDATE" if e.args.get("sysdate") else "GETDATE()" 183 ), 184 exp.DateAdd: date_delta_sql("DATEADD"), 185 exp.DateDiff: date_delta_sql("DATEDIFF"), 186 exp.DistKeyProperty: lambda self, e: self.func("DISTKEY", e.this), 187 exp.DistStyleProperty: lambda self, e: self.naked_property(e), 188 exp.Explode: lambda self, e: self.explode_sql(e), 189 exp.FromBase: rename_func("STRTOL"), 190 exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql, 191 exp.JSONExtract: json_extract_segments("JSON_EXTRACT_PATH_TEXT"), 192 exp.JSONExtractScalar: json_extract_segments("JSON_EXTRACT_PATH_TEXT"), 193 exp.GroupConcat: rename_func("LISTAGG"), 194 exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))), 195 exp.Select: transforms.preprocess( 196 [ 197 transforms.eliminate_distinct_on, 198 transforms.eliminate_semi_and_anti_joins, 199 transforms.unqualify_unnest, 200 transforms.unnest_generate_date_array_using_recursive_cte, 201 ] 202 ), 203 exp.SortKeyProperty: lambda self, 204 e: f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})", 205 exp.StartsWith: lambda self, 206 e: f"{self.sql(e.this)} LIKE {self.sql(e.expression)} || '%'", 207 exp.StringToArray: rename_func("SPLIT_TO_ARRAY"), 208 exp.TableSample: no_tablesample_sql, 209 exp.TsOrDsAdd: date_delta_sql("DATEADD"), 210 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 211 exp.UnixToTime: lambda self, 212 e: f"(TIMESTAMP 'epoch' + {self.sql(e.this)} * INTERVAL '1 SECOND')", 213 } 214 215 # Postgres maps exp.Pivot to no_pivot_sql, but Redshift support pivots 216 TRANSFORMS.pop(exp.Pivot) 217 218 # Postgres doesn't support JSON_PARSE, but Redshift does 219 TRANSFORMS.pop(exp.ParseJSON) 220 221 # Redshift uses the POW | POWER (expr1, expr2) syntax instead of expr1 ^ expr2 (postgres) 222 TRANSFORMS.pop(exp.Pow) 223 224 # Redshift supports these functions 225 TRANSFORMS.pop(exp.AnyValue) 226 TRANSFORMS.pop(exp.LastDay) 227 TRANSFORMS.pop(exp.SHA2) 228 229 RESERVED_KEYWORDS = { 230 "aes128", 231 "aes256", 232 "all", 233 "allowoverwrite", 234 "analyse", 235 "analyze", 236 "and", 237 "any", 238 "array", 239 "as", 240 "asc", 241 "authorization", 242 "az64", 243 "backup", 244 "between", 245 "binary", 246 "blanksasnull", 247 "both", 248 "bytedict", 249 "bzip2", 250 "case", 251 "cast", 252 "check", 253 "collate", 254 "column", 255 "constraint", 256 "create", 257 "credentials", 258 "cross", 259 "current_date", 260 "current_time", 261 "current_timestamp", 262 "current_user", 263 "current_user_id", 264 "default", 265 "deferrable", 266 "deflate", 267 "defrag", 268 "delta", 269 "delta32k", 270 "desc", 271 "disable", 272 "distinct", 273 "do", 274 "else", 275 "emptyasnull", 276 "enable", 277 "encode", 278 "encrypt ", 279 "encryption", 280 "end", 281 "except", 282 "explicit", 283 "false", 284 "for", 285 "foreign", 286 "freeze", 287 "from", 288 "full", 289 "globaldict256", 290 "globaldict64k", 291 "grant", 292 "group", 293 "gzip", 294 "having", 295 "identity", 296 "ignore", 297 "ilike", 298 "in", 299 "initially", 300 "inner", 301 "intersect", 302 "interval", 303 "into", 304 "is", 305 "isnull", 306 "join", 307 "leading", 308 "left", 309 "like", 310 "limit", 311 "localtime", 312 "localtimestamp", 313 "lun", 314 "luns", 315 "lzo", 316 "lzop", 317 "minus", 318 "mostly16", 319 "mostly32", 320 "mostly8", 321 "natural", 322 "new", 323 "not", 324 "notnull", 325 "null", 326 "nulls", 327 "off", 328 "offline", 329 "offset", 330 "oid", 331 "old", 332 "on", 333 "only", 334 "open", 335 "or", 336 "order", 337 "outer", 338 "overlaps", 339 "parallel", 340 "partition", 341 "percent", 342 "permissions", 343 "pivot", 344 "placing", 345 "primary", 346 "raw", 347 "readratio", 348 "recover", 349 "references", 350 "rejectlog", 351 "resort", 352 "respect", 353 "restore", 354 "right", 355 "select", 356 "session_user", 357 "similar", 358 "snapshot", 359 "some", 360 "sysdate", 361 "system", 362 "table", 363 "tag", 364 "tdes", 365 "text255", 366 "text32k", 367 "then", 368 "timestamp", 369 "to", 370 "top", 371 "trailing", 372 "true", 373 "truncatecolumns", 374 "type", 375 "union", 376 "unique", 377 "unnest", 378 "unpivot", 379 "user", 380 "using", 381 "verbose", 382 "wallet", 383 "when", 384 "where", 385 "with", 386 "without", 387 } 388 389 def unnest_sql(self, expression: exp.Unnest) -> str: 390 args = expression.expressions 391 num_args = len(args) 392 393 if num_args != 1: 394 self.unsupported(f"Unsupported number of arguments in UNNEST: {num_args}") 395 return "" 396 397 if isinstance(expression.find_ancestor(exp.From, exp.Join, exp.Select), exp.Select): 398 self.unsupported("Unsupported UNNEST when not used in FROM/JOIN clauses") 399 return "" 400 401 arg = self.sql(seq_get(args, 0)) 402 403 alias = self.expressions(expression.args.get("alias"), key="columns", flat=True) 404 return f"{arg} AS {alias}" if alias else arg 405 406 def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str: 407 if expression.is_type(exp.DataType.Type.JSON): 408 # Redshift doesn't support a JSON type, so casting to it is treated as a noop 409 return self.sql(expression, "this") 410 411 return super().cast_sql(expression, safe_prefix=safe_prefix) 412 413 def datatype_sql(self, expression: exp.DataType) -> str: 414 """ 415 Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean 416 VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type 417 without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert 418 `TEXT` to `VARCHAR`. 419 """ 420 if expression.is_type("text"): 421 expression.set("this", exp.DataType.Type.VARCHAR) 422 precision = expression.args.get("expressions") 423 424 if not precision: 425 expression.append("expressions", exp.var("MAX")) 426 427 return super().datatype_sql(expression) 428 429 def alterset_sql(self, expression: exp.AlterSet) -> str: 430 exprs = self.expressions(expression, flat=True) 431 exprs = f" TABLE PROPERTIES ({exprs})" if exprs else "" 432 location = self.sql(expression, "location") 433 location = f" LOCATION {location}" if location else "" 434 file_format = self.expressions(expression, key="file_format", flat=True, sep=" ") 435 file_format = f" FILE FORMAT {file_format}" if file_format else "" 436 437 return f"SET{exprs}{location}{file_format}" 438 439 def array_sql(self, expression: exp.Array) -> str: 440 if expression.args.get("bracket_notation"): 441 return super().array_sql(expression) 442 443 return rename_func("ARRAY")(self, expression) 444 445 def explode_sql(self, expression: exp.Explode) -> str: 446 self.unsupported("Unsupported EXPLODE() function") 447 return ""
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
389 def unnest_sql(self, expression: exp.Unnest) -> str: 390 args = expression.expressions 391 num_args = len(args) 392 393 if num_args != 1: 394 self.unsupported(f"Unsupported number of arguments in UNNEST: {num_args}") 395 return "" 396 397 if isinstance(expression.find_ancestor(exp.From, exp.Join, exp.Select), exp.Select): 398 self.unsupported("Unsupported UNNEST when not used in FROM/JOIN clauses") 399 return "" 400 401 arg = self.sql(seq_get(args, 0)) 402 403 alias = self.expressions(expression.args.get("alias"), key="columns", flat=True) 404 return f"{arg} AS {alias}" if alias else arg
406 def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str: 407 if expression.is_type(exp.DataType.Type.JSON): 408 # Redshift doesn't support a JSON type, so casting to it is treated as a noop 409 return self.sql(expression, "this") 410 411 return super().cast_sql(expression, safe_prefix=safe_prefix)
413 def datatype_sql(self, expression: exp.DataType) -> str: 414 """ 415 Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean 416 VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type 417 without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert 418 `TEXT` to `VARCHAR`. 419 """ 420 if expression.is_type("text"): 421 expression.set("this", exp.DataType.Type.VARCHAR) 422 precision = expression.args.get("expressions") 423 424 if not precision: 425 expression.append("expressions", exp.var("MAX")) 426 427 return super().datatype_sql(expression)
Redshift converts the TEXT
data type to VARCHAR(255)
by default when people more generally mean
VARCHAR of max length which is VARCHAR(max)
in Redshift. Therefore if we get a TEXT
data type
without precision we convert it to VARCHAR(max)
and if it does have precision then we just convert
TEXT
to VARCHAR
.
429 def alterset_sql(self, expression: exp.AlterSet) -> str: 430 exprs = self.expressions(expression, flat=True) 431 exprs = f" TABLE PROPERTIES ({exprs})" if exprs else "" 432 location = self.sql(expression, "location") 433 location = f" LOCATION {location}" if location else "" 434 file_format = self.expressions(expression, key="file_format", flat=True, sep=" ") 435 file_format = f" FILE FORMAT {file_format}" if file_format else "" 436 437 return f"SET{exprs}{location}{file_format}"
Inherited Members
- sqlglot.generator.Generator
- Generator
- NULL_ORDERING_SUPPORTED
- IGNORE_NULLS_IN_FUNC
- WRAP_DERIVED_VALUES
- CREATE_FUNCTION_RETURN_AS
- MATCHED_BY_SOURCE
- INTERVAL_ALLOWS_PLURAL_FORM
- LIMIT_FETCH
- LIMIT_ONLY_LITERALS
- GROUPINGS_SEP
- INDEX_ON
- QUERY_HINT_SEP
- IS_BOOL_ALLOWED
- DUPLICATE_KEY_UPDATE_WITH_SET
- LIMIT_IS_TOP
- RETURNING_END
- EXTRACT_ALLOWS_QUOTES
- 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_KEYWORDS
- TABLESAMPLE_WITH_METHOD
- COLLATE_IS_FUNC
- DATA_TYPE_SPECIFIERS_ALLOWED
- ENSURE_BOOLS
- CTE_RECURSIVE_KEYWORD_REQUIRED
- SUPPORTS_SINGLE_ARG_CONCAT
- SUPPORTS_TABLE_ALIAS_COLUMNS
- UNPIVOT_ALIASES_ARE_IDENTIFIERS
- JSON_KEY_VALUE_PAIR_SEP
- INSERT_OVERWRITE
- SUPPORTS_CREATE_TABLE_LIKE
- JSON_PATH_BRACKETED_KEY_SUPPORTED
- JSON_PATH_SINGLE_QUOTE_ESCAPE
- SUPPORTS_TO_NUMBER
- SET_OP_MODIFIERS
- COPY_PARAMS_EQ_REQUIRED
- STAR_EXCEPT
- QUOTE_JSON_PATH
- PAD_FILL_PATTERN_IS_REQUIRED
- SUPPORTS_EXPLODING_PROJECTIONS
- TIME_PART_SINGULARS
- TOKEN_MAPPING
- STRUCT_DELIMITER
- 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
- create_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
- directory_sql
- delete_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
- offset_limit_modifiers
- after_limit_modifiers
- select_sql
- schema_sql
- schema_columns_sql
- star_sql
- parameter_sql
- sessionparameter_sql
- placeholder_sql
- subquery_sql
- qualify_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
- 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
- interval_sql
- 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
- currentdate_sql
- collate_sql
- command_sql
- comment_sql
- mergetreettlaction_sql
- mergetreettl_sql
- transaction_sql
- commit_sql
- rollback_sql
- altercolumn_sql
- alterdiststyle_sql
- altersortkey_sql
- renametable_sql
- renamecolumn_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
- struct_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
- apply_sql
- grant_sql
- grantprivilege_sql
- grantprincipal_sql
- columns_sql
- overlay_sql
- sqlglot.dialects.postgres.Postgres.Generator
- SINGLE_STRING_INTERVAL
- RENAME_TABLE_WITH_DB
- JOIN_HINTS
- TABLE_HINTS
- PARAMETER_TOKEN
- TABLESAMPLE_SIZE_IS_ROWS
- TABLESAMPLE_SEED_KEYWORD
- SUPPORTS_SELECT_INTO
- JSON_TYPE_REQUIRED_FOR_EXTRACTION
- SUPPORTS_UNLOGGED_TABLES
- LIKE_PROPERTY_INSIDE_SCHEMA
- COPY_HAS_INTO_KEYWORD
- SUPPORTED_JSON_PATH_PARTS
- PROPERTIES_LOCATION
- schemacommentproperty_sql
- commentcolumnconstraint_sql
- bracket_sql
- matchagainst_sql