opalr (version 3.3.1)

opal.sql: Execute a SQL query on tables

Description

The SQL query can apply to raw tables and/or views and require the permission to view the values of these tables. When all tables belong to a project, it is possible to simplify the SQL query by providing the project name parameter. Otherwise the fully qualified table names (`<project>.<table>`) must be specified in the FROM statements.

Usage

opal.sql(opal, query, project = NULL, id.name = "_id")

Value

The lists of columns and rows, as a data.frame.

Arguments

opal

Opal connection object.

query

The SQL query statement.

project

Project name where the table(s) are located. If not provided, the SQL query must refer to the full table name(s) (use backquotes to escape, see examples).

id.name

The name of the column representing the entity identifiers. Default is '_id'.

Examples

if (FALSE) {
o <- opal.login('administrator','password', url='https://opal-demo.obiba.org')
# with project context
opal.sql(o,  
  'select avg(LAB_HDL) as HDL_AVG, GENDER 
     from CNSIM1 
     where LAB_HDL is not null 
     group by GENDER',
  'CNSIM')
# without project context
opal.sql(o,
  'select avg(LAB_HDL) as HDL_AVG, GENDER 
     from `CNSIM.CNSIM1` 
     where LAB_HDL is not null 
     group by GENDER')
opal.logout(o)
}