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