SQL Kernel

SQL (Structured Query Language) is one of the oldest and most popular languages for database access. BeakerX has first-class support for SQL, including syntax highlighting, autocompletion, and autotranslation to JavaScript (and more languages coming). Most of the languages in BeakerX have their own SQL APIs, and you can use them as well.

In order to be completely self-contained, this demo notebook uses an in-memory H2 database that's created and populated here before we query it. Normally you would connect to an existing database by putting its JDBC configuration in the %defaultDatasource magic. You can access more than one database from the same notebook with the %datasources magic.

You can also set the default data source with the BEAKERX_SQL_DEFAULT_JDBC environment variable.

For example, if you want to connect to a MySQL running locally then use jdbc:mysql://localhost/dbName, where dbName would be replaced as appropriate.

The BeakerX SQL kernel comes with drivers for H2, PostgreSQL, MySQL, Derby, HyperSQL, SQLite, Microsoft SQL Server, and Amazon Redshift built in. To access another database, you can load the JDBC driver with the %classpath add jar magic first.

In [ ]:
%defaultDatasource jdbc:h2:mem:db
In [ ]:
DROP TABLE IF EXISTS cities;
CREATE TABLE cities(
    zip_code varchar(5),
    latitude float,
    longitude float,
    city varchar(100),
    state varchar(2),
    county varchar(100),
    PRIMARY KEY (zip_code),
) AS SELECT
    zip_code,
    latitude,
    longitude,
    city,
    state,
    county
FROM CSVREAD('../resources/data/UScity.csv')
In [ ]:
SELECT * FROM cities WHERE state = 'NY'

Autotranslation of results

In [ ]:
SELECT * INTO ${citiesTable} FROM cities WHERE longitude BETWEEN -80 and -79;
In [ ]:
%%javascript
var tableStart = "<table border>"
var tableTemplate = (zip, lat, long, city, state) => `<tr><td>${city}</td><td>${state}</td></tr>`
var tableFinish = "</table>"
beakerx.displayHTML(this, tableStart +
                          beakerx.citiesTable.values.reduce((tmp, item) => tmp + tableTemplate(...item), "") +
                          tableFinish)
In [ ]:
%%groovy
beakerx.citiesTable.values[0]
In [ ]:
%%python
from beakerx import beakerx
beakerx.citiesTable.loc[0]

Autocomplete

In [ ]:
s
In [ ]:
SELECT * FROM cities WHE
In [ ]:
INSERT INTO cities (zip_code, city, state) VAL