sqlglot.dialects.snowflake
1from __future__ import annotations 2 3from sqlglot import exp, generator, parser, tokens 4from sqlglot.dialects.dialect import ( 5 Dialect, 6 datestrtodate_sql, 7 format_time_lambda, 8 inline_array_sql, 9 rename_func, 10 timestrtotime_sql, 11 var_map_sql, 12) 13from sqlglot.expressions import Literal 14from sqlglot.helper import flatten, seq_get 15from sqlglot.tokens import TokenType 16 17 18def _check_int(s): 19 if s[0] in ("-", "+"): 20 return s[1:].isdigit() 21 return s.isdigit() 22 23 24# from https://docs.snowflake.com/en/sql-reference/functions/to_timestamp.html 25def _snowflake_to_timestamp(args): 26 if len(args) == 2: 27 first_arg, second_arg = args 28 if second_arg.is_string: 29 # case: <string_expr> [ , <format> ] 30 return format_time_lambda(exp.StrToTime, "snowflake")(args) 31 32 # case: <numeric_expr> [ , <scale> ] 33 if second_arg.name not in ["0", "3", "9"]: 34 raise ValueError( 35 f"Scale for snowflake numeric timestamp is {second_arg}, but should be 0, 3, or 9" 36 ) 37 38 if second_arg.name == "0": 39 timescale = exp.UnixToTime.SECONDS 40 elif second_arg.name == "3": 41 timescale = exp.UnixToTime.MILLIS 42 elif second_arg.name == "9": 43 timescale = exp.UnixToTime.MICROS 44 45 return exp.UnixToTime(this=first_arg, scale=timescale) 46 47 first_arg = seq_get(args, 0) 48 if not isinstance(first_arg, Literal): 49 # case: <variant_expr> 50 return format_time_lambda(exp.StrToTime, "snowflake", default=True)(args) 51 52 if first_arg.is_string: 53 if _check_int(first_arg.this): 54 # case: <integer> 55 return exp.UnixToTime.from_arg_list(args) 56 57 # case: <date_expr> 58 return format_time_lambda(exp.StrToTime, "snowflake", default=True)(args) 59 60 # case: <numeric_expr> 61 return exp.UnixToTime.from_arg_list(args) 62 63 64def _unix_to_time_sql(self, expression): 65 scale = expression.args.get("scale") 66 timestamp = self.sql(expression, "this") 67 if scale in [None, exp.UnixToTime.SECONDS]: 68 return f"TO_TIMESTAMP({timestamp})" 69 if scale == exp.UnixToTime.MILLIS: 70 return f"TO_TIMESTAMP({timestamp}, 3)" 71 if scale == exp.UnixToTime.MICROS: 72 return f"TO_TIMESTAMP({timestamp}, 9)" 73 74 raise ValueError("Improper scale for timestamp") 75 76 77# https://docs.snowflake.com/en/sql-reference/functions/date_part.html 78# https://docs.snowflake.com/en/sql-reference/functions-date-time.html#label-supported-date-time-parts 79def _parse_date_part(self): 80 this = self._parse_var() or self._parse_type() 81 self._match(TokenType.COMMA) 82 expression = self._parse_bitwise() 83 84 name = this.name.upper() 85 if name.startswith("EPOCH"): 86 if name.startswith("EPOCH_MILLISECOND"): 87 scale = 10**3 88 elif name.startswith("EPOCH_MICROSECOND"): 89 scale = 10**6 90 elif name.startswith("EPOCH_NANOSECOND"): 91 scale = 10**9 92 else: 93 scale = None 94 95 ts = self.expression(exp.Cast, this=expression, to=exp.DataType.build("TIMESTAMP")) 96 to_unix = self.expression(exp.TimeToUnix, this=ts) 97 98 if scale: 99 to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale)) 100 101 return to_unix 102 103 return self.expression(exp.Extract, this=this, expression=expression) 104 105 106def _datatype_sql(self, expression): 107 if expression.this == exp.DataType.Type.ARRAY: 108 return "ARRAY" 109 elif expression.this == exp.DataType.Type.MAP: 110 return "OBJECT" 111 return self.datatype_sql(expression) 112 113 114class Snowflake(Dialect): 115 null_ordering = "nulls_are_large" 116 time_format = "'yyyy-mm-dd hh24:mi:ss'" 117 118 time_mapping = { 119 "YYYY": "%Y", 120 "yyyy": "%Y", 121 "YY": "%y", 122 "yy": "%y", 123 "MMMM": "%B", 124 "mmmm": "%B", 125 "MON": "%b", 126 "mon": "%b", 127 "MM": "%m", 128 "mm": "%m", 129 "DD": "%d", 130 "dd": "%d", 131 "d": "%-d", 132 "DY": "%w", 133 "dy": "%w", 134 "HH24": "%H", 135 "hh24": "%H", 136 "HH12": "%I", 137 "hh12": "%I", 138 "MI": "%M", 139 "mi": "%M", 140 "SS": "%S", 141 "ss": "%S", 142 "FF": "%f", 143 "ff": "%f", 144 "FF6": "%f", 145 "ff6": "%f", 146 } 147 148 class Parser(parser.Parser): 149 FUNCTIONS = { 150 **parser.Parser.FUNCTIONS, 151 "ARRAYAGG": exp.ArrayAgg.from_arg_list, 152 "IFF": exp.If.from_arg_list, 153 "TO_TIMESTAMP": _snowflake_to_timestamp, 154 "ARRAY_CONSTRUCT": exp.Array.from_arg_list, 155 "RLIKE": exp.RegexpLike.from_arg_list, 156 "DECODE": exp.Matches.from_arg_list, 157 "OBJECT_CONSTRUCT": parser.parse_var_map, 158 } 159 160 FUNCTION_PARSERS = { 161 **parser.Parser.FUNCTION_PARSERS, 162 "DATE_PART": _parse_date_part, 163 } 164 FUNCTION_PARSERS.pop("TRIM") 165 166 FUNC_TOKENS = { 167 *parser.Parser.FUNC_TOKENS, 168 TokenType.RLIKE, 169 TokenType.TABLE, 170 } 171 172 COLUMN_OPERATORS = { 173 **parser.Parser.COLUMN_OPERATORS, # type: ignore 174 TokenType.COLON: lambda self, this, path: self.expression( 175 exp.Bracket, 176 this=this, 177 expressions=[path], 178 ), 179 } 180 181 RANGE_PARSERS = { 182 **parser.Parser.RANGE_PARSERS, # type: ignore 183 TokenType.LIKE_ANY: lambda self, this: self._parse_escape( 184 self.expression(exp.LikeAny, this=this, expression=self._parse_bitwise()) 185 ), 186 TokenType.ILIKE_ANY: lambda self, this: self._parse_escape( 187 self.expression(exp.ILikeAny, this=this, expression=self._parse_bitwise()) 188 ), 189 } 190 191 class Tokenizer(tokens.Tokenizer): 192 QUOTES = ["'", "$$"] 193 STRING_ESCAPES = ["\\", "'"] 194 195 KEYWORDS = { 196 **tokens.Tokenizer.KEYWORDS, 197 "EXCLUDE": TokenType.EXCEPT, 198 "ILIKE ANY": TokenType.ILIKE_ANY, 199 "LIKE ANY": TokenType.LIKE_ANY, 200 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 201 "PUT": TokenType.COMMAND, 202 "RENAME": TokenType.REPLACE, 203 "TIMESTAMP_LTZ": TokenType.TIMESTAMPLTZ, 204 "TIMESTAMP_NTZ": TokenType.TIMESTAMP, 205 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 206 "TIMESTAMPNTZ": TokenType.TIMESTAMP, 207 "MINUS": TokenType.EXCEPT, 208 "SAMPLE": TokenType.TABLE_SAMPLE, 209 } 210 211 SINGLE_TOKENS = { 212 **tokens.Tokenizer.SINGLE_TOKENS, 213 "$": TokenType.PARAMETER, 214 } 215 216 class Generator(generator.Generator): 217 CREATE_TRANSIENT = True 218 PARAMETER_TOKEN = "$" 219 220 TRANSFORMS = { 221 **generator.Generator.TRANSFORMS, # type: ignore 222 exp.Array: inline_array_sql, 223 exp.ArrayConcat: rename_func("ARRAY_CAT"), 224 exp.DateAdd: rename_func("DATEADD"), 225 exp.DateStrToDate: datestrtodate_sql, 226 exp.DataType: _datatype_sql, 227 exp.If: rename_func("IFF"), 228 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 229 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 230 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 231 exp.Matches: rename_func("DECODE"), 232 exp.StrPosition: lambda self, e: self.func( 233 "POSITION", e.args.get("substr"), e.this, e.args.get("position") 234 ), 235 exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})", 236 exp.TimeStrToTime: timestrtotime_sql, 237 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 238 exp.Trim: lambda self, e: self.func("TRIM", e.this, e.expression), 239 exp.UnixToTime: _unix_to_time_sql, 240 exp.DayOfWeek: rename_func("DAYOFWEEK"), 241 } 242 243 TYPE_MAPPING = { 244 **generator.Generator.TYPE_MAPPING, # type: ignore 245 exp.DataType.Type.TIMESTAMP: "TIMESTAMPNTZ", 246 } 247 248 STAR_MAPPING = { 249 "except": "EXCLUDE", 250 "replace": "RENAME", 251 } 252 253 def ilikeany_sql(self, expression: exp.ILikeAny) -> str: 254 return self.binary(expression, "ILIKE ANY") 255 256 def likeany_sql(self, expression: exp.LikeAny) -> str: 257 return self.binary(expression, "LIKE ANY") 258 259 def except_op(self, expression): 260 if not expression.args.get("distinct", False): 261 self.unsupported("EXCEPT with All is not supported in Snowflake") 262 return super().except_op(expression) 263 264 def intersect_op(self, expression): 265 if not expression.args.get("distinct", False): 266 self.unsupported("INTERSECT with All is not supported in Snowflake") 267 return super().intersect_op(expression) 268 269 def values_sql(self, expression: exp.Values) -> str: 270 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted. 271 272 We also want to make sure that after we find matches where we need to unquote a column that we prevent users 273 from adding quotes to the column by using the `identify` argument when generating the SQL. 274 """ 275 alias = expression.args.get("alias") 276 if alias and alias.args.get("columns"): 277 expression = expression.transform( 278 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 279 if isinstance(node, exp.Identifier) 280 and isinstance(node.parent, exp.TableAlias) 281 and node.arg_key == "columns" 282 else node, 283 ) 284 return self.no_identify(lambda: super(self.__class__, self).values_sql(expression)) 285 return super().values_sql(expression) 286 287 def select_sql(self, expression: exp.Select) -> str: 288 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted and also 289 that all columns in a SELECT are unquoted. We also want to make sure that after we find matches where we need 290 to unquote a column that we prevent users from adding quotes to the column by using the `identify` argument when 291 generating the SQL. 292 293 Note: We make an assumption that any columns referenced in a VALUES expression should be unquoted throughout the 294 expression. This might not be true in a case where the same column name can be sourced from another table that can 295 properly quote but should be true in most cases. 296 """ 297 values_expressions = expression.find_all(exp.Values) 298 values_identifiers = set( 299 flatten( 300 v.args.get("alias", exp.Alias()).args.get("columns", []) 301 for v in values_expressions 302 ) 303 ) 304 if values_identifiers: 305 expression = expression.transform( 306 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 307 if isinstance(node, exp.Identifier) and node in values_identifiers 308 else node, 309 ) 310 return self.no_identify(lambda: super(self.__class__, self).select_sql(expression)) 311 return super().select_sql(expression) 312 313 def describe_sql(self, expression: exp.Describe) -> str: 314 # Default to table if kind is unknown 315 kind_value = expression.args.get("kind") or "TABLE" 316 kind = f" {kind_value}" if kind_value else "" 317 this = f" {self.sql(expression, 'this')}" 318 return f"DESCRIBE{kind}{this}" 319 320 def generatedasidentitycolumnconstraint_sql( 321 self, expression: exp.GeneratedAsIdentityColumnConstraint 322 ) -> str: 323 start = expression.args.get("start") 324 start = f" START {start}" if start else "" 325 increment = expression.args.get("increment") 326 increment = f" INCREMENT {increment}" if increment else "" 327 return f"AUTOINCREMENT{start}{increment}"
115class Snowflake(Dialect): 116 null_ordering = "nulls_are_large" 117 time_format = "'yyyy-mm-dd hh24:mi:ss'" 118 119 time_mapping = { 120 "YYYY": "%Y", 121 "yyyy": "%Y", 122 "YY": "%y", 123 "yy": "%y", 124 "MMMM": "%B", 125 "mmmm": "%B", 126 "MON": "%b", 127 "mon": "%b", 128 "MM": "%m", 129 "mm": "%m", 130 "DD": "%d", 131 "dd": "%d", 132 "d": "%-d", 133 "DY": "%w", 134 "dy": "%w", 135 "HH24": "%H", 136 "hh24": "%H", 137 "HH12": "%I", 138 "hh12": "%I", 139 "MI": "%M", 140 "mi": "%M", 141 "SS": "%S", 142 "ss": "%S", 143 "FF": "%f", 144 "ff": "%f", 145 "FF6": "%f", 146 "ff6": "%f", 147 } 148 149 class Parser(parser.Parser): 150 FUNCTIONS = { 151 **parser.Parser.FUNCTIONS, 152 "ARRAYAGG": exp.ArrayAgg.from_arg_list, 153 "IFF": exp.If.from_arg_list, 154 "TO_TIMESTAMP": _snowflake_to_timestamp, 155 "ARRAY_CONSTRUCT": exp.Array.from_arg_list, 156 "RLIKE": exp.RegexpLike.from_arg_list, 157 "DECODE": exp.Matches.from_arg_list, 158 "OBJECT_CONSTRUCT": parser.parse_var_map, 159 } 160 161 FUNCTION_PARSERS = { 162 **parser.Parser.FUNCTION_PARSERS, 163 "DATE_PART": _parse_date_part, 164 } 165 FUNCTION_PARSERS.pop("TRIM") 166 167 FUNC_TOKENS = { 168 *parser.Parser.FUNC_TOKENS, 169 TokenType.RLIKE, 170 TokenType.TABLE, 171 } 172 173 COLUMN_OPERATORS = { 174 **parser.Parser.COLUMN_OPERATORS, # type: ignore 175 TokenType.COLON: lambda self, this, path: self.expression( 176 exp.Bracket, 177 this=this, 178 expressions=[path], 179 ), 180 } 181 182 RANGE_PARSERS = { 183 **parser.Parser.RANGE_PARSERS, # type: ignore 184 TokenType.LIKE_ANY: lambda self, this: self._parse_escape( 185 self.expression(exp.LikeAny, this=this, expression=self._parse_bitwise()) 186 ), 187 TokenType.ILIKE_ANY: lambda self, this: self._parse_escape( 188 self.expression(exp.ILikeAny, this=this, expression=self._parse_bitwise()) 189 ), 190 } 191 192 class Tokenizer(tokens.Tokenizer): 193 QUOTES = ["'", "$$"] 194 STRING_ESCAPES = ["\\", "'"] 195 196 KEYWORDS = { 197 **tokens.Tokenizer.KEYWORDS, 198 "EXCLUDE": TokenType.EXCEPT, 199 "ILIKE ANY": TokenType.ILIKE_ANY, 200 "LIKE ANY": TokenType.LIKE_ANY, 201 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 202 "PUT": TokenType.COMMAND, 203 "RENAME": TokenType.REPLACE, 204 "TIMESTAMP_LTZ": TokenType.TIMESTAMPLTZ, 205 "TIMESTAMP_NTZ": TokenType.TIMESTAMP, 206 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 207 "TIMESTAMPNTZ": TokenType.TIMESTAMP, 208 "MINUS": TokenType.EXCEPT, 209 "SAMPLE": TokenType.TABLE_SAMPLE, 210 } 211 212 SINGLE_TOKENS = { 213 **tokens.Tokenizer.SINGLE_TOKENS, 214 "$": TokenType.PARAMETER, 215 } 216 217 class Generator(generator.Generator): 218 CREATE_TRANSIENT = True 219 PARAMETER_TOKEN = "$" 220 221 TRANSFORMS = { 222 **generator.Generator.TRANSFORMS, # type: ignore 223 exp.Array: inline_array_sql, 224 exp.ArrayConcat: rename_func("ARRAY_CAT"), 225 exp.DateAdd: rename_func("DATEADD"), 226 exp.DateStrToDate: datestrtodate_sql, 227 exp.DataType: _datatype_sql, 228 exp.If: rename_func("IFF"), 229 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 230 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 231 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 232 exp.Matches: rename_func("DECODE"), 233 exp.StrPosition: lambda self, e: self.func( 234 "POSITION", e.args.get("substr"), e.this, e.args.get("position") 235 ), 236 exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})", 237 exp.TimeStrToTime: timestrtotime_sql, 238 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 239 exp.Trim: lambda self, e: self.func("TRIM", e.this, e.expression), 240 exp.UnixToTime: _unix_to_time_sql, 241 exp.DayOfWeek: rename_func("DAYOFWEEK"), 242 } 243 244 TYPE_MAPPING = { 245 **generator.Generator.TYPE_MAPPING, # type: ignore 246 exp.DataType.Type.TIMESTAMP: "TIMESTAMPNTZ", 247 } 248 249 STAR_MAPPING = { 250 "except": "EXCLUDE", 251 "replace": "RENAME", 252 } 253 254 def ilikeany_sql(self, expression: exp.ILikeAny) -> str: 255 return self.binary(expression, "ILIKE ANY") 256 257 def likeany_sql(self, expression: exp.LikeAny) -> str: 258 return self.binary(expression, "LIKE ANY") 259 260 def except_op(self, expression): 261 if not expression.args.get("distinct", False): 262 self.unsupported("EXCEPT with All is not supported in Snowflake") 263 return super().except_op(expression) 264 265 def intersect_op(self, expression): 266 if not expression.args.get("distinct", False): 267 self.unsupported("INTERSECT with All is not supported in Snowflake") 268 return super().intersect_op(expression) 269 270 def values_sql(self, expression: exp.Values) -> str: 271 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted. 272 273 We also want to make sure that after we find matches where we need to unquote a column that we prevent users 274 from adding quotes to the column by using the `identify` argument when generating the SQL. 275 """ 276 alias = expression.args.get("alias") 277 if alias and alias.args.get("columns"): 278 expression = expression.transform( 279 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 280 if isinstance(node, exp.Identifier) 281 and isinstance(node.parent, exp.TableAlias) 282 and node.arg_key == "columns" 283 else node, 284 ) 285 return self.no_identify(lambda: super(self.__class__, self).values_sql(expression)) 286 return super().values_sql(expression) 287 288 def select_sql(self, expression: exp.Select) -> str: 289 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted and also 290 that all columns in a SELECT are unquoted. We also want to make sure that after we find matches where we need 291 to unquote a column that we prevent users from adding quotes to the column by using the `identify` argument when 292 generating the SQL. 293 294 Note: We make an assumption that any columns referenced in a VALUES expression should be unquoted throughout the 295 expression. This might not be true in a case where the same column name can be sourced from another table that can 296 properly quote but should be true in most cases. 297 """ 298 values_expressions = expression.find_all(exp.Values) 299 values_identifiers = set( 300 flatten( 301 v.args.get("alias", exp.Alias()).args.get("columns", []) 302 for v in values_expressions 303 ) 304 ) 305 if values_identifiers: 306 expression = expression.transform( 307 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 308 if isinstance(node, exp.Identifier) and node in values_identifiers 309 else node, 310 ) 311 return self.no_identify(lambda: super(self.__class__, self).select_sql(expression)) 312 return super().select_sql(expression) 313 314 def describe_sql(self, expression: exp.Describe) -> str: 315 # Default to table if kind is unknown 316 kind_value = expression.args.get("kind") or "TABLE" 317 kind = f" {kind_value}" if kind_value else "" 318 this = f" {self.sql(expression, 'this')}" 319 return f"DESCRIBE{kind}{this}" 320 321 def generatedasidentitycolumnconstraint_sql( 322 self, expression: exp.GeneratedAsIdentityColumnConstraint 323 ) -> str: 324 start = expression.args.get("start") 325 start = f" START {start}" if start else "" 326 increment = expression.args.get("increment") 327 increment = f" INCREMENT {increment}" if increment else "" 328 return f"AUTOINCREMENT{start}{increment}"
Inherited Members
149 class Parser(parser.Parser): 150 FUNCTIONS = { 151 **parser.Parser.FUNCTIONS, 152 "ARRAYAGG": exp.ArrayAgg.from_arg_list, 153 "IFF": exp.If.from_arg_list, 154 "TO_TIMESTAMP": _snowflake_to_timestamp, 155 "ARRAY_CONSTRUCT": exp.Array.from_arg_list, 156 "RLIKE": exp.RegexpLike.from_arg_list, 157 "DECODE": exp.Matches.from_arg_list, 158 "OBJECT_CONSTRUCT": parser.parse_var_map, 159 } 160 161 FUNCTION_PARSERS = { 162 **parser.Parser.FUNCTION_PARSERS, 163 "DATE_PART": _parse_date_part, 164 } 165 FUNCTION_PARSERS.pop("TRIM") 166 167 FUNC_TOKENS = { 168 *parser.Parser.FUNC_TOKENS, 169 TokenType.RLIKE, 170 TokenType.TABLE, 171 } 172 173 COLUMN_OPERATORS = { 174 **parser.Parser.COLUMN_OPERATORS, # type: ignore 175 TokenType.COLON: lambda self, this, path: self.expression( 176 exp.Bracket, 177 this=this, 178 expressions=[path], 179 ), 180 } 181 182 RANGE_PARSERS = { 183 **parser.Parser.RANGE_PARSERS, # type: ignore 184 TokenType.LIKE_ANY: lambda self, this: self._parse_escape( 185 self.expression(exp.LikeAny, this=this, expression=self._parse_bitwise()) 186 ), 187 TokenType.ILIKE_ANY: lambda self, this: self._parse_escape( 188 self.expression(exp.ILikeAny, this=this, expression=self._parse_bitwise()) 189 ), 190 }
Parser consumes a list of tokens produced by the sqlglot.tokens.Tokenizer and produces
a parsed syntax tree.
Arguments:
- error_level: the desired error level. Default: ErrorLevel.RAISE
- error_message_context: determines the amount of context to capture from a query string when displaying the error message (in number of characters). Default: 50.
- index_offset: Index offset for arrays eg ARRAY[0] vs ARRAY[1] as the head of a list. Default: 0
- alias_post_tablesample: If the table alias comes after tablesample. Default: False
- 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
- null_ordering: Indicates the default null ordering method to use if not explicitly set. Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". Default: "nulls_are_small"
Inherited Members
192 class Tokenizer(tokens.Tokenizer): 193 QUOTES = ["'", "$$"] 194 STRING_ESCAPES = ["\\", "'"] 195 196 KEYWORDS = { 197 **tokens.Tokenizer.KEYWORDS, 198 "EXCLUDE": TokenType.EXCEPT, 199 "ILIKE ANY": TokenType.ILIKE_ANY, 200 "LIKE ANY": TokenType.LIKE_ANY, 201 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 202 "PUT": TokenType.COMMAND, 203 "RENAME": TokenType.REPLACE, 204 "TIMESTAMP_LTZ": TokenType.TIMESTAMPLTZ, 205 "TIMESTAMP_NTZ": TokenType.TIMESTAMP, 206 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 207 "TIMESTAMPNTZ": TokenType.TIMESTAMP, 208 "MINUS": TokenType.EXCEPT, 209 "SAMPLE": TokenType.TABLE_SAMPLE, 210 } 211 212 SINGLE_TOKENS = { 213 **tokens.Tokenizer.SINGLE_TOKENS, 214 "$": TokenType.PARAMETER, 215 }
Inherited Members
217 class Generator(generator.Generator): 218 CREATE_TRANSIENT = True 219 PARAMETER_TOKEN = "$" 220 221 TRANSFORMS = { 222 **generator.Generator.TRANSFORMS, # type: ignore 223 exp.Array: inline_array_sql, 224 exp.ArrayConcat: rename_func("ARRAY_CAT"), 225 exp.DateAdd: rename_func("DATEADD"), 226 exp.DateStrToDate: datestrtodate_sql, 227 exp.DataType: _datatype_sql, 228 exp.If: rename_func("IFF"), 229 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 230 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 231 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 232 exp.Matches: rename_func("DECODE"), 233 exp.StrPosition: lambda self, e: self.func( 234 "POSITION", e.args.get("substr"), e.this, e.args.get("position") 235 ), 236 exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})", 237 exp.TimeStrToTime: timestrtotime_sql, 238 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 239 exp.Trim: lambda self, e: self.func("TRIM", e.this, e.expression), 240 exp.UnixToTime: _unix_to_time_sql, 241 exp.DayOfWeek: rename_func("DAYOFWEEK"), 242 } 243 244 TYPE_MAPPING = { 245 **generator.Generator.TYPE_MAPPING, # type: ignore 246 exp.DataType.Type.TIMESTAMP: "TIMESTAMPNTZ", 247 } 248 249 STAR_MAPPING = { 250 "except": "EXCLUDE", 251 "replace": "RENAME", 252 } 253 254 def ilikeany_sql(self, expression: exp.ILikeAny) -> str: 255 return self.binary(expression, "ILIKE ANY") 256 257 def likeany_sql(self, expression: exp.LikeAny) -> str: 258 return self.binary(expression, "LIKE ANY") 259 260 def except_op(self, expression): 261 if not expression.args.get("distinct", False): 262 self.unsupported("EXCEPT with All is not supported in Snowflake") 263 return super().except_op(expression) 264 265 def intersect_op(self, expression): 266 if not expression.args.get("distinct", False): 267 self.unsupported("INTERSECT with All is not supported in Snowflake") 268 return super().intersect_op(expression) 269 270 def values_sql(self, expression: exp.Values) -> str: 271 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted. 272 273 We also want to make sure that after we find matches where we need to unquote a column that we prevent users 274 from adding quotes to the column by using the `identify` argument when generating the SQL. 275 """ 276 alias = expression.args.get("alias") 277 if alias and alias.args.get("columns"): 278 expression = expression.transform( 279 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 280 if isinstance(node, exp.Identifier) 281 and isinstance(node.parent, exp.TableAlias) 282 and node.arg_key == "columns" 283 else node, 284 ) 285 return self.no_identify(lambda: super(self.__class__, self).values_sql(expression)) 286 return super().values_sql(expression) 287 288 def select_sql(self, expression: exp.Select) -> str: 289 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted and also 290 that all columns in a SELECT are unquoted. We also want to make sure that after we find matches where we need 291 to unquote a column that we prevent users from adding quotes to the column by using the `identify` argument when 292 generating the SQL. 293 294 Note: We make an assumption that any columns referenced in a VALUES expression should be unquoted throughout the 295 expression. This might not be true in a case where the same column name can be sourced from another table that can 296 properly quote but should be true in most cases. 297 """ 298 values_expressions = expression.find_all(exp.Values) 299 values_identifiers = set( 300 flatten( 301 v.args.get("alias", exp.Alias()).args.get("columns", []) 302 for v in values_expressions 303 ) 304 ) 305 if values_identifiers: 306 expression = expression.transform( 307 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 308 if isinstance(node, exp.Identifier) and node in values_identifiers 309 else node, 310 ) 311 return self.no_identify(lambda: super(self.__class__, self).select_sql(expression)) 312 return super().select_sql(expression) 313 314 def describe_sql(self, expression: exp.Describe) -> str: 315 # Default to table if kind is unknown 316 kind_value = expression.args.get("kind") or "TABLE" 317 kind = f" {kind_value}" if kind_value else "" 318 this = f" {self.sql(expression, 'this')}" 319 return f"DESCRIBE{kind}{this}" 320 321 def generatedasidentitycolumnconstraint_sql( 322 self, expression: exp.GeneratedAsIdentityColumnConstraint 323 ) -> str: 324 start = expression.args.get("start") 325 start = f" START {start}" if start else "" 326 increment = expression.args.get("increment") 327 increment = f" INCREMENT {increment}" if increment else "" 328 return f"AUTOINCREMENT{start}{increment}"
Generator interprets the given syntax tree and produces a SQL string as an output.
Arguments:
- time_mapping (dict): the dictionary of custom time mappings in which the key represents a python time format and the output the target time format
- time_trie (trie): a trie of the time_mapping keys
- pretty (bool): if set to True the returned string will be formatted. Default: False.
- quote_start (str): specifies which starting character to use to delimit quotes. Default: '.
- quote_end (str): specifies which ending character to use to delimit quotes. Default: '.
- identifier_start (str): specifies which starting character to use to delimit identifiers. Default: ".
- identifier_end (str): specifies which ending character to use to delimit identifiers. Default: ".
- identify (bool): if set to True all identifiers will be delimited by the corresponding character.
- normalize (bool): if set to True all identifiers will lower cased
- string_escape (str): specifies a string escape character. Default: '.
- identifier_escape (str): specifies an identifier escape character. Default: ".
- pad (int): determines padding in a formatted string. Default: 2.
- indent (int): determines the size of indentation in a formatted string. Default: 4.
- unnest_column_only (bool): if true unnest table aliases are considered only as column aliases
- normalize_functions (str): normalize function names, "upper", "lower", or None Default: "upper"
- alias_post_tablesample (bool): if the table alias comes after tablesample Default: False
- unsupported_level (ErrorLevel): determines the generator's behavior when it encounters unsupported expressions. Default ErrorLevel.WARN.
- null_ordering (str): Indicates the default null ordering method to use if not explicitly set. Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". Default: "nulls_are_small"
- max_unsupported (int): 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 (bool): if the the comma is leading or trailing in select statements 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 or not to preserve comments in the output SQL code. Default: True
270 def values_sql(self, expression: exp.Values) -> str: 271 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted. 272 273 We also want to make sure that after we find matches where we need to unquote a column that we prevent users 274 from adding quotes to the column by using the `identify` argument when generating the SQL. 275 """ 276 alias = expression.args.get("alias") 277 if alias and alias.args.get("columns"): 278 expression = expression.transform( 279 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 280 if isinstance(node, exp.Identifier) 281 and isinstance(node.parent, exp.TableAlias) 282 and node.arg_key == "columns" 283 else node, 284 ) 285 return self.no_identify(lambda: super(self.__class__, self).values_sql(expression)) 286 return super().values_sql(expression)
Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted.
We also want to make sure that after we find matches where we need to unquote a column that we prevent users
from adding quotes to the column by using the identify argument when generating the SQL.
288 def select_sql(self, expression: exp.Select) -> str: 289 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted and also 290 that all columns in a SELECT are unquoted. We also want to make sure that after we find matches where we need 291 to unquote a column that we prevent users from adding quotes to the column by using the `identify` argument when 292 generating the SQL. 293 294 Note: We make an assumption that any columns referenced in a VALUES expression should be unquoted throughout the 295 expression. This might not be true in a case where the same column name can be sourced from another table that can 296 properly quote but should be true in most cases. 297 """ 298 values_expressions = expression.find_all(exp.Values) 299 values_identifiers = set( 300 flatten( 301 v.args.get("alias", exp.Alias()).args.get("columns", []) 302 for v in values_expressions 303 ) 304 ) 305 if values_identifiers: 306 expression = expression.transform( 307 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 308 if isinstance(node, exp.Identifier) and node in values_identifiers 309 else node, 310 ) 311 return self.no_identify(lambda: super(self.__class__, self).select_sql(expression)) 312 return super().select_sql(expression)
Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted and also
that all columns in a SELECT are unquoted. We also want to make sure that after we find matches where we need
to unquote a column that we prevent users from adding quotes to the column by using the identify argument when
generating the SQL.
Note: We make an assumption that any columns referenced in a VALUES expression should be unquoted throughout the expression. This might not be true in a case where the same column name can be sourced from another table that can properly quote but should be true in most cases.
def
generatedasidentitycolumnconstraint_sql( self, expression: sqlglot.expressions.GeneratedAsIdentityColumnConstraint) -> str:
321 def generatedasidentitycolumnconstraint_sql( 322 self, expression: exp.GeneratedAsIdentityColumnConstraint 323 ) -> str: 324 start = expression.args.get("start") 325 start = f" START {start}" if start else "" 326 increment = expression.args.get("increment") 327 increment = f" INCREMENT {increment}" if increment else "" 328 return f"AUTOINCREMENT{start}{increment}"
Inherited Members
- sqlglot.generator.Generator
- Generator
- generate
- unsupported
- sep
- seg
- pad_comment
- maybe_comment
- wrap
- no_identify
- normalize_func
- indent
- sql
- uncache_sql
- cache_sql
- characterset_sql
- column_sql
- columndef_sql
- columnconstraint_sql
- autoincrementcolumnconstraint_sql
- notnullcolumnconstraint_sql
- primarykeycolumnconstraint_sql
- uniquecolumnconstraint_sql
- create_sql
- prepend_ctes
- with_sql
- cte_sql
- tablealias_sql
- bitstring_sql
- hexstring_sql
- datatype_sql
- directory_sql
- delete_sql
- drop_sql
- except_sql
- fetch_sql
- filter_sql
- hint_sql
- index_sql
- identifier_sql
- national_sql
- partition_sql
- properties_sql
- root_properties
- properties
- with_properties
- locate_properties
- property_sql
- likeproperty_sql
- fallbackproperty_sql
- journalproperty_sql
- freespaceproperty_sql
- afterjournalproperty_sql
- checksumproperty_sql
- mergeblockratioproperty_sql
- datablocksizeproperty_sql
- blockcompressionproperty_sql
- isolatedloadingproperty_sql
- lockingproperty_sql
- insert_sql
- intersect_sql
- introducer_sql
- pseudotype_sql
- rowformatdelimitedproperty_sql
- table_sql
- tablesample_sql
- pivot_sql
- tuple_sql
- update_sql
- var_sql
- into_sql
- from_sql
- group_sql
- having_sql
- join_sql
- lambda_sql
- lateral_sql
- limit_sql
- offset_sql
- lock_sql
- literal_sql
- loaddata_sql
- null_sql
- boolean_sql
- order_sql
- cluster_sql
- distribute_sql
- sort_sql
- ordered_sql
- matchrecognize_sql
- query_modifiers
- schema_sql
- star_sql
- structkwarg_sql
- parameter_sql
- sessionparameter_sql
- placeholder_sql
- subquery_sql
- qualify_sql
- union_sql
- union_op
- unnest_sql
- where_sql
- window_sql
- partition_by_sql
- window_spec_sql
- withingroup_sql
- between_sql
- bracket_sql
- all_sql
- any_sql
- exists_sql
- case_sql
- constraint_sql
- extract_sql
- trim_sql
- concat_sql
- check_sql
- foreignkey_sql
- primarykey_sql
- unique_sql
- if_sql
- in_sql
- in_unnest_op
- interval_sql
- return_sql
- reference_sql
- anonymous_sql
- paren_sql
- neg_sql
- not_sql
- alias_sql
- aliases_sql
- attimezone_sql
- add_sql
- and_sql
- connector_sql
- bitwiseand_sql
- bitwiseleftshift_sql
- bitwisenot_sql
- bitwiseor_sql
- bitwiserightshift_sql
- bitwisexor_sql
- cast_sql
- currentdate_sql
- collate_sql
- command_sql
- transaction_sql
- commit_sql
- rollback_sql
- altercolumn_sql
- renametable_sql
- altertable_sql
- droppartition_sql
- addconstraint_sql
- distinct_sql
- ignorenulls_sql
- respectnulls_sql
- intdiv_sql
- dpipe_sql
- div_sql
- distance_sql
- dot_sql
- eq_sql
- escape_sql
- glob_sql
- gt_sql
- gte_sql
- ilike_sql
- is_sql
- like_sql
- similarto_sql
- lt_sql
- lte_sql
- mod_sql
- mul_sql
- neq_sql
- nullsafeeq_sql
- nullsafeneq_sql
- or_sql
- slice_sql
- sub_sql
- trycast_sql
- use_sql
- binary
- function_fallback_sql
- func
- format_args
- text_width
- format_time
- expressions
- op_expressions
- naked_property
- set_operation
- tag_sql
- token_sql
- userdefinedfunction_sql
- joinhint_sql
- kwarg_sql
- when_sql
- merge_sql