When to Use Which SQL Dialect in Apps

Modified on Thu, 18 Mar 2021 at 05:52 PM

Introduction

In Apps, it is possible to have Datasources that gather data from various origins that are registered in ONE DATA. Either for example just from an uploaded dataset, or from a database that ONE DATA has access to via a Connection. While this opens up many possibilities, differences when working with different technologies need to be taken into account. In this context, the dialect of SQL which you need to use can differ between database technologies.

Therefore, this article provides an overview on when to use what, and also gives insight on known limitations.

Data Table Sources

This section focuses on the Datasources that have the origin Data Table. Below is a list of the available Data Table types in ONE DATA, which shows if they are supported in Apps and if yes, which SQL to use.

Data Table Type	Supported	SQL Dialects	Known Limitations
Legacy Parquet (from Data Table Save Processor)	no
Legacy CSV (from CSV upload)	no
internal PostgreSQL	yes	Spark or native PostgreSQL depending on the server configuration. ONE DATA backend config to enable native SQL: frt.postgres.sqlForDataRequests. enable=true (this is the default) frt.postgres.sqlForDataRequests. usePostgresCustomSql=true (false by default)
regular Parquet	yes	Spark
regular CSV	yes	Spark
PostgreSQL from Connection	yes	Spark or native PostgreSQL depending on the server configuration.
Oracle from Connection	partially	native Oracle	Custom SQL on top of custom SQL doesn't work. An example can be found below.
Hive from Connection	partially	native Hive	Custom SQL on top of custom SQL doesn't work. An example can be found below.
Impala from Connection	yes	native Impala
DB2 from Connection	yes	Spark
HANA from Connection	(yes)	native HANA
Microsoft SQL Server (also with Kerberos)	yes	Spark
MySQL	yes	Spark
CSV in File System Connection	no

Connection Datasources

This section focuses on the Datasources that have a ONE DATA Connection as origin. Below is a list of the available database Connections which shows if they are supported in Apps and if yes, which SQL dialect must be used.

Database Type	Supported	SQL Dialect	Known Limitations
PostgreSQL	yes	native
Oracle	partially	native
Hive	yes		Custom SQL on top of custom SQL doesn't work. An example can be found below.
Impala	yes	native
DB2	no
HANA	yes	native
Microsoft SQL Server (also with Kerberos)	yes	native
MySQL	partially	native	Custom SQL doesn't work, at least not for versions prior to 8.

Example for Invalid Datasource Configuration

As mentioned above, for Oracle and Hive Datasources (from a Connection) you can not use custom SQL on top of custom SQL.

Here is an example for an invalid configuration:

{

      "id": "example",

      "origin" : "connection",

      "config" : {

        "schema" : "table",

        "connectionId" : "43797342-dcdf-454b-8cd5-760a36c9db2f ",

        "baseQuery" : "SELECT * FROM foo.bar",

        "dataOptions" : {

          "transformations" : [

{

              "type" : "sql",

              "config": "SELECT * from inputTable"

},

{

              "type" : "sql",

              "config": "SELECT COUNT(*) from inputTable"

}

]

}

}

}