Le livre "Google BigQuery. Tout sur l'entreposage de données, l'analyse et l'apprentissage automatique "

Bonjour les Habitants! Êtes-vous intimidé par la nécessité de traiter des ensembles de données de pétaoctets? Découvrez Google BigQuery, un système de stockage d'informations qui peut consolider les données dans toute l'entreprise, faciliter l'analyse interactive et activer l'apprentissage automatique. Vous pouvez désormais stocker, interroger, recevoir et explorer efficacement des données dans un environnement pratique. Walyappa Lakshmanan et Jordan Taijani vous apprendront à travailler dans un entrepôt de données moderne en utilisant toute la puissance d'un cloud public évolutif et sans serveur. Avec ce livre, vous allez: - Plonger dans les rouages ​​de BigQuery - Explorer les types de données, les fonctions et les opérateurs pris en charge par Big Query - Optimiser les requêtes et mettre en œuvre des schémas pour améliorer les performances ou réduire les coûts - En savoir plus sur les SIG, les voyages dans le temps, DDL / DML.fonctions personnalisées et scripts SQL - Résolvez de nombreux défis d'apprentissage automatique - Découvrez comment protéger les données, suivre les performances et authentifier les utilisateurs.

Minimisation des coûts de réseau BigQuery est un service régional disponible dans le monde entier. Par exemple, si vous demandez un ensemble de données stocké dans la région UE, la demande s'exécutera sur des serveurs situés dans un centre de données de l'Union européenne. Pour que vous puissiez stocker les résultats de la requête dans une table, celle-ci doit se trouver dans un ensemble de données qui se trouve également dans la région UE. Cependant, l'API REST de BigQuery peut être appelée (c'est-à-dire exécuter une requête) de n'importe où dans le monde, même à partir d'ordinateurs extérieurs à GCP. Lorsque vous travaillez avec d'autres ressources GCP, telles que Google Cloud Storage ou Cloud Pub / Sub, les meilleures performances sont obtenues si elles se trouvent dans la même région que l'ensemble de données. Par conséquent, si la requête est exécutée à partir d'une instance Compute Engine ou d'un cluster Cloud Dataproc, la surcharge du réseau sera minimale,si l'instance ou le cluster se trouve également dans la même région que l'ensemble de données demandé. Lorsque vous accédez à BigQuery depuis l'extérieur de GCP, tenez compte de la topologie de votre réseau et essayez de minimiser le nombre de sauts entre l'ordinateur client et le centre GCP où réside l'ensemble de données.



Réponses



concises et incomplètes En accédant directement à l'API REST, la surcharge du réseau peut être réduite en acceptant des réponses concises et incomplètes. Pour accepter les réponses compressées, vous pouvez spécifier dans l'en-tête HTTP que vous êtes prêt à accepter une archive gzip et vous assurer que la ligne "gzip" est présente dans l'en-tête User-Agent, par exemple:

Accept-Encoding: gzip
User-Agent: programName (gzip)

Dans ce cas, toutes les réponses seront compressées à l'aide de gzip. Par défaut, les réponses BigQuery contiennent tous les champs répertoriés dans la documentation. Cependant, si nous savons quelle partie de la réponse nous intéresse, nous pouvons demander à BigQuery de n'envoyer que cette partie, réduisant ainsi la surcharge du réseau. Par exemple, dans ce chapitre, nous avons vu comment obtenir des informations complètes sur un travail à l'aide de l'API Jobs. Si vous n'êtes intéressé que par un sous-ensemble de la réponse complète (par exemple, uniquement les étapes du plan de requête), vous pouvez spécifier les champs d'intérêt pour limiter la taille de la réponse:

JOBSURL="https://www.googleapis.com/bigquery/v2/projects/$PROJECT/jobs"
FIELDS="statistics(query(queryPlan(steps)))"
curl --silent \
    -H "Authorization: Bearer $access_token" \
    -H "Accept-Encoding: gzip" \
    -H "User-Agent: get_job_details (gzip)" \
    -X GET \
    "${JOBSURL}/${JOBID}?fields=${FIELDS}" \
| zcat

Remarque: il indique également que nous acceptons les données compressées gzip.



Combiner plusieurs demandes dans des packages



Lorsque vous utilisez l'API REST, il est possible de combiner plusieurs appels d'API BigQuery à l'aide du type de contenu multipart / mixte et des requêtes HTTP imbriquées dans chaque partie. Le corps de chaque partie spécifie l'opération HTTP (GET, PUT, etc.), le chemin vers l'URL, les en-têtes et le corps. En réponse, le serveur enverra une seule réponse HTTP avec le type de contenu multipart / mixed, dont chaque partie contiendra la réponse (dans l'ordre) à la requête correspondante dans la requête batch. Bien que les réponses soient renvoyées dans un ordre spécifique, le serveur peut traiter les appels dans n'importe quel ordre. Par conséquent, une demande par lots peut être considérée comme un groupe de demandes exécutées en parallèle. Voici un exemple d'envoi d'une requête par lots pour obtenir des détails à partir des plans d'exécution des cinq dernières requêtes de notre projet. Nous utilisons d'abord l'outil de ligne de commande BigQuery,pour obtenir les cinq dernières quêtes réussies:

# 5   
JOBS=$(bq ls -j -n 50 | grep SUCCESS | head -5 | awk '{print $1}')

La demande est envoyée au point de terminaison BigQuery pour un traitement par lots:

BATCHURL="https://www.googleapis.com/batch/bigquery/v2"
JOBSPATH="/projects/$PROJECT/jobs"
FIELDS="statistics(query(queryPlan(steps)))"

Vous pouvez définir des demandes individuelles dans le chemin de l'URL:

request=""
for JOBID in $JOBS; do
read -d '' part << EOF
--batch_part_starts_here
GET ${JOBSPATH}/${JOBID}?fields=${FIELDS}
EOF
request=$(echo "$request"; echo "$part")
done

Ensuite, vous pouvez envoyer la demande en tant que demande composée:

curl --silent \
   -H "Authorization: Bearer $access_token" \
   -H "Content-Type: multipart/mixed; boundary=batch_part_starts_here" \
   -X POST \
   -d "$request" \
   "${BATCHURL}"

Lecture groupée à l'aide de l'API de stockage BigQuery



Dans le chapitre 5, nous avons discuté de l'utilisation de l'API REST BigQuery et des bibliothèques clientes pour énumérer les tables et récupérer les résultats des requêtes. L'API REST renvoie les données sous forme d'enregistrements paginés qui conviennent mieux aux ensembles de résultats relativement petits. Cependant, avec l'avènement du machine learning et des outils d'extraction, de transformation et de chargement (ETL) distribués, les outils externes nécessitent désormais un accès en masse rapide et efficace au référentiel BigQuery géré. Cet accès en lecture groupée est fourni dans l'API BigQuery Storage via le protocole d'appel de procédure à distance (RPC). Avec l'API BigQuery Storage, les données structurées sont transmises sur le réseau dans un format de sérialisation binaire qui correspond plus étroitement au format de stockage de données en colonnes.Cela fournit une parallélisation supplémentaire de l'ensemble de résultats sur plusieurs consommateurs.



Les utilisateurs finaux n'utilisent pas directement l'API BigQuery Storage. Au lieu de cela, ils utilisent Cloud Dataflow, Cloud Dataproc, TensorFlow, AutoML et d'autres outils qui utilisent l'API de stockage pour lire les données directement à partir du stockage géré plutôt que via l'API BigQuery.



Étant donné que l'API Storage accède directement aux données stockées, l'autorisation d'accéder à l'API BigQuery Storage est différente de l'API BigQuery existante. Les autorisations de l'API BigQuery Storage doivent être configurées indépendamment des autorisations BigQuery.



L'API BigQuery Storage offre plusieurs avantages aux outils qui lisent les données directement à partir du stockage géré BigQuery. Par exemple, les consommateurs peuvent lire des jeux d'enregistrements disjoints à partir d'une table à l'aide de plusieurs threads (par exemple, en autorisant des lectures distribuées de données à partir de différents serveurs de production dans Cloud Dataproc), en segmentant dynamiquement ces threads (réduisant ainsi la latence de la queue, ce qui peut être un problème sérieux pour les tâches MapReduce) , sélectionnez un sous-ensemble de colonnes à lire (pour ne transmettre que les fonctionnalités utilisées par le modèle aux structures d'apprentissage automatique), filtrez les valeurs de colonne (réduisez la quantité de données transmises sur le réseau) et en même temps assurez la cohérence des instantanés (c'est-à-dire la lecture des données à partir d'un certain point dans le temps).



Dans le chapitre 5, nous avons abordé l'utilisation de l'extension %% bigquery dans Jupyter Notebook pour charger les résultats de la requête dans DataFrames. Cependant, les exemples utilisaient des ensembles de données relativement petits - d'une douzaine à plusieurs centaines d'enregistrements. Est-il possible de charger l'ensemble de données london_bicycles (24 millions d'enregistrements) dans un DataFrame? Oui, vous pouvez, mais dans ce cas, vous devez utiliser l'API de stockage, et non l'API BigQuery, pour charger des données dans le DataFrame. Tout d'abord, vous devez installer la bibliothèque cliente de l'API de stockage Python avec la prise en charge d'Avro et de pandas. Cela peut être fait avec la commande

%pip install google-cloud-bigquery-storage[fastavro,pandas]

Ensuite, il ne reste plus qu'à utiliser l'extension %% bigquery, comme auparavant, mais ajouter un paramètre qui nécessite l'utilisation de l'API de stockage:

%%bigquery df --use_bqstorage_api --project $PROJECT
SELECT 
   start_station_name 
   , end_station_name 
   , start_date 
   , duration
FROM `bigquery-public-data`.london_bicycles.cycle_hire

Notez que nous utilisons ici la capacité de l'API de stockage à fournir un accès direct aux colonnes individuelles; il n'est pas nécessaire de lire l'intégralité de la table BigQuery dans un DataFrame. Si la requête renvoie une petite quantité de données, l'extension utilisera automatiquement l'API BigQuery. Par conséquent, ce n'est pas effrayant si vous indiquez toujours cet indicateur dans les cellules du cahier. Pour activer l'indicateur --usebqstorageapi dans toutes les cellules du bloc-notes, vous pouvez définir l'indicateur de contexte:

import google.cloud.bigquery.magics
google.cloud.bigquery.magics.context.use_bqstorage_api = True

Choisir un format de stockage efficace

Les performances d'une requête dépendent de l'emplacement et du format de stockage des données qui composent la table. En général, moins la requête doit effectuer des recherches ou des conversions de type, meilleures sont les performances.



Sources de données internes et externes



BigQuery prend en charge l'interrogation de sources de données externes telles que Google Cloud Storage, Cloud Bigtable et Google Sheets, mais vous ne pouvez obtenir les meilleures performances qu'à partir de vos propres tables.



Nous vous recommandons d'utiliser BigQuery comme référentiel de données analytiques pour toutes vos données structurées et semi-structurées. Les sources de données externes sont mieux utilisées pour le stockage intermédiaire (Google Cloud Storage), les importations en direct (Cloud Pub / Sub, Cloud Bigtable) ou les mises à jour périodiques (Cloud SQL, Cloud Spanner). Ensuite, configurez votre pipeline de données pour charger les données selon un calendrier à partir de ces sources externes dans BigQuery (voir le chapitre 4).



Si vous devez demander des données à Google Cloud Storage, enregistrez-les si possible dans un format de colonne compressé (tel que Parquet). Utilisez des formats basés sur des enregistrements tels que JSON ou CSV en dernier recours.



Gestion du cycle de vie du bucket de préparation



Si vous importez des données dans BigQuery après les avoir placées dans Google Cloud Storage, assurez-vous de les supprimer du cloud après l'importation. Si vous utilisez le pipeline ETL pour charger des données dans BigQuery (pour les transformer de manière significative ou ne laisser qu'une partie des données en cours de route), vous souhaiterez peut-être enregistrer les données d'origine dans Google Cloud Storage. Dans ce cas, vous pouvez contribuer à réduire les coûts en définissant des règles de gestion du cycle de vie du bucket qui rétrogradent le stockage dans Google Cloud Storage.



Voici comment activer la gestion du cycle de vie du bucket et configurer le déplacement automatique des données des régions fédérées ou des classes standard de plus de 30 jours vers le stockage Nearline, et les données stockées dans Nearline Storage pendant plus de 90 jours vers Coldline Storage:

gsutil lifecycle set lifecycle.yaml gs://some_bucket/

Dans cet exemple, le fichier lifecycle.yaml contient le code suivant:

{
"lifecycle": {
  "rule": [
  {
   "action": {
    "type": "SetStorageClass",
    "storageClass": "NEARLINE"
   },
   "condition": {
    "age": 30,
    "matchesStorageClass": ["MULTI_REGIONAL", "STANDARD"]
   }
 },
 {
  "action": {
   "type": "SetStorageClass",
   "storageClass": "COLDLINE"
  },
  "condition": {
   "age": 90,
   "matchesStorageClass": ["NEARLINE"]
  }
 }
]}}

Vous pouvez utiliser la gestion du cycle de vie non seulement pour modifier la classe d'un objet, mais également pour supprimer des objets plus anciens qu'un certain seuil.



Stockage des données sous forme de tableaux et de structures



En plus d'autres ensembles de données accessibles au public, BigQuery dispose d'un ensemble de données contenant des informations sur les tempêtes cycloniques (ouragans, typhons, cyclones, etc.) provenant des services météorologiques du monde entier. Les tempêtes cycloniques peuvent durer jusqu'à plusieurs semaines et leurs paramètres météorologiques sont mesurés environ toutes les trois heures. Supposons que vous décidiez de trouver dans cet ensemble de données toutes les tempêtes qui se sont produites en 2018, la vitesse maximale du vent atteinte par chaque tempête, ainsi que l'heure et le lieu de la tempête lorsque cette vitesse maximale a été atteinte. La requête suivante récupère toutes ces informations de l'ensemble de données public:

SELECT
  sid, number, basin, name,
  ARRAY_AGG(STRUCT(iso_time, usa_latitude, usa_longitude, usa_wind) ORDER BY
usa_wind DESC LIMIT 1)[OFFSET(0)].*
FROM
  `bigquery-public-data`.noaa_hurricanes.hurricanes
WHERE
  season = '2018'
GROUP BY
  sid, number, basin, name
ORDER BY number ASC

La requête récupère l'identifiant de la tempête (Sid), ses saisons, son bassin et le nom de la tempête (si attribué), puis trouve un tableau d'observations effectuées pour cette tempête, en classant les observations par ordre décroissant de vitesse du vent et en choisissant la vitesse maximale pour chaque tempête. ... Les tempêtes elles-mêmes sont classées par numéro séquentiel. Le résultat comprend 88 enregistrements et ressemble à ceci:





La demande a pris 1,4 seconde et traité 41,7 Mo. La première entrée décrit la tempête Bolaven, qui a atteint une vitesse maximale de 29 m / s le 2 janvier 2018 à 18h00 UTC.



Étant donné que les observations sont effectuées par plusieurs services météorologiques, ces données peuvent être standardisées à l'aide de champs imbriqués et stockées dans BigQuery, comme indiqué ci-dessous:

CREATE OR REPLACE TABLE ch07.hurricanes_nested AS

SELECT sid, season, number, basin, name, iso_time, nature, usa_sshs,
    STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
    STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
tokyo_wind AS wind, tokyo_pressure AS pressure) AS tokyo,
    ... AS cma,
    ... AS hko,
    ... AS newdelhi,
    ... AS reunion,
    ... bom,
    ... AS wellington,
    ... nadi
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes

Les requêtes sur cette table ont le même aspect que les requêtes sur la table d'origine, mais avec un léger changement dans les noms des colonnes (usa.latitude au lieu de usa_latitude):

SELECT
  sid, number, basin, name,
  ARRAY_AGG(STRUCT(iso_time, usa.latitude, usa.longitude, usa.wind) ORDER BY
usa.wind DESC LIMIT 1)[OFFSET(0)].*
FROM
  ch07.hurricanes_nested
WHERE
  season = '2018'
GROUP BY
  sid, number, basin, name
ORDER BY number ASC

Cette demande traite la même quantité de données et s'exécute dans le même laps de temps que l'original, en utilisant l'ensemble de données public. L'utilisation de champs (structures) imbriqués ne modifie pas la vitesse ou le coût de la requête, mais peut rendre la requête plus lisible. Comme il y a de nombreuses observations de la même tempête au cours de sa durée, nous pouvons modifier le stockage pour adapter l'ensemble des observations pour chaque tempête dans un seul enregistrement:

CREATE OR REPLACE TABLE ch07.hurricanes_nested_track AS

SELECT sid, season, number, basin, name,
 ARRAY_AGG(
   STRUCT(
    iso_time,
    nature,
    usa_sshs,
    STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
    STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
      tokyo_wind AS wind, tokyo_pressure AS pressure) AS tokyo,
    ... AS cma,
    ... AS hko,
    ... AS newdelhi,
    ... AS reunion,
    ... bom,
    ... AS wellington,
    ... nadi
  ) ORDER BY iso_time ASC ) AS obs
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes
GROUP BY sid, season, number, basin, name

Notez que nous stockons désormais le sid, la saison et d'autres caractéristiques de la tempête sous forme de colonnes scalaires, car elles ne changent pas en fonction de sa durée.



Le reste des données, changeant à chaque observation, est stocké sous forme de tableau de structures. Voici à quoi ressemble la requête pour la nouvelle table:

SELECT
  number, name, basin,
  (SELECT AS STRUCT iso_time, usa.latitude, usa.longitude, usa.wind
     FROM UNNEST(obs) ORDER BY usa.wind DESC LIMIT 1).*
FROM ch07.hurricanes_nested_track
WHERE season = '2018'
ORDER BY number ASC

Cette requête renverra le même résultat, mais cette fois, elle ne traitera que 14,7 Mo (une triple réduction des coûts) et se terminera en une seconde (une augmentation de 30% de la vitesse). Qu'est-ce qui a causé cette amélioration des performances? Lorsque les données sont stockées sous forme de tableau, le nombre d'enregistrements dans la table diminue considérablement (de 682 000 à 14 000) 2, car il n'y a plus qu'un enregistrement par tempête, pas beaucoup d'enregistrements - un pour chaque observation. Ensuite, lorsque nous filtrons les lignes par saison, BigQuery peut supprimer de nombreux cas associés en même temps, comme illustré dans la figure 1. 7.13.





Un autre avantage est qu'il n'est pas nécessaire de dupliquer les enregistrements de données lorsque des observations avec différents niveaux de détail sont stockées dans la même table. Une table peut stocker des données de changement de latitude et de longitude pour les tempêtes et des données de haut niveau telles que les noms et la saison des tempêtes. Et comme BigQuery stocke les données tabulaires dans des colonnes à l'aide de la compression, vous pouvez interroger et traiter des données de haut niveau sans craindre le coût de l'utilisation de données détaillées - elles sont désormais stockées sous forme de tableau distinct de valeurs pour chaque tempête.



Par exemple, pour connaître le nombre de tempêtes par année, vous ne pouvez interroger que les colonnes requises:

WITH hurricane_detail AS (
SELECT sid, season, number, basin, name,
 ARRAY_AGG(
  STRUCT(
    iso_time,
    nature,
    usa_sshs,
    STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
    STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
        tokyo_wind
AS wind, tokyo_pressure AS pressure) AS tokyo
  ) ORDER BY iso_time ASC ) AS obs
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes
GROUP BY sid, season, number, basin, name
)
SELECT
  COUNT(sid) AS count_of_storms,
  season
FROM hurricane_detail
GROUP BY season
ORDER BY season DESC

La demande précédente traitait 27 Mo, soit la moitié des 56 Mo qui devraient être traités si les champs répétitifs imbriqués n'étaient pas utilisés.



Les champs imbriqués n'améliorent pas les performances par eux-mêmes, bien qu'ils puissent améliorer la lisibilité en effectuant une jointure à d'autres tables associées. De plus, les champs répétitifs imbriqués sont extrêmement utiles du point de vue des performances. Pensez à utiliser des champs répétitifs imbriqués dans votre schéma, car ils peuvent considérablement augmenter la vitesse et réduire le coût du filtrage des requêtes sur une colonne non imbriquée ou répétitive (dans notre cas, saison).



Le principal inconvénient des champs répétitifs imbriqués est la difficulté d'implémenter la diffusion en continu dans une telle table si les mises à jour en continu impliquent l'ajout d'éléments à des tableaux existants. C'est beaucoup plus difficile à mettre en œuvre que l'ajout de nouveaux enregistrements: vous devrez modifier un enregistrement existant - pour la table d'informations sur la tempête, c'est un inconvénient majeur, car de nouvelles observations y sont constamment ajoutées, et cela explique pourquoi cet ensemble de données public n'utilise pas de doublons imbriqués des champs.



Pratique de l'utilisation de tableaux



L'expérience a montré qu'il faut un peu de pratique pour utiliser avec succès des champs répétitifs imbriqués. L'exemple d'ensemble de données Google Analytics dans BigQuery est idéal à cette fin. Le moyen le plus simple d'identifier les données imbriquées dans un schéma consiste à rechercher le mot RECORD dans la colonne Type, qui correspond au type de données STRUCT, et le mot REPEATED dans la colonne Mode, comme indiqué ci-dessous:





Dans cet exemple, le champ TOTALS est STRUCT (mais pas répété) et le champ HITS est STRUCT et se répète. Cela a du sens, car Google Analytics suit les données de session des visiteurs au niveau d'agrégation (une valeur de session pour totals.hits) et au niveau de granularité (valeurs hit.time distinctes pour chaque page et images extraites de votre site) ... Le stockage des données à ces différents niveaux de détail sans duplication de visiteurId dans les enregistrements n'est possible qu'avec des tableaux. Après avoir enregistré les données dans un format répétitif avec des tableaux, vous devez envisager de déployer ces données dans vos demandes à l'aide de UNNEST, par exemple:

SELECT DISTINCT
  visitId
  , totals.pageviews
  , totals.timeOnsite
  , trafficSource.source
  , device.browser
  , device.isMobile
  , h.page.pageTitle
FROM
  `bigquery-public-data`.google_analytics_sample.ga_sessions_20170801,
  UNNEST(hits) AS h
WHERE
  totals.timeOnSite IS NOT NULL AND h.page.pageTitle =
'Shopping Cart'
ORDER BY pageviews DESC
LIMIT 10
     ,   [1,2,3,4,5]   :
[1,
2
3
4
5]

Vous pouvez ensuite effectuer des opérations SQL normales telles que WHERE pour filtrer les appels sur les pages avec des titres tels que Panier. Essayez-le!



D'autre part, l'ensemble de données d'informations de commit public GitHub (bigquery-publicdata.githubrepos.commits) utilise un champ répétitif imbriqué (reponame) pour stocker la liste des référentiels affectés par le commit. Il ne change pas au fil du temps et fournit des requêtes plus rapides qui filtrent sur n'importe quel autre champ.



Stockage des données sous forme de types géographiques



L'ensemble de données public BigQuery contient un tableau des limites des zones de code postal américain (bigquery-public-data.utilityus.zipcodearea) et un autre tableau avec des polygones décrivant les limites des villes américaines (bigquery-publicdata.utilityus.uscitiesarea). Une colonne zipcodegeom est une chaîne, tandis qu'une colonne city_geom est un type géographique.



À partir de ces deux tableaux, vous pouvez obtenir une liste de tous les codes postaux pour Santa Fe au Nouveau-Mexique, comme indiqué ci-dessous:

SELECT name, zipcode
FROM `bigquery-public-data`.utility_us.zipcode_area
JOIN `bigquery-public-data`.utility_us.us_cities_area
ON ST_INTERSECTS(ST_GeogFromText(zipcode_geom), city_geom)
WHERE name LIKE '%Santa Fe%'

Cette requête prend 51,9 secondes, traite 305,5 Mo de données et renvoie les résultats suivants:





Pourquoi cette demande prend-elle autant de temps? Ce n'est pas à cause de l'opération STINTERSECTS, mais principalement parce que la fonction STGeogFromText doit évaluer les cellules S2 et construire le type GEOGRAPHY correspondant à chaque code postal.



Nous pouvons essayer de modifier la table des codes postaux en faisant cela au préalable et stocker la géométrie en tant que valeur GEOGRAPHY:

CREATE OR REPLACE TABLE ch07.zipcode_area AS
SELECT 
  * REPLACE(ST_GeogFromText(zipcode_geom) AS zipcode_geom)
FROM 
  `bigquery-public-data`.utility_us.zipcode_area

REPLACE (voir la requête précédente) est un moyen pratique de remplacer une colonne d'une expression SELECT *.

Le nouvel ensemble de données a une taille de 131,8 Mo, ce qui est nettement plus grand que les 116,5 Mo du tableau d'origine. Cependant, les requêtes sur cette table peuvent utiliser la couverture S2 et sont beaucoup plus rapides. Par exemple, la requête suivante prend 5,3 secondes (une augmentation de 10 fois la vitesse) et traite 320,8 Mo (une légère augmentation du coût lors de l'utilisation d'un plan tarifaire «à la demande»):

SELECT name, zipcode
FROM ch07.zipcode_area
JOIN `bigquery-public-data`.utility_us.us_cities_area
ON ST_INTERSECTS(zipcode_geom, city_geom)
WHERE name LIKE '%Santa Fe%'

Les avantages en termes de performances du stockage des données géographiques dans une colonne GEOGRAPHY sont plus que convaincants. C'est pourquoi l'ensemble de données Utilityus est obsolète (il est toujours disponible pour conserver les requêtes déjà écrites) en vie. Nous vous recommandons d'utiliser la table bigquery-public-data.geousboundaries.uszip_codes, qui stocke les informations géographiques dans une colonne GEOGRAPHY et est constamment mise à jour.



»Plus de détails sur le livre peuvent être trouvés sur le site de la maison d'édition

» Table des matières

» Extrait



Pour Habitants une réduction de 25% sur le coupon - Google



Lors du paiement de la version papier du livre, un e-book est envoyé à l'e-mail.