Gestion des bases de données

Création d'une base de données

CREATE DATABASE IF NOT EXISTS entrepot_analytics;

Consulter une base de données

USE entrepot_analytics;
DESCRIBE DATABASE entrepot_analytics;

Création avec emplacement HDFS personnalisé

CREATE DATABASE entrepot_logs LOCATION '/data/entrepot_logs';

Suppression d'une base vide

La suppression échoue si la base contient encore des tables.

DROP DATABASE entrepot_analytics;

Suppression récursive (incluant les tables)

DROP DATABASE entrepot_analytics CASCADE;

Lien entre Hive et HDFS

• Chaque base de données Hive correspond à un répertoire HDFS portant l'extension .db
• L'emplacement par défaut se trouve sous /user/hive/warehouse
• Il est possible de personnaliser ce chemin via le mot-clé LOCATION lors de la création

Types de tables disponibles dans Hive

• Table managée (interne)
• Table externe
• Table partitionnée
• Table bucketée (en compartiments)

Syntaxe SQL dans Hive

Schéma de création de table

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] nom_table
  (colonne type_donnee [COMMENT 'description_colonne'], ...)
  [COMMENT 'description_table']
  [PARTITIONED BY (colonne type_donnee [COMMENT 'description'], ...)]
  [CLUSTERED BY (colonne, ...) INTO nb_compartiments BUCKETS
    [SORTED BY (colonne [ASC|DESC], ...)]]
  [ROW FORMAT DELIMITED | SERDE nom_classe
    WITH SERDEPROPERTIES(propriete=valeur, ...)]
  [STORED AS format_fichier]
  [LOCATION chemin_hdfs]

Les crochets [] indiquent des clauses facultatives. Le symbole | signifie un choix entre deux options. L'ordre des clauses doit respecter celui présenté dans le schéma.

Paramètres principaux

• EXTERNAL : déclare une table externe
• COMMENT : ajoute une annotation descriptive sur la table ou une colonne
• PARTITIONED BY : définit les colonnes de partitionnement
• CLUSTERED BY : organise les données en compartiments (buckets)
• SORTED BY : impose un tri au sein de chaque compartiment (usage rare)
• ROW FORMAT DELIMITED : applique le sérialiseur par défaut avec un délimiteur spécifié
• SERDE : utilise un sérialiseur/désérialiseur personnalisé
• STORED AS : indique le format de stockage des fichiers
• LOCATION : fixe le répertoire HDFS de la table

Création et suppression de table

-- Création d'une table simple
CREATE TABLE utilisateurs(
  identifiant INT,
  nom_complet STRING,
  sexe STRING
);

-- Suppression
DROP TABLE utilisateurs;

Tables managées (internes)

Par défaut, toute table créée sans le mot-clé EXTERNAL est une table managée. Les données et la structure sont gérées conjointement par Hive.

CREATE DATABASE IF NOT EXISTS demo_hive;
USE demo_hive;

CREATE TABLE IF NOT EXISTS eleves(
  num INT,
  prenom STRING
);

INSERT INTO eleves VALUES (101, 'alice'), (102, 'bob');

SELECT * FROM eleves;

Tables externes

-- Création d'une table externe pointant vers un répertoire HDFS
CREATE EXTERNAL TABLE mesures_capteurs(
  capteur_id INT,
  valeur STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/capteurs';

-- Aucune donnée n'apparaît tant que le répertoire est vide
SELECT * FROM mesures_capteurs;

-- Chargement de données via HDFS
hadoop fs -put mesures.csv /data/capteurs/

-- Les données sont maintenant lisibles
SELECT * FROM mesures_capteurs;

-- Suppression de la table externe : les fichiers HDFS restent intacts
DROP TABLE mesures_capteurs;

Convertir une table managée en externe (et inversement)

ALTER TABLE eleves SET TBLPROPERTIES('EXTERNAL'='TRUE');

Pour repasser en table managée, remplacez TRUE par FALSE. Notez que les valeurs doivent être en majuscules.

Chargement et export de données

Exemple de table cible

CREATE TABLE demo_hive.journaux_recherche(
  horodatage STRING COMMENT 'Heure de la requête',
  visiteur STRING COMMENT 'Identifiant utilisateur',
  mot_cle STRING COMMENT 'Terme recherché',
  lien STRING COMMENT 'URL visitée'
)
COMMENT 'Table de logs moteur de recherche'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

Méthode 1 : Chargement depuis le système de fichiers local

Cette opération déplace physiquement le fichier (le fichier source n'est pas conservé à l'origine).

LOAD DATA LOCAL INPATH '/home/utilisateur/logs_recherche.txt'
INTO TABLE demo_hive.journaux_recherche;

Méthode 2 : Insertion depuis une requête SELECT

Les fichiers sources restent préservés. La table d'origine peut être interne ou externe.

-- Ajout des données
INSERT INTO TABLE dest SELECT * FROM source;

-- Écrasement complet avant insertion
INSERT OVERWRITE TABLE dest SELECT * FROM source;

Export vers le système local — séparateur par défaut

INSERT OVERWRITE LOCAL DIRECTORY '/home/utilisateur/export_defaut'
SELECT * FROM demo_hive.journaux_recherche;

Export vers le système local — séparateur tabulation

INSERT OVERWRITE LOCAL DIRECTORY '/home/utilisateur/export_tab'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT * FROM demo_hive.journaux_recherche;

Export vers HDFS (absence du mot-clé LOCAL)

INSERT OVERWRITE DIRECTORY '/resultats/export_hdfs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT * FROM demo_hive.journaux_recherche;

Export via la ligne de commande Hive

bin/hive -e "SELECT * FROM demo_hive.journaux_recherche;" > /home/utilisateur/sortie/resultat1.txt

bin/hive -f requete_export.sql > /home/utilisateur/sortie/resultat2.txt

Tables partitionnées

Le principe de division est fondamental dans le traitement de données massives : découper un volume important en sous-ensembles plus petits facilite chaque opération. Hive applique cette logique en segmentant les données selon des critères temporels (jour, heure, etc.), ce qui réduit considérablement la quantité de données lues lors des requêtes.

Syntaxe :

CREATE TABLE nom_table(...)
PARTITIONED BY (colonne_partition TYPE, ...)
ROW FORMAT DELIMITED FIELDS TERMINATED BY 'separateur';

Partitionnement simple

CREATE TABLE resultats_examen(
  etudiant_id STRING,
  matiere_id STRING,
  note INT
)
PARTITIONED BY (mois STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

Partitionnement multi-niveaux

CREATE TABLE resultats_examen_multi(
  etudiant_id STRING,
  matiere_id STRING,
  note INT
)
PARTITIONED BY (annee STRING, mois STRING, jour STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

Chargement dans une partition

LOAD DATA LOCAL INPATH '/opt/data/resultats.txt'
INTO TABLE resultats_examen PARTITION(mois='202406');

Chargement dans des partitions multiples

LOAD DATA LOCAL INPATH '/opt/data/resultats.txt'
INTO TABLE resultats_examen_multi
PARTITION(annee='2024', mois='06', jour='15');

Afficher les partitions existantes

SHOW PARTITIONS resultats_examen;

Ajouter des partitions

ALTER TABLE resultats_examen ADD PARTITION(mois='202407');

-- Ajout simultané de plusieurs partitions
ALTER TABLE resultats_examen ADD PARTITION(mois='202408') PARTITION(mois='202409');

Supprimer une partition

ALTER TABLE resultats_examen DROP PARTITION(mois='202406');

Tables bucketées (en compartiments)

Activation de l'optimisation automatique

Cette configuration aligne le nombre de reducers sur le nombre de compartiments.

SET hive.enforce.bucketing = true;

Création d'une table bucketée

CREATE TABLE catalogue_produits(
  ref_produit STRING,
  libelle STRING,
  fournisseur_id STRING
)
CLUSTERED BY(ref_produit) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

Chargement des données dans une table bucketée

La commande LOAD DATA ne fonctionne pas directement sur les tables bucketées. Il faut passer par une table intermédiaire et une insertion via INSERT SELECT.

-- 1. Créer une table standard
CREATE TABLE produits_temp(
  ref_produit STRING,
  libelle STRING,
  fournisseur_id STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- 2. Charger les données dans la table temporaire
LOAD DATA LOCAL INPATH '/opt/data/produits.csv' INTO TABLE produits_temp;

-- 3. Répartir les données vers la table bucketée
INSERT OVERWRITE TABLE catalogue_produits
SELECT * FROM produits_temp CLUSTER BY(ref_produit);

Pourquoi LOAD DATA est-il inadapté aux tables bucketées ?

La répartition en compartiments repose sur un algorithme de hachage appliqué à la colonne de clustering. Le principe est le suivant :

• Le hachage produit un identifiant numérique à partir d'une valeur donnée. Ce résultat est déterministe : une même valeur produit toujours le même hash.
• Le résultat du hash est ensuite divisé par le nombre de compartiments (modulo). L'écartement vaut toujours l'un des entiers entre 0 et N-1 (où N est le nombre de buckets).
• Les enregistrements ayant la même valeur de colonne de clustering atterrissent systématiquement dans le même copmartiment.

Puisque LOAD DATA déplace simplement les fichiers sans déclencher de traitement MapReduce, aucun calcul de hachage n'est effectué. Seul INSERT SELECT déclenche un job MapReduce capable d'exécuter cette logique de répartition.