Query

class Query

Used to execute a SQL query against table(s) in Redivis, using the Redivis SQL query syntax, and read out the results.

Constructors

redivis$query(query_string)	Execute a SQL query within the current default scope (either a dataset or project). In a Redivis notebook, the default scope will always be the notebook's project, and the notebook's source table can be referenced via the _source_ identifier. If no default scope is specified, all tables in the query must be fully qualified. Consult the referencing resources documentation to learn more.
Dataset$query(query_string)	Execute a SQL query scoped to a specific dataset. Tables referenced by the query do not need to be fully qualified, since the table lookup is already scoped to the dataset. Consult the referencing resources documentation to learn more.
Project$query(query_string)	Execute a SQL query scoped to a specific project. Tables referenced by the query do not need to be fully qualified, since the table lookup is already scoped to the dataset. Consult the referencing resources documentation to learn more.

Examples

Basics

# Execute any SQL query and read the results
query <- redivis::query("SELECT 1 + 1 AS two, 'foo' AS bar")
query$to_tibble()
# 	two	bar
# 0	2	foo

# The query can reference any table on Redivis 
query <- redivis::query("
    SELECT * 
    FROM demo.iris_species.iris 
    WHERE SepalLengthCm > 5
")
query::to_tibble()
# 	Id	SepalLengthCm	SepalWidthCm	PetalLengthCm	PetalWidthCm	Species
# 0	33	5.2	        4.1	        1.5	        0.1	        Iris-setosa
# ...

# Other methods to read data:
# query$to_arrow_batch_reader()
# query$to_arrow_dataset()
# query$to_arrow_dataset()
# query$to_data_frame()
# query$to_data_table()
# query$to_sf_tibble()

Scoped queries

# To simplify table references, execute a query scoped to a dataset or project
dataset = redivis.organization("Demo").dataset("CMS 2014 Medicare Data")
query = dataset.query("""
    SELECT 
        hospice_providers.name, 
        inpatient_charges.drg_definition
    -- The tables inpatient_chargers, hospice_providers are assumed to be 
    -- within the scoped dataset
    FROM inpatient_charges
    INNER JOIN hospice_providers 
        ON hospice_providers.provider_id = inpatient_charges.provider_id
""")

# In a notebook, all queries are scoped to the current project.
# Additionally, the notebooks source table can simply be referenced as _source_
query = redivis.query("SELECT * FROM _source_ LIMIT 10")

Fields

properties

A named list containing the API resource representation of the query. This will always be populated after the query has been created, and can be refreshed by calling query.get()

Methods

Query$download_files([path, *, overwrite, ...])	Download all files represented by a file_id variable in the query results to a local directory.
Query$get()	Fetch query metadata. Once called, the properties attribute on the query will be fully populated.
Query$list_files([max_results, ...])	Return a list of File instances for query results containing a file_id variable.
Query$to_arrow_batch_reader([...])	Returns a reader that mimics the Arrow RecordBatchStreamReader, which can then be consumed to process batches of rows in a streaming fashion.
Query$to_arrow_dataset([max_results, ...])	Return an Arrow Dataset for the table. Data is backed by disk, allowing for larger-than-memory analysis.
Query$to_arrow_table([max_results, ...])	Return an Arrow Table with the table's data. This is the highest-performance option for loading data in-memory.
Query$to_data_frame([max_results, ...])	Return a data.frame with the table's data.
Query$to_data_table([max_results, ...])	Return a data.table with the table's data.
Query$to_tibble([max_results, variables, ...])	Return a tibble with the table's data.
Query$to_sf_tibble([max_results, ...])	Return a simple features tibble with a table's data. Used for tables that contain a geography variable.