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

SQL Dialect

Redivis supports the BigQuery Standard SQL Syntax and features, with certain limitations.

Referencing the source table

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:

SELECT * 
FROM _source_
ORDER BY mean_drg_cost
LIMIT 1000

Referencing other tables

You can also reference any other table in the current workflow from your SQL step, following the same reference rules as those in the Redivis API. Start typing a table's name to select it from the autocomplete options.

For example:

SELECT t0.id, t0.name, t1.latitude, t1.longitude
FROM _source_ AS t0
INNER JOIN `demo.ghcn_daily_weather_data:7br5:v1_1.stations:g2q3` AS t1
    ON t0.id = t1.id

The query editor will automatically qualify referenced tables and parameters as you reference them, which ensures your query will continue to work if tables or parameters get renamed. In most cases, you can just reference the table by name, and the fully qualified reference will be inserted for you.

Referencing parameters

You may also use parameters in your query as a SQL parameter, prefixed with the @ symbol. Note that all parameters are arrays of a given type, and you will need to cast the parameters if there is a type mismatch.

For example:

SELECT provider_id 
FROM _source_
WHERE provider_state IN UNNEST(@`states:8a2h`) 
    -- If a parameter is in the incorrect type, use the following code to convert it
    OR provider_fips IN UNNEST(
        (
            SELECT ARRAY_AGG(CAST(val AS STRING))
            FROM UNNEST(@`fips_codes:134a`)val
        )
    )
ORDER BY mean_drg_cost
LIMIT 1000

-- @states = ['CA', 'TX', etc...]
-- @fips_codes = [6071, 2999, 3242]

Referencing upstream variables

You can mix and match SQL steps with other step types within your transform. If you choose to do so, this may introduce additional semantics, since transforms allow variables with the same name to co-exist at any given step, delineated in the UI with the t0, t1, ... and v prefixes. The v prefix denotes any newly created variable, while the t# prefix denotes variables that have been joined from other tables.

If there is a name conflict with an upstream variable, you must reference that variable with its source prefix; e.g., t0_id or v_myvar. If there is no conflict, you may reference the variable using its source prefix, though the prefix can also be omitted (e.g., id, myvar).

For example:

SELECT 
-- If variables have a name conflict in the SQL step's source, they must be prefixed
    t0_id,   -- An original input variable to this transform, shown as "t0"
    t1_name, -- joined upstream, shown as "t1"
    v_year   -- created upstream
-- Otherwise, you can just reference the variable by name (though prefixes are still allowed)
    latitude, 
    longitude
FROM _source_

Syntax errors

If your query is invalid, you will see an invalid icon and the error message displayed above your code, as well as the relevant part of your SQL query highlighted. Hover over any of the invalid indicators to see the full error message.

Transforms can't be run while any step is invalid.

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

• Recursive CTEs can only exist in transforms with a single SQL step

• Statements may only reference tables that exist in your workflow

• Unnamed variables in the result are not supported

• Duplicate variable names in the result are not supported