(v22) Custom SQL Queries

Custom SQL Queries

Applicable only to SQL-based connectors, a data source using a connector that supports custom SQL queries can use an SQL query to select fields from the table. The custom SQL statement can be specified on the Custom SQL area of the Source Creation tab after selecting the Custom SQL option. Any visual you create displays fields in the order they are retrieved from the source. When you create a source using custom SQL, your field data is shown in the order you specify.

Custom SQL queries are a powerful tool for performing complex data queries. However, be careful when creating custom SQL queries because it is easy to define a heavy query or a query that may overwhelm your database. Use this feature carefully.

In SQL-based sources, Composer typically wraps the query with select * from. For example, suppose the original query is this:

select count(*), someField from myCollection GROUP By someField

The resulting query that Composer uses is this:

select * from (select count(*), someField from myCollection GROUP By someField)

Support for this feature by Composer connectors is shown in the following table.

Key:Y - Supported; N - Not Supported; N/A - not applicable

Connector	Supported?
Amazon Redshift	Y
Amazon S3	N
Apache Drill	Y
Apache Phoenix	Y
Apache Phoenix Query Server (QS)	Y
Apache Solr	N/A
BigQuery	Y
Cloudera Impala	Y
Cloudera Search	N/A
Couchbase	N
Dremio	Y
Elasticsearch 7.0	N/A
Elasticsearch 8.0	N/A
File Upload	Y
HDFS	N
Hive	Y
MemSQL	Y
Microsoft SQL Server	Y
MongoDB	N/A
MySQL	Y
Oracle	Y
PostgreSQL	Y
Real Time Sales	N
SAP Hana	Y
SAP IQ	Y
Snowflake	Y
Spark SQL	Y
Teradata	Y
TIBCO DV	Y
Trino	Y
File Upload (Upload API)	N
Vertica	Y