class SelectStatementSegment(ansi_SelectClauseSegment):
"""A snowflake `SELECT` statement including optional Qualify.
https://docs.snowflake.com/en/sql-reference/constructs/qualify.html
"""
type = "select_statement"
match_grammar = StartsWith(
# NB: In bigquery, the select clause may include an EXCEPT, which
# will also match the set operator, but by starting with the whole
# select clause rather than just the SELECT keyword, we normally
# mitigate that here. But this isn't BigQuery! So we can be more
# efficient and just just the keyword.
"SELECT",
terminator=Ref("SetOperatorSegment"),
)
parse_grammar = Sequence(
Ref("SelectClauseSegment"),
Ref("FromClauseSegment", optional=True),
Ref("WhereClauseSegment", optional=True),
Ref("GroupByClauseSegment", optional=True),
Ref("HavingClauseSegment", optional=True),
Ref("QualifyClauseSegment", optional=True),
Ref("OrderByClauseSegment", optional=True),
Ref("LimitClauseSegment", optional=True),
)
class SetStatementSegment(BaseSegment):
"""Setting an already declared variable.
https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting#set
"""
type = "set_segment"
match_grammar = StartsWith("SET")
parse_grammar = Sequence(
"SET",
OneOf(
Ref("NakedIdentifierSegment"),
Bracketed(Delimited(Ref("NakedIdentifierSegment"))),
),
Ref("EqualsSegment"),
Delimited(
OneOf(
Ref("LiteralGrammar"),
Bracketed(Ref("SelectStatementSegment")),
Ref("BareFunctionSegment"),
Ref("FunctionSegment"),
Bracketed(
Delimited(
OneOf(
Ref("LiteralGrammar"),
Bracketed(Ref("SelectStatementSegment")),
Ref("BareFunctionSegment"),
Ref("FunctionSegment"),
)
)
),
Ref("ArrayLiteralSegment"),
),
),
)
class UnorderedSelectStatementSegment(BaseSegment):
"""A `SELECT` statement without any ORDER clauses or later.
We need to change ANSI slightly to remove LimitClauseSegment
and NamedWindowSegment which don't exist in T-SQL.
"""
type = "select_statement"
# match grammar. This one makes sense in the context of knowing that it's
# definitely a statement, we just don't know what type yet.
match_grammar = StartsWith(
# NB: In bigquery, the select clause may include an EXCEPT, which
# will also match the set operator, but by starting with the whole
# select clause rather than just the SELECT keyword, we mitigate that
# here.
Ref("SelectClauseSegment"),
terminator=OneOf(
Ref("SetOperatorSegment"),
Ref("WithNoSchemaBindingClauseSegment"),
Ref("OrderByClauseSegment"),
),
enforce_whitespace_preceding_terminator=True,
)
parse_grammar = Sequence(
Ref("SelectClauseSegment"),
# Dedent for the indent in the select clause.
# It's here so that it can come AFTER any whitespace.
Dedent,
Ref("FromClauseSegment", optional=True),
Ref("PivotUnpivotStatementSegment", optional=True),
Ref("WhereClauseSegment", optional=True),
Ref("GroupByClauseSegment", optional=True),
Ref("HavingClauseSegment", optional=True),
)
class DistributeByClauseSegment(ansi.OrderByClauseSegment):
"""A `DISTRIBUTE BY` clause like in `SELECT`."""
type = "distributeby_clause"
match_grammar: Matchable = StartsWith(
Sequence("DISTRIBUTE", "BY"),
terminator=OneOf(
"SORT",
"LIMIT",
"HAVING",
"QUALIFY",
# For window functions
"WINDOW",
Ref("FrameClauseUnitGrammar"),
"SEPARATOR",
),
)
parse_grammar: Optional[Matchable] = Sequence(
"DISTRIBUTE",
"BY",
Indent,
Delimited(
Sequence(
OneOf(
Ref("ColumnReferenceSegment"),
Ref("NumericLiteralSegment"),
Ref("ExpressionSegment"),
),
),
terminator=OneOf(
Ref.keyword("LIMIT"), Ref("FrameClauseUnitGrammar"), Ref.keyword("SORT")
),
),
Dedent,
)
class CreateAdapterScriptStatementSegment(BaseSegment):
"""`CREATE SCRIPT` statement create a adapter script.
https://docs.exasol.com/sql/create_script.htm
"""
type = "create_adapter_script"
is_ddl = True
is_dml = False
is_dql = False
is_dcl = False
match_grammar = StartsWith(
"CREATE",
Ref("OrReplaceGrammar", optional=True),
OneOf("JAVA", "PYTHON", Ref("SingleIdentifierGrammar"), optional=True),
"ADAPTER",
"SCRIPT",
)
parse_grammar = Sequence(
"CREATE",
Ref("OrReplaceGrammar", optional=True),
OneOf("JAVA", "PYTHON", Ref("SingleIdentifierGrammar"), optional=True),
"ADAPTER",
"SCRIPT",
Ref("ScriptReferenceSegment"),
Ref("ScriptContentSegment"),
)
class SortByClauseSegment(ansi.OrderByClauseSegment):
"""A `SORT BY` clause like in `SELECT`."""
type = "sortby_clause"
match_grammar: Matchable = StartsWith(
Sequence("SORT", "BY"),
terminator=ansi.OrderByClauseSegment.match_grammar.terminator, # type: ignore
)
parse_grammar: Optional[Matchable] = Sequence(
"SORT",
"BY",
Indent,
Delimited(
Sequence(
OneOf(
Ref("ColumnReferenceSegment"),
Ref("NumericLiteralSegment"),
Ref("ExpressionSegment"),
),
OneOf("ASC", "DESC", optional=True),
Sequence("NULLS", OneOf("FIRST", "LAST"), optional=True),
),
terminator=OneOf(Ref.keyword("LIMIT"), Ref("FrameClauseUnitGrammar")),
),
Dedent,
)
class QualifyClauseSegment(BaseSegment):
"""A `QUALIFY` clause like in `SELECT`.
https://docs.snowflake.com/en/sql-reference/constructs/qualify.html
"""
type = "having_clause"
match_grammar = StartsWith(
"QUALIFY",
terminator=OneOf(
"ORDER",
"LIMIT",
),
)
parse_grammar = Sequence(
"QUALIFY",
Indent,
OneOf(
Bracketed(
Ref("ExpressionSegment"),
),
Ref("ExpressionSegment"),
),
Dedent,
)
class UpdateStatementSegment(BaseSegment):
"""A `Update from` statement.
The UPDATE statement FROM clause is a Teradata extension to the
ANSI SQL:2011 standard.
UPDATE (<table name> | FROM Statement)
SET <set clause list> [ WHERE <search condition> ]
"""
type = "update_statement"
match_grammar = StartsWith("UPDATE")
parse_grammar = Sequence(
"UPDATE",
OneOf(
Ref("TableReferenceSegment"),
Ref("FromUpdateClauseSegment"),
Sequence(
Ref("TableReferenceSegment"),
Ref("FromUpdateClauseSegment"),
),
),
Ref("SetClauseListSegment"),
Ref("WhereClauseSegment", optional=True),
)
class DropStatementSegment(BaseSegment):
"""A `DROP` statement."""
type = "drop_statement"
match_grammar = StartsWith("DROP")
parse_grammar = OneOf(
Ref("DropDatabaseStatementSegment"),
Ref("DropTableStatementSegment"),
# TODO: add other drops
)
class FromUpdateClauseSegment(BaseSegment):
"""A `FROM` clause like in `SELECT` but terminated by SET."""
type = "from_in_update_clause"
match_grammar = StartsWith("FROM", terminator=Ref.keyword("SET"))
parse_grammar = Sequence(
"FROM",
Delimited(
# Optional old school delimited joins
Ref("FromExpressionElementSegment"),
),
)
class TruncateStatementSegment(BaseSegment):
"""`TRUNCATE TABLE` statement."""
type = "truncate_table"
match_grammar = StartsWith("TRUNCATE")
parse_grammar = Sequence(
"TRUNCATE",
Ref.keyword("TABLE", optional=True),
Ref("TableReferenceSegment"),
Ref("PartitionSpecGrammar", optional=True),
)
class CreateUDFScriptStatementSegment(BaseSegment):
"""`CREATE SCRIPT` statement create a UDF script.
https://docs.exasol.com/sql/create_script.htm
"""
type = "create_udf_script"
is_ddl = True
is_dml = False
is_dql = False
is_dcl = False
match_grammar = StartsWith(
Sequence(
"CREATE",
Ref("OrReplaceGrammar", optional=True),
OneOf(
"JAVA",
"PYTHON",
"LUA",
"R",
Ref("SingleIdentifierGrammar"),
optional=True,
),
OneOf("SCALAR", "SET"),
"SCRIPT",
))
parse_grammar = Sequence(
"CREATE",
Ref("OrReplaceGrammar", optional=True),
OneOf("JAVA",
"PYTHON",
"LUA",
"R",
Ref("SingleIdentifierGrammar"),
optional=True),
OneOf("SCALAR", "SET"),
"SCRIPT",
Ref("ScriptReferenceSegment"),
Bracketed(
Sequence(
Delimited(Ref("ColumnDatatypeSegment")),
Ref("OrderByClauseSegment", optional=True),
optional=True,
),
optional=True,
),
OneOf(Sequence("RETURNS", Ref("DatatypeSegment")),
Ref("EmitsGrammar")),
"AS",
Ref("ScriptContentSegment"),
)
class CreateFunctionStatementSegment(BaseSegment):
"""A `CREATE FUNCTION` statement."""
type = "create_function_statement"
is_ddl = True
is_dml = False
is_dql = False
is_dcl = False
match_grammar = StartsWith(
Sequence(
"CREATE",
Ref("OrReplaceGrammar", optional=True),
"FUNCTION",
)
)
parse_grammar = Sequence(
"CREATE",
Ref("OrReplaceGrammar", optional=True),
"FUNCTION",
Ref("FunctionReferenceSegment"),
Bracketed(
Delimited(
Sequence(
Ref("SingleIdentifierGrammar"), # Column name
Ref.keyword("IN", optional=True),
Ref("DatatypeSegment"), # Column type
),
optional=True,
),
),
"RETURN",
Ref("DatatypeSegment"),
OneOf("IS", "AS", optional=True),
AnyNumberOf(
Sequence(
Ref("VariableNameSegment"),
Ref("DatatypeSegment"),
Ref("SemicolonSegment"),
),
optional=True,
),
"BEGIN",
AnyNumberOf(Ref("FunctionBodySegment")),
"RETURN",
Ref("FunctionContentsExpressionGrammar"),
Ref("SemicolonSegment"),
"END",
Ref("FunctionReferenceSegment", optional=True),
Ref("SemicolonSegment", optional=True),
)
class PartitionBySegment(BaseSegment):
"""PARTITION BY partition_expression."""
type = "partition_by_segment"
match_grammar = StartsWith(
"PARTITION",
terminator=OneOf("CLUSTER", "OPTIONS", "AS", Ref("DelimiterSegment")),
enforce_whitespace_preceding_terminator=True,
)
parse_grammar = Sequence(
"PARTITION",
"BY",
Ref("ExpressionSegment"),
)
class UseStatementSegment(BaseSegment):
"""A snowflake `USE` statement.
https://docs.snowflake.com/en/sql-reference/sql/use.html
"""
type = "use_statement"
match_grammar = StartsWith("USE")
parse_grammar = Sequence(
"USE",
OneOf("ROLE", "WAREHOUSE", "DATABASE", "SCHEMA", optional=True),
Ref("ObjectReferenceSegment"),
)
class ClusterBySegment(BaseSegment):
"""CLUSTER BY clustering_column_list."""
type = "cluster_by_segment"
match_grammar = StartsWith(
"CLUSTER",
terminator=OneOf("OPTIONS", "AS", Ref("DelimiterSegment")),
enforce_whitespace_preceding_terminator=True,
)
parse_grammar = Sequence(
"CLUSTER",
"BY",
Delimited(Ref("ExpressionSegment")),
)
class InsertStatementSegment(BaseSegment):
"""A `INSERT` statement.
N.B. not a complete implementation.
"""
type = "insert_statement"
match_grammar = StartsWith("INSERT")
parse_grammar = Sequence(
"INSERT",
Ref.keyword("INTO", optional=True),
Ref("TableReferenceSegment"),
Ref("BracketedColumnReferenceListGrammar", optional=True),
Ref("SelectableGrammar"),
)
class QualifyClauseSegment(BaseSegment):
"""A `QUALIFY` clause like in `SELECT`."""
type = "qualify_clause"
match_grammar = StartsWith(
"QUALIFY",
terminator=OneOf("ORDER", "LIMIT", "QUALIFY", "WINDOW"),
enforce_whitespace_preceding_terminator=True,
)
parse_grammar = Sequence(
"QUALIFY",
Indent,
OptionallyBracketed(Ref("ExpressionSegment")),
Dedent,
)
class InsertStatementSegment(BaseSegment):
"""An `INSERT` statement.
Full Apache Hive `INSERT` reference here:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML
"""
type = "insert_statement"
match_grammar = StartsWith("INSERT")
parse_grammar = Sequence(
"INSERT",
OneOf(
Sequence(
"OVERWRITE",
OneOf(
Sequence(
"TABLE",
Ref("TableReferenceSegment"),
Ref("PartitionSpecGrammar", optional=True),
Ref("IfNotExistsGrammar", optional=True),
Ref("SelectableGrammar"),
),
Sequence(
Sequence("LOCAL", optional=True),
"DIRECTORY",
Ref("QuotedLiteralSegment"),
Ref("RowFormatClauseSegment", optional=True),
Ref("StoredAsGrammar", optional=True),
Ref("SelectableGrammar"),
),
),
),
Sequence(
"INTO",
"TABLE",
Ref("TableReferenceSegment"),
Ref("PartitionSpecGrammar", optional=True),
OneOf(
Ref("SelectableGrammar"),
Ref("ValuesClauseSegment"),
),
),
),
)
class SelectClauseSegment(ansi.SelectClauseSegment):
"""A group of elements in a select target statement.
Remove OVERLAPS as a terminator as this can be part of SelectClauseModifierSegment
"""
match_grammar = StartsWith(
Sequence(
OneOf("SELECT", "SEL"), Ref("WildcardExpressionSegment", optional=True)
),
terminator=OneOf(
"FROM",
"WHERE",
Sequence("ORDER", "BY"),
"LIMIT",
Ref("SetOperatorSegment"),
),
enforce_whitespace_preceding_terminator=True,
)
parse_grammar = ansi.SelectClauseSegment.parse_grammar
class DeclareStatementSegment(BaseSegment):
"""Declaration of a variable.
https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting#declare
"""
type = "declare_segment"
match_grammar = StartsWith("DECLARE")
parse_grammar = Sequence(
"DECLARE",
Delimited(Ref("NakedIdentifierSegment")),
OneOf(
Ref("DatatypeSegment"),
Ref("DefaultDeclareOptionsGrammar"),
Sequence(
Ref("DatatypeSegment"),
Ref("DefaultDeclareOptionsGrammar"),
),
),
)
class CommentStatementSegment(BaseSegment):
"""A `Comment` statement.
COMMENT [text]
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4009.htm
"""
type = "comment_statement"
match_grammar = StartsWith(Sequence("COMMENT", "ON"))
parse_grammar = Sequence(
"COMMENT",
"ON",
Sequence(
OneOf(
Sequence(
"TABLE",
Ref("TableReferenceSegment"),
),
Sequence(
"COLUMN",
Ref("ColumnReferenceSegment"),
),
Sequence(
"OPERATOR",
Ref("ObjectReferenceSegment"),
),
Sequence(
"INDEXTYPE",
Ref("IndexTypeReferenceSegment"),
),
Sequence(
"MATERIALIZED",
"VIEW",
Ref("TableReferenceSegment"),
),
),
Sequence("IS", OneOf(Ref("QuotedLiteralSegment"), "NULL")),
),
)
class CreateProcedureStatementSegment(BaseSegment):
"""A `CREATE OR ALTER PROCEDURE` statement.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15
"""
type = "create_procedure_statement"
match_grammar = StartsWith(
Sequence("CREATE", Sequence("OR", "ALTER", optional=True),
OneOf("PROCEDURE", "PROC")))
parse_grammar = Sequence(
"CREATE",
Sequence("OR", "ALTER", optional=True),
OneOf("PROCEDURE", "PROC"),
Ref("ObjectReferenceSegment"),
Ref("FunctionParameterListGrammar", optional=True),
"AS",
# Pending to add BEGIN END optional
Ref("ProcedureDefinitionGrammar"),
)
class CreateScriptingLuaScriptStatementSegment(BaseSegment):
"""`CREATE SCRIPT` statement to create a Lua scripting script.
https://docs.exasol.com/sql/create_script.htm
"""
type = "create_scripting_lua_script"
is_ddl = True
is_dml = False
is_dql = False
is_dcl = False
match_grammar = StartsWith(
Sequence(
"CREATE",
Ref("OrReplaceGrammar", optional=True),
Ref.keyword("LUA", optional=True),
"SCRIPT",
))
parse_grammar = Sequence(
"CREATE",
Ref("OrReplaceGrammar", optional=True),
Ref.keyword("LUA", optional=True),
"SCRIPT",
Ref("ScriptReferenceSegment"),
Bracketed(
Delimited(
Sequence(Ref.keyword("ARRAY", optional=True),
Ref("SingleIdentifierGrammar")),
optional=True,
),
optional=True,
),
Sequence(Ref.keyword("RETURNS"),
OneOf("TABLE", "ROWCOUNT"),
optional=True),
"AS",
Ref("ScriptContentSegment"),
)
class PivotUnpivotStatementSegment(BaseSegment):
"""Declaration of a variable.
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15
"""
type = "pivotunpivot_segment"
match_grammar = StartsWith(
OneOf("PIVOT", "UNPIVOT"),
terminator=Ref("FromClauseTerminatorGrammar"),
enforce_whitespace_preceding_terminator=True,
)
parse_grammar = Sequence(
OneOf(
Sequence(
"PIVOT",
OptionallyBracketed(
Sequence(
OptionallyBracketed(Ref("FunctionSegment")),
"FOR",
Ref("ColumnReferenceSegment"),
"IN",
Bracketed(Delimited(Ref("ColumnReferenceSegment"))),
)),
),
Sequence(
"UNPIVOT",
OptionallyBracketed(
Sequence(
OptionallyBracketed(Ref("ColumnReferenceSegment")),
"FOR",
Ref("ColumnReferenceSegment"),
"IN",
Bracketed(Delimited(Ref("ColumnReferenceSegment"))),
)),
),
),
"AS",
Ref("TableReferenceSegment"),
)
class DeclareStatementSegment(BaseSegment):
"""Declaration of a variable.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql?view=sql-server-ver15
"""
type = "declare_segment"
match_grammar = StartsWith("DECLARE")
parse_grammar = Sequence(
"DECLARE",
Delimited(Ref("ParameterNameSegment")),
Ref("DatatypeSegment"),
Sequence(
Ref("EqualsSegment"),
OneOf(
Ref("LiteralGrammar"),
Bracketed(Ref("SelectStatementSegment")),
Ref("BareFunctionSegment"),
Ref("FunctionSegment"),
),
optional=True,
),
)
class DeclareStatementSegment(BaseSegment):
"""Declaration of a variable.
https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting#declare
"""
type = "declare_segment"
match_grammar = StartsWith("DECLARE")
parse_grammar = Sequence(
"DECLARE",
Delimited(Ref("NakedIdentifierSegment")),
Ref("DatatypeIdentifierSegment"),
Sequence(
"DEFAULT",
OneOf(
Ref("LiteralGrammar"),
Bracketed(Ref("SelectStatementSegment")),
Ref("BareFunctionSegment"),
Ref("FunctionSegment"),
),
optional=True,
),
)
class BteqStatementSegment(BaseSegment):
"""Bteq statements start with a dot, followed by a Keyword.
Non exhaustive and maybe catching too many statements?
# BTEQ commands
.if errorcode > 0 then .quit 2
.IF ACTIVITYCOUNT = 0 THEN .QUIT
"""
type = "bteq_statement"
match_grammar = StartsWith(Ref("DotSegment"))
parse_grammar = Sequence(
Ref("DotSegment"),
Ref("BteqKeyWordSegment"),
AnyNumberOf(
Ref("BteqKeyWordSegment"),
# if ... then: the ...
Sequence(Ref("ComparisonOperatorGrammar"),
Ref("LiteralGrammar"),
optional=True),
optional=True,
),
)
class ClusterByClauseSegment(ansi.OrderByClauseSegment):
"""A `CLUSTER BY` clause like in `SELECT`."""
type = "clusterby_clause"
match_grammar: Matchable = StartsWith(
Sequence("CLUSTER", "BY"),
terminator=ansi.OrderByClauseSegment.match_grammar.terminator, # type: ignore
)
parse_grammar: Optional[Matchable] = Sequence(
"CLUSTER",
"BY",
Indent,
Delimited(
Sequence(
OneOf(
Ref("ColumnReferenceSegment"),
Ref("NumericLiteralSegment"),
Ref("ExpressionSegment"),
),
),
terminator=OneOf(Ref.keyword("LIMIT"), Ref("FrameClauseUnitGrammar")),
),
Dedent,
)
class CreateTableStatementSegment(BaseSegment):
"""A `CREATE TABLE` statement.
Full Apache Hive `CREATE TABLE` reference here:
https://cwiki.apache.org/confluence/display/hive/languagemanual+ddl#LanguageManualDDL-CreateTable
"""
type = "create_table_statement"
match_grammar = StartsWith(
Sequence(
"CREATE",
Ref.keyword("TEMPORARY", optional=True),
Ref.keyword("EXTERNAL", optional=True),
"TABLE",
))
parse_grammar = Sequence(
"CREATE",
Ref.keyword("TEMPORARY", optional=True),
Ref.keyword("EXTERNAL", optional=True),
"TABLE",
Ref("IfNotExistsGrammar", optional=True),
Ref("TableReferenceSegment"),
OneOf(
# Columns and comment syntax:
Sequence(
Bracketed(
Delimited(
OneOf(
# TODO: support all constraints
Ref("TableConstraintSegment", optional=True),
Sequence(
Ref("ColumnDefinitionSegment"),
Ref("CommentGrammar", optional=True),
),
),
bracket_pairs_set="angle_bracket_pairs",
),
optional=True,
),
Ref("CommentGrammar", optional=True),
# `STORED AS` can be called before or after the additional table
# properties below
Ref("StoredAsGrammar", optional=True),
Sequence(
"PARTITIONED",
"BY",
Bracketed(
Delimited(
Sequence(
Ref("ColumnDefinitionSegment"),
Ref("CommentGrammar", optional=True),
), ), ),
optional=True,
),
Sequence(
"CLUSTERED",
"BY",
Ref("BracketedColumnReferenceListGrammar"),
Sequence(
"SORTED",
"BY",
Bracketed(
Delimited(
Sequence(
Ref("ColumnReferenceSegment"),
OneOf("ASC", "DESC", optional=True),
))),
optional=True,
),
"INTO",
Ref("NumericLiteralSegment"),
"BUCKETS",
optional=True,
),
# Second call of `STORED AS` to match when appears after
Ref("StoredAsGrammar", optional=True),
Ref("SkewedByClauseSegment", optional=True),
Ref("StorageFormatGrammar", optional=True),
Ref("LocationGrammar", optional=True),
Ref("TablePropertiesGrammar", optional=True),
Ref("CommentGrammar", optional=True),
Sequence(
"AS",
OptionallyBracketed(Ref("SelectableGrammar")),
optional=True,
),
),
# Create like syntax
Sequence(
"LIKE",
Ref("TableReferenceSegment"),
Ref("LocationGrammar", optional=True),
Ref("TablePropertiesGrammar", optional=True),
),
),
)
