sqlglot.dialects.snowflake

 17class Snowflake(Dialect):
 18    # https://docs.snowflake.com/en/sql-reference/identifiers-syntax
 19    NORMALIZATION_STRATEGY = NormalizationStrategy.UPPERCASE
 20    # https://docs.snowflake.com/en/sql-reference/data-types-text#escape-sequences
 21    UNESCAPED_SEQUENCES = {"\\a": "a", "\\v": "v"}
 22    NULL_ORDERING = "nulls_are_large"
 23    TIME_FORMAT = "'YYYY-MM-DD HH24:MI:SS'"
 24    SUPPORTS_USER_DEFINED_TYPES = False
 25    PREFER_CTE_ALIAS_COLUMN = True
 26    SUPPORTS_POSITIONAL_COLUMN_REFS = True
 27    TABLESAMPLE_SIZE_IS_PERCENT = True
 28    COPY_PARAMS_ARE_CSV = False
 29    ARRAY_AGG_INCLUDES_NULLS = None
 30    ARRAY_FUNCS_PROPAGATES_NULLS = True
 31    ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN = False
 32    ALTER_TABLE_DROP_REQUIRED_FOR_EACH_COLUMN = False
 33    TRY_CAST_REQUIRES_STRING = True
 34    SUPPORTS_ALIAS_REFS_IN_JOIN_CONDITIONS = True
 35    LEAST_GREATEST_IGNORES_NULLS = False
 36    UUID_IS_STRING_TYPE = True
 37    STAR_ILIKE_BACKSLASH_ESCAPE = True
 38
 39    EXPRESSION_METADATA = EXPRESSION_METADATA.copy()
 40
 41    # https://docs.snowflake.com/en/en/sql-reference/functions/initcap
 42    INITCAP_DEFAULT_DELIMITER_CHARS = ' \t\n\r\f\v!?@"^#$&~_,.:;+\\-*%/|\\[\\](){}<>'
 43
 44    INVERSE_TIME_MAPPING = {
 45        "T": "T",  # in TIME_MAPPING we map '"T"' with the double quotes to 'T', and we want to prevent 'T' from being mapped back to '"T"' so that 'AUTO' doesn't become 'AU"T"O'
 46    }
 47
 48    TIME_MAPPING = {
 49        "YYYY": "%Y",
 50        "yyyy": "%Y",
 51        "YY": "%y",
 52        "yy": "%y",
 53        "MMMM": "%B",
 54        "mmmm": "%B",
 55        "MON": "%b",
 56        "mon": "%b",
 57        "MM": "%m",
 58        "mm": "%m",
 59        "DD": "%d",
 60        "dd": "%-d",
 61        "DY": "%a",
 62        "dy": "%w",
 63        "HH24": "%H",
 64        "hh24": "%H",
 65        "HH12": "%I",
 66        "hh12": "%I",
 67        "MI": "%M",
 68        "mi": "%M",
 69        "SS": "%S",
 70        "ss": "%S",
 71        "FF": "%f_nine",  # %f_ internal representation with precision specified
 72        "ff": "%f_nine",
 73        "FF0": "%f_zero",
 74        "ff0": "%f_zero",
 75        "FF1": "%f_one",
 76        "ff1": "%f_one",
 77        "FF2": "%f_two",
 78        "ff2": "%f_two",
 79        "FF3": "%f_three",
 80        "ff3": "%f_three",
 81        "FF4": "%f_four",
 82        "ff4": "%f_four",
 83        "FF5": "%f_five",
 84        "ff5": "%f_five",
 85        "FF6": "%f",
 86        "ff6": "%f",
 87        "FF7": "%f_seven",
 88        "ff7": "%f_seven",
 89        "FF8": "%f_eight",
 90        "ff8": "%f_eight",
 91        "FF9": "%f_nine",
 92        "ff9": "%f_nine",
 93        "TZHTZM": "%z",
 94        "tzhtzm": "%z",
 95        "TZH:TZM": "%:z",  # internal representation for ±HH:MM
 96        "tzh:tzm": "%:z",
 97        "TZH": "%-z",  # internal representation ±HH
 98        "tzh": "%-z",
 99        '"T"': "T",  # remove the optional double quotes around the separator between the date and time
100        # Seems like Snowflake treats AM/PM in the format string as equivalent,
101        # only the time (stamp) value's AM/PM affects the output
102        "AM": "%p",
103        "am": "%p",
104        "PM": "%p",
105        "pm": "%p",
106    }
107
108    DATE_PART_MAPPING = {
109        **Dialect.DATE_PART_MAPPING,
110        "ISOWEEK": "WEEKISO",
111        # The base Dialect maps EPOCH_SECOND -> EPOCH, but we need to preserve
112        # EPOCH_SECOND as a distinct value for two reasons:
113        # 1. Type annotation: EPOCH_SECOND returns BIGINT, while EPOCH returns DOUBLE
114        # 2. Transpilation: DuckDB's EPOCH() returns float, so we cast EPOCH_SECOND
115        #    to BIGINT to match Snowflake's integer behavior
116        # Without this override, EXTRACT(EPOCH_SECOND FROM ts) would be normalized
117        # to EXTRACT(EPOCH FROM ts) and lose the integer semantics.
118        "EPOCH_SECOND": "EPOCH_SECOND",
119        "EPOCH_SECONDS": "EPOCH_SECOND",
120    }
121
122    PSEUDOCOLUMNS = {"LEVEL"}
123
124    def can_quote(self, identifier: exp.Identifier, identify: str | bool = "safe") -> bool:
125        # This disables quoting DUAL in SELECT ... FROM DUAL, because Snowflake treats an
126        # unquoted DUAL keyword in a special way and does not map it to a user-defined table
127        return super().can_quote(identifier, identify) and not (
128            isinstance(identifier.parent, exp.Table)
129            and not identifier.quoted
130            and identifier.name.lower() == "dual"
131        )
132
133    class JSONPathTokenizer(jsonpath.JSONPathTokenizer):
134        SINGLE_TOKENS = jsonpath.JSONPathTokenizer.SINGLE_TOKENS.copy()
135        SINGLE_TOKENS.pop("$")
136
137    Parser = SnowflakeParser
138
139    class Tokenizer(tokens.Tokenizer):
140        STRING_ESCAPES = ["\\", "'"]
141        NUMERIC_ESCAPES = {"x": (16, 2, 2, 0xFF), "u": (16, 4, 4, 0xFFFF), "0": (8, 1, 3, 0xFF)}
142        DROP_UNKNOWN_ESCAPES = True
143        HEX_STRINGS = [("x'", "'"), ("X'", "'")]
144        RAW_STRINGS = ["$$"]
145        COMMENTS = ["--", "//", ("/*", "*/")]
146        NESTED_COMMENTS = False
147
148        KEYWORDS = {
149            **tokens.Tokenizer.KEYWORDS,
150            "(+)": TokenType.JOIN_MARKER,
151            "BYTEINT": TokenType.INT,
152            "FILE://": TokenType.URI_START,
153            "FILE FORMAT": TokenType.FILE_FORMAT,
154            "GET": TokenType.GET,
155            "INTEGRATION": TokenType.INTEGRATION,
156            "LS": TokenType.LIST,
157            "MATCH_CONDITION": TokenType.MATCH_CONDITION,
158            "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
159            "MINUS": TokenType.EXCEPT,
160            "NCHAR VARYING": TokenType.VARCHAR,
161            "PACKAGE": TokenType.PACKAGE,
162            "POLICY": TokenType.POLICY,
163            "POOL": TokenType.POOL,
164            "PUT": TokenType.PUT,
165            "UNDROP": TokenType.UNDROP,
166            "REMOVE": TokenType.COMMAND,
167            "RM": TokenType.COMMAND,
168            "ROLE": TokenType.ROLE,
169            "RULE": TokenType.RULE,
170            "SAMPLE": TokenType.TABLE_SAMPLE,
171            "SEMANTIC VIEW": TokenType.SEMANTIC_VIEW,
172            "SQL_DOUBLE": TokenType.DOUBLE,
173            "SQL_VARCHAR": TokenType.VARCHAR,
174            "STAGE": TokenType.STAGE,
175            "STORAGE INTEGRATION": TokenType.STORAGE_INTEGRATION,
176            "STREAMLIT": TokenType.STREAMLIT,
177            "TAG": TokenType.TAG,
178            "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ,
179            "TOP": TokenType.TOP,
180            "VOLUME": TokenType.VOLUME,
181            "WAREHOUSE": TokenType.WAREHOUSE,
182            # https://docs.snowflake.com/en/sql-reference/data-types-numeric#float
183            # FLOAT is a synonym for DOUBLE in Snowflake
184            "FLOAT": TokenType.DOUBLE,
185        }
186        KEYWORDS.pop("/*+")
187
188        SINGLE_TOKENS = {
189            **tokens.Tokenizer.SINGLE_TOKENS,
190            "$": TokenType.PARAMETER,
191            "!": TokenType.EXCLAMATION,
192        }
193
194        VAR_SINGLE_TOKENS = {"$"}
195
196        COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}
197
198    Generator = SnowflakeGenerator

133    class JSONPathTokenizer(jsonpath.JSONPathTokenizer):
134        SINGLE_TOKENS = jsonpath.JSONPathTokenizer.SINGLE_TOKENS.copy()
135        SINGLE_TOKENS.pop("$")

139    class Tokenizer(tokens.Tokenizer):
140        STRING_ESCAPES = ["\\", "'"]
141        NUMERIC_ESCAPES = {"x": (16, 2, 2, 0xFF), "u": (16, 4, 4, 0xFFFF), "0": (8, 1, 3, 0xFF)}
142        DROP_UNKNOWN_ESCAPES = True
143        HEX_STRINGS = [("x'", "'"), ("X'", "'")]
144        RAW_STRINGS = ["$$"]
145        COMMENTS = ["--", "//", ("/*", "*/")]
146        NESTED_COMMENTS = False
147
148        KEYWORDS = {
149            **tokens.Tokenizer.KEYWORDS,
150            "(+)": TokenType.JOIN_MARKER,
151            "BYTEINT": TokenType.INT,
152            "FILE://": TokenType.URI_START,
153            "FILE FORMAT": TokenType.FILE_FORMAT,
154            "GET": TokenType.GET,
155            "INTEGRATION": TokenType.INTEGRATION,
156            "LS": TokenType.LIST,
157            "MATCH_CONDITION": TokenType.MATCH_CONDITION,
158            "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
159            "MINUS": TokenType.EXCEPT,
160            "NCHAR VARYING": TokenType.VARCHAR,
161            "PACKAGE": TokenType.PACKAGE,
162            "POLICY": TokenType.POLICY,
163            "POOL": TokenType.POOL,
164            "PUT": TokenType.PUT,
165            "UNDROP": TokenType.UNDROP,
166            "REMOVE": TokenType.COMMAND,
167            "RM": TokenType.COMMAND,
168            "ROLE": TokenType.ROLE,
169            "RULE": TokenType.RULE,
170            "SAMPLE": TokenType.TABLE_SAMPLE,
171            "SEMANTIC VIEW": TokenType.SEMANTIC_VIEW,
172            "SQL_DOUBLE": TokenType.DOUBLE,
173            "SQL_VARCHAR": TokenType.VARCHAR,
174            "STAGE": TokenType.STAGE,
175            "STORAGE INTEGRATION": TokenType.STORAGE_INTEGRATION,
176            "STREAMLIT": TokenType.STREAMLIT,
177            "TAG": TokenType.TAG,
178            "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ,
179            "TOP": TokenType.TOP,
180            "VOLUME": TokenType.VOLUME,
181            "WAREHOUSE": TokenType.WAREHOUSE,
182            # https://docs.snowflake.com/en/sql-reference/data-types-numeric#float
183            # FLOAT is a synonym for DOUBLE in Snowflake
184            "FLOAT": TokenType.DOUBLE,
185        }
186        KEYWORDS.pop("/*+")
187
188        SINGLE_TOKENS = {
189            **tokens.Tokenizer.SINGLE_TOKENS,
190            "$": TokenType.PARAMETER,
191            "!": TokenType.EXCLAMATION,
192        }
193
194        VAR_SINGLE_TOKENS = {"$"}
195
196        COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}