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

Credentials

Authentication can be done by username/password credentials OR by personal access token OR by certificate/private key pair (two-way SSL authentication).

Option	Description
`--opal OPAL, -o OPAL`	Opal server base url
`--user USER, -u USER`	Credentials auth: user name (requires a password)
`--password PASSWORD, -p PASSWORD`	Credentials auth: user password (requires a user name)
`--token TOKEN, -tk TOKEN`	Token auth: user’s personal access token
`--ssl-cert SSL_CERT, -sc SSL_CERT`	Two-way SSL auth: certificate/public key file (requires a private key)
`--ssl-key SSL_KEY, -sk SSL_KEY`	Two-way SSL auth: private key file (requires a certificate)

Extras

Option	Description
`-h, --help`	Show the command help’s message.
`--verbose, -v`	Verbose output.
`--json, -j`	Output pretty-print JSON

Example

Simple SQL query with CSV output:

opal sql --opal https://opal-demo.obiba.org --user administrator --password password --project CNSIM --query "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 --query "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 --query "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 --query "select * from `CNSIM.CNSIM1` limit 10"

You can also escape the backticks (`) when executing from a Linux terminal (Bash)

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