Repository: tobymao/sqlglot

Issue:
Snowflake `Create ... Clone` not supported
```
>>> from sqlglot import parse
>>> parse('create or replace table table2 clone table1;', read='snowflake')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/ian.hook/.local/share/virtualenvs/mwaa-dags-pICechnl/lib/python3.11/site-packages/sqlglot/__init__.py", line 75, in parse
    return dialect.parse(sql, **opts)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/ian.hook/.local/share/virtualenvs/mwaa-dags-pICechnl/lib/python3.11/site-packages/sqlglot/dialects/dialect.py", line 151, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/ian.hook/.local/share/virtualenvs/mwaa-dags-pICechnl/lib/python3.11/site-packages/sqlglot/parser.py", line 854, in parse
    return self._parse(
           ^^^^^^^^^^^^
  File "/Users/ian.hook/.local/share/virtualenvs/mwaa-dags-pICechnl/lib/python3.11/site-packages/sqlglot/parser.py", line 920, in _parse
    self.raise_error("Invalid expression / Unexpected token")
  File "/Users/ian.hook/.local/share/virtualenvs/mwaa-dags-pICechnl/lib/python3.11/site-packages/sqlglot/parser.py", line 963, in raise_error
    raise error
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 1, Col: 37.
  create or replace table table2 clone table1;
```

https://docs.snowflake.com/en/sql-reference/sql/create-clone


Discussion:

Patch:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 27acb27a..84bf0a47 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -52,8 +52,12 @@ def _snowflake_to_timestamp(args: t.Sequence) -> t.Union[exp.StrToTime, exp.Unix
 
         return exp.UnixToTime(this=first_arg, scale=timescale)
 
+    from sqlglot.optimizer.simplify import simplify_literals
+
+    # The first argument might be an expression like 40 * 365 * 86400, so we try to
+    # reduce it using `simplify_literals` first and then check if it's a Literal.
     first_arg = seq_get(args, 0)
-    if not isinstance(first_arg, Literal):
+    if not isinstance(simplify_literals(first_arg, root=True), Literal):
         # case: <variant_expr>
         return format_time_lambda(exp.StrToTime, "snowflake", default=True)(args)
 
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 953c0fd2..048dc55a 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -949,6 +949,17 @@ class Create(Expression):
         "indexes": False,
         "no_schema_binding": False,
         "begin": False,
+        "clone": False,
+    }
+
+
+# https://docs.snowflake.com/en/sql-reference/sql/create-clone
+class Clone(Expression):
+    arg_types = {
+        "this": True,
+        "when": False,
+        "kind": False,
+        "expression": False,
     }
 
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 3001a548..84fa74ae 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -725,9 +725,23 @@ class Generator:
             " WITH NO SCHEMA BINDING" if expression.args.get("no_schema_binding") else ""
         )
 
-        expression_sql = f"CREATE{modifiers} {kind}{exists_sql} {this}{properties_sql}{expression_sql}{postexpression_props_sql}{index_sql}{no_schema_binding}"
+        clone = self.sql(expression, "clone")
+        clone = f" {clone}" if clone else ""
+
+        expression_sql = f"CREATE{modifiers} {kind}{exists_sql} {this}{properties_sql}{expression_sql}{postexpression_props_sql}{index_sql}{no_schema_binding}{clone}"
         return self.prepend_ctes(expression, expression_sql)
 
+    def clone_sql(self, expression: exp.Clone) -> str:
+        this = self.sql(expression, "this")
+        when = self.sql(expression, "when")
+
+        if when:
+            kind = self.sql(expression, "kind")
+            expr = self.sql(expression, "expression")
+            return f"CLONE {this} {when} ({kind} => {expr})"
+
+        return f"CLONE {this}"
+
     def describe_sql(self, expression: exp.Describe) -> str:
         return f"DESCRIBE {self.sql(expression, 'this')}"
 
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 19352f8b..b6c99bd7 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -768,6 +768,8 @@ class Parser(metaclass=_Parser):
 
     INSERT_ALTERNATIVES = {"ABORT", "FAIL", "IGNORE", "REPLACE", "ROLLBACK"}
 
+    CLONE_KINDS = {"TIMESTAMP", "OFFSET", "STATEMENT"}
+
     WINDOW_ALIAS_TOKENS = ID_VAR_TOKENS - {TokenType.ROWS}
     WINDOW_BEFORE_PAREN_TOKENS = {TokenType.OVER}
 
@@ -1152,6 +1154,7 @@ class Parser(metaclass=_Parser):
         indexes = None
         no_schema_binding = None
         begin = None
+        clone = None
 
         if create_token.token_type in (TokenType.FUNCTION, TokenType.PROCEDURE):
             this = self._parse_user_defined_function(kind=create_token.token_type)
@@ -1234,6 +1237,20 @@ class Parser(metaclass=_Parser):
                 if self._match_text_seq("WITH", "NO", "SCHEMA", "BINDING"):
                     no_schema_binding = True
 
+            if self._match_text_seq("CLONE"):
+                clone = self._parse_table(schema=True)
+                when = self._match_texts({"AT", "BEFORE"}) and self._prev.text.upper()
+                clone_kind = (
+                    self._match(TokenType.L_PAREN)
+                    and self._match_texts(self.CLONE_KINDS)
+                    and self._prev.text.upper()
+                )
+                clone_expression = self._match(TokenType.FARROW) and self._parse_bitwise()
+                self._match(TokenType.R_PAREN)
+                clone = self.expression(
+                    exp.Clone, this=clone, when=when, kind=clone_kind, expression=clone_expression
+                )
+
         return self.expression(
             exp.Create,
             this=this,
@@ -1246,6 +1263,7 @@ class Parser(metaclass=_Parser):
             indexes=indexes,
             no_schema_binding=no_schema_binding,
             begin=begin,
+            clone=clone,
         )
 
     def _parse_property_before(self) -> t.Optional[exp.Expression]:


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 7b00c60b..baf83bed 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -555,10 +555,22 @@ class TestSnowflake(Validator):
         )
 
     def test_ddl(self):
+        self.validate_identity("CREATE MATERIALIZED VIEW a COMMENT='...' AS SELECT 1 FROM x")
+        self.validate_identity("CREATE DATABASE mytestdb_clone CLONE mytestdb")
+        self.validate_identity("CREATE SCHEMA mytestschema_clone CLONE testschema")
+        self.validate_identity("CREATE TABLE orders_clone CLONE orders")
+        self.validate_identity(
+            "CREATE TABLE orders_clone_restore CLONE orders AT (TIMESTAMP => TO_TIMESTAMP_TZ('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss'))"
+        )
+        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) WITH (PARTITION BY (x), integration='q', auto_refresh=TRUE, file_format=(type = parquet))"
         )
-        self.validate_identity("CREATE MATERIALIZED VIEW a COMMENT='...' AS SELECT 1 FROM x")
+        self.validate_identity(
+            "CREATE SCHEMA mytestschema_clone_restore CLONE testschema BEFORE (TIMESTAMP => TO_TIMESTAMP(40 * 365 * 86400))"
+        )
 
         self.validate_all(
             "CREATE OR REPLACE TRANSIENT TABLE a (id INT)",