Tutoriel Hive Join & SubQuery avec exemples
David Carter
Rejoindre des requêtes
Les requêtes de jointure peuvent être exécutées sur deux tables présentes dans Hive. Pour comprendre Rejoignez Concepts en clair ici nous créons deux tables ici,
- Sample_joins (lié aux détails des clients)
- Sample_joins1 (lié aux détails des commandes effectuées par les employés)
Étape 1) Création de la table « sample_joins » avec les noms de colonnes ID, Nom, Âge, adresse et salaire des employés
Étape 2) Chargement et affichage des données
D'après la capture d'écran ci-dessus
- Chargement des données dans sample_joins à partir de Customers.txt
- Affichage du contenu de la table sample_joins
Étape 3) Création de la table sample_joins1 et chargement, affichage des données
À partir de la capture d'écran ci-dessus, nous pouvons observer ce qui suit
- Création de la table sample_joins1 avec les colonnes Orderid, Date1, Id, Amount
- Chargement des données dans sample_joins1 à partir deorders.txt
- Affichage des enregistrements présents dans sample_joins1
À l'avenir, nous verrons différents types de jointures qui peuvent être effectuées sur les tables que nous avons créées, mais avant cela, vous devez considérer les points suivants pour les jointures.
Quelques points à observer dans les jointures :
- Seules les jointures d'égalité sont autorisées dans les jointures
- Plus de deux tables peuvent être jointes dans la même requête
- Les jointures LEFT, RIGHT, FULL OUTER existent afin de fournir plus de contrôle sur la clause ON pour laquelle il n'y a pas de correspondance
- Les jointures ne sont pas commutatives
- Les jointures sont associatives à gauche, qu'il s'agisse de jointures GAUCHE ou DROITE.
Différents types de jointures
Les jointures sont de 4 types, ce sont
- Jointure interne
- Jointure externe gauche
- Jointure externe droite
- Jointure externe complète
Jointure interne:
Les Records communs aux deux tables seront récupérés par cette Inner Join.
À partir de la capture d'écran ci-dessus, nous pouvons observer ce qui suit
- Ici, nous effectuons une requête de jointure en utilisant le mot-clé JOIN entre les tables sample_joins et sample_joins1 avec la condition de correspondance telle que (c.Id= o.Id).
- La sortie affichant les enregistrements communs présents dans la table en vérifiant la condition mentionnée dans la requête
requête:
SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);
Jointure externe gauche:
- Langage de requête Hive LEFT OUTER JOIN renvoie toutes les lignes du tableau de gauche même s'il n'y a aucune correspondance dans le tableau de droite
- Si la clause ON ne correspond à aucun enregistrement dans la table de droite, les jointures renvoient toujours un enregistrement dans le résultat avec NULL dans chaque colonne de la table de droite.
À partir de la capture d'écran ci-dessus, nous pouvons observer ce qui suit
- Ici, nous effectuons une requête de jointure en utilisant le mot-clé « LEFT OUTER JOIN » entre les tables sample_joins et sample_joins1 avec la condition de correspondance comme (c.Id= o.Id).Par exemple ici, nous utilisons l'identifiant de l'employé comme référence, il vérifie si l'identifiant est commun à droite et à gauche de la table ou non. Il agit comme une condition de correspondance.
- La sortie affichant les enregistrements communs présents dans les deux tables en vérifiant la condition mentionnée dans la requête. Les valeurs NULL dans la sortie ci-dessus sont des colonnes sans valeurs de la table de droite qui est sample_joins1
requête:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Jointure externe droite :
- Le langage de requête Hive RIGHT OUTER JOIN renvoie toutes les lignes de la table de droite même s'il n'y a aucune correspondance dans la table de gauche
- Si la clause ON correspond à zéro enregistrement dans la table de gauche, les jointures renvoient toujours un enregistrement dans le résultat avec NULL dans chaque colonne de la table de gauche.
- Les jointures RIGHT renvoient toujours les enregistrements d'une table de droite et les enregistrements correspondants de la table de gauche. Si la table de gauche n'a aucune valeur correspondant à la colonne, elle renverra des valeurs NULL à cet endroit.
À partir de la capture d'écran ci-dessus, nous pouvons observer ce qui suit
- Ici, nous effectuons une requête de jointure en utilisant le mot-clé « RIGHT OUTER JOIN » entre les tables sample_joins et sample_joins1 avec la condition de correspondance comme (c.Id= o.Id).
- La sortie affichant les enregistrements communs présents dans la table en vérifiant la condition mentionnée dans la requête
Question:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Jointure externe complète :
Il combine les enregistrements des tables sample_joins et sample_joins1 en fonction de la condition JOIN donnée dans la requête.
Il renvoie tous les enregistrements des deux tables et remplit les valeurs NULL pour les colonnes manquant de valeurs correspondant de chaque côté.
À partir de la capture d'écran ci-dessus, nous pouvons observer ce qui suit :
- Ici, nous effectuons une requête de jointure en utilisant le mot-clé « FULL OUTER JOIN » entre les tables sample_joins et sample_joins1 avec la condition de correspondance comme (c.Id= o.Id).
- La sortie affichant tous les enregistrements présents dans la table en vérifiant la condition mentionnée dans la requête. Les valeurs nulles en sortie indiquent ici les valeurs manquantes dans les colonnes des deux tables.
Question
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Sous-requêtes
Une requête présente dans une requête est appelée sous-requête. La requête principale dépendra des valeurs renvoyées par les sous-requêtes.
Les sous-requêtes peuvent être classées en deux types
- Sous-requêtes dans la clause FROM
- Sous-requêtes dans la clause WHERE
Quand utiliser:
- Pour obtenir une valeur particulière combinée à partir de deux valeurs de colonnes provenant de tables différentes
- Dépendance des valeurs d'une table sur d'autres tables
- Vérification comparative des valeurs d'une colonne à partir d'autres tables
syntaxe:
Subquery in FROM clause SELECT <column names 1, 2…n>From (SubQuery) <TableName_Main > Subquery in WHERE clause SELECT <column names 1, 2…n> From<TableName_Main>WHERE col1 IN (SubQuery);
Mise en situation :
SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2
Ici, t1 et t2 sont des noms de table. Celui coloré est une sous-requête effectuée sur la table t1. Ici, a et b sont des colonnes ajoutées dans une sous-requête et affectées à col1. Col1 est la valeur de la colonne présente dans la table principale. Cette colonne « col1 » présente dans la sous-requête est équivalente à la requête de la table principale dans la colonne col1.
Intégration de scripts personnalisés
Ruche offre la possibilité d'écrire des scripts spécifiques à l'utilisateur pour les besoins du client. Les utilisateurs peuvent écrire leur propre carte et réduire les scripts selon les besoins. Ceux-ci sont appelés scripts personnalisés intégrés. La logique de codage est définie dans les scripts personnalisés et nous pouvons utiliser ce script dans le temps ETL.
Quand choisir les scripts intégrés :
- Selon les exigences spécifiques du client, les développeurs doivent écrire et déployer des scripts dans Hive
- Où les fonctions intégrées de Hive ne fonctionneront pas pour des exigences de domaine spécifiques
Pour cela, dans Hive, il utilise la clause TRANSFORM pour intégrer les scripts de mappage et de réduction.
Dans ces scripts personnalisés intégrés, nous devons observer les points suivants
- Les colonnes seront transformées en chaîne et délimitées par TAB avant de les donner au script utilisateur
- La sortie standard du script utilisateur sera traitée sous forme de colonnes de chaînes séparées par des TAB.
Exemple de script intégré,
FROM ( FROM pv_users MAP pv_users.userid, pv_users.date USING 'map_script' AS dt, uid CLUSTER BY dt) map_output INSERT OVERWRITE TABLE pv_users_reduced REDUCE map_output.dt, map_output.uid USING 'reduce_script' AS date, count;
À partir du script ci-dessus, nous pouvons observer ce qui suit
Ceci n'est qu'un exemple de script pour comprendre
- pv_users est la table des utilisateurs qui contient des champs comme l'ID utilisateur et la date comme mentionné dans map_script
- Script réducteur défini sur la date et le nombre des tables pv_users
