Repository: tobymao/sqlglot

Issue:
Snowflake - ParseError when multiple file_format options provided
```python
sqlglot.parse_one(
    """create external table et2(
  col1 date as (parse_json(metadata$external_table_partition):COL1::date),
  col2 varchar as (parse_json(metadata$external_table_partition):COL2::varchar),
  col3 number as (parse_json(metadata$external_table_partition):COL3::number))
  partition by (col1,col2,col3)
  location=@s2/logs/
  partition_type = user_specified
  file_format = (type = parquet COMPRESSION=gzip BINARY_AS_TEXT=FALSE)""",
    read="snowflake",
)
```

Errors with

```
ParseError: Expecting ). Line 8, Col: 43.
  )
  location=@s2/logs/
  partition_type = user_specified
  file_format = (type = parquet COMPRESSION=gzip BINARY_AS_TEXT=FALSE)
```

If I remove the `COMPRESSION=gzip BINARY_AS_TEXT=FALSE` options it works.

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 20eca3edc9..92f71b724b 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -516,6 +516,7 @@ class Parser(parser.Parser):
 
         PROPERTY_PARSERS = {
             **parser.Parser.PROPERTY_PARSERS,
+            "FILE_FORMAT": lambda self: self._parse_file_format_property(),
             "LOCATION": lambda self: self._parse_location_property(),
             "TAG": lambda self: self._parse_tag(),
             "USING": lambda self: self._match_text_seq("TEMPLATE")
@@ -546,9 +547,11 @@ class Parser(parser.Parser):
             "TERSE UNIQUE KEYS": _show_parser("UNIQUE KEYS"),
             "SEQUENCES": _show_parser("SEQUENCES"),
             "TERSE SEQUENCES": _show_parser("SEQUENCES"),
+            "STAGES": _show_parser("STAGES"),
             "COLUMNS": _show_parser("COLUMNS"),
             "USERS": _show_parser("USERS"),
             "TERSE USERS": _show_parser("USERS"),
+            "FILE FORMATS": _show_parser("FILE FORMATS"),
             "FUNCTIONS": _show_parser("FUNCTIONS"),
             "PROCEDURES": _show_parser("PROCEDURES"),
             "WAREHOUSES": _show_parser("WAREHOUSES"),
@@ -898,6 +901,12 @@ def _parse_foreign_key(self) -> exp.ForeignKey:
             # outoflineFK, explicitly names the columns
             return super()._parse_foreign_key()
 
+        def _parse_file_format_property(self) -> exp.FileFormatProperty:
+            self._match(TokenType.EQ)
+            return self.expression(
+                exp.FileFormatProperty, expressions=self._parse_wrapped_options()
+            )
+
     class Tokenizer(tokens.Tokenizer):
         STRING_ESCAPES = ["\\", "'"]
         HEX_STRINGS = [("x'", "'"), ("X'", "'")]
@@ -912,6 +921,7 @@ class Tokenizer(tokens.Tokenizer):
             "CHAR VARYING": TokenType.VARCHAR,
             "CHARACTER VARYING": TokenType.VARCHAR,
             "EXCLUDE": TokenType.EXCEPT,
+            "FILE FORMAT": TokenType.FILE_FORMAT,
             "ILIKE ANY": TokenType.ILIKE_ANY,
             "LIKE ANY": TokenType.LIKE_ANY,
             "MATCH_CONDITION": TokenType.MATCH_CONDITION,
@@ -993,6 +1003,8 @@ class Generator(generator.Generator):
             exp.DayOfYear: rename_func("DAYOFYEAR"),
             exp.Explode: rename_func("FLATTEN"),
             exp.Extract: rename_func("DATE_PART"),
+            exp.FileFormatProperty: lambda self,
+            e: f"FILE_FORMAT=({self.expressions(e, 'expressions', sep=' ')})",
             exp.FromTimeZone: lambda self, e: self.func(
                 "CONVERT_TIMEZONE", e.args.get("zone"), "'UTC'", e.this
             ),
@@ -1008,6 +1020,10 @@ class Generator(generator.Generator):
             exp.JSONObject: lambda self, e: self.func("OBJECT_CONSTRUCT_KEEP_NULL", *e.expressions),
             exp.JSONPathRoot: lambda *_: "",
             exp.JSONValueArray: _json_extract_value_array_sql,
+            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost")(
+                rename_func("EDITDISTANCE")
+            ),
+            exp.LocationProperty: lambda self, e: f"LOCATION={self.sql(e, 'this')}",
             exp.LogicalAnd: rename_func("BOOLAND_AGG"),
             exp.LogicalOr: rename_func("BOOLOR_AGG"),
             exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
@@ -1071,9 +1087,6 @@ class Generator(generator.Generator):
             exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
             exp.WeekOfYear: rename_func("WEEKOFYEAR"),
             exp.Xor: rename_func("BOOLXOR"),
-            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost")(
-                rename_func("EDITDISTANCE")
-            ),
         }
 
         SUPPORTED_JSON_PATH_PARTS = {
@@ -1094,6 +1107,8 @@ class Generator(generator.Generator):
 
         PROPERTIES_LOCATION = {
             **generator.Generator.PROPERTIES_LOCATION,
+            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
+            exp.LocationProperty: exp.Properties.Location.POST_WITH,
             exp.SetProperty: exp.Properties.Location.UNSUPPORTED,
             exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
         }
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 531b740fd0..f299824e28 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2785,7 +2785,7 @@ class FallbackProperty(Property):
 
 
 class FileFormatProperty(Property):
-    arg_types = {"this": True}
+    arg_types = {"this": False, "expressions": False}
 
 
 class FreespaceProperty(Property):
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 1575cea2ed..ba5d827b10 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -438,6 +438,7 @@ class Parser(metaclass=_Parser):
     DB_CREATABLES = {
         TokenType.DATABASE,
         TokenType.DICTIONARY,
+        TokenType.FILE_FORMAT,
         TokenType.MODEL,
         TokenType.NAMESPACE,
         TokenType.SCHEMA,
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 2f2bad6ca8..e212d27a76 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -278,6 +278,7 @@ class TokenType(AutoName):
     EXISTS = auto()
     FALSE = auto()
     FETCH = auto()
+    FILE_FORMAT = auto()
     FILTER = auto()
     FINAL = auto()
     FIRST = auto()


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index efb859f967..b5655cefc8 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -1487,6 +1487,9 @@ def test_ddl(self):
         self.validate_identity(
             "CREATE OR REPLACE TAG IF NOT EXISTS cost_center COMMENT='cost_center tag'"
         ).this.assert_is(exp.Identifier)
+        self.validate_identity(
+            "CREATE TEMPORARY FILE FORMAT fileformat1 TYPE=PARQUET COMPRESSION=auto"
+        ).this.assert_is(exp.Table)
         self.validate_identity(
             "CREATE DYNAMIC TABLE product (pre_tax_profit, taxes, after_tax_profit) TARGET_LAG='20 minutes' WAREHOUSE=mywh AS SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate) FROM staging_table"
         )
@@ -1502,9 +1505,6 @@ def test_ddl(self):
         self.validate_identity(
             "CREATE TABLE orders_clone_restore CLONE orders BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')"
         )
-        self.validate_identity(
-            "CREATE TABLE a (x DATE, y BIGINT) PARTITION BY (x) integration='q' auto_refresh=TRUE file_format=(type = parquet)"
-        )
         self.validate_identity(
             "CREATE SCHEMA mytestschema_clone_restore CLONE testschema BEFORE (TIMESTAMP => TO_TIMESTAMP(40 * 365 * 86400))"
         )
@@ -1547,8 +1547,8 @@ def test_ddl(self):
   partition by (col1,col2,col3)
   location=@s2/logs/
   partition_type = user_specified
-  file_format = (type = parquet)""",
-            "CREATE EXTERNAL TABLE et2 (col1 DATE AS (CAST(GET_PATH(PARSE_JSON(metadata$external_table_partition), 'COL1') AS DATE)), col2 VARCHAR AS (CAST(GET_PATH(PARSE_JSON(metadata$external_table_partition), 'COL2') AS VARCHAR)), col3 DECIMAL(38, 0) AS (CAST(GET_PATH(PARSE_JSON(metadata$external_table_partition), 'COL3') AS DECIMAL(38, 0)))) LOCATION @s2/logs/ PARTITION BY (col1, col2, col3) partition_type=user_specified file_format=(type = parquet)",
+  file_format = (type = parquet compression = gzip binary_as_text = false)""",
+            "CREATE EXTERNAL TABLE et2 (col1 DATE AS (CAST(GET_PATH(PARSE_JSON(metadata$external_table_partition), 'COL1') AS DATE)), col2 VARCHAR AS (CAST(GET_PATH(PARSE_JSON(metadata$external_table_partition), 'COL2') AS VARCHAR)), col3 DECIMAL(38, 0) AS (CAST(GET_PATH(PARSE_JSON(metadata$external_table_partition), 'COL3') AS DECIMAL(38, 0)))) PARTITION BY (col1, col2, col3) LOCATION=@s2/logs/ partition_type=user_specified FILE_FORMAT=(type=parquet compression=gzip binary_as_text=FALSE)",
         )
 
         self.validate_all(
@@ -2095,6 +2095,15 @@ def test_show_databases(self):
         self.assertEqual(ast.this, "DATABASES")
         self.assertEqual(ast.args.get("scope_kind"), "ACCOUNT")
 
+    def test_show_file_formats(self):
+        self.validate_identity("SHOW FILE FORMATS")
+        self.validate_identity("SHOW FILE FORMATS LIKE 'foo' IN DATABASE db1")
+        self.validate_identity("SHOW FILE FORMATS LIKE 'foo' IN SCHEMA db1.schema1")
+
+        ast = parse_one("SHOW FILE FORMATS IN ACCOUNT", read="snowflake")
+        self.assertEqual(ast.this, "FILE FORMATS")
+        self.assertEqual(ast.args.get("scope_kind"), "ACCOUNT")
+
     def test_show_functions(self):
         self.validate_identity("SHOW FUNCTIONS")
         self.validate_identity("SHOW FUNCTIONS LIKE 'foo' IN CLASS bla")
@@ -2112,6 +2121,15 @@ def test_show_procedures(self):
         self.assertEqual(ast.this, "PROCEDURES")
         self.assertEqual(ast.args.get("scope_kind"), "ACCOUNT")
 
+    def test_show_stages(self):
+        self.validate_identity("SHOW STAGES")
+        self.validate_identity("SHOW STAGES LIKE 'foo' IN DATABASE db1")
+        self.validate_identity("SHOW STAGES LIKE 'foo' IN SCHEMA db1.schema1")
+
+        ast = parse_one("SHOW STAGES IN ACCOUNT", read="snowflake")
+        self.assertEqual(ast.this, "STAGES")
+        self.assertEqual(ast.args.get("scope_kind"), "ACCOUNT")
+
     def test_show_warehouses(self):
         self.validate_identity("SHOW WAREHOUSES")
         self.validate_identity("SHOW WAREHOUSES LIKE 'foo' WITH PRIVILEGES USAGE, MODIFY")