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 minimap 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. All table names are represented by their parent transform followed by
- output , and must be delineated by backticks. For example, if we wanted to select the first 100 records from the result of a transform named "Select cohort", we would write:
SELECT patient_id, patient_name, diagnosisFROM `Select cohort - output`ORDER BY admit_dateLIMIT 100
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 injected as an array of string-like values — if you would like to cast your list as a float or integer, you may explicitly do so by using the
:float syntax. For example, if we wanted to expand on our query above to select for specific diagnoses, and given a "diagnosis" variable that is of type integer, we would write:
SELECT patient_id, patient_name, diagnosisFROM `Select cohort - output`WHERE diagnosis IN @diagnoses::integerORDER BY admit_dateLIMIT 100-- @diagnoses:-- ['123', '456', '789']
Join and set operations are fully supported, and will be reflected on the Minimap when additional tables are referenced in your code.
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:
DML / DDL statements are not supported, and statements may not modify any existing tables
BigQuery ML syntax 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.