Raw SQL

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. 
WARNING: Editing the generated code will permanently erase any work in the transform's graphical user interface. When you first attempt to edit the code, you will be prompted with a warning before you can proceed.
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
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.
Cost saving tips!
Avoid SELECT * queries when possible, as costs are determined based on the size of all columns referenced.
Note that applying a LIMIT to your query will not affect costs.
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.
Parameters
Query optimization
Last modified 10mo ago