Das Buch „Google BigQuery. Alles über Data Warehousing, Analytics und maschinelles Lernen "

Hallo Bewohner! Sind Sie eingeschüchtert von der Notwendigkeit, Petabyte-Datensätze zu verarbeiten? Lernen Sie Google BigQuery kennen, eine Speicher-Engine, die Daten im gesamten Unternehmen konsolidieren, interaktive Analysen ermöglichen und maschinelles Lernen ermöglichen kann. Jetzt können Sie Daten in einer praktischen Umgebung effizient speichern, abfragen, abrufen und untersuchen. Walyappa Lakshmanan und Jordan Taijani zeigen Ihnen, wie Sie in einem modernen Data Warehouse mit der vollen Leistung einer skalierbaren, serverlosen öffentlichen Cloud arbeiten. Mit diesem Buch werden Sie: - in die Interna von BigQuery eintauchen - die von Big Query unterstützten Datentypen, Funktionen und Operatoren kennenlernen - Abfragen optimieren und Schemata implementieren, um die Leistung zu verbessern oder Kosten zu senken - mehr über GIS, Zeitreisen, DDL / DML erfahren.Benutzerdefinierte Funktionen und SQL-Skripte - Lösen Sie viele Probleme beim maschinellen Lernen. - Erfahren Sie, wie Sie Daten schützen, die Leistung verfolgen und Benutzer authentifizieren.

Minimierung der Netzwerkkosten BigQuery ist ein regionaler Service, der weltweit verfügbar ist. Wenn Sie beispielsweise einen in der EU-Region gespeicherten Datensatz anfordern, wird die Anforderung auf Servern ausgeführt, die sich in einem Rechenzentrum in der Europäischen Union befinden. Damit Sie die Abfrageergebnisse in einer Tabelle speichern können, muss sie sich in einem Datensatz befinden, der sich ebenfalls in der EU-Region befindet. Die BigQuery-REST-API kann jedoch von überall auf der Welt aufgerufen werden (d. H. Eine Abfrage ausführen), selbst von Computern außerhalb von GCP. Bei der Arbeit mit anderen GCP-Ressourcen wie Google Cloud Storage oder Cloud Pub / Sub wird die beste Leistung erzielt, wenn sie sich in derselben Region wie das Dataset befinden. Wenn die Anforderung von einer Compute Engine-Instanz oder einem Cloud Dataproc-Cluster ausgeführt wird, ist der Netzwerk-Overhead daher minimal.Wenn sich die Instanz oder der Cluster ebenfalls in derselben Region wie das angeforderte Dataset befindet. Berücksichtigen Sie beim Zugriff auf BigQuery von außerhalb von GCP Ihre Netzwerktopologie und versuchen Sie, die Anzahl der Sprünge zwischen dem Clientcomputer und dem GCP-Center, in dem sich das Dataset befindet, zu minimieren.



Prägnante, unvollständige Antworten



Durch direkten Zugriff auf die REST-API kann der Netzwerk-Overhead reduziert werden, indem präzise, ​​unvollständige Antworten akzeptiert werden. Um komprimierte Antworten zu akzeptieren, können Sie im HTTP-Header angeben, dass Sie bereit sind, ein gzip-Archiv zu akzeptieren, und sicherstellen, dass die Zeile "gzip" im User-Agent-Header vorhanden ist. Beispiel:

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

In diesem Fall werden alle Antworten mit gzip komprimiert. Standardmäßig enthalten BigQuery-Antworten alle in der Dokumentation aufgeführten Felder. Wenn wir jedoch wissen, an welchem ​​Teil der Antwort wir interessiert sind, können wir BigQuery bitten, nur diesen Teil zu senden, wodurch der Netzwerk-Overhead reduziert wird. In diesem Kapitel haben wir beispielsweise gesehen, wie Sie mithilfe der Jobs-API vollständige Informationen zu einem Job erhalten. Wenn Sie nur an einer Teilmenge der vollständigen Antwort interessiert sind (z. B. nur Schritte im Abfrageplan), können Sie die gewünschten Felder angeben, um die Größe der Antwort zu begrenzen:

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

Bitte beachten Sie, dass wir auch gzip-komprimierte Daten akzeptieren.



Kombinieren mehrerer Anforderungen zu Paketen



Bei Verwendung der REST-API können mehrere BigQuery-API-Aufrufe mithilfe des mehrteiligen / gemischten Inhaltstyps und verschachtelter HTTP-Anforderungen in jedem Teil kombiniert werden. Der Hauptteil jedes Teils gibt die HTTP-Operation (GET, PUT usw.), den Pfad zur URL, die Header und den Hauptteil an. Als Antwort sendet der Server eine einzelne HTTP-Antwort mit dem mehrteiligen / gemischten Inhaltstyp, von der jeder Teil die Antwort (in der Reihenfolge) auf die entsprechende Anforderung in der Stapelanforderung enthält. Obwohl die Antworten in einer bestimmten Reihenfolge zurückgegeben werden, kann der Server Anrufe in beliebiger Reihenfolge verarbeiten. Daher kann eine Stapelanforderung als eine Gruppe von Anforderungen betrachtet werden, die parallel ausgeführt werden. Hier ist ein Beispiel für das Senden einer Stapelanforderung, um einige Details aus den Ausführungsplänen der letzten fünf Anforderungen in unserem Projekt abzurufen. Wir verwenden zuerst das BigQuery-Befehlszeilentool.um die letzten fünf erfolgreichen Quests zu erhalten:

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

Die Anforderung wird zur Stapelverarbeitung an den BigQuery-Endpunkt gesendet:

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

Einzelne Anfragen können im URL-Pfad definiert werden:

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

Dann können Sie die Anfrage als zusammengesetzte Anfrage senden:

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

Massenlesen mit der BigQuery-Speicher-API



In Kapitel 5 haben wir die Verwendung der BigQuery-REST-API und der Clientbibliotheken zum Auflisten von Tabellen und Abrufen von Abfrageergebnissen erläutert. Die REST-API gibt Daten als paginierte Datensätze zurück, die für relativ kleine Ergebnismengen besser geeignet sind. Mit dem Aufkommen von maschinellem Lernen und verteilten ETL-Tools (Extrahieren, Transformieren und Laden) erfordern externe Tools jetzt einen schnellen und effizienten Massenzugriff auf das verwaltete BigQuery-Repository. Dieser Massenlesezugriff wird in der BigQuery-Speicher-API über das RPC-Protokoll (Remote Procedure Call) bereitgestellt. Mit der BigQuery-Speicher-API werden strukturierte Daten in einem binären Serialisierungsformat über das Netzwerk übertragen, das dem säulenförmigen Datenspeicherformat besser entspricht.Dies bietet eine zusätzliche Parallelisierung der Ergebnismenge über mehrere Verbraucher hinweg.



Endbenutzer verwenden die BigQuery-Speicher-API nicht direkt. Stattdessen verwenden sie Cloud Dataflow, Cloud Dataproc, TensorFlow, AutoML und andere Tools, die die Speicher-API verwenden, um Daten direkt aus dem verwalteten Speicher und nicht über die BigQuery-API zu lesen.



Da die Speicher-API direkt auf die gespeicherten Daten zugreift, unterscheidet sich die Berechtigung zum Zugriff auf die BigQuery-Speicher-API von der vorhandenen BigQuery-API. BigQuery Storage API-Berechtigungen müssen unabhängig von BigQuery-Berechtigungen konfiguriert werden.



Die BigQuery-Speicher-API bietet Tools, die Daten direkt aus dem von BigQuery verwalteten Speicher lesen, mehrere Vorteile. Beispielsweise können Verbraucher nicht überlappende Datensatzgruppen mithilfe mehrerer Threads aus einer Tabelle lesen (z. B. indem sie verteilte Lesevorgänge von verschiedenen Produktionsservern in Cloud Dataproc zulassen) und diese Threads dynamisch segmentieren (wodurch die Endlatenz verringert wird, was für MapReduce-Jobs ein ernstes Problem sein kann). Wählen Sie eine Teilmenge der zu lesenden Spalten aus (um nur die vom Modell verwendeten Funktionen an maschinelle Lernstrukturen zu übergeben), filtern Sie die Spaltenwerte (reduzieren Sie die über das Netzwerk übertragene Datenmenge) und stellen Sie gleichzeitig die Konsistenz der Snapshots sicher (dh das Lesen von Daten ab einem bestimmten Zeitpunkt).



In Kapitel 5 haben wir die Verwendung der Erweiterung %% bigquery in Jupyter Notebook zum Laden von Abfrageergebnissen in DataFrames behandelt. In den Beispielen wurden jedoch relativ kleine Datensätze verwendet - von einem Dutzend bis zu mehreren hundert Datensätzen. Ist es möglich, den gesamten Datensatz von london_bicycles (24 Millionen Datensätze) in einen DataFrame zu laden? Ja, das können Sie, aber in diesem Fall sollten Sie die Speicher-API und nicht die BigQuery-API verwenden, um Daten in den DataFrame zu laden. Zunächst müssen Sie die Python Storage API-Clientbibliothek mit Avro- und Pandas-Unterstützung installieren. Dies kann mit dem Befehl erfolgen

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

Dann müssen Sie nur noch die Erweiterung %% bigquery wie zuvor verwenden, aber einen Parameter hinzufügen, für den die Speicher-API verwendet werden muss:

%%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

Beachten Sie, dass wir hier die Fähigkeit der Speicher-API verwenden, direkten Zugriff auf einzelne Spalten zu gewähren. Es ist nicht erforderlich, die gesamte BigQuery-Tabelle in einen DataFrame einzulesen. Wenn die Anforderung eine kleine Datenmenge zurückgibt, verwendet die Erweiterung automatisch die BigQuery-API. Daher ist es nicht beängstigend, wenn Sie dieses Flag immer in den Zellen des Notizbuchs angeben. Um das Flag --usebqstorageapi in allen Zellen des Notizbuchs zu aktivieren, können Sie das Kontextflag setzen:

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

Auswahl eines effizienten Speicherformats

Die Leistung einer Abfrage hängt davon ab, wo und in welchem ​​Format die Daten gespeichert sind, aus denen die Tabelle besteht. Im Allgemeinen ist die Leistung umso besser, je weniger die Abfrage zum Durchführen von Suchvorgängen oder Typkonvertierungen benötigt wird.



Interne und externe Datenquellen



BigQuery unterstützt das Abfragen externer Datenquellen wie Google Cloud Storage, Cloud Bigtable und Google Sheets. Sie können jedoch nur die beste Leistung aus Ihren eigenen Tabellen erzielen.



Wir empfehlen, BigQuery als Repository für Analysedaten für alle Ihre strukturierten und halbstrukturierten Daten zu verwenden. Externe Datenquellen werden am besten für Staging-Speicher (Google Cloud Storage), Live-Uploads (Cloud Pub / Sub, Cloud Bigtable) oder regelmäßige Updates (Cloud SQL, Cloud Spanner) verwendet. Richten Sie als Nächstes Ihre Datenpipeline so ein, dass Daten aus diesen externen Quellen nach einem Zeitplan in BigQuery geladen werden (siehe Kapitel 4).



Wenn Sie Daten von Google Cloud Storage anfordern müssen, speichern Sie diese nach Möglichkeit in einem komprimierten Spaltenformat (z. B. Parkett). Verwenden Sie als letzten Ausweg datensatzbasierte Formate wie JSON oder CSV.



Staging Bucket Lifecycle Management



Wenn Sie Daten nach dem Einfügen in Google Cloud Storage in BigQuery hochladen, müssen Sie sie nach dem Hochladen aus der Cloud löschen. Wenn Sie die ETL-Pipeline verwenden, um Daten in BigQuery zu laden (um sie erheblich zu transformieren oder nur einen Teil der Daten auf dem Weg zu belassen), möchten Sie möglicherweise die Originaldaten in Google Cloud Storage speichern. In solchen Fällen können Sie zur Kostensenkung beitragen, indem Sie Regeln für die Verwaltung des Bucket-Lebenszyklus definieren, mit denen der Speicher in Google Cloud Storage heruntergestuft wird.



So können Sie die Verwaltung des Bucket-Lebenszyklus aktivieren und die automatische Übertragung von Daten aus Verbundregionen oder Standardklassen, die älter als 30 Tage sind, in den Nearline-Speicher und Daten, die länger als 90 Tage im Nearline-Speicher gespeichert sind, in den Coldline-Speicher einrichten:

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

In diesem Beispiel enthält die Datei lifecycle.yaml den folgenden Code:

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

Mit der Lebenszyklusverwaltung können Sie nicht nur die Klasse eines Objekts ändern, sondern auch Objekte entfernen, die älter als ein bestimmter Schwellenwert sind.



Speichern von Daten als Arrays und Strukturen



Neben anderen öffentlich verfügbaren Datensätzen verfügt BigQuery über einen Datensatz mit Informationen zu Zyklonstürmen (Hurrikane, Taifune, Zyklone usw.) von Wetterdiensten auf der ganzen Welt. Zyklonstürme können bis zu mehreren Wochen andauern und ihre meteorologischen Parameter werden ungefähr alle drei Stunden gemessen. Angenommen, Sie möchten in diesem Datensatz alle Stürme aus dem Jahr 2018, die von jedem Sturm erreichte maximale Windgeschwindigkeit sowie die Zeit und den Ort des Sturms finden, an dem diese maximale Geschwindigkeit erreicht wurde. Die folgende Abfrage ruft alle diese Informationen aus dem öffentlichen Datensatz ab:

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

Die Abfrage ruft die Sturmkennung (sid), ihre Jahreszeiten, den Pool und den Sturmnamen (falls zugewiesen) ab und findet dann eine Reihe von Beobachtungen für diesen Sturm, wobei die Beobachtungen in absteigender Reihenfolge der Windgeschwindigkeit eingestuft und die maximale Geschwindigkeit für jeden Sturm ausgewählt werden ... Die Stürme selbst sind nach fortlaufender Nummer geordnet. Das Ergebnis enthält 88 Datensätze und sieht ungefähr so ​​aus:





Die Anfrage dauerte 1,4 Sekunden und verarbeitete 41,7 MB. Der erste Eintrag beschreibt den Sturm Bolaven, der am 2. Januar 2018 um 18:00 UTC eine Höchstgeschwindigkeit von 29 m / s erreichte.



Da Beobachtungen von mehreren Wetterdiensten gemacht werden, können diese Daten mithilfe verschachtelter Felder standardisiert und in BigQuery gespeichert werden, wie unten gezeigt:

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

Abfragen in dieser Tabelle sehen genauso aus wie Abfragen in der Originaltabelle, jedoch mit einer geringfügigen Änderung der Spaltennamen (usa.latitude anstelle von 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

Diese Anforderung verarbeitet dieselbe Datenmenge und wird unter Verwendung des öffentlichen Datasets in derselben Zeit wie das Original ausgeführt. Die Verwendung verschachtelter Felder (Strukturen) ändert weder die Geschwindigkeit noch die Kosten der Abfrage, kann jedoch die Lesbarkeit der Abfrage verbessern. Da es während seiner Dauer viele Beobachtungen desselben Sturms gibt, können wir den Speicher so ändern, dass in einen Datensatz die gesamte Reihe von Beobachtungen für jeden Sturm passt:

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

Beachten Sie, dass wir jetzt die Seiten-, Jahreszeit- und andere Merkmale des Sturms als Skalarsäulen speichern, da sie sich je nach Dauer nicht ändern.



Der Rest der Daten, die sich mit jeder Beobachtung ändern, wird als Array von Strukturen gespeichert. So sieht die Abfrage für die neue Tabelle aus:

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

Diese Anforderung gibt das gleiche Ergebnis zurück, verarbeitet jedoch diesmal nur 14,7 MB (eine dreifache Kostenreduzierung) und ist in einer Sekunde abgeschlossen (eine 30% ige Geschwindigkeitssteigerung). Was hat diese Leistungsverbesserung verursacht? Wenn Daten als Array gespeichert werden, sinkt die Anzahl der Datensätze in der Tabelle dramatisch (von 682.000 auf 14.000) 2, da es jetzt nur einen Datensatz pro Sturm gibt, nicht viele Datensätze - einen für jede Beobachtung. Wenn wir dann Zeilen nach Saison filtern, kann BigQuery viele verwandte Fälle gleichzeitig löschen, wie in Abbildung 1 dargestellt. 7.13.





Ein weiterer Vorteil besteht darin, dass keine Datensätze dupliziert werden müssen, wenn Fälle mit unterschiedlichen Detaillierungsgraden in derselben Tabelle gespeichert werden. In einer einzigen Tabelle können sowohl Breiten- als auch Längengradänderungsdaten für Stürme und übergeordnete Daten wie Sturmname und Jahreszeit gespeichert werden. Und da BigQuery Tabellendaten mithilfe der Komprimierung in Spalten speichert, können Sie Daten auf hoher Ebene abfragen und verarbeiten, ohne die Kosten für die Arbeit mit detaillierten Daten zu befürchten. Jetzt werden sie als separates Array von Werten für jeden Sturm gespeichert.



Um beispielsweise die Anzahl der Stürme pro Jahr zu ermitteln, können Sie nur die erforderlichen Spalten abfragen:

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

Die vorherige Anforderung verarbeitete 27 MB, was der Hälfte der 56 MB entspricht, die verarbeitet werden müssten, wenn verschachtelte Wiederholungsfelder nicht verwendet würden.



Verschachtelte Felder verbessern die Leistung allein nicht, obwohl sie die Lesbarkeit verbessern können, indem sie tatsächlich einen Join mit anderen verwandten Tabellen durchführen. Darüber hinaus sind verschachtelte sich wiederholende Felder vom Standpunkt der Leistung aus äußerst nützlich. Erwägen Sie die Verwendung verschachtelter sich wiederholender Felder in Ihrem Schema, da diese die Geschwindigkeit erheblich erhöhen und die Kosten für das Filtern von Abfragen in einer nicht verschachtelten oder sich wiederholenden Spalte (in unserem Fall Saison) senken können.



Der Hauptnachteil verschachtelter sich wiederholender Felder ist die Schwierigkeit, das Streaming in eine solche Tabelle zu implementieren, wenn bei den Streaming-Aktualisierungen Elemente zu vorhandenen Arrays hinzugefügt werden. Dies ist viel schwieriger zu implementieren als das Hinzufügen neuer Datensätze: Sie müssen einen vorhandenen Datensatz ändern. Für die Sturminformationstabelle ist dies ein erheblicher Nachteil, da ständig neue Beobachtungen hinzugefügt werden. Dies erklärt, warum in diesem öffentlichen Datensatz keine verschachtelten Duplikate verwendet werden Felder.



Übung zur Verwendung von Arrays



Die Erfahrung hat gezeigt, dass es einige Übung erfordert, verschachtelte Wiederholungsfelder erfolgreich zu verwenden. Der Google Analytics-Beispieldatensatz in BigQuery ist für diesen Zweck ideal. Der einfachste Weg, verschachtelte Daten in einem Schema zu identifizieren, besteht darin, das Wort RECORD in der Spalte Type zu finden, das dem Datentyp STRUCT entspricht, und das Wort REPEATED in der Spalte Mode, wie unten gezeigt:





In diesem Beispiel ist das Feld TOTALS STRUCT (wird jedoch nicht wiederholt), und das Feld HITS ist STRUCT und wird wiederholt. Dies ist sinnvoll, da Google Analytics Besuchersitzungsdaten auf Aggregationsebene (ein Sitzungswert für totals.hits) und auf Granularitätsebene (separate Trefferzeitwerte für jede von Ihrer Website abgerufene Seite und Bilder) verfolgt. ... Das Speichern von Daten auf diesen verschiedenen Detailebenen ohne Duplizieren der Besucher-ID in Datensätzen ist nur mit Arrays möglich. Nachdem Sie die Daten in einem sich wiederholenden Format mit Arrays gespeichert haben, müssen Sie in Betracht ziehen, diese Daten in Ihren Anforderungen mithilfe von UNNEST bereitzustellen. Beispiel:

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]

Sie können dann normale SQL-Vorgänge wie WHERE ausführen, um Treffer auf Seiten mit Titeln wie Shopping Cart zu filtern. Versuch es!



Andererseits verwendet das GitHub-Dataset für öffentliche Festschreibungsinformationen (bigquery-publicdata.githubrepos.commits) ein verschachteltes Wiederholungsfeld (reponame), um die Liste der vom Festschreiben betroffenen Repositorys zu speichern. Es ändert sich nicht im Laufe der Zeit und bietet schnellere Abfragen, die nach anderen Feldern filtern.



Speichern von Daten als geografische Typen



Das öffentliche BigQuery-Dataset enthält eine Tabelle mit den Grenzen der US-Postleitzahl (bigquery-public-data.utilityus.zipcodearea) und eine weitere Tabelle mit Polygonen, die die Grenzen der US-Städte beschreiben (bigquery-publicdata.utilityus.uscitiesarea). Eine Postleitzahlenspalte ist eine Zeichenfolge, während eine city_geom-Spalte ein geografischer Typ ist.



Aus diesen beiden Tabellen können Sie eine Liste aller Postleitzahlen für Santa Fe in New Mexico abrufen, wie unten gezeigt:

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%'

Diese Abfrage dauert 51,9 Sekunden, verarbeitet 305,5 MB Daten und gibt die folgenden Ergebnisse zurück:





Warum dauert diese Anfrage so lange? Nicht wegen der STINTERSECTS-Operation, sondern hauptsächlich, weil die STGeogFromText-Funktion die S2-Zellen auswerten und den GEOGRAPHY-Typ erstellen muss, der jeder Postleitzahl entspricht.



Wir können versuchen, die Postleitzahlentabelle vorher zu ändern und die Geometrie als GEOGRAPHIE-Wert zu speichern:

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 (siehe vorherige Abfrage) ist eine bequeme Möglichkeit, eine Spalte aus einem SELECT * -Ausdruck zu ersetzen.

Der neue Datensatz ist 131,8 MB groß und damit deutlich größer als die 116,5 MB in der Originaltabelle. Abfragen für diese Tabelle können jedoch die S2-Abdeckung verwenden und sind viel schneller. Die folgende Abfrage dauert beispielsweise 5,3 Sekunden (eine 10-fache Geschwindigkeitssteigerung) und verarbeitet 320,8 MB (eine leichte Kostensteigerung bei Verwendung eines "On-Demand" -Tarifplans):

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%'

Die Leistungsvorteile beim Speichern von geografischen Daten in einer GEOGRAPHY-Spalte sind mehr als überzeugend. Aus diesem Grund ist das Utilityus-Dataset veraltet (es ist weiterhin verfügbar, um die bereits geschriebenen Abfragen aufrechtzuerhalten). Wir empfehlen die Verwendung der Tabelle bigquery-public-data.geousboundaries.uszip_codes, in der geografische Informationen in einer GEOGRAPHY-Spalte gespeichert und ständig aktualisiert werden.



»Weitere Details zum Buch finden Sie auf der Website des Verlags.

» Inhaltsverzeichnis

» Auszug



für Einwohner 25% Rabatt auf den Gutschein - Google



Nach Zahlung der Papierversion des Buches wird ein E-Book per E-Mail verschickt.