Custom Queries integration

Updated

Introduction

The Custom Queries module allows you to create, run, and automate your own SQL queries within the data stored in your data warehouse — both data collected by WitCloud and data from external sources. This enables you to create custom views, reports, and datasets without modifying the main dataset structure.

Configuration guide

Follow the steps below to create the Custom Queries module.

Module configuration

Go to Integrations in the menu and click Add integration.

Integrations list with the Add integration button

From the list of available integrations, select Custom Queries.

Integration catalog with Custom Queries selected

Enter the Custom Query name, e.g., "Example Custom Query".

Custom Query name field filled in with the example name Example Custom Query

Enter the SQL query in the Query content field. You can use the full BigQuery syntax and the {{RUN_DATE}} variable, which will automatically be replaced with the process execution date.

The {{RUN_DATE}} variable follows the YYYYMMDD format and corresponds to the scheduled task execution date or the manual run date.

Query content field filled in with an example SQL query using the RUN_DATE variable

Saving results

To save the query results in BigQuery, enable the Save Query results switch. Without it, the query is only run and nothing is written.

The Table settings section appears. In Destination table, enter the name of the table the results are written to. The default is results_{id}: {id} becomes the Custom Query's own id, so every query gets a table of its own. The name may contain only letters, digits and _, is written in lowercase, and the table is always in your project's dataset - the text above the field shows where the results go.

Save Query results switched on, with the Table settings section - Destination table with its default results_ and where the results are written, Write disposition, Clustering and Partitioned

Two Custom Queries of a project cannot write to the same table.

Write disposition – the method of writing data:

  • Overwrite if table/partition exists – replaces the table, or with partitioning by Ingestion time only the partition of the run's date.
  • Append to table/partition – appends the results to the table.
  • Write only if table/partition is empty – writes only if the table (or the run date's partition) is empty.

Write disposition drop-down open with Overwrite if table/partition exists, Append to table/partition and Write only if table/partition is empty

With Append to table/partition, or Overwrite if table/partition exists on a table partitioned by Ingestion time, columns you add to the query are added to the table automatically.

Clustering

Clustering orders the stored data by up to 4 of the query's result columns. Enter the column names separated by commas, in the order you filter by most, e.g. channel, country. Leave it empty for no clustering.

Write disposition set to Overwrite if table/partition exists and Clustering filled in with channel, country

The columns must be among the query's result columns and of a type BigQuery can cluster on, and cannot repeat. When you change the clustering of a query whose table already exists, the table is changed with the next run.

Data partitioning

If you want the results to be saved in a partitioned table, enable the Partitioned option. The Partitioning settings section appears:

  • Partitioning by:
    • Ingestion time – each run writes the partition of its own date.
    • Custom field – partitions by a column of the query's results. Enter its name in the Custom field box; the column must be DATE, TIMESTAMP or DATETIME.
  • Partition type – Day, Month or Year, and Hour with a Custom field of type TIMESTAMP or DATETIME. The default is Day.
  • Partition expiration (days) – partitions older than this many days are deleted. Leave it empty to keep them forever.
  • Require partition filter – every query of this table must filter on the partitioning column (_PARTITIONTIME with Ingestion time), or BigQuery refuses it. That includes All in One, data sources and other queries that read this table, so switch it on only when they all filter on it.

Partitioning settings - partitioning by the custom field date, Partition type Day, Partition expiration 730 days and Require partition filter switched on with its warning

This allows you to easily manage large datasets and speed up processing in BigQuery.

Query testing

Before saving the configuration, click Test Query. This runs a dry run of the query, which costs nothing and writes no data. {{RUN_DATE}} is replaced with yesterday's date. The test also checks the Table settings against the columns the query returns.

Test Query and Create buttons below the form

If the query is correct, the Query success window shows how much data one run processes (Processes ... per run), the Test date, the Statement type, the tables it Reads and its Result columns.

Query success window with example dry run results - data processed per run, test date, statement type, the table it reads and the result columns

Otherwise the Query error window shows the error from BigQuery, or under Table settings what in the table settings does not fit the query (see Troubleshooting).

Query error window whose Table settings list says the example clustering column region is not among the query's columns

Click Create to save the module. Create runs the same test first: if it finds a problem, nothing is saved and the Not saved: fix the query window shows what to fix.

Done! The Custom Query module has appeared on the Integrations list and is now ready to use.

Remember that the table will appear only after the module is run in the Workflow!

To change a Custom Query later, open it from the Integrations list, change the settings and click Save. Save runs the test too.

Execution and scheduling

To run the module and set up its schedule, you need to configure a Workflow. You can read more about it here.

Troubleshooting

The test and Create/Save can report these problems with the table settings:

  • The table name cannot contain a dot: results always go to the project dataset. – enter the table name without a dataset.
  • The table name may contain only letters, digits and _. / The only placeholder a table name takes is {id}. – remove the other characters from Destination table.
  • Clustering takes at most 4 columns. / Clustering columns cannot repeat. – shorten the Clustering list.
  • Clustering column "…" is not among the query's columns / Partitioning column "…" is not among the query's columns – the column is missing from the query's results; check its name in Result columns.
  • Partitioning column "…" is STRING; it must be DATE, TIMESTAMP, DATETIME – partition by a date or time column, or convert the column in the query.
  • Query job "…" already writes to …; choose another destinationTable – another Custom Query of the project writes to this table; choose another Destination table.