Relational Databases

Following commands allow reading data from relational databases (implemented using Java Database Connectivity (Jdbc) framework):

JDBC_TABLE '(' uri::STRING [,table::STRING] [,username::STRING] [,password::STRING] ')'

JDBC_QUERY '(' uri::STRING ,query::STRING [,username::STRING] [,password::STRING] ')'

where:

  • uri - is Jdbc connection string. The format of string depends on the particular Jdbc driver of the database.
  • table - name of the table. JDBC_TABLE constructs SQL query retrieving all rows in the table. When omitted then SQL query is constructed retrieving the list of all tables in all schemas.
  • query - the query to execute in the database.
  • username - username of database user. Optional.
  • password - password of database user. Optional.

Note that you need to enable respective Jdbc drivers in configuration.

Example 1: Selecting all rows from table ‘example’ in PostgreSQL database ‘test’

1
JDBC_TABLE(uri:'jdbc:postgresql://127.0.0.1/test', table:'example', username:'postgres', password:'test');

Example 2: executing select statement in a SQLite database

1
JDBC_QUERY(uri:'jdbc:sqlite:/tmp/example.db', query:'SELECT * FROM mytable WHERE id=1');

Example 3: retrieving list of all tables from an Oracle database

1
JDBC_TABLE(uri:'jdbc:oracle:thin:@localhost:1521:ORCLCDB', username:'scott', password:'tiger')

Jdbc Drivers

See details of driver installation in Admin Manual Relational Database Connectivity section.

Protecting Confidentiality of Credentials

Information security best practice says that usernames and passwords are personal. And just like as any other personal items (such as underwear) they are not to be shared with others. Therefore query scripts containing Jdbc connection strings with passwords should be kept in user private directory (i.e under /user/).

When such query scripts are meant to be shared between users, then we recommend to declare the stream in a separate script file which can be included by each analysis script. The stream declaration must be placed under /system/ directory or protected by Access Control from viewing while allowing execution.

Example:

Include script file stored at: /system/employeeTable.sx:

1
2
@exampleTable = JDBC_TABLE(uri:'jdbc:postgresql://127.0.0.1/test', table:'employees',
                           username:'postgres', password:'test');

Shared query script file stored at: /shared/exampleQuery.sx

1
2
3
4
include "/system/employeeTable.sx";

@exampleTable
  .select(name, id);