sqlglot.dialects.snowflake

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

120    class JSONPathTokenizer(jsonpath.JSONPathTokenizer):
121        SINGLE_TOKENS = jsonpath.JSONPathTokenizer.SINGLE_TOKENS.copy()
122        SINGLE_TOKENS.pop("$")

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