What is a Database?

A database is a system used to store, organize, and access information. It allows users to input, manage, and retrieve data efficiently and securely. Databases are structured to handle large amounts of information by allowing data to be stored in tables that can be searched, manipulated, and maintained easily.

FR \\ 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 of Databases

Relational Store data in structured tables with rows and columns, allowing complex queries using SQL. Example: HR, and CRM database

non-relational Handle unstructured and semi-structured data without a fixed table structure, using formats like key-value, documents, and graphs. Example: E-commerce platforms, content management, IoT and real-time analytics

FR \\ 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 to Database Management Systems (DBMS)

Software that allows users to define, create, maintain, and control access to the database. It manages data storage, manipulation, and retrieval, facilitating multiple user interactions with various databases

FR \\ 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.

Row-Oriented vs. Column-Oriented Storage

Row-Oriented Databases:

• Structure: Stores data by rows, making it efficient for writing operations and transactional processing where access to complete records is common.
• Benefits: Optimal for OLTP systems (Online Transaction Processing) like customer relationship management where fast updates and inserts are crucial.

Column-Oriented Databases:

• Structure: Stores data by columns, enhancing read operations and data analytics where aggregations over large datasets are required.
• Benefits: Ideal for OLAP systems (Online Analytical Processing) such as data warehousing and big data analytics, offering high performance for queries accessing only a subset of columns.

Advantages:

• Row-Oriented: Quick data retrieval by complete row, simpler implementation, efficient for entry and updating of records.
• Column-Oriented: Better compression, efficient storage leading to faster read of column data, optimized for querying large datasets, which reduces I/O operations.

FR \\ 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.

Column-Oriented Database

graph TD
    subgraph "Column-Oriented Database"
        subgraph "ID Column"
            ID1[1]
            ID2[2]
            ID3[3]
        end
        subgraph "Name Column"
            Name1[John]
            Name2[Alice]
            Name3[Bob]
        end
        subgraph "Age Column"
            Age1[30]
            Age2[25]
            Age3[35]
        end
        subgraph "City Column"
            City1[New York]
            City2[Los Angeles]
            City3[San Francisco]
        end
    end

Row Oriented Database

graph TD
    subgraph "Row-Oriented Storage"
        A["Row 1: ID | Name | Age | City"]
        B["Row 2: ID | Name | Age | City"]
        C["Row 3: ID | Name | Age | City"]
    end

What is ClickHouse?

ClickHouse is an open-source column-oriented database management system (DBMS) designed for online analytical processing (OLAP). It allows users to generate analytical reports using SQL queries in real-time, offering high performance and scalability for processing large volumes of data. Key features include:

• Column-oriented storage
• Data compression
• Distributed query processing
• SQL support
• High performance for analytical queries
• Ability to handle petabytes of data
• Fault tolerance through replication

ClickHouse is particularly well-suited for applications requiring fast analytics on massive datasets, such as business intelligence, observability, and real-time data warehousing.

FR \\ 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.

Key Features of ClickHouse & Speed

• Column-Oriented Storage: Often, only a few of the hundreds of columns in source data are needed for a report. Column-oriented storage improves efficiency by only accessing the necessary columns, reducing costly disk reads.

• Indexes: ClickHouse utilizes in-memory data structures to read only the required columns and specific row ranges, optimizing data retrieval.

• Data Compression: Grouping the same column's values enhances compression compared to row-oriented systems, as similar values are common in adjacent rows. ClickHouse also employs specialized codecs for even greater data compactness.

• Vectorized Query Execution: ClickHouse processes data by columns, improving CPU cache efficiency and enabling the use of SIMD CPU instructions for faster operations.

• Scalability: ClickHouse maximizes resource utilization by leveraging all available CPU cores and disks, not just on a single server but across a cluster, enhancing performance for complex queries.

FR \\

• 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.

Overview of ClickHouse Architecture

• Engine ClickHouse engine is a type of table engine that determines how and where data is stored, how queries are executed, and the concurrency and replication capabilities of the database. Each engine is optimized for specific use cases, providing various functionalities such as data partitioning, indexing, and support for different query types.

FR \\

• 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.

Clickhouse Engines

MergeTree Family:

• MergeTree: The most versatile and commonly used engine, designed for high-performance read and write operations, supporting data partitioning, primary keys, and data replication.
• ReplacingMergeTree: Allows for deduplication of rows during merges based on the sorting key.
• SummingMergeTree: Sums up numeric columns for rows with the same sorting key during merges.
• AggregatingMergeTree: Aggregates data during merges using specified aggregate functions.
• CollapsingMergeTree: Collapses rows with the same sorting key during merges, based on a special 'Sign' column.
• VersionedCollapsingMergeTree: Extends CollapsingMergeTree with version control for rows.
• GraphiteMergeTree: Specialized for storing time series data.

Integration Engines:

• ODBC: Allows querying external databases using ODBC.
• JDBC: Enables querying external databases using JDBC.
• MySQL: For querying MySQL databases.
• MongoDB: Allows querying MongoDB databases.
• HDFS: For working with data stored in Hadoop Distributed File System.
• S3: Enables working with data stored in Amazon S3.
• Kafka: For working with Apache Kafka streams.

FR \\

• 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.

Installation and Setup - on Mac or Linux

1. Install library

curl https://clickhouse.com/ | sh

1. Run the following command to start clickhouse server:

./clickhouse server

1. Open a new Terminal and connect to the server

clickhouse-client

1. Query a remote files

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

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

--The number of delays by carrier for 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;

Results \\ ┌─Carrier─┬─count()─┐

1. │ WN │ 296293 │ └─────────┴─────────┘ ┌─Carrier─┬─count()─┐
2. │ AA │ 176203 │
3. │ MQ │ 145630 │
4. │ US │ 135987 │
5. │ UA │ 128174 │ └─────────┴─────────┘

5 rows in set. Elapsed: 420.586 sec. Processed 204.84 million rows, 11.66 GB (487.03 thousand rows/s., 27.72 MB/s.) Peak memory usage: 1.18 GiB.

Running Your First Query

CREATE TABLE `ontime`
(
    `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_Airline` LowCardinality(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);

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;

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

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

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

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

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

Overview

chDB is an in-process SQL OLAP (Online Analytical Processing) engine powered by ClickHouse. It's designed to be an embedded database that brings the power of ClickHouse to applications without the need for a separate server installation. chDB is developed by ClickHouse, Inc. and open-source contributors.

Key features of chDB include:

1. Serverless operation - no need to install or run ClickHouse services
2. Support for multiple programming languages including Python, Go, Rust, NodeJS, and Bun
3. Ability to work with various data formats like Parquet, CSV, JSON, Arrow, and ORC
4. Custom User Defined Functions (UDFs)

FR \\ 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.

Les principales caractéristiques de chDB incluent :

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

Use Case - Why use chDB

Based on the information provided, here are some practical use cases for chDB in real-world applications:

1. Data analysis in Jupyter notebooks: chDB allows users to run ClickHouse queries directly in Jupyter notebooks, making it convenient for data scientists and analysts to work with large datasets without needing a separate ClickHouse server.
2. Embedded analytics in applications: Developers can embed chDB into their applications to provide fast analytical capabilities without requiring a separate database server.
3. Processing large datasets locally: chDB enables processing of large datasets (e.g. Parquet, CSV, JSON files) on a local machine without needing to set up a full database system.
4. Rapid prototyping: Data engineers and analysts can quickly prototype and test queries on large datasets without deploying a full ClickHouse cluster.
5. Serverless environments: chDB can be used in serverless functions to provide analytical capabilities without managing database infrastructure.
6. Data pipeline processing: chDB can be integrated into data pipelines for fast data transformations and aggregations.

FR \\ Basé sur les informations fournies, voici quelques cas d'utilisation pratiques de chDB dans des applications réelles :

1. Analyse de données dans les notebooks Jupyter : chDB permet aux utilisateurs d'exécuter des requêtes ClickHouse directement dans des notebooks Jupyter, facilitant ainsi le travail des data scientists et analystes sur de grands ensembles de données sans avoir besoin d'un serveur ClickHouse séparé.
2. Analytique embarquée dans les applications : Les développeurs peuvent intégrer chDB dans leurs applications pour fournir des capacités analytiques rapides sans nécessiter un serveur de base de données séparé.
3. Traitement de grands ensembles de données localement** : chDB permet de traiter de grands ensembles de données (par exemple, Parquet, CSV, fichiers JSON) sur une machine locale sans avoir à configurer un système de base de données complet.
4. Prototypage rapide : Les ingénieurs de données et les analystes peuvent rapidement prototyper et tester des requêtes sur de grands ensembles de données sans déployer un cluster ClickHouse complet.
5. Environnements sans serveur : chDB peut être utilisé dans des fonctions serverless pour fournir des capacités analytiques sans avoir à gérer une infrastructure de base de données.
6. Traitement des pipelines de données : chDB peut être intégré dans des pipelines de données pour des transformations et agrégations rapides des données.