Notebook SQL Editor¶

Ibis is a great tool for abtracting SQL queries, and for more programmatic query generation. However, there are times when it is still useful to quickly test raw SQL. Fortunately, we can use an ibis connection and some display magics to accomplish this.

First, we make the relevant imports:

import ibis
import jupyterlab_omnisci

Now we make a connection to the database:

omnisci_cli = ibis.omniscidb.connect(
    host='metis.mapd.com', user='mapd', password='HyperInteractive',
    port=443, database='mapd', protocol= 'https'
)

And verify that the connection worked

omnisci_cli.list_tables()

['flights_donotmodify',
 'contributions_donotmodify',
 'tweets_nov_feb',
 'zipcodes_orig',
 'zipcodes',
 'demo_vote_clean',
 'us_faults',
 'zipcodes_2017',
 'us_county_level_tiger_edges_2018',
 'ca_roads_tiger',
 'input_node',
 'uk_wells',
 'RentalListings']

Let’s create a SQL editor from this client:

jupyterlab_omnisci.OmniSciSQLEditorRenderer(omnisci_cli)

<jupyterlab_omnisci.magics.OmniSciSQLEditorRenderer at 0x7f78d3873690>

That gave us a blank canvas for getting data from the database. However, it can be nice to start from an ibis expreesion and then vary it from there. To do that, we can give an expression as the second argument for the OmniSciSQLEditorRenderer:

table = omnisci_cli.table('tweets_nov_feb')
expr = table[table.goog_x.name('x'), table.goog_y.name('y'), table.tweet_id.name('rowid')]

jupyterlab_omnisci.OmniSciSQLEditorRenderer(omnisci_cli, expr)

<jupyterlab_omnisci.magics.OmniSciSQLEditorRenderer at 0x7f78dd0d1390>

Since Jupyter Book cannot render this output, this is a screenshot of how it looks in JupyterLab:

Jupyterlab OmniSci

Notebook SQL Editor¶