内容
SQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine. It can be used to format SQL or translate between [over 30 dialects](https://github.com/tobymao/sqlglot/blob/main/sqlglot/dialects/__init__.py) like [DuckDB](https://duckdb.org/), [Presto](https://prestodb.io/) / [Trino](https://trino.io/), [Spark](https://spark.apache.org/) / [Databricks](https://www.databricks.com/), [Snowflake](https://www.snowflake.com/en/), and [BigQuery](https://cloud.google.com/bigquery/). It aims to read a wide variety of SQL inputs and output syntactically and semantically correct SQL in the targeted dialects.
It is a very comprehensive generic SQL parser with a robust [test suite](https://github.com/tobymao/sqlglot/blob/main/tests/). It is also quite [performant](#benchmarks), while being written purely in Python.
You can easily [customize](#custom-dialects) the parser, [analyze](#metadata) queries, traverse expression trees, and programmatically [build](#build-and-modify-sql) SQL.
SQLGlot can detect a variety of [syntax errors](#parser-errors), such as unbalanced parentheses, incorrect usage of reserved keywords, and so on. These errors are highlighted and dialect incompatibilities can warn or raise depending on configurations.
Learn more about SQLGlot in the API [documentation](https://sqlglot.com/) and the expression tree [primer](https://github.com/tobymao/sqlglot/blob/main/posts/ast_primer.md).
Contributions are very welcome in SQLGlot; read the [contribution guide](https://github.com/tobymao/sqlglot/blob/main/CONTRIBUTING.md) and the [onboarding document](https://github.com/tobymao/sqlglot/blob/main/posts/onboarding.md) to get started!
## Table of Contents
* [Install](#install)
* [Versioning](#versioning)
* [Get in Touch](#get-in-touch)
* [FAQ](#faq)
* [Examples](#examples)
* [Formatting and Transpiling](#formatting-and-transpiling)
* [Metadata](#metadata)
* [Parser Errors](#parser-errors)
* [Unsupported Errors](#unsupported-errors)
* [Build and Modify SQL](#build-and-modify-sql)
* [SQL Optimizer](#sql-optimizer)
* [AST Introspection](#ast-introspection)
* [AST Diff](#ast-diff)
* [Custom Dialects](#custom-dialects)
* [SQL Execution](#sql-execution)
* [Used By](#used-by)
* [Documentation](#documentation)
* [Run Tests and Lint](#run-tests-and-lint)
* [Benchmarks](#benchmarks)
* [Optional Dependencies](#optional-dependencies)
* [Supported Dialects](#supported-dialects)
## Install
From PyPI:
```bash
# Pure python version
pip3 install sqlglot
# C extensions compiled with mypyc
# prebuilt wheel if available for your platform, otherwise builds from source
pip3 install "sqlglot[c]"
```
Or with a local checkout:
```
# Optionally prefix with UV=1 to use uv for the installation
make install
```
Requirements for development (optional):
```
# Optionally prefix with UV=1 to use uv for the installation
make install-dev
```
## Versioning
Given a version number `MAJOR`.`MINOR`.`PATCH`:
- `PATCH` is incremented for backwards-compatible changes.
- `MINOR` is incremented for backwards-incompatible changes.
- `MAJOR` is incremented for significant backwards-incompatible changes.
## Get in Touch
We'd love to hear from you. Join our community [Slack channel](https://tobikodata.com/slack)!
## FAQ
**I tried to parse SQL that should be valid, but it failed. Why?**
You probably didn't specify the source dialect. Without it, `parse_one` assumes the "SQLGlot dialect", which is designed to be a superset of all supported dialects. Always pass the dialect when you know it, e.g. `parse_one(sql, dialect="spark")`. If parsing still fails, please file an issue.
**The generated SQL is not in the correct dialect!**
The target dialect must also be specified explicitly. For example, to transpile a query from Spark SQL to DuckDB, do `parse_one(sql, dialect="spark").sql(dialect="duckdb")`, or `transpile(sql, read="spark", write="duckdb")`.
**Why does SQLGlot parse my invalid SQL without complaining?**
The parser is intentionally lenient, so it can accept queries that a real engine would reject. SQLGlot is a transpiler, not a validator. A query that parses successfully may still fail at execution time.
**Why doesn't the output preserve my formatting, casing or quoting?**
SQLGlot parses queries into an AST and generates SQL back from it, so it preserves the meaning of a query rather than its exact text. Cosmetic details can change in the process. Use `pretty=True` for formatted output and `identify=True` to quote all identifiers. Comments are preserved on a best-effort basis.
**How can I make parsing faster?**
Install the version compiled with mypyc: `pip3 install "sqlglot[c]"`. It is roughly 3-5x faster than the pure Python version (see [Benchmarks](#benchmarks)).
**My dialect isn't supported. What are my options?**
You can subclass an existing dialect ([Custom Dialects](#custom-dialects)), or ship one as a separate package ([Creating a Dialect Plugin](#creating-a-dialect-plugin)). Keep in mind that subclassing may not work properly with `sqlglot[c]` installed, so custom dialects may require the pure Python version.
## Examples
### Formatting and Transpiling
Easily translate from one dialect to another. For example, date/time functions vary between dialects and can be hard to deal with:
```python
import sqlglot
sqlglot.transpile("SELECT EPOCH_MS(1618088028295)", read="duckdb", write="hive")[0]
```
```sql
'SELECT FROM_UNIXTIME(1618088028295 / POW(10, 3))'
```
SQLGlot can even translate custom time formats:
```python
import sqlglot
sqlglot.transpile("SELECT STRFTIME(x, '%y-%-m-%S')", read="duckdb", write="hive")[0]
```
```sql
"SELECT DATE_FORMAT(x, 'yy-M-ss')"
```
Identifier delimiters and data types can be translated as well:
```python
import sqlglot
# Spark SQL requires backticks (`) for delimited identifiers and uses `FLOAT` over `REAL`
sql = """WITH baz AS (SELECT a, c FROM foo WHERE a = 1) SELECT f.a, b.b, baz.c, CAST("b"."a" AS REAL) d FROM foo f JOIN bar b ON f.a = b.a LEFT JOIN baz ON f.a = baz.a"""
# Translates the query into Spark SQL, formats it, and delimits all of its identifiers
print(sqlglot.transpile(sql, write="spark", identify=True, pretty=True)[0])
```
```sql
WITH `baz` AS (
SELECT
`a`,
`c`
FROM `foo`
WHERE
`a` = 1
)
SELECT
`f`.`a`,
`b`.`b`,
`baz`.`c`,
CAST(`b`.`a` AS FLOAT) AS `d`
FROM `foo` AS `f`
JOIN `bar` AS `b`
ON `f`.`a` = `b`.`a`
LEFT JOIN `baz`
ON `f`.`a` = `baz`.`a`
```
Comments are also preserved on a best-effort basis:
```python
sql = """
/* multi
line
comment
*/
SELECT
tbl.cola /* comment 1 */ + tbl.colb /* comment 2 */,
CAST(x AS SIGNED), # comment 3
y -- comment 4
FROM
bar /* comment 5 */,
tbl # comment 6
"""
# Note: MySQL-specific comments (`#`) are converted into standard syntax
print(sqlglot.transpile(sql, read='mysql', pretty=True)[0])
```
```sql
/* multi
line
comment
*/
SELECT
tbl.cola /* comment 1 */ + tbl.colb /* comment 2 */,
CAST(x AS SIGNED), /* comment 3 */
y /* comment 4 */
FROM bar /* comment 5 */, tbl /* comment 6 */
```
### Metadata
You can explore SQL with expression helpers to do things like find columns and tables in a query:
```python
from sqlglot import parse_one, exp
# print all column references (a and b)
for column in parse_one("SELECT a, b + 1 AS c FROM d").find_all(exp.Column):
print(column.alias_or_name)
# find all projections in select statements (a and c)
for select in parse_one("SELECT a, b + 1 AS c FROM d").find_all(exp.Select):
for projection in select.expressions:
print(projection.alias_or_name)
# find all tables (x, y, z)
for table in parse_one("SELECT * FROM x JOIN y JOIN z").find_all(exp.Table):
print(table.name)
```
Read the [ast primer](https://github.com/tobymao/sqlglot/blob/main/posts/ast_primer.md) to learn more about SQLGlot's internals.
### Parser Errors
When the parser detects an error in the syntax, it raises a `ParseError`:
```python
import sqlglot
sqlglot.transpile("SELECT foo FROM (SELECT baz FROM t")
```
```
sqlglot.errors.ParseError: Expecting ). Line 1, Col: 34.
SELECT foo FROM (SELECT baz FROM t
~
```
Structured syntax errors are accessible for programmatic use:
```python
import sqlglot.errors
try:
sqlglot.transpile("SELECT foo FROM (SELECT baz FROM t")
except sqlglot.errors.ParseError as e:
print(e.errors)
```
```python
[{
'description': 'Expecting )',
'line': 1,
'col': 34,
'start_context': 'SELECT foo FROM (SELECT baz FROM ',
'highlight': 't',
'end_context': '',
'into_expression': None
}]
```
### Unsupported Errors
It may not be possible to translate some queries between certain dialects. For these cases, SQLGlot may emit a warning and will proceed to do a best-effort translation by default:
```python
import sqlglot
sqlglot.transpile("SELECT APPROX_DISTINCT(a, 0.1) FROM foo", read="presto", write="hive")
```
```sql
Argument 'accuracy' is not supported for expression 'ApproxDistinct' when targeting Hive.
'SELECT APPROX_COUNT_DISTINCT(a) FROM foo'
```
This behavior can be changed by setting the [`unsupported_level`](https://github.com/tobymao/sqlglot/blob/b0e8dc96ba179edb1776647b5bde4e704238b44d/sqlglot/errors.py#L9) attribute. For example, we can set it to either `RAISE` or `IMMEDIATE` to ensure an exception is raised instead:
```python
import sqlglot
sqlglot.transpile("SELECT APPROX_DISTINCT(a, 0.1) FROM foo", read="presto", write="hive", unsupported_level=sqlglot.ErrorLevel.RAISE)
```
```
sqlglot.errors.UnsupportedError: Argument 'accuracy' is not supported for expression 'ApproxDistinct' when targeting Hive.
```
Some queries need additional information to be transpiled accurately, such as the schemas of the referenced tables. This is because certain transformations are type-sensitive and depend on type inference. The `qualify` and `annotate_types` optimizer [rules](https://github.com/tobymao/sqlglot/tree/main/sqlglot/optimizer) can provide this information, but they are not used by default because they add overhead and complexity.
Transpilation is a hard problem, so SQLGlot solves it incrementally. Some dialect pairs may lack support for certain inputs, but coverage improves over time. We highly appreciate well-documented and tested issues or PRs, so feel free to [reach out](#get-in-touch) if you need guidance!
### Build and Modify SQL
SQLGlot supports incrementally building SQL expressions:
```python
from sqlglot import select, condition
where = condition("x=1").and_("y=1")
select("*").from_("y").where(where).sql()
```
```sql
'SELECT * FROM y WHERE x = 1 AND y = 1'
```
It's possible to modify a parsed tree:
```python
from sqlglot import parse_one
parse_one("SELECT x FROM y").from_("z").sql()
```
```sql
'SELECT x FROM z'
```
Parsed expressions can also be transformed recursively by applying a mapping function to each node in the tree:
```python
from sqlglot import exp, parse_one
expression_tree = parse_one("SELECT a FROM x")
def transformer(node):
if isinstance(node, exp.Column) and node.name == "a":
return parse_one("FUN(a)")
return node
transformed_tree = expression_tree.transform(transformer)
transformed_tree.sql()
```
```sql
'SELECT FUN(a) FROM x'
```
### SQL Optimizer
SQLGlot can rewrite queries into an "optimized" form. It performs a variety of [techniques](https://github.com/tobymao/sqlglot/blob/main/sqlglot/optimizer/optimizer.py) to create a new canonical AST. This AST can be used to standardize queries or provide the foundations for implementing an actual engine. For example:
```python
import sqlglot
from sqlglot.optimizer import optimize
print(
optimize(
sqlglot.parse_one("""
SELECT A OR (B OR (C AND D))
FROM x
WHERE Z = date '2021-01-01' + INTERVAL '1' month OR 1 = 0
"""),
schema={"x": {"A": "INT", "B": "INT", "C": "INT", "D": "INT", "Z": "STRING"}}
).sql(pretty=True)
)
```
```sql
SELECT
(
"x"."a" <> 0 OR "x"."b" <> 0 OR "x"."c" <> 0
)
AND (
"x"."a" <> 0 OR "x"."b" <> 0 OR "x"."d" <> 0
) AS "_col_0"
FROM "x" AS "x"
WHERE
CAST("x"."z" AS DATE) = CAST('2021-02-01' AS DATE)
```
### AST Introspection
You can see the AST version of the parsed SQL by calling `repr`:
```python
from sqlglot import parse_one
print(repr(parse_one("SELECT a + 1 AS z")))
```
```python
Select(
expressions=[
Alias(
this=Add(
this=Column(
this=Identifier(this=a, quoted=False)),
expression=Literal(this=1, is_string=False)),
alias=Identifier(this=z, quoted=False))])
```
### AST Diff
SQLGlot can calculate the semantic difference between two expressions and output changes in a form of a sequence of actions needed to transform a source expression into a target one:
```python
from sqlglot import diff, parse_one
diff(parse_one("SELECT a + b, c, d"), parse_one("SELECT c, a - b, d"))
```
```python
[
Remove(expression=Add(
this=Column(
this=Identifier(this=a, quoted=False)),
expression=Column(
this=Identifier(this=b, quoted=False)))),
Insert(expression=Sub(
this=Column(
this=Identifier(this=a, quoted=False)),
expression=Column(
this=Identifier(this=b, quoted=False)))),
Keep(
source=Column(this=Identifier(this=a, quoted=False)),
target=Column(this=Identifier(this=a, quoted=False))),
...
]
```
The sequence consists of `Insert`, `Remove`, `Move`, `Update` and `Keep` actions. The order of the matched (`Keep` / `Move`) actions may vary between runs.
See also: [Semantic Diff for SQL](https://github.com/tobymao/sqlglot/blob/main/posts/sql_diff.md).
### Custom Dialects
[Dialects](https://github.com/tobymao/sqlglot/tree/main/sqlglot/dialects) can be added by subclassing `Dialect`:
```python
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",
}
print(Dialect["custom"])
```
```
<class '__main__.Custom'>
```
Note: when `sqlglot[c]` is installed, subclassing may not work properly, because runtime subclassing of the mypyc-compiled classes has been disabled for performance reasons. Use the pure Python version if you need custom dialects.
### SQL Execution
SQLGlot is able to interpret SQL queries, where the tables are represented as Python dictionaries. The engine is not supposed to be fast, but it can be useful for unit testing and running SQL natively across Python objects. Additionally, the foundation can be easily integrated with fast compute kernels, such as [Arrow](https://arrow.apache.org/docs/index.html) and [Pandas](https://pandas.pydata.org/).
The example below showcases the execution of a query that involves aggregations and joins:
```python
from sqlglot.executor import execute
tables = {
"sushi": [
{"id": 1, "price": 1.0},
{"id": 2, "price": 2.0},
{"id": 3, "price": 3.0},
],
"order_items": [
{"sushi_id": 1, "order_id": 1},
{"sushi_id": 1, "order_id": 1},
{"sushi_id": 2, "order_id": 1},
{"sushi_id": 3, "order_id": 2},
],
"orders": [
{"id": 1, "user_id": 1},
{"id": 2, "user_id": 2},
],
}
execute(
"""
SELECT
o.user_id,
SUM(s.price) AS price
FROM orders o
JOIN order_items i
ON o.id = i.order_id
JOIN sushi s
ON i.sushi_id = s.id
GROUP BY o.user_id
""",
tables=tables
)
```
```python
user_id price
1 4.0
2 3.0
```
See also: [Writing a Python SQL engine from scratch](https://github.com/tobymao/sqlglot/blob/main/posts/python_sql_engine.md).
## Used By
* [SQLMesh](https://github.com/TobikoData/sqlmesh)
* [Apache Superset](https://github.com/apache/superset)
* [Dagster](https://github.com/dagster-io/dagster)
* [Fugue](https://github.com/fugue-project/fugue)
* [Ibis](https://github.com/ibis-project/ibis)
* [dlt](https://github.com/dlt-hub/dlt)
* [mysql-mimic](https://github.com/kelsin/mysql-mimic)
* [Querybook](https://github.com/pinterest/querybook)
* [Quokka](https://github.com/marsupialtail/quokka)
* [Splink](https://github.com/moj-analytical-services/splink)
* [SQLFrame](https://github.com/eakmanrq/sqlframe)
## Documentation
SQLGlot uses [pdoc](https://pdoc.dev/) to serve its API documentation.
A hosted version is on the [SQLGlot website](https://sqlglot.com/), or you can build locally with:
```
make docs-serve
```
## Run Tests and Lint
```
make style # Only linter checks
make unit # Only unit tests (pure Python)
make test # Unit and integration tests (pure Python)
make unitc # Only unit tests (mypyc compiled)
make testc # Unit and integration tests (mypyc compiled)
make check # Full test suite & linter checks
make clean # Remove compiled C artifacts (.so files, build dirs)
```
## Benchmarks
[Benchmarks](https://github.com/tobymao/sqlglot/blob/main/benchmarks/parse.py) run on Python 3.14.3 in seconds.
sqlglot, sqltree, sqlparse, and sqlfluff are python based whereas sqloxide and polyglot-sql are rust bindings.
| Query | sqlglot | sqlglot[c] | sqltree | sqlparse | sqlfluff | sqloxide | polyglot-sql |
| ----------------- | --------------- | --------------- | --------------- | ---------------- | ----------------- | --------------- | --------------- |
| tpch | 0.002709 (1.00) | 0.000740 (0.27) | 0.002172 (0.80) | 0.014152 (5.22) | 0.241027 (88.97) | 0.000655 (0.24) | 0.000698 (0.26) |
| short | 0.000226 (1.00) | 0.000075 (0.33) | 0.000184 (0.81) | 0.000938 (4.15) | 0.031542 (139.47) | 0.000041 (0.18) | 0.000174 (0.77) |
| deep_arithmetic | 0.007760 (1.00) | 0.002015 (0.26) | 0.005927 (0.76) | N/A | 1.359824 (175.22) | 0.003117 (0.40) | 0.002964 (0.38) |
| large_in | 0.407987 (1.00) | 0.101644 (0.25) | 0.467943 (1.15) | N/A | N/A | 0.147765 (0.36) | 0.105854 (0.26) |
| values | 0.466734 (1.00) | 0.113762 (0.24) | 0.522797 (1.12) | N/A | N/A | 0.117628 (0.25) | 0.117169 (0.25) |
| many_joins | 0.011943 (1.00) | 0.002701 (0.23) | 0.009887 (0.83) | 0.059303 (4.97) | 1.246253 (104.35) | 0.002918 (0.24) | 0.002964 (0.25) |
| many_unions | 0.041321 (1.00) | 0.008291 (0.20) | 0.038249 (0.93) | N/A | 1.826401 (44.20) | 0.012395 (0.30) | 0.013087 (0.32) |
| nested_subqueries | 0.001200 (1.00) | 0.000235 (0.20) | N/A | 0.003860 (3.22) | 0.089490 (74.56) | 0.000215 (0.18) | 0.000262 (0.22) |
| many_columns | 0.011821 (1.00) | 0.002825 (0.24) | 0.012722 (1.08) | 0.238510 (20.18) | 1.050386 (88.86) | 0.002515 (0.21) | 0.003765 (0.32) |
| large_case | 0.035822 (1.00) | 0.008593 (0.24) | 0.033578 (0.94) | N/A | 4.200220 (117.25) | 0.009870 (0.28) | 0.009442 (0.26) |
| complex_where | 0.032710 (1.00) | 0.006602 (0.20) | N/A | 0.136203 (4.16) | 2.492927 (76.21) | 0.006002 (0.18) | 0.007787 (0.24) |
| many_ctes | 0.017610 (1.00) | 0.003630 (0.21) | 0.012377 (0.70) | 0.123620 (7.02) | 0.657611 (37.34) | 0.004197 (0.24) | 0.003273 (0.19) |
| many_windows | 0.020790 (1.00) | 0.005751 (0.28) | N/A | 0.203144 (9.77) | 1.421216 (68.36) | 0.003941 (0.19) | 0.004570 (0.22) |
| nested_functions | 0.000703 (1.00) | 0.000189 (0.27) | 0.000754 (1.07) | 0.005082 (7.23) | 0.091007 (129.51) | 0.000168 (0.24) | 0.000225 (0.32) |
| large_strings | 0.005073 (1.00) | 0.001480 (0.29) | 0.014533 (2.86) | 0.049392 (9.74) | 0.320672 (63.22) | 0.001616 (0.32) | 0.002151 (0.42) |
| many_numbers | 0.103898 (1.00) | 0.024483 (0.24) | 0.120119 (1.16) | N/A | N/A | 0.031667 (0.30) | 0.026880 (0.26) |
```
make bench # Run parsing benchmark
make bench-optimize # Run optimization benchmark
```
## Optional Dependencies
SQLGlot uses [dateutil](https://github.com/dateutil/dateutil) to simplify literal timedelta expressions. The optimizer will not simplify expressions like the following if the module cannot be found:
```sql
x + interval '1' month
```
## Supported Dialects
| Dialect | Support Level |
|---------|---------------|
| Athena | Official |
| BigQuery | Official |
| ClickHouse | Official |
| Databricks | Official |
| DAX | Community |
| Doris | Community |
| Dremio | Community |
| Drill | Community |
| Druid | Community |
| DuckDB | Official |
| Dune | Community |
| Exasol | Community |
| Fabric | Community |
| Hive | Official |
| Materialize | Community |
| MySQL | Official |
| Oracle | Official |
| Postgres | Official |
| Presto | Official |
| PRQL | Community |
| Redshift | Official |
| RisingWave | Community |
| SingleStore | Community |
| Snowflake | Official |
| Solr | Community |
| Spark | Official |
| SQLite | Official |
| StarRocks | Official |
| Tableau | Official |
| Teradata | Community |
| Trino | Official |
| TSQL | Official |
| YDB | [Plugin](https://pypi.org/project/ydb-sqlglot-plugin) |
| MaxCompute | [Plugin](https://pypi.org/project/sqlglot-maxcompute) |
**Official Dialects** are maintained by the core SQLGlot team with higher priority for bug fixes and feature additions.
**Community Dialects** are developed and maintained primarily through community contributions. These are fully functional but may receive lower priority for issue resolution compared to officially supported dialects. We welcome and encourage community contributions to improve these dialects.
**Plugin Dialects** (supported since v28.6.0) are third-party dialects developed and maintained in external repositories by independent contributors. These dialects are not part of the SQLGlot codebase and are distributed as separate packages. The SQLGlot team does not provide support or maintenance for plugin dialects — please direct any issues or feature requests to their respective repositories. See [Creating a Dialect Plugin](#creating-a-dialect-plugin) below for information on how to build your own.
### Creating a Dialect Plugin
If your database isn't supported, you can create a plugin that registers a custom dialect via entry points. Create a package with your dialect class and register it in `setup.py`:
```python
from setuptools import setup
setup(
name="mydb-sqlglot-dialect",
entry_points={
"sqlglot.dialects": [
"mydb = my_package.dialect:MyDB",
],
},
)
```
The dialect will be automatically discovered and can be used like any built-in dialect:
```python
from sqlglot import transpile
transpile("SELECT * FROM t", read="mydb", write="postgres")
```
See the [Custom Dialects](#custom-dialects) section for implementation details.
