Class structure

model.AP_Types List of all possible antipatterns
    Enumerals:
  • NonAnsiJoin
  • CountDistinct
  • CountInOuterJoin
  • ScalarQueryInSelect
  • SelectStar
  • DistinctUsage
  • FunctionInWhereClause
  • NullWithArithmeticAndString
  • FilteringAttributesFromTheNonPreservedSideOfAnOuterJoin
  • SearchingForNull
  • ImplicitColumnUsage
  • ImplicitSelfJoinInSubQuery
  • InnerJoinAfterOuterJoin
  • ImplicitCrossJoin
  • AnonymousColumn
  • NullAndNotEqualOperator
  • OrVersusInAndNotIn
  • OrdinalNumberUsage
  • OverloadedJoins
  • UnusedCTE
  • SelfJoin
  • UnionForMultipleFilters
  • NaturalJoin
  • RegexUsedInsteadOfLike
  • UnionUsage
  • WhereInsteadOfHaving
  • AnonymousSubQuery
  • FunctionInJoin
  • ParenthesisWithAndOr
  • DelayOrAvoidOrderBy
  • NotInWithoutNotNullCheckInSubQuery
model.Agg Is the Group by clause which contains a list of attributes and a having clause
Ex:
`SELECT product_ID, SUM(retail_price * quantity) AS gross_revenue
FROM sales
GROUP BY product_ID having product_id = 10;`
`GROUP BY product_ID having product_id = 10;` -> part of Agg
`product_ID` -> Expr in Agg
`having product_id = 10` -> Filter in Agg
    Attributes:
  • filter_: This is the HAVING clause in Group By
  • pos: Specifies the start position and length of the object in the query
  • exprs: Contains Attr or other expressions which needs to be aggregated
  • type_: Type of aggregation. Can be aggreg/aggwnd
model.AntiPattern AntiPattern contains a list of ids representing the start and end positions of where the antipattern has been detected
It also contains the type of antipattern which has been detected as well as few other flags
    Attributes:
  • name: Title of the antipattern
  • severity: Warning/Caution/Notice
  • readability: flag to indicate a readability antipattern
  • correctness: flag to indicate a correctness antipattern
  • performance: flag to indicate a performance antipattern
  • pos: Specifies the start position and length of the antipattern in the query
  • link: Link to the AntiPattern detailed documentation
  • type_: Specifies the antipattern code (Ex. AP_01, AP_02 ..)
model.AntiPatterns
    Attributes:
  • antiPattern: the list of AntiPattern objects found
model.Asterisk Contains a list of Attr which are present in the relevant tables, datasets, tables, databases, and schema
Ex: `select * from tab where id = 10`
Then the Attr * will contain col.id as an Attr within it
    Attributes:
  • refsch: Contains the name of the schema which has been referenced
  • fullref: Contains the complete location of the attr which has been referenced
  • refdb: Contains the name of the database which has been referenced
  • pos: Specifies the start position and length of the object in the query
  • refds: Contains the name of the dataset which has been referenced
  • sref: Contains the name of reference to the source attribute which has been aliased
  • alias: The display name
  • exprs: Contains the list of Attributes or Expressions
model.Attr Attributes are the smallest unit in the query. Can contain reference to other attributes(in the case of alias), tables, datasets, tables, databases, and schema.
Ex:
select a, b, 1, sum(10) from tab
`a`, `b` - are the Attr
`sum(10)` -> will be Func
`1` -> Const
    Attributes:
  • refsch: Contains the name of the schema which has been referenced
  • refvar: Contains the name of variable being referenced
  • fullref: Contains the complete location of the attr which has been referenced
  • refdb: Contains the name of the database which has been referenced
  • pos: Specifies the start position and length of the attribute in the query
  • refds: Contains the name of the dataset which has been referenced
  • sref: Contains the name of reference to the source attribute which has been aliased
  • alias: The display name
  • refoutidx: Numeric value which is used to refer to the Attr based on its position in select clause
  • direction: ASC/DESC. Specifies the sorting order for an Attr
  • attref: Origin pos of the complex expression being references
  • oref: Reference to the parent DBO's oid
model.BaseExpr
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • alias: The display name
  • direction: ASC/DESC
model.BaseExprCollectionHolder
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • exprs
model.BaseExprHolder
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • expr
model.BetweenType Can be ROWS or RANGE BETWEEN
    Enumerals:
  • rows
  • range
model.Case Contains information regarding the CASE ... WHEN clause. It includes attribute/expression, when clause, then clause, and else clause Ex :
`SELECT CASE
WHEN column1=1 THEN 'one'
WHEN column1=2 THEN 'two'
ELSE 'other'
END AS result
FROM (values(1),(2),(3)) v;`
`WHEN column1=1 THEN 'one'
WHEN column1=2 THEN 'two'` -> When part of Case
`column1=1` and `column1=2` -> condition part of When
`THEN 'one'` and `THEN 'two'` -> Then part of When
`ELSE 'other'` -> Else part of Case
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • else: The expression that is returned when none of the WHEN conditions are true
  • alias: The display name
  • expr: Contains information about the conditions that are checked for the then clause(present in When) to take effect
  • when: Contains information about the conditions that are checked for the then clause(present in When) to take effect
  • direction: ASC/DESC
model.Cast Converts a value of one data type into another data type
Contains names of the data and the type to which it needs to be converted to
Ex:
SELECT CAST('1.2345' AS DECIMAL(15,2));
`'1.2345'` -> is the Expr in Cast to be converted
`DECIMAL(15,2)` -> Specifies the dataType
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • dataType: The datatype being cast to
  • alias: The display name
  • expr
  • direction: ASC/DESC
model.ColumnDef ColumnDefinition contains the name of the column, data type, precision, and scale
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • precision: Specifies the precision of the data
  • name: Specifies the name of the column
  • scale: Specifies the scale of the data
  • type_: Specifies the data type of the column
model.Const Comprised of a constant numeric/string value
Ex:
select a, b, 1, sum(10) from tab
`a`, `b` - are the Attr
`sum(10)` -> will be Func
`1` -> Const
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • alias: The display name
  • value: A constant numeric/string value
  • direction: ASC/DESC
model.CoordinateBlock
    Attributes:
  • pos: Specifies the start position and length of the object in the query
model.CopyStatement Copy contains details about data that is to be loaded from staged files to an existing table/location
Ex.1:
`COPY INTO @my_stage FROM (SELECT * FROM orderstiny LIMIT 5) VALIDATION_MODE='RETURN_ROWS' header=TRUE;`
`VALIDATION_MODE='RETURN_ROWS'` -> validation
`@my_stage` -> into
`SELECT * FROM orderstiny LIMIT 5` -> fromQuery
`header=TRUE` -> header
Ex.2:
`COPY INTO mytable FILE_FORMAT = (TYPE = 'CSV') PATTERN='.*/.*/.*[.]csv[.]gz';`
`@my_stage` -> into
`FILE_FORMAT = (TYPE = 'CSV')` -> fileFormat
`PATTERN='.*/.*/.*[.]csv[.]gz';` -> pattern
    Attributes:
  • antiPatterns: list of antipatterns found for the input query (if any)
  • rawInput: Contains the user input
  • type_
  • ds: A DS used in a COPY if any
  • pos: Specifies the start position and length of the object in the query
  • dialExt: Dictionary with the db vendor specific modifiers (e.g. Snowflake copy and fileFormat options, patterns, etc.)
model.CreateStageStatement Create stage contains info on creating a new named internal or external stage to use for loading data from files
Ex.1:
`CREATE STAGE my_int_stage ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE') COPY_OPTIONS = (ON_ERROR='skip_file');`
`ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')` -> location (It is the internal stage parameter in this case)
`COPY_OPTIONS = (ON_ERROR='skip_file')` -> copyOptions
`my_int_stage` -> stageName
Ex.2:
`CREATE STAGE mystage URL='s3://load/files/'
STORAGE_INTEGRATION = my_storage_int
ENCRYPTION=(TYPE='AZURE_CSE' MASTER_KEY = 'kPxX0jzYfIamtnJEUTHwq80Au6NbSgPH5r4BDDwOaO8=')
DIRECTORY = ( ENABLE = true AUTO_REFRESH = true );`
`URL='s3://load/files/'
STORAGE_INTEGRATION = my_storage_int
ENCRYPTION=(TYPE='AZURE_CSE' MASTER_KEY = 'kPxX0jzYfIamtnJEUTHwq80Au6NbSgPH5r4BDDwOaO8=')` -> location
`DIRECTORY = ( ENABLE = true AUTO_REFRESH = true );` -> directoryParam
    Attributes:
      Attributes:
    • antiPatterns: list of antipatterns found for the input query (if any)
    • rawInput: Contains the user input
    • type_
    • ds
    • pos: Specifies the start position and length of the object in the query
    • dialExt: Dictionary with the db vendor specific modifiers (e.g. the Snowflake directoryParams, fileFormat, tags, etc.)
model.CreateTableStatement Creates a new table in the current/specified schema
Ex.1:
`CREATE OR REPLACE TABLE tab AS SELECT col1, col2 FROM mytable;`
    Attributes:
  • antiPatterns: list of antipatterns found for the input query (if any)
  • rawInput: The input query submitted by the user
  • type_
  • subType: Indicates that the table is an external table etc.
  • ds: Contains the table's data
  • pos: Specifies the start position and length of the object in the query
  • dialExt: Dictionary with the db vendor specific modifiers
model.CreateViewStatement Creates a new view in the current/specified schema, based on a query of one or more existing tables
Ex.1:
`CREATE OR REPLACE SECURE VIEW myview AS SELECT col1, col2 FROM mytable;`
`REPLACE` -> replace (will be set to TRUE)
`myview` -> dataset
`SELECT col1, col2 FROM mytable` -> query
notExists will be set to FALSE
Ex.2:`CREATE VIEW employee_hierarchy (title, employee_ID, manager_ID) as SELECT title,
employee_ID, manager_ID FROM employees`
`SELECT title,
employee_ID, manager_ID FROM employees` -> query
`title, employee_ID, manager_ID` -> columns
    Attributes:
  • antiPatterns: list of antipatterns found for the input query (if any)
  • rawInput: The input query submitted by the user
  • type_
  • subType: Indicates that the view is a materialized view, etc.
  • ds: Contains the view's data
  • pos: Specifies the start position and length of the object in the query
  • dialExt: Dictionary with the db vendor specific modifiers
model.Current A system command, which can either be current_timestamp, or current_date
Ex: `Select current_date`
current_date -> type
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • alias: The display name
  • type_: Can be either CURRENT_TIMESTAMP or CURRENT_DATE
  • direction: ASC/DESC
model.DBO Contains information about individual database object and its children
    Attributes:
  • dbo: List of all children object of the current DBO
  • oid: The object ID for the current database Object
  • name: The display name
  • type_: Can be either DB, SCHEMA, COLUMN, STAGE, VIEW, or TABLE
  • dtype: Datatype of the DBO column (applies to COLUMNS only)
  • constraint: Name of the primary key or foreign key where the DBO column is used (applies to COLUMNS only)
  • index: Name of the index where the DBO column is used (applies to COLUMNS only)
model.DBOHier Contains all database objects that are detected in the query
    Attributes:
  • dbo: List of all object present in the query
model.DBOType Type of Database Object collected
    Enumerals:
  • DB
  • SCHEMA
  • TABLE
  • STAGE
  • COLUMN
  • VIEW
model.DeleteStatement Delete removes rows from a table
You can use a WHERE clause to specify which rows should be removed
If you need to use a subquery(s) or additional table(s) to identify the rows to be removed, specify the subquery(s) or table(s) in a USING clause.
Ex.1:
`DELETE FROM tab1 USING tab2 WHERE tab1.k = tab2.k`
`tab1` -> target
`tab2` -> ds in "in"
`tab1.k = tab2.k` -> filter
    Attributes:
  • antiPatterns: list of antipatterns found for the input query (if any)
  • rawInput: The input query submitted by the user
  • type_
  • ds: Ds to target
  • pos: Specifies the start position and length of the object in the query
model.Direction Sorting Direction
    Enumerals:
  • asc
  • desc
model.Ds Dataset - Acts as a wrapper and contains all the data of a full or partial query.
Contains select, from, joins, where, groupBy, having, qualify, sorting, and paging details.
    Attributes:
  • refsch: Contains the name of the schema which has been referenced
  • fullref: Contains the complete location of the Ds which has been referenced
  • refdb: Contains the name of the database which has been referenced
  • in_: Is the FROM tag which contains all the inputs for the query
  • matchRecognize: MATCH_RECOGNIZE accepts a set of rows (from a table, view, subquery, or other source) as input, and returns all matches for a given row pattern within this set
  • setOp: Contains information the type and the Dataset(DS) with which the set operation should occur
  • type_: Contains details about the type of dataset. Can be CTE, RECURSIVE CTE, TABLE, VIEW, ROOT, ANONYMOUS or a REFERENCE
  • out: Is the select tag which contains a list of attributes and expressions
  • modifiers: List of Ds modifiers: - Filters that contain information regarding the where, having, and qualify clauses. Filter contains Expr (usually Op) which can be used to represent the above clauses; - Order By - Comprised of the attribute or constant with which the output must be sorted in either direction(ascending or descending); - Group by clause which contains a list of attributes and a having clause; - Information about paging. Used to only display a limited number of results
  • tableSample: Table Sample or Sample is used to return a subset of rows sampled randomly from the specified table
  • pos: Specifies the start position and length of the object in the query
  • refds: Contains the name of the dataset which has been referenced
  • name: Contains the name of the dataset which has been referenced.
  • action: Info on the type of statement the Ds is used
  • alias: The display name
  • subType: Contains details about the subtype of dataset. Can be PSEUDO, RECURSIVE, INLINE, PHYSICAL, FUNCTION
  • sref: Contains a list of columns (Attr) which refer to this dataset
  • oref: Reference to the parent DBO's oid
  • direction: ASC/DESC
model.DsAction
    Enumerals:
  • delete
  • insert
  • update
  • create_view
  • create_table
  • create_stage
  • copy
  • add_fk
  • alter_fk
  • rename_fk
  • add_pk
  • alter_pk
  • rename_pk
  • drop_constraint
  • rename_table
  • add_index
  • drop_index
  • alter_index
  • rename_index
  • add_column
  • drop_column
  • alter_column
  • rename_column
model.DsSubType Contains details about the subtype of dataset. Can be PSEUDO, RECURSIVE, INLINE, PHYSICAL, FUNCTION
    Enumerals:
  • pseudo
  • recursive
  • inline
  • physical
  • function
model.DsType Contains details about the type of dataset. Can be CTE, RECURSIVE CTE, TABLE, VIEW, ROOT, ANONYMOUS or a REFERENCE
    Enumerals:
  • cte
  • table
  • reference
  • parenthesis
  • view
  • root
  • anonymous
model.Edge The LATERAL VIEW clause is used in conjunction with generator functions such as EXPLODE, which will generate a virtual table containing one or more rows.
LATERAL VIEW will apply the rows to each original output row.
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • generator: Specifies a generator function (EXPLODE, INLINE, etc.)
  • exprs: Expressions to which the generator function is applied to
  • alias: The display name
  • type_: Can either be null or OUTER. If OUTER is specified, returns null if an input array/map is empty or null
  • columnAlias: Lists the column aliases of generator function, which may be used in output rows. If there are multiple aliases then the output will have multiple columns
  • direction: ASC/DESC
model.Else Contains information regarding the CASE ... WHEN clause. It includes attribute/expression, when clause, then clause, and else clause
Ex :
`SELECT CASE
WHEN column1=1 THEN 'one'
WHEN column1=2 THEN 'two'
ELSE 'other'
END AS result
FROM (values(1),(2),(3)) v;`
`WHEN column1=1 THEN 'one'
WHEN column1=2 THEN 'two'` -> When part of Case
`column1=1` and `column1=2` -> condition part of When
`THEN 'one'` and `THEN 'two'` -> Then part of When
`ELSE 'other'` -> Else part of Case
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • expr: The expression that is returned when none of the WHEN conditions are true
model.Error Acts as a wrapper for the error message of the statement
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • message: Contains the actual error message
model.Expr Expressions can be comprised of attr, another expression, function, window function, operation, constant, case, asterisk, current, pivot/unpivot, multi-value, inline table, merge, position, querying stage, structured reference, table function, table sample, lateral view, window frame, insert, join, match recoginize, update, or dataset.
model.ExprCollectionHolder
model.ExprExprCollectionHolder
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • alias: The display name
  • exprs
  • direction: ASC/DESC
model.ExprExprHolder
    Attributes:
  • alias: The display name
  • expr
  • direction: ASC/DESC
model.ExprHolder
model.Filter Filter contains information regarding the where, having, and qualify clauses
Filter contains Expr (usually Op) which can be used to represent the above clauses
Ex.-
`select * from tab where a = 10`
`where a = 10` -> will be in the Filter in the form of an Op
    Attributes:
  • op: The constraint which is specified in the where, having, or qualify clause
  • type_: Filter classifier to distinguish between Having(filtagg)/Qualify(filtvert)/Where(filtreg)
  • pos: Specifies the start position and length of the object in the query
model.FilterType
    Enumerals:
  • filtagg
  • filtvert
  • filtreg
model.Frame Between - Used in logical operations and in window functions
Sets upper and lower limits with positional values in window functions
Performs range operations in Logical Operations
Ex:
`select branch_id, 100 * RATIO_TO_REPORT(net_profit) OVER (PARTITION BY branch_id order by branch_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from sales as s1 `
`RATIO_TO_REPORT` -> name
`net_profit` -> Expr in the Wfunc
`branch_id` -> partition
`branch_id` -> sort
`ROWS` -> type in frame
`UNBOUNDED` -> lowerLimit in Frame
`PRECEDING` -> lowerPosition in Frame
`CURRENT_ROW` -> upperPosition in Frame
    Attributes:
  • upperPosition: Specifies the upper position. Can be (FOLLOWING, PRECEDING, or CURRENT_ROW)
  • pos: Specifies the start position and length of the object in the query
  • lowerPosition: Specifies the lower position. Can be (FOLLOWING, PRECEDING, or CURRENT_ROW)
  • exprs: The expression (Const or Attr or Op) which is used in the Frame
  • alias: The display name
  • upperLimit: Specifies the upper limit value (can be constants or CURRENT ROW)
  • lowerLimit: Specifies the lower limit value (can be constants or UNBOUNDED)
  • type_: Can be ROWS or RANGE BETWEEN
  • direction: ASC/DESC
model.Func Function: Performs a task based on the name of the function and accepts a number of arguments.
Ex.1:
`select sum(col1) from tab1`
`sum` -> name
`col1` -> Expr arguments for the function
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • name: Contains the name of the function
  • withinGroup: Specifies order by part of the function in the case of "listagg" or similar
  • exprs: The expression arguments for the function
  • alias: The display name
  • type_: Can be any of AGG/WINDOW/SCALAR
  • subType: Can be any of AGG/WINDOW/SCALAR
  • quantifier: Specifies Distinct/All qualifier
  • frame: Upper and lower limits with positional values in window functions
  • direction: ASC/DESC
model.FuncType Function classifier
    Enumerals:
  • fagg
  • fwnd
  • fscalar
  • ftable
  • fsystem
model.FuncSubType
    Enumerals:
  • bitwise
  • conditional
  • context
  • conversion
  • data_generation
  • datetime
  • encryption
  • file
  • geospatial
  • hash
  • metadata
  • numeric
  • regexp
  • semi_structured_data
  • string_binary
model.In Input - Is the FROM tag which contains all the inputs for the query (Joins, dataset, operations on dataset)
Ex.1:
`select * from tab`
`tab` -> is the dataset present in In
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • exprs: Can be any data source. Ex :
    Tables, Joins, Set Operations
model.InlineTable Inline table - An inline table is a temporary table created using a VALUES clause.
Ex :
`SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three'));`
-> VALUES (1, 'one'), (2, 'two'), (3, 'three') is the inline table
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • alias: The display name
  • exprs: Contains a list of expressions
  • direction: ASC/DESC
model.InsertStatement Insert tag contains information about insert statement
Inserts one or more rows into a table. The values inserted into each column in the table can be explicitly-specified or the results of a query
    Attributes:
  • antiPatterns: list of antipatterns found for the input query (if any)
  • rawInput: Contains the user input
  • type_
  • subType
  • ds
  • pos: Specifies the start position and length of the object in the query
  • dialExt: Dictionary with the db vendor specific modifiers
model.Join Join - Contains the data to be joined, the type of join and the condition on which it should do so
Ex.:
`select * from tab1 inner join tab2 on tab1.id = tab2.id`
`inner` -> type
`join tab2 on tab1.id = tab2.id` -> will be represented in Join
`tab1.id = tab2.id` -> is the join condition
    Attributes:
  • op
  • definedAs: Indicate if the join is explicitly defined and if it is NATURAL
  • pos: Specifies the start position and length of the object in the query
  • alias: The display name
  • subType: Specifies the subType of the join
  • type_: Specifies the type of the join
  • ds: Contains the data sources which are used in join
  • direction: ASC/DESC
model.JoinSubType
    Enumerals:
  • left
  • right
  • full
model.JoinType
    Attributes:
  • outer
  • inner
  • cross
  • nonansi
model.MatchRecognize MATCH_RECOGNIZE accepts a set of rows (from a table, view, subquery, or other source) as input, and returns all matches for a given row pattern within this set
• orderBy - This is the order in which the individual rows of each partition are passed to the MATCH_RECOGNIZE operator
• partitionBy - is used to specify an expression that groups rows that are related to each other
• measures - are optional additional columns that are added to the output of the MATCH_RECOGNIZE operator
• rowCondition - specifies what should occur for the row action to take place
• rowAction - specifies what should happen when the rowCondition is met
• pattern - stores the pattern that is used to match a valid sequence of rows
• define - is used to specify symbols for MATCH_RECOGNIZE operator
Ex:
`SELECT * FROM stock_price_history
MATCH_RECOGNIZE(
PARTITION BY company
ORDER BY price_date
MEASURES
MATCH_NUMBER() AS match_number,
FIRST(price_date) AS start_date,
LAST(price_date) AS end_date,
COUNT(*) AS rows_in_sequence,
COUNT(row_with_price_decrease.*) AS num_decreases,
COUNT(row_with_price_increase.*) AS num_increases
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST row_with_price_increase
PATTERN(row_before_decrease row_with_price_decrease+ row_with_price_increase+)
DEFINE
row_with_price_decrease AS price > LAG(price),
row_with_price_increase AS price > LAG(price)
)
ORDER BY company, match_number;`
`company` -> partitionBy
`price_date` -> orderBy
`MATCH_NUMBER() AS match_number,
FIRST(price_date) AS start_date,
LAST(price_date) AS end_date,
COUNT(*) AS rows_in_sequence,
COUNT(row_with_price_decrease.*) AS num_decreases,
COUNT(row_with_price_increase.*) AS num_increases`-> measure
`ONE ROW PER MATCH` -> rowCondition
`AFTER MATCH SKIP TO LAST row_with_price_increase` -> rowAction
`row_with_price_increase+` -> pattern
`row_with_price_decrease AS price > LAG(price),
row_with_price_increase AS price > LAG(price)` -> define
    Attributes:
  • partitionBy: Specifies an expression that groups rows that are related to each other
  • measures: Optional additional columns that are added to the output of the MATCH_RECOGNIZE operator
  • pos: Specifies the start position and length of the object in the query
  • pattern: Stores the pattern that is used to match a valid sequence of rows
  • define: Specify symbols for MATCH_RECOGNIZE operator
  • orderBy: This is the order in which the individual rows of each partition are passed to the MATCH_RECOGNIZE operator
  • rowMatchAction: Specifies what should happen when the rowCondition is met
  • rowMatchCondition: Specifies what should occur for the row action to take place
  • alias: The display name
  • direction: ASC/DESC
model.MergeStatement Inserts, updates, and/or deletes values in a table based on values in a second table or a subquery. This can be useful if the second table is a change log that contains new rows (to be inserted), modified rows (to be updated), and/or marked rows (to be deleted) in the target table.
• target - specifies the target dataset to which the source is being merged into
• ctes - CTEs that are used in the USING clause of the Merge statement
• source - is the primary dataset from where the data is being pulled from
• conditions - contains constraints which specify the merge to happen in a certain way
• actions - specifies actions to perform if data is matched or if it isn't matched
Ex.1:
`MERGE INTO target_table USING source_table
ON target_table.id = source_table.id
WHEN MATCHED THEN
UPDATE SET target_table.description = source_table.description;`
`source_table` -> source
`target_table` -> target
`ON target_table.id = source_table.id` -> conditions
`WHEN MATCHED THEN
UPDATE SET target_table.description = source_table.description;` -> actions
    Attributes:
  • antiPatterns: list of antipatterns found for the input query (if any)
  • rawInput: Contains the user input
  • type_
  • ds: The Ds to target
  • pos: Specifies the start position and length of the object in the query
model.MultiValue A wrapper tag for comma separated expressions in brackets
Ex:
`select * from tab where a in ('1', '2', '3')`
('1', '2', '3') is the multivalue expression
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • alias: The display name
  • exprs: Contains the list of expressions
  • direction: ASC/DESC
model.Named
model.Op Any form of arithmetic/logical operations are represented by Op
Operations between other operators, functions, attributes, Case/when, constants, datasets, joins, windows, etc. are included
Ex :
select 1+2
`1+2` -> Is the Op
`1`, `2` -> Are the Expr in the Op
`+` -> type
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • exprs: Contains the list of expression on which the operation has been applied
  • alias: The display name
  • type_: Comprises all operations possible in SQL(Arithmetic, Logical, etc)
  • direction: ASC/DESC
model.OpType Comprises all operations possible in SQL(Arithmetic, Logical, etc)
    Enumerals:
  • DEFAULT
  • CONCAT
  • CONCAT_PIPE
  • PLUS
  • MINUS
  • MUL
  • DIV_LIT
  • EQ
  • NEQ
  • AND
  • OR
  • NOT
  • LIKE
  • RLIKE
  • REGEX
  • NOT_LIKE
  • IS
  • IS_DISTINCT
  • LIKE_ALL
  • LIKE_SOME
  • LIKE_ANY
  • IS_NOT_DISTINCT
  • IS_DISTINCT_FROM
  • IS_NOT_DISTINCT_FROM
  • IN
  • ANY
  • ALL
  • MOD
  • LT
  • GT
  • GTE
  • LTE
  • IS_NOT
  • PARENTHESIS
  • NOT_EXISTS
  • DIV
  • MULTI
  • BETWEEN
  • NEQJ
  • EXISTS
  • INTERSECT
  • UNION
  • EXCEPT
  • SETMINUS
  • UNION_ALL
  • UNION_DISTINCT
  • REGEXP
  • ASSIGN
  • MATCH
  • NOT_MATCH
  • COLLATE
  • NOT_LIKE_ALL
  • NOT_LIKE_ANY
  • NOT_LIKE_SOME
  • NOT_IN
  • NOT_RLIKE_ALL
  • NOT_RLIKE_ANY
  • NOT_RLIKE_SOME
  • NOT_REGEXP_ALL
  • NOT_REGEXP_ANY
  • NOT_REGEXP_SOME
  • NOT_REGEXP
  • NOT_RLIKE
  • RLIKE_ANY
  • RLIKE_ALL
  • RLIKE_SOME
  • REGEXP_ANY
  • REGEXP_SOME
  • REGEXP_ALL
  • EQ_ALL
  • EQ_ANY
  • NEQ_ALL
  • NEQ_ANY
  • NEQJ_ALL
  • NEQJ_ANY
  • LT_ALL
  • LT_ANY
  • LTE_ALL
  • LTE_ANY
  • GT_ALL
  • GT_ANY
  • GTE_ALL
  • GTE_ANY
  • NSEQ_ANY
  • NSEQ_ALL
  • NSEQ
  • CASE
  • AMPERSAND
  • SOME
model.Ordered
model.Out Is the select tag which contains a list of attributes and expressions
Ex.1:
`select 1, 2, 3`
`1, 2, 3` -> are the Out Expr
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • exprs: The output can be any expression. Ex - Attr, Func, Op etc
  • type_: Specifies the quantifier for the select. Can be ALL or DISTINCT
  • derived: Specifies if the columns mentioned in Out are not explicitly mentioned, but are rather derived through the dataset's known columns
model.Page Contains information about paging. Used to only display a limited number of results.
Ex. `select * from tab limit 5`
`limit` -> type
`5` -> is the Expr (value) which is used in Limit
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • type_: Specifies the type of the paging (LIMIT, FETCH, TOP)
  • value: Specifies the paging limit expression
model.PageType
    Enumerals:
  • limit
  • fetch
  • top
model.ParSeQL
    Attributes:
  • DBOHier: Hierarchical list of all the Database objects
  • pos: Specifies the start position and length of the object in the query
  • statement: Acts as wrapper for statement. Holds multiple statement within the tag.
  • error: Acts as wrapper for Error. Holds multiple Error within the tag
  • version: Provides version information
  • status: Indicates the status of the API
    • OK : Everything works as expected
    • FATAL_ERROR : Caused due to a bug in the API, invalid json input to the API, some exception being thrown by the API, etc.
    • SYNTAX_ERROR : Errors which are caused due to an invalid/unsupported query being passed to the API.
  • ts: Displays the time when the API call was made
model.ParSeQLStatus One of the possible Response status
    Enumerals:
  • OK
  • FATAL_ERROR
  • SYNTAX_ERROR
model.Position Searches for the n-th(default is first) occurrence of the first argument in the second argument
Ex:
`select position('an', 'banana', 3);`
searches for the third instance of `an` in `banana`
`an` -> subString
`banana` -> str
    Attributes:
  • string: The second argument in which the first argument needs to be searched
  • subString: The first argument which needs to be found
  • pos: Specifies the start position and length of the object in the query
  • alias: The display name
  • direction: ASC/DESC
model.Quantifier
    Enumerals:
  • DISTINCT
  • ALL
model.QueryingStage Query data files located in an internal (i.e. Snowflake) stage or named external (Amazon S3, Google Cloud Storage, or Microsoft Azure) stage
This can be useful for inspecting/viewing the contents of the staged files, particularly before loading or after unloading data
Ex :
`SELECT t.$1, t.$2 FROM @mystage1 (file_format => 'myformat', pattern=>'.*data.*[.]csv.gz') t;`
`@mystage1 (file_format => 'myformat', pattern=>'.*data.*[.]csv.gz') t` -> is the QueryingStage
`@mystage1` -> location
`'myformat'` -> fileFormat
`'.*data.*[.]csv.gz'` -> pattern
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • pattern: Is used to further filter the files in the staged location
  • alias: The display name
  • location: Specifies where the data is being used from
  • fileFormat: Specifies the format of the file
  • direction: ASC/DESC
model.ReferencableExpr
model.Rotate There are two types of Rotate
1. Pivot: Rotates a table by turning the unique values from one column in the input expression into multiple columns and aggregating results where required on any remaining column values
OR
2. Unpivot: Rotates a table by transforming columns into rows
    Attributes:
  • nameColumn: The name to assign to the generated column that will be populated with the names of the columns in the column list in the case of Unpivot
  • pos: Specifies the start position and length of the object in the query
  • valueColumn: The column from the source table or subquery that contains the values from which column names will be generated in the case of Pivot
  • pivotColumn: The column from the source table or subquery that will be aggregated
  • columnList: Contains the column from the source table or subquery that contains the data from which column names will be generated
  • alias: The display name
  • type_: PIVOT/UNPIVOT
  • columnAlias: Contains the name of the columns
  • aggregate: Specifies the aggregation function that is used
  • direction: ASC/DESC
model.Searchable
model.Sort Order By - Comprised of the attribute or constant with which the output must be sorted in either direction(ascending or descending). (Default - ascending)
Ex:
`select * from tab order by col1, col2 desc`
`col1, col2 desc` -> are the Sort Expr
desc is the direction for col2
col1 will have asc as the default direction
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • exprs: Attribute or constant with which the output must be sorted in either direction
model.Statement Acts as wrapper for the SQL statement
Ex:
`create table tab (col1 int, col2 int);
select * from tab;`
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • antiPatterns: list of antipatterns found for the input query (if any)
  • rawInput: Contains the user input
  • ds: Acts as a wrapper and contains all the data of a query. Contains select, from, joins, where, groupBy, having, qualify, sorting, and/or paging details
model.StructRef Structured References that contain the column to which the semi structured data is referring to (JSON, Avro etc)
EX.:
SELECT src:dealership
FROM car_sales
ORDER BY 1;
`src` -> columnRef
`dealership` -> structureRef
    Attributes:
  • refsch: Contains the name of the schema which has been referenced
  • refvar: Contains the name of variable being referenced
  • fullref: Contains the complete location of the attr which has been referenced
  • refdb: Contains the name of the database which has been referenced
  • pos: Specifies the start position and length of the object in the query
  • refds: Contains the name of the dataset which has been referenced
  • refpath: Contains the path of the data being referenced
  • sref: Contains the name of reference to the source attribute which has been aliased
  • alias: The display name
  • refoutidx
  • direction: ASC/DESC
  • attref: Origin pos of the complex expression being references
  • oref: Reference to the parent DBO's oid
model.SubString Creates a substring from the source string
Ex:
`select substr('banana', 1, 3);`
creates a substring of banana from a to n: anan
`banana` -> string
`1` -> position
`3` -> length
    Attributes:
  • string: The source string
  • length: The length of the substring from the starting position
  • position: The starting position for the substring
model.TableFunc Table function - table function returns a set of rows for each input row. The returned set can contain zero, one, or more rows
Ex.1: `select fav.color as favorite_2017, f.*
from fashion f JOIN table(favorite_colors(2017)) fav
where fav.color = f.fashion_color;`
    Attributes:
  • refsch: Contains the name of the schema which has been referenced
  • fullref: Contains the complete location of the Ds which has been referenced
  • refdb: Contains the name of the database which has been referenced
  • in_: Is the FROM tag which contains all the inputs for the query
  • matchRecognize: MATCH_RECOGNIZE accepts a set of rows (from a table, view, subquery, or other source) as input, and returns all matches for a given row pattern within this set
  • setOp: Contains information the type and the Dataset(DS) with which the set operation should occur
  • type_: Contains details about the type of dataset. Can be CTE, RECURSIVE CTE, TABLE, VIEW, ROOT, ANONYMOUS or a REFERENCE
  • out: Is the select tag which contains a list of attributes and expressions
  • modifiers: List of Ds modifiers: - Filters that contain information regarding the where, having, and qualify clauses. Filter contains Expr (usually Op) which can be used to represent the above clauses; - Order By - Comprised of the attribute or constant with which the output must be sorted in either direction(ascending or descending); - Group by clause which contains a list of attributes and a having clause; - Information about paging. Used to only display a limited number of results
  • tableSample: Table Sample or Sample is used to return a subset of rows sampled randomly from the specified table
  • pos: Specifies the start position and length of the object in the query
  • refds: Contains the name of the dataset which has been referenced
  • name: Contains the name of the dataset which has been referenced.
  • action: Info on the type of statement the Ds is used
  • alias: The display name
  • subType: Contains details about the subtype of dataset. Can be PSEUDO, RECURSIVE, INLINE, PHYSICAL, FUNCTION
  • sref: Contains a list of columns (Attr) which refer to this dataset
  • direction: ASC/DESC
  • partition: Contains a list of expressions which are used for partitioning
  • options: Contains the options being applied to the operation in the function
  • tableFuncType: Table func classifier
  • sort: Contains the ORDER BY clause
  • subQuery: Contains a dataset which can be used in a table function
  • frame: Contains the BETWEEN statement
  • oref: Reference to the parent DBO's oid
model.TableSample Table Sample or Sample is used to return a subset of rows sampled randomly from the specified table
    Attributes:
  • sampleMethod: Specifies the type of sampling methodology. Can be either BERNOULLI, ROW, SYSTEM, or BLOCK
  • seedType: Can be either REPEATABLE or SEED
  • seed: Specifies a seed value to make the sampling deterministic
  • pos: Specifies the start position and length of the object in the query
  • probability: Specifies the percentage probability to use for selecting the sample
  • num: Specifies the number of rows to sample from the table
  • alias: The display name
  • sampleType: Is either SAMPLE or TABLESAMPLE
  • direction: ASC/DESC
model.Then Contains information about what to do after the WHEN condition is satisfied
Ex:
`SELECT CASE
WHEN column1=1 THEN 'one'
WHEN column1=2 THEN 'two'
ELSE 'other'
END AS result
FROM (values(1),(2),(3)) v;`
`WHEN column1=1 THEN 'one'
WHEN column1=2 THEN 'two'` -> When part of Case
`column1=1` and `column1=2` -> condition part of When
`THEN 'one'` and `THEN 'two'` -> Then part of When
`ELSE 'other'` -> Else part of Case
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • alias: The display name
  • expr: The expression that is returned if the WHEN condition is true
  • direction: ASC/DESC
model.TreeNode Base type with attributes and functions to traverse the tree (parent/children hierarchy). Most of the objects in SDK inherit from this class, making them traversable
    Attributes:
  • _id: Internal unique identifier
  • _parent: Reference to the parent node in the hierarchy
  • _children: List of descendants TreeNode objects
model.TypeCast
model.AlterTableStatement Modifies the properties, columns, or constraints for an existing table.
    Attributes:
  • antiPatterns: list of antipatterns found for the input query (if any)
  • rawInput: Contains the user input
  • type_
  • ds: The Table/Column/Constraint being altered
  • dialExt: Dictionary with the db vendor specific modifiers
  • pos: Specifies the start position and length of the object in the query
model.UpdateStatement Update uses a dataset to update the target dataset
    Attributes:
  • antiPatterns: list of antipatterns found for the input query (if any)
  • rawInput: Contains the user input
  • type_
  • ds: The Ds being updated
  • pos: Specifies the start position and length of the object in the query
model.When Contains information about the conditions that are checked for the then clause to take effect. Ex:
`SELECT CASE
WHEN column1=1 THEN 'one'
WHEN column1=2 THEN 'two'
ELSE 'other'
END AS result
FROM (values(1),(2),(3)) v;`
`WHEN column1=1 THEN 'one'
WHEN column1=2 THEN 'two'` -> When part of Case
`column1=1` and `column1=2` -> condition part of When
`THEN 'one'` and `THEN 'two'` -> Then part of When
`ELSE 'other'` -> Else part of Case
    Attributes:
  • pos: Specifies the start position and length of the object in the query
  • alias: The display name
  • expr: The condition that is checked for the current WHEN clause
  • then: Contains information about what to do after the WHEN condition is satisfied
  • direction: ASC/DESC
model.WrappedExpr
    Attributes:
  • expr: The attribute, function or expression being hold