(v24) Custom SQL Queries

Custom SQL Queries

This applies to: Visual Data Discovery

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.

Important: 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.

If your Symphony environment makes use of Logi AI, you can download or create your own chatflows to generate custom SQL statements for use in your environment. See Generate SQL Statements.

In SQL-based sources, Symphony 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 Symphony uses is this:

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

Support for this feature by connector 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
Business Central Jet	N
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
Jira	Y
MemSQL	Y
Microsoft SQL Server	Y
MongoDB	N/A
MySQL	Y
Oracle	Y
PostgreSQL	Y
Python	Y
Real Time Sales	N
Salesforce	Y
SAP Hana	Y
SAP S/4HANA	Y
SAP IQ	Y
Spark SQL	Y
Snowflake	Y
Teradata	Y
TIBCO DV	Y
Trino	Y
File Upload (Upload API)	N
Vertica	Y