sqlglot.generators.redshift

  1from __future__ import annotations
  2
  3
  4from sqlglot import exp, transforms
  5from sqlglot.dialects.dialect import (
  6    array_concat_sql,
  7    concat_to_dpipe_sql,
  8    concat_ws_to_dpipe_sql,
  9    date_delta_sql,
 10    generatedasidentitycolumnconstraint_sql,
 11    json_extract_segments,
 12    no_tablesample_sql,
 13    rename_func,
 14)
 15from sqlglot.generator import Generator
 16from sqlglot.generators.postgres import PostgresGenerator
 17from sqlglot.helper import seq_get
 18
 19
 20class RedshiftGenerator(PostgresGenerator):
 21    LOCKING_READS_SUPPORTED = False
 22    QUERY_HINTS = False
 23    CAST_TO_INT_ROUNDS = True
 24    VALUES_AS_TABLE = False
 25    TZ_TO_WITH_TIME_ZONE = True
 26    NVL2_SUPPORTED = True
 27    LAST_DAY_SUPPORTS_DATE_PART = False
 28    CAN_IMPLEMENT_ARRAY_ANY = False
 29    MULTI_ARG_DISTINCT = True
 30    COPY_PARAMS_ARE_WRAPPED = False
 31    HEX_FUNC = "TO_HEX"
 32    PARSE_JSON_NAME: str | None = "JSON_PARSE"
 33    ARRAY_CONCAT_IS_VAR_LEN = False
 34    SUPPORTS_CONVERT_TIMEZONE = True
 35    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
 36    SUPPORTS_MEDIAN = True
 37    ALTER_SET_TYPE = "TYPE"
 38    SUPPORTS_DECODE_CASE = True
 39    SUPPORTS_BETWEEN_FLAGS = False
 40    LIMIT_FETCH = "LIMIT"
 41    STAR_EXCEPT = "EXCLUDE"
 42    STAR_EXCLUDE_REQUIRES_DERIVED_TABLE = False
 43
 44    # Redshift doesn't have `WITH` as part of their with_properties so we remove it
 45    WITH_PROPERTIES_PREFIX = " "
 46
 47    PROPERTIES_LOCATION = {
 48        **PostgresGenerator.PROPERTIES_LOCATION,
 49        exp.DistKeyProperty: exp.Properties.Location.POST_SCHEMA,
 50        exp.DistStyleProperty: exp.Properties.Location.POST_SCHEMA,
 51        exp.SortKeyProperty: exp.Properties.Location.POST_SCHEMA,
 52    }
 53
 54    TYPE_MAPPING = {
 55        **PostgresGenerator.TYPE_MAPPING,
 56        exp.DType.BINARY: "VARBYTE",
 57        exp.DType.BLOB: "VARBYTE",
 58        exp.DType.INT: "INTEGER",
 59        exp.DType.TIMETZ: "TIME",
 60        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
 61        exp.DType.VARBINARY: "VARBYTE",
 62        exp.DType.ROWVERSION: "VARBYTE",
 63    }
 64
 65    TRANSFORMS = {
 66        **{
 67            k: v
 68            for k, v in PostgresGenerator.TRANSFORMS.items()
 69            if k
 70            not in {
 71                exp.Pivot,
 72                exp.ParseJSON,
 73                exp.AnyValue,
 74                exp.LastDay,
 75                exp.SHA2,
 76                exp.Getbit,
 77                exp.IntDiv,
 78                exp.Round,
 79                exp.TryCast,
 80            }
 81        },
 82        exp.ArrayConcat: array_concat_sql("ARRAY_CONCAT"),
 83        exp.Concat: concat_to_dpipe_sql,
 84        exp.ConcatWs: concat_ws_to_dpipe_sql,
 85        exp.ApproxDistinct: lambda self, e: f"APPROXIMATE COUNT(DISTINCT {self.sql(e, 'this')})",
 86        exp.CurrentTimestamp: lambda self, e: "SYSDATE" if e.args.get("sysdate") else "GETDATE()",
 87        exp.CurrentUserId: lambda *_: "CURRENT_USER_ID",
 88        exp.DateAdd: date_delta_sql("DATEADD"),
 89        exp.DateDiff: date_delta_sql("DATEDIFF"),
 90        exp.DistKeyProperty: lambda self, e: self.func("DISTKEY", e.this),
 91        exp.DistStyleProperty: lambda self, e: self.naked_property(e),
 92        exp.Explode: lambda self, e: self.explode_sql(e),
 93        exp.FarmFingerprint: rename_func("FARMFINGERPRINT64"),
 94        exp.FromBase: rename_func("STRTOL"),
 95        exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql,
 96        exp.JSONExtract: json_extract_segments("JSON_EXTRACT_PATH_TEXT"),
 97        exp.JSONExtractScalar: json_extract_segments("JSON_EXTRACT_PATH_TEXT"),
 98        exp.GroupConcat: rename_func("LISTAGG"),
 99        exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))),
100        exp.RegexpExtract: rename_func("REGEXP_SUBSTR"),
101        exp.Select: transforms.preprocess(
102            [
103                transforms.eliminate_window_clause,
104                transforms.eliminate_distinct_on,
105                transforms.eliminate_semi_and_anti_joins,
106                transforms.unqualify_unnest,
107                transforms.unnest_generate_date_array_using_recursive_cte,
108            ]
109        ),
110        exp.SortKeyProperty: lambda self, e: (
111            f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})"
112        ),
113        exp.StartsWith: lambda self, e: f"{self.sql(e.this)} LIKE {self.sql(e.expression)} || '%'",
114        exp.StringToArray: rename_func("SPLIT_TO_ARRAY"),
115        exp.TableSample: no_tablesample_sql,
116        exp.TsOrDsAdd: date_delta_sql("DATEADD"),
117        exp.TsOrDsDiff: date_delta_sql("DATEDIFF"),
118        exp.UnixToTime: lambda self, e: self._unix_to_time_sql(e),
119        exp.SHA2Digest: lambda self, e: self.func(
120            "SHA2", e.this, e.args.get("length") or exp.Literal.number(256)
121        ),
122    }
123
124    RESERVED_KEYWORDS = {
125        "aes128",
126        "aes256",
127        "all",
128        "allowoverwrite",
129        "analyse",
130        "analyze",
131        "and",
132        "any",
133        "array",
134        "as",
135        "asc",
136        "authorization",
137        "az64",
138        "backup",
139        "between",
140        "binary",
141        "blanksasnull",
142        "both",
143        "bytedict",
144        "bzip2",
145        "case",
146        "cast",
147        "check",
148        "collate",
149        "column",
150        "constraint",
151        "create",
152        "credentials",
153        "cross",
154        "current_date",
155        "current_time",
156        "current_timestamp",
157        "current_user",
158        "current_user_id",
159        "default",
160        "deferrable",
161        "deflate",
162        "defrag",
163        "delta",
164        "delta32k",
165        "desc",
166        "disable",
167        "distinct",
168        "do",
169        "else",
170        "emptyasnull",
171        "enable",
172        "encode",
173        "encrypt     ",
174        "encryption",
175        "end",
176        "except",
177        "explicit",
178        "false",
179        "for",
180        "foreign",
181        "freeze",
182        "from",
183        "full",
184        "globaldict256",
185        "globaldict64k",
186        "grant",
187        "group",
188        "gzip",
189        "having",
190        "identity",
191        "ignore",
192        "ilike",
193        "in",
194        "initially",
195        "inner",
196        "intersect",
197        "interval",
198        "into",
199        "is",
200        "isnull",
201        "join",
202        "leading",
203        "left",
204        "like",
205        "limit",
206        "localtime",
207        "localtimestamp",
208        "lun",
209        "luns",
210        "lzo",
211        "lzop",
212        "minus",
213        "mostly16",
214        "mostly32",
215        "mostly8",
216        "natural",
217        "new",
218        "not",
219        "notnull",
220        "null",
221        "nulls",
222        "off",
223        "offline",
224        "offset",
225        "oid",
226        "old",
227        "on",
228        "only",
229        "open",
230        "or",
231        "order",
232        "outer",
233        "overlaps",
234        "parallel",
235        "partition",
236        "percent",
237        "permissions",
238        "pivot",
239        "placing",
240        "primary",
241        "raw",
242        "readratio",
243        "recover",
244        "references",
245        "rejectlog",
246        "resort",
247        "respect",
248        "restore",
249        "right",
250        "select",
251        "session_user",
252        "similar",
253        "snapshot",
254        "some",
255        "sysdate",
256        "system",
257        "table",
258        "tag",
259        "tdes",
260        "text255",
261        "text32k",
262        "then",
263        "timestamp",
264        "to",
265        "top",
266        "trailing",
267        "true",
268        "truncatecolumns",
269        "type",
270        "union",
271        "unique",
272        "unnest",
273        "unpivot",
274        "user",
275        "using",
276        "verbose",
277        "wallet",
278        "when",
279        "where",
280        "with",
281        "without",
282    }
283
284    def bytestring_sql(self, expression: exp.ByteString) -> str:
285        if not expression.args.get("is_bytes"):
286            return self.sql(exp.Literal.string(expression.this))
287
288        return self.func("FROM_HEX", exp.Literal.string(expression.this.encode("latin-1").hex()))
289
290    def stpoint_sql(self, expression: exp.StPoint) -> str:
291        # ST_POINT only accepts 2 args in Redshift; use ST_MAKEPOINT for 3 or 4 args
292        if expression.args.get("z") or expression.args.get("m"):
293            return self.func(
294                "ST_MAKEPOINT",
295                expression.this,
296                expression.expression,
297                expression.args.get("z"),
298                expression.args.get("m"),
299            )
300        return self.func("ST_POINT", expression.this, expression.expression)
301
302    def arraycontains_sql(self, expression: exp.ArrayContains) -> str:
303        return self.func(
304            "ARRAY_CONTAINS",
305            expression.this,
306            expression.expression,
307            expression.args.get("check_null"),
308        )
309
310    def objecttransform_sql(self, expression: exp.ObjectTransform) -> str:
311        this = self.sql(expression, "this")
312        keep = self.expressions(expression, key="keep", flat=True)
313        set_ = self.expressions(expression, key="set_", flat=True)
314        keep_sql = " KEEP " + keep if keep else ""
315        set_sql = " SET " + set_ if set_ else ""
316        return f"OBJECT_TRANSFORM({this}{keep_sql}{set_sql})"
317
318    def approxquantile_sql(self, expression: exp.ApproxQuantile) -> str:
319        return "APPROXIMATE " + self.sql(
320            exp.WithinGroup(
321                this=exp.PercentileDisc(this=expression.args["quantile"]),
322                expression=exp.Order(expressions=[exp.Ordered(this=expression.this)]),
323            )
324        )
325
326    def unnest_sql(self, expression: exp.Unnest) -> str:
327        args = expression.expressions
328        num_args = len(args)
329
330        if num_args != 1:
331            self.unsupported(f"Unsupported number of arguments in UNNEST: {num_args}")
332            return ""
333
334        if isinstance(expression.find_ancestor(exp.From, exp.Join, exp.Select), exp.Select):
335            self.unsupported("Unsupported UNNEST when not used in FROM/JOIN clauses")
336            return ""
337
338        arg = self.sql(seq_get(args, 0))
339
340        alias = self.expressions(expression.args.get("alias"), key="columns", flat=True)
341        return f"{arg} AS {alias}" if alias else arg
342
343    def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
344        if expression.is_type(exp.DType.JSON):
345            # Redshift doesn't support a JSON type, so casting to it is treated as a noop
346            return self.sql(expression, "this")
347
348        return super().cast_sql(expression, safe_prefix=safe_prefix)
349
350    def datatype_sql(self, expression: exp.DataType) -> str:
351        """
352        Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean
353        VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type
354        without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert
355        `TEXT` to `VARCHAR`.
356        """
357        if expression.is_type("text"):
358            expression.set("this", exp.DType.VARCHAR)
359            precision = expression.args.get("expressions")
360
361            if not precision:
362                expression.append("expressions", exp.var("MAX"))
363
364        return super().datatype_sql(expression)
365
366    def alterset_sql(self, expression: exp.AlterSet) -> str:
367        exprs = self.expressions(expression, flat=True)
368        exprs = f" TABLE PROPERTIES ({exprs})" if exprs else ""
369        location = self.sql(expression, "location")
370        location = f" LOCATION {location}" if location else ""
371        file_format = self.expressions(expression, key="file_format", flat=True, sep=" ")
372        file_format = f" FILE FORMAT {file_format}" if file_format else ""
373
374        return f"SET{exprs}{location}{file_format}"
375
376    def array_sql(self, expression: exp.Array) -> str:
377        if expression.args.get("bracket_notation"):
378            return super().array_sql(expression)
379
380        return rename_func("ARRAY")(self, expression)
381
382    def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
383        return Generator.ignorenulls_sql(self, expression)
384
385    def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
386        return Generator.respectnulls_sql(self, expression)
387
388    def explode_sql(self, expression: exp.Explode) -> str:
389        self.unsupported("Unsupported EXPLODE() function")
390        return ""
391
392    def _unix_to_time_sql(self, expression: exp.UnixToTime) -> str:
393        scale = expression.args.get("scale")
394        this = self.sql(expression.this)
395
396        if scale is not None and scale != exp.UnixToTime.SECONDS and scale.is_int:
397            this = f"({this} / POWER(10, {scale.to_py()}))"
398
399        return f"(TIMESTAMP 'epoch' + {this} * INTERVAL '1 SECOND')"