Exploring CockroachDB with ipython-sql and Jupyter Notebook

Here's how ipython-sql can be leveraged in querying CockroachDB.

Artem Ervits

CORE ·

Jan. 20, 22 · Tutorial

Likes (4)

Comment

Save

2.1K Views

Today, I will demonstrate how ipython-sql can be leveraged in querying CockroachDB. This will require a secure instance of CockroachDB for the reasons I will explain below.

Running a secure docker-compose instance of CRDB is beyond the scope of this tutorial. Instead, I will publish everything you need to get through the tutorial in my repo, including the Jupyter Notebook. You may also use CRDB docs to stand up a secure instance and change the URL in the notebook to follow along.

This post will dive deeper into the Python ecosystem and build on my previous Python post. Instead of reliance on pandas alone, we're going to use a popular SQL extension called ipython-sql, a.k.a. SQLmagic to execute SQL queries against CRDB.

As stated earlier, we need to use a secure instance of CockroachDB. In fact, from this point forward, I will attempt to write posts only with secure clusters, as that's the recommended approach. Ipython-sql uses sqlalchemy underneath and it expects database URLs in the format postgresql://username:password@hostname:port/dbname. CockroachDB does not support password fields with insecure clusters, as passwords alone will not protect your data.

Install a Secure Instance of CockroachDB

Use the following steps to launch a secure instance of CockroachDB.
For advanced Docker users, I have a slightly modified docker-compose recipe that will install a secure three-node instance of CockroachDB and JupyterLab, along with the Jupyter Notebook to complete this tutorial here.

Launch the Jupyter Notebook

If you choose to run the tutorial from my repo, you will need to execute the script called up.sh. Credit to my colleague Tim Veil for building out the original secure three-node docker environment, on which this tutorial is built.

Open the Jupyter Notebook Called `crdb-sqlmagic.ipynb` in the Current Directory

I named the Jupyter environment jupyterlab as this is meant to be a stepping stone for my next article.

Grab the Notebook token with the command below:

docker logs jupyterlab

Notice the Notebook environment looks a bit different. It is because this is JupyterLab rather than Jupyter. Let's ignore that for now as I will dive deep into that in the follow-up posts.

Right-click the file and select Open With, then select Notebook.

You can also re-create the notebook with the following raw JSON, save it as an .ipynb extension, and open it within the Jupyter Notebook environment.

    JSON
   
 

   {
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Install the necessary libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install ipython-sql psycopg2-binary sqlalchemy-cockroachdb pandas matplotlib"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the sqlmagic extension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## instantiate the connection string for ipython-sql sqlmagic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql cockroachdb://maxroach@roach-0:26257/movr?sslmode=verify-full&sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslrootcert=/certs/ca.crt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Execute SQL queries against CockroachDB as you would in a native SQL client"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql select * from vehicles limit 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql select * from rides limit 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = _"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    " print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result[0].city"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Maintain connection to an existing cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "maxroach@movr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Using Pandas DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = %sql select city, count(city) from vehicles group by city;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe = result.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe.head()\n",
    "dataframe['city'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql DROP TABLE IF EXISTS dataframe;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The following will create a table and map datatypes as it assumes them, it expects that given table does not exist."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql PERSIST dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SHOW CREATE TABLE dataframe;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataframe schema\n",
    "```sql\n",
    "CREATE TABLE dataframe (\n",
    "\"index\" INT8 NULL,\n",
    "city STRING NULL,\n",
    "count INT8 NULL,\n",
    "INDEX ix_dataframe_index (\"index\" ASC),\n",
    "FAMILY \"primary\" (\"index\", city, count, rowid)\n",
    ");\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set row limit as you're likely run out of space"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = %sql SELECT city, count(city) FROM vehicles GROUP BY city LIMIT 500;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.bar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.pie()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Passing PostgreSQL `psql`-style \"backslash\" meta-commands to CockroachDB does not work today."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql \\d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql \\?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
  

Install the Necessary Libraries We Will Need to Complete the Tutorial

We will get all of the libs out of the way as I noticed SQLmagic loses context and errors out, requiring to reconnect to the database.

!pip install ipython-sql psycopg2-binary sqlalchemy-cockroachdb matplotlib pandas

You can launch the command by clicking into the cell within the notebook and clicking Run.

Load the SQLmagic Extension

%load_ext sql

The extension will load and there is no output.

Connect to CockroachDB

    JSON
   
   %sql cockroachdb://maxroach@roach-0:26257/movr?sslmode=verify-full&sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslrootcert=/certs/ca.crt

The command may look slightly different depending on your environment. Keep in mind that you need proper certificates and connectivity to the database from your Jupyter environment to make it work.

Execute SQL Against CockroachDB.

If you recall from my last Python post, we had to leverage pandas to execute SQL. Here, we're going to use native SQL.

%sql select * from vehicles limit 10;

%sql select * from rides limit 10;

More explanation can be found in the docs and a quick way to fix it is to re-execute the connection step:

%sql cockroachdb://maxroach@roach-0:26257/movr?sslmode=verify-full&sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslrootcert=/certs/ca.crt

Then, the error goes away. I still didn't figure out how to avoid this altogether.

Let's follow the steps on the project's site to complete the tour of ipython-sql with CockroachDB, starting with Step 3, as we already completed loading the extension and connecting to CockroachDB.

result = _
print(result)
result.keys
result[0][0]
result[0].city

Notice, result[0].description is replaced with result[0].city because I don't have that column in my movr.rides table.

Confirm connection is maintained just like in the ipython-sql wiki:

%%sql
maxroach@movr

You can also reference the result of the last command executed with:

print(_)

View SQLmagic Configuration Parameters

%config SqlMagic

Use Pandas With SQLmagic

result = %sql select city, count(city) from vehicles group by city;
dataframe = result.DataFrame()
dataframe.head()
dataframe['city'].head()

Persist Pandas Dataframe to CockroachDB

Having the result of the SQL query stored in pandas dataframe, we can persist the dataframe to CockroachDB.

%sql DROP TABLE IF EXISTS dataframe;

%sql PERSIST dataframe

Inspect the Newly Created Table

%sql SHOW CREATE TABLE dataframe;

Schema of the dataframe table will look like so:

CREATE TABLE dataframe (
"index" INT8 NULL,
city STRING NULL,
count INT8 NULL,
INDEX ix_dataframe_index ("index" ASC),
FAMILY "primary" ("index", city, count, rowid)
);

In case you have a large resultset, it's a good idea to limit the number of rows.

result = %sql SELECT city, count(city) FROM vehicles GROUP BY city LIMIT 500;

Use `matplotlib` to graph resultsets.

%matplotlib inline

result.bar()

result.pie()

Notice the invocation of the graphs is a bit different here than in my previous post.

Unfortunately, I was not able to pass PostgreSQL psql-style "backslash" meta-commands to CockroachDB. We are going to investigate this internally. Until then, the following won't work.

%sql \d

%sql \?

The entire executed notebook is below.

Install the Necessary Libraries

!pip install ipython-sql psycopg2-binary sqlalchemy-cockroachdb pandas matplotlib

Requirement already satisfied: ipython-sql in /opt/conda/lib/python3.7/site-packages (0.3.9)
Requirement already satisfied: psycopg2-binary in /opt/conda/lib/python3.7/site-packages (2.8.4)
Requirement already satisfied: cockroachdb in /opt/conda/lib/python3.7/site-packages (0.3.3)
Requirement already satisfied: pandas in /opt/conda/lib/python3.7/site-packages (0.25.3)
Requirement already satisfied: matplotlib in /opt/conda/lib/python3.7/site-packages (3.1.3)
Requirement already satisfied: sqlalchemy>=0.6.7 in /opt/conda/lib/python3.7/site-packages (from ipython-sql) (1.3.13)
Requirement already satisfied: ipython>=1.0 in /opt/conda/lib/python3.7/site-packages (from ipython-sql) (7.11.1)
Requirement already satisfied: sqlparse in /opt/conda/lib/python3.7/site-packages (from ipython-sql) (0.3.1)
Requirement already satisfied: prettytable in /opt/conda/lib/python3.7/site-packages (from ipython-sql) (0.7.2)
Requirement already satisfied: ipython-genutils>=0.1.0 in /opt/conda/lib/python3.7/site-packages (from ipython-sql) (0.2.0)
Requirement already satisfied: six in /opt/conda/lib/python3.7/site-packages (from ipython-sql) (1.14.0)
Requirement already satisfied: numpy>=1.13.3 in /opt/conda/lib/python3.7/site-packages (from pandas) (1.18.1)
Requirement already satisfied: python-dateutil>=2.6.1 in /opt/conda/lib/python3.7/site-packages (from pandas) (2.8.1)
Requirement already satisfied: pytz>=2017.2 in /opt/conda/lib/python3.7/site-packages (from pandas) (2019.3)
Requirement already satisfied: cycler>=0.10 in /opt/conda/lib/python3.7/site-packages (from matplotlib) (0.10.0)
Requirement already satisfied: kiwisolver>=1.0.1 in /opt/conda/lib/python3.7/site-packages (from matplotlib) (1.1.0)
Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /opt/conda/lib/python3.7/site-packages (from matplotlib) (2.4.6)
Requirement already satisfied: pickleshare in /opt/conda/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)
Requirement already satisfied: traitlets>=4.2 in /opt/conda/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.3.3)
Requirement already satisfied: pygments in /opt/conda/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (2.5.2)
Requirement already satisfied: pexpect; sys_platform != "win32" in /opt/conda/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)
Requirement already satisfied: jedi>=0.10 in /opt/conda/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.16.0)
Requirement already satisfied: decorator in /opt/conda/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.4.1)
Requirement already satisfied: backcall in /opt/conda/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.1.0)
Requirement already satisfied: setuptools>=18.5 in /opt/conda/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (45.1.0.post20200119)
Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /opt/conda/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (3.0.3)
Requirement already satisfied: ptyprocess>=0.5 in /opt/conda/lib/python3.7/site-packages (from pexpect; sys_platform != "win32"->ipython>=1.0->ipython-sql) (0.6.0)
Requirement already satisfied: parso>=0.5.2 in /opt/conda/lib/python3.7/site-packages (from jedi>=0.10->ipython>=1.0->ipython-sql) (0.6.0)
Requirement already satisfied: wcwidth in /opt/conda/lib/python3.7/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.1.8)

Load the SQLmagic Extension

%load_ext sql

Instantiate the connection string for ipython-sql SQLmagic.

%sql cockroachdb://maxroach@roach-0:26257/movr?sslmode=verify-full&sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslrootcert=/certs/ca.crt

'Connected: maxroach@movr'

Execute SQL queries against CockroachDB as you would in a native SQL client.

%sql select * from vehicles limit 10;

 * cockroachdb://maxroach@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt
10 rows affected.

id	city	type	owner_id	creation_time	status	current_location	ext
aaaaaaaa-aaaa-4800-8000-00000000000a	amsterdam	scooter	c28f5c28-f5c2-4000-8000-000000000026	2019-01-02 03:04:05	in_use	62609 Stephanie Route	{'color': 'red'}
bbbbbbbb-bbbb-4800-8000-00000000000b	amsterdam	scooter	bd70a3d7-0a3d-4000-8000-000000000025	2019-01-02 03:04:05	available	57637 Mitchell Shoals Suite 59	{'color': 'blue'}
22222222-2222-4200-8000-000000000002	boston	scooter	2e147ae1-47ae-4400-8000-000000000009	2019-01-02 03:04:05	in_use	19659 Christina Ville	{'color': 'blue'}
33333333-3333-4400-8000-000000000003	boston	scooter	33333333-3333-4400-8000-00000000000a	2019-01-02 03:04:05	in_use	47259 Natasha Cliffs	{'color': 'green'}
99999999-9999-4800-8000-000000000009	los angeles	scooter	9eb851eb-851e-4800-8000-00000000001f	2019-01-02 03:04:05	in_use	43051 Jonathan Fords Suite 36	{'color': 'red'}
00000000-0000-4000-8000-000000000000	new york	skateboard	051eb851-eb85-4ec0-8000-000000000001	2019-01-02 03:04:05	in_use	64110 Richard Crescent	{'color': 'black'}
11111111-1111-4100-8000-000000000001	new york	scooter	147ae147-ae14-4b00-8000-000000000004	2019-01-02 03:04:05	in_use	86667 Edwards Valley	{'color': 'black'}
cccccccc-cccc-4000-8000-00000000000c	paris	skateboard	c7ae147a-e147-4000-8000-000000000027	2019-01-02 03:04:05	in_use	19202 Edward Pass	{'color': 'black'}
dddddddd-dddd-4000-8000-00000000000d	paris	skateboard	cccccccc-cccc-4000-8000-000000000028	2019-01-02 03:04:05	available	2505 Harrison Parkway Apt. 89	{'color': 'red'}
eeeeeeee-eeee-4000-8000-00000000000e	rome	bike	fae147ae-147a-4000-8000-000000000031	2019-01-02 03:04:05	in_use	64935 Matthew Flats Suite 55	{'brand': 'Pinarello', 'color': 'blue'}

%sql select * from rides limit 10;

 * cockroachdb://maxroach@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt
10 rows affected.

id	city	vehicle_city	rider_id	vehicle_id	start_address	end_address	start_time	end_time	revenue
ab020c49-ba5e-4800-8000-00000000014e	amsterdam	amsterdam	c28f5c28-f5c2-4000-8000-000000000026	aaaaaaaa-aaaa-4800-8000-00000000000a	1905 Christopher Locks Apt. 77	66037 Belinda Plaza Apt. 93	2018-12-13 03:04:05	2018-12-14 08:04:05	77.00
ab851eb8-51eb-4800-8000-00000000014f	amsterdam	amsterdam	b851eb85-1eb8-4000-8000-000000000024	aaaaaaaa-aaaa-4800-8000-00000000000a	70458 Mary Crest	33862 Charles Junctions Apt. 49	2018-12-26 03:04:05	2018-12-28 10:04:05	81.00
ac083126-e978-4800-8000-000000000150	amsterdam	amsterdam	c28f5c28-f5c2-4000-8000-000000000026	aaaaaaaa-aaaa-4800-8000-00000000000a	50217 Victoria Fields Apt. 44	56217 Wilson Spring	2018-12-07 03:04:05	2018-12-07 10:04:05	9.00
ac8b4395-8106-4800-8000-000000000151	amsterdam	amsterdam	ae147ae1-47ae-4800-8000-000000000022	bbbbbbbb-bbbb-4800-8000-00000000000b	34704 Stewart Ports Suite 56	53889 Frank Lake Apt. 49	2018-12-22 03:04:05	2018-12-22 16:04:05	27.00
ad0e5604-1893-4800-8000-000000000152	amsterdam	amsterdam	ae147ae1-47ae-4800-8000-000000000022	aaaaaaaa-aaaa-4800-8000-00000000000a	10806 Kevin Spur	15744 Valerie Squares	2018-12-08 03:04:05	2018-12-08 22:04:05	20.00
ad916872-b020-4800-8000-000000000153	amsterdam	amsterdam	b3333333-3333-4000-8000-000000000023	bbbbbbbb-bbbb-4800-8000-00000000000b	51101 Cassandra Spring	96936 Parker Summit	2018-12-10 03:04:05	2018-12-12 02:04:05	22.00
ae147ae1-47ae-4800-8000-000000000154	amsterdam	amsterdam	bd70a3d7-0a3d-4000-8000-000000000025	aaaaaaaa-aaaa-4800-8000-00000000000a	63503 Lisa Summit Suite 28	26800 Brown Station	2018-12-25 03:04:05	2018-12-26 22:04:05	0.00
ae978d4f-df3b-4800-8000-000000000155	amsterdam	amsterdam	b851eb85-1eb8-4000-8000-000000000024	bbbbbbbb-bbbb-4800-8000-00000000000b	95059 Mendez Village Apt. 96	26739 Ellis Drive Apt. 91	2018-12-16 03:04:05	2018-12-16 21:04:05	51.00
af1a9fbe-76c8-4800-8000-000000000156	amsterdam	amsterdam	ae147ae1-47ae-4800-8000-000000000022	aaaaaaaa-aaaa-4800-8000-00000000000a	64807 Melissa Branch	32661 Dalton Flats Suite 70	2018-12-18 03:04:05	2018-12-18 12:04:05	87.00
af9db22d-0e56-4800-8000-000000000157	amsterdam	amsterdam	c28f5c28-f5c2-4000-8000-000000000026	aaaaaaaa-aaaa-4800-8000-00000000000a	20937 Gibson River	50480 Steven Row	2018-12-23 03:04:05	2018-12-25 11:04:05	88.00

result = _

 print(result)

+--------------------------------------+-------------+------------+--------------------------------------+---------------------+-----------+--------------------------------+-----------------------------------------+
|                  id                  |     city    |    type    |               owner_id               |    creation_time    |   status  |        current_location        |                   ext                   |
+--------------------------------------+-------------+------------+--------------------------------------+---------------------+-----------+--------------------------------+-----------------------------------------+
| aaaaaaaa-aaaa-4800-8000-00000000000a |  amsterdam  |  scooter   | c28f5c28-f5c2-4000-8000-000000000026 | 2019-01-02 03:04:05 |   in_use  |     62609 Stephanie Route      |             {'color': 'red'}            |
| bbbbbbbb-bbbb-4800-8000-00000000000b |  amsterdam  |  scooter   | bd70a3d7-0a3d-4000-8000-000000000025 | 2019-01-02 03:04:05 | available | 57637 Mitchell Shoals Suite 59 |            {'color': 'blue'}            |
| 22222222-2222-4200-8000-000000000002 |    boston   |  scooter   | 2e147ae1-47ae-4400-8000-000000000009 | 2019-01-02 03:04:05 |   in_use  |     19659 Christina Ville      |            {'color': 'blue'}            |
| 33333333-3333-4400-8000-000000000003 |    boston   |  scooter   | 33333333-3333-4400-8000-00000000000a | 2019-01-02 03:04:05 |   in_use  |      47259 Natasha Cliffs      |            {'color': 'green'}           |
| 99999999-9999-4800-8000-000000000009 | los angeles |  scooter   | 9eb851eb-851e-4800-8000-00000000001f | 2019-01-02 03:04:05 |   in_use  | 43051 Jonathan Fords Suite 36  |             {'color': 'red'}            |
| 00000000-0000-4000-8000-000000000000 |   new york  | skateboard | 051eb851-eb85-4ec0-8000-000000000001 | 2019-01-02 03:04:05 |   in_use  |     64110 Richard Crescent     |            {'color': 'black'}           |
| 11111111-1111-4100-8000-000000000001 |   new york  |  scooter   | 147ae147-ae14-4b00-8000-000000000004 | 2019-01-02 03:04:05 |   in_use  |      86667 Edwards Valley      |            {'color': 'black'}           |
| cccccccc-cccc-4000-8000-00000000000c |    paris    | skateboard | c7ae147a-e147-4000-8000-000000000027 | 2019-01-02 03:04:05 |   in_use  |       19202 Edward Pass        |            {'color': 'black'}           |
| dddddddd-dddd-4000-8000-00000000000d |    paris    | skateboard | cccccccc-cccc-4000-8000-000000000028 | 2019-01-02 03:04:05 | available | 2505 Harrison Parkway Apt. 89  |             {'color': 'red'}            |
| eeeeeeee-eeee-4000-8000-00000000000e |     rome    |    bike    | fae147ae-147a-4000-8000-000000000031 | 2019-01-02 03:04:05 |   in_use  |  64935 Matthew Flats Suite 55  | {'brand': 'Pinarello', 'color': 'blue'} |
+--------------------------------------+-------------+------------+--------------------------------------+---------------------+-----------+--------------------------------+-----------------------------------------+

result.keys

['id',
 'city',
 'type',
 'owner_id',
 'creation_time',
 'status',
 'current_location',
 'ext']

result[0][0]

UUID('aaaaaaaa-aaaa-4800-8000-00000000000a')

result[0].city

'amsterdam'

Maintain a Connection to an Existing Cluster

%%sql 
maxroach@movr

'Connected: maxroach@movr'

print(_)

Connected: maxroach@movr

%config SqlMagic

SqlMagic options
--------------
SqlMagic.autocommit=<Bool>
    Current: True
    Set autocommit mode
SqlMagic.autolimit=<Int>
    Current: 0
    Automatically limit the size of the returned result sets
SqlMagic.autopandas=<Bool>
    Current: False
    Return Pandas DataFrames instead of regular result sets
SqlMagic.column_local_vars=<Bool>
    Current: False
    Return data into local variables from column names
SqlMagic.displaylimit=<Int>
    Current: None
    Automatically limit the number of rows displayed (full result set is still
    stored)
SqlMagic.dsn_filename=<Unicode>
    Current: 'odbc.ini'
    Path to DSN file. When the first argument is of the form [section], a
    sqlalchemy connection string is formed from the matching section in the DSN
    file.
SqlMagic.feedback=<Bool>
    Current: True
    Print number of rows affected by DML
SqlMagic.short_errors=<Bool>
    Current: True
    Don't display the full traceback on SQL Programming Error
SqlMagic.style=<Unicode>
    Current: 'DEFAULT'
    Set the table printing style to any of prettytable's defined styles
    (currently DEFAULT, MSWORD_FRIENDLY, PLAIN_COLUMNS, RANDOM)

## Using Pandas DataFrames

result = %sql select city, count(city) from vehicles group by city;

 * cockroachdb://maxroach@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt
9 rows affected.

dataframe = result.DataFrame()

dataframe.head()
dataframe['city'].head()

	city	count
0	amsterdam	2
1	boston	2
2	los angeles	1
3	new york	2
4	paris	2

%sql DROP TABLE IF EXISTS dataframe;

 * cockroachdb://maxroach@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt
Done.

[]

The following will create a table and map datatypes as it assumes them, it expects that given table does not exist.

%sql PERSIST dataframe

 * cockroachdb://maxroach@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt

'Persisted dataframe'

%sql SHOW CREATE TABLE dataframe;

 * cockroachdb://maxroach@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt
1 rows affected.

table_name	create_statement
dataframe	CREATE TABLE dataframe ( "index" INT8 NULL, city STRING NULL, count INT8 NULL, INDEX ix_dataframe_index ("index" ASC), FAMILY "primary" ("index", city, count, rowid) )

Dataframe schema:

CREATE TABLE dataframe (
"index" INT8 NULL,
city STRING NULL,
count INT8 NULL,
INDEX ix_dataframe_index ("index" ASC),
FAMILY "primary" ("index", city, count, rowid)
);

Set row limit, as you're likely to run out of space.

result = %sql SELECT city, count(city) FROM vehicles GROUP BY city LIMIT 500;

 * cockroachdb://maxroach@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt
9 rows affected.

%matplotlib inline

result.bar()

<BarContainer object of 9 artists>

result.pie()

%sql \d

 * cockroachdb://maxroach@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt
(psycopg2.errors.SyntaxError) at or near "\": syntax error
DETAIL:  source SQL:
\d
^

[SQL: \d]
(Background on this error at: http://sqlalche.me/e/f405)

%sql \?

 * cockroachdb://maxroach@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt
(psycopg2.errors.SyntaxError) at or near "\": syntax error
DETAIL:  source SQL:
\?
^

[SQL: \?]
(Background on this error at: http://sqlalche.me/e/f405)

This concludes the tour of CockroachDB through the lens of ipython-sql. Hopefully it was of value!

jupyter notebook CockroachDB Database sql

Published at DZone with permission of Artem Ervits. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

Trending

Exploring CockroachDB with ipython-sql and Jupyter Notebook

Here's how ipython-sql can be leveraged in querying CockroachDB.

Install a Secure Instance of CockroachDB

Launch the Jupyter Notebook

Open the Jupyter Notebook Called crdb-sqlmagic.ipynb in the Current Directory

Install the Necessary Libraries We Will Need to Complete the Tutorial

Load the SQLmagic Extension

Connect to CockroachDB

Execute SQL Against CockroachDB.

View SQLmagic Configuration Parameters

Use Pandas With SQLmagic

Persist Pandas Dataframe to CockroachDB

Inspect the Newly Created Table

Use matplotlib to graph resultsets.

Install the Necessary Libraries

Load the SQLmagic Extension

Maintain a Connection to an Existing Cluster

Related

Partner Resources

Open the Jupyter Notebook Called `crdb-sqlmagic.ipynb` in the Current Directory

Use `matplotlib` to graph resultsets.