Parameterized queries parameterized-queries

Query Service supports the use of parameterized queries in the Query Editor. With parameterized queries, you can now use placeholders for parameters and add the parameter values at execution time. Placeholders allow you to work with dynamic data where you don’t know what the values will be until the statement is executed. You can also prepare your queries ahead of time and reuse them for similar purposes. Reusing queries saves considerable effort as you avoid creating distinct SQL queries for each use case.

Prerequisites

Before continuing with this guide, read the Query Editor UI guide. The Query Editor guide provides detailed information on how to write, validate, and run queries for customer experience data within the Experience Platform user interface.

Parameterized query syntax syntax

Parameterized queries use the format '$YOUR_PARAMETER_NAME' and can be concatenated using dot notation. An example SQL statement that uses parameterized queries can be seen below.

INSERT INTO
   $Database_Name.Schema_Name.adwh_lkup_process_delta_log
   (process_name, merge_policy_id, process_status, process_date, create_ts, change_ts)
SELECT
   '$Table_Process_Name' process_name,
   hash('$Merge_PolicyID') merge_policy_id,
   '$process_status' process_status,
   to_date('$date_key') process_date,
   CURRENT_TIMESTAMP create_ts,
   CURRENT_TIMESTAMP change_ts;

Create a parameterized query create

To create your parameterized query in the UI, navigate to the Query Editor. See the section on accessing the Query Editor for more instructions.

Use the '$' preface to enter a query parameter into your query in the text editor. Next, select the Query parameters tab next to the Console add the missing value for the key. The query cannot be executed if you neglect to add a value to any of the required keys. An alert icon ( ) appears in the Query Parameters section next to any empty Value input fields.

Use query logs details to check parameter values check-parameter-values

You cannot save parameters within templates as the values used are not persistent. However, you can check the Query log details page to find the parameter values used in a query run. In this case, the logs do not indicate that the query was a parameterized query. See the query logs documentation for instructions on how to find the values used.

Schedule a parameterized query schedule

Parameter values are saved when you schedule a parameterized query. To schedule a parameterized query, follow the typical process to create a scheduled query as described in the guide to create a query schedule, then enter the parameter values to be used in the query run. This UI section only appears for parameterized queries. See the section on setting parameters for a scheduled parameterized query for specific instructions.

Next steps

By reading this document, you have learned how to parameterize queries in the Adobe Experience Platform UI and use them in scheduled query runs. The document also highlighted how to check the logs for the parameter values used in query executions.

Next, you are recommended to read the guide on monitoring scheduled queries to gain a better understanding of the status of all query jobs through the Platform UI.