Custom parameters on the query editor

Custom parameters allow you to define parameters for your queries along with their values.

Custom parameters help streamline query executions and define scalable and reusable queries by removing the need to hard-code values in the query. Custom parameters give you the ability to update your query easily and run it for a different set of values without the need to edit the SQL code.

In this section, you’ll learn how to define custom parameters, invoke them in your SQL query, and provide values to parameters prior to running your query. The parameters defined using custom parameters can then be added to your SQL query as placeholders. They’re replaced by parameter values during query execution.

For more in-depth instructions on using custom parameters, refer to the Instructional Query: How to use Custom Parameters.

Basics

A custom parameter is a placeholder in your query that will be replaced by values at execution time. Custom parameters provide you with the ability to define scalable and reusable queries, by separating your SQL code from the query parameter definition and values.

Custom parameters in AMC are declared in the query editor page. To enable the custom parameters view, use the toggle available at the top right of the query editor page to display the custom parameters pane. The custom parameters pane allows you to declare custom parameters in a YAML syntax. YAML is a human-readable data serialization language. The syntax is easy to read and can be defined in the form of a list using Key: Value pairs.

For example: dataType: LONG

For our queries in AMC, we’ll use the YAML syntax to define custom parameters.

Syntax

Below is the syntax for a custom parameter that allows you to specify a single value:

<Name - define the name of your parameter here>:
    description: "<Add your description>"
    dataType: STRING
    value:
        - <value1>

Below is the syntax for a custom parameter that allows you to specify multiple values:

<Name - define the name of your parameter here>:
    description: "<Add your description>"
    dataType: 
       type: ARRAY
       elementDataType: <assign a data type> 
    value:
        - <value1>
        - <value2>

name: Define the name of your custom parameter that you’ll use to reference in your SQL query. The name can’t have spaces or special characters, with underscore being an exception. The name is case-sensitive. For example: campaign_id.

description: Provide a clear description for your custom parameter. This helps clarify the purpose of the parameter and makes it easier to provide values prior to execution. For example: description: "List of Campaign IDs to perform overlap with." The description field is optional.

dataType: Assign a data type to your custom parameter. In instructional queries (IQs), custom parameters can be used to define a list of values, by specifying ARRAY as the dataType.

Below are some valid simple dataTypes:

• INTEGER
• STRING
• LONG

Below is an example of a complex dataType:

• ARRAY

elementDataType: For complex dataTypes, you may assign a data type to the element.

dataType:   
    type: ARRAY  
    elementDataType: STRING

value: Provide values for your custom parameter. The values provided in this field replace the parameter name in the SQL query at execution.

Example

dsp_campaigns:
    description: List of DSP campaign IDs to perform overlap with.
    dataType: 
        type: "ARRAY"
        elementDataType: "LONG"

    value:
        - 1111111111111111
        - 2222222222222222
        - 3333333333333333
        
sa_campaigns:
    description: List of SA campaign names to perform overlap with.
    dataType: 
        type: "ARRAY"
        elementDataType: "STRING"    
    value:
    - CampaignName1
    - CampaignName2
    - CampaignName3

Assigning values to custom parameters

Before clicking Run on your SQL query, you’ll be able to edit these values per your use case. This allows your SQL code to remain intact, while the new values are passed during execution, and will make your SQL query scalable and reusable.

You can specify the value as shown below:

<Name - define the name of your parameter here>:
    description: "<Add your description>"
    dataType: STRING
    value:
        - <value1>

You may specify values prefixed with an ‘-’, separated by a newline. (YAML Array Syntax). This aligns with our recommended best practices, as special characters and commas that may be part of campaign names are automatically handled, if values are prefixed with an ‘-’ and separated by a new line.

You may also specify values using a regular Array syntax.

value: [value1,value2,value3,value4,value5]

However, if you have commas in your campaign names, you’ll have to enclose your campaign name in quotes. This to avoid having the comma as an interpretation to separate the name into values of the array.

Example: Campaign name is campaign,Name2

value: [campaignName1,"campaign,Name2"]

Invoking custom parameters in queries

You can reference custom parameters by enclosing them in the inbuilt function (CUSTOM_PARAMETER('<param1>'))

Syntax

SELECT 
    <column1>, <column2> 
FROM
    <table1>
WHERE 
    ARRAY_CONTAINS(CUSTOM_PARAMETER('<param1>'),<column1>)

Example

SELECT 
    campaign_id, campaign, sum(impressions)
FROM 
    dsp_impressions
WHERE 
    ARRAY_CONTAINS(CUSTOM_PARAMETER('DSP_CAMPAIGNS'),campaign_id)
GROUP BY 
    1,2