Skip to main content

Enable Custom SQL Query

To improve performance of your Hive or relational connections, custom SQL queries can be enabled to push the initial filtration of table rows and columns back the database, which is more efficient at performing this task. Instead of loading the entire table into the Trifacta Application and then performing the filtration through the Transformer page, you can insert basic SQL commands as part of your relational queries to collect only the rows and columns of interest from the source.

When enabled, custom SQL query is available for all relational sources.

Limitations

See Create Dataset with SQL.

Enable

Steps:

  1. You apply this change through the Workspace Settings Page. For more information, see Platform Configuration Methods.

  2. Locate the following setting:

    Enable custom SQL Query

    Setting

    Description

    enabled

    Set to true to enable the SQL pushdown feature. By default, this feature is enabled.

Enable multi-statement

Optionally, you can enable the use of multiple statements in your SQL queries for imported datasets.

Steps:

  1. You can apply this change through the Admin Settings Page (recommended) or trifacta-conf.json. For more information, see Platform Configuration Methods.

  2. Locate the following setting:

    "webapp.connectivity.customSQLQuery.enableMultiStatement": false,

    Setting

    Description

    enableMultiStatement

    When set to true, you can insert multi-line statements in your SQL pushdown queries. The default is false.

    Note

    Use of multi-line SQL has limitations. See Create Dataset with SQL.

  3. Save your changes and restart the platform.

Configure query timeout

As needed, you can configure the maximum permitted load time before timeout from the application. See Configure Application Limits.

Use Custom SQL Queries

When custom SQL query is enabled, you can enter customized SQL statements in the imported dataset page as part of the import process. See Import Data Page.

For examples, see Create Dataset with SQL.

After a dataset has been imported using custom SQL, you can edit the SQL as needed. See Dataset Details Page.

In this section: