Step: SQL query
Overview
Redivis supports direct SQL queries for advanced functionality and for users who prefer to work in a programmatic interface. You can add one or more SQL query steps to any transform.
This step accepts SQL code and will execute in the order it has been placed within the rest of the steps.
Query syntax
Referencing the source table
Redivis supports the BigQuery Standard SQL Syntax and features, with certain limitations. All queries must reference the SQL step's source, represented as _source_
in the query. If the SQL step is the first step in the transform, the source represents the transform's source table. Otherwise, the source represents the output of the previous step in the transform.
For example:
Joining tables
You can also reference any other table in the current project from your SQL step, following the same reference rules as those in the Redivis API. After typing the backtick character (`
), available tables will be presented as autocomplete options.
For example:
The query editor will automatically qualify referenced tables and value lists as you reference them, which ensures your query will continue to work if tables get renamed. In most cases, you can just reference the table by name, and the fully qualified reference will be inserted for you.
Referencing upstream variables
You can mix and match SQL steps with other step types within your transform. If you choose to do so, this will introduce some additional language semantics, since transforms allow variables with the same name to co-exist at any given step, delineated in the UI with the t1, t2, ...
and v
prefixes. The v
prefix denotes any newly created variable, while the tN
prefix denotes variables that have been joined from other tables.
In order to reference these variables, you must include the prefix in your SQL code before the variable name. The only exception to this rule is for variables prefixed by t0
— these should be referenced normally, allowing you to ignore these rules if you aren't mixing the SQL interface with other step types.
For example:
Referencing value lists
You may also use value lists in your query — they can be referenced as a SQL parameter, prefixed with the @
symbol. Note that all value lists are explicitly typed, and you will need to cast the value list if there is a type mismatch.
For example:
Limitations
DDL / DML queries are not supported
All queries must return fields of a valid Redivis type. The following BigQuery types are not supported in the result set, though they may be used internally within the query:
TIMESTAMP
NUMERIC
BIGNUMERIC
BYTES
INTERVAL
JSON
ARRAY
STRUCT
BigQuery ML syntax is not supported
BigQuery scripting language is not supported
Statements may only reference tables that exist in your project
Unnamed variables in the result are not supported
Duplicate variable names in the result are not supported
Errors
If your query is invalid, you will see an invalid icon and the error message displayed above your code. Transforms can't be run while any step is invalid.
Last updated