Par Ajay Lakshminarayanarao et Allen Fung

Google Bigquery est un outil puissant. Un ingénieur logiciel ayant une idée minimaliste de SQL peut l'utiliser facilement. Vous pouvez pirater de grandes quantités de données pour obtenir des informations en quelques secondes. Chez ShareThisnous, nous examinons chaque jour des téraoctets de données sociales exploitables et elles sont devenues une partie intégrante de notre travail d'analyse, de contrôle et de traitement.

Un des problèmes récents que nous avons rencontrés était de séparer les lignes dans une table Bigquery (BQ) basée sur le pays. BQ est basé sur le stockage de colonnes. Au lieu de stocker l'intégralité de l'enregistrement sur un seul volume, BQ divise la ligne en fonction des valeurs des colonnes et stocke chaque valeur dans son propre volume de stockage. Pour tirer parti de ce schéma, nous avons conçu une solution efficace de recherche à faible coût pour récupérer des valeurs pour chaque pays, comme décrit ci-dessous.

Problème: pour isoler un tableau BQ par pays
La manière simple

Pour isoler une table BQ par pays, nous devons interroger la table, autant de fois que le nombre de pays.

Supposons que nous devons interroger les colonnes A, B, C, D et E. La taille de chaque colonne est de 20 Go. Le nombre d'octets facturable sera de 100 Go pour notre table.

Si nous avons besoin d'exécuter notre requête autant de fois que le nombre de pays à savoir 240, notre nombre facturable d'octets sera 24 000 GB ou presque 24 to. Disons que les frais pour 1 TB est $5, notre coût total sera $120 pour une table BQ.

La manière optimisée

L'astuce ici est de regrouper le nombre requis de champs comme une seule colonne. Ce seul n'enregistrera pas le nombre d'octets facturés. La deuxième astuce consiste à pivoter par la colonne de ségrégation; dans ce cas, il sera par pays.

Après les étapes ci-dessus, notre tableau des résultats contiendra autant de colonnes que de pays. Chaque colonne représente des données pour un pays et n'aura les données précédemment groupées que pour ce pays.

Dans notre tableau BQ de 100 Go, nous pouvons supposer un résultat probable, comme la colonne qui nous représente est de 20 Go, les 10 principaux pays en moyenne autour de 3GB et le reste des 230 pays en moyenne vers 0,5 GB.

Maintenant, nos octets facturables sont basés sur la taille des données pour chaque pays. Le coût facturable pour nous interroger sera seulement $0,1, le coût pour les grands pays sera à $0,015 chacun et le reste des pays beaucoup plus petit que cela.

Le total des octets facturables sera de 100 Go pour la table groupée et 165GB pour les requêtes de tous les pays combine. Le coût total est d'environ $1,5!

Génération de la table groupée

Nous pouvons regrouper les colonnes requises en une seule colonne à l'aide de CONCAt. Nous pouvons créer une ligne dans un format qui représente une ligne dans un fichier CSV ou un objet JSON valide.

Pour la deuxième étape, la requête se composera de 240 relevés de cas, avec une instruction de cas pour chaque pays à pivoter vers une colonne particulière. La requête sera grande, mais BQ prend en charge la taille de la requête jusqu'à 256KB. Nous pouvons combiner les deux étapes en une seule requête où nous concat dans chaque instruction case.

Exemple de requête:

SÉLECTIONNEZ
(Cas où Geo. ISO = 'US'PUIS CONCAT (A, B, C, D, E)) EN TANT QUE PAYSNOUS
(Cas où Geo. ISO = 'CA'PUIS CONCAT (A, B, C, D, E)) EN TANT QUE PAYSCA...
DE
mergelog_20160101;