Edit on GitHub

Dialects

While there is a SQL standard, most SQL engines support a variation of that standard. This makes it difficult to write portable SQL code. SQLGlot bridges all the different variations, called "dialects", with an extensible SQL transpilation framework.

The base sqlglot.dialects.dialect.Dialect class implements a generic dialect that aims to be as universal as possible.

Each SQL variation has its own Dialect subclass, extending the corresponding Tokenizer, Parser and Generator classes as needed.

Implementing a custom Dialect

Consider the following example:

from sqlglot import exp
from sqlglot.dialects.dialect import Dialect
from sqlglot.generator import Generator
from sqlglot.tokens import Tokenizer, TokenType


class Custom(Dialect):
    class Tokenizer(Tokenizer):
        QUOTES = ["'", '"']
        IDENTIFIERS = ["`"]

        KEYWORDS = {
            **Tokenizer.KEYWORDS,
            "INT64": TokenType.BIGINT,
            "FLOAT64": TokenType.DOUBLE,
        }

    class Generator(Generator):
        TRANSFORMS = {exp.Array: lambda self, e: f"[{self.expressions(e)}]"}

        TYPE_MAPPING = {
            exp.DataType.Type.TINYINT: "INT64",
            exp.DataType.Type.SMALLINT: "INT64",
            exp.DataType.Type.INT: "INT64",
            exp.DataType.Type.BIGINT: "INT64",
            exp.DataType.Type.DECIMAL: "NUMERIC",
            exp.DataType.Type.FLOAT: "FLOAT64",
            exp.DataType.Type.DOUBLE: "FLOAT64",
            exp.DataType.Type.BOOLEAN: "BOOL",
            exp.DataType.Type.TEXT: "STRING",
        }

This is a typical example of adding a new dialect implementation in SQLGlot: we specify its identifier and string delimiters, as well as what tokens it uses for its types and how they're associated with SQLGlot types. Since the Expression classes are common for each dialect supported in SQLGlot, we may also need to override the generation logic for some expressions; this is usually done by adding new entries to the TRANSFORMS mapping.

 1"""
 2## Dialects
 3
 4While there is a SQL standard, most SQL engines support a variation of that standard. This makes it difficult
 5to write portable SQL code. SQLGlot bridges all the different variations, called "dialects", with an extensible
 6SQL transpilation framework. 
 7
 8The base `sqlglot.dialects.dialect.Dialect` class implements a generic dialect that aims to be as universal as possible.
 9
10Each SQL variation has its own `Dialect` subclass, extending the corresponding `Tokenizer`, `Parser` and `Generator`
11classes as needed.
12
13### Implementing a custom Dialect
14
15Consider the following example:
16
17```python
18from sqlglot import exp
19from sqlglot.dialects.dialect import Dialect
20from sqlglot.generator import Generator
21from sqlglot.tokens import Tokenizer, TokenType
22
23
24class Custom(Dialect):
25    class Tokenizer(Tokenizer):
26        QUOTES = ["'", '"']
27        IDENTIFIERS = ["`"]
28
29        KEYWORDS = {
30            **Tokenizer.KEYWORDS,
31            "INT64": TokenType.BIGINT,
32            "FLOAT64": TokenType.DOUBLE,
33        }
34
35    class Generator(Generator):
36        TRANSFORMS = {exp.Array: lambda self, e: f"[{self.expressions(e)}]"}
37
38        TYPE_MAPPING = {
39            exp.DataType.Type.TINYINT: "INT64",
40            exp.DataType.Type.SMALLINT: "INT64",
41            exp.DataType.Type.INT: "INT64",
42            exp.DataType.Type.BIGINT: "INT64",
43            exp.DataType.Type.DECIMAL: "NUMERIC",
44            exp.DataType.Type.FLOAT: "FLOAT64",
45            exp.DataType.Type.DOUBLE: "FLOAT64",
46            exp.DataType.Type.BOOLEAN: "BOOL",
47            exp.DataType.Type.TEXT: "STRING",
48        }
49```
50
51This is a typical example of adding a new dialect implementation in SQLGlot: we specify its identifier and string
52delimiters, as well as what tokens it uses for its types and how they're associated with SQLGlot types. Since
53the `Expression` classes are common for each dialect supported in SQLGlot, we may also need to override the generation
54logic for some expressions; this is usually done by adding new entries to the `TRANSFORMS` mapping.
55
56----
57"""
58
59from sqlglot.dialects.bigquery import BigQuery
60from sqlglot.dialects.clickhouse import ClickHouse
61from sqlglot.dialects.databricks import Databricks
62from sqlglot.dialects.dialect import Dialect, Dialects
63from sqlglot.dialects.drill import Drill
64from sqlglot.dialects.duckdb import DuckDB
65from sqlglot.dialects.hive import Hive
66from sqlglot.dialects.mysql import MySQL
67from sqlglot.dialects.oracle import Oracle
68from sqlglot.dialects.postgres import Postgres
69from sqlglot.dialects.presto import Presto
70from sqlglot.dialects.redshift import Redshift
71from sqlglot.dialects.snowflake import Snowflake
72from sqlglot.dialects.spark import Spark
73from sqlglot.dialects.sqlite import SQLite
74from sqlglot.dialects.starrocks import StarRocks
75from sqlglot.dialects.tableau import Tableau
76from sqlglot.dialects.teradata import Teradata
77from sqlglot.dialects.trino import Trino
78from sqlglot.dialects.tsql import TSQL