Πίνακες στο BigQuery - Πώς να βελτιώσετε την απόδοση ερωτήματος και να βελτιστοποιήσετε την αποθήκευση

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

Επιλογές DataModel για αποθήκες

Τυπικά οι αποθήκες δεδομένων είναι μια επέκταση των βάσεων δεδομένων που έχουν κατασκευαστεί με χαρακτηριστικά OLAP για να ταιριάζουν στις αναλυτικές ανάγκες και στις αναφορές. Από μια προοπτική μοντέλου δεδομένων, εξακολουθούν να μιμούνται RDBMS στις υποκείμενες δομές πίνακα. Για την απόδοση, οι πίνακες απορυθμίζονται στην αποθήκη για να μειωθούν οι επιβαρύνσεις των συνενώσεων τραπέζης. Αυτή είναι μια αποδεδειγμένη βέλτιστη πρακτική για τη μείωση του κόστους των συνεργασιών με το κόστος αποθήκευσης περιττών δεδομένων.

Κίνητρο

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

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

  1. Ένα κανονικοποιημένο μοντέλο δεδομένων όπου οι γραμμές παραγγελίας και παραγγελίας αποθηκεύονται ως δύο διαφορετικοί πίνακες
  2. Ένα απο-κανονικοποιημένο μοντέλο δεδομένων όπου οι γραμμές παραγγελίας και παραγγελίας αποθηκεύονται στον ίδιο πίνακα, όπου επαναλαμβάνονται τα δεδομένα παραγγελίας στο επίπεδο γραμμής
  3. Ένα De-Normalized Data Model αξιοποιώντας συστοιχίες BigQuery όπου οι γραμμές παραγγελίας και παραγγελίας αποθηκεύονται στον ίδιο πίνακα, αλλά τα δεδομένα παραγγελίας δεν επαναλαμβάνονται

Πίνακες στο BigQuery

Οι πίνακες είναι στήλες πολλαπλών τιμών στο BigQuery, όπου ένα πεδίο συστοιχίας περιέχει περισσότερες από μία τιμές (του ίδιου τύπου δεδομένων)

Κάθε σειρά μπορεί να έχει διαφορετικές στήλες πολλών τιμών

Αυτή η δομή πίνακα με συστοιχίες διατηρεί μια σχέση μεταξύ της τιμής στην ομάδα_ομάδων (θηλαστικά) με κάθε τιμή στον κατάλογο species_list χωρίς να επαναλαμβάνεται η τιμή της. Έτσι, η αποθήκευση δεδομένων με συστοιχίες ελαχιστοποιεί την αποθήκευση (τα θηλαστικά αποθηκεύονται μόνο μία φορά αντί για 3 φορές). Αλλά αυτή η δομή δεν υποστηρίζει τυπική ανάλυση στυλ SQL. Για να μετατρέψετε αυτή τη δομή σε σύνολο αποτελεσμάτων στυλ SQL, χρησιμοποιήστε το UNNEST, το οποίο αποσυσκευάζει τη συστοιχία και επαναλαμβάνει την τιμή του species_group για κάθε species_list, ακριβώς όπως ένα cross join.

Σετ αποτελεσμάτων στυλ SQL, κατάλληλο για την εφαρμογή οποιασδήποτε ανάλυσης τύπου SQL
Σημείωση: Αποθήκευση των θηλαστικών (16 bytes) και των ερπετών (18 bytes) μία φορά, αποθηκεύσαμε (2 * 16 + 3 * 18 = 86) bytes αποθήκευσης (72%) σε μία μόνο στήλη. Σχεδιάστε αυτό για έναν πίνακα κλίμακας terabyte με πολλαπλές στήλες, γεγονός που μεταφράζεται άμεσα στην εξοικονόμηση κόστους αποθήκευσης.

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

Δειγμα δεδομένων

Χρησιμοποιώντας τη μαγεία των συστοιχιών και των τυχαίων αριθμών, αυτό το ερώτημα προσομοιώνει ένα σύστημα παραγγελιών δημιουργώντας μια παραγγελία για κάθε 5 δευτερόλεπτα, δίνοντάς μας περίπου 518K παραγγελίες το μήνα. Με την παράταση σε ένα χρόνο, το ερώτημα δημιουργεί τυχαίες παραγγελίες 6,3M με συνολικά γραμμές παραγγελιών 22M, δημιουργώντας επαρκή όγκο παραγγελιών για να ελέγξει την απόδοση των τριών μοντέλων δεδομένων.

Ρύθμιση δεδομένων

Κάντε λήψη αυτού του github repo και εκτελέστε setup.sh Τα σενάρια συμπληρώνουν δεδομένα για 3 μοντέλα δεδομένων.

Μοντέλο δεδομένων

Κανονικοποιημένο μοντέλο δεδομένων

Τυπική σχέση βασικής λεπτομέρειας μεταξύ παραγγελίας και γραμμών

Απομορφωμένο μοντέλο δεδομένων

De-κανονικοποιημένο μοντέλο με παραγγελία, αναγνωριστικά πελάτη και ημερομηνία παραγγελίας επαναλαμβανόμενα για κάθε γραμμή

Απο-κανονικοποιημένη με συστοιχίες BigQuery

Παραγγελία και γραμμές που έχουν αποθηκευτεί στην ίδια σειρά, αλλά δεν επαναλαμβάνονται για κάθε γραμμή

Δοκιμή απόδοσης

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

  1. Συνολική παραγγελία ανά πελάτη
  2. Συνολική παραγγελία ανά μήνα
  3. Σύνολο παραγγελιών ανά στοιχείο

Ακολουθεί μια σύγκριση των βασικών μετρήσεων ερωτήματος που έχουν ληφθεί για κάθε ερώτημα

Τα απο-κανονικοποιημένα μοντέλα έχουν βελτιωθεί καλύτερα σε όλες τις μετρήσεις με το μοντέλο BQ Arrays που καταναλώνει το λιγότερο επεξεργασμένο byte και το χρόνο λήψης

Συγκρίνοντας το μέγεθος των πινάκων με μοντέλο δεδομένων

Μέτρηση ερωτήματος

  • Bytes Processed - Η ποσότητα των δεδομένων που διαβάζονται από τους πίνακες, συμβάλλει άμεσα στο κόστος της επεξεργασίας ερωτήματος
  • Bytes Shuffled - Ποσότητα δεδομένων ή ενδιάμεσων αποτελεσμάτων που μετακινούνται μεταξύ κόμβων παράλληλης επεξεργασίας. Αυτό είναι ένα μέτρο της ποσότητας της επεξεργαστικής ισχύος που αφιερώνεται στη μετακίνηση δεδομένων που αυξάνει τον χρόνο αναμονής και περιορίζει τον παραλληλισμό
  • Χρόνος που λήφθηκε - Έλλειψε ο χρόνος εκτέλεσης του ερωτήματος
  • Χρόνος χρήσης (δευτερόλεπτα) - Αθροιστική ποσότητα χρόνου που δαπανάται από παράλληλους κόμβους για την επεξεργασία του ερωτήματος
Ο σχεδιασμός του μοντέλου δεδομένων και των ερωτημάτων θα πρέπει να στοχεύει στην ελαχιστοποίηση αυτών των μετρήσεων

Παρατηρήσεις

  • Το κανονικοποιημένο μοντέλο κατανάλωσε το υψηλότερο ποσό των bytes και χρειάστηκε περισσότερο χρόνο για να δώσει ένα αποτέλεσμα. Τα ερωτήματα είχαν το υψηλότερο ποσό bytes ανακατευθυνόμενο (τάξεις μεγέθους υψηλότερο από το κανονικοποιημένο μοντέλο), αποτέλεσμα της σύνδεσης μεταξύ των πινάκων. Αποδεικνύει απαγορευτικά ακριβό.
  • Και τα δύο De-Normalized μοντέλα κατανάλωσαν ελάχιστα bytes ανακατωμένα, δεδομένου ότι τα δεδομένα βρίσκονται μέσα στην ίδια σειρά, δεν απαιτούνται συμμετοχές
  • Μοντέλο με συστοιχίες BigQuery είχε τις καλύτερες μετρήσεις σε byte επεξεργασμένο και χρόνο που λαμβάνονται

Συστάσεις

Κατά τον καθορισμό μοντέλων δεδομένων λάβετε υπόψη τα παρακάτω για βέλτιστη απόδοση

  1. Χρησιμοποιήστε τους πίνακες για να αποθηκεύσετε πίνακες με σχέσεις γονέα / παιδιού. Μεγαλύτερη η καρδιανότητα, υψηλότερα τα οφέλη
  2. Χρησιμοποιήστε τη μορφή De-Normalized εάν χρησιμοποιείτε Arrays περιπλέκει ερωτήματα είτε για προγραμματιστές είτε περιλαμβάνει μια απότομη καμπύλη εκμάθησης για τελικούς χρήστες / αναλυτές που χρησιμοποιούν το BigQuery
  3. Βελτιστοποίηση τύπων δεδομένων: Προσδιορίστε τους κατάλληλους τύπους δεδομένων κατά τον ορισμό των πινάκων. Εξετάστε εναλλακτικές λύσεις, όπου χρειάζεται.

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

Περαιτέρω ανάγνωση / πρακτική

  • Οπτικοποίηση των σχεδίων ερωτημάτων BigQuery - Αναπαράσταση οπτικής απόδοσης αυτών των αναζητήσεων, σχεδιάζω να επεξηγήσω τα σχέδια εκτέλεσης ερωτημάτων χρησιμοποιώντας αυτό το εξαιρετικό εργαλείο από τον Stephan Meyn
  • Πώς να χρησιμοποιήσετε τη λειτουργία UNNEST στο BigQuery για να αναλύσετε τις παραμέτρους συμβάντων στο Analytics - Εδώ είναι ένα άρθρο με εξαιρετική χρήση του UNNEST, συμπεριλαμβανομένης μιας όμορφης απεικόνισης από τον Todd Kerpelman