Repository: tobymao/sqlglot
Pull request: fix(starrocks): exp.Create transpilation
Fix #3997

> We closed #3998 because it was too convoluted, so I split out Starrocks own create table transpile fix here.

Supports starrocks create table parse and generator

This PR adds support for the following:
- Supports `DISTRIBUTED BY HASH|RANDOM`, `DUPLICATE KEY`, `PROPERTIES` to be parsed correctly
- I standardized the exp.PrimaryKey of Starrocks. Restore the PrimaryKey in properties to the schema to ensure that the dialect can read it correctly

## Docs

[starrocks distributed Doc](https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc)
 | [key_desc Doc](https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#key_desc)

Problem statement:
Incorect tranformation of create table using Starrocks
**Before you file an issue**
sqlglot.transpile(sql, read="starrocks", write="hive") # or  write="spark"

**Fully reproducible code snippet**
```
CREATE TABLE if not exists `sample_table` (
    `tenantid` varchar(1048576) NULL COMMENT "",
    `create_day` date NOT NULL COMMENT "",
    `shopsite` varchar(65533) NOT NULL COMMENT "shopsite",
    `id` varchar(65533) NOT NULL COMMENT "shopsite id",
    `price` decimal128(38, 10) NULL COMMENT "test the bigdecimal",
    `seq` int(11) NULL COMMENT "order",
    `use_status` smallint(6) NULL COMMENT "0,1",
    `created_user` bigint(20) NULL COMMENT "create user",
    `created_time` datetime NULL COMMENT "create time",
) ENGINE=OLAP
DUPLICATE KEY(tenantid)
PRIMARY KEY(`tenantid`, `shopsite`, `id`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`tenantid`, `shopsite`, `id`) BUCKETS 10
ORDER BY (`tenantid`, `shopsite`, `id`)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "enable_persistent_index" = "false",
    "replicated_storage" = "false",
    "storage_medium" = "HDD",
    "compression" = "LZ4"
)
```

The following create table statement of `starrocks` will report an error during `parse_one`:
```
Traceback (most recent call last):
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/parts/trans.py", line 74, in <module>
    raise ex
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/parts/trans.py", line 67, in <module>
    test_transpile_sql(os.path.join(dir, '../' + sys.argv[0]), read='starrocks', dialect='hive', pretty=True)
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/parts/trans.py", line 29, in test_transpile_sql
    raise ex
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/parts/trans.py", line 25, in test_transpile_sql
    transpiled_sql = sqlglot.transpile(sql, read=read, write=dialect)[0].strip()
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/__init__.py", line 174, in transpile
    for expression in parse(sql, read, error_level=error_level)
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/__init__.py", line 99, in parse
    return Dialect.get_or_raise(read or dialect).parse(sql, **opts)
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/dialects/dialect.py", line 894, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/parser.py", line 1366, in parse
    return self._parse(
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/parser.py", line 1435, in _parse
    expressions.append(parse_method(self))
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/parser.py", line 1667, in _parse_statement
    return self.STATEMENT_PARSERS[self._prev.token_type](self)
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/parser.py", line 758, in <lambda>
    TokenType.CREATE: lambda self: self._parse_create(),
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/parser.py", line 1820, in _parse_create
    this = self._parse_schema(this=table_parts)
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/parser.py", line 5225, in _parse_schema
    self._match_r_paren()
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/parser.py", line 6956, in _match_r_paren
    self.raise_error("Expecting )")
  File "/Users/zhanglin/PycharmProjects/sqlglot_master/sqlglot/parser.py", line 1479, in raise_error
    raise error
sqlglot.errors.ParseError: Expecting ). Line 6, Col: 19.
  3) NOT NULL COMMENT "shopsite",
    `id` varchar(65533) NOT NULL COMMENT "shopsite id",
    `price` decimal128(38, 10) NULL COMMENT "test the bigdecimal",
    `seq` int(11) NULL COMMENT "order",
    `use_status
```

Hints from issue discussion:
This is low priority for us right now, so I'll go ahead and close the ticket. Regardless, feel free to work on it. Make sure to check out the comments I left in #3998.

Commit message: fix(starrocks): exp.Create transpilation

Fix #3997

Supports starrocks create table parse and generator

This PR adds support for the following:
- Supports `DISTRIBUTED BY HASH|RANDOM`, `DUPLICATE KEY`, `PROPERTIES` to be parsed correctly
- I standardized the exp.PrimaryKey of Starrocks. Restore the PrimaryKey in properties to the schema to ensure that the dialect can read it correctly

## Docs

[starrocks distributed Doc](https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc)
 | [key_desc Doc](https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#key_desc)

Patch to review:
diff --git a/sqlglot/dialects/starrocks.py b/sqlglot/dialects/starrocks.py
index da0d28fb40..87b9d29bfa 100644
--- a/sqlglot/dialects/starrocks.py
+++ b/sqlglot/dialects/starrocks.py
@@ -13,11 +13,58 @@
 )
 from sqlglot.dialects.mysql import MySQL
 from sqlglot.helper import seq_get
+from sqlglot.tokens import TokenType
+
+
+def _duplicate_key_sql(self, expression: exp.DuplicateKeyProperty) -> str:
+    expressions = self.expressions(expression, flat=True)
+    options = self.expressions(expression, key="options", flat=True, sep=" ")
+    options = f" {options}" if options else ""
+    return f"DUPLICATE KEY ({expressions}){options}"
+
+
+def _distributed_by_hash_sql(self, expression: exp.DistributedByHashProperty) -> str:
+    expressions = self.expressions(expression, key="expressions", flat=True)
+    sorted_by = self.expressions(expression, key="sorted_by", flat=True)
+    sorted_by = f" ORDER BY ({sorted_by})" if sorted_by else ""
+    buckets = self.sql(expression, "buckets")
+    # Since StarRocks v2.5.7, the number of buckets is AUTO by default.
+    # https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc
+    if expression.auto_bucket:
+        buckets = "AUTO"
+    # [DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num] [ORDER BY (k1[,k2 ...])]]
+    return f"DISTRIBUTED BY HASH ({expressions}) BUCKETS {buckets}{sorted_by}"
+
+
+def _distributed_by_random_sql(self, expression: exp.DistributedByRandomProperty) -> str:
+    expressions = self.expressions(expression, flat=True)
+    sorted_by = self.expressions(expression, key="sorted_by", flat=True)
+    sorted_by = f" ORDER BY ({sorted_by})" if sorted_by else ""
+    buckets = self.sql(expression, "buckets")
+    if expression.auto_bucket:
+        buckets = "AUTO"
+    # [DISTRIBUTED BY RANDOM (k1[,k2 ...]) [BUCKETS num] [ORDER BY (k1[,k2 ...])]]
+    return f"DISTRIBUTED BY RANDOM ({expressions}) BUCKETS {buckets}{sorted_by}"
+
+
+def _property_sql(self: MySQL.Generator, expression: exp.Property) -> str:
+    # [PROPERTIES (['key1'='value1', 'key2'='value2', ...])]
+    return f"{self.property_name(expression, string_key=True)}={self.sql(expression, 'value')}"
 
 
 class StarRocks(MySQL):
     STRICT_JSON_PATH_SYNTAX = False
 
+    class Tokenizer(MySQL.Tokenizer):
+        KEYWORDS = {
+            **MySQL.Tokenizer.KEYWORDS,
+            "DECIMAL64": TokenType.DECIMAL,
+            "DECIMAL128": TokenType.BIGDECIMAL,
+            "DISTRIBUTED BY HASH": TokenType.DISTRIBUTE_BY,
+            "DISTRIBUTED BY RANDOM": TokenType.DISTRIBUTE_BY,
+            "DUPLICATE KEY": TokenType.DUPLICATE_KEY,
+        }
+
     class Parser(MySQL.Parser):
         FUNCTIONS = {
             **MySQL.Parser.FUNCTIONS,
@@ -31,6 +78,68 @@ class Parser(MySQL.Parser):
             "REGEXP": exp.RegexpLike.from_arg_list,
         }
 
+        PROPERTY_PARSERS = {
+            **MySQL.Parser.PROPERTY_PARSERS,
+            "DISTRIBUTED BY HASH": lambda self: self._parse_distributed_by("HASH"),
+            "DISTRIBUTED BY RANDOM": lambda self: self._parse_distributed_by("RANDOM"),
+            "PROPERTIES": lambda self: self._parse_wrapped_csv(self._parse_property),
+            "DUPLICATE KEY": lambda self: self._parse_duplicate(),
+            "PARTITION BY": lambda self: self._parse_partitioned_by(),
+        }
+
+        def _parse_create(self) -> exp.Create | exp.Command:
+            create = super()._parse_create()
+
+            # Starrocks's primary is defined outside the schema, so we need to move it into the schema
+            # https://docs.starrocks.io/docs/table_design/table_types/primary_key_table/#usage
+            if (
+                isinstance(create, exp.Create)
+                and create.this
+                and isinstance(create.this, exp.Schema)
+            ):
+                props = create.args.get("properties")
+                if props:
+                    primary_key = next(
+                        (expr for expr in props.expressions if isinstance(expr, exp.PrimaryKey)),
+                        None,
+                    )
+                    if primary_key:
+                        create.this.expressions.append(primary_key)
+
+                        # Remove the PrimaryKey from properties
+                        props.expressions.remove(primary_key)
+
+            return create
+
+        def _parse_distributed_by(self, type: str) -> exp.Expression:
+            expressions = self._parse_wrapped_csv(self._parse_id_var)
+
+            # If the BUCKETS keyword not present, the number of buckets is AUTO
+            # [ BUCKETS AUTO | BUCKETS <number> ]
+            buckets = None
+            if self._match_text_seq("BUCKETS", "AUTO"):
+                pass
+            elif self._match_text_seq("BUCKETS"):
+                buckets = self._parse_number()
+
+            if self._match_text_seq("ORDER BY"):
+                order_by = self._parse_wrapped_csv(self._parse_ordered)
+            else:
+                order_by = None
+
+            return self.expression(
+                exp.DistributedByHashProperty
+                if type == "HASH"
+                else exp.DistributedByRandomProperty,
+                expressions=expressions,
+                buckets=buckets,
+                sorted_by=order_by,
+            )
+
+        def _parse_duplicate(self) -> exp.DuplicateKeyProperty:
+            expressions = self._parse_wrapped_csv(self._parse_id_var, optional=False)
+            return self.expression(exp.DuplicateKeyProperty, expressions=expressions)
+
         def _parse_unnest(self, with_alias: bool = True) -> t.Optional[exp.Unnest]:
             unnest = super()._parse_unnest(with_alias=with_alias)
 
@@ -54,6 +163,7 @@ class Generator(MySQL.Generator):
         EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
         JSON_TYPE_REQUIRED_FOR_EXTRACTION = False
         PARSE_JSON_NAME: t.Optional[str] = "PARSE_JSON"
+        WITH_PROPERTIES_PREFIX = "PROPERTIES"
 
         CAST_MAPPING = {}
 
@@ -64,6 +174,14 @@ class Generator(MySQL.Generator):
             exp.DataType.Type.TIMESTAMPTZ: "DATETIME",
         }
 
+        PROPERTIES_LOCATION = {
+            **MySQL.Generator.PROPERTIES_LOCATION,
+            exp.DistributedByHashProperty: exp.Properties.Location.POST_SCHEMA,
+            exp.DistributedByRandomProperty: exp.Properties.Location.POST_SCHEMA,
+            exp.DuplicateKeyProperty: exp.Properties.Location.POST_SCHEMA,
+            exp.PrimaryKey: exp.Properties.Location.UNSUPPORTED,
+        }
+
         TRANSFORMS = {
             **MySQL.Generator.TRANSFORMS,
             exp.Array: inline_array_sql,
@@ -71,8 +189,12 @@ class Generator(MySQL.Generator):
             exp.DateDiff: lambda self, e: self.func(
                 "DATE_DIFF", unit_to_str(e), e.this, e.expression
             ),
+            exp.DistributedByHashProperty: _distributed_by_hash_sql,
+            exp.DistributedByRandomProperty: _distributed_by_random_sql,
+            exp.DuplicateKeyProperty: _duplicate_key_sql,
             exp.JSONExtractScalar: arrow_json_extract_sql,
             exp.JSONExtract: arrow_json_extract_sql,
+            exp.Property: _property_sql,
             exp.RegexpLike: rename_func("REGEXP"),
             exp.StrToUnix: lambda self, e: self.func("UNIX_TIMESTAMP", e.this, self.format_time(e)),
             exp.TimestampTrunc: lambda self, e: self.func("DATE_TRUNC", unit_to_str(e), e.this),
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 7df2cb1693..29af3366be 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2603,10 +2603,32 @@ class DistKeyProperty(Property):
     arg_types = {"this": True}
 
 
+# [starrocks distributed Doc](https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc)
+# [doris distributed Doc](https://doris.apache.org/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-TABLE?_highlight=create&_highlight=table#distribution_desc)
+class DistributedByHashProperty(Property):
+    arg_types = {"expressions": True, "buckets": False, "sorted_by": False}
+
+    @property
+    def auto_bucket(self) -> bool:
+        return self.args.get("buckets") is None
+
+
+class DistributedByRandomProperty(Property):
+    arg_types = {"expressions": True, "buckets": False, "sorted_by": False}
+
+    @property
+    def auto_bucket(self) -> bool:
+        return self.args.get("buckets") is None
+
+
 class DistStyleProperty(Property):
     arg_types = {"this": True}
 
 
+class DuplicateKeyProperty(Property):
+    arg_types = {"expressions": True}
+
+
 class EngineProperty(Property):
     arg_types = {"this": True}
 
@@ -2937,6 +2959,8 @@ class Properties(Expression):
         "COMMENT": SchemaCommentProperty,
         "DEFINER": DefinerProperty,
         "DISTKEY": DistKeyProperty,
+        "DISTRIBUTED_BY_HASH": DistributedByHashProperty,
+        "DISTRIBUTED_BY_RANDOM": DistributedByRandomProperty,
         "DISTSTYLE": DistStyleProperty,
         "ENGINE": EngineProperty,
         "EXECUTE AS": ExecuteAsProperty,
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index f921b4b3ef..0f6be685fa 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -245,6 +245,7 @@ class TokenType(AutoName):
     DICTIONARY = auto()
     DISTINCT = auto()
     DISTRIBUTE_BY = auto()
+    DUPLICATE_KEY = auto()
     DIV = auto()
     DROP = auto()
     ELSE = auto()
diff --git a/tests/dialects/test_starrocks.py b/tests/dialects/test_starrocks.py
index bfd05b9998..56580ec9dd 100644
--- a/tests/dialects/test_starrocks.py
+++ b/tests/dialects/test_starrocks.py
@@ -5,6 +5,12 @@
 class TestStarrocks(Validator):
     dialect = "starrocks"
 
+    def test_ddl(self):
+        self.validate_identity("CREATE TABLE foo (col VARCHAR(50))")
+        self.validate_identity(
+            """CREATE TABLE IF NOT EXISTS `sample_table` (`tenantid` VARCHAR(1048576) NULL COMMENT '', `create_day` DATE NOT NULL COMMENT '', `shopsite` VARCHAR(65533) NOT NULL COMMENT 'shopsite', `id` VARCHAR(65533) NOT NULL COMMENT 'shopsite id', `price` BIGDECIMAL(38, 10) NULL COMMENT 'test the bigdecimal', `seq` INT(11) NULL COMMENT 'order', `use_status` SMALLINT(6) NULL COMMENT '0,1', `created_user` BIGINT(20) NULL COMMENT 'create user', `created_time` DATETIME NULL COMMENT 'create time', PRIMARY KEY (`tenantid`, `shopsite`, `id`)) ENGINE=OLAP DUPLICATE KEY (tenantid) COMMENT='OLAP' DISTRIBUTED BY HASH (`tenantid`, `shopsite`, `id`) BUCKETS 10 ORDER BY (`tenantid`, `shopsite`, `id`) PROPERTIES ('replication_num'='1', 'in_memory'='false', 'enable_persistent_index'='false', 'replicated_storage'='false', 'storage_medium'='HDD', 'compression'='LZ4')""",
+        )
+
     def test_identity(self):
         self.validate_identity("SELECT CAST(`a`.`b` AS INT) FROM foo")
         self.validate_identity("SELECT APPROX_COUNT_DISTINCT(a) FROM x")


Code review comments:
diff_hunk: @@ -13,11 +13,58 @@
 )
 from sqlglot.dialects.mysql import MySQL
 from sqlglot.helper import seq_get
+from sqlglot.tokens import TokenType
+
+
+def _duplicate_key_sql(self, expression: exp.DuplicateKeyProperty) -> str:
+    expressions = self.expressions(expression, flat=True)
+    options = self.expressions(expression, key="options", flat=True, sep=" ")
+    options = f" {options}" if options else ""
+    return f"DUPLICATE KEY ({expressions}){options}"
+
+
+def _distributed_by_hash_sql(self, expression: exp.DistributedByHashProperty) -> str:
+    expressions = self.expressions(expression, key="expressions", flat=True)
+    sorted_by = self.expressions(expression, key="sorted_by", flat=True)
+    sorted_by = f" ORDER BY ({sorted_by})" if sorted_by else ""
+    buckets = self.sql(expression, "buckets")
+    # Since StarRocks v2.5.7, the number of buckets is AUTO by default.
+    # https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc
+    if expression.auto_bucket:
+        buckets = "AUTO"
+    # [DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num] [ORDER BY (k1[,k2 ...])]]
+    return f"DISTRIBUTED BY HASH ({expressions}) BUCKETS {buckets}{sorted_by}"
+
+
+def _distributed_by_random_sql(self, expression: exp.DistributedByRandomProperty) -> str:
+    expressions = self.expressions(expression, flat=True)
+    sorted_by = self.expressions(expression, key="sorted_by", flat=True)
+    sorted_by = f" ORDER BY ({sorted_by})" if sorted_by else ""
+    buckets = self.sql(expression, "buckets")
+    if expression.auto_bucket:
+        buckets = "AUTO"
+    # [DISTRIBUTED BY RANDOM (k1[,k2 ...]) [BUCKETS num] [ORDER BY (k1[,k2 ...])]]
+    return f"DISTRIBUTED BY RANDOM ({expressions}) BUCKETS {buckets}{sorted_by}"
+
+
+def _property_sql(self: MySQL.Generator, expression: exp.Property) -> str:
+    # [PROPERTIES (['key1'='value1', 'key2'='value2', ...])]
+    return f"{self.property_name(expression, string_key=True)}={self.sql(expression, 'value')}"
 
 
 class StarRocks(MySQL):
     STRICT_JSON_PATH_SYNTAX = False
 
+    class Tokenizer(MySQL.Tokenizer):
+        KEYWORDS = {
+            **MySQL.Tokenizer.KEYWORDS,
+            "DECIMAL64": TokenType.DECIMAL,
+            "DECIMAL128": TokenType.BIGDECIMAL,
path: sqlglot/dialects/starrocks.py
text: @user1:
We need to test these mappings. Can you share docs to justify these entries?

@author:
Oh ok, I'll add it here.
```
- Several types of decimal: https://docs.starrocks.io/docs/unloading/Flink_connector/#data-type-mapping-between-starrocks-and-flink
> Focus on the starrocks column

- Use:
https://docs.starrocks.io/docs/sql-reference/sql-functions/math-functions/round/#parameters
```

I will also add ut to test these mappings

@author:
According to the documentation, I additionally added `DECIMAL32`

@author:
In starrocks, it is executed as follows:
![image](https://github.com/user-attachments/assets/b0e66ab2-6b6f-4d81-a65e-2c8457f908f8)

diff_hunk: @@ -2603,10 +2603,32 @@ class DistKeyProperty(Property):
     arg_types = {"this": True}
 
 
+# [starrocks distributed Doc](https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc)
+# [doris distributed Doc](https://doris.apache.org/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-TABLE?_highlight=create&_highlight=table#distribution_desc)
+class DistributedByHashProperty(Property):
+    arg_types = {"expressions": True, "buckets": False, "sorted_by": False}
+
+    @property
+    def auto_bucket(self) -> bool:
+        return self.args.get("buckets") is None
path: sqlglot/expressions.py
text: @user1:
No need for this property, same for the expression below.

@author:
Got it.

diff_hunk: @@ -31,6 +78,68 @@ class Parser(MySQL.Parser):
             "REGEXP": exp.RegexpLike.from_arg_list,
         }
 
+        PROPERTY_PARSERS = {
+            **MySQL.Parser.PROPERTY_PARSERS,
+            "DISTRIBUTED BY HASH": lambda self: self._parse_distributed_by("HASH"),
+            "DISTRIBUTED BY RANDOM": lambda self: self._parse_distributed_by("RANDOM"),
+            "PROPERTIES": lambda self: self._parse_wrapped_csv(self._parse_property),
+            "DUPLICATE KEY": lambda self: self._parse_duplicate(),
+            "PARTITION BY": lambda self: self._parse_partitioned_by(),
+        }
+
+        def _parse_create(self) -> exp.Create | exp.Command:
+            create = super()._parse_create()
+
+            # Starrocks's primary is defined outside the schema, so we need to move it into the schema
+            # https://docs.starrocks.io/docs/table_design/table_types/primary_key_table/#usage
+            if (
+                isinstance(create, exp.Create)
+                and create.this
+                and isinstance(create.this, exp.Schema)
+            ):
+                props = create.args.get("properties")
+                if props:
+                    primary_key = next(
+                        (expr for expr in props.expressions if isinstance(expr, exp.PrimaryKey)),
+                        None,
+                    )
+                    if primary_key:
+                        create.this.expressions.append(primary_key)
+
+                        # Remove the PrimaryKey from properties
+                        props.expressions.remove(primary_key)
+
+            return create
+
+        def _parse_distributed_by(self, type: str) -> exp.Expression:
+            expressions = self._parse_wrapped_csv(self._parse_id_var)
+
+            # If the BUCKETS keyword not present, the number of buckets is AUTO
+            # [ BUCKETS AUTO | BUCKETS <number> ]
+            buckets = None
+            if self._match_text_seq("BUCKETS", "AUTO"):
+                pass
path: sqlglot/dialects/starrocks.py
text: @user1:
```suggestion
            if self._match_text_seq("BUCKETS", "AUTO"):
                buckets: t.Optional[exp.Expression] = None
```

diff_hunk: @@ -31,6 +78,68 @@ class Parser(MySQL.Parser):
             "REGEXP": exp.RegexpLike.from_arg_list,
         }
 
+        PROPERTY_PARSERS = {
+            **MySQL.Parser.PROPERTY_PARSERS,
+            "DISTRIBUTED BY HASH": lambda self: self._parse_distributed_by("HASH"),
+            "DISTRIBUTED BY RANDOM": lambda self: self._parse_distributed_by("RANDOM"),
+            "PROPERTIES": lambda self: self._parse_wrapped_csv(self._parse_property),
+            "DUPLICATE KEY": lambda self: self._parse_duplicate(),
+            "PARTITION BY": lambda self: self._parse_partitioned_by(),
+        }
+
+        def _parse_create(self) -> exp.Create | exp.Command:
+            create = super()._parse_create()
+
+            # Starrocks's primary is defined outside the schema, so we need to move it into the schema
+            # https://docs.starrocks.io/docs/table_design/table_types/primary_key_table/#usage
+            if (
+                isinstance(create, exp.Create)
+                and create.this
+                and isinstance(create.this, exp.Schema)
+            ):
+                props = create.args.get("properties")
+                if props:
+                    primary_key = next(
+                        (expr for expr in props.expressions if isinstance(expr, exp.PrimaryKey)),
+                        None,
+                    )
+                    if primary_key:
+                        create.this.expressions.append(primary_key)
+
+                        # Remove the PrimaryKey from properties
+                        props.expressions.remove(primary_key)
+
+            return create
+
+        def _parse_distributed_by(self, type: str) -> exp.Expression:
+            expressions = self._parse_wrapped_csv(self._parse_id_var)
+
+            # If the BUCKETS keyword not present, the number of buckets is AUTO
+            # [ BUCKETS AUTO | BUCKETS <number> ]
+            buckets = None
+            if self._match_text_seq("BUCKETS", "AUTO"):
+                pass
+            elif self._match_text_seq("BUCKETS"):
+                buckets = self._parse_number()
+
+            if self._match_text_seq("ORDER BY"):
+                order_by = self._parse_wrapped_csv(self._parse_ordered)
+            else:
+                order_by = None
path: sqlglot/dialects/starrocks.py
text: @user1:
We can do this with a single `_parse_order`, see other comment.

diff_hunk: @@ -13,11 +13,58 @@
 )
 from sqlglot.dialects.mysql import MySQL
 from sqlglot.helper import seq_get
+from sqlglot.tokens import TokenType
+
+
+def _duplicate_key_sql(self, expression: exp.DuplicateKeyProperty) -> str:
+    expressions = self.expressions(expression, flat=True)
+    options = self.expressions(expression, key="options", flat=True, sep=" ")
+    options = f" {options}" if options else ""
+    return f"DUPLICATE KEY ({expressions}){options}"
+
+
+def _distributed_by_hash_sql(self, expression: exp.DistributedByHashProperty) -> str:
+    expressions = self.expressions(expression, key="expressions", flat=True)
+    sorted_by = self.expressions(expression, key="sorted_by", flat=True)
+    sorted_by = f" ORDER BY ({sorted_by})" if sorted_by else ""
+    buckets = self.sql(expression, "buckets")
+    # Since StarRocks v2.5.7, the number of buckets is AUTO by default.
+    # https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc
+    if expression.auto_bucket:
+        buckets = "AUTO"
+    # [DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num] [ORDER BY (k1[,k2 ...])]]
+    return f"DISTRIBUTED BY HASH ({expressions}) BUCKETS {buckets}{sorted_by}"
+
+
+def _distributed_by_random_sql(self, expression: exp.DistributedByRandomProperty) -> str:
+    expressions = self.expressions(expression, flat=True)
+    sorted_by = self.expressions(expression, key="sorted_by", flat=True)
+    sorted_by = f" ORDER BY ({sorted_by})" if sorted_by else ""
+    buckets = self.sql(expression, "buckets")
+    if expression.auto_bucket:
+        buckets = "AUTO"
+    # [DISTRIBUTED BY RANDOM (k1[,k2 ...]) [BUCKETS num] [ORDER BY (k1[,k2 ...])]]
+    return f"DISTRIBUTED BY RANDOM ({expressions}) BUCKETS {buckets}{sorted_by}"
+
+
+def _property_sql(self: MySQL.Generator, expression: exp.Property) -> str:
+    # [PROPERTIES (['key1'='value1', 'key2'='value2', ...])]
+    return f"{self.property_name(expression, string_key=True)}={self.sql(expression, 'value')}"
path: sqlglot/dialects/starrocks.py
text: @user1:
What is this achieving? I'm not sure if it's correct, `Property` is a pretty generic class to override.

@author:
Yes. This case is used to set the parameter string_key to True.
I refer to the code from `sqlglot.dialects.hive._property_sql`.

@user1:
We shouldn't be copy-pasting code, please move the logic in `dialect.py` so it can be imported in both Hive and Starrocks.

@author:
I see, I'll change that.

diff_hunk: @@ -2603,10 +2603,32 @@ class DistKeyProperty(Property):
     arg_types = {"this": True}
 
 
+# [starrocks distributed Doc](https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc)
+# [doris distributed Doc](https://doris.apache.org/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-TABLE?_highlight=create&_highlight=table#distribution_desc)
+class DistributedByHashProperty(Property):
+    arg_types = {"expressions": True, "buckets": False, "sorted_by": False}
+
+    @property
+    def auto_bucket(self) -> bool:
+        return self.args.get("buckets") is None
+
+
+class DistributedByRandomProperty(Property):
+    arg_types = {"expressions": True, "buckets": False, "sorted_by": False}
path: sqlglot/expressions.py
text: @user1:
Can't we have an "order" arg instead of `"sorted_by"` and then use `_parse_order` to capture the syntax? With this approach we're duplicating the generation for `ORDER BY`.

diff_hunk: @@ -13,11 +13,58 @@
 )
 from sqlglot.dialects.mysql import MySQL
 from sqlglot.helper import seq_get
+from sqlglot.tokens import TokenType
+
+
+def _duplicate_key_sql(self, expression: exp.DuplicateKeyProperty) -> str:
+    expressions = self.expressions(expression, flat=True)
+    options = self.expressions(expression, key="options", flat=True, sep=" ")
+    options = f" {options}" if options else ""
+    return f"DUPLICATE KEY ({expressions}){options}"
+
+
+def _distributed_by_hash_sql(self, expression: exp.DistributedByHashProperty) -> str:
+    expressions = self.expressions(expression, key="expressions", flat=True)
+    sorted_by = self.expressions(expression, key="sorted_by", flat=True)
+    sorted_by = f" ORDER BY ({sorted_by})" if sorted_by else ""
+    buckets = self.sql(expression, "buckets")
+    # Since StarRocks v2.5.7, the number of buckets is AUTO by default.
+    # https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc
+    if expression.auto_bucket:
+        buckets = "AUTO"
+    # [DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num] [ORDER BY (k1[,k2 ...])]]
+    return f"DISTRIBUTED BY HASH ({expressions}) BUCKETS {buckets}{sorted_by}"
+
+
+def _distributed_by_random_sql(self, expression: exp.DistributedByRandomProperty) -> str:
+    expressions = self.expressions(expression, flat=True)
+    sorted_by = self.expressions(expression, key="sorted_by", flat=True)
+    sorted_by = f" ORDER BY ({sorted_by})" if sorted_by else ""
+    buckets = self.sql(expression, "buckets")
+    if expression.auto_bucket:
+        buckets = "AUTO"
+    # [DISTRIBUTED BY RANDOM (k1[,k2 ...]) [BUCKETS num] [ORDER BY (k1[,k2 ...])]]
+    return f"DISTRIBUTED BY RANDOM ({expressions}) BUCKETS {buckets}{sorted_by}"
+
+
+def _property_sql(self: MySQL.Generator, expression: exp.Property) -> str:
+    # [PROPERTIES (['key1'='value1', 'key2'='value2', ...])]
+    return f"{self.property_name(expression, string_key=True)}={self.sql(expression, 'value')}"
 
 
 class StarRocks(MySQL):
     STRICT_JSON_PATH_SYNTAX = False
 
+    class Tokenizer(MySQL.Tokenizer):
+        KEYWORDS = {
+            **MySQL.Tokenizer.KEYWORDS,
+            "DECIMAL64": TokenType.DECIMAL,
+            "DECIMAL128": TokenType.BIGDECIMAL,
+            "DISTRIBUTED BY HASH": TokenType.DISTRIBUTE_BY,
+            "DISTRIBUTED BY RANDOM": TokenType.DISTRIBUTE_BY,
+            "DUPLICATE KEY": TokenType.DUPLICATE_KEY,
path: sqlglot/dialects/starrocks.py
text: @user1:
Let's not do this multi-word token mapping here. I'd just remove the new `DUPLICATE_KEY` token and only match on the text. So you'll have `"DISTRIBUTED": lambda self: self._parse_distributed_property()` and the same for `"DUPLICATE"`. Then, within those parsing methods you can do a `_match_text_seq` to choose which property to parse.

@author:
Thank you, good suggestion, I will try to fix it

diff_hunk: @@ -31,6 +78,68 @@ class Parser(MySQL.Parser):
             "REGEXP": exp.RegexpLike.from_arg_list,
         }
 
+        PROPERTY_PARSERS = {
+            **MySQL.Parser.PROPERTY_PARSERS,
+            "DISTRIBUTED BY HASH": lambda self: self._parse_distributed_by("HASH"),
+            "DISTRIBUTED BY RANDOM": lambda self: self._parse_distributed_by("RANDOM"),
+            "PROPERTIES": lambda self: self._parse_wrapped_csv(self._parse_property),
+            "DUPLICATE KEY": lambda self: self._parse_duplicate(),
+            "PARTITION BY": lambda self: self._parse_partitioned_by(),
path: sqlglot/dialects/starrocks.py
text: @user1:
Why are `PROPERTIES` and `PARTITION BY` here?

@author:
`PARTITION BY` is unnecessary here, I deleted it.

@author:
`TBLPROPERTIES` is defined in sqlglot/parser.py:865, which allows hive to parse it well:
```
"TBLPROPERTIES": lambda self: self._parse_wrapped_properties(),
```
We are missing PROPERTIES, so I added `PROPERTIES` here

@author:
create table `properties` docs links:
https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#properties

In addition, I did not put Properties into parse because it may affect other dialects. The following test will fail:
```
self.validate_identity(
"ALTER TABLE people SET PROPERTIES x = 'y'", check_command_warning=True
)
```