ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

September 30, 2022 0 Von admin

Η HQL ή η Hive Query Language είναι μια απλή αλλά ισχυρή γλώσσα ερωτημάτων όπως η SQL, η οποία παρέχει στους χρήστες τη δυνατότητα να εκτελούν αναλύσεις δεδομένων σε μεγάλα σύνολα δεδομένων. Λόγω της συντακτικής ομοιότητάς του με την SQL, η HQL έχει υιοθετηθεί ευρέως μεταξύ των μηχανικών δεδομένων και μπορεί να μαθευτεί γρήγορα από άτομα που είναι καινούργια στον κόσμο των μεγάλων δεδομένων και του Hive.

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

ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

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

Στη συνέχεια, πληκτρολογούμε την ακόλουθη εντολή που μας συνδέει με τον HiveServer2.

!connect jdbc:hive2://m02.itversity.com:10000/;auth=noSasl

Απαιτεί έλεγχο ταυτότητας, επομένως εισάγουμε το όνομα χρήστη και τον κωδικό πρόσβασης για αυτήν την περίοδο λειτουργίας και παρέχουμε τη θέση ή τη διαδρομή όπου έχουμε αποθηκευμένη τη βάση δεδομένων μας. Οι εντολές (υπογραμμισμένες με κόκκινο χρώμα) για αυτό δίνονται παρακάτω.

set hive.metastore.warehouse.dir = /user/username/warehouse;
ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

Τώρα που είμαστε συνδεδεμένοι στον HiveServer2, είμαστε έτοιμοι να ξεκινήσουμε να ρωτάμε τη βάση δεδομένων μας. Αρχικά δημιουργούμε τη βάση δεδομένων μας „demo01“ και μετά πληκτρολογούμε την εντολή για να τη χρησιμοποιήσουμε.

Use demo01;
ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

Τώρα θα παραθέσουμε όλους τους πίνακες που υπάρχουν στη βάση δεδομένων demo01 χρησιμοποιώντας την ακόλουθη εντολή

show tables;
Βάση δεδομένων

Όπως μπορούμε να δούμε παραπάνω, 2 πίνακες „emp“ και „t1“ υπάρχουν ήδη στη βάση δεδομένων demo01. Έτσι, για το σύνολο δεδομένων των πελατών μας, πρόκειται να δημιουργήσουμε έναν νέο πίνακα που ονομάζεται „πελάτες“.

CREATE TABLE IF NOT EXISTS customers (InvoiceNo VARCHAR(255),Stock_Code VARCHAR(255),Description VARCHAR(255),Quantity INT,UnitPrice DECIMAL(6,2),CustomerID INT,Country VARCHAR(255)) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

Τώρα αν τρέξουμε την εντολή “show tables” βλέπουμε την ακόλουθη έξοδο.

Εμφάνιση πινάκων |  Παραγωγή

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

desc customers;
Περιγραφή πελατών

Τώρα ανεβάζουμε το αρχείο new.csv που περιέχει εγγραφές πελατών στον χώρο αποθήκευσης hdfs χρησιμοποιώντας αυτήν την εντολή.

hdfs dfs -put new.csv /user/itv001775/warehouse/demo01.db/customers
αρχεία πελατών

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

load data inpath
'/user/itv001775/warehouse/demo01.db/customers/new.csv' into table customers;
ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

Αυτό ολοκληρώνει το μέρος όπου έχουμε ανεβάσει τα δεδομένα σε hdf και τα φορτώσαμε στον πίνακα του πελάτη που δημιουργήσαμε στη βάση δεδομένων demo01.

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

select * from customers limit 5;
Όριο πελατών 5

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

ΑΝΑΛΥΣΗ ΔΕΔΟΜΕΝΩΝ ΜΕΣΩ HQL

ΠΕΡΙΠΤΩΣΗ 1: Θέλουμε να εισάγετε
στον πίνακα πελατών τα αρχεία ενός πελάτη από τη Γερμανία με πελάτη- 18443 που αγόρασε έναν υπολογιστή παιχνιδιών, αριθμός τιμολογίου 610221,
κωδικός αποθέματος 3427AB, ποσότητα 2 σε τιμή μονάδας 9000.

ΕΡΩΤΗΣΗ:

insert into customers values (‘610221’,’3427AB’,’Gaming PC’,2,9000,18443,’Germany’);
Περίπτωση 1 |  Ερώτηση

Τώρα μπορούμε να ρωτήσουμε τη βάση δεδομένων για να δούμε αν η εγγραφή εισήχθη με επιτυχία.

επιλέξτε * από το όριο πελατών 5.

ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

Όπως βλέπουμε έχει εισαχθεί η εγγραφή.

ΠΕΡΙΠΤΩΣΗ 2: Θέλουμε να δούμε το άθροισμα των αγορών που έκανε κάθε πελάτης μαζί με το τιμολόγιο σε φθίνουσα σειρά.

ΕΡΩΤΗΣΗ: (για ευκολία περιορίζουμε την έξοδο μας στις πρώτες 10 εγγραφές)

select customerid, sum(unitprice) as total_purchase from customers group by customerid order by total_purchase desc limit 10;

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

Περίπτωση 2 |  Ερώτηση
ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

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

ΠΕΡΙΠΤΩΣΗ 3: Θέλουμε να μάθουμε τη μέση τιμή των τσαντών που πωλούνται στους πελάτες μας.

ΕΡΩΤΗΣΗ:

select avg(unitprice) as average_bagPrice from customers where description like '%BAG%';

Σημειώστε ότι στο παραπάνω ερώτημα χρησιμοποιήσαμε τον λογικό τελεστή „όπως“ για να βρούμε το κείμενο από το πεδίο περιγραφής. Το σύμβολο „%“ αντιπροσωπεύει ότι οτιδήποτε μπορεί να μπει πριν και μετά τη λέξη „τσάντα“ στο κείμενο.

Περίπτωση 3 |  Ερώτηση
ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

Μπορούμε να παρατηρήσουμε ότι η μέση τιμή σε όλο το φάσμα των προϊόντων που πωλούνται με την ετικέτα των σακουλών είναι 2,35 (δολάρια, ευρώ ή οποιοδήποτε άλλο νόμισμα). Το ίδιο μπορεί να γίνει και για άλλα άρθρα που μπορούν να βοηθήσουν τις εταιρείες να καθορίσουν τα εύρη τιμών για τα προϊόντα τους για καλύτερη απόδοση πωλήσεων.

ΠΕΡΙΠΤΩΣΗ 4: Θέλουμε να μετρήσουμε τον αριθμό των προϊόντων που πωλήθηκαν και τον μέσο όρο
τιμή των προϊόντων για τις 10 κορυφαίες χώρες σε φθίνουσα σειρά ανά μέτρηση.

ΕΡΩΤΗΣΗ:

select count(*) as number_of_products, sum(unitprice) as totalsum_of_price, country from customers group by country order by totalsum_of_price desc limit 10;

Εδώ μετρήστε

σημαίνει μέτρηση όλων των εγγραφών ξεχωριστά για κάθε χώρα και παραγγελία της παραγωγής με βάση το συνολικό άθροισμα της τιμής των αγαθών που πωλήθηκαν στη συγκεκριμένη χώρα.
Περίπτωση 4 |  Ερώτηση
ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

Περίπτωση 5 | Ερώτημα | ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

Μέσω αυτού του ερωτήματος, μπορούμε να συμπεράνουμε τις χώρες στις οποίες πρέπει να στοχεύουν περισσότερο οι επιχειρήσεις, καθώς τα συνολικά έσοδα που παράγονται από αυτές τις χώρες είναι μέγιστα. ΠΕΡΙΠΤΩΣΗ 5:
εμφανίστε τον πελάτη και τον συνολικό αριθμό προϊόντων που παραγγέλθηκαν για καθένα
πελάτης που έχει ποσότητα μεγαλύτερη από 10, παραγγελθεί φθίνουσα σύμφωνα με

ποσότητα για τους 20 κορυφαίους πελάτες.

select customerid, sum(quantity) as number_of_products from customers group by customerid having number_of_products>10 order by number_of_products desc limit 20;

ΕΡΩΤΗΣΗ:

Για κάθε πελάτη, ομαδοποιούμε τα αρχεία του με βάση το αναγνωριστικό του και βρίσκουμε τον αριθμό των προϊόντων που αγόρασαν με φθίνουσα σειρά αυτού του στατιστικού. Χρησιμοποιούμε επίσης την προϋπόθεση ότι επιλέγονται μόνο εκείνες οι εγγραφές όπου ένας αριθμός προϊόντων είναι μεγαλύτερος από 10.

Σημειώστε ότι χρησιμοποιούμε πάντα την ρήτρα
Περίπτωση 5|  Ερώτηση

Ερώτημα 5 | Παραγωγή

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

ΔΩΡΟ

Το Hive έχει μια εκπληκτική δυνατότητα ταξινόμησης των δεδομένων μας μέσω της ρήτρας „Ταξινόμηση κατά“. Κάνει σχεδόν την ίδια λειτουργία με την πρόταση „order by“ καθώς και τα δύο τακτοποιούν τα δεδομένα σε αύξουσα ή φθίνουσα σειρά. Αλλά η κύρια διαφορά μπορεί να φανεί στη λειτουργία και των δύο αυτών εντολών.

Γνωρίζουμε ότι στο Hive, τα ερωτήματα που γράφουμε στο HQL μετατρέπονται σε εργασίες MapReduce έτσι ώστε να αφαιρείται η πολυπλοκότητα και να είναι άνετο για τον χρήστη.

Select customerid, unitprice from customers sort by customerid;

Έτσι, όταν εκτελούμε ένα ερώτημα όπως:

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

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

Έξοδος | ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

Select customerid, unitprice from customers order by customerid;
Ενώ κατά σειρά από πολλαπλούς χαρτογράφους χρησιμοποιούνται μαζί με μόνο 1 μειωτήρα.  Η χρήση ενός μόνο μειωτήρα έχει ως αποτέλεσμα την πλήρη ταξινόμηση των δεδομένων που μεταδίδονται από τους χαρτογράφους.

Διαφορετική έξοδος | ΕΝΤΟΛΕΣ HQL ΓΙΑ ΑΝΑΛΥΤΙΚΑ ΔΕΔΟΜΕΝΩΝ

Η διαφορά στην έξοδο του Reducer φαίνεται ξεκάθαρα στα δεδομένα. Ως εκ τούτου, μπορούμε να πούμε ότι η „παραγγελία κατά“ εγγυάται πλήρη σειρά στα δεδομένα ενώ η „ταξινόμηση κατά“ παρέχει μερικώς διατεταγμένα αποτελέσματα.

ΤΕΛΟΣ ΣΗΜΕΙΩΣΕΙΣ

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

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

Σχετίζεται με