Apache Drill : Sql distribué - fédérateur de données

Apache Drill est un moteur Sql distribué permettant de fédérer les données non-relationnelles (NoSql databases, file systems, hadoop, Amazon S3, Azure, …) et les données relationnelles (oracle, mysql, mssql, posgresql, ...) et ce, en utilisant le Sql Ansi.

Exemple : vous pouvez requêter en Sql Ansi les données dans hive avec croisement avec les données dans oracle, ou un fichier json croisé avec les données dans MongoDb, … C'est une sorte de dblink mutli-sources. 

Une simple requête peut joindre multiple données depuis plusieurs sources de données.

Pour plus de détails : https://drill.apache.org/

Ce tutoriel utilise le mode "embedded" d'Apache Drill (installation locale), pour une installation distribuée, vous avez toutes les informations nécessaires ici : https://drill.apache.org/docs/distributed-mode-prerequisites/

Installation en mode embedded (sous Windows):

Pré-requis :
- Télécharger Apache Drill ici https://drill.apache.org/download/ et de-zippe le fichier dans un répertoire de votre choix
- Assurez-vous d'avoir une variable d'environnement JAVA_HOME qui pointe sur un jdk 1.8, et que cet emplacement est dans le path du système d'exploitation

Lancement d'Apache Drill :
- Depuis la ligne de commande de windows, positionnez-vous dans le répertoire bin du répertoire d'installation d'Apache Drill, exemple : C:\dev\apache-drill-1.13.0\bin>
- Lancer la commande :sqlline.bat -u "jdbc:drill:zk=local"
- Si tout ce passe bien, vous aurez un message qui ressemble à :

INFOS: Initiating Jersey application, version Jersey: 2.8 2014-04-29 01:25:26...
apache drill 1.13.0
"this isn't your grandfather's sql"
0: jdbc:drill:zk=local>

Depuis cette ligne de commande, vous pouvez explorer les données exemples embarquées dans Apache Drill en exécutant par exemples les requêtes suivantes :

SELECT * FROM cp.`employee.json` Limit 20;

SELECT position_title,max(salary),min(salary)
FROM cp.`employee.json` group by position_title;

Pour quitter Apache Drill, dans la ligne de commande taper : !quit

Ajout de "Data Source / Storage"

Chaque source de données doit être déclarer pour être utiliser dans Apache Drill : file system hadoop, bdd oracle, bdd posgresql, mongodb, ...

La déclaration est à faire dans l'url d'administration des "Storage" : http://localhost:8047/storage

Exemple ajout d'une base de données Postgresql :

- récupérer le driver jdbc de votre version de postgresql ici: https://jdbc.postgresql.org/download.html
- copier ce driver jdbc dans le répertoire "..\jars\3rdparty\" de votre installation Apache Drill
- dans http://localhost:8047/storage , créer un nouveau storage, donner un nom (le mien c'est postgreSakila), et dans configuration, renseigner les paramètres suivants (à personnaliser) :

{
  "type": "jdbc",
  "driver": "org.postgresql.Driver",
  "url": "jdbc:postgresql://serveur:port/postgres",
  "username": "postgres",
  "password": "password",
  "enabled": true
}

Exemple ajout d'une base de données MySql :

- récupérer le driver jdbc de votre version de MySql ici: https://dev.mysql.com/downloads/connector/j/
- copier ce driver jdbc dans le répertoire "..\jars\3rdparty\" de votre installation Apache Drill
- dans http://localhost:8047/storage , créer un nouveau storage, donner un nom (le mien mysqlSakila), et dans configuration, renseigner les paramètres suivants (à personnaliser) :

{
  "type": "jdbc",
  "driver": "com.mysql.jdbc.Driver",
  "url": "jdbc:mysql://machine:port",
  "username": "root",
  "password": "sakila",
  "enabled": true
}

Ici j'ai simulé une migration du bdd MySql Sakila vers PostgreSql. Mon but c'est de comparer si les données de la base Sakila versées dans PostgreSql sont équivalente à sa source MySql:

select a.actor_id,b.actor_id as actor_id_b,a.first_name,b.first_name as first_name_b 
from postgreSakila.public.actor a 
join mysqlSakila.sakila.actor b on a.actor_id=b.actor_id;

Je peux joindre facilement les données des deux bases, la comparaison est facilitée.

Utilisation d'un Sql Client, exemple Squirrel
- Télécharger Squirrel ici et de-zippe-le dans un répertoire de votre choix: https://sourceforge.net/projects/squirrel-sql/files/1-stable/3.8.1-plainzip/

- Récupérer le driver jdbc drill-jdbc-all-1.13.0.jar d'Apache Drill dans le répertoire ..\jars\jdbc-driver. Ce fichier est à copier dans le répertoire lib de l'installation de Squirrel.

- Dans l'onglet Drivers cliquer sur "+" pour ajouter le driver Apache Drill :

- Renseigner les informations comme suit :

C'est tout, vous pouvez créer une connexion à votre Apache Drill

Divers :

- Pour savoir la liste des tables (vue,...) accessibles :

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.`TABLES`
Where TABLE_TYPE = 'TABLE'
ORDER BY TABLE_NAME DESC;

- Utilisation d'Apache Drill par ODBC, voir le site :

https://drill.apache.org/docs/installing-the-driver-on-windows/