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 Tables/Indices Tab of the data source configuration wizard after selecting the Custom SQL button.

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 N
Cloudera Impala Y
Cloudera Search N/A
Couchbase N
Dremio Y
Elasticsearch 6.0 N/A
Elasticsearch 7.0 N/A
Flat File Y
HDFS N
Hive Y
MemSQL Y
Microsoft SQL Server Y
MongoDB N/A
MySQL Y
Oracle Y
PostgreSQL Y
Presto Y
Real Time Sales N
SAP Hana Y
SAP IQ Y
Snowflake Y
Spark SQL Y
Teradata Y
TIBCO DV Y
Upload API N
Vertica Y