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