SQL Dialects

SQL Dialects#

Data sources we connect to support different SQL languages, for example, Oracle provider supports PL/SQL that differs in many areas from TSQL that Querona supports. Because of this differences, Querona needs to be configured to convert queries from one SQL language dialect to another. SQL Dialects provide the functionality to map TSQL language elements between Querona’s SQL and provider SQL.

While Querona has a lot of dialects configured out-of-the-box, there are ADO.net, JDBC or ODBC drivers that are less popular and are not included with Querona. Users might also decide not to use a specific provider functionality or just do things differently.

For example, provider SQL can be missing an aggregate function like SUM(). In such case, Querona has to provide the implementation of SUM(). Even if function parity exists, thanks to the mapping configuration, we can decide what SQL functionality we’d like to push-down to the provider.

Note

If the feature-set of a provider SQL is different than TSQL, Querona fills the gaps using its in-memory function implementations or using a default federator.

SQL dialect in action#

Let’s assume that we have connected Querona to MySql and use a single table called Shop.Sales. We want to execute in Querona the following SQL statement, which gives us a sum of all sales:

SELECT SUM(Total)
  FROM Shop.Sales

Let’s assume that MySql dialect has a defined mapping of the SUM() function. The resulting SQL executed in MySql would look like this:

SELECT SUM(`Total`)
  FROM `Shop`.`Sales`

Notice that the SQL statement was rewritten to SQL format of MySql database server, along with the SUM() function. It means that the whole statement will execute in one step in MySql database and only results of that query will be transferred from MySql to Querona.

Now let’s assume that MySql dialect has not defined the mapping of the SUM() function. In such case, the execution of the statement will be different. Due to lack of SUM() function mapping, the execution plan will be a two-step. The first step is to get the required data from MySql and transfer it to Querona for processing of SUM() function.

The first SQL statement will look like that:

SELECT `Total`  -- notice the lack of SUM() function
  FROM `Shop`.`Sales`

The second step will consist of processing the data obtained in the first step, using a default federator - usually the built-in Apache Spark.

The SQL statement will be similar to the following:

SELECT sum('Total')
  FROM <temporary_object_name_holding_data_from_step_one>

SQL dialect configuration is an essential part of the query execution and optimization engine. It enables a lot of flexibility in many scenarios, such as:

  • Configuring a new provider without coding, even for less popular exotic data sources that Querona vendor does not have access or built-in support.

  • Configuring a dialect that does not depend on a given technology (ADO.net, JDBC, ODBC) or dialect for each technology.

  • Control over optimizer behavior of what SQL features should be pushed down to the provider. For example, a given functionality in provider may be buggy or perform poorly. In such case, we can disable it and rely on a better option provided by Querona.

Features of SQL dialects#

Querona supports many data-access technologies, such as ADO.Net, JDBC, ODBC, REST, and others. Each technology has its requirements, may support the same features in different ways, or support features not present anywhere else.

To find the common denominator, we introduced the following features to SQL dialects:

  • Connection string definition customization,

  • Schema analyzers with configuration,

  • Query features configuration,

  • SQL features configuration,

  • Type mappings,

  • Function mappings.

We will describe each of them in turn.

Note

SQL dialect configuration requires administrative privileges and is available in web GUI under Administer ‣ SQL Dialects. You can use the SQL dialect configuration to tweak the existing dialect definitions or create a new dialect.

Connection definition customization#

Connection definition provides means to set a connection string template, which will be used by a new connection wizard. Identifier quote character is also configurable.

Query features#

Query feature

Description

CROSS JOIN

True if data provider’s SQL supports CROSS JOIN clause.

EXCEPT

True if data provider’s SQL supports EXCEPT operator.

FROM with a subquery

True if data provider’s SQL FROM clause supports subqueries.

FULL JOIN

True if data provider’s SQL supports a FULL JOIN operator.

GROUP BY

True if data provider’s SQL supports GROUP BY clause.

GROUP BY CUBE

True if data provider’s SQL supports GROUP BY CUBE clause.

GROUP BY GROUPING SETS

True if data provider’s SQL supports GROUP BY GROUPING SETS clause.

GROUP BY ROLLUP

True if data provider’s SQL supports GROUP BY ROLLUP clause.

HAVING

True if data provider’s SQL supports HAVING clause.

INNER JOIN

True if data provider’s SQL supports INNER JOIN operator.

INTERSECT

True if data provider’s SQL supports INTERSECT operator.

LEFT JOIN

True if data provider’s SQL supports LEFT JOIN clause.

Multisource FROM

True if data provider’s SQL supports a multi-source FROM clause.

ORDER BY

True if data provider’s SQL supports ORDER BY clause.

OVER

True if data provider’s SQL supports OVER clause.

Paging

True if data provider’s SQL supports paging.

Parametrized queries

True if data provider’s SQL supports parametrized queries.

PARTITION BY inside OVER

True if data provider’s SQL supports PARTITION BY inside an OVER clause.

RIGHT JOIN

True if data provider’s SQL supports RIGHT JOIN clause.

SELECT…INTO

True if data provider’s SQL supports SELECT...INTO clause.

TOP

True if data provider’s SQL supports TOP clause and which variant: TOP(<n>), LIMIT <n> or ROWS <n>. To allow TOP clause to be pushed-down to a source provider Literal value and Column reference SQL features must be enabled.

UNION

True if data provider’s SQL supports UNION operator.

UNION ALL

True if data provider’s SQL supports UNION ALL operator.

SQL features#

SQL features configuration section allows you to map SQL from source to SQL features in Querona.

SQL feature

Operator

Description

Arithmetic: And

&

True if data provider’s SQL supports arithmetic AND operator.

Arithmetic: Divide

/

True if data provider’s SQL supports arithmetic DIVISION operator.

Arithmetic: Subtract

-

True if data provider’s SQL supports arithmetic SUBTRACTION and unary NEGATIVE operators. The unary NEGATIVE operator can also be used to perform arithmetic operations on datetime and smalldatetime values.

Arithmetic: Modulo

%

True if data provider’s SQL supports arithmetic MODULUS operator.

Arithmetic: Multiply

*

True if data provider’s SQL supports arithmetic MULTIPLICATION operator.

Arithmetic: Not

~

True if data provider’s SQL supports arithmetic NEGATION operator.

Arithmetic: Or

\

True if data provider’s SQL supports arithmetic OR operator.

Arithmetic: Plus

+

True if data provider’s SQL supports arithmetic ADDITION and UNARY POSITIVE operator. The unary POSITIVE operator can also be used to perform arithmetic operations on datetime and smalldatetime values.

Arithmetic: Xor

^

True if data provider’s SQL supports bitwise XOR operator.

Bitwise NOT expr.

~

True if data provider’s SQL supports bitwise NOT operator.

CAST

True if data provider’s SQL supports a CAST (expression AS data_type [ (length) ]) expression.

Column reference

True if data provider’s SQL supports column reference in expression.

Comparison: Equal

=

True if data provider’s SQL supports comparison EQUAL operator.

Comparison: Greater

>

True if data provider’s SQL supports comparison GREATER THAN operator.

Comparison: Greater or equal

>=

True if data provider’s SQL supports comparison GREATER THAN OR EQUAL operator.

Comparison: Less

<

True if data provider’s SQL supports comparison LESS THAN operator.

Comparison: Less or equal

<=

True if data provider’s SQL supports comparison LESS OR EQUAL THAN operator.

Comparison: Not equal

<>

True if data provider’s SQL supports comparison NOT EQUAL operator.

Concatenate strings

+

True if data provider’s SQL supports a string concatenation using + operator.

CONVERT

True if data provider’s SQL supports a CONVERT(data_type [ (length) ] , expression [ , style ] ) expression.

Date and time predefined value expr.

True if data provider’s SQL supports a predefined date value like in DATEPART(`month`, $1).

Function call

True if data provider’s SQL supports functions.

Literal value

True if data provider’s SQL supports literals.

Literal value in projection

True if data provider’s SQL supports literals in projection.

Negative expr.

True if data provider’s SQL supports unary negative operator.

Parameter reference

True if data provider’s SQL supports parameter references in expressions.

Predicate: AND

True if data provider’s SQL supports AND predicate.

Predicate: BETWEEN

True if data provider’s SQL supports BETWEEN predicate.

Predicate: EXISTS

True if data provider’s SQL supports EXISTS predicate.

Predicate: IN (expression)

True if data provider’s SQL supports a composite predicate IN <values>.

Predicate: IN (subquery)

True if data provider’s SQL supports a composite predicate IN (subquery).

Predicate: IS NULL

True if data provider’s SQL supports IS NULL predicate.

Predicate: LIKE

True if data provider’s SQL supports LIKE predicate.

Predicate: NOT

True if data provider’s SQL supports NOT predicate.

Predicate: OR

True if data provider’s SQL supports OR predicate.

Searched CASE

True if data provider’s SQL supports a Searched CASE expression, for example CASE WHEN case_operand = when_operand1 THEN result1 WHEN case_operand = when_operand2 THEN result2 ... END.

SELECT scalar expr.

True if data provider’s SQL supports a select scalar expression, which is a nested SELECT statement that returns one column and one row.

Simple CASE

True if data provider’s SQL supports a simple CASE expression.

Microsoft’s documentation#

Type mappings#

Type mappings section allows you to map data types from source to data types in Querona.

SQL type mapping column name

Scope

Description

System type

System

Data type in Querona, read only. Type system is compatible with Microsoft SQL Server 2012.

Target storage type

System

Data storage type in Querona.

Cast as

Provider

Data type that will be used to cast values to when preparing a query for provider.

Provider import types (CSV)

Provider

Comma-separated list of provider type names, including aliases.

Variable length

System

True if a provider data type has variable length.

Variable precision

System

True if a provider data type has variable precision.

Variable scale

System

True if a provider data type has variable scale.

For example, Teradata provider reports its supported binary data types are VARBYTE and BLOB. We store such data in Querona as VarBinary type. When we format a query to Teradata that uses any of types (VARBYTE, BLOB) we cast it to VARBYTE. The type mappings for the scenario above look like this:

SQL type mapping column name

Value

System type

VarBinary

Target storage type

VarBinary

Cast as

VARBYTE

Provider import types (CSV)

VARBYTE, BLOB

Variable length

True

Variable precision

False

Variable scale

False

Function mappings#

Function mappings section allows you to map provider functions to Querona functions. Left table column represents the Querona function declaration and is read-only. Right table column holds the function declaration supported by provider and is user-editable.

To map a function declaration find its name in the left column and enter the provider function declaration to the right column. Function parameters are represented by a dollar sign followed by one-based parameter ordinal number, for example, $3 represents the third parameter of Querona’s function.

Note

SQL Server 2012 built-in functions on Microsoft TechNet .

Example, representative variants of function mappings:

Function name

Mapping to provider function

Comment

CEILING($1)

CEIL($1)

Querona function CEILING(parameter) has a different name in provider and maps to CEIL(parameter).

CHARINDEX($1, $2)

CHARINDEX($2, $1)

Querona function declaration maps to the same function name, but with reversed parameters ($1,$2)->($2,$1).

COUNT($1)

COUNT($1)

Querona function declaration maps to exactly the same function in provider.

COUNT_BIG($1)

<empty>

COUNT_BIG($1) function is not supported by provider and will be simulated by Querona.

YEAR($1)

DATEPART(year, $1)

YEAR($1) function does not directly exist in provider, but is substituted with DATEPART(year, $1).

QUOTENAME($1)

CONCAT (‘’’, REPLACE($1, ‘’’, CONCAT(‘’’,’’’)), ‘’’)

QUOTENAME($1) function does not exist in provider, but can be simulated with a compound expression.

Date/time function mappings#

Date/time function mappings section allows you to map all variants of the following functions:

Example variants of function mappings:

Function name

Mapping to provider function

Comment

DATEADD(day, $1, $2)

DATE_ADD($2, INTERVAL $1 day)

Querona function variant DATEADD(day, $1, $2) mapped to provider function DATE_ADD($2, INTERVAL $1 day).

DATEADD(nanosecond, $1, $2)

NANOSECOND($1)

Querona function variant DATEADD(nanosecond, $1, $2) mapped to provider function NANOSECOND($1).

Schema analyzers#

Schema analyzers are built-in, configurable adapters that read metadata from a specific provider and perform necessary steps to make the metadata importable to Querona.

We support the following analyzers:

  • Custom with restrictions,

  • Custom with Select,

  • JDBC.

Standard settings for all providers are:

Setting Name

Type of value

Description

Length field name

text

Column name in metadata table returned by the provider that holds length information. Usually a COLUMN_SIZE, COLUMNSIZE, CHARACTER_MAXIMUM_LENGTH.

Name field name

text

Column name in metadata table returned by the provider that holds column name. Usually a COLUMN_NAME.

Nullability check field return type

list of values

Type of value returned by the provider in the column designated in ‘Nullable field name’ setting. Supported values are Bool, Int, Text:Yes/No, Text:Y/N.

Nullable field name

text

Column name in metadata table returned by the provider that holds column nullability. Usually a NULLABLE, IS_NULLABLE.

Precision field name

text

Column name in metadata table returned by the provider that holds precision. Usually a NUM_PREC_RADIX, NUMERIC_PRECISION.

Scale field name

text

Column name in metadata table returned by the provider that holds scale. Usually a DECIMAL_DIGITS, NUMERIC_SCALE.

Type field name

text

Column name in metadata table returned by the provider that holds type name. Usually a TYPE_NAME, DATA_TYPE.

Field type retrieved by

list of values

Designates a method to extract type information from column designated in ‘Type field name’. Supported methods are Odbc code, Type.

Table’s metadata loading method

list of values

Supported methods are Simple object name restriction, Fully qualified object name restriction.

Table list loading method

list of values

Supported methods are With restrictions, Without restrictions.

Custom query for table’s metadata

text

Query to use to extract table’s metadata from the provider.

Divide Unicode column length by 2

true/false

True when column length returned from provider holds the length doubled for Unicode characters.

Use database name in restrictions

true/false

True when database name has to be used in restrictions. False otherwise.

Use schema name in restrictions

true/false

True when schema name has to be used in restrictions. False otherwise.

Use table name in restrictions

true/false

True when table name has to be used in restrictions. False otherwise.