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.
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.
SELECT provider_idFROM `ianmathews91.medicare_public_example.high_cost_in_providers_in_ca_output`ORDER BY mean_drg_costLIMIT 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_idFROM `ianmathews91.medicare_public_example.high_cost_in_providers_in_ca_output`WHERE provider_state IN @imathews91.medicare_public_example.states:stringORDER BY mean_drg_costLIMIT 1000-- @states = ['CA', 'TX', etc...]
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:
BigQuery ML syntax is not supported
BigQuery scripting language is not supported
Statements may only reference tables that exist in your project
Please consult the BigQuery performance best practices documentation.
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.
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.