Using Ibis and Altair for OmniSci¶

In this notebook, we will use Ibis to build OmniSci query and then use Altair to compose a visualization on top of it.

We will be plotting a map of a bunch of tweets.

Assembling the Query¶

First, let's connect to the omnisci backend and assemble the query:

In [1]:

import omnisci_renderer
import ibis
import altair as alt

C:\Anaconda3\lib\site-packages\ibis\sql\postgres\compiler.py:223: UserWarning: locale specific date formats (%c, %x, %X) are not yet implemented for Windows
  'for %s' % platform.system()

In [2]:

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

In [3]:

t = omnisci_cli.table('tweets_nov_feb')

In [4]:

expr = t[t.goog_x.name('x'), t.goog_y.name('y'), t.tweet_id.name('rowid')]
print(expr.compile())

SELECT "goog_x" AS x, "goog_y" AS y, "tweet_id" AS rowid
FROM tweets_nov_feb

Browser rendering¶

Now, let's first try rendering this by getting a Pandas DataFrame and rendering this on the browser

In [5]:

df = expr.execute()
df.head()

Out[5]:

	x	y	rowid
0	14901896.0	4116482.50	528340935361318912
1	16176363.0	-4547984.00	528340935378096128
2	-9273029.0	5176628.00	528340939178536960
3	3618257.5	4582324.00	528340939178524672
4	-5483653.0	-2930803.75	528340943553171456

This is a lot of data, so let's tell Altair to save it as a temporary JSON file.

This way it all won't be stored in the notebook

In [6]:

alt.data_transformers.enable('json')
alt.renderers.enable('default')
alt.Chart(df, width=384, height=564).mark_square(
    color='green',
    size=2,
    clip=True
).encode(
    alt.X(
        'x:Q',
        scale=alt.Scale(domain=[-3650484.1235206556, 7413325.514451755], range='width')
    ),
    alt.Y(
        'y:Q',
        scale=alt.Scale(domain=[-5778161.9183506705, 10471808.487466192], range='height')
    ),
)

Out[6]:

OmniSci Rendering¶

That looks OK, but we seem to be missing some data! Well omnisci's API has a default max row limit. Instead of increasing this, let's move all the computation to omnisci, so that we don't have to send all the data to the client, just the rendered graphic.

We enable the omnisci renderer with our connection info and then Altair will treat the string we pass in as a Mapd SQL query.

In [ ]:

alt.renderers.enable('omnisci', conn=omnisci_cli)
alt.Chart(expr.compile(), width=384, height=564).mark_square(
    color='green',
    size=2,
    clip=True
).encode(
    alt.X(
        'x:Q',
        scale=alt.Scale(domain=[-3650484.1235206556, 7413325.514451755], range='width')
    ),
    alt.Y(
        'y:Q',
        scale=alt.Scale(domain=[-5778161.9183506705, 10471808.487466192], range='height')
    ),
)

You see that we used the exact same Altair code, but we just changed the input. However, it does look a bit different because OmniScis vega backend doesn't behave the same as the standard javascript one.