Cloud Services JDBC SQL Shell with sqlline

Article

sween · 2 hr ago 2m read

InterSystems Cloud JDBC SQL Shell

A simple way to connect, explore, and debug InterSytems Cloud Services over JDBC with sqlline.

This post backs the open exhange submission that offers slightly more detailed on how to get parked on an InterSystems Cloud sql prompt quickly. Here will will connect to a single cloud sql over jdbc and perform some operations, and secondly connect to multiple and federate the sql to multiple deployments at once.

Quick Start

Going to keep this brief and hope it remains simple .

Get the app

https://github.com/sween/isc-cloud-jdbc-sql-shell

Download

Driver ☕
Certificate(s) for Deployments 📃

Build

cd isc-cloud-jdbc-sql-shell
docker build -t isc-cloud-jdbc-sql-shell .

Run

Fill out run.sh like its a form, includes credentials and jdbc url information

bash run.sh

sqlline

No sense in rehashing the extensive manual for sqlline once you are connected, but here is some example usage across a couple of InterSystems Cloud offers in the form of an article.

This is just an implementation of the great sqlline, with straightforward tls connectivity for simplicity and easy use.

Usage Examples

Session Logging

Here we will setup logging the shell to the output folder, and start interrogating the database. This will end up on your local system when you are done with the session.

!set script /output/debug_omop_jdbc.log

Worth showing this as if you havent already picked up on it, there is a `!command` interface to sqlline you can find with !help.

Create Schema and Copy Table

Not earth shattering, but, you can see the result in the Portal... here we create a schema and copy a table from one schema to the newly created schema.

sween@pop-os:~/Desktop/OMOP/isc-cloud-sql-shell$ bash run.sh
Starting sqlline and connecting to:
  URL    : jdbc:IRIS://k8s-0a6bc2ca-a8e3f174-84fc3b8135-aa1cd181c9825c06.elb.us-east-1.amazonaws.com:443/USER/:::true
  Driver : com.intersystems.jdbc.IRISDriver
  User   : SQLAdmin

Transaction isolation level TRANSACTION_REPEATABLE_READ is not supported. Default (TRANSACTION_READ_UNCOMMITTED) will be used instead.
sqlline version 1.12.0
ISC > CREATE SCHEMA OMOPCDM54BAK;
No rows affected (0.091 seconds)
ISC > CREATE TABLE OMOPCDM54BAK.person AS SELECT * FROM OMOPCDM54.person;
1,101,000 rows affected (5.84 seconds)
ISC >

Federated SQL Across Deployments

Sorta earth shattering... while connected to the deployment in context when you launched it, also connect to another database, of a different type of offer and do federated sql against them !all. There is a "certs" folder you can just go ahead and dump all of the certificates in there for the deployments at hand and rebuild the container at will.

sqlline supports `!connect` to add connections to your list so you can either select them, or do things against `!all`

🎉