Using JupySQL with ClickHouse
In this guide we'll show an integration with ClickHouse.
We will use JupySQL to run queries on top of ClickHouse. Once the data is loaded, we'll visualize it via SQL plotting.
The integration between JupySQL and ClickHouse is made possible by the use of the clickhouse_sqlalchemy library. This library allows for easy communication between the two systems, and enables users to connect to ClickHouse and pass the SQL dialect. Once connected, users can run SQL queries directly from the Clickhouse native UI, or from the Jupyter notebook directly.
Note: you may need to restart the kernel to use updated packages.
You'd need to make sure your Clickhouse is up and reachable for the next stages. You can use either the local or the cloud version.
Note: you will need to adjust the connection string according to the instance type you're trying to connect to (url, user, password). In the example below we've used a local instance. To learn more about it, check out this guide.
- clickhouse://default:***@localhost:8123/default Done.
- clickhouse://default:***@localhost:8123/default Done.
- clickhouse://default:***@localhost:8123/default Done.
| count() |
|---|
| 1999657 |
- clickhouse://default:***@localhost:8123/default Done.
| pickup_ntaname |
|---|
| Morningside Heights |
| Hudson Yards-Chelsea-Flatiron-Union Square |
| Midtown-Midtown South |
| SoHo-Tribeca-Civic Center-Little Italy |
| Murray Hill-Kips Bay |
- clickhouse://default:***@localhost:8123/default Done.
| round(avg(tip_amount), 2) |
|---|
| 1.68 |
- clickhouse://default:***@localhost:8123/default Done.
| passenger_count | average_total_amount |
|---|---|
| 0 | 22.69 |
| 1 | 15.97 |
| 2 | 17.15 |
| 3 | 16.76 |
| 4 | 17.33 |
| 5 | 16.35 |
| 6 | 16.04 |
| 7 | 59.8 |
| 8 | 36.41 |
| 9 | 9.81 |
- clickhouse://default:***@localhost:8123/default Done.
| pickup_date | pickup_ntaname | number_of_trips |
|---|---|---|
| 2015-07-01 | Bushwick North | 2 |
| 2015-07-01 | Brighton Beach | 1 |
| 2015-07-01 | Briarwood-Jamaica Hills | 3 |
| 2015-07-01 | Williamsburg | 1 |
| 2015-07-01 | Queensbridge-Ravenswood-Long Island City | 9 |
- clickhouse://default:***@localhost:8123/default Skipping execution...
