Introduction aux Bases de Données

Qu'est-ce qu'une Base de Données ?

Une base de données est un système utilisé pour stocker, organiser et accéder à des informations. Elle permet aux utilisateurs de saisir, gérer et récupérer des données de manière efficace et sécurisée. Les bases de données sont structurées pour gérer de grandes quantités d'informations en permettant que les données soient stockées dans des tables qui peuvent être recherchées, manipulées et entretenues facilement.

Types de Bases de Données

Relationnelle Stocke les données dans des tables structurées avec des lignes et des colonnes, permettant des requêtes complexes en utilisant SQL. Exemple : bases de données RH et CRM.

Non relationnelle Gère des données non structurées et semi-structurées sans structure de table fixe, en utilisant des formats tels que clé-valeur, documents et graphes. Exemple : plateformes de commerce électronique, gestion de contenu, IoT et analyses en temps réel.

Introduction aux systèmes de gestion de bases de données (SGBD)

Logiciel permettant aux utilisateurs de définir, créer, maintenir et contrôler l'accès à la base de données. Il gère le stockage, la manipulation et la récupération des données, facilitant les interactions de plusieurs utilisateurs avec diverses bases de données.

Fondamentaux des bases de données orientées colonnes

Stockage orienté lignes vs. orienté colonnes

Bases de données orientées lignes :- Structure : Stocke les données par lignes, ce qui le rend efficace pour les opérations d'écriture et le traitement transactionnel où l'accès à des enregistrements complets est courant.

Avantages : Optimal pour les systèmes OLTP (Traitement Transactionnel en Ligne) comme la gestion de la relation client où les mises à jour et les insertions rapides sont cruciales.

Bases de données orientées colonnes :- Structure : Stocke les données par colonnes, améliorant les opérations de lecture et l'analyse de données où des agrégations sur de grands ensembles de données sont nécessaires.

Avantages : Idéal pour les systèmes OLAP (Online Analytical Processing) tels que l'entreposage de données et l'analyse de mégadonnées, offrant des performances élevées pour les requêtes accédant uniquement à un sous-ensemble de colonnes.
Avantages :- Orienté lignes : Récupération rapide des données par ligne complète, implémentation plus simple, efficace pour la saisie et la mise à jour des enregistrements.
Orienté Colonne : Meilleure compression, stockage efficace permettant une lecture plus rapide des données de colonne, optimisé pour l'interrogation de grands ensembles de données, ce qui réduit les opérations d'E/S.

Bases de données orientées ligne :- Structure: Stocke les données par lignes, ce qui est efficace pour les opérations d'écriture et le traitement transactionnel où l'accès aux enregistrements complets est courant.

Avantages: Optimal pour les systèmes OLTP (Traitement des Transactions en Ligne) comme la gestion de la relation client où des mises à jour et des insertions rapides sont cruciales.

Bases de données orientées colonne :- Structure: Stocke les données par colonnes, améliorant les opérations de lecture et l'analyse de données où des agrégations sur de grands ensembles de données sont nécessaires.

Avantages: Idéal pour les systèmes OLAP (Traitement Analytique en Ligne) tels que l'entreposage de données et l'analyse de grandes données, offrant de hautes performances pour les requêtes accédant uniquement à un sous-ensemble de colonnes.

Avantages :- Orienté ligne: Récupération rapide des données par ligne complète, mise en œuvre plus simple, efficace pour l'entrée et la mise à jour des enregistrements.

Orienté colonne: Meilleure compression, stockage efficace conduisant à une lecture rapide des données de colonne, optimisé pour l'interrogation de grands ensembles de données, ce qui réduit les opérations d'entrée/sortie.

Base de données orientée colonne

graph TD
subgraph "Base de données orientée colonne"
subgraph "Colonne ID"
ID1 [1]
ID2 [2]
ID3 [3]
end
subgraph "Colonne Nom"
Name1 [John]
Name2 [Alice]
Name3 [Bob]
end
subgraph "Colonne Âge"
Age1 [30]
Age2 [25]
Age3 [35]
end
subgraph "Colonne Ville"
City1 [New York]
City2 [Los Angeles]
City3 [San Francisco]
end
end

Base de données orientée ligne

graph TD
subgraph "Stockage orienté ligne"
A ["Ligne 1 : ID | Nom | Âge | Ville"]
B ["Ligne 2 : ID | Nom | Âge | Ville"]
C ["Ligne 3 : ID | Nom | Âge | Ville"]
end

Introduction à ClickHouse

Qu'est-ce que ClickHouse ?

ClickHouse est un système de gestion de base de données (SGBD) open-source orienté colonne, conçu pour le traitement analytique en ligne (OLAP). Il permet aux utilisateurs de générer des rapports analytiques à l'aide de requêtes SQL en temps réel, offrant des performances élevées et une évolutivité pour le traitement de grands volumes de données. Les principales caractéristiques incluent :

Stockage orienté colonne
Compression des données
Traitement de requêtes distribué
Prise en charge SQL
Hautes performances pour les requêtes analytiques
Capacité à gérer des pétaoctets de données
Tolérance aux pannes grâce à la réplication

ClickHouse est particulièrement bien adapté aux applications nécessitant des analyses rapides sur des ensembles de données massifs, tels que la veille économique, l'observabilité et l'entreposage de données en temps réel.

ClickHouse est un système de gestion de base de données (SGBD) orienté colonne et open-source, conçu pour le traitement analytique en ligne (OLAP). Il permet aux utilisateurs de générer des rapports analytiques en temps réel à l'aide de requêtes SQL, offrant des performances élevées et une scalabilité pour le traitement de grands volumes de données. Les caractéristiques clés incluent :

Stockage orienté colonne
Compression des données
Traitement distribué des requêtes
Support SQL
Haute performance pour les requêtes analytiques
Capacité à gérer des pétaoctets de données
Tolérance aux pannes grâce à la réplication

ClickHouse est particulièrement adapté pour les applications nécessitant des analyses rapides sur des ensembles de données massifs, comme l'intelligence d'affaires, l'observabilité et l'entreposage de données en temps réel.

Principales fonctionnalités de ClickHouse et rapidité

Stockage orienté colonne : Souvent, seules quelques-unes des centaines de colonnes des données source sont nécessaires pour un rapport. Le stockage orienté colonne améliore l'efficacité en n'accédant qu'aux colonnes nécessaires, réduisant ainsi les lectures de disque coûteuses.
Index : ClickHouse utilise des structures de données en mémoire pour lire uniquement les colonnes requises et des plages de lignes spécifiques, optimisant ainsi la récupération des données.
Compression des données : Le regroupement des valeurs d'une même colonne améliore la compression par rapport aux systèmes orientés ligne, car des valeurs similaires sont courantes dans les lignes adjacentes. ClickHouse utilise également des codecs spécialisés pour une compacité des données encore plus grande.
Exécution de requêtes vectorisées : ClickHouse traite les données par colonnes, améliorant l'efficacité du cache du processeur et permettant l'utilisation d'instructions CPU SIMD pour des opérations plus rapides.
Évolutivité : ClickHouse maximise l'utilisation des ressources en exploitant tous les cœurs de CPU et disques disponibles, non seulement sur un seul serveur mais à travers un cluster, améliorant ainsi les performances pour les requêtes complexes.
Stockage orienté colonne: Souvent, seules quelques-unes des centaines de colonnes des données sources sont nécessaires pour un rapport. Le stockage orienté colonne améliore l'efficacité en accédant uniquement aux colonnes nécessaires, réduisant ainsi les coûteuses lectures de disque.
Index *: ClickHouse utilise des structures de données en mémoire pour lire uniquement les colonnes requises et les plages de lignes spécifiques, optimisant ainsi la récupération des données.
Compression des données: Regrouper les valeurs d'une même colonne améliore la compression par rapport aux systèmes orientés ligne, car les valeurs similaires sont courantes dans les lignes adjacentes. ClickHouse utilise également des codecs spécialisés pour une compacité encore plus grande des données.
Exécution de requêtes vectorisées: ClickHouse traite les données par colonnes, améliorant l'efficacité du cache CPU et permettant l'utilisation d'instructions CPU SIMD pour des opérations plus rapides.
Scalabilité: ClickHouse maximise l'utilisation des ressources en exploitant tous les cœurs de CPU et les disques disponibles, non seulement sur un seul serveur mais sur tout un cluster, améliorant ainsi les performances pour les requêtes complexes.

Clé primaire, Index & granule

Clé primaire :

Détermine l'ordre de tri physique des données sur le disque
Utilisé pour créer un index creux

Index :

L'index est créé pour chaque N lignes (par défaut 8192), appelé un granule
Stocké dans le fichier primary.idx
Utilisé pour localiser rapidement les blocs de données pertinents

Granules :

Un granule est un groupe logique de lignes (par défaut 8192 lignes)
Les données sont divisées en plusieurs parties, et chaque partie est divisée en granules

Comment ils fonctionnent ensemble :

Lorsqu'une requête est exécutée, ClickHouse utilise la clé primaire pour rechercher l'index creux
L'index aide à identifier quels granules contiennent potentiellement des données pertinentes
ClickHouse charge ensuite uniquement les granules correspondants en mémoire
Les données au sein des granules sont traitées pour trouver les lignes correspondantes exactes

Architecture de ClickHouse

Aperçu de l'architecture de ClickHouse

Moteur : Le moteur ClickHouse est un type de moteur de table qui détermine comment et où les données sont stockées, comment les requêtes sont exécutées, ainsi que les capacités de concurrence et de réplication de la base de données. Chaque moteur est optimisé pour des cas d'utilisation spécifiques, offrant diverses fonctionnalités telles que le partitionnement des données, l'indexation et le support pour différents types de requêtes.

Moteurs ClickHouse

Famille MergeTree :

MergeTree : Le moteur le plus polyvalent et le plus couramment utilisé, conçu pour des opérations de lecture et d'écriture haute performance, prenant en charge le partitionnement des données, les clés primaires et la réplication des données.
ReplacingMergeTree : Permet la déduplication des lignes lors des fusions en fonction de la clé de tri.
SummingMergeTree : Additionne les colonnes numériques pour les lignes ayant la même clé de tri lors des fusions.
AggregatingMergeTree : Agrège les données lors des fusions en utilisant des fonctions d'agrégation spécifiées.
CollapsingMergeTree : Réduit les lignes ayant la même clé de tri lors des fusions, en se basant sur une colonne spéciale 'Sign'.
VersionedCollapsingMergeTree : Étend CollapsingMergeTree avec le contrôle de version pour les lignes.
GraphiteMergeTree : Spécialisé dans le stockage des données de séries chronologiques.

Moteurs d'intégration :

ODBC : Permet d'interroger des bases de données externes à l'aide d'ODBC.
JDBC : Permet d'interroger des bases de données externes à l'aide de JDBC.
MySQL : Pour interroger les bases de données MySQL.
MongoDB : Permet d'interroger les bases de données MongoDB.
HDFS : Pour travailler avec les données stockées dans le système de fichiers distribué Hadoop.
S3 : Permet de travailler avec les données stockées dans Amazon S3.
Kafka : Pour travailler avec les flux Apache Kafka.
Famille MergeTree :
MergeTree : Le moteur le plus polyvalent et le plus utilisé, conçu pour des opérations de lecture et d'écriture à haute performance, prenant en charge le partitionnement des données, les clés primaires et la réplication des données.
ReplacingMergeTree : Permet la déduplication des lignes pendant les fusions basées sur la clé de tri.
SummingMergeTree : Somme les colonnes numériques pour les lignes ayant la même clé de tri pendant les fusions.
AggregatingMergeTree : Agrège les données pendant les fusions en utilisant des fonctions d'agrégation spécifiées.
CollapsingMergeTree : Effondre les lignes ayant la même clé de tri pendant les fusions, en fonction d'une colonne spéciale 'Signe'.
VersionedCollapsingMergeTree : Étend CollapsingMergeTree avec un contrôle de version pour les lignes.
GraphiteMergeTree : Spécialisé pour le stockage des données de séries temporelles.

Démarrer avec ClickHouse

Installation et Configuration - sur Mac ou Linux

Installer la bibliothèque

curl https://clickhouse.com/ | sh

Exécutez la commande suivante pour démarrer le serveur ClickHouse :

./clickhouse server

Ouvrez un nouveau terminal et connectez-vous au serveur

clickhouse-client

Interroger des fichiers distants

- -Check the file
SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/ontime/csv_by_year/*.csv.gz', CSVWithNames) limit 10;

-Comptage de base
SELECT count() FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/ontime/csv_by_year/*.csv.gz', CSVWithNames) limit 10;
-Le nombre de retards par transporteur pour 2007
SELECT IATA_CODE_Reporting_Airline AS Carrier, count()
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/ontime/csv_by_year/.csv.gz')
WHERE DepDelay>10 AND Year=2007
GROUP BY Carrier
ORDER BY count(*) DESC limit 5;


**Résultats** \\ ┌─Carrier─┬─count()─┐

- │ WN │ 296293 │ └─────────┴─────────┘ ┌─Carrier─┬─count()─┐

- │ AA │ 176203 │

- │ MQ │ 145630 │

- │ US │ 135987 │

- │ UA │ 128174 │ └─────────┴─────────┘

5 lignes dans l'ensemble. Temps écoulé : 420.586 sec. 204.84 millions de lignes traitées, 11.66 Go (487.03 mille lignes/s., 27.72 Mo/s.) Utilisation maximale de la mémoire : 1.18 GiB.

## Exécuter Votre Première Requête

#### Créer une table

CREATE TABLEontime
(
Year UInt16,
Quarter UInt8,
Month UInt8,
DayofMonth UInt8,
DayOfWeek UInt8,
FlightDate Date,
Reporting_Airline LowCardinality(String),
DOT_ID_Reporting_Airline Int32,
IATA_CODE_Reporting_Airline LowCardinality(String),
Tail_Number LowCardinality(String),
Flight_Number_Reporting_AirlineLowCardinality(String),
OriginAirportID Int32,
OriginAirportSeqID Int32,
OriginCityMarketID Int32,
Origin FixedString(5),
OriginCityName LowCardinality(String),
OriginState FixedString(2),

OriginStateFips FixedString(2),
OriginStateName LowCardinality(String),
OriginWac Int32,
DestAirportID Int32,
DestAirportSeqID Int32,
DestCityMarketID Int32,
Dest FixedString(5),
DestCityName LowCardinality(String),
DestState FixedString(2),
DestStateFips FixedString(2),
DestStateName LowCardinality(String),
DestWac Int32,
CRSDepTime Int32,
DepTime Int32,
DepDelay Int32,
DepDelayMinutes Int32,
DepDel15 Int32,
DepartureDelayGroups LowCardinality(String),

DepTimeBlk LowCardinality(String),
TaxiOut Int32,
WheelsOff LowCardinality(String),
WheelsOn LowCardinality(String),
TaxiIn Int32,
CRSArrTime Int32,
ArrTime Int32,
ArrDelay Int32,
ArrDelayMinutes Int32,
ArrDel15 Int32,
ArrivalDelayGroups LowCardinality(String),
ArrTimeBlk LowCardinality(String),
Cancelled Int8,
CancellationCode FixedString(1),
Diverted Int8,
CRSElapsedTime Int32,
ActualElapsedTime Int32,
AirTime Int32,

Flights Int32,
Distance Int32,
DistanceGroup Int8,
CarrierDelay Int32,
WeatherDelay Int32,
NASDelay Int32,
SecurityDelay Int32,
LateAircraftDelay Int32,
FirstDepTime Int16,
TotalAddGTime Int16,
LongestAddGTime Int16,
DivAirportLandings Int8,
DivReachedDest Int8,
DivActualElapsedTime Int16,
DivArrDelay Int16,
DivDistance Int16,
Div1Airport LowCardinality(String),
Div1AirportID Int32,
Div1AirportSeqID Int32,
Div1WheelsOn Int16,

Div1TotalGTime Int16,
Div1LongestGTime Int16,
Div1WheelsOff Int16,
Div1TailNum LowCardinality(String),
Div2Airport LowCardinality(String),
Div2AirportID Int32,
Div2AirportSeqID Int32,
Div2WheelsOn Int16,
Div2TotalGTime Int16,
Div2LongestGTime Int16,
Div2WheelsOff Int16,
Div2TailNum LowCardinality(String),
Div3Airport LowCardinality(String),
Div3AirportID Int32,
Div3AirportSeqID Int32,
Div3WheelsOn Int16,
Div3TotalGTime Int16,
Div3LongestGTime Int16,
Div3WheelsOff Int16,

Div3TailNum LowCardinality(String),
Div4Airport LowCardinality(String),
Div4AirportID Int32,
Div4AirportSeqID Int32,
Div4WheelsOn Int16,
Div4TotalGTime Int16,
Div4LongestGTime Int16,
Div4WheelsOff Int16,
Div4TailNum LowCardinality(String),
Div5Airport LowCardinality(String),
Div5AirportID Int32,
Div5AirportSeqID Int32,
Div5WheelsOn Int16,
Div5TotalGTime Int16,
Div5LongestGTime Int16,
Div5WheelsOff Int16,
Div5TailNum LowCardinality(String)
) ENGINE = MergeTree

ORDER BY (Year, Quarter, Month, DayofMonth, FlightDate, IATA_CODE_Reporting_Airline);


#### Charger les données - chargé 1M d'enregistrements et seulement 2002

INSERT INTO ontime SELECT *
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/ontime/csv_by_year/2001.csv.gz', CSVWithNames)
WHERE Year=2001 limit 1000000
SETTINGS max_insert_threads = 40;


- Nombre de retards par aéroport

SELECT Origin, count(*) AS c
FROM ontime
WHERE DepDelay>10
GROUP BY Origin
ORDER BY c DESC
LIMIT 10;


#### Interroger les données locales - Le fichier est local

Utilisation du jeu de données NY Complaints.

[NYPD COMPLAINTS](https://data.cityofnewyork.us/Public-Safety/NYPD-Complaint-Data-Current-Year-To-Date-/5uac-w243/about_data)

describe table file("/Users/ [you]/user_files/nypd_complaint.tsv")

select * from file("/Users/ [you]/user_files/nypd_complaint.tsv") limit 10


##### Analyse rapide

- Nombre de plaintes par type de crime

SELECT IFNULL(NYPD_Complaint_Data_Current_Year_To_Date.OFNS_DESC, 'Unknown') AS Crime_Type,
COUNT(*) AS Complaint_Count
FROM url('https://data.cityofnewyork.us/api/views/5uac-w243/rows.csv?accessType=DOWNLOAD', CSVWithNames)
AS NYPD_Complaint_Data_Current_Year_To_Date
GROUP BY Crime_Type ORDER BY Complaint_Count DESC limit 10


- Crimes les plus courants la nuit

SELECT IFNULL(NYPD_Complaint.OFNS_DESC, 'Unknown') AS Crime_Type,
COUNT(*) AS Complaint_Count
FROM url('https://data.cityofnewyork.us/api/views/5uac-w243/rows.csv?accessType=DOWNLOAD', CSVWithNames)
AS NYPD_Complaint

WHERE (NYPD_Complaint.CMPLNT_FR_TM>= '18:00:00' OR NYPD_Complaint.CMPLNT_FR_TM<= '06:00:00')
GROUP BY Crime_Type ORDER BY Complaint_Count DESC limit 10

Démarrer avec ClickHouse Cloud

Clickhouse Cloud

chDB - Un aperçu

Aperçu

chDB est un moteur SQL OLAP (Online Analytical Processing) intégré, alimenté par ClickHouse. Il est conçu pour être une base de données embarquée qui apporte la puissance de ClickHouse aux applications sans nécessiter d'installation de serveur séparée. chDB est développé par ClickHouse, Inc. et des contributeurs open-source.

Les principales caractéristiques de chDB incluent :

Fonctionnement sans serveur - pas besoin d'installer ou d'exécuter les services ClickHouse
Prise en charge de plusieurs langages de programmation, y compris Python, Go, Rust, NodeJS et Bun
Capacité à travailler avec divers formats de données comme Parquet, CSV, JSON, Arrow et ORC
Fonctions définies par l'utilisateur (UDF) personnalisées

chDB est un moteur OLAP (Online Analytical Processing) SQL en cours d'exécution, alimenté par ClickHouse. Il est conçu pour être une base de données embarquée qui apporte la puissance de ClickHouse aux applications sans nécessiter d'installation de serveur séparée. chDB est développé par ClickHouse, Inc. et des contributeurs open source.