Hive Join & SubQuery Tutorial με παραδείγματα

Ενώστε ερωτήματα

Τα ερωτήματα σύνδεσης μπορούν να εκτελεστούν σε δύο πίνακες που υπάρχουν στο ΚυψέληΓια να κατανοήσουμε με σαφήνεια τις έννοιες της ένωσης, δημιουργούμε δύο πίνακες εδώ:

• sample_joins (σχετίζεται με τα στοιχεία του πελάτη)
• sample_joins1 (σχετίζεται με λεπτομέρειες παραγγελίας που υποβάλλονται από υπαλλήλους)

Βήμα 1) Δημιουργία του πίνακα “sample_joins” με τα ονόματα στηλών Id, Name, Age, address και μισθός των υπαλλήλων. Το παρακάτω στιγμιότυπο οθόνης δείχνει την εντολή CREATE TABLE και την επιβεβαίωσή της.

Βήμα 2) Φόρτωση και εμφάνιση δεδομένων. Το επόμενο στιγμιότυπο οθόνης δείχνει την εντολή φόρτωσης ακολουθούμενη από τα περιεχόμενα του πίνακα.

Από το παραπάνω στιγμιότυπο οθόνης:

1. Φόρτωση δεδομένων σε sample_joins από Customers.txt
2. Εμφάνιση περιεχομένων πίνακα sample_joins

Βήμα 3) Δημιουργία του πίνακα sample_joins1 και, στη συνέχεια, φόρτωση και εμφάνιση των δεδομένων του, όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης.

Από το παραπάνω στιγμιότυπο οθόνης, μπορούμε να παρατηρήσουμε τα εξής:

1. Δημιουργία του πίνακα sample_joins1 με τις στήλες Orderid, Date1, Id και Amount
2. Φόρτωση δεδομένων στο sample_joins1 από το orders.txt
3. Εμφάνιση εγγραφών που υπάρχουν στο sample_joins1

Προχωρώντας, θα δούμε τους διαφορετικούς τύπους συνδέσεων που μπορούν να εκτελεστούν στους πίνακες που έχουμε δημιουργήσει. Πριν από αυτό, πρέπει να λάβετε υπόψη τα ακόλουθα σημεία σχετικά με τις συνδέσεις.

Μερικά σημεία που πρέπει να προσέξετε στις ενώσεις:

• Μόνο οι ισότιμες ενώσεις επιτρέπονται στις ενώσεις
• Στο ίδιο ερώτημα μπορούν να ενωθούν περισσότεροι από δύο πίνακες
• Οι συνδέσεις LEFT, RIGHT και FULL OUTER υπάρχουν για να παρέχουν περισσότερο έλεγχο στην ρήτρα ON για την οποία δεν υπάρχει αντιστοίχιση.
• Οι ενώσεις δεν είναι αντιμεταθετικές
• Οι συνδέσεις είναι αριστερές συνειρμικές, ανεξάρτητα από το αν είναι ΑΡΙΣΤΕΡΕΣ ή ΔΕΞΙΕΣ ενώσεις

Ο περιορισμός ισότητας αντικατοπτρίζει το Hive όπως ήταν για πολλά χρόνια. Από το Hive 2.2.0 και μετά, υποστηρίζονται σύνθετες εκφράσεις στον όρο ON (HIVE-15211), επομένως μια συνθήκη μη ισότητας γίνεται αποδεκτή σε μια τρέχουσα έκδοση. Σε παλαιότερες εκδόσεις, η συνθήκη πρέπει να είναι μια δοκιμή ισότητας, με οτιδήποτε άλλο να μετακινείται σε έναν όρο WHERE.

Διαφορετικοί τύποι ενώσεων

Οι ενώσεις είναι 4 τύπων. Αυτές είναι:

• Εσωτερική σύνδεση
• Αριστερή εξωτερική ένωση
• Δεξιά εξωτερική ένωση
• Πλήρης εξωτερική ένωση

Κάθε τύπος παρουσιάζεται παρακάτω σε σχέση με τους ίδιους δύο πίνακες, επομένως το μόνο που αλλάζει μεταξύ των παραδειγμάτων είναι ποιες γραμμές που δεν ταιριάζουν επιβιώνουν.

Εσωτερική σύνδεση

Οι εγγραφές που είναι κοινές και στους δύο πίνακες θα ανακτηθούν από αυτήν την εσωτερική ένωση. Το αποτέλεσμα στο παρακάτω στιγμιότυπο οθόνης περιέχει μόνο τους πελάτες που έχουν αντίστοιχη παραγγελία.

Από το παραπάνω στιγμιότυπο οθόνης, μπορούμε να παρατηρήσουμε τα εξής:

1. Εδώ εκτελούμε ένα ερώτημα σύνδεσης χρησιμοποιώντας τη λέξη-κλειδί JOIN μεταξύ των πινάκων sample_joins και sample_joins1, με την αντίστοιχη συνθήκη (c.Id = o.Id).
2. Η έξοδος εμφανίζει τις κοινές εγγραφές που υπάρχουν και στους δύο πίνακες, οι οποίες επιλέγονται ελέγχοντας τη συνθήκη που αναφέρεται στο ερώτημα.

Ερώτηση:

SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);

Αριστερά εξωτερική εγγραφή

• HiveQL Η συνάρτηση LEFT OUTER JOIN επιστρέφει όλες τις γραμμές από τον αριστερό πίνακα, παρόλο που δεν υπάρχουν αντιστοιχίες στον δεξιό πίνακα.
• Εάν ο όρος ON ταιριάζει με μηδενικές εγγραφές στον δεξιό πίνακα, η συνένωση εξακολουθεί να επιστρέφει μια εγγραφή στο αποτέλεσμα με NULL σε κάθε στήλη από τον δεξιό πίνακα.

Το παρακάτω στιγμιότυπο οθόνης δείχνει ότι εμφανίζεται κάθε πελάτης, συμπεριλαμβανομένων εκείνων που δεν έχουν παραγγελία.

Από το παραπάνω στιγμιότυπο οθόνης, μπορούμε να παρατηρήσουμε τα εξής:

1. Εδώ εκτελούμε ένα ερώτημα σύνδεσης χρησιμοποιώντας τη λέξη-κλειδί "LEFT OUTER JOIN" μεταξύ των πινάκων sample_joins και sample_joins1, με την αντίστοιχη συνθήκη (c.Id = o.Id). Για παράδειγμα, εδώ χρησιμοποιούμε το αναγνωριστικό υπαλλήλου ως αναφορά. Ελέγχει εάν το αναγνωριστικό είναι κοινό τόσο στον δεξιό όσο και στον αριστερό πίνακα. Λειτουργεί ως η αντίστοιχη συνθήκη.
2. Η έξοδος εμφανίζει τις εγγραφές που επιλέχθηκαν από τη συνθήκη που αναφέρεται στο ερώτημα. Οι τιμές NULL στην παραπάνω έξοδο είναι στήλες χωρίς τιμές από τον δεξιό πίνακα, δηλαδή sample_joins1.

Ερώτηση:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Δεξιά εξωτερική συμμετοχή

• Η συνάρτηση HiveQL RIGHT OUTER JOIN επιστρέφει όλες τις γραμμές από τον δεξιό πίνακα, παρόλο που δεν υπάρχουν αντιστοιχίες στον αριστερό πίνακα.
• Εάν ο όρος ON ταιριάζει με μηδενικές εγγραφές στον αριστερό πίνακα, η συνένωση εξακολουθεί να επιστρέφει μια εγγραφή στο αποτέλεσμα με NULL σε κάθε στήλη από τον αριστερό πίνακα.
• Οι RIGHT joins επιστρέφουν πάντα εγγραφές από τον δεξιό πίνακα και αντίστοιχες εγγραφές από τον αριστερό πίνακα. Εάν ο αριστερός πίνακας δεν έχει τιμή που να αντιστοιχεί στη στήλη, θα επιστρέψει τιμές NULL σε αυτήν τη θέση.

Το παρακάτω στιγμιότυπο οθόνης δείχνει την κατοπτρική εικόνα του προηγούμενου αποτελέσματος: εμφανίζεται κάθε παραγγελία, αντιστοιχισμένη ή όχι.

Από το παραπάνω στιγμιότυπο οθόνης, μπορούμε να παρατηρήσουμε τα εξής:

1. Εδώ εκτελούμε ένα ερώτημα σύνδεσης χρησιμοποιώντας τη λέξη-κλειδί "RIGHT OUTER JOIN" μεταξύ των πινάκων sample_joins και sample_joins1, με την αντίστοιχη συνθήκη (c.Id = o.Id).
2. Η έξοδος εμφανίζει τις εγγραφές που επιλέχθηκαν ελέγχοντας τη συνθήκη που αναφέρεται στο ερώτημα.

Ερώτηση:

  SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Πλήρης εξωτερική συμμετοχή

Συνδυάζει εγγραφές και των δύο πινάκων sample_joins και sample_joins1 με βάση τη συνθήκη JOIN που δίνεται στο ερώτημα.

Επιστρέφει όλες τις εγγραφές και από τους δύο πίνακες και συμπληρώνει τιμές NULL για τις στήλες των οποίων οι αντίστοιχες τιμές λείπουν σε κάθε πλευρά, όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης.

Από το παραπάνω στιγμιότυπο οθόνης, μπορούμε να παρατηρήσουμε τα εξής:

1. Εδώ εκτελούμε ένα ερώτημα σύνδεσης χρησιμοποιώντας τη λέξη-κλειδί "FULL OUTER JOIN" μεταξύ των πινάκων sample_joins και sample_joins1, με την αντίστοιχη συνθήκη (c.Id = o.Id).
2. Η έξοδος εμφανίζει όλες τις εγγραφές που υπάρχουν και στους δύο πίνακες, οι οποίες επιλέγονται ελέγχοντας τη συνθήκη που αναφέρεται στο ερώτημα. Οι τιμές NULL στην έξοδο εδώ υποδεικνύουν τις τιμές που λείπουν από τις στήλες και των δύο πινάκων.

Ερώτηση:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Υποερωτήματα

Οι ενώσεις τοποθετούν πίνακες δίπλα-δίπλα. Ένα υποερώτημα κάνει κάτι διαφορετικό: ενθυλακώνει ένα ερώτημα μέσα σε ένα άλλο, έτσι ώστε το εξωτερικό ερώτημα να μπορεί να λειτουργήσει από ένα αποτέλεσμα που έχει ήδη υπολογιστεί.

Ένα ερώτημα που υπάρχει μέσα σε ένα ερώτημα είναι γνωστό ως υποερώτημα. Το κύριο ερώτημα θα εξαρτηθεί από τις τιμές που επιστρέφονται από το υποερώτημα.

Τα υποερωτήματα μπορούν να ταξινομηθούν σε δύο τύπους:

• Υποερωτήματα στον όρο FROM
• Υποερωτήματα στον όρο WHERE

Πότε πρέπει να χρησιμοποιήσετε:

• Για να λάβετε μια συγκεκριμένη τιμή συνδυασμένη από δύο τιμές στηλών από διαφορετικούς πίνακες
• Εξάρτηση των τιμών ενός πίνακα από άλλους πίνακες
• Συγκριτικός έλεγχος των τιμών μιας στήλης με άλλους πίνακες

Σύνταξη:

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);

Παράδειγμα:

SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2

Εδώ, τα t1 και t2 είναι ονόματα πινάκων. Η εσωτερική πρόταση είναι το υποερώτημα που εκτελείται στον πίνακα t1. Εδώ, τα a και b είναι στήλες που προστίθενται στο υποερώτημα και αντιστοιχίζονται στη στήλη 1. Η στήλη 1 είναι η τιμή της στήλης που υπάρχει στον κύριο πίνακα. Αυτή η στήλη "στήλη 1" που υπάρχει στο υποερώτημα είναι ισοδύναμη με το ερώτημα του κύριου πίνακα στη στήλη 1.

Ενσωμάτωση προσαρμοσμένων σεναρίων

Ενώ ένα υποερώτημα αναδιαμορφώνει δεδομένα μόνο με το HiveQL, ένα ενσωματωμένο σενάριο μετατρέπει γραμμές σε κώδικα γραμμένο εκτός του Hive.

Το Hive καθιστά δυνατή τη σύνταξη σεναρίων ειδικά για τον χρήστη για τις απαιτήσεις του πελάτη. Οι χρήστες μπορούν να γράψουν τον δικό τους χάρτη και να μειώσουν τα σενάρια για αυτές τις απαιτήσεις. Αυτά ονομάζονται ενσωματωμένα προσαρμοσμένα σενάρια. Η λογική κωδικοποίησης ορίζεται στο προσαρμοσμένο σενάριο και μπορούμε να χρησιμοποιήσουμε αυτό το σενάριο κατά την ώρα ETL.

Πότε να επιλέξετε ενσωματωμένα σενάρια:

• Όπου οι απαιτήσεις του πελάτη σημαίνουν ότι οι προγραμματιστές πρέπει να γράφουν και να αναπτύσσουν σενάρια στο Hive
• Όπου οι ενσωματωμένες συναρτήσεις του Hive δεν πρόκειται να λειτουργήσουν για συγκεκριμένες απαιτήσεις τομέα

Για αυτό, το Hive χρησιμοποιεί την ρήτρα TRANSFORM για να ενσωματώσει σενάρια map και reducer.

Σε αυτά τα ενσωματωμένα προσαρμοσμένα σενάρια, πρέπει να παρατηρήσουμε τα ακόλουθα σημεία:

• Οι στήλες θα μετατραπούν σε συμβολοσειρά και θα οριοθετηθούν με TAB πριν δοθούν στο σενάριο χρήστη.
• Η τυπική έξοδος του σεναρίου χρήστη θα αντιμετωπίζεται ως στήλες συμβολοσειρών διαχωρισμένες με TAB

Δείγμα ενσωματωμένου σεναρίου:

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;

Από το παραπάνω σενάριο, μπορούμε να παρατηρήσουμε τα εξής. Αυτό είναι μόνο ένα δείγμα σεναρίου για την κατανόησή του.

• Το pv_users είναι ο πίνακας χρηστών, ο οποίος έχει πεδία όπως userid και date όπως αναφέρονται στο map_script.
• Το σενάριο reducer ορίζεται στην ημερομηνία και τον αριθμό του πίνακα pv_users

Συχνές Ερωτήσεις