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.
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, 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 |
| 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 IQ | Y |
| Spark SQL | Y |
| Snowflake | Y |
| Teradata | Y |
| TIBCO DV | Y |
| Trino | Y |
| File Upload (Upload API) | N |
| Vertica | Y |