https://github.com/tobymao/sqlglot Skip to content Sign up * Product + Features + Mobile + Actions + Codespaces + Copilot + Packages + Security + Code review + Issues + Integrations + GitHub Sponsors + Customer stories * Team * Enterprise * Explore + Explore GitHub + Learn and contribute + Topics + Collections + Trending + Skills + GitHub Sponsors + Open source guides + Connect with others + The ReadME Project + Events + Community forum + GitHub Education + GitHub Stars program * Marketplace * Pricing + Plans + Compare plans + Contact Sales + Education [ ] * # In this repository All GitHub | Jump to | * No suggested jump to results * # In this repository All GitHub | Jump to | * # In this user All GitHub | Jump to | * # In this repository All GitHub | Jump to | Sign in Sign up {{ message }} tobymao / sqlglot Public * Notifications * Fork 43 * Star 456 Python SQL Parser and Transpiler License MIT license 456 stars 43 forks Star Notifications * Code * Issues 5 * Pull requests 0 * Discussions * Actions * Projects 0 * Wiki * Security * Insights More * Code * Issues * Pull requests * Discussions * Actions * Projects * Wiki * Security * Insights tobymao/sqlglot This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. main Switch branches/tags [ ] Branches Tags Could not load branches Nothing to show {{ refName }} default View all branches Could not load tags Nothing to show {{ refName }} default View all tags 2 branches 153 tags Code Latest commit @tobymao tobymao Update README.md ... f5ca70a Jul 4, 2022 Update README.md f5ca70a Git stats * 776 commits Files Permalink Failed to load latest commit information. Type Name Latest commit message Commit time .github/workflows all strings Jun 30, 2022 .vscode Refactor Sep 10, 2021 benchmarks Allow additional function arguments in transform Mar 4, 2022 sqlglot v3.0.4 Jul 4, 2022 tests allow nested ctes fixes #192 Jul 4, 2022 .gitignore Expression building (#96) Apr 21, 2022 .pylintrc fix all tests Jun 21, 2022 LICENSE Initial commit Mar 13, 2021 README.md Update README.md Jul 4, 2022 format_code.sh use python -m Sep 26, 2021 requirements.txt make dateutil.relativedelta an optional requirement Jun 28, 2022 run_checks.sh use python -m Sep 26, 2021 setup.py fix: publish optimizer package (#156) Jun 1, 2022 View code [ ] SQLGlot Install Examples Formatting and Transpiling Metadata Customization Custom Types Custom Functions Parser Errors Unsupported Errors Build and Modify SQL SQL Annotations SQL Optimizer Benchmarks Run Tests and Lint Optional Dependencies README.md SQLGlot SQLGlot is a no dependency Python SQL parser, transpiler, and optimizer. It can be used to format SQL or translate between different dialects like Presto, Spark, and Hive. It aims to read a wide variety of SQL inputs and output syntatically correct SQL in the targeted dialects. It is currently the fastest pure-Python SQL parser. You can easily customize the parser to support UDF's across dialects as well through the transform API. Syntax errors are highlighted and dialect incompatibilities can warn or raise depending on configurations. Install From PyPI pip3 install sqlglot Or with a local checkout pip3 install -e . Examples Easily translate from one dialect to another. For example, date/time functions vary from dialects and can be hard to deal with. import sqlglot sqlglot.transpile("SELECT EPOCH_MS(1618088028295)", read='duckdb', write='hive') SELECT TO_UTC_TIMESTAMP(FROM_UNIXTIME(1618088028295 / 1000, 'yyyy-MM-dd HH:mm:ss'), 'UTC') SQLGlot can even translate custom time formats. import sqlglot sqlglot.transpile("SELECT STRFTIME(x, '%y-%-m-%S')", read='duckdb', write='hive') SELECT DATE_FORMAT(x, 'yy-M-ss')" Formatting and Transpiling Read in a SQL statement with a CTE and CASTING to a REAL and then transpiling to Spark. Spark uses backticks as identifiers and the REAL type is transpiled to FLOAT. import sqlglot 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""" sqlglot.transpile(sql, write='spark', identify=True, pretty=True)[0] 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` Metadata You can explore SQL with expression helpers to do things like find columns and tables. from sqlglot import parse_one, exp for column in parse_one("SELECT a, b + 1 AS c FROM d").find_all(exp.Column): print(column.alias_or_name) Customization Custom Types A simple transform on types can be accomplished by providing a corresponding mapping: from sqlglot import * transpile("SELECT CAST(a AS INT) FROM x", type_mapping={exp.DataType.Type.INT: "SPECIAL INT"})[0] SELECT CAST(a AS SPECIAL INT) FROM x More complicated transforms can be accomplished by using the Tokenizer, Parser, and Generator directly. Custom Functions In this example, we want to parse a UDF SPECIAL_UDF and then output another version called SPECIAL_UDF_INVERSE with the arguments switched. from sqlglot import * from sqlglot.expressions import Func class SpecialUdf(Func): arg_types = {'a': True, 'b': True} tokens = Tokenizer().tokenize("SELECT SPECIAL_UDF(a, b) FROM x") Here is the output of the tokenizer: [ , , , , , , , , , ] expression = Parser(functions={ **SpecialUdf.default_parser_mappings(), }).parse(tokens)[0] The expression tree produced by the parser: (SELECT distinct: False, expressions: (SPECIALUDF a: (COLUMN this: (IDENTIFIER this: a, quoted: False)), b: (COLUMN this: (IDENTIFIER this: b, quoted: False))), from: (FROM expressions: (TABLE this: (IDENTIFIER this: x, quoted: False)))) Finally generating the new SQL: Generator(transforms={ SpecialUdf: lambda self, e: f"SPECIAL_UDF_INVERSE({self.sql(e, 'b')}, {self.sql(e, 'a')})" }).generate(expression) SELECT SPECIAL_UDF_INVERSE(b, a) FROM x Parser Errors A syntax error will result in a parser error. transpile("SELECT foo( FROM bar") sqlglot.errors.ParseError: Expected ) SELECT foo( __FROM__ bar Unsupported Errors Presto APPROX_DISTINCT supports the accuracy argument which is not supported in Spark. transpile( 'SELECT APPROX_DISTINCT(a, 0.1) FROM foo', read='presto', write='spark', ) WARNING:root:APPROX_COUNT_DISTINCT does not support accuracy SELECT APPROX_COUNT_DISTINCT(a) FROM foo Build and Modify SQL SQLGlot supports incrementally building sql expressions. from sqlglot import select, condition where = condition("x=1").and_("y=1") select("*").from_("y").where(where).sql() Which outputs: SELECT * FROM y WHERE x = 1 AND y = 1 You can also modify a parsed tree: from sqlglot import parse_one parse_one("SELECT x FROM y").from_("z").sql() Which outputs: SELECT x FROM y, z There is also a way to recursively transform the parsed tree by applying a mapping function to each tree node: 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() Which outputs: SELECT FUN(a) FROM x SQL Annotations SQLGlot supports annotations in the sql expression. This is an experimental feature that is not part of any of the SQL standards but it can be useful when needing to annotate what a selected field is supposed to be. Below is an example: SELECT user #primary_key, country FROM users SQL Optimizer SQLGlot can rewrite queries into an "optimized" form. It performs a variety of techniques to create a new canonical AST. This AST can be used to standaradize queries or provide the foundations for implementing an actual engine. import sqlglot from sqlglot.optimizer import optimize >>> 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) """ SELECT ( "x"."A" OR "x"."B" OR "x"."C" ) AND ( "x"."A" OR "x"."B" OR "x"."D" ) AS "_col_0" FROM "x" AS "x" WHERE "x"."Z" = CAST('2021-02-01' AS DATE) """ Benchmarks Benchmarks run on Python 3.9.6 in seconds. Query sqlglot sqlparse moz_sql_parser sqloxide short 0.00038 0.00104 0.00174 0.000060 long 0.00508 0.01522 0.02162 0.000597 crazy 0.01871 3.49415 0.35346 0.003104 Run Tests and Lint pip install -r requirements.txt ./format_code.sh ./run_checks.sh Optional Dependencies SQLGlot uses dateutil to simplify literal timedelta expressions. The optimizer will not simplify expressions like x + interval '1' month if the module cannot be found. About Python SQL Parser and Transpiler Topics python parser sql translation optimizer transpiler Resources Readme License MIT license Stars 456 stars Watchers 19 watching Forks 43 forks Releases 153 tags Packages 0 No packages published Used by 12 * @RyanHarrisonOriginal * @ptorrestr * @jjcaine * @Comparative-Inc * @audaykumar * @sasano8 * @coast-team * @Gonlo2 + 4 Contributors 17 * @tobymao * @izeigerman * @acreux * @barakalon * @robert8138 * @th368 * @nolamesa * @RobinL * @samnlindsay * @maggie1121 * @eakmanrq + 6 contributors Languages * Python 100.0% Footer (c) 2022 GitHub, Inc. Footer navigation * Terms * Privacy * Security * Status * Docs * Contact GitHub * Pricing * API * Training * Blog * About You can't perform that action at this time. You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.