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 TypeSupportedSQL DialectsKnown Limitations
Legacy Parquet (from Data Table Save Processor)no

Legacy CSV (from CSV upload)no

internal PostgreSQLyesSpark 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 ParquetyesSpark
regular CSVyesSpark
PostgreSQL from ConnectionyesSpark or native PostgreSQL depending on the server configuration.

Oracle from Connectionpartiallynative OracleCustom SQL on top of custom SQL doesn't work. An example can be found below.
Hive from Connectionpartiallynative HiveCustom SQL on top of custom SQL doesn't work. An example can be found below.
Impala from Connectionyesnative Impala
DB2 from ConnectionyesSpark
HANA from Connection(yes)native HANA
Microsoft SQL Server (also with Kerberos)yesSpark
MySQLyesSpark
CSV in File System Connectionno



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 TypeSupportedSQL DialectKnown Limitations
PostgreSQLyesnative
Oraclepartiallynative
Hiveyes

Custom SQL on top of custom SQL doesn't work. An example can be found below.
Impalayesnative
DB2no

HANAyesnative
Microsoft SQL Server (also with Kerberos)yesnative
MySQLpartiallynativeCustom 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"
            }
          ]
        }
      }
    }