sqlglot.optimizer.optimizer
1from __future__ import annotations 2 3import typing as t 4 5import sqlglot 6from sqlglot import Schema, exp 7from sqlglot.dialects.dialect import DialectType 8from sqlglot.optimizer.annotate_types import annotate_types 9from sqlglot.optimizer.canonicalize import canonicalize 10from sqlglot.optimizer.eliminate_ctes import eliminate_ctes 11from sqlglot.optimizer.eliminate_joins import eliminate_joins 12from sqlglot.optimizer.eliminate_subqueries import eliminate_subqueries 13from sqlglot.optimizer.isolate_table_selects import isolate_table_selects 14from sqlglot.optimizer.lower_identities import lower_identities 15from sqlglot.optimizer.merge_subqueries import merge_subqueries 16from sqlglot.optimizer.normalize import normalize 17from sqlglot.optimizer.optimize_joins import optimize_joins 18from sqlglot.optimizer.pushdown_predicates import pushdown_predicates 19from sqlglot.optimizer.pushdown_projections import pushdown_projections 20from sqlglot.optimizer.qualify_columns import qualify_columns, validate_qualify_columns 21from sqlglot.optimizer.qualify_tables import qualify_tables 22from sqlglot.optimizer.simplify import simplify 23from sqlglot.optimizer.unnest_subqueries import unnest_subqueries 24from sqlglot.schema import ensure_schema 25 26RULES = ( 27 lower_identities, 28 qualify_tables, 29 isolate_table_selects, 30 qualify_columns, 31 pushdown_projections, 32 validate_qualify_columns, 33 normalize, 34 unnest_subqueries, 35 pushdown_predicates, 36 optimize_joins, 37 eliminate_subqueries, 38 merge_subqueries, 39 eliminate_joins, 40 eliminate_ctes, 41 annotate_types, 42 canonicalize, 43 simplify, 44) 45 46 47def optimize( 48 expression: str | exp.Expression, 49 schema: t.Optional[dict | Schema] = None, 50 db: t.Optional[str] = None, 51 catalog: t.Optional[str] = None, 52 dialect: DialectType = None, 53 rules: t.Sequence[t.Callable] = RULES, 54 **kwargs, 55): 56 """ 57 Rewrite a sqlglot AST into an optimized form. 58 59 Args: 60 expression: expression to optimize 61 schema: database schema. 62 This can either be an instance of `sqlglot.optimizer.Schema` or a mapping in one of 63 the following forms: 64 1. {table: {col: type}} 65 2. {db: {table: {col: type}}} 66 3. {catalog: {db: {table: {col: type}}}} 67 If no schema is provided then the default schema defined at `sqlgot.schema` will be used 68 db: specify the default database, as might be set by a `USE DATABASE db` statement 69 catalog: specify the default catalog, as might be set by a `USE CATALOG c` statement 70 dialect: The dialect to parse the sql string. 71 rules: sequence of optimizer rules to use. 72 Many of the rules require tables and columns to be qualified. 73 Do not remove qualify_tables or qualify_columns from the sequence of rules unless you know 74 what you're doing! 75 **kwargs: If a rule has a keyword argument with a same name in **kwargs, it will be passed in. 76 Returns: 77 sqlglot.Expression: optimized expression 78 """ 79 schema = ensure_schema(schema or sqlglot.schema, dialect=dialect) 80 possible_kwargs = {"db": db, "catalog": catalog, "schema": schema, **kwargs} 81 expression = exp.maybe_parse(expression, dialect=dialect, copy=True) 82 83 for rule in rules: 84 # Find any additional rule parameters, beyond `expression` 85 rule_params = rule.__code__.co_varnames 86 rule_kwargs = { 87 param: possible_kwargs[param] for param in rule_params if param in possible_kwargs 88 } 89 expression = rule(expression, **rule_kwargs) 90 91 return expression
def
optimize( expression: str | sqlglot.expressions.Expression, schema: Union[dict, sqlglot.schema.Schema, NoneType] = None, db: Optional[str] = None, catalog: Optional[str] = None, dialect: Union[str, sqlglot.dialects.dialect.Dialect, Type[sqlglot.dialects.dialect.Dialect], NoneType] = None, rules: Sequence[Callable] = (<function lower_identities at 0x7f0af2fc76d0>, <function qualify_tables at 0x7f0af2fa1d80>, <function isolate_table_selects at 0x7f0af2fc7370>, <function qualify_columns at 0x7f0af2fa0c10>, <function pushdown_projections at 0x7f0af2fa1750>, <function validate_qualify_columns at 0x7f0af2fa0ca0>, <function normalize at 0x7f0af2fc5630>, <function unnest_subqueries at 0x7f0af2fa1e10>, <function pushdown_predicates at 0x7f0af2fa0700>, <function optimize_joins at 0x7f0af2fa03a0>, <function eliminate_subqueries at 0x7f0af2fc72e0>, <function merge_subqueries at 0x7f0af2fc79a0>, <function eliminate_joins at 0x7f0af2fc53f0>, <function eliminate_ctes at 0x7f0af2fc52d0>, <function annotate_types at 0x7f0af2ff9a20>, <function canonicalize at 0x7f0af2fc4d30>, <function simplify at 0x7f0af2fc57e0>), **kwargs):
48def optimize( 49 expression: str | exp.Expression, 50 schema: t.Optional[dict | Schema] = None, 51 db: t.Optional[str] = None, 52 catalog: t.Optional[str] = None, 53 dialect: DialectType = None, 54 rules: t.Sequence[t.Callable] = RULES, 55 **kwargs, 56): 57 """ 58 Rewrite a sqlglot AST into an optimized form. 59 60 Args: 61 expression: expression to optimize 62 schema: database schema. 63 This can either be an instance of `sqlglot.optimizer.Schema` or a mapping in one of 64 the following forms: 65 1. {table: {col: type}} 66 2. {db: {table: {col: type}}} 67 3. {catalog: {db: {table: {col: type}}}} 68 If no schema is provided then the default schema defined at `sqlgot.schema` will be used 69 db: specify the default database, as might be set by a `USE DATABASE db` statement 70 catalog: specify the default catalog, as might be set by a `USE CATALOG c` statement 71 dialect: The dialect to parse the sql string. 72 rules: sequence of optimizer rules to use. 73 Many of the rules require tables and columns to be qualified. 74 Do not remove qualify_tables or qualify_columns from the sequence of rules unless you know 75 what you're doing! 76 **kwargs: If a rule has a keyword argument with a same name in **kwargs, it will be passed in. 77 Returns: 78 sqlglot.Expression: optimized expression 79 """ 80 schema = ensure_schema(schema or sqlglot.schema, dialect=dialect) 81 possible_kwargs = {"db": db, "catalog": catalog, "schema": schema, **kwargs} 82 expression = exp.maybe_parse(expression, dialect=dialect, copy=True) 83 84 for rule in rules: 85 # Find any additional rule parameters, beyond `expression` 86 rule_params = rule.__code__.co_varnames 87 rule_kwargs = { 88 param: possible_kwargs[param] for param in rule_params if param in possible_kwargs 89 } 90 expression = rule(expression, **rule_kwargs) 91 92 return expression
Rewrite a sqlglot AST into an optimized form.
Arguments:
- expression: expression to optimize
- schema: database schema.
This can either be an instance of
sqlglot.optimizer.Schema
or a mapping in one of the following forms: 1. {table: {col: type}} 2. {db: {table: {col: type}}} 3. {catalog: {db: {table: {col: type}}}} If no schema is provided then the default schema defined atsqlgot.schema
will be used - db: specify the default database, as might be set by a
USE DATABASE db
statement - catalog: specify the default catalog, as might be set by a
USE CATALOG c
statement - dialect: The dialect to parse the sql string.
- rules: sequence of optimizer rules to use. Many of the rules require tables and columns to be qualified. Do not remove qualify_tables or qualify_columns from the sequence of rules unless you know what you're doing!
- *kwargs: If a rule has a keyword argument with a same name in *kwargs, it will be passed in.
Returns:
sqlglot.Expression: optimized expression