SQL {SQL}

Run duckdb SQL statements on data frames

Description

Runs Duckdb SQL statements on in-memory data frames without registering nor copying them. Optionnaly sends the result of a query to a parquet file in an efficient way. Using the 'path' argument may be be twenty times faster than using the COPY ... TO SQL statement.

Usage

SQL(query,path)

Arguments

query

A character string containing SQL statements undertandable by Duckdb.

path

A path to a parquet file to be created.

Value

If 'path' is not given a value, returns the result of the query as a data frame, else returns an invisible NULL.

Examples

SQL("SELECT * FROM mtcars LIMIT 1")

# Temporary tables may be created but must be quoted when used
SQL("
  CREATE TABLE cyls AS SELECT DISTINCT cyl FROM mtcars;
  SELECT * FROM 'cyls'
")

## Not run: 
# Mixing data frames and parquet files, then writing to parquet
dep <- rio::import('V:/PALETTES/IGoR/data/dep2014.dbf')

SQL(
  "SELECT a.*,b.REGION
   FROM 'V:/PALETTES/parquet/rp68a19.parquet' a
   LEFT JOIN dep b
   ON a.DR=b.DEP",
  'V:/PALETTES/tmp/rp68a19s.parquet'
)
 
## End(Not run)