Cara membongkar data dengan struktur bersarang dari Google BigQuery menggunakan contoh parameter khusus Google Analytics

Google BigQuery adalah basis data cloud populer yang digunakan oleh perusahaan di seluruh dunia. Terutama nyaman untuk bekerja dengan data Google Analytics mentah: di GA 360, integrasi dengan BigQuery dikonfigurasikan dalam beberapa klik, dan untuk versi gratis terdapat skrip dan modul pihak ketiga.

Dalam data mentah Google Analytics, setiap catatan (baris) berhubungan dengan satu sesi. Di dalam entri tersebut terdapat bidang bersarang yang terkait dengan klik sesi:


Seringkali struktur data bersarang seperti itu membingungkan bagi pengguna yang tidak mengerti cara bekerja dengannya dan menurunkan data tersebut.

Dengan menggunakan parameter khusus Google Analytics sebagai contoh, saya akan mencoba menjelaskan "dengan jari" bagaimana data yang disematkan disimpan di Google BigQuery dan bagaimana data itu dapat diunggah.

Kode kueri yang diberikan berfungsi, mereka dapat digunakan dalam menyelesaikan masalah, mengganti nama tabel mereka dan nomor indeks Dimensi Khusus yang diperlukan.

• Membongkar baris
• Bongkar sambil mempertahankan struktur bersarang
• Contoh penggantian nilai parameter pengguna

Dasar-dasarnya

BigQuery mendukung 2 dialek SQL: Legacy dan Standard. Google merekomendasikan untuk menggunakan SQL Standard yang lebih baru, di mana kami akan menulis pertanyaan untuk dibongkar.
Setiap orang yang telah bekerja dengan SQL setidaknya sedikit mengetahui konstruksi kueri standar:

SELECT *  * FROM *  * WHERE * * 

Desain ini berfungsi jika struktur tabelnya sederhana, tanpa bidang lain bersarang di dalam sel:


Kami menganggap tabel dengan bidang bersarang. Struktur tabel seperti itu (misalnya, parameter pengguna Google Analytics):

Opsi GA Kustom dalam BQ

Di Google BigQuery, tabel seperti itu akan memiliki nama kolom berikut (pemisah "." Menampilkan struktur bersarang):


Jadi, bagaimana kita membongkar data dari bidang bersarang?

Membongkar Baris

Kembali ke tabel dengan contoh parameter khusus GA di BQ.

Kolom customDimensions.index dan customDimensions.value adalah indeks dan nilai sesi dan Dimensi Khusus yang ditentukan pengguna .

Kolom hits.customDimensions.index dan hits.customDimensions.value adalah indeks dan nilai Dimensi Khusus hit .

Di Google BigQuery ada tingkat tindakan parameter pengguna lain - produk. Nama dan nilai Dimensi Khusus produk di Google BigQuery ada di kolom hits.product.customDimensions.index dan hits.product.customDimensions.value . Mereka diturunkan oleh analogi dengan parameter pengguna hit, Anda hanya perlu mempertimbangkan tingkat lain dari sarang.

Sesi dan opsi pengguna tingkat pengguna

Apa yang harus kita lakukan jika kita perlu menurunkan nilai-nilai Dimensi Khusus sesi (khusus) untuk setiap tanggal tanpa menyimpan struktur bersarang (mis. Baris demi baris)?

Untuk menjawab pertanyaan, mari kita lihat lebih dekat tabel dengan parameter pengguna GA di BQ.
Ini menunjukkan bahwa nilai-nilai sel di kolom customDimensions adalah tabel lain :


Cukup membuat subquery ke tabel ini dalam permintaan utama:

 SELECT --   date, --    value (SELECT value --   customDimensions,     t FROM t.customDimensions --       WHERE index = 1) AS customDimensions1 FROM --    t     `project.dataset.tablename` AS t 

Outputnya adalah tabel:


Jika kita perlu menambahkan kolom dengan nilai parameter pengguna lain, kita membuat subquery lain:

 SELECT date, (SELECT value FROM t.customDimensions WHERE index = 1) AS customDimensions1, --      customDimensions (SELECT value FROM t.customDimensions WHERE index = 2) AS customDimensions2 FROM `project.dataset.tablename` AS t 

Kami mendapatkan yang berikut ini:

Opsi khusus tingkat klik

Parameter pengguna hit diturunkan dengan cara yang sama seperti yang sesi (pengguna), kecuali bahwa subquery perlu dilakukan ke tabel hits bersarang. Dengan kata lain, nilai sel di kolom klik di tabel data mentah Google Analytics adalah tabel bersarang tempat tabel customDimensions bersarang:


Permintaan untuk mengunduh parameter pengguna klik per baris akan sebagai berikut:

 SELECT --   date, --    value (SELECT value --   customDimensions,     h FROM h.customDimensions --       WHERE index = 3) AS customDimensions3 FROM --    t     `project.dataset.tablename` AS t, --   t.hits  h     t.hits AS h 

Hasil kueri akan berupa tabel:


Anda dapat membongkar beberapa parameter pengguna klik dan menambahkan parameter hitNumber (nomor urut hit di sesi):

 SELECT date, h.hitNumber AS hitNumber, (SELECT value FROM h.customDimensions WHERE index = 3) AS customDimensions3, --        h.customDimensions (SELECT value FROM h.customDimensions WHERE index = 4) AS customDimensions4 FROM `project.dataset.tablename` AS t, t.hits AS h 

Dapatkan meja:

Sesi (pengguna) + parameter pengguna hit

Jika dalam satu kueri kami ingin menurunkan sesi dan menekan parameter pengguna, Anda hanya perlu membuat subquery yang diperlukan untuk tabel utama dan bersarang:

 SELECT date, h.hitNumber AS hitNumber, --     (SELECT value FROM t.customDimensions WHERE index=1) AS customDimensions1, (SELECT value FROM t.customDimensions WHERE index=2) AS customDimensions2, --     (SELECT value FROM h.customDimensions WHERE index=3) AS customDimensions3, (SELECT value FROM h.customDimensions WHERE index=4) AS customDimensions4 FROM `project.dataset.tablename` AS t, t.hits AS h 

Tabel yang akan diperoleh sebagai hasil dari kueri:

Bongkar sambil mempertahankan struktur bersarang

Pembongkaran seperti itu mungkin diperlukan saat mengganti nilai parameter pengguna di Google BigQuery.

Contoh
Di Google Analytics, nama negara dalam format penuh ditransfer ke parameter pengguna sesi dengan indeks 12 dan parameter pengguna hit dengan indeks 25 untuk pengguna dari Rusia: RUSSIA. Anda perlu mengubah format negara menjadi disingkat: RUS.

Untuk melakukan ini, Anda perlu mengganti nilai yang diperlukan dari parameter pengguna dengan negara pengguna untuk seluruh riwayat data di Google BigQuery.

Prosedur untuk memecahkan masalah:

1. Bongkar semua data sambil mempertahankan struktur bersarang
2. Ganti nilai parameter pengguna dengan negara
3. Tulis ulang tabelnya

Untuk mengunggah data sambil mempertahankan struktur bersarang, Anda harus menggunakan fungsi ARRAY dan konstruk SELECT AS STRUCT. Mari kita cari tahu apa itu.

Sintaks fungsi ARRAY adalah sebagai berikut:

 ARRAY(**) 

Ini mengembalikan array elemen.

Membandingkan array dengan rekaman baris demi baris:

Di sebelah kiri adalah array, di sebelah kanan adalah rekaman garis

Jika kita ingin menyimpan struktur bersarang dan membongkar array dengan beberapa kolom, kita harus menggunakan ARRAY (SELECT AS STRUCT ...) :

Array bersarang

Sesi dan opsi pengguna tingkat pengguna

Untuk membongkar sambil mempertahankan struktur Dimensi Khusus sesi (khusus), kami menggunakan kueri:

 SELECT date, --  ARRAY(SELECT AS STRUCT...)    ARRAY(SELECT AS STRUCT index, value FROM t.customDimensions) AS customDimensions FROM `project.dataset.tablename` AS t 

Sebagai hasil dari eksekusi, sebuah tabel diperoleh di mana struktur bersarang dari data Google Analytics "mentah" disimpan:

Opsi khusus tingkat klik

Untuk menurunkan nilai parameter pengguna hit dari Google BigQuery sambil mempertahankan struktur bersarang, penting untuk dicatat bahwa tabel customDimensions bersarang di tabel hits. Dengan kata lain, Anda perlu membuat subquery ARRAY (PILIH SEBAGAI STRUKTUR ...) 2 kali: pertama ke tabel hits bersarang, kemudian ke tabel customDimensions bersarang:

 SELECT date, --    t.hits ARRAY(SELECT AS STRUCT hitNumber, --    h.customDimensions ARRAY(SELECT AS STRUCT index, value FROM h.customDimensions) AS customDimensions FROM t.hits AS h ) AS hits FROM `project.dataset.tablename` AS t 

Hasil dari kueri ini adalah tabel:

Sesi (pengguna) + parameter pengguna hit

Seperti halnya pembongkaran baris demi baris, kita perlu menggabungkan subquery ARRAY (SELECT AS STRUCT ...) dalam kueri yang sama ke tabel bersarang yang diinginkan:

 SELECT date, --  () Custom Dimensions ARRAY(SELECT AS STRUCT index, value FROM t.customDimensions ) AS customDimensions, --  Custom Dimensions ARRAY(SELECT AS STRUCT hitNumber, ARRAY(SELECT AS STRUCT index, value FROM h.customDimensions ) AS customDimensions FROM t.hits AS h) AS hits FROM `project.dataset.tablename` AS t 

Apa hasilnya:

Contoh mengganti nilai parameter pengguna

Mari kita kembali ke contoh kita.
Di bagian sebelumnya, kami menerima permintaan untuk mengunggah sesi (pengguna) dan mengenai parameter pengguna Google Analytics sambil mempertahankan struktur bersarang.
Kami melengkapi kueri ini dengan SELECT * REPLACE untuk pembongkaran dengan penggantian dan KASUS untuk memperbarui nilai-nilai parameter pengguna yang diperlukan:

 --      t.customDimensions  t.hits SELECT *REPLACE( --  () Custom Dimensions ARRAY(SELECT AS STRUCT index, --      CASE WHEN index=12 AND value='RUSSIA' THEN 'RUS' ELSE value END AS value FROM t.customDimensions) AS customDimensions, --  Custom Dimensions --   t.hits      h.customDimensions ARRAY(SELECT AS STRUCT *REPLACE( ARRAY(SELECT AS STRUCT index, --      CASE WHEN index=25 AND value='RUSSIA' THEN 'RUS' ELSE value END AS value FROM h.customDimensions) AS customDimensions) FROM t.hits AS h) AS hits) FROM `project.dataset.tablename` AS t 

Sebagai hasil dari permintaan ini, kami akan mendapatkan tabel asli dengan data mentah dari Google Analytics. Dia akan sepenuhnya mempertahankan struktur sarang asli, tetapi nilai-nilai dari parameter pengguna yang diperlukan akan diubah ke yang baru.

Topik bekerja dengan struktur data bersarang di Google BigQuery tidak mudah.

Saya harap saya berhasil mengklarifikasi masalah ini. Tetapi, izinkan saya mengingatkan Anda, cara terbaik untuk belajar bagaimana melakukan sesuatu adalah dengan berlatih lebih banyak.