Transformation variables allow you to create special types of content that can then be referenced via a placeholder in SQL code.
Create variable
- Click the ‘+’ button in the variable section.
- Choose variable type: String, Integer or Float.
- Enter a unique variable name. It should consist of alphanumeric characters and underscores, should always start with a letter, and do not have any kind of leading sigil (that is, they look like
var_name
). - Enter a value.
- Click the ‘Save variable’ button.
The list of Transformation variables displays in the left sidebar.
Using variables in SQL code
An output statement is a set of double curly braces containing a variable name. Here is a simple example: {{var_name}}
.
Example SQL code with variables:
WITH
--Inputs
dates AS
(
SELECT
'{{start_suffix}}' AS s,
'{{end_suffix}}' AS e
),
streaming AS
(
(
SELECT
PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS date,
'site.com' AS dataSource,
s.* EXCEPT(date)
FROM
dates AS d, `{{project_id}}.{{dataset_id_streaming}}.owoxbi_sessions_*` AS s
WHERE
_TABLE_SUFFIX BETWEEN d.s AND d.e
)
),
When the Transformation is run, an output statement in SQL code gets replaced with the value of that variable. The compiled SQL code you can find in the “Run history” tab. Read more in this article.
Edit variable
To edit a variable, click on it in the list. Then you can change the variables settings: type, name, and value. Click the ‘Save variable’ button to apply changes and close the popup.
Delete variable
To delete a variable, click on it in the list. Then click the Delete button in the opened popup. After that check, your code does not use this variable.
0 Comments