Skip to content

SQL Dialects per Connector

The language of a Computed Table's query depends on the kind of datastore it lives in:

  • JDBC connectors: the query runs on the parent datastore's connection, in the source's own dialect. Qualytics does not translate the dialect, so write the SQL the warehouse expects (HiveQL for Hive, T-SQL for SQL Server, and so on).
  • Native connectors: Qualytics runs the query itself on the data it reads, so it is always written in Spark SQL, whatever platform holds the data. See Native Connectors below.

This page collects the connector-specific quirks worth knowing before you write a Computed Table's SQL.

The query runs inside a subquery

On JDBC connectors, Qualytics embeds your query in a subquery, so it must also be valid there:

  • Give each expression without an output name an alias (for example, SELECT amount * 2 AS doubled_amount). Ordinary columns and * do not need aliases.
  • On SQL Server, an ORDER BY in the query also needs TOP or OFFSET ... ROWS (for example, ORDER BY score DESC OFFSET 0 ROWS keeps every row), because SQL Server rejects a bare ORDER BY inside a subquery. Other warehouses, such as PostgreSQL, accept it as written.
  • A trailing semicolon is removed for you.

Fully-Qualified Names

The following connectors reject unqualified table references and require every table to be qualified with its schema:

  • Microsoft SQL Server: reference tables as SCHEMA.TABLE.
  • Oracle: reference tables as SCHEMA.TABLE.
  • Amazon Redshift: reference tables as SCHEMA.TABLE.

The table picker in the Add and Edit modals prefixes the schema for these connectors automatically, so a table name pasted from the picker already includes the schema. Hand-typed references, or references to tables in a non-default schema, still require the explicit SCHEMA.TABLE form.

For every other JDBC connector, an unqualified name is usually enough as long as the datastore connection's default schema resolves it. Use Validate in the modal to confirm before saving.

Supported Connectors

Computed Tables run on every JDBC connector and every native connector Qualytics exposes.

JDBC Connectors

The Computed Tables? column is Yes for every JDBC connector. The FQN Required? column flags the three connectors covered in Fully-Qualified Names above where the SQL body must use SCHEMA.TABLE for every reference. The Spark SQL? column shows whether the query is written in Spark SQL. On every JDBC connector it is written in the source's own dialect instead. Databricks is the closest case: Databricks SQL is built on Spark SQL, so most Spark SQL syntax works there, but the query goes to Databricks and follows Databricks SQL, so the column stays No.

No. Connector Computed Tables? FQN Required? Spark SQL?
1. Redshift Amazon Redshift
2. Athena Athena
3. BigQuery BigQuery
4. Databricks Databricks
5. DB2 DB2
6. Dremio Dremio
7. Fabric Fabric Analytics
8. Hive Hive
9. MariaDB MariaDB
10. SQL Server Microsoft SQL Server
11. MySQL MySQL
12. Oracle Oracle
13. PostgreSQL PostgreSQL
14. Presto Presto
15. SAP HANA SAP HANA
16. Snowflake Snowflake
17. Synapse Synapse
18. Teradata Teradata
19. Timescale Timescale DB
20. Trino Trino

Native Connectors

The same columns apply here. Computed Tables? is Yes for every native connector. FQN Required? is No, because an unqualified name resolves against the datastore's own database. Spark SQL? is Yes for all of them, because Qualytics runs the query itself instead of sending it to the source platform's own query engine.

No. Connector Computed Tables? FQN Required? Spark SQL?
1. AWS Glue Native AWS Glue Native
2. Hive Native Hive Native
3. MongoDB MongoDB
4. Unity Catalog Native Unity Catalog Native

Databricks Native, available on Databricks-based deployments, follows the same rule.

A few things work differently from JDBC connectors:

  • Only read queries are accepted. The query must start with SELECT, WITH, VALUES, TABLE, or FROM. Statements that create, change, or remove objects or settings, such as CREATE, DROP, SET, CACHE TABLE, or ADD JAR, are refused.
  • Table names resolve against the datastore's own database. An unqualified name such as orders points to the database (or schema) set on the datastore, so table names need no prefix.
  • Give each expression an alias. As on JDBC connectors, an expression without an output name needs one (for example, SELECT amount * 2 AS doubled_amount).
  • Platform-specific syntax may not carry over. Spark SQL is close to HiveQL and Databricks SQL, but functions registered only on your cluster (such as Hive UDFs) and session statements are not available. On Hive Native, write the query in Spark SQL even though the same tables take HiveQL through the Hive connector.

The editor hint names the connector

The hint below the query editor names the datastore's connector, for example "Any valid Hive Native query". On native connectors that always means a Spark SQL query.

DFS Connectors

DFS datastores do not offer Computed Tables, so the Computed Tables? column is No for every DFS connector. They use Computed Files instead, which are written in Spark SQL, so the Spark SQL? column is Yes. See Spark SQL Rules for how a Computed File's clauses are written.

No. Connector Computed Tables? Spark SQL?
1. Amazon S3 Amazon S3
2. Azure Datalake Storage Azure Datalake Storage (ABFS)
3. Google Cloud Storage Google Cloud Storage

Dialect Notes

Each dialect has its own preferred syntax for common patterns. A short cheat sheet of things to remember:

  • Snowflake: supports QUALIFY for window-function filtering; IFF() is a common alternative to CASE WHEN; identifiers are case-sensitive when wrapped in double quotes.
  • BigQuery: table references are project.dataset.table; use STRUCT and UNNEST for nested data; prefer SAFE_CAST over CAST for error-safe conversions. Computed tables are unaffected by Billing Project ID.
  • SQL Server: TOP instead of LIMIT; + is the string-concatenation operator; identifiers can be quoted with [brackets] or double quotes.
  • Oracle: no built-in boolean type in SELECT output; NVL and NVL2 are Oracle-flavored COALESCE; use ROWNUM for row-number filtering or a window function for a more portable pattern.
  • PostgreSQL and Redshift: identifiers are case-insensitive unless wrapped in double quotes; LIMIT and OFFSET for pagination; Redshift diverges from PostgreSQL for some window-function edge cases.
  • Databricks: supports standard SQL over Delta and Parquet tables. Watch out for the semantics of IS NULL on nested STRUCT columns. Databricks SQL is built on Spark SQL, so most Spark SQL syntax also works here, but the query runs on Databricks compute.
  • Athena, Presto, and Trino: mostly ANSI SQL; use TRY_CAST and TRY for error-safe conversions; array and map types are first-class.

For dialect-specific reference material, consult the warehouse's own documentation.