Repository: tobymao/sqlglot

Issue:
LOCATION not supported in CREATE TABLE
Use case is Spark DDL with Iceberg, example:

```sql
create table a (b timestamp, c int)
using iceberg
partitioned by (months(b))
location 's3://tables/a'
```

Discussion:

Patch:
diff --git a/sqlglot/constants.py b/sqlglot/constants.py
index 21b8d888..863e250b 100644
--- a/sqlglot/constants.py
+++ b/sqlglot/constants.py
@@ -1,3 +1,4 @@
-TABLE_FORMAT = "TABLE_FORMAT"
 FILE_FORMAT = "FORMAT"
+LOCATION = "LOCATION"
 PARTITIONED_BY = "PARTITIONED_BY"
+TABLE_FORMAT = "TABLE_FORMAT"
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index c22248b2..acdd57f6 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -57,12 +57,16 @@ def _properties_sql(self, expression):
     using = ""
     stored_as = ""
     partitioned_by = ""
+    location = ""
 
     for p in properties:
-        if p.name.upper() == c.TABLE_FORMAT:
+        name = p.name.upper()
+        if name == c.TABLE_FORMAT:
             using = p
-        elif p.name.upper() == c.FILE_FORMAT:
+        elif name == c.FILE_FORMAT:
             stored_as = p
+        elif name == c.LOCATION:
+            location = p
         elif isinstance(p.args["value"], exp.Schema):
             partitioned_by = p
 
@@ -77,6 +81,9 @@ def _properties_sql(self, expression):
     if stored_as:
         properties.remove(stored_as)
         stored_as = self.seg(f"STORED AS {stored_as.text('value').upper()}")
+    if location:
+        properties.remove(location)
+        stored_as = self.seg(f"LOCATION {self.sql(location, 'value')}")
 
     return f"{using}{partitioned_by}{stored_as}{self.properties('TBLPROPERTIES', expression)}"
 
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 1bb769f5..4bb10b01 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -94,6 +94,7 @@ class Parser:
         TokenType.IF,
         TokenType.INTERVAL,
         TokenType.LAZY,
+        TokenType.LOCATION,
         TokenType.NEXT,
         TokenType.ONLY,
         TokenType.OPTIMIZE,
@@ -106,6 +107,7 @@ class Parser:
         TokenType.SCHEMA_COMMENT,
         TokenType.SET,
         TokenType.SHOW,
+        TokenType.STORED,
         TokenType.TABLE,
         TokenType.TEMPORARY,
         TokenType.TOP,
@@ -113,6 +115,7 @@ class Parser:
         TokenType.TRUE,
         TokenType.UNBOUNDED,
         TokenType.UNIQUE,
+        TokenType.PROPERTIES,
         *SUBQUERY_PREDICATES,
         *TYPE_TOKENS,
     }
@@ -610,6 +613,14 @@ class Parser:
                         value=exp.Literal.string(self._parse_var().text("this")),
                     )
                 )
+            if self._match(TokenType.LOCATION):
+                properties.append(
+                    self.expression(
+                        exp.Property,
+                        this=exp.Literal.string(c.LOCATION),
+                        value=self._parse_string(),
+                    )
+                )
 
             if self._match(TokenType.PROPERTIES):
                 self._match_l_paren()
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index f9a402d6..2a137e98 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -146,6 +146,7 @@ class TokenType(AutoName):
     LEFT = auto()
     LIKE = auto()
     LIMIT = auto()
+    LOCATION = auto()
     MAP = auto()
     MOD = auto()
     NEXT = auto()
@@ -383,6 +384,7 @@ class Tokenizer(metaclass=_Tokenizer):
         "LEFT": TokenType.LEFT,
         "LIKE": TokenType.LIKE,
         "LIMIT": TokenType.LIMIT,
+        "LOCATION": TokenType.LOCATION,
         "NEXT": TokenType.NEXT,
         "NOT": TokenType.NOT,
         "NULL": TokenType.NULL,


Test patch:
diff --git a/tests/test_dialects.py b/tests/test_dialects.py
index a8013dc1..84bb09b8 100644
--- a/tests/test_dialects.py
+++ b/tests/test_dialects.py
@@ -1436,6 +1436,12 @@ class TestDialects(unittest.TestCase):
             read="spark",
             write="presto",
         )
+        self.validate(
+            "CREATE TABLE x USING ICEBERG PARTITIONED BY (MONTHS(y)) LOCATION 's3://z'",
+            "CREATE TABLE x USING ICEBERG PARTITIONED BY (MONTHS(y)) LOCATION 's3://z'",
+            read="spark",
+            write="spark",
+        )
         self.validate(
             "CREATE TABLE test STORED AS PARQUET AS SELECT 1",
             "CREATE TABLE test WITH (FORMAT = 'PARQUET') AS SELECT 1",