Hive¶

Premier exemple : Wordcount¶

Nous reprenons l'exemple de comptage de mots utilisé dans l'atelier MapReduce pour le résoudre avec Hive.

wordcount en HiveQL
CREATE EXTERNAL TABLE lines (line STRING); -- (1) 
LOAD DATA INPATH '/user/uti/shakespeare.txt' OVERWRITE INTO TABLE lines;
CREATE TABLE words AS SELECT explode(split(line, ' ')) AS word FROM lines;
CREATE TABLE word_counts AS SELECT word, count(1) AS nb FROM words
GROUP BY word
ORDER BY nb DESC;

EXTERNAL permet d'empêcher de supprimer le fichier shakespeare.txt original après son chargement avec Hive.

Suivre les étapes suivantes pour exécuter le code HiveQL :

Lancer la machine virtuelle et accéder à Shell Hive (Beehive) à partir du terminal
```
hive
```
Vous aurez alors l'invite hive :
```
hive>
```
Maintenant taper les commandes du code fourni.

Services requis pour Hive

Le shell Hive a besoin des services suivants :

MySQL
hive-metastore
hive-server2

Utiliser la commande service nom_service start pour les démarrer en cas de besoin.

Les lignes du code permettent respectivement de :

Créer une table externe linesavec une colonne unique line.

Charger le contenu du fichier shakespeare.txt à partir de HDFS dans la table précédente. Vérifier, après cette commande, la création du fichier /user/hive/warehouse/lines/shakespeare.txt.

Créer une deuxième table words pour stocker le résultat d'une requête pour décomposer chaque ligne (line) du texte dans la table lines en mot avec les fonctions explodeet split.

Créer une table word_counts structurée en 2 colonnes : word pour le mot et nb pour le nombre d'occurences obtenu par le résultat de la fonction count à partir de la table words.

Grouper par mot.

Trier par nombre d'occurences décroissant

Vérifier la création de Jobs MapReduce pour les différentes requêtes SELECT sur http://localhost:8088

Un dossier word_counts est créé dans /user/hive/warehouse où des fichiers (1 dans note cas 000000_0) contiennent les nombres d'occurences associés aux mots du fichier.

Afficher le résultat :

hadoop fs -cat /user/hive/warehouse/word_counts/000000_0 | more

HiveQL¶

Préparer les données

Dans cette section, nous allons utiliser les mêmes données de la section Pig. Si les fichiers csv ne sont pas déjà téléchargés alors procéder aux étapes suivantes :

Créer un dossier demo : mkdir ~/demo
Télécharger les fichiers csv dans /home/uti/demo :
Envoyer les données sur HDFS
```
hadoop fs -put /home/uti/demo
```

LDD¶

Créer une base de données demo

CREATE DATABASE demo;

Sélectionner une base de données

USE demo;
-- La base de données par défaut est DEFAULT

Créer les tables

create table employee
(id int,
 fname string,
 lname string,
 department_id int
 )
 ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';


create table department
(id int,
 dept_name string
 )
 ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = ",",
   "quoteChar"     = "\""
)  ;

create external table salary
(
salary_id int,
employee_id int,
payment double,
payment_date date
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
location '/user/uti/salary';

Contraintes d'intégrité

Les contraintes primary key et foreign key ne sont supportées qu'à partir de la version 2.

Les contraintes unique, not null et check sont ajoutées depuis la version3.

Ajouter une colonne à une table

CREATE TABLE test(id int);
ALTER TABLE test ADD COLUMNS (name string, dob date);

Supprimer une table

DROP TABLE test;

SHOW/DESCRIBE

Afficher les objets disponibles

Show Databases;
Show Tables;
Show Partitions <table>;
Show TblProperties <table>;
Show Create Table <table>;
Show Indexes on <table>;
Show Columns in <table>;
Show Functions;
Show transactions;

Afficher la strucuture ou les propriétés d'un objet

Describe database <db_name>;
Describe <table>;
Describe <table.column>;

LMD¶

Charger des données à partir des fichiers CSV depuis HDFS

LOAD DATA INPATH '/user/uti/demo/employee.csv' INTO TABlE employee;
LOAD DATA INPATH '/user/uti/demo/department.csv' INTO TABlE department;
LOAD DATA INPATH '/user/uti/demo/salary.csv' INTO TABlE salary;

Remarque

Vérifier sur HDFS l'emplacement, le type et le format des fichiers de données relatifs à ces tables.

Insertion de données

CREATE TABLE jours(num int, nom string);
INSERT INTO jours VALUES(1, 'Lundi');
INSERT INTO jours VALUES(2, 'Mardi');
INSERT INTO jours VALUES(3, 'Mercredi');

MapReduce

La commande d'insertion génère un job MapReduce que vous pouvez consulter sur YARN.

Mise à jour et suppression

Pour exécuter ces opérations, quelques conditions doivent être vérifiées :

La table utilise le format ORC
La table supporte le Bucketing
Les transactions ACID sont activées sur Hive

Essayer de mettre à jour la table jours :

UPDATE jours set nom = upper(nom) WHERE num = 1;

Une erreur est alors affichée. Pour pouvoir exécuter ces requêtes, nous commençons par activer les transactions sous Hive. Ceci est possible avec l'une des deux méthodes :

Modifier le fichier hive-site.xml en ajoutant les lignes suivante et puis redémarrer Hive :

<property>
    <name>hive.support.concurrency</name>
    <value>true</value>
</property>
<property>
    <name>hive.txn.manager</name>
    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
    <name>hive.enforce.bucketing</name>
    <value>true</value>
</property>
<property>
    <name>hive.exec.dynamic.partition.mode</name>
    <value>nostrict</value>
</property>
<property>
    <name>hive.compactor.initiator.on</name>
    <value>true</value>
</property>
<property>
    <name>hive.compactor.worker.threads</name>
    <value>1</value>
</property>

2. À partir du shell hive :

SET hive.support.concurrency=true;
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
# Pour Hive 2.0
SET hive.enforce.bucketing=true;
SET hive.exec.dynamic.partition.mode=nostrict;
# Pour hive metastore
SET hive.compactor.initiator.on=true;
SET hive.compactor.worker.threads=1;

Ensuite créer une table supportant les transactions :

CREATE TABLE employee_trans (
 id int,
 name string,
 age int,
 gender string)
 clustered by (gender) into 2 buckets
 STORED AS ORC
 TBLPROPERTIES ('transactional'='true');

-- insérer des données
INSERT INTO employee_trans VALUES(1,'James',30,'M');
INSERT INTO employee_trans VALUES(2,'Ann',40,'F');
INSERT INTO employee_trans VALUES(3,'Jeff',41,'M');
INSERT INTO employee_trans VALUES(4,'Jennifer',20,'F');

-- Mise à jour
UPDATE employee_trans
SET age=45
WHERE id=3;

-- Suppression
DELETE FROM employee_trans
WHERE id=4;

-- Vérifier les données
SELECT * FROM employee_trans;

SELECT¶

Format de la requête :

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[ORDER BY col_list]
[LIMIT [offset,] rows]

Reprenons les mêmes exemples de la section Pig.

Filtrage pour avoir les employés (sans doublons) ayant reçu un salaire compris entre 5000 et 7000

SELECT distinct employee_id
FROM salary
WHERE payment >= 5000 AND payment <= 7000;

Nombre d'employés par département et afficher les 3 plus grands départements

SELECT department_id, count(id) AS nb_emp
FROM employee
GROUP BY department_id
ORDER BY nb_emp DESC
LIMIT 3;

Jointure : trouver le nom du département de chaque employé (afficher les 4 premiers employés)

SELECT fname, lname, dept_name
FROM employee JOIN department ON (employee.department_id = department.id) limit 4;

Requête imbriquée : Trouver le nombre moyen d'employés par département

SELECT AVG(nb_emp)
FROM (SELECT department_id, count(id) AS nb_emp
FROM employee
GROUP BY department_id
) q1;

Références HiveQL¶

Pour une référence complète du langage Pi Latin, aller sur la page de documentation (ici)

Voici aussi, un mémo du langage :

Ouvrir dans un nouvel onglet

Exercices¶

Exercice 1 ¶

Reprendre l'exercice de la section Pig en utilisant Hive.

Exercice 2 ¶

Le fichier de données utilisé dans cet examen est CrimeData_200K.csv qui comporte 200 milles lignes de 5 colonnes.

Un aperçu des 6 première lignes est donné dans la figure ci-après :

Dc_Dist,Dispatch_Date_Time,Text_General_Code,Lon,Lat
25,2010-03-03 11:57:00,Fraud,-75.117665,40.019968
15,2008-02-05 15:10:00,All Other Offenses,-75.083062,40.027315
24,2015-03-17 22:17:00,Prostitution and Commercialized Vice,-75.113389,39.996459
12,2010-07-11 23:19:00,All Other Offenses,-75.223246,39.937435
2,2006-12-05 22:59:00,All Other Offenses,-75.102684,40.044051

Le fichier contient des informations sur des actes criminels décrits par :

Colonne	Description
Dc_Dist	Numéro de district
Dispatch_Date_Time	Date et heure de prise en charge de l’incident
Text_General_code	Catégories du crime
Lon	Longitude
Lat	Latitude

Le département de Police vous a recruté pour l’aider à préparer les données pour créer des tableaux de bord.

Télécharger le fichier CSV depuis l’URL : https://tinyurl.com/crimesdataset puis créer une base de données contenant une table avec Hive et charger les données du fichier précédent dans cette même table.
Donner les requêtes Hive pour répondre aux questions suivantes :
1. Combien y-a-t-il de districts ?
2. Donner le pourcentage de crimes par district.
3. Quels sont les 3 mois avec le plus grand nombre d’incidents enregistrés. (La fonction month permet d’extraire le mois de la date)