Repository: tobymao/sqlglot

Issue:
Transpile from DuckDB to BigQuery for UUID results in a BigQuery column type of UUID but no such type exists in BQ
As the name suggests DuckDB defines a number of column types one of which is UUID. But such a type doesn't exist in BigQuery. The below snippet shows that.

```
>>> sqlg.transpile("create table foo(a long, id uuid)", read="duckdb", write="
... bigquery")[0]
'CREATE TABLE foo (a INT64, id UUID)'
```

There exists a BQ function to generate UUIDs: `GENERATE_UUID` and it returns a string.

SQLGlot should probably parse this and return it as a `STRING` when going from duckdb to BQ.

More types reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types


Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 883ee665..08dfbbc8 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -754,8 +754,9 @@ class BigQuery(Dialect):
             exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
             exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
             exp.DataType.Type.TINYINT: "INT64",
-            exp.DataType.Type.VARBINARY: "BYTES",
             exp.DataType.Type.ROWVERSION: "BYTES",
+            exp.DataType.Type.UUID: "STRING",
+            exp.DataType.Type.VARBINARY: "BYTES",
             exp.DataType.Type.VARCHAR: "STRING",
             exp.DataType.Type.VARIANT: "ANY TYPE",
         }


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 18a030c6..98d1604b 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -18,6 +18,13 @@ class TestDuckDB(Validator):
             "WITH _data AS (SELECT [STRUCT(1 AS a, 2 AS b), STRUCT(2 AS a, 3 AS b)] AS col) SELECT col.b FROM _data, UNNEST(_data.col) AS col WHERE col.a = 1",
         )
 
+        self.validate_all(
+            "CAST(x AS UUID)",
+            write={
+                "bigquery": "CAST(x AS STRING)",
+                "duckdb": "CAST(x AS UUID)",
+            },
+        )
         self.validate_all(
             """SELECT CASE WHEN JSON_VALID('{"x: 1}') THEN '{"x: 1}' ELSE NULL END""",
             read={