Concepts → Incorta SQL View
About an Incorta SQL View
An Incorta SQL View is one of three types of views that you can create in a business schema. The other types of view are a business schema view and Incorta Analyzer View. Conceptually speaking, you can consider these types of views as being a runtime business view.
An Incorta Labs feature is experimental and functionality may produce unexpected results. For this reason, an Incorta Lab feature is not ready for use in a production environment. Incorta Support will investigate issues with an Incorta Labs feature. In a future release, an Incorta Lab feature may be either promoted to a product feature ready for use in a production environment or be deprecated without notice.
You can create an Incorta SQL View using the SQL editor. Here are some of the reasons why a schema developer will create an Incorta SQL View:
- Curate the columns exposed in one or more underlying physical schema tables
- Curate the columns exposed in one or more runtime business views
- Implement an individual filter for the view
- Implement a distinct filter for the view
- Implement an aggregated filter for the view
- Standardize business terminology
- Standardize row-level formulas and calculations
- Standardize aggregated formulas and calculations
Starting with 2023.7.0, you can view the data lineage of runtime business views in the Data Lineage Viewer v2 that displays a diagram with the entities referenced in the view (upstream lineage) and entities where the view is referenced (downstream lineage). Additionally, you can view the data lineage per column or formula column (as applicable) in the view.
About an applicable insight on a dashboard tab and an Incorta SQL View
A dashboard runtime filter applies the filter expression to all applicable insights on all tabs of a given dashboard.
For a filterable column, an applicable insight meets one of the following conditions:
- the filterable column is a dimension or measure column in the insight
- the filterable column has a sibling dimension column from the same physical schema table, and that sibling is a dimension for the insight
- the filterable column is a dimension that shares a measure on the insight from a common child table with other dashboard insights
Creating an Incorta SQL View
You can create an Incorta SQL view in the Business Schema Designer. From the Action bar, select +New → Add New View, and then select Create via SQL.
An Incorta SQL View only runs when it is referenced by an insight in a dashboard. Incorta SQL View is not run at startup time, or at a schema refresh time, instead, the view definition is saved, and the Incorta SQL View query runs when you open an insight that is using that view.
You can filter data in an Incorta SQL view using presentation variables in dashboards.
Supported SQL
The type of supported SQL queries depends on the tables that you are trying to access whether they are performance optimized or not. When an Incorta SQL View uses memory optimized tables, the SQL runs using the Incorta SQL engine.
If the Incorta SQL View uses non-optimized tables, the SQL runs using Spark. Refer to the Spark documentation for more information.
Referenceable objects
In the FROM clause, you can reference the following physical schema objects:
- Physical_Schema.Alias
- Physical_Schema.Table
- Physical_Schema.Materialized_View
- Physical_Schema.Incorta_Analyzer_Table
- Physical_Schema.Incorta_SQL_Table
It is not possible to reference the following objects:
- Business_Schema.Incorta_View
- Business_Schema.View
Join Types
Here are the join types for schema tables and SQL queries:
- CROSS JOIN
- INNER JOIN
- FULL OUTER
- LEFT OUTER
- RIGHT OUTER
Join Conditions
Here are the supported join conditions:
- table1.column1 (<), (<=), (>), (>=), (=) table2.column1
- table1.column1 (<), (<=), (>), (>=), (=), (<>) table1.column1
- table1.column (<), (<=), (>), (>=), (=), (<>) constant
- table1.column IN (constant1, … constantN)
- table1.column BETWEEN constant1 AND constant2
- table1.column LIKE ‘pattern’ [‘escape’]
- table1.column is [NOT] NULL
- startsWith(table1.column, ‘STRING’)
- endsWith(table1.column, ‘STRING’)
- contains(table1.column, ‘STRING’)
Supported predicates between join conditions and filters
The following are supported predicates between join condition and filter
- AND
- OR
Data types compatibility in JOIN constraints
The following conversion table describes how an Incorta SQL View handles a join constraint where the data types differ for the join columns.
LONG/INT | DOUBLE | DATE | TIMESTAMP | STRING | |
---|---|---|---|---|---|
LONG/INT | LONG/INT | DOUBLE | LONG | LONG | DOUBLE |
DOUBLE | DOUBLE | DOUBLE | DOUBLE* | ||
DATE | LONG | LONG | LONG | STRING | |
TIMESTAMP | LONG | LONG | LONG | STRING | |
STRING | DOUBLE* | DOUBLE* | STRING | STRING | STRING |
For a join between a string that stores a numeric value and a numeric column, the string
converts into a double
.
Set operators
Here is a list of supported set operators:
- UNION
- UNION ALL
Group filters
Here is a list of supported Group filters:
- HAVING
Row filters
Here is a list of supported Row filters:
- column (<), (<=), (>), (>=), (=), (<>) constant
- column [NOT] IN (constant1, … constantN)
- column BETWEEN constant1 AND constant2
- column is [NOT] NULL
- column [NOT] LIKE
In addition to the standard row-level filters, you can specify in a WHERE clause the following:
- [NOT] EXISTS (SELECT… )
- [NOT] IN (SELECT… )
- WHERE column (<), (<=), (>), (>=), (=) ANY(SELECT… correlated subquery)
- WHERE column (<), (<=), (>), (>=), (=) ALL(SELECT… non-correlated subquery)
Aggregation functions
Here is a list of supported aggregation functions:
- AVG()
- COUNT()
- COUNT(DISTINCT)
- MIN()
- MAX()
- STDDEV()
- STDDEV_SAMP()
- STDDEV_POP()
- SUM()
- VARIANCE()
- VAR_SAMP()
- VAR_POP()
Referencing Incorta Variables
Inside the SQL, you can reference Incorta variables using the following functions:
Function | Description |
---|---|
getsysvar(VAR_NAME, DEFAULT_VALUE) | Uses the System Variable VAR_NAME if defined, else use the DEFAULT_VALUE |
getsessvar(VAR_NAME, DEFAULT_VALUE) | Uses the Session Variable VAR_NAME if defined, else it uses the DEFAULT_VALUE |
getpresvar(VAR_NAME, DEFAULT_VALUE) | Uses the Presentation Variable VAR_NAME if defined, else it uses the DEFAULT_VALUE |
getvar(VAR_NAME, DEFAULT_VALUE) | Uses the Presentation Variable VAR_NAME if defined else, it uses the Session Variable VAR_NAME if defined, else it uses the System Variable VAR_NAME if defined, else it uses the DEFAULT_VALUE |
These functions enable you to use dashboard prompt filters and create more dynamic queries based on Incorta variables.
Window functions
A window function performs a calculation across a set of table rows that are somehow related to the current row. An Incorta SQL View supports windows functions such as:
OVER ()
OVER (PARTITION BY column)
OVER (ORDER BY column)
OVER (PARTITION BY column ORDER BY column)
You use these aggregation functions in a window function:
AVG()
,MIN()
,MAX()
,SUM()
,LEAD()
, andLAG()
You can use the advanced analytics functions in a window function:
DENSE_RANK()
,RANK()
, andINDEX()
Example of a Windows functions query
Here is an example of a windows function query using the HR physical schema found in the sample data provided by Incorta:
SELECT t2.DEPARTMENT_NAME, t1.salary, RANK() OVER (PARTITION BY t2.DEPARTMENT_NAME ORDER BY t1.salary DESC)FROM tbl_Employee t1 INNER JOIN tbl_Department t2 ON t1.department_id = t2.department_idGROUP BY t2.DEPARTMENT_NAME, t1.salaryORDER BY t2.DEPARTMENT_NAME, t1.salary
Common Table Expressions
An Incorta SQL View supports a non-recursive Common Table Expressions (CTE) for a SELECT statement.
The WITH keyword signifies a CTE, followed by a CTE name and the body of the CTE, and a SELECT statement. Optionally, a list of column names can be specified in the CTE SELECT statement. A primary SELECT statement references the CTE by name in a FROM clause. Here is an example:
WITH cte_analysts AS( SELECT * FROM tbl_EmployeesWHERE dept = 'Finance' )SELECT * FROM cte_analystsWHERE ...
A non-recursive CTE is more readable than a nested SELECT statement such as:
SELECT v1.* FROM (SELECT * FROM tbl_EmployeesWHERE dept = 'Finance') v1
A WITH query can only reference sibling WITH queries that are earlier in the WITH list. A WITH query evaluates only once per execution of the primary query, even if the primary query refers to WITH query more than once.
Examples of a CTE
Here is an example of a year-over-year comparison using the SALES physical schema:
WITH sales_product_year AS (SELECT t1.PROD_ID, YEAR(t1.TIME_ID) AS year,SUM(t1.AMOUNT_SOLD) AS total_amtFROM SALES.SALES t1GROUP BY t1.PROD_ID, YEAR(t1.TIME_ID))SELECT t2.PROD_ID, t2.total_amt as cur_total_amount, t2.year as cur_year, t3.total_amt as prev_total_amt, t3.year as prev_yearFROM sales_product_year t2 INNER JOINsales_product_year t3 ON t2.PROD_ID = t3.PROD_IDAND t2.year = t3.year + 1ORDER BY t2.PROD_ID, t2.year
Insights over Incorta SQL Views
When you create insights referencing Incorta SQL Views, the insights are not rendered using the Incorta engine like other insights. Instead, the insights are converted to SQL, and then the engine is chosen based on what tables are referenced in the Incorta SQL Views.
If all of the tables being referenced in the Incorta SQL View are memory optimized, then Incorta uses the Incorta SQL engine to run the generated SQL. If any of the tables being referenced is not memory optimized, then Incorta uses Spark to run the generated SQL.
Incorta SQL Views supports the following for insights:
- Prompt Filter
- Applied Filter
- Filter Option
- Insight Filter
- Measure Filter
Incorta SQL Views appear in the Analyzer in the list of views in the Data panel.
Although it is feasible to create a dashboard that incorporates insights derived from Incorta SQL Views with those derived from other business views or physical schema objects, it’s important to recognize that this approach is not recommended. Such an approach may lead to errors and could potentially render the Analytics Service unresponsive when rendering or accessing these dashboards.
Incorta SQL View limitations
- It is not recommended to use
SELECT *
. In case of changing the source tables definitions, the Incorta SQL View definition will not automatically update. - You cannot use more than one Incorta SQL View in an insight.
- Dashboards with insights based on Incorta SQL Views do not support data alerts (data notifications).
- Using a SQL view column as the base of conditional formatting is not supported.