Memuat dan membuat kueri data dengan alat command line bq untuk BigQuery

1. Pengantar

BigQuery adalah database analisis hemat biaya dan terkelola sepenuhnya dari Google. Dengan BigQuery, Anda dapat membuat kueri data berukuran terabyte tanpa administrator database atau infrastruktur. BigQuery menggunakan SQL yang sudah dikenal dan model pengisian daya bayar sesuai penggunaan. Dengan BigQuery, Anda dapat berfokus pada analisis data untuk menemukan insight yang bermakna. Dalam codelab ini, Anda akan menggunakan alat command line bq untuk memuat file CSV lokal ke tabel BigQuery yang baru.

Yang akan Anda pelajari

• Cara menggunakan alat command line bq untuk BigQuery
• Cara memuat file data lokal ke tabel BigQuery

Yang Anda butuhkan

• Project Google Cloud
• Browser, seperti Google Chrome

2. Memulai persiapan

Mengaktifkan BigQuery

Jika belum memiliki Akun Google, Anda harus membuatnya.

1. Login ke Konsol Google Cloud, lalu buka BigQuery. Anda juga dapat membuka UI web BigQuery secara langsung dengan memasukkan URL berikut di browser Anda.

https://console.cloud.google.com/bigquery

2. Setujui persyaratan layanan.
3. Sebelum dapat menggunakan BigQuery, Anda harus membuat proyek. Ikuti petunjuk untuk membuat project baru.

Pilih nama project dan catat ID project-nya.

ID Project adalah nama unik di semua project Google Cloud. Project ID tersebut selanjutnya akan dirujuk di codelab ini sebagai PROJECT_ID.

Codelab ini menggunakan resource BigQuery bersama batas sandbox BigQuery. Akun penagihan tidak diperlukan. Jika nanti Anda ingin menghapus batas sandbox, Anda dapat menambahkan akun penagihan dengan mendaftar ke uji coba gratis Google Cloud.

Cloud Shell

Anda akan menggunakan Cloud Shell, yakni lingkungan command line yang berjalan di Google Cloud.

Mengaktifkan Cloud Shell

1. Dari Cloud Console, klik Aktifkan Cloud Shell .

Jika belum pernah memulai Cloud Shell, Anda akan melihat layar perantara (di paruh bawah) yang menjelaskan apa itu Cloud Shell. Jika demikian, klik Lanjutkan (dan Anda tidak akan pernah melihatnya lagi). Berikut tampilan layar sekali-tampil tersebut:

Perlu waktu beberapa saat untuk penyediaan dan terhubung ke Cloud Shell.

Mesin virtual ini dimuat dengan semua alat pengembangan yang Anda butuhkan. Layanan ini menawarkan direktori beranda tetap sebesar 5 GB dan beroperasi di Google Cloud, sehingga sangat meningkatkan performa dan autentikasi jaringan. Sebagian besar pekerjaan Anda dalam codelab ini dapat dilakukan hanya dengan browser atau Chromebook.

Setelah terhubung ke Cloud Shell, Anda akan melihat bahwa Anda sudah diautentikasi dan project sudah ditetapkan ke project ID Anda.

2. Jalankan perintah berikut di Cloud Shell untuk mengonfirmasi bahwa Anda telah diautentikasi:

gcloud auth list

Output perintah

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Catatan: Alat command-line gcloud adalah alat command-line yang canggih dan terpadu di Google Cloud. Alat ini sudah diinstal lebih dulu di Cloud Shell. Anda akan melihat dukungannya untuk penyelesaian tab. Untuk mengetahui informasi selengkapnya, baca ringkasan alat command line gcloud.

3. Jalankan perintah berikut di Cloud Shell untuk mengonfirmasi bahwa perintah gcloud mengetahui project Anda:

gcloud config list project

Output perintah

[core]
project = <PROJECT_ID>

Jika tidak, Anda dapat menyetelnya dengan perintah ini:

gcloud config set project <PROJECT_ID>

Output perintah

Updated property [core/project].

3. Membuat set data

Buat set data untuk memuat tabel Anda.

Apa yang dimaksud dengan set data?

Set data BigQuery adalah kumpulan tabel. Semua tabel dalam set data disimpan di lokasi data yang sama. Anda juga dapat melampirkan kontrol akses kustom untuk membatasi akses ke set data dan tabelnya.

Membuat set data

Di Cloud Shell, gunakan perintah bq mk untuk membuat set data bernama "bq_load_codelab".

bq mk bq_load_codelab

Melihat properti set data

Verifikasi bahwa Anda telah membuat {i>dataset<i} dengan melihat properti {i>dataset<i} menggunakan perintah bq show.

bq show bq_load_codelab

Anda akan melihat output yang mirip dengan berikut ini:

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      [e-mail]
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. Membuat file data

BigQuery dapat memuat data dari beberapa format data, termasuk JSON yang dibatasi baris baru, Avro, dan CSV. Untuk mempermudah, Anda akan menggunakan CSV.

Membuat file CSV

Di Cloud Shell, buat file CSV kosong.

touch customer_transactions.csv

Buka file CSV di editor kode di Cloud Shell dengan menjalankan perintah edit cloudshell, yang akan membuka jendela browser baru dengan editor kode dan panel Cloud Shell.

cloudshell edit customer_transactions.csv

Catatan: Editor kode memerlukan cookie pihak ketiga agar dapat berfungsi karena dimuat dari domain yang berbeda dengan yang ada di Konsol Google Cloud.

Di editor kode, masukkan beberapa nilai yang dipisahkan koma untuk dimuat ke BigQuery.

ID,Zipcode,Timestamp,Amount,Feedback,SKU
c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5
c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee
c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0

Simpan file CSV dengan mengklik File > Edit.

5. Muat data

Gunakan perintah {i>bq load<i} untuk memuat {i>file<i} CSV Anda ke dalam tabel BigQuery.

bq load \
    --source_format=CSV \
    --skip_leading_rows=1 \
    bq_load_codelab.customer_transactions \
    ./customer_transactions.csv \
    id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string

Anda menggunakan opsi berikut:

• --source_format=CSV menggunakan format data CSV saat mengurai file data.
• --skip_leading_rows=1 melewati baris pertama dalam file CSV karena merupakan baris header.
• Bq_load_codelab.customer_transactions—the first positional argument— menentukan tabel mana yang harus memuat data.
• ./customer_transactions.csv—argumen posisi kedua—menentukan file yang akan dimuat. Selain file lokal, perintah pemuatan bq dapat memuat file dari Cloud Storage dengan gs://my_bucket/path/to/file URIs.
• Skema, yang dapat ditentukan dalam file skema JSON atau sebagai daftar yang dipisahkan koma. (Anda menggunakan daftar yang dipisahkan koma agar lebih mudah.)

Anda telah menggunakan skema berikut dalam tabel customer_transactions:

• Id:string: ID pelanggan
• Zip:string: Kode pos AS
• Ttime:timestamp: Tanggal dan waktu terjadinya transaksi
• Amount:numeric: Jumlah transaksi (Kolom numerik menyimpan data dalam bentuk desimal, yang berguna untuk nilai moneter.)
• Fdbk:float: Rating dari survei masukan tentang transaksi
• Sku:string: ID untuk item yang dibeli

Mendapatkan detail tabel

Pastikan tabel dimuat dengan menampilkan properti tabel.

bq show bq_load_codelab.customer_transactions

Output:

Table my-project:bq_load_codelab.customer_transactions

   Last modified          Schema          Total Rows   Total Bytes
 ----------------- --------------------- ------------ -------------
  15 Jun 15:13:55   |- id: string         3            159
                    |- zip: string
                    |- ttime: timestamp
                    |- amount: numeric
                    |- fdbk: float
                    |- sku: string

6. Mengkueri data.

Setelah data dimuat, Anda dapat membuat kuerinya menggunakan UI web BigQuery, perintah bq, atau API. Kueri Anda dapat menggabungkan data dengan set data mana pun (atau set data, selama berada di lokasi yang sama) yang izin bacanya Anda miliki.

Menjalankan kueri SQL standar yang menggabungkan set data Anda dengan set data kode pos AS dan menjumlahkan transaksi berdasarkan negara bagian AS. Gunakan perintah kueri bq untuk mengeksekusi kueri.

bq query --nouse_legacy_sql '
SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code
FROM `bq_load_codelab.customer_transactions` c
JOIN `bigquery-public-data.utility_us.zipcode_area` z
ON c.zip = z.zipcode
GROUP BY state_code
'

Perintah tersebut akan menghasilkan output seperti ini:

Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE   
+--------------+------------+
| amount_total | state_code |
+--------------+------------+
|         53.6 | NY         |
|         7.18 | TX         |
+--------------+------------+

Kueri yang Anda jalankan menggunakan {i>dataset<i} publik dan {i>dataset<i} pribadi Anda. Pelajari lebih lanjut dengan membaca versi komentar dari kueri yang sama berikut:

#standardSQL
SELECT
  /* Total of all transactions in the state. */
  SUM(c.amount) AS amount_total,

  /* State corresponding to the transaction's zipcode. */
  z.state_code AS state_code

/* Query the table you just constructed.
 * Note: If you omit the project from the table ID,
 *       the dataset is read from your project. */
FROM `bq_load_codelab.customer_transactions` c

/* Join the table to the zipcode public dataset. */
JOIN `bigquery-public-data.utility_us.zipcode_area` z

/* Find the state corresponding to the transaction's zipcode. */
ON c.zip = z.zipcode

/* Group over all transactions by state. */
GROUP BY state_code

7. Pembersihan

Hapus {i>dataset<i} yang Anda buat dengan perintah {i>bq rm<i}. Gunakan penanda {i>-r<i} untuk menghapus setiap tabel yang ada di dalamnya.

bq rm -r bq_load_codelab

8. Selamat!

Anda telah mengupload tabel ke BigQuery dan membuat kuerinya.

Yang telah Anda bahas

• Menggunakan alat command line bq untuk berinteraksi dengan BigQuery.
• Menggabungkan data Anda dan {i>dataset<i} publik dengan kueri BigQuery.

Langkah berikutnya

Pelajari lebih lanjut:

• Alat command line bq
• Cara lain untuk memuat data ke BigQuery.
• Set data publik lainnya yang tersedia melalui BigQuery.
• Cuaca, kejahatan, dan jenis data lainnya di TIL dengan BigQuery.