Repository: tobymao/sqlglot

Issue:
clickhouse: heredoc is not parsable
```python
import sqlglot

sql = """
SELECT $$this is a heredoc string$$
"""
print(sqlglot.parse_one(sql, read="clickhouse"))
```
```
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 2, Col: 18.
  
SELECT $$this is a heredoc string$$
```

docs: https://clickhouse.com/docs/en/sql-reference/syntax#heredoc

_Note: `$$anything$$` is a form of heredoc too, i.e. `SELECT $$aaa$$this is a heredoc string$$aaa$$`, and it could be multiline, etc._

Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 55095c88..03b116e4 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -43,6 +43,7 @@ class ClickHouse(Dialect):
         STRING_ESCAPES = ["'", "\\"]
         BIT_STRINGS = [("0b", "")]
         HEX_STRINGS = [("0x", ""), ("0X", "")]
+        HEREDOC_STRINGS = ["$"]
 
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
@@ -75,6 +76,11 @@ class ClickHouse(Dialect):
             "UINT8": TokenType.UTINYINT,
         }
 
+        SINGLE_TOKENS = {
+            **tokens.Tokenizer.SINGLE_TOKENS,
+            "$": TokenType.HEREDOC_STRING,
+        }
+
     class Parser(parser.Parser):
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 342fd95a..c22a30b3 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -248,11 +248,10 @@ class Postgres(Dialect):
     }
 
     class Tokenizer(tokens.Tokenizer):
-        QUOTES = ["'", "$$"]
-
         BIT_STRINGS = [("b'", "'"), ("B'", "'")]
         HEX_STRINGS = [("x'", "'"), ("X'", "'")]
         BYTE_STRINGS = [("e'", "'"), ("E'", "'")]
+        HEREDOC_STRINGS = ["$"]
 
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
@@ -296,7 +295,7 @@ class Postgres(Dialect):
 
         SINGLE_TOKENS = {
             **tokens.Tokenizer.SINGLE_TOKENS,
-            "$": TokenType.PARAMETER,
+            "$": TokenType.HEREDOC_STRING,
         }
 
         VAR_SINGLE_TOKENS = {"$"}
diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py
index 2eaa2ae9..63924d43 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -96,3 +96,17 @@ class Spark(Spark2):
                 return self.func("DATEDIFF", unit, start, end)
 
             return self.func("DATEDIFF", end, start)
+
+        def create_sql(self, expression: exp.Create) -> str:
+            kind = self.sql(expression, "kind").upper()
+            properties = expression.args.get("properties")
+            temporary = any(
+                isinstance(prop, exp.TemporaryProperty)
+                for prop in (properties.expressions if properties else [])
+            )
+            if kind == "TABLE" and temporary:
+                provider = exp.FileFormatProperty(this=exp.Literal.string("parquet"))
+                expression = expression.copy()
+                expression.args["properties"].append("expressions", provider)
+
+            return super().create_sql(expression)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 84f7e46d..5f8a8448 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -590,6 +590,9 @@ class Parser(metaclass=_Parser):
             exp.National, this=token.text
         ),
         TokenType.RAW_STRING: lambda self, token: self.expression(exp.RawString, this=token.text),
+        TokenType.HEREDOC_STRING: lambda self, token: self.expression(
+            exp.RawString, this=token.text
+        ),
         TokenType.SESSION_PARAMETER: lambda self, _: self._parse_session_parameter(),
     }
 
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 4d5f1983..6b86a6b3 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -77,6 +77,7 @@ class TokenType(AutoName):
     BYTE_STRING = auto()
     NATIONAL_STRING = auto()
     RAW_STRING = auto()
+    HEREDOC_STRING = auto()
 
     # types
     BIT = auto()
@@ -418,6 +419,7 @@ class _Tokenizer(type):
             **_quotes_to_format(TokenType.BYTE_STRING, klass.BYTE_STRINGS),
             **_quotes_to_format(TokenType.HEX_STRING, klass.HEX_STRINGS),
             **_quotes_to_format(TokenType.RAW_STRING, klass.RAW_STRINGS),
+            **_quotes_to_format(TokenType.HEREDOC_STRING, klass.HEREDOC_STRINGS),
         }
 
         klass._STRING_ESCAPES = set(klass.STRING_ESCAPES)
@@ -484,6 +486,7 @@ class Tokenizer(metaclass=_Tokenizer):
     BYTE_STRINGS: t.List[str | t.Tuple[str, str]] = []
     HEX_STRINGS: t.List[str | t.Tuple[str, str]] = []
     RAW_STRINGS: t.List[str | t.Tuple[str, str]] = []
+    HEREDOC_STRINGS: t.List[str | t.Tuple[str, str]] = []
     IDENTIFIERS: t.List[str | t.Tuple[str, str]] = ['"']
     IDENTIFIER_ESCAPES = ['"']
     QUOTES: t.List[t.Tuple[str, str] | str] = ["'"]
@@ -997,9 +1000,11 @@ class Tokenizer(metaclass=_Tokenizer):
                 word = word.upper()
                 self._add(self.KEYWORDS[word], text=word)
                 return
+
         if self._char in self.SINGLE_TOKENS:
             self._add(self.SINGLE_TOKENS[self._char], text=self._char)
             return
+
         self._scan_var()
 
     def _scan_comment(self, comment_start: str) -> bool:
@@ -1126,6 +1131,10 @@ class Tokenizer(metaclass=_Tokenizer):
                 base = 16
             elif token_type == TokenType.BIT_STRING:
                 base = 2
+            elif token_type == TokenType.HEREDOC_STRING:
+                self._advance()
+                tag = "" if self._char == end else self._extract_string(end)
+                end = f"{start}{tag}{end}"
         else:
             return False
 


Test patch:
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 3e0ffd5f..55c1f3b0 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -5,6 +5,7 @@ from sqlglot import (
     Dialects,
     ErrorLevel,
     ParseError,
+    TokenError,
     UnsupportedError,
     parse_one,
 )
@@ -308,6 +309,44 @@ class TestDialect(Validator):
             read={"postgres": "INET '127.0.0.1/32'"},
         )
 
+    def test_heredoc_strings(self):
+        for dialect in ("clickhouse", "postgres", "redshift"):
+            # Invalid matching tag
+            with self.assertRaises(TokenError):
+                parse_one("SELECT $tag1$invalid heredoc string$tag2$", dialect=dialect)
+
+            # Unmatched tag
+            with self.assertRaises(TokenError):
+                parse_one("SELECT $tag1$invalid heredoc string", dialect=dialect)
+
+            # Without tag
+            self.validate_all(
+                "SELECT 'this is a heredoc string'",
+                read={
+                    dialect: "SELECT $$this is a heredoc string$$",
+                },
+            )
+            self.validate_all(
+                "SELECT ''",
+                read={
+                    dialect: "SELECT $$$$",
+                },
+            )
+
+            # With tag
+            self.validate_all(
+                "SELECT 'this is also a heredoc string'",
+                read={
+                    dialect: "SELECT $foo$this is also a heredoc string$foo$",
+                },
+            )
+            self.validate_all(
+                "SELECT ''",
+                read={
+                    dialect: "SELECT $foo$$foo$",
+                },
+            )
+
     def test_decode(self):
         self.validate_identity("DECODE(bin, charset)")
 
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 74a3f1c9..0ddc1061 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -207,7 +207,6 @@ class TestPostgres(Validator):
         self.validate_identity("SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]")
         self.validate_identity("SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]")
         self.validate_identity("SELECT ARRAY[1, 2, 3] && ARRAY[1, 2]")
-        self.validate_identity("$x")
         self.validate_identity("x$")
         self.validate_identity("SELECT ARRAY[1, 2, 3]")
         self.validate_identity("SELECT ARRAY(SELECT 1)")
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index c75654c8..5f337b05 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -226,7 +226,6 @@ class TestRedshift(Validator):
         self.validate_identity("SELECT * FROM #x")
         self.validate_identity("SELECT INTERVAL '5 day'")
         self.validate_identity("foo$")
-        self.validate_identity("$foo")
         self.validate_identity("CAST('bla' AS SUPER)")
         self.validate_identity("CREATE TABLE real1 (realcol REAL)")
         self.validate_identity("CAST('foo' AS HLLSKETCH)")
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 2e43ba52..0148e553 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -8,6 +8,7 @@ class TestSpark(Validator):
     dialect = "spark"
 
     def test_ddl(self):
+        self.validate_identity("CREATE TEMPORARY VIEW test AS SELECT 1")
         self.validate_identity("CREATE TABLE foo (col VARCHAR(50))")
         self.validate_identity("CREATE TABLE foo (col STRUCT<struct_col_a: VARCHAR((50))>)")
         self.validate_identity("CREATE TABLE foo (col STRING) CLUSTERED BY (col) INTO 10 BUCKETS")
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 10b1087d..960f65ef 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -564,7 +564,7 @@ class TestTSQL(Validator):
         self.validate_all(
             "CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIME(4), d FLOAT(24))",
             write={
-                "spark": "CREATE TEMPORARY TABLE mytemp (a INT, b CHAR(2), c TIMESTAMP, d FLOAT)",
+                "spark": "CREATE TEMPORARY TABLE mytemp (a INT, b CHAR(2), c TIMESTAMP, d FLOAT) USING PARQUET",
                 "tsql": "CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIME(4), d FLOAT(24))",
             },
         )