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 class Tokenizer(tokens.Tokenizer): 182 QUOTES = ["'", "$$"] 183 STRING_ESCAPES = ["\\", "'"] 184 185 SINGLE_TOKENS = { 186 **tokens.Tokenizer.SINGLE_TOKENS, 187 "$": TokenType.PARAMETER, 188 } 189 190 KEYWORDS = { 191 **tokens.Tokenizer.KEYWORDS, 192 "EXCLUDE": TokenType.EXCEPT, 193 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 194 "PUT": TokenType.COMMAND, 195 "RENAME": TokenType.REPLACE, 196 "TIMESTAMP_LTZ": TokenType.TIMESTAMPLTZ, 197 "TIMESTAMP_NTZ": TokenType.TIMESTAMP, 198 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 199 "TIMESTAMPNTZ": TokenType.TIMESTAMP, 200 "MINUS": TokenType.EXCEPT, 201 "SAMPLE": TokenType.TABLE_SAMPLE, 202 } 203 204 class Generator(generator.Generator): 205 CREATE_TRANSIENT = True 206 207 TRANSFORMS = { 208 **generator.Generator.TRANSFORMS, # type: ignore 209 exp.Array: inline_array_sql, 210 exp.ArrayConcat: rename_func("ARRAY_CAT"), 211 exp.DateAdd: rename_func("DATEADD"), 212 exp.DateStrToDate: datestrtodate_sql, 213 exp.DataType: _datatype_sql, 214 exp.If: rename_func("IFF"), 215 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 216 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 217 exp.Parameter: lambda self, e: f"${self.sql(e, 'this')}", 218 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 219 exp.Matches: rename_func("DECODE"), 220 exp.StrPosition: lambda self, e: f"{self.normalize_func('POSITION')}({self.format_args(e.args.get('substr'), e.this, e.args.get('position'))})", 221 exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})", 222 exp.TimeStrToTime: timestrtotime_sql, 223 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 224 exp.Trim: lambda self, e: f"TRIM({self.format_args(e.this, e.expression)})", 225 exp.UnixToTime: _unix_to_time_sql, 226 exp.DayOfWeek: rename_func("DAYOFWEEK"), 227 } 228 229 TYPE_MAPPING = { 230 **generator.Generator.TYPE_MAPPING, # type: ignore 231 exp.DataType.Type.TIMESTAMP: "TIMESTAMPNTZ", 232 } 233 234 STAR_MAPPING = { 235 "except": "EXCLUDE", 236 "replace": "RENAME", 237 } 238 239 def except_op(self, expression): 240 if not expression.args.get("distinct", False): 241 self.unsupported("EXCEPT with All is not supported in Snowflake") 242 return super().except_op(expression) 243 244 def intersect_op(self, expression): 245 if not expression.args.get("distinct", False): 246 self.unsupported("INTERSECT with All is not supported in Snowflake") 247 return super().intersect_op(expression) 248 249 def values_sql(self, expression: exp.Values) -> str: 250 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted. 251 252 We also want to make sure that after we find matches where we need to unquote a column that we prevent users 253 from adding quotes to the column by using the `identify` argument when generating the SQL. 254 """ 255 alias = expression.args.get("alias") 256 if alias and alias.args.get("columns"): 257 expression = expression.transform( 258 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 259 if isinstance(node, exp.Identifier) 260 and isinstance(node.parent, exp.TableAlias) 261 and node.arg_key == "columns" 262 else node, 263 ) 264 return self.no_identify(lambda: super(self.__class__, self).values_sql(expression)) 265 return super().values_sql(expression) 266 267 def select_sql(self, expression: exp.Select) -> str: 268 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted and also 269 that all columns in a SELECT are unquoted. We also want to make sure that after we find matches where we need 270 to unquote a column that we prevent users from adding quotes to the column by using the `identify` argument when 271 generating the SQL. 272 273 Note: We make an assumption that any columns referenced in a VALUES expression should be unquoted throughout the 274 expression. This might not be true in a case where the same column name can be sourced from another table that can 275 properly quote but should be true in most cases. 276 """ 277 values_expressions = expression.find_all(exp.Values) 278 values_identifiers = set( 279 flatten( 280 v.args.get("alias", exp.Alias()).args.get("columns", []) 281 for v in values_expressions 282 ) 283 ) 284 if values_identifiers: 285 expression = expression.transform( 286 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 287 if isinstance(node, exp.Identifier) and node in values_identifiers 288 else node, 289 ) 290 return self.no_identify(lambda: super(self.__class__, self).select_sql(expression)) 291 return super().select_sql(expression) 292 293 def describe_sql(self, expression: exp.Describe) -> str: 294 # Default to table if kind is unknown 295 kind_value = expression.args.get("kind") or "TABLE" 296 kind = f" {kind_value}" if kind_value else "" 297 this = f" {self.sql(expression, 'this')}" 298 return f"DESCRIBE{kind}{this}" 299 300 def generatedasidentitycolumnconstraint_sql( 301 self, expression: exp.GeneratedAsIdentityColumnConstraint 302 ) -> str: 303 start = expression.args.get("start") 304 start = f" START {start}" if start else "" 305 increment = expression.args.get("increment") 306 increment = f" INCREMENT {increment}" if increment else "" 307 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 class Tokenizer(tokens.Tokenizer): 183 QUOTES = ["'", "$$"] 184 STRING_ESCAPES = ["\\", "'"] 185 186 SINGLE_TOKENS = { 187 **tokens.Tokenizer.SINGLE_TOKENS, 188 "$": TokenType.PARAMETER, 189 } 190 191 KEYWORDS = { 192 **tokens.Tokenizer.KEYWORDS, 193 "EXCLUDE": TokenType.EXCEPT, 194 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 195 "PUT": TokenType.COMMAND, 196 "RENAME": TokenType.REPLACE, 197 "TIMESTAMP_LTZ": TokenType.TIMESTAMPLTZ, 198 "TIMESTAMP_NTZ": TokenType.TIMESTAMP, 199 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 200 "TIMESTAMPNTZ": TokenType.TIMESTAMP, 201 "MINUS": TokenType.EXCEPT, 202 "SAMPLE": TokenType.TABLE_SAMPLE, 203 } 204 205 class Generator(generator.Generator): 206 CREATE_TRANSIENT = True 207 208 TRANSFORMS = { 209 **generator.Generator.TRANSFORMS, # type: ignore 210 exp.Array: inline_array_sql, 211 exp.ArrayConcat: rename_func("ARRAY_CAT"), 212 exp.DateAdd: rename_func("DATEADD"), 213 exp.DateStrToDate: datestrtodate_sql, 214 exp.DataType: _datatype_sql, 215 exp.If: rename_func("IFF"), 216 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 217 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 218 exp.Parameter: lambda self, e: f"${self.sql(e, 'this')}", 219 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 220 exp.Matches: rename_func("DECODE"), 221 exp.StrPosition: lambda self, e: f"{self.normalize_func('POSITION')}({self.format_args(e.args.get('substr'), e.this, e.args.get('position'))})", 222 exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})", 223 exp.TimeStrToTime: timestrtotime_sql, 224 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 225 exp.Trim: lambda self, e: f"TRIM({self.format_args(e.this, e.expression)})", 226 exp.UnixToTime: _unix_to_time_sql, 227 exp.DayOfWeek: rename_func("DAYOFWEEK"), 228 } 229 230 TYPE_MAPPING = { 231 **generator.Generator.TYPE_MAPPING, # type: ignore 232 exp.DataType.Type.TIMESTAMP: "TIMESTAMPNTZ", 233 } 234 235 STAR_MAPPING = { 236 "except": "EXCLUDE", 237 "replace": "RENAME", 238 } 239 240 def except_op(self, expression): 241 if not expression.args.get("distinct", False): 242 self.unsupported("EXCEPT with All is not supported in Snowflake") 243 return super().except_op(expression) 244 245 def intersect_op(self, expression): 246 if not expression.args.get("distinct", False): 247 self.unsupported("INTERSECT with All is not supported in Snowflake") 248 return super().intersect_op(expression) 249 250 def values_sql(self, expression: exp.Values) -> str: 251 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted. 252 253 We also want to make sure that after we find matches where we need to unquote a column that we prevent users 254 from adding quotes to the column by using the `identify` argument when generating the SQL. 255 """ 256 alias = expression.args.get("alias") 257 if alias and alias.args.get("columns"): 258 expression = expression.transform( 259 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 260 if isinstance(node, exp.Identifier) 261 and isinstance(node.parent, exp.TableAlias) 262 and node.arg_key == "columns" 263 else node, 264 ) 265 return self.no_identify(lambda: super(self.__class__, self).values_sql(expression)) 266 return super().values_sql(expression) 267 268 def select_sql(self, expression: exp.Select) -> str: 269 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted and also 270 that all columns in a SELECT are unquoted. We also want to make sure that after we find matches where we need 271 to unquote a column that we prevent users from adding quotes to the column by using the `identify` argument when 272 generating the SQL. 273 274 Note: We make an assumption that any columns referenced in a VALUES expression should be unquoted throughout the 275 expression. This might not be true in a case where the same column name can be sourced from another table that can 276 properly quote but should be true in most cases. 277 """ 278 values_expressions = expression.find_all(exp.Values) 279 values_identifiers = set( 280 flatten( 281 v.args.get("alias", exp.Alias()).args.get("columns", []) 282 for v in values_expressions 283 ) 284 ) 285 if values_identifiers: 286 expression = expression.transform( 287 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 288 if isinstance(node, exp.Identifier) and node in values_identifiers 289 else node, 290 ) 291 return self.no_identify(lambda: super(self.__class__, self).select_sql(expression)) 292 return super().select_sql(expression) 293 294 def describe_sql(self, expression: exp.Describe) -> str: 295 # Default to table if kind is unknown 296 kind_value = expression.args.get("kind") or "TABLE" 297 kind = f" {kind_value}" if kind_value else "" 298 this = f" {self.sql(expression, 'this')}" 299 return f"DESCRIBE{kind}{this}" 300 301 def generatedasidentitycolumnconstraint_sql( 302 self, expression: exp.GeneratedAsIdentityColumnConstraint 303 ) -> str: 304 start = expression.args.get("start") 305 start = f" START {start}" if start else "" 306 increment = expression.args.get("increment") 307 increment = f" INCREMENT {increment}" if increment else "" 308 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 }
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
182 class Tokenizer(tokens.Tokenizer): 183 QUOTES = ["'", "$$"] 184 STRING_ESCAPES = ["\\", "'"] 185 186 SINGLE_TOKENS = { 187 **tokens.Tokenizer.SINGLE_TOKENS, 188 "$": TokenType.PARAMETER, 189 } 190 191 KEYWORDS = { 192 **tokens.Tokenizer.KEYWORDS, 193 "EXCLUDE": TokenType.EXCEPT, 194 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 195 "PUT": TokenType.COMMAND, 196 "RENAME": TokenType.REPLACE, 197 "TIMESTAMP_LTZ": TokenType.TIMESTAMPLTZ, 198 "TIMESTAMP_NTZ": TokenType.TIMESTAMP, 199 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 200 "TIMESTAMPNTZ": TokenType.TIMESTAMP, 201 "MINUS": TokenType.EXCEPT, 202 "SAMPLE": TokenType.TABLE_SAMPLE, 203 }
Inherited Members
205 class Generator(generator.Generator): 206 CREATE_TRANSIENT = True 207 208 TRANSFORMS = { 209 **generator.Generator.TRANSFORMS, # type: ignore 210 exp.Array: inline_array_sql, 211 exp.ArrayConcat: rename_func("ARRAY_CAT"), 212 exp.DateAdd: rename_func("DATEADD"), 213 exp.DateStrToDate: datestrtodate_sql, 214 exp.DataType: _datatype_sql, 215 exp.If: rename_func("IFF"), 216 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 217 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 218 exp.Parameter: lambda self, e: f"${self.sql(e, 'this')}", 219 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 220 exp.Matches: rename_func("DECODE"), 221 exp.StrPosition: lambda self, e: f"{self.normalize_func('POSITION')}({self.format_args(e.args.get('substr'), e.this, e.args.get('position'))})", 222 exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})", 223 exp.TimeStrToTime: timestrtotime_sql, 224 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 225 exp.Trim: lambda self, e: f"TRIM({self.format_args(e.this, e.expression)})", 226 exp.UnixToTime: _unix_to_time_sql, 227 exp.DayOfWeek: rename_func("DAYOFWEEK"), 228 } 229 230 TYPE_MAPPING = { 231 **generator.Generator.TYPE_MAPPING, # type: ignore 232 exp.DataType.Type.TIMESTAMP: "TIMESTAMPNTZ", 233 } 234 235 STAR_MAPPING = { 236 "except": "EXCLUDE", 237 "replace": "RENAME", 238 } 239 240 def except_op(self, expression): 241 if not expression.args.get("distinct", False): 242 self.unsupported("EXCEPT with All is not supported in Snowflake") 243 return super().except_op(expression) 244 245 def intersect_op(self, expression): 246 if not expression.args.get("distinct", False): 247 self.unsupported("INTERSECT with All is not supported in Snowflake") 248 return super().intersect_op(expression) 249 250 def values_sql(self, expression: exp.Values) -> str: 251 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted. 252 253 We also want to make sure that after we find matches where we need to unquote a column that we prevent users 254 from adding quotes to the column by using the `identify` argument when generating the SQL. 255 """ 256 alias = expression.args.get("alias") 257 if alias and alias.args.get("columns"): 258 expression = expression.transform( 259 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 260 if isinstance(node, exp.Identifier) 261 and isinstance(node.parent, exp.TableAlias) 262 and node.arg_key == "columns" 263 else node, 264 ) 265 return self.no_identify(lambda: super(self.__class__, self).values_sql(expression)) 266 return super().values_sql(expression) 267 268 def select_sql(self, expression: exp.Select) -> str: 269 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted and also 270 that all columns in a SELECT are unquoted. We also want to make sure that after we find matches where we need 271 to unquote a column that we prevent users from adding quotes to the column by using the `identify` argument when 272 generating the SQL. 273 274 Note: We make an assumption that any columns referenced in a VALUES expression should be unquoted throughout the 275 expression. This might not be true in a case where the same column name can be sourced from another table that can 276 properly quote but should be true in most cases. 277 """ 278 values_expressions = expression.find_all(exp.Values) 279 values_identifiers = set( 280 flatten( 281 v.args.get("alias", exp.Alias()).args.get("columns", []) 282 for v in values_expressions 283 ) 284 ) 285 if values_identifiers: 286 expression = expression.transform( 287 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 288 if isinstance(node, exp.Identifier) and node in values_identifiers 289 else node, 290 ) 291 return self.no_identify(lambda: super(self.__class__, self).select_sql(expression)) 292 return super().select_sql(expression) 293 294 def describe_sql(self, expression: exp.Describe) -> str: 295 # Default to table if kind is unknown 296 kind_value = expression.args.get("kind") or "TABLE" 297 kind = f" {kind_value}" if kind_value else "" 298 this = f" {self.sql(expression, 'this')}" 299 return f"DESCRIBE{kind}{this}" 300 301 def generatedasidentitycolumnconstraint_sql( 302 self, expression: exp.GeneratedAsIdentityColumnConstraint 303 ) -> str: 304 start = expression.args.get("start") 305 start = f" START {start}" if start else "" 306 increment = expression.args.get("increment") 307 increment = f" INCREMENT {increment}" if increment else "" 308 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
250 def values_sql(self, expression: exp.Values) -> str: 251 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted. 252 253 We also want to make sure that after we find matches where we need to unquote a column that we prevent users 254 from adding quotes to the column by using the `identify` argument when generating the SQL. 255 """ 256 alias = expression.args.get("alias") 257 if alias and alias.args.get("columns"): 258 expression = expression.transform( 259 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 260 if isinstance(node, exp.Identifier) 261 and isinstance(node.parent, exp.TableAlias) 262 and node.arg_key == "columns" 263 else node, 264 ) 265 return self.no_identify(lambda: super(self.__class__, self).values_sql(expression)) 266 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.
268 def select_sql(self, expression: exp.Select) -> str: 269 """Due to a bug in Snowflake we want to make sure that all columns in a VALUES table alias are unquoted and also 270 that all columns in a SELECT are unquoted. We also want to make sure that after we find matches where we need 271 to unquote a column that we prevent users from adding quotes to the column by using the `identify` argument when 272 generating the SQL. 273 274 Note: We make an assumption that any columns referenced in a VALUES expression should be unquoted throughout the 275 expression. This might not be true in a case where the same column name can be sourced from another table that can 276 properly quote but should be true in most cases. 277 """ 278 values_expressions = expression.find_all(exp.Values) 279 values_identifiers = set( 280 flatten( 281 v.args.get("alias", exp.Alias()).args.get("columns", []) 282 for v in values_expressions 283 ) 284 ) 285 if values_identifiers: 286 expression = expression.transform( 287 lambda node: exp.Identifier(**{**node.args, "quoted": False}) 288 if isinstance(node, exp.Identifier) and node in values_identifiers 289 else node, 290 ) 291 return self.no_identify(lambda: super(self.__class__, self).select_sql(expression)) 292 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.
301 def generatedasidentitycolumnconstraint_sql( 302 self, expression: exp.GeneratedAsIdentityColumnConstraint 303 ) -> str: 304 start = expression.args.get("start") 305 start = f" START {start}" if start else "" 306 increment = expression.args.get("increment") 307 increment = f" INCREMENT {increment}" if increment else "" 308 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
- checkcolumnconstraint_sql
- commentcolumnconstraint_sql
- collatecolumnconstraint_sql
- encodecolumnconstraint_sql
- defaultcolumnconstraint_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
- 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
- format_args
- text_width
- format_time
- expressions
- op_expressions
- naked_property
- set_operation
- tag_sql
- token_sql
- userdefinedfunction_sql
- userdefinedfunctionkwarg_sql
- joinhint_sql
- kwarg_sql
- when_sql
- merge_sql