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.doris import Doris 64from sqlglot.dialects.drill import Drill 65from sqlglot.dialects.duckdb import DuckDB 66from sqlglot.dialects.hive import Hive 67from sqlglot.dialects.mysql import MySQL 68from sqlglot.dialects.oracle import Oracle 69from sqlglot.dialects.postgres import Postgres 70from sqlglot.dialects.presto import Presto 71from sqlglot.dialects.redshift import Redshift 72from sqlglot.dialects.snowflake import Snowflake 73from sqlglot.dialects.spark import Spark 74from sqlglot.dialects.spark2 import Spark2 75from sqlglot.dialects.sqlite import SQLite 76from sqlglot.dialects.starrocks import StarRocks 77from sqlglot.dialects.tableau import Tableau 78from sqlglot.dialects.teradata import Teradata 79from sqlglot.dialects.trino import Trino 80from sqlglot.dialects.tsql import TSQL