Skip to content

Latest commit

 

History

History
54 lines (33 loc) · 2.08 KB

sql.rst

File metadata and controls

54 lines (33 loc) · 2.08 KB
Raw

SQL

Execute a SQL query on one or more tables of a project. Permission to access values of these tables is required.

See more SQL query examples in the Project Tables sql section.

opal sql <CREDENTIALS> [OPTIONS] [XTRAS]

Options

Option Description
--project PROJECT, -pr PROJECT Source project name, that will be used to resolve the table names in the FROM statement. If not provided, the fully qualified table names must be specified in the query (escaped by backquotes: `<project>.<table>`).
--query QUERY, -q QUERY The SQL query
--format FORMAT, -f FORMAT The format of the output, can be json or csv. Default is csv.
--id-name ID_NAME, -in ID_NAME Name of the ID column name. Default is _id.

Example

Simple SQL query with CSV output:

opal sql --opal https://opal-demo.obiba.org --user administrator --password password --project CNSIM --sql "select * from CNSIM1 limit 10"

Simple SQL query with JSON output:

opal sql --opal https://opal-demo.obiba.org --user administrator --password password --project CNSIM --sql "select * from CNSIM1 limit 10" --format json

More advanced SQL query:

opal sql --opal https://opal-demo.obiba.org --user administrator --password password --project CNSIM --sql "select count(*) as N, avg(LAB_HDL) as HDL_AVG, GENDER from (select * from CNSIM1 union all select * from CNSIM2) where LAB_HDL is not null group by GENDER"

Simple SQL query with CSV output and without specifying a project in the arguments:

opal sql --opal https://opal-demo.obiba.org --user administrator --password password --sql "select * from `CNSIM.CNSIM1` limit 10"