À la découverte du SQL avec Apache Drill

| 7 minutes read

Aujourd’hui, je vais vous parler du framework SQL Big Data tendance du moment :

logo drillJe vous entends déjà me dire :

“Ok, Alexia ! Mais il existe déjà pléthore de frameworks SQL pour le Big Data alors pourquoi encore un nouveau ????”

Vous avez raison !

  • PIG et HIVE permettent de requêter des gros volumes de données sur une plate-forme Hadoop en SQL like ou en script. Mais le point faible de ces 2 outils est la vitesse (ils génèrent des jobs map reduce et on sait tous que map reduce, blabla, batch, blabla, bref c’est lent et pas adapté pour des traitements/reporting temps réel ou quasi temps réel)
  • Impala est très performant. Cependant, il ne peut être utilisé que dans un environnement Hadoop.
  • Spark SQL nécessite d’avoir compris les principes de Spark (RDD, DataFrame, DataSet) : on ne peut pas exécuter une requête SQL sans avoir initialisé un Spark context, SQL context, Spark session, etc… en revanche, Spark fonctionne sur toutes les plate-formes : standalone, Hadoop, Mesos, cluster, …

Noël est passé, mais imaginons que dans nos rêves les plus fous, on veuille un nouveau framework avec :

  • Un moteur SQL facile à installer
  • Avec la possibilité de requêter un jeu de données à grande échelle
  • Dans des formats structurés ou semi-structurés (CSV, JSON, AVRO, base de données SQL/NoSQL, parquet files, …)
  • Sans nécessité de définir un schéma ou de transformer la donnée via un ETL (la structure de la donnée serait automatiquement déduite du système et un repository centralisé de méta données ne serait pas nécessaire)
  • Permettant de requêter dans une même requête différentes sources de données dans différents formats
  • En utilisant du « real SQL » et non du «SQL like »
  • Scalable de notre machine à un cluster de 1000 nœuds avec de très bonnes performances (stockage colonne & exécution in memory)
  • S’exécutant sur toutes les plateformes

Alors une idée ? Et oui, Drill est sous le sapin !

Drill est la version open-source du système Dremel de Google utilisé dans Google Big Query.

Je vous propose dans un premier temps de parcourir l’architecture de Drill, de voir les différentes manières d’interagir avec Drill et d’exécuter ensemble quelques requêtes Drill !

1 – Architecture :

Le service « Drillbit » est le démon Drill. Il est en charge de réceptionner les requêtes du client, d’exécuter la requête et de retourner le résultat au client.
Bref le « Drillbit » est le cœur du système !

Dans le cas d’un environnement distribué, un service « Drillbit » est installé et est exécuté sur chaque nœud du cluster.

Drill maximise la « data locality », la donnée est traitée dans la mesure du possible là où elle est stockée (ce qui limite les temps de latence réseau et de lecture/écriture sur disque).

Zookeeper est utilisé pour coordonner et synchroniser les informations de chaque nœud du cluster et maintenir ainsi le cluster en bonne santé. On ne le dira jamais assez : «la santé c’est important !»

Architecture DrillLe Drillbit qui reçoit la requête du client s’appelle le foreman.
Le foreman réceptionne la requête, crée un « plan logique » (optimisation & réorganisation de la requête SQL) puis crée un « plan physique » qui décrit comment exécuter la requête sur les drillbits.Pipeline Execution Requête DrillPlus d’infos sur l’exécution des requêtes sur la documentation officielle de Drill.

2 – Les clients Drill :

Plusieurs solutions s’offrent à vous :

Le shell

Le shell utilise SQLline qui permet de se connecter à une base de données via un driver JDBC et d’exécuter des requêtes SQL.

Pour une utilisation en local :
Le script drill-embedded dans le répertoire d’installation de Drill lance une  commande pré-configurée pour une connexion locale (le noeud local est identifié comme un noeud ZooKeeper).

Dans un environnement distribué :
Il faut utiliser la commande sqlline en précisant a minima le/les noeud(s) ZooKeeper, ainsi que les identifiants de connexion, le schéma à utiliser, etc…

La console Web Drill

La console Web Drill est disponible sur chaque nœud du cluster, par défaut à cette adresse : <adresse_ip_du noeud>:8047
Elle permet de :

  • Consulter des informations générales sur votre cluster Drill
  • Monitorer, modifier et annuler des requêtes
  • Configurer les plug-ins de stockage
  • Visualiser les métriques, les threads et les logs

Driver ODBC

Pour accéder à Drill via des outils de BI, un driver ODBC est disponible.
Pour l’avoir testé avec Tableau, on attend avec impatience un vrai driver car sur Windows, l’alliance Tableau-ODBC-Drill manque de stabilité et Tableau ne prend pas en charge le driver ODBC pour Mac.
Un driver natif devrait voir le jour dans les prochains jours alors wait & see !

3 – Let’s go to the code !

Dans cet exemple, nous utiliserons Drill en local.
Vous retrouverez toutes les requêtes sur mon github.

Téléchargement et Installation de drill sur votre poste :

Lancement de Drill :

  • Linux ou Mac OS X : Exécuter la commande <repertoire_installation_drill>/bin/drill-embedded
  • Windows

A ce stade, vous devez avoir un drillbit instancié et le shell drill lancé.Shell

Les données :

Deux jeux de données seront utilisés :

  • Liste détaillée de tous les vols commerciaux aux USA entre Octobre 1987 et Avril 2008 (120 millions de lignes, taille : 12Go, 22 fichiers au format CSV)
  • Liste détaillée des aéroports américains (3364 aéroports, 3364 fichiers JSON, taille 14Mo)

Schéma

En Drill, un schéma est un storage plugin (exemple : un système de fichier, une base de données NoSQL : HBase, MongoDB, …, des métadonnées Hive, …) pouvant inclure des workspaces (exemple : répertoire du système de fichiers).

Nous utiliserons le storage plugin dfs qui pointe par défaut vers le système de fichiers de notre machine.

Les commandes « show schemas » et « show databases » retournent la liste des schémas requêtables via Drill.schemas

Dans la console web Drill, le workspace « ekito » a été ajouté ; il pointe vers le répertoire où sont stockés les deux jeux de données; les aéroports sont stockés dans un sous-répertoire nommé “json” et les vols dans “csv”.
workspace

La définition du format CSV a été modifiée pour ajouter le paramètre extractHeader. Le schéma des données sera automatiquement déduit et les colonnes pourront être sélectionnées par leur nom.CSV format extractHeader

Requêtes sur les aéroports :

Les aéroports sont stockés dans des fichiers JSON.
JSON preview
Drill traite un objet JSON comme un enregistrement SQL (équivalent à une ligne dans une table).

Afficher 10 aéroports :Requête affichant 10 aéroports

Afficher le nombre total d’aéroports :Nb total d'aéroport

Afficher le nombre total d’aéroports par état : Requête affichant le nombre total d’aéroports par état

Requête sur les vols :

Les vols sont stockés par année dans des fichiers CSV comportant des en-têtes. Comme la fonction extractHeader est activé dans la définition du format CSV dans Drill : les colonnes peuvent être sélectionnées par leur nom.

Afficher 10 vols :Requête : Afficher 10 vols

Quel est le temps moyen de retard par jour ?

La donnée n’est pas propre : certaines valeurs de ArrDelay ne sont pas renseignées.
Une première solution est d’effectuer une requête en excluant dans la clause where les valeurs où ‘ArrDelay’ = NARequête : Quel est le temps moyen de retard par jour ?

Requête sur les aéroports et les vols – Temps moyen de retard par aéroport de destination

Dans cette requête, nous allons effectuer une requête à la fois sur des fichiers CSV et JSON.
Requête : Temps moyen de retard par aéroport de destination

4 – Conclusion

Comme vous avez pu le constater, dès lors qu’on sait faire du SQL, on sait manipuler Drill.
On s’abstrait alors complètement du fait que les données soient stockées dans des formats semi-structurés différents.

Cependant, Drill n’est pas magique et comme tout framework data, il faut vérifier que vos formats de fichiers sont corrects. Attention aux caractères « unprintables » qui peuvent se cacher dans vos fichiers…

Bref, Drill est facile à utiliser, performant, et open-source et permet de répondre à un réel besoin : enrichir le self-service analytics !

Maintenant, vous êtes prêts pour jouer avec Drill et découvrir les concepts suivants que nous n’avons pas abordé dans cet article : création de vue, optimisation avec des parquets files, Drill en mode distribué, connexion à HDFS, à des bases de données NoSQL…

Nous attendons avec impatience vos retours sur cette techno !

Alexia Audevart Author: Alexia Audevart

Data & Enthusiasm @ekito;
Co-Organizer of Toulouse Data Science TDS meet-up;
My hashtags: #BigData #DataScience #Spark #DataVizualisation #MachineLearning

Like it?  Share  it!

Share Button
What do  You  think? Write a comment!

Leave a Reply

Required fields are marked *.


CommentLuv badge