Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Utiliser JupySQL avec ClickHouse

View as MarkdownEdit this pageReport an issueSet up docs MCP
Géré par la communauté

Dans ce guide, nous allons présenter une intégration avec ClickHouse.

Nous utiliserons JupySQL pour exécuter des requêtes sur ClickHouse. Une fois les données chargées, nous les visualiserons à l’aide de graphiques SQL.

L’intégration entre JupySQL et ClickHouse est rendue possible par l’utilisation de la bibliothèque clickhouse_sqlalchemy. Cette bibliothèque facilite la communication entre les deux systèmes et vous permet de vous connecter à ClickHouse et de spécifier le dialecte SQL. Une fois connecté, vous pouvez exécuter des requêtes SQL directement depuis l’UI native de ClickHouse, ou directement depuis le Jupyter notebook.

# Install required packages
%pip install --quiet jupysql clickhouse_sqlalchemy

Remarque : vous devrez peut-être redémarrer le noyau pour utiliser les paquets mis à jour.

import pandas as pd

from sklearn_evaluation import plot

# Import jupysql Jupyter extension to create SQL cells
%load_ext sql
%config SqlMagic.autocommit=False

Vous devez vous assurer que votre instance ClickHouse est opérationnelle et accessible pour les prochaines étapes. Vous pouvez utiliser une version locale ou la version Cloud.

Remarque : vous devrez ajuster la chaîne de connexion en fonction du type d’instance à laquelle vous essayez de vous connecter (URL, utilisateur, mot de passe). Dans l’exemple ci-dessous, nous avons utilisé une instance locale. Pour en savoir plus, consultez ce guide.

%sql clickhouse://default:@localhost:8123/default
%%sql
CREATE TABLE trips
(
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;
  • clickhouse://default:***@localhost:8123/default Terminé.
%%sql
INSERT INTO trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
") SETTINGS input_format_try_infer_datetimes = 0
  • clickhouse://default:***@localhost:8123/default Terminé.
%sql SELECT count() FROM trips limit 5;
  • clickhouse://default:***@localhost:8123/default C'est fait.
count()
1999657
%sql SELECT DISTINCT(pickup_ntaname) FROM trips limit 5;
  • clickhouse://default:***@localhost:8123/default Terminé.
pickup_ntaname
Morningside Heights
Hudson Yards-Chelsea-Flatiron-Union Square
Midtown-Midtown South
SoHo-Tribeca-Civic Center-Little Italy
Murray Hill-Kips Bay
%sql SELECT round(avg(tip_amount), 2) FROM trips
  • clickhouse://default:***@localhost:8123/default Terminé.
round(avg(tip_amount), 2)
1.68
%%sql
SELECT
    passenger_count,
    ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_count
  • clickhouse://default:***@localhost:8123/default C'est fait.
passenger_countaverage_total_amount
022.69
115.97
217.15
316.76
417.33
516.35
616.04
759.8
836.41
99.81
%%sql
SELECT
    pickup_date,
    pickup_ntaname,
    SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC
limit 5;
  • clickhouse://default:***@localhost:8123/default Terminé.
pickup_datepickup_ntanamenumber_of_trips
2015-07-01Bushwick North2
2015-07-01Brighton Beach1
2015-07-01Briarwood-Jamaica Hills3
2015-07-01Williamsburg1
2015-07-01Queensbridge-Ravenswood-Long Island City9
# %sql DESCRIBE trips;
# %sql SELECT DISTINCT(trip_distance) FROM trips limit 50;
%%sql --save short-trips --no-execute
SELECT *
FROM trips
WHERE trip_distance < 6.3
  • clickhouse://default:***@localhost:8123/default Exécution ignorée…
%sqlplot histogram --table short-trips --column trip_distance --bins 10 --with short-trips
<AxesSubplot: title={'center': "'trip_distance' from 'short-trips'"}, xlabel='trip_distance', ylabel='Count'>
Histogramme montrant la répartition des distances de trajet en 10 classes à partir du jeu de données short-trips
ax = %sqlplot histogram --table short-trips --column trip_distance --bins 50 --with short-trips
ax.grid()
ax.set_title("Trip distance from trips < 6.3")
_ = ax.set_xlabel("Trip distance")
Histogramme montrant la répartition des distances de trajet en 50 classes, avec grille, intitulé « Distance de trajet pour les trajets < 6,3 »
Navigation