Raw SQL

Raw SQL queries are currently in Beta. Please refer any issues to our github repo or contact us directly at support@redivis.com

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 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.

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. 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, diagnosis
FROM `Select cohort - output`
ORDER BY admit_date
LIMIT 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 ::integer or :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, diagnosis
FROM `Select cohort - output`
WHERE diagnosis IN @diagnoses::integer
ORDER BY admit_date
LIMIT 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.

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

  • 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

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.