Repository: tobymao/sqlglot

Issue:
Support for Spark's `str_to_map` and Presto's `split_to_map`
**Is your feature request related to a problem? Please describe.**
[Spark](https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map) and [Presto](https://trino.io/docs/current/functions/string.html?highlight=split_to_map#split_to_map) support splitting text into into key/value pairs using delimiters. This is useful when parsing text fields.

sqlglot currently parses either one into an anonymous, i.e., `sqlglot.parse_one("select str_to_map(c1, ';', '=')")` gives
```python
(SELECT expressions:
  (ANONYMOUS this: str_to_map, expressions:
    (COLUMN this:
      (IDENTIFIER this: c1, quoted: False)),
    (LITERAL this: ;, is_string: True),
    (LITERAL this: =, is_string: True)))
```

This prevents us from transpiling `str_to_map` to `split_to_map` (and vice versa).

Discussion:

Patch:
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index b6b7de72..69c92225 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -258,6 +258,11 @@ class Hive(Dialect):
             ),
             "SIZE": exp.ArraySize.from_arg_list,
             "SPLIT": exp.RegexpSplit.from_arg_list,
+            "STR_TO_MAP": lambda args: exp.StrToMap(
+                this=seq_get(args, 0),
+                pair_delim=seq_get(args, 1) or exp.Literal.string(","),
+                key_value_delim=seq_get(args, 2) or exp.Literal.string(":"),
+            ),
             "TO_DATE": format_time_lambda(exp.TsOrDsToDate, "hive"),
             "TO_JSON": exp.JSONFormat.from_arg_list,
             "UNBASE64": exp.FromBase64.from_arg_list,
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index ac8f3a0a..aa84462e 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -222,6 +222,7 @@ class Presto(Dialect):
             ),
             "ROW": exp.Struct.from_arg_list,
             "SEQUENCE": exp.GenerateSeries.from_arg_list,
+            "SPLIT_TO_MAP": exp.StrToMap.from_arg_list,
             "STRPOS": lambda args: exp.StrPosition(
                 this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2)
             ),
@@ -321,6 +322,7 @@ class Presto(Dialect):
             exp.SortArray: _no_sort_array,
             exp.StrPosition: rename_func("STRPOS"),
             exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
+            exp.StrToMap: rename_func("SPLIT_TO_MAP"),
             exp.StrToTime: _str_to_time_sql,
             exp.StrToUnix: lambda self, e: f"TO_UNIXTIME(DATE_PARSE({self.sql(e, 'this')}, {self.format_time(e)}))",
             exp.Struct: rename_func("ROW"),
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index c231eb3e..b73a5e8a 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4656,6 +4656,17 @@ class StrToUnix(Func):
     arg_types = {"this": False, "format": False}
 
 
+# https://prestodb.io/docs/current/functions/string.html
+# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
+class StrToMap(Func):
+    arg_types = {
+        "this": True,
+        "pair_delim": False,
+        "key_value_delim": False,
+        "duplicate_resolution_callback": False,
+    }
+
+
 class NumberToStr(Func):
     arg_types = {"this": True, "format": True, "culture": False}
 


Test patch:
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 92b10a5b..f116b6b5 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -487,6 +487,9 @@ class TestPresto(Validator):
         self.validate_identity("START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE")
         self.validate_identity("START TRANSACTION ISOLATION LEVEL REPEATABLE READ")
         self.validate_identity("APPROX_PERCENTILE(a, b, c, d)")
+        self.validate_identity(
+            "SELECT SPLIT_TO_MAP('a:1;b:2;a:3', ';', ':', (k, v1, v2) -> CONCAT(v1, v2))"
+        )
 
         self.validate_all(
             "SELECT ROW(1, 2)",
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 63487606..a984025c 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -239,7 +239,22 @@ TBLPROPERTIES (
         self.validate_identity("TRIM(LEADING 'SL' FROM 'SSparkSQLS')")
         self.validate_identity("TRIM(TRAILING 'SL' FROM 'SSparkSQLS')")
         self.validate_identity("SPLIT(str, pattern, lim)")
+        self.validate_identity(
+            "SELECT STR_TO_MAP('a:1,b:2,c:3')",
+            "SELECT STR_TO_MAP('a:1,b:2,c:3', ',', ':')",
+        )
 
+        self.validate_all(
+            "SELECT STR_TO_MAP('a:1,b:2,c:3', ',', ':')",
+            read={
+                "presto": "SELECT SPLIT_TO_MAP('a:1,b:2,c:3', ',', ':')",
+                "spark": "SELECT STR_TO_MAP('a:1,b:2,c:3', ',', ':')",
+            },
+            write={
+                "presto": "SELECT SPLIT_TO_MAP('a:1,b:2,c:3', ',', ':')",
+                "spark": "SELECT STR_TO_MAP('a:1,b:2,c:3', ',', ':')",
+            },
+        )
         self.validate_all(
             "SELECT DATEDIFF(month, CAST('1996-10-30' AS TIMESTAMP), CAST('1997-02-28 10:30:00' AS TIMESTAMP))",
             read={