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:
(this is the default)
(false by default)
|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|
|CSV in File System Connection||no|
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|
|Hive||yes||Custom SQL on top of custom SQL doesn't work. An example can be found below.|
|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: