Repository: tobymao/sqlglot

Issue:
Convert SAMPLE (snowflake) to SAMPLE USING (duckdb)
```python
>>> sqlglot.parse_one("""
SELECT * FROM example SAMPLE (3) SEED (82);
            """, read="snowflake").sql(dialect="duckdb")
'SELECT * FROM example SAMPLE (3) REPEATABLE (82)'
```
however [duckdb requires USING SAMPLE](https://duckdb.org/docs/sql/query_syntax/sample), eg:
```
SELECT * FROM example USING SAMPLE (3) REPEATABLE (82)
```

I think this is because the parse tree is different, eg:

Snowflake

```python
>>> sqlglot.parse_one("""
SELECT * FROM example SAMPLE (3) SEED (82);
            """, read="snowflake").find(exp.Select).args["from"].this
(TABLESAMPLE size: 
  (LITERAL this: 3, is_string: False), seed: 
  (LITERAL this: 82, is_string: False), kind: SAMPLE, this: 
  (TABLE this: 
    (IDENTIFIER this: example, quoted: False)))
````                
           
Duckdb
```python
>>> sqlglot.parse_one("""
SELECT * FROM example USING SAMPLE (3) REPEATABLE (82);
            """, read="duckdb").find(exp.Select).args["sample"]
(TABLESAMPLE size: 
  (LITERAL this: 3, is_string: False), seed: 
  (LITERAL this: 82, is_string: False), kind: USING SAMPLE)
```              

Should these be the same parse trees? If not no worries, I'll add a transform instead.           

Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index fe214439..593e0e9c 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -484,6 +484,7 @@ class ClickHouse(Dialect):
         STRUCT_DELIMITER = ("(", ")")
         NVL2_SUPPORTED = False
         TABLESAMPLE_REQUIRES_PARENS = False
+        SAMPLE_CLAUSE = "SAMPLE"
 
         STRING_TYPE_MAPPING = {
             exp.DataType.Type.CHAR: "String",
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 879eb217..774feb32 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -307,6 +307,7 @@ class DuckDB(Dialect):
         RENAME_TABLE_WITH_DB = False
         NVL2_SUPPORTED = False
         SEMI_ANTI_JOIN_WITH_SIDE = False
+        SAMPLE_CLAUSE = "USING SAMPLE"
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -435,6 +436,21 @@ class DuckDB(Dialect):
             exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
         }
 
+        def tablesample_sql(
+            self,
+            expression: exp.TableSample,
+            seed_prefix: str = "SEED",
+            sep: str = " AS ",
+            sample_clause: t.Optional[str] = None,
+        ) -> str:
+            if not isinstance(expression.parent, exp.Select):
+                # This sample clause only applies to a single source, not the entire resulting relation
+                sample_clause = "TABLESAMPLE"
+
+            return super().tablesample_sql(
+                expression, seed_prefix="REPEATABLE", sep=sep, sample_clause=sample_clause
+            )
+
         def interval_sql(self, expression: exp.Interval) -> str:
             multiplier: t.Optional[int] = None
             unit = expression.text("unit").lower()
@@ -449,11 +465,6 @@ class DuckDB(Dialect):
 
             return super().interval_sql(expression)
 
-        def tablesample_sql(
-            self, expression: exp.TableSample, seed_prefix: str = "SEED", sep: str = " AS "
-        ) -> str:
-            return super().tablesample_sql(expression, seed_prefix="REPEATABLE", sep=sep)
-
         def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
             if isinstance(expression.parent, exp.UserDefinedFunction):
                 return self.sql(expression, "this")
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 51dbd535..1127148e 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -153,8 +153,8 @@ class Oracle(Dialect):
         COLUMN_JOIN_MARKS_SUPPORTED = True
         DATA_TYPE_SPECIFIERS_ALLOWED = True
         ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = False
-
         LIMIT_FETCH = "FETCH"
+        SAMPLE_CLAUSE = "SAMPLE"
 
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,
diff --git a/sqlglot/dialects/teradata.py b/sqlglot/dialects/teradata.py
index 0ccc5672..d6b4160b 100644
--- a/sqlglot/dialects/teradata.py
+++ b/sqlglot/dialects/teradata.py
@@ -175,6 +175,7 @@ class Teradata(Dialect):
         JOIN_HINTS = False
         TABLE_HINTS = False
         QUERY_HINTS = False
+        SAMPLE_CLAUSE = "SAMPLE"
 
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,
@@ -214,7 +215,11 @@ class Teradata(Dialect):
             return self.cast_sql(expression, safe_prefix="TRY")
 
         def tablesample_sql(
-            self, expression: exp.TableSample, seed_prefix: str = "SEED", sep=" AS "
+            self,
+            expression: exp.TableSample,
+            seed_prefix: str = "SEED",
+            sep: str = " AS ",
+            sample_clause: t.Optional[str] = None,
         ) -> str:
             return f"{self.sql(expression, 'this')} SAMPLE {self.expressions(expression)}"
 
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 623339b9..2e2a0acc 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -3481,7 +3481,6 @@ class TableSample(Expression):
         "rows": False,
         "size": False,
         "seed": False,
-        "kind": False,
     }
 
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 2a0b921c..e38ab504 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -235,6 +235,9 @@ class Generator:
     # Whether or not CONCAT requires >1 arguments
     SUPPORTS_SINGLE_ARG_CONCAT = True
 
+    # The keyword(s) to use when generating a sample clause
+    SAMPLE_CLAUSE = "TABLESAMPLE"
+
     TYPE_MAPPING = {
         exp.DataType.Type.NCHAR: "CHAR",
         exp.DataType.Type.NVARCHAR: "VARCHAR",
@@ -1459,7 +1462,11 @@ class Generator:
         return f"{table}{version}{file_format}{alias}{hints}{pivots}{joins}{laterals}{ordinality}"
 
     def tablesample_sql(
-        self, expression: exp.TableSample, seed_prefix: str = "SEED", sep=" AS "
+        self,
+        expression: exp.TableSample,
+        seed_prefix: str = "SEED",
+        sep: str = " AS ",
+        sample_clause: t.Optional[str] = None,
     ) -> str:
         if self.dialect.ALIAS_POST_TABLESAMPLE and expression.this and expression.this.alias:
             table = expression.this.copy()
@@ -1488,13 +1495,12 @@ class Generator:
 
         seed = self.sql(expression, "seed")
         seed = f" {seed_prefix} ({seed})" if seed else ""
-        kind = expression.args.get("kind", "TABLESAMPLE")
 
         expr = f"{bucket}{percent}{rows}{size}"
         if self.TABLESAMPLE_REQUIRES_PARENS:
             expr = f"({expr})"
 
-        return f"{this} {kind} {method}{expr}{seed}{alias}"
+        return f"{this} {sample_clause or self.SAMPLE_CLAUSE} {method}{expr}{seed}{alias}"
 
     def pivot_sql(self, expression: exp.Pivot) -> str:
         expressions = self.expressions(expression, flat=True)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index a67897bb..40efbb60 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2874,9 +2874,6 @@ class Parser(metaclass=_Parser):
         size = None
         seed = None
 
-        kind = (
-            self._prev.text if self._prev.token_type == TokenType.TABLE_SAMPLE else "USING SAMPLE"
-        )
         method = self._parse_var(tokens=(TokenType.ROW,), upper=True)
 
         matched_l_paren = self._match(TokenType.L_PAREN)
@@ -2926,7 +2923,6 @@ class Parser(metaclass=_Parser):
             rows=rows,
             size=size,
             seed=seed,
-            kind=kind,
         )
 
     def _parse_pivots(self) -> t.Optional[t.List[exp.Pivot]]:


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index a10c10e8..9b3c296d 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -712,6 +712,16 @@ class TestDuckDB(Validator):
                 "duckdb": "SELECT * FROM tbl, tbl2 WHERE tbl.i = tbl2.i USING SAMPLE RESERVOIR (20 PERCENT)"
             },
         )
+        self.validate_all(
+            "SELECT * FROM example TABLESAMPLE (3) REPEATABLE (82)",
+            read={
+                "snowflake": "SELECT * FROM example SAMPLE (3) SEED (82)",
+            },
+            write={
+                "duckdb": "SELECT * FROM example TABLESAMPLE (3) REPEATABLE (82)",
+                "snowflake": "SELECT * FROM example TABLESAMPLE (3) SEED (82)",
+            },
+        )
 
     def test_array(self):
         self.validate_identity("ARRAY(SELECT id FROM t)")
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 1c24bae4..3d016596 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -38,7 +38,6 @@ WHERE
 
         self.validate_identity("SELECT TO_ARRAY(CAST(x AS ARRAY))")
         self.validate_identity("SELECT TO_ARRAY(CAST(['test'] AS VARIANT))")
-        self.validate_identity("SELECT user_id, value FROM table_name sample ($s) SEED (0)")
         self.validate_identity("SELECT ARRAY_UNIQUE_AGG(x)")
         self.validate_identity("SELECT OBJECT_CONSTRUCT()")
         self.validate_identity("SELECT DAYOFMONTH(CURRENT_TIMESTAMP())")
@@ -74,6 +73,10 @@ WHERE
         self.validate_identity(
             'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage'
         )
+        self.validate_identity(
+            "SELECT user_id, value FROM table_name SAMPLE ($s) SEED (0)",
+            "SELECT user_id, value FROM table_name TABLESAMPLE ($s) SEED (0)",
+        )
         self.validate_identity(
             "SELECT * FROM foo at",
             "SELECT * FROM foo AS at",
@@ -676,15 +679,24 @@ WHERE
         self.validate_identity("SELECT * FROM testtable TABLESAMPLE (100)")
         self.validate_identity("SELECT * FROM testtable TABLESAMPLE SYSTEM (3) SEED (82)")
         self.validate_identity("SELECT * FROM testtable TABLESAMPLE (10 ROWS)")
-        self.validate_identity("SELECT * FROM testtable SAMPLE (10)")
-        self.validate_identity("SELECT * FROM testtable SAMPLE ROW (0)")
-        self.validate_identity("SELECT a FROM test SAMPLE BLOCK (0.5) SEED (42)")
         self.validate_identity(
             "SELECT i, j FROM table1 AS t1 INNER JOIN table2 AS t2 TABLESAMPLE (50) WHERE t2.j = t1.i"
         )
         self.validate_identity(
             "SELECT * FROM (SELECT * FROM t1 JOIN t2 ON t1.a = t2.c) TABLESAMPLE (1)"
         )
+        self.validate_identity(
+            "SELECT * FROM testtable SAMPLE (10)",
+            "SELECT * FROM testtable TABLESAMPLE (10)",
+        )
+        self.validate_identity(
+            "SELECT * FROM testtable SAMPLE ROW (0)",
+            "SELECT * FROM testtable TABLESAMPLE ROW (0)",
+        )
+        self.validate_identity(
+            "SELECT a FROM test SAMPLE BLOCK (0.5) SEED (42)",
+            "SELECT a FROM test TABLESAMPLE BLOCK (0.5) SEED (42)",
+        )
 
         self.validate_all(
             """
@@ -695,20 +707,20 @@ WHERE
                      table2 AS t2 SAMPLE (50)     -- 50% of rows in table2
                 WHERE t2.j = t1.i""",
             write={
-                "snowflake": "SELECT i, j FROM table1 AS t1 SAMPLE (25) /* 25% of rows in table1 */ INNER JOIN table2 AS t2 SAMPLE (50) /* 50% of rows in table2 */ WHERE t2.j = t1.i",
+                "snowflake": "SELECT i, j FROM table1 AS t1 TABLESAMPLE (25) /* 25% of rows in table1 */ INNER JOIN table2 AS t2 TABLESAMPLE (50) /* 50% of rows in table2 */ WHERE t2.j = t1.i",
             },
         )
         self.validate_all(
             "SELECT * FROM testtable SAMPLE BLOCK (0.012) REPEATABLE (99992)",
             write={
-                "snowflake": "SELECT * FROM testtable SAMPLE BLOCK (0.012) SEED (99992)",
+                "snowflake": "SELECT * FROM testtable TABLESAMPLE BLOCK (0.012) SEED (99992)",
             },
         )
         self.validate_all(
             "SELECT * FROM (SELECT * FROM t1 join t2 on t1.a = t2.c) SAMPLE (1)",
             write={
-                "snowflake": "SELECT * FROM (SELECT * FROM t1 JOIN t2 ON t1.a = t2.c) SAMPLE (1)",
-                "spark": "SELECT * FROM (SELECT * FROM t1 JOIN t2 ON t1.a = t2.c) SAMPLE (1 PERCENT)",
+                "snowflake": "SELECT * FROM (SELECT * FROM t1 JOIN t2 ON t1.a = t2.c) TABLESAMPLE (1)",
+                "spark": "SELECT * FROM (SELECT * FROM t1 JOIN t2 ON t1.a = t2.c) TABLESAMPLE (1 PERCENT)",
             },
         )