Overview

Redivis supports raw SQL queries for advanced functionality and for users who prefer to work in a programmatic interface. Any transform within a project can be converted to SQL, while persisting the project functionality of displaying joined tables and decomposing your project into clear steps.

To convert a transform into SQL mode, click the code button at the top-right of the transform to view the generated SQL. As soon as you begin editing this SQL, you will be prompted to convert the transform into SQL mode; if you proceed, you will have a full featured SQL editor.

Query syntax

Redivis supports the BigQuery Standard SQL Syntax and features. When reference tables on Redivis, this interface follows the same reference rules as those in the Redivis API. All tables referenced must be available within your project. After typing the backtick character (`), available tables will be presented as autocomplete options.

For example:

SELECT provider_id 
FROM `ianmathews91.medicare_public_example.high_cost_in_providers_in_ca_output` 
ORDER BY mean_drg_cost
LIMIT 1000

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 must be explicitly typed by providing the type as a suffix, e.g. in the form :integer. For example, if we wanted to expand on our query above to select for specific states, we would write:

SELECT provider_id 
FROM `ianmathews91.medicare_public_example.high_cost_in_providers_in_ca_output` 
WHERE provider_state IN @imathews91.medicare_public_example.states:string
ORDER BY mean_drg_cost
LIMIT 1000
​
-- @states = ['CA', 'TX', etc...]

Limitations

• DDL / DML queries are not supported

• All queries must return fields of a valid Redivis type. The following BigQuery types are yet supported in the result set, though they may be used internally within the query:

  • TIMESTAMP

  • NUMERIC

  • ARRAY

  • STRUCT

  • GEOGRAPHY

• BigQuery ML syntax is not supported

• BigQuery scripting language is not supported

• Statements may only reference tables that exist in your project

Performance

Please consult the BigQuery performance best practices documentation.

Costs

Compute cost is displayed just as in the GUI interface; hover over the run button to see estimated costs. We recommend reviewing the BigQuery cost best practices to limit your costs. Raw queries are subject to the same max costs as other queries.

Errors

If your query is invalid, the "Run" button will be marked as grey — hover over this button to see any errors that are currently in your query.