Repository: tobymao/sqlglot
Pull request: fix(snowflake): COPY postfix
Fixes #3388

Design notes
---------------
- Snowflake's `FILE_PARAM` option is now parsed & generated on its own to accommodate for the optional `formatTypeOptions` that might come after the `TYPE` opts:

```
FILE_FORMAT = (  
 FORMAT_NAME = '[<namespace>.]<file_format_name>' | 
 TYPE = { CSV | ... | XML } [ formatTypeOptions ] 
) 
```

- Allowed `parse_primary()` to parse expressions such as `NULL_IF = ()` into empty tuples. That would otherwise try to be parsed in an `exp.Paren` with no expression, which would throw an error

- Refactored the file parsing to also include Snowflake's `@external_file` syntax besides strings 

- Added the issue query and other Snowflake doc examples as test cases

Docs
--------
- [Snowflake COPY](https://docs.snowflake.com/en/sql-reference/sql/copy-into-table#syntax)

Problem statement:
Version 23.12.2 fails to parse some COPY clauses
Looks as if in prior versions it didn't fully parse and fell back to the default Command but with 23.12.2 it's trying to parse and failing. Maybe we can have it fall back to Command as we work through various options COPY has?

**Fully reproducible code snippet**

```python
from sqlglot import parse_one

q = """
copy into temp FROM @random_stage/path/
FILE_FORMAT = (
    TYPE=CSV
    FIELD_DELIMITER='|'
    NULL_IF=()
    FIELD_OPTIONALLY_ENCLOSED_BY='"'
    TIMESTAMP_FORMAT='TZHTZM YYYY-MM-DD HH24:MI:SS.FF9'
    DATE_FORMAT='TZHTZM YYYY-MM-DD HH24:MI:SS.FF9'
    BINARY_FORMAT=BASE64
  )
"""

pq = parse_one(q, read="snowflake")
print("Parsed:", pq, "\n")
```

The above gives the following using 23.12.1:
```
✗ python test_sqlglot.py 
'copy into temp FROM @random_stage/path/
FILE_FORMAT = (
    TYPE=CSV
    FIELD_DELIMITER='|'
    NUL' contains unsupported syntax. Falling back to parsing as a 'Command'.
Parsed: COPY into temp FROM @random_stage/path/
FILE_FORMAT = (
    TYPE=CSV
    FIELD_DELIMITER='|'
    NULL_IF=()
    FIELD_OPTIONALLY_ENCLOSED_BY='"'
    TIMESTAMP_FORMAT='TZHTZM YYYY-MM-DD HH24:MI:SS.FF9'
    DATE_FORMAT='TZHTZM YYYY-MM-DD HH24:MI:SS.FF9'
    BINARY_FORMAT=BASE64
  ) 
```

And the following using 23.12.2:
```
✗ python test_sqlglot.py       
Traceback (most recent call last):
  File "/Users/dangoldin/code/analysis/test_sqlglot.py", line 494, in <module>
    pq = parse_one(q, read="snowflake")
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/__init__.py", line 139, in parse_one
    result = dialect.parse(sql, **opts)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/dialects/dialect.py", line 506, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 1175, in parse
    return self._parse(
           ^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 1241, in _parse
    expressions.append(parse_method(self))
                       ^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 1470, in _parse_statement
    return self.STATEMENT_PARSERS[self._prev.token_type](self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 631, in <lambda>
    TokenType.COPY: lambda self: self._parse_copy(),
                                 ^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 6363, in _parse_copy
    files = self._parse_csv(self._parse_conjunction)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 5649, in _parse_csv
    parse_result = parse_method()
                   ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3808, in _parse_conjunction
    return self._parse_tokens(self._parse_equality, self.CONJUNCTION)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 5663, in _parse_tokens
    this = parse_method()
           ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3811, in _parse_equality
    return self._parse_tokens(self._parse_comparison, self.EQUALITY)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 5670, in _parse_tokens
    expression=parse_method(),
               ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3814, in _parse_comparison
    return self._parse_tokens(self._parse_range, self.COMPARISON)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 5663, in _parse_tokens
    this = parse_method()
           ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3817, in _parse_range
    this = this or self._parse_bitwise()
                   ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3941, in _parse_bitwise
    this = self._parse_term()
           ^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3973, in _parse_term
    return self._parse_tokens(self._parse_factor, self.TERM)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 5663, in _parse_tokens
    this = parse_method()
           ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3977, in _parse_factor
    this = parse_method()
           ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3998, in _parse_unary
    return self._parse_at_time_zone(self._parse_type())
                                    ^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 4020, in _parse_type
    this = self._parse_column()
           ^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 4220, in _parse_column
    this = self._parse_column_reference()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 4224, in _parse_column_reference
    this = self._parse_field()
           ^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 4347, in _parse_field
    field = self._parse_primary() or self._parse_function(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 4330, in _parse_primary
    self._match_r_paren(expression=this)
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 6196, in _match_r_paren
    self.raise_error("Expecting )")
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 1285, in raise_error
    raise error
sqlglot.errors.ParseError: Expecting ). Line 5, Col: 17.
  
copy into temp FROM @random_stage/path/
FILE_FORMAT = (
    TYPE=CSV
    FIELD_DELIMITER='|'
    NULL_IF=()
    FIELD_OPTIONALLY_ENCLOSED_BY='"'
    TIMESTAMP_FORMAT='TZHTZM YYYY-MM-DD HH24
```

**Official Documentation**
Please include links to official SQL documentation related to your issue.

Hints from issue discussion:
Sorry for the trouble @dangoldin, this was an oversight on our end. Should have deployed a minor version. We'll fix this soon and look into deploying a patch.
All good and appreciate the responsiveness. I just fell back to 23.12.1 in my code and would attempt a fix here but haven't been keeping up with the changes here so it was quicker to quickly file this.
No worries. This is probably an edge case, because we _do_ have some [logic](https://github.com/tobymao/sqlglot/commit/d2a6f16c35cbe355932d0e0eab2fc6ba096d8a97#diff-63eb8dd82d3561bc414e3e13cf59516bd6584c5766a10072157a8a5aee4ad85fR6370-R6372) that tries to parse this into a `Command` as a fallback case, so it fails somewhere in between.

Commit message: Add missing type hints

Patch to review:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index c87f416d90..ce2fa7bb53 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -585,16 +585,21 @@ def _parse_at_before(self, table: exp.Table) -> exp.Table:
 
             return table
 
-        def _parse_table_parts(
-            self, schema: bool = False, is_db_reference: bool = False, wildcard: bool = False
-        ) -> exp.Table:
+        def _parse_file_location(self) -> t.Optional[exp.Expression]:
             # https://docs.snowflake.com/en/user-guide/querying-stage
             if self._match(TokenType.STRING, advance=False):
-                table = self._parse_string()
+                file = self._parse_string()
             elif self._match_text_seq("@", advance=False):
-                table = self._parse_location_path()
+                file = self._parse_location_path()
             else:
-                table = None
+                file = None
+
+            return file
+
+        def _parse_table_parts(
+            self, schema: bool = False, is_db_reference: bool = False, wildcard: bool = False
+        ) -> exp.Table:
+            table = self._parse_file_location()
 
             if table:
                 file_format = None
@@ -1037,3 +1042,11 @@ def struct_sql(self, expression: exp.Struct) -> str:
                     values.append(e)
 
             return self.func("OBJECT_CONSTRUCT", *flatten(zip(keys, values)))
+
+        def copyparameter_sql(self, expression: exp.CopyParameter) -> str:
+            option = self.sql(expression, "this")
+            if option == "FILE_FORMAT":
+                value = self.expressions(expression, key="expression", flat=True, sep=" ")
+                return f"{option} = ({value})"
+
+            return super().copyparameter_sql(expression)
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 6dbc72d6b3..a017734e4d 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3795,20 +3795,20 @@ def credentials_sql(self, expression: exp.Credentials) -> str:
         if isinstance(cred_expr, exp.Literal):
             # Redshift case: CREDENTIALS <string>
             credentials = self.sql(expression, "credentials")
-            credentials = f"CREDENTIALS {credentials}"
+            credentials = f" CREDENTIALS {credentials}" if credentials else ""
         else:
             # Snowflake case: CREDENTIALS = (...)
             credentials = self.expressions(expression, key="credentials", flat=True, sep=" ")
-            credentials = f"CREDENTIALS = ({credentials})" if credentials else ""
+            credentials = f" CREDENTIALS = ({credentials})" if credentials else ""
 
         storage = self.sql(expression, "storage")
         storage = f" {storage}" if storage else ""
 
         encryption = self.expressions(expression, key="encryption", flat=True, sep=" ")
-        encryption = f"ENCRYPTION = ({encryption})" if encryption else ""
+        encryption = f" ENCRYPTION = ({encryption})" if encryption else ""
 
         iam_role = self.sql(expression, "iam_role")
-        iam_role = f"IAM_ROLE {iam_role}" if iam_role else ""
+        iam_role = f" IAM_ROLE {iam_role}" if iam_role else ""
 
         region = self.sql(expression, "region")
         region = f" REGION {region}" if region else ""
@@ -3820,7 +3820,7 @@ def copy_sql(self, expression: exp.Copy) -> str:
         this = f" INTO {this}" if self.COPY_HAS_INTO_KEYWORD else f" {this}"
 
         credentials = self.sql(expression, "credentials")
-        credentials = f" {credentials}" if credentials else ""
+        credentials = f"{credentials}" if credentials else ""
 
         kind = " FROM " if expression.args.get("kind") else " TO "
         files = self.expressions(expression, key="files", flat=True)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 9969e95ca4..4857c92890 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -4315,7 +4315,9 @@ def _parse_primary(self) -> t.Optional[exp.Expression]:
 
             this = self._parse_query_modifiers(seq_get(expressions, 0))
 
-            if isinstance(this, exp.UNWRAPPED_QUERIES):
+            if not this and self._match(TokenType.R_PAREN, advance=False):
+                this = self.expression(exp.Tuple)
+            elif isinstance(this, exp.UNWRAPPED_QUERIES):
                 this = self._parse_set_operations(
                     self._parse_subquery(this=this, parse_alias=False)
                 )
@@ -6305,6 +6307,15 @@ def _parse_with_operator(self) -> t.Optional[exp.Expression]:
 
         return self.expression(exp.WithOperator, this=this, op=op)
 
+    def _parse_wrapped_options(self) -> t.List[t.Optional[exp.Expression]]:
+        opts = []
+        self._match(TokenType.EQ)
+        self._match(TokenType.L_PAREN)
+        while self._curr and not self._match(TokenType.R_PAREN):
+            opts.append(self._parse_conjunction())
+            self._match(TokenType.COMMA)
+        return opts
+
     def _parse_copy_parameters(self) -> t.List[exp.CopyParameter]:
         sep = TokenType.COMMA if self.dialect.COPY_PARAMS_ARE_CSV else None
 
@@ -6312,12 +6323,19 @@ def _parse_copy_parameters(self) -> t.List[exp.CopyParameter]:
         while self._curr and not self._match(TokenType.R_PAREN, advance=False):
             option = self._parse_unquoted_field()
             value = None
+
             # Some options are defined as functions with the values as params
             if not isinstance(option, exp.Func):
+                prev = self._prev.text.upper()
                 # Different dialects might separate options and values by white space, "=" and "AS"
                 self._match(TokenType.EQ)
                 self._match(TokenType.ALIAS)
-                value = self._parse_unquoted_field()
+
+                if prev == "FILE_FORMAT" and self._match(TokenType.L_PAREN):
+                    # Snowflake FILE_FORMAT case
+                    value = self._parse_wrapped_options()
+                else:
+                    value = self._parse_unquoted_field()
 
             param = self.expression(exp.CopyParameter, this=option, expression=value)
             options.append(param)
@@ -6328,24 +6346,18 @@ def _parse_copy_parameters(self) -> t.List[exp.CopyParameter]:
         return options
 
     def _parse_credentials(self) -> t.Optional[exp.Credentials]:
-        def parse_options():
-            opts = []
-            self._match(TokenType.EQ)
-            self._match(TokenType.L_PAREN)
-            while self._curr and not self._match(TokenType.R_PAREN):
-                opts.append(self._parse_conjunction())
-            return opts
-
         expr = self.expression(exp.Credentials)
 
         if self._match_text_seq("STORAGE_INTEGRATION", advance=False):
             expr.set("storage", self._parse_conjunction())
         if self._match_text_seq("CREDENTIALS"):
             # Snowflake supports CREDENTIALS = (...), while Redshift CREDENTIALS <string>
-            creds = parse_options() if self._match(TokenType.EQ) else self._parse_field()
+            creds = (
+                self._parse_wrapped_options() if self._match(TokenType.EQ) else self._parse_field()
+            )
             expr.set("credentials", creds)
         if self._match_text_seq("ENCRYPTION"):
-            expr.set("encryption", parse_options())
+            expr.set("encryption", self._parse_wrapped_options())
         if self._match_text_seq("IAM_ROLE"):
             expr.set("iam_role", self._parse_field())
         if self._match_text_seq("REGION"):
@@ -6353,7 +6365,10 @@ def parse_options():
 
         return expr
 
-    def _parse_copy(self):
+    def _parse_file_location(self) -> t.Optional[exp.Expression]:
+        return self._parse_field()
+
+    def _parse_copy(self) -> exp.Copy | exp.Command:
         start = self._prev
 
         self._match(TokenType.INTO)
@@ -6366,7 +6381,7 @@ def _parse_copy(self):
 
         kind = self._match(TokenType.FROM) or not self._match_text_seq("TO")
 
-        files = self._parse_csv(self._parse_conjunction)
+        files = self._parse_csv(self._parse_file_location)
         credentials = self._parse_credentials()
 
         self._match_text_seq("WITH")
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 3419539af5..ed33366fe9 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -104,9 +104,6 @@ def test_snowflake(self):
         self.validate_identity(
             "SELECT * FROM DATA AS DATA_L ASOF JOIN DATA AS DATA_R MATCH_CONDITION (DATA_L.VAL > DATA_R.VAL) ON DATA_L.ID = DATA_R.ID"
         )
-        self.validate_identity(
-            "COPY INTO mytable (col1, col2) FROM 's3://mybucket/data/files' FILES = ('file1', 'file2') PATTERN = 'pattern' FILE_FORMAT = (FORMAT_NAME = my_csv_format) PARSE_HEADER = TRUE"
-        )
         self.validate_identity(
             "REGEXP_REPLACE('target', 'pattern', '\n')",
             "REGEXP_REPLACE('target', 'pattern', '\\n')",
@@ -1833,3 +1830,17 @@ def test_try_cast(self):
 
         expression = annotate_types(expression)
         self.assertEqual(expression.sql(dialect="snowflake"), "SELECT TRY_CAST(FOO() AS TEXT)")
+
+    def test_copy(self):
+        self.validate_identity(
+            """COPY INTO mytable (col1, col2) FROM 's3://mybucket/data/files' FILES = ('file1', 'file2') PATTERN = 'pattern' FILE_FORMAT = (FORMAT_NAME = my_csv_format NULL_IF = ('str1', 'str2')) PARSE_HEADER = TRUE"""
+        )
+        self.validate_identity(
+            """COPY INTO temp FROM @random_stage/path/ FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = '|' NULL_IF = () FIELD_OPTIONALLY_ENCLOSED_BY = '"' TIMESTAMP_FORMAT = 'TZHTZM YYYY-MM-DD HH24:MI:SS.FF9' DATE_FORMAT = 'TZHTZM YYYY-MM-DD HH24:MI:SS.FF9' BINARY_FORMAT = BASE64) VALIDATION_MODE = 'RETURN_3_ROWS'"""
+        )
+        self.validate_identity(
+            """COPY INTO load1 FROM @%load1/data1/ FILES = ('test1.csv', 'test2.csv') FORCE = TRUE"""
+        )
+        self.validate_identity(
+            """COPY INTO mytable FROM 'azure://myaccount.blob.core.windows.net/mycontainer/data/files' CREDENTIALS = (AZURE_SAS_TOKEN = 'token') ENCRYPTION = (TYPE = 'AZURE_CSE' MASTER_KEY = 'kPx...') FILE_FORMAT = (FORMAT_NAME = my_csv_format)"""
+        )


Code review comments:
diff_hunk: @@ -3820,7 +3820,7 @@ def copy_sql(self, expression: exp.Copy) -> str:
         this = f" INTO {this}" if self.COPY_HAS_INTO_KEYWORD else f" {this}"
 
         credentials = self.sql(expression, "credentials")
-        credentials = f" {credentials}" if credentials else ""
+        credentials = f"{credentials}" if credentials else ""
path: sqlglot/generator.py
text: @user1:
why was this space removed? if it's not needed, you don't need the f sting

@author:
Pushed the spaces down to `credentials_sql()` as there was a spacing bug with a certain combination of creds and was testing the result there, thanks for the catch