sql_editor.ipynb
OmniSci 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:
In [1]:
import ibis
import omnisci_renderer
Now we make a connection to the database:
In [2]:
omnisci_cli = ibis.mapd.connect(
host='metis.mapd.com', user='mapd', password='HyperInteractive',
port=443, database='mapd', protocol= 'https'
)
And verify that the connection worked
In [3]:
omnisci_cli.list_tables()
Out[3]:
Let's create a SQL editor from this client:
In [4]:
omnisci_renderer.OmniSciSQLEditorRenderer(omnisci_cli)
Out[4]:
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
:
In [5]:
table = omnisci_cli.table('tweets_nov_feb')
expr = table[table.goog_x.name('x'), table.goog_y.name('y'), table.tweet_id.name('rowid')]
omnisci_renderer.OmniSciSQLEditorRenderer(omnisci_cli, expr)
Out[5]: