Clickhouse

Chez Affini-Tech, notre métier c’est de construire et de manager des plateformes Data pour nos clients et de leur apporter le meilleur service possible. Dans cette quête de la meilleure plateforme Data le fait d’avoir un moteur permettant d’opérer des transformations SQL sur les données résidant dans un datalake est un enjeu majeur.

Récement l’apparition de DuckDB dans le paysage semblait cocher un nombre important de caractéristiques nécessaires avec la capacité à traiter les données SQL résidant dans des fichiers parquets sur du S3, mais aussi en autorisant le pilotable depuis DBT. Néanmoins l’une de faiblesse de DuckDB est de ne pas disposer d’un serveur SQL pour exposer directement les données à un outil de BI. Cette situation requiert de faire co-exister deux moteurs SQL, et aussi deux projets DBT, l’un pour le Datalake et un autre pour le Datamart dans lequel les données sont exposées aux utilisateurs.

Clickhouse

Clickhouse est un moteur SQL analytique optimisé pour répondre à des requêtes sur de gros volumes de données. Le stockage peut être assez flexible, celui-ci pouvant être le disque local comme un stockage objet. Les performances obtenues sur une machine relativement modeste (4vcpu / 16GB de RAM) sont très bonnes, et permettent d’offrir une équation économique très intéressante.

Parmi les nombreuses fonctionnalités offertes par Clickhouse, on va se concentrer sur les suivantes :

• Stockage sur disque local ou sur S3
• Utilisation de fichiers parquets ou de fichiers natifs
• integration avec DBT
• Performances

Lecture des données du Datalake dans le stockage S3

Une fois Clickhouse installé, il faut modifier quelques fichiers de configuration pour accéder au stockage S3.

Le fichier de configuration SQL pour utiliser le S3 de Scaleway /etc/clickhouse-server/config.d/s3.xml indique le endpoint et les credentials du stockage.

<clickhouse>
<s3>
    <crypto>
        <endpoint>https://s3.fr-par.scw.cloud</endpoint>
        <access_key_id>YOURKEY</access_key_id>
        <secret_access_key>YOURSECRET</secret_access_key>
        <region>fr-par</region>
        <use_environment_credentials>false</use_environment_credentials>
    </crypto>
</s3>
</clickhouse>

Et la requête SQL pour accéder à ce stockage.

SELECT
	toYear (OpenTime) AS y,
	count(*)
FROM s3 ('https://s3.fr-par.scw.cloud/bucket/path/to/file.parquet')
GROUP BY y;

Cette requête lit en 77 secondes 257 millions d’enregistrements répartis dans 400 fichiers parquets sur le stockage S3. Le fait de devoir mettre le path complet est peu pratique, mais on peut très facilement créer une vue pour masquer cette complexité. De plus avec l’utilisation de DBT, ces vues seront masquées dans les sources.

CREATE VIEW crypto AS (
SELECT *
FROM s3 ('https://s3.fr-par.scw.cloud/bucket/path/to/file.parquet')
);

SELECT * FROM crypto LIMIT 10;

Fichiers parquet vs format MergeTree

Pour améliorer les performances, on va charger l’ensemble des parquets dans une table MergedTree sur disque local

CREATE TABLE crypto_all 
engine = MergeTree
ORDER BY OpenTime
settings allow_nullable_key=1
AS
SELECT *
FROM s3 ('https://s3.fr-par.scw.cloud/bucketname/path/to/*.parquet');

Cette table au format MergeTree peut être aussi stockée dans un stockage objet de type S3. Pour ce faire il est nécessaire de créer un disque pour commencer, en ajoutant le fichier /etc/clickhouse-server/config.d/storage_config.xml comme suit :

<clickhouse>
  <storage_configuration>
    <disks>
      <s3_disk>
        <type>s3</type>
        <endpoint>https://s3.fr-par.scw.cloud/bucketname/path/to/tables/</endpoint>
        <access_key_id>XXXXXXXX</access_key_id>
        <secret_access_key>YYYYYYYYYYY</secret_access_key>
        <region>fr-par</region>
        <use_environment_credentials>false</use_environment_credentials>
        <metadata_path>/var/lib/clickhouse/disks/s3_disk/</metadata_path>
      </s3_disk>
      <s3_cache>
        <type>cache</type>
        <disk>s3_disk</disk>
        <path>/var/lib/clickhouse/disks/s3_cache/</path>
        <max_size>10Gi</max_size>
      </s3_cache>
    </disks>
    <policies>
      <s3_main>
        <volumes>
          <main>
            <disk>s3_disk</disk>
          </main>
        </volumes>
      </s3_main>
    </policies>
  </storage_configuration>
</clickhouse>

On peut donc maintenant utiliser ce stockage pour créer une table de type MergeTree dans le Datalake

CREATE TABLE crypto_all_s3 
engine = MergeTree
ORDER BY OpenTime
settings allow_nullable_key=1, storage_policy='s3_main'
AS
SELECT *
FROM s3 ('https://s3.fr-par.scw.cloud/bucketname/path/to/file/*.parquet');

Performances

Les trois modes de stockage de la donnée que nous venons de parcourir peuvent être comparés en termes de performances en executant la même requête sur chacune des 3 tables :

• requête de la table MergeTree sur disque local : < 1s
• requête de la table MergeTree sur S3 : environ 3s
• requête des 400 fichiers parquets sur s3 : > 70s

A noter que le coût du stockage sur S3 est relativement insignifiant comparé à celui du SSD local sur la VM.

Les performances obtenues depuis un client SQL compatible avec Clickhouse (tableplus, tableau, metabase…) sont du même ordre de grandeur que les performances obtenues par le client inclu dans la console Clickhouse.

Intégration DBT

L’utilisation de DBT conjointement avec Clickhouse va donc permettre d’utiliser toutes les fonctionnalité de DBT à l’échelle. Le package s’ajoute assez classiquement en faisant un pip install dbt-clickhouse.

La configuration Clickhouse se fait dans le profile.yaml

dbt_clickhouse_project:
  target: dev
  outputs:
    dev:
      type: clickhouse
      schema: default
      host: clickhouse-host.datatask.io
      port: 9440
      user: default
      password: 'the_default_user_password'
      secure: True
      verify: False

On va ensuite définir les sources de données pour utiliser nos tables préalablement dans sources.yaml

version: 2

sources:
  - name: cryptodb
    database: default  
    schema: default  
    tables:
      - name: crypto

Et on se retrouve avec un usage DBT des plus classiques pour créer des modèles :

{{
  config(
    materialized="table"
  )
}}
SELECT *
FROM {{ source("cryptodb","crypto")}}
LIMIT 10

Conclusion

Comme vous avez pu le lire ci-dessus, Clickhouse nous permet de travailler avec des données dans du stockage objet (S3) avec SQL, mais aussi de matérialiser ces données sur du disque pour améliorer les performances.

Nous reviendrons très rapidement sur des fonctionnalités avancées de Clickhouse, et sur l’intégration que en faisons dans DataTask.

Ces instances managées chez notre partenaire Scaleway sont disponibles dès aujourd’hui pour nos clients dans l’offre DataTask. Pour essayer ces instances managées, prenez un rdv avec nous.