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.expand_laterals import expand_laterals 14from sqlglot.optimizer.expand_multi_table_selects import expand_multi_table_selects 15from sqlglot.optimizer.isolate_table_selects import isolate_table_selects 16from sqlglot.optimizer.lower_identities import lower_identities 17from sqlglot.optimizer.merge_subqueries import merge_subqueries 18from sqlglot.optimizer.normalize import normalize 19from sqlglot.optimizer.optimize_joins import optimize_joins 20from sqlglot.optimizer.pushdown_predicates import pushdown_predicates 21from sqlglot.optimizer.pushdown_projections import pushdown_projections 22from sqlglot.optimizer.qualify_columns import qualify_columns, validate_qualify_columns 23from sqlglot.optimizer.qualify_tables import qualify_tables 24from sqlglot.optimizer.simplify import simplify 25from sqlglot.optimizer.unnest_subqueries import unnest_subqueries 26from sqlglot.schema import ensure_schema 27 28RULES = ( 29 lower_identities, 30 qualify_tables, 31 isolate_table_selects, 32 qualify_columns, 33 expand_laterals, 34 pushdown_projections, 35 validate_qualify_columns, 36 normalize, 37 unnest_subqueries, 38 expand_multi_table_selects, 39 pushdown_predicates, 40 optimize_joins, 41 eliminate_subqueries, 42 merge_subqueries, 43 eliminate_joins, 44 eliminate_ctes, 45 annotate_types, 46 canonicalize, 47 simplify, 48) 49 50 51def optimize( 52 expression: str | exp.Expression, 53 schema: t.Optional[dict | Schema] = None, 54 db: t.Optional[str] = None, 55 catalog: t.Optional[str] = None, 56 dialect: DialectType = None, 57 rules: t.Sequence[t.Callable] = RULES, 58 **kwargs, 59): 60 """ 61 Rewrite a sqlglot AST into an optimized form. 62 63 Args: 64 expression: expression to optimize 65 schema: database schema. 66 This can either be an instance of `sqlglot.optimizer.Schema` or a mapping in one of 67 the following forms: 68 1. {table: {col: type}} 69 2. {db: {table: {col: type}}} 70 3. {catalog: {db: {table: {col: type}}}} 71 If no schema is provided then the default schema defined at `sqlgot.schema` will be used 72 db: specify the default database, as might be set by a `USE DATABASE db` statement 73 catalog: specify the default catalog, as might be set by a `USE CATALOG c` statement 74 dialect: The dialect to parse the sql string. 75 rules: sequence of optimizer rules to use. 76 Many of the rules require tables and columns to be qualified. 77 Do not remove qualify_tables or qualify_columns from the sequence of rules unless you know 78 what you're doing! 79 **kwargs: If a rule has a keyword argument with a same name in **kwargs, it will be passed in. 80 Returns: 81 sqlglot.Expression: optimized expression 82 """ 83 schema = ensure_schema(schema or sqlglot.schema, dialect=dialect) 84 possible_kwargs = {"db": db, "catalog": catalog, "schema": schema, **kwargs} 85 expression = exp.maybe_parse(expression, dialect=dialect, copy=True) 86 for rule in rules: 87 # Find any additional rule parameters, beyond `expression` 88 rule_params = rule.__code__.co_varnames 89 rule_kwargs = { 90 param: possible_kwargs[param] for param in rule_params if param in possible_kwargs 91 } 92 expression = rule(expression, **rule_kwargs) 93 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 0x7fac3cd8eef0>, <function qualify_tables at 0x7fac3cdc5630>, <function isolate_table_selects at 0x7fac3cd8edd0>, <function qualify_columns at 0x7fac3cdc4550>, <function expand_laterals at 0x7fac3cd8eb90>, <function pushdown_projections at 0x7fac3cdc5000>, <function validate_qualify_columns at 0x7fac3cdc45e0>, <function normalize at 0x7fac3cd8c9d0>, <function unnest_subqueries at 0x7fac3cdc5750>, <function expand_multi_table_selects at 0x7fac3cd8ed40>, <function pushdown_predicates at 0x7fac3cdc40d0>, <function optimize_joins at 0x7fac3cd8fc70>, <function eliminate_subqueries at 0x7fac3cd8e7a0>, <function merge_subqueries at 0x7fac3cd8f1c0>, <function eliminate_joins at 0x7fac3cd8c8b0>, <function eliminate_ctes at 0x7fac3cd8c790>, <function annotate_types at 0x7fac3cd5cdc0>, <function canonicalize at 0x7fac3cd8c1f0>, <function simplify at 0x7fac3cd8caf0>), **kwargs):
52def optimize( 53 expression: str | exp.Expression, 54 schema: t.Optional[dict | Schema] = None, 55 db: t.Optional[str] = None, 56 catalog: t.Optional[str] = None, 57 dialect: DialectType = None, 58 rules: t.Sequence[t.Callable] = RULES, 59 **kwargs, 60): 61 """ 62 Rewrite a sqlglot AST into an optimized form. 63 64 Args: 65 expression: expression to optimize 66 schema: database schema. 67 This can either be an instance of `sqlglot.optimizer.Schema` or a mapping in one of 68 the following forms: 69 1. {table: {col: type}} 70 2. {db: {table: {col: type}}} 71 3. {catalog: {db: {table: {col: type}}}} 72 If no schema is provided then the default schema defined at `sqlgot.schema` will be used 73 db: specify the default database, as might be set by a `USE DATABASE db` statement 74 catalog: specify the default catalog, as might be set by a `USE CATALOG c` statement 75 dialect: The dialect to parse the sql string. 76 rules: sequence of optimizer rules to use. 77 Many of the rules require tables and columns to be qualified. 78 Do not remove qualify_tables or qualify_columns from the sequence of rules unless you know 79 what you're doing! 80 **kwargs: If a rule has a keyword argument with a same name in **kwargs, it will be passed in. 81 Returns: 82 sqlglot.Expression: optimized expression 83 """ 84 schema = ensure_schema(schema or sqlglot.schema, dialect=dialect) 85 possible_kwargs = {"db": db, "catalog": catalog, "schema": schema, **kwargs} 86 expression = exp.maybe_parse(expression, dialect=dialect, copy=True) 87 for rule in rules: 88 # Find any additional rule parameters, beyond `expression` 89 rule_params = rule.__code__.co_varnames 90 rule_kwargs = { 91 param: possible_kwargs[param] for param in rule_params if param in possible_kwargs 92 } 93 expression = rule(expression, **rule_kwargs) 94 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