Concepts → Incorta SQL Table

About an Incorta SQL Table

An Incorta SQL Table is a new type of derived table that a schema developer creates in a physical schema using a SELECT statement. It uses a new SQL engine that orchestrates complex query execution using the existing engine as a query processor.

The SELECT statement also supports the use of the WITH clause for a non-recursive Common Table Expression (CTE).

The Loader Service automatically determines the load order for the Incorta SQL table.

In this release, you can enable the Incorta SQL Table, an Incorta Labs feature.

Considerations for an Incorta SQL Table

Here are some important considerations for an Incorta SQL Table:

• All referenceable objects must be performance optimized physical schema tables or materialized views.
• There is no support for an incremental load.
• There is no fallback routing for SQL processing to Apache Spark.
• It is not possible to specify a load order for the table in a physical schema.
• It is not possible to specify a load filter.
• It is a single-source table.

Referenceable objects

In the FROM clause, you can reference the following physical schema objects:

• Physcial_Schema.Alias
• Physcial_Schema.Table
• Physcial_Schema.Materialized_View

It is not possible to reference the following objects:

• Business_Schema.Incorta_View
• Business_Schema.View
• Physcial_Schema.Incorta_Analyzer_Table
• Physcial_Schema.Incorta_SQL_Table

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
• AND
• expression in JOIN

The following are unsupported join conditions

• OR

Data types for Join Constraints

The following conversion table describes how an Incorta SQL Table handles a join constraint where the data types differ for the join columns.

Set operators

Here is a list of supported set operators:

• UNION
• UNION ALL
• MINUS
• EXCEPT
• INTERSECT

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 use specify in a WHERE clause the following:

• [NOT] EXISTS (SELECT… )
• [NOT] IN (SELECT… )
• WHERE column (<), (<=), (>), (>=), (=) (SELECT… correlated subquery)
• WHERE column (<), (<=), (>), (>=), (=) (SELECT… non-correlated subquery)

Aggregation functions

Here is a list of supported aggregations functions

• AVG()
• COUNT()
• COUNT(DISTINCT )
• MIN()
• MAX()
• STDDEV()
• STDDEV_SAMP()
• STDDEV_POP()
• SUM()
• VARIANCE()
• VAR_SAMP()
• VAR_POP()

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 Table 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(), and COUNT()

You can use the advanced analytics functions in a window function:

• DENSE_RANK(), RANK(), and ROW_NUMBER()

Example of a Windows functions query

Here are is an example of a windows function query using the HR physical schema:

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_id
GROUP BY t2.DEPARTMENT_NAME, t1.salary
ORDER BY  t2.DEPARTMENT_NAME,  t1.salary

Common Table Expressions

An Incorta SQL Table supports a non-recursive CTE for a SELECT statement.

The WITH keyword signifies a CTE, followed by a CTE name and the body of the CTE, 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 a an example:

WITH cte_analysts AS
   ( SELECT * FROM tbl_Employees
     WHERE dept = 'Finance' )

SELECT * FROM cte_analysts
WHERE ...

A non-recursive CTE is more readable than a nested SELECT statement such as:

SELECT v1.* FROM (
  SELECT * FROM tbl_Employees
  WHERE 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 are is an example of a year-over-year comparison using the SALES physical schema:

year-over-year

WITH sales_product_year AS (
  SELECT t1.PROD_ID, YEAR(t1.TIME_ID) AS year,
  SUM(t1.AMOUNT_SOLD) AS total_amt
  FROM SALES.SALES t1
  GROUP 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_year
FROM sales_product_year t2 INNER JOIN
sales_product_year t3 ON t2.PROD_ID = t3.PROD_ID
AND  t2.year = t3.year + 1
ORDER BY t2.PROD_ID, t2.year

Steps to enable the Incorta SQL Table in the CMC

Here are the steps to enable this option as the default tenant configuration in the CMC:

• Sign in to the CMC as the CMC Administrator.
• In the Navigation bar, select Clusters.
• In the cluster list, select a Cluster name.
• In the canvas tabs, select Cluster Configurations.
• In the panel tabs, select Default Tenant Configurations.
• In the left pane, select Incorta Labs.
• In the right pane, toggle on Enable Incorta SQL Table.
• Select Save.

Here are the steps to enable this option for a specific tenant configuration in the CMC:

• Sign in to the CMC as the CMC Administrator.
• In the Navigation bar, select Clusters.
• In the cluster list, select a Cluster name.
• In the canvas tabs, select the Tenants tab.
• In the Tenant list, for the given tenant, select Configure.
• In the left pane, select Incorta Labs.
• In the right pane, toggle on Enable Incorta SQL Table.
• Select Save.