Buku “Google BigQuery. Semua Tentang Data Warehousing, Analytics dan Machine Learning "

Halo para Penduduk! Apakah Anda terintimidasi oleh kebutuhan untuk memproses kumpulan data petabyte? Kenali Google BigQuery, sistem penyimpanan informasi yang dapat menggabungkan data di seluruh perusahaan, memfasilitasi analisis interaktif, dan mengaktifkan pembelajaran mesin. Sekarang Anda dapat menyimpan, membuat kueri, mengambil, dan menjelajahi data secara efisien dalam satu lingkungan yang nyaman. Walyappa Lakshmanan dan Jordan Taijani mengajari Anda cara bekerja di gudang data modern menggunakan kekuatan penuh dari cloud publik tanpa server yang dapat diskalakan. Dengan buku ini, Anda akan: - Mempelajari internal BigQuery - Menjelajahi jenis data, fungsi, dan operator yang didukung Big Query - Mengoptimalkan kueri dan menerapkan skema untuk meningkatkan kinerja atau mengurangi biaya - Mempelajari tentang GIS, perjalanan waktu, DDL / DML.fungsi khusus dan skrip SQL - Selesaikan banyak tantangan pembelajaran mesin - Pelajari cara melindungi data, melacak kinerja, dan mengautentikasi pengguna.

Meminimalkan biaya jaringan

Meminimalkan Biaya Jaringan BigQuery adalah layanan regional yang tersedia di seluruh dunia. Misalnya, jika Anda meminta kumpulan data yang disimpan di wilayah UE, permintaan tersebut akan dijalankan di server yang terletak di pusat data di Uni Eropa. Agar Anda dapat menyimpan hasil kueri dalam tabel, harus dalam kumpulan data yang juga berada di wilayah UE. Namun, REST API BigQuery dapat dipanggil (yaitu menjalankan kueri) dari mana saja di dunia, bahkan dari komputer di luar GCP. Saat bekerja dengan resource GCP lainnya, seperti Google Cloud Storage atau Cloud Pub / Sub, performa terbaik diperoleh jika keduanya berada di region yang sama dengan set data. Oleh karena itu, jika permintaan dijalankan dari instance Compute Engine atau cluster Cloud Dataproc, overhead jaringan akan minimal,jika instance atau cluster juga berada di region yang sama dengan set data yang diminta. Saat mengakses BigQuery dari luar GCP, pertimbangkan topologi jaringan Anda dan coba minimalkan jumlah lompatan antara komputer klien dan pusat GCP tempat kumpulan data berada.



Respons yang ringkas dan tidak lengkap



Dengan mengakses REST API secara langsung, overhead jaringan dapat dikurangi dengan menerima respons yang ringkas dan tidak lengkap. Untuk menerima tanggapan terkompresi, Anda dapat menentukan di header HTTP bahwa Anda siap menerima arsip gzip dan memastikan bahwa baris "gzip" ada di tajuk Agen-Pengguna, misalnya:

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

Dalam kasus ini, semua tanggapan akan dikompresi menggunakan gzip. Secara default, respons BigQuery berisi semua kolom yang tercantum dalam dokumentasi. Namun, jika kami mengetahui bagian mana dari jawaban yang kami minati, kami dapat meminta BigQuery untuk hanya mengirimkan bagian tersebut, sehingga mengurangi overhead jaringan. Misalnya, di bab ini, kami melihat cara mendapatkan informasi lengkap tentang suatu pekerjaan menggunakan API Pekerjaan. Jika Anda hanya tertarik pada subset dari jawaban lengkap (misalnya, hanya langkah-langkah dalam rencana kueri), Anda bisa menentukan bidang minat untuk membatasi ukuran respons:

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

Harap dicatat bahwa itu juga menyatakan bahwa kami menerima data terkompresi gzip.



Menggabungkan beberapa permintaan ke dalam paket



Saat menggunakan REST API, dimungkinkan untuk menggabungkan beberapa panggilan BigQuery API menggunakan jenis konten multipart / campuran dan permintaan HTTP bertingkat di setiap bagian. Isi setiap bagian menentukan operasi HTTP (GET, PUT, dll.), Jalur ke URL, header, dan isi. Sebagai tanggapan, server akan mengirim respons HTTP tunggal dengan tipe konten multipart / campuran, yang setiap bagiannya akan berisi respons (dalam urutan) ke permintaan terkait dalam permintaan batch. Meskipun tanggapan dikembalikan dalam urutan tertentu, server dapat memproses panggilan dalam urutan apa pun. Oleh karena itu, permintaan batch dapat dianggap sebagai sekelompok permintaan yang dijalankan secara paralel. Berikut adalah contoh pengiriman permintaan batch untuk mendapatkan beberapa detail dari rencana eksekusi dari lima permintaan terakhir dalam proyek kita. Kami pertama kali menggunakan alat baris perintah BigQuery,untuk mendapatkan lima misi terakhir yang berhasil:

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

Permintaan dikirim ke titik akhir BigQuery untuk pemrosesan batch:

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

Permintaan individu dapat ditentukan di jalur 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

Kemudian Anda dapat mengirim permintaan sebagai permintaan gabungan:

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

Baca Massal Menggunakan BigQuery Storage API



Di Bab 5, kita membahas penggunaan BigQuery REST API dan library klien untuk menghitung tabel dan mengambil hasil kueri. REST API mengembalikan data sebagai catatan paginasi yang lebih cocok untuk kumpulan hasil yang relatif kecil. Namun, dengan munculnya pembelajaran mesin dan alat ekstrak, transformasi, dan pemuatan (ETL) terdistribusi, alat eksternal sekarang memerlukan akses massal yang cepat dan efisien ke repositori BigQuery terkelola. Akses membaca massal ini disediakan di BigQuery Storage API melalui protokol Remote Procedure Call (RPC). Dengan BigQuery Storage API, data terstruktur dikirim melalui jaringan dalam format serialisasi biner yang lebih cocok dengan format penyimpanan data berbentuk kolom.Ini memberikan paralelisasi tambahan dari hasil yang ditetapkan di beberapa konsumen.



Pengguna akhir tidak menggunakan BigQuery Storage API secara langsung. Sebagai gantinya, mereka menggunakan Cloud Dataflow, Cloud Dataproc, TensorFlow, AutoML, dan alat lain yang menggunakan Storage API untuk membaca data secara langsung dari penyimpanan terkelola, bukan melalui BigQuery API.



Karena Storage API mengakses data yang disimpan secara langsung, izin untuk mengakses BigQuery Storage API berbeda dari BigQuery API yang ada. Izin BigQuery Storage API harus dikonfigurasi secara terpisah dari izin BigQuery.



BigQuery Storage API memberikan beberapa manfaat untuk alat yang membaca data langsung dari penyimpanan terkelola BigQuery. Misalnya, konsumen dapat membaca kumpulan data yang tidak tumpang tindih dari tabel menggunakan beberapa utas (misalnya, dengan mengizinkan pembacaan terdistribusi dari server produksi yang berbeda di Cloud Dataproc), secara dinamis membagi utas ini (sehingga mengurangi latensi ekor, yang dapat menjadi masalah serius untuk pekerjaan MapReduce) , pilih subset kolom untuk dibaca (untuk meneruskan fitur yang digunakan oleh model ke struktur pembelajaran mesin), memfilter nilai kolom (mengurangi jumlah data yang dikirim melalui jaringan) dan pada saat yang sama memastikan konsistensi snapshot (yaitu, membaca data dari titik waktu tertentu).



Di Bab 5, kita membahas penggunaan ekstensi %% bigquery di Notebook Jupyter untuk memuat hasil kueri ke DataFrames. Namun, contoh tersebut menggunakan kumpulan data yang relatif kecil - dari selusin hingga beberapa ratus catatan. Apakah mungkin untuk memuat seluruh set data london_bicycles (24 juta catatan) ke dalam DataFrame? Ya, Anda bisa, tetapi dalam kasus ini, Anda harus menggunakan Storage API, bukan BigQuery API, untuk memuat data ke DataFrame. Pertama, Anda perlu menginstal pustaka klien Python Storage API dengan dukungan Avro dan pandas. Ini bisa dilakukan dengan perintah

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

Kemudian yang tersisa hanyalah menggunakan ekstensi %% bigquery, seperti sebelumnya, tetapi tambahkan parameter yang memerlukan penggunaan Storage API:

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

Perhatikan bahwa di sini kami menggunakan kemampuan Storage API untuk menyediakan akses langsung ke kolom individual; tidak perlu membaca seluruh tabel BigQuery ke dalam DataFrame. Jika permintaan mengembalikan sejumlah kecil data, ekstensi secara otomatis akan menggunakan BigQuery API. Karenanya, tidak menakutkan jika Anda selalu menunjukkan flag ini di sel-sel notebook. Untuk mengaktifkan bendera --usebqstorageapi di semua sel buku catatan, Anda bisa menyetel bendera konteks:

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

Memilih format penyimpanan yang efisien

Kinerja kueri bergantung pada di mana dan dalam format apa data yang menyusun tabel disimpan. Secara umum, semakin sedikit kueri yang diperlukan untuk melakukan pencarian atau jenis konversi, semakin baik kinerjanya.



Sumber Data Internal dan Eksternal



BigQuery mendukung pembuatan kueri sumber data eksternal seperti Google Cloud Storage, Cloud Bigtable, dan Google Spreadsheet, tetapi Anda hanya bisa mendapatkan performa terbaik dari tabel Anda sendiri.



Sebaiknya gunakan BigQuery sebagai repositori data analitik untuk semua data terstruktur dan semi-terstruktur Anda. Sumber data eksternal paling baik digunakan untuk penyimpanan bertahap (Google Cloud Storage), upload langsung (Cloud Pub / Sub, Cloud Bigtable), atau update berkala (Cloud SQL, Cloud Spanner). Selanjutnya, siapkan pipeline data Anda untuk memuat data sesuai jadwal dari sumber eksternal ini ke BigQuery (lihat Bab 4).



Jika Anda perlu meminta data dari Google Cloud Storage, simpan dalam format kolom terkompresi (seperti Parquet) jika memungkinkan. Gunakan format berbasis catatan seperti JSON atau CSV sebagai pilihan terakhir.



Staging Bucket Lifecycle Management



Jika Anda mengupload data ke BigQuery setelah memasukkannya ke Google Cloud Storage, pastikan untuk menghapusnya dari cloud setelah mengupload. Jika Anda menggunakan pipeline ETL untuk memuat data ke BigQuery (untuk mengubahnya secara signifikan atau hanya menyisakan sebagian datanya), Anda mungkin ingin menyimpan data asli ke Google Cloud Storage. Dalam kasus seperti itu, Anda dapat membantu mengurangi biaya dengan menentukan aturan pengelolaan siklus proses bucket yang menurunkan versi penyimpanan di Google Cloud Storage.



Berikut cara mengaktifkan manajemen siklus proses bucket dan menyiapkan perpindahan otomatis data dari wilayah federasi atau kelas standar yang lebih dari 30 hari ke Nearline Storage, dan data yang disimpan di Nearline Storage selama lebih dari 90 hari ke Coldline Storage:

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

Dalam contoh ini, file lifecycle.yaml berisi kode berikut:

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

Anda dapat menggunakan manajemen siklus hidup tidak hanya untuk mengubah kelas suatu objek, tetapi juga untuk menghapus objek yang lebih tua dari ambang batas tertentu.



Menyimpan data sebagai array dan struktur



Selain kumpulan data lain yang tersedia untuk publik, BigQuery memiliki kumpulan data yang berisi informasi tentang badai siklon (angin topan, topan, siklon, dll.) Dari layanan meteorologi di seluruh dunia. Badai siklon dapat berlangsung hingga beberapa minggu, dan parameter meteorologinya diukur kira-kira setiap tiga jam. Misalkan Anda memutuskan untuk menemukan dalam kumpulan data ini semua badai yang terjadi pada tahun 2018, kecepatan angin maksimum yang dicapai oleh setiap badai, dan waktu serta lokasi badai saat kecepatan maksimum tersebut tercapai. Kueri berikut mengambil semua informasi ini dari kumpulan data publik:

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

Kueri mengambil pengenal badai (sid), musim, kolam, dan nama badai (jika ditetapkan) dan kemudian menemukan serangkaian pengamatan yang dibuat untuk badai itu, memberi peringkat pengamatan dalam urutan kecepatan angin dan memilih kecepatan maksimum untuk setiap badai ... Badai itu sendiri diurutkan berdasarkan nomor urut. Hasilnya mencakup 88 rekaman dan terlihat seperti ini:





Permintaan tersebut memakan waktu 1,4 detik dan diproses 41,7 MB. Entri pertama menjelaskan badai Bolaven, yang mencapai kecepatan maksimum 29 m / s pada 2 Januari 2018 pukul 18:00 UTC.



Karena pengamatan dilakukan oleh beberapa layanan meteorologi, data ini dapat distandarisasi menggunakan kolom bersarang dan disimpan di BigQuery, seperti yang ditunjukkan di bawah ini:

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

Query pada tabel ini terlihat sama dengan query pada tabel asli, tetapi dengan sedikit perubahan pada nama kolomnya (usa.latitude bukan 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

Permintaan ini memproses jumlah data yang sama dan berjalan dalam jumlah waktu yang sama seperti aslinya, menggunakan kumpulan data publik. Penggunaan bidang bersarang (struktur) tidak mengubah kecepatan atau biaya kueri, tetapi bisa membuat kueri lebih mudah dibaca. Karena ada banyak pengamatan atas badai yang sama selama durasinya, kita dapat mengubah penyimpanan agar sesuai dengan seluruh rangkaian pengamatan untuk setiap badai dalam satu catatan:

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

Perhatikan bahwa sekarang kita menyimpan sid, season, dan karakteristik badai lainnya sebagai kolom skalar, karena tidak berubah tergantung durasinya.



Sisa data, berubah dengan setiap pengamatan, disimpan sebagai susunan struktur. Seperti inilah tampilan kueri untuk tabel baru:

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

Permintaan ini akan mengembalikan hasil yang sama, tetapi kali ini hanya akan memproses 14,7 MB (pengurangan biaya tiga kali lipat) dan selesai dalam satu detik (peningkatan kecepatan 30%). Apa yang menyebabkan peningkatan kinerja ini? Ketika data disimpan sebagai sebuah array, jumlah catatan dalam tabel turun drastis (dari 682.000 menjadi 14.000), 2 karena sekarang hanya ada satu catatan per badai, tidak banyak catatan - satu untuk setiap pengamatan. Kemudian, saat kami memfilter baris berdasarkan musim, BigQuery dapat menghapus banyak kasus terkait secara bersamaan, seperti yang ditunjukkan pada Gambar 1. 7.13.





Keuntungan lainnya adalah tidak perlu menduplikasi rekaman data saat kasus dengan tingkat detail yang berbeda disimpan dalam tabel yang sama. Satu tabel dapat menyimpan data perubahan lintang dan bujur untuk badai dan data tingkat tinggi seperti nama dan musim badai. Dan karena BigQuery menyimpan data tabel dalam kolom menggunakan kompresi, Anda dapat membuat kueri dan memproses data tingkat tinggi tanpa takut biaya pengerjaan data mendetail - sekarang data tersebut disimpan sebagai larik nilai terpisah untuk setiap badai.



Misalnya, untuk mengetahui jumlah badai menurut tahun, Anda hanya dapat menanyakan kolom yang diperlukan:

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

Permintaan sebelumnya memproses 27 MB, yang merupakan setengah dari 56 MB yang harus diproses jika bidang berulang bersarang tidak digunakan.



Bidang bersarang tidak meningkatkan kinerja sendiri, meskipun mereka dapat meningkatkan keterbacaan dengan benar-benar melakukan penggabungan ke tabel terkait lainnya. Selain itu, bidang berulang bersarang sangat berguna dari sudut pandang kinerja. Pertimbangkan untuk menggunakan bidang berulang bertingkat dalam skema Anda karena mereka dapat secara dramatis meningkatkan kecepatan dan mengurangi biaya kueri pemfilteran pada kolom tidak bersarang atau berulang (dalam kasus kami, musim).



Kerugian utama dari bidang berulang bersarang adalah kesulitan dalam mengimplementasikan streaming ke dalam tabel seperti itu jika pembaruan streaming melibatkan penambahan item ke array yang ada. Ini jauh lebih sulit untuk diterapkan daripada menambahkan catatan baru: Anda perlu mengubah catatan yang ada - untuk tabel informasi badai ini adalah kelemahan yang signifikan karena pengamatan baru terus-menerus ditambahkan ke dalamnya, dan ini menjelaskan mengapa kumpulan data publik ini tidak menggunakan duplikat bersarang bidang.



Praktik menggunakan array



Pengalaman telah menunjukkan bahwa dibutuhkan beberapa latihan untuk berhasil menggunakan bidang berulang bertingkat. Contoh set data Google Analytics di BigQuery ideal untuk tujuan ini. Cara termudah untuk mengidentifikasi data yang disematkan dalam skema adalah dengan menemukan kata RECORD di kolom Type, yang sesuai dengan tipe data STRUCT, dan kata REPEATED di kolom Mode, seperti yang ditunjukkan di bawah ini:





Dalam contoh ini, bidang TOTAL adalah STRUCT (tetapi tidak diulang), dan bidang HITS adalah STRUCT dan berulang. Ini masuk akal, karena Google Analytics melacak data sesi pengunjung di tingkat agregasi (satu nilai sesi untuk totals.hits) dan di tingkat perincian (pisahkan nilai waktu klik untuk setiap halaman dan gambar yang diambil dari situs Anda) ... Menyimpan data pada tingkat detail yang berbeda ini tanpa menduplikasi visitorId dalam catatan hanya dapat dilakukan dengan array. Setelah menyimpan data dalam format berulang dengan array, Anda perlu mempertimbangkan untuk menyebarkan data tersebut dalam permintaan Anda menggunakan UNNEST, misalnya:

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]

Anda kemudian dapat melakukan operasi SQL normal seperti WHERE untuk memfilter klik pada halaman dengan judul seperti Keranjang Belanja. Cobalah!



Di sisi lain, kumpulan data informasi komit publik GitHub (bigquery-publicdata.githubrepos.commits) menggunakan bidang berulang bersarang (nama ulang) untuk menyimpan daftar repositori yang terpengaruh oleh komit. Itu tidak berubah seiring waktu dan memberikan kueri lebih cepat yang memfilter bidang lain.



Menyimpan data sebagai tipe geografis



Set data publik BigQuery berisi tabel batas area kode pos AS (bigquery-public-data.utilityus.zipcodearea) dan tabel lain dengan poligon yang menjelaskan batas kota AS (bigquery-publicdata.utilityus.uscitiesarea). Kolom zipcodegeom adalah string, sedangkan kolom city_geom adalah tipe geografis.



Dari dua tabel ini, Anda bisa mendapatkan daftar semua kode pos untuk Santa Fe di New Mexico, seperti yang ditunjukkan di bawah ini:

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

Kueri ini memerlukan waktu 51,9 detik, memproses 305,5 MB data, dan mengembalikan hasil berikut:





Mengapa permintaan ini berlangsung lama? Ini bukan karena operasi STINTERSECTS, tetapi terutama karena fungsi STGeogFromText harus mengevaluasi sel S2 dan menyusun jenis GEOGRAPHY yang sesuai dengan setiap kode pos.



Kami dapat mencoba memodifikasi tabel kode pos dengan melakukan ini sebelumnya dan menyimpan geometri sebagai nilai 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 (lihat kueri sebelumnya) adalah cara mudah untuk mengganti kolom dari ekspresi SELECT *.

Dataset baru berukuran 131,8 MB, yang secara signifikan lebih besar dari 116,5 MB di tabel asli. Namun, kueri terhadap tabel ini dapat menggunakan cakupan S2 dan jauh lebih cepat. Misalnya, kueri berikut memerlukan waktu 5,3 detik (peningkatan kecepatan 10x) dan memproses 320,8 MB (sedikit peningkatan biaya saat menggunakan rencana tarif "sesuai permintaan"):

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

Manfaat kinerja dari menyimpan data geografis dalam kolom GEOGRAFI lebih dari menarik. Inilah sebabnya mengapa kumpulan data utilityus tidak digunakan lagi (masih tersedia untuk menjaga kueri yang sudah ditulis) tetap hidup. Kami merekomendasikan penggunaan tabel bigquery-public-data.geousboundaries.uszip_codes, yang menyimpan informasi geografis dalam kolom GEOGRAFI dan terus diperbarui.



»Rincian lebih lanjut tentang buku dapat ditemukan di situs web penerbit

» Daftar Isi

» Kutipan



Untuk Habitants diskon 25% untuk kupon - Google



Setelah pembayaran untuk versi kertas buku, sebuah e-book dikirim ke email.