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

1. Pengantar

BigQuery adalah database analisis berbiaya rendah dan tanpa pengoperasian (NoOps) yang dikelola sepenuhnya oleh Google. Dengan BigQuery, Anda dapat mengkueri data berskala terabyte tanpa administrator database atau infrastruktur. BigQuery menggunakan SQL yang sudah dikenal dan model pengisian daya bayar hanya untuk yang Anda gunakan. 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 dalam tabel BigQuery baru.

Yang akan Anda pelajari

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

Yang Anda butuhkan

• Project Google Cloud
• Browser, seperti Google Chrome

2. Memulai persiapan

Aktifkan BigQuery

Jika belum memiliki Akun Google, Anda harus membuatnya.

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

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

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

Pilih nama project dan catat project ID.

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 dalam batas sandbox BigQuery. Akun penagihan tidak diperlukan. Jika ingin menghapus batas sandbox nanti, Anda dapat menambahkan akun penagihan dengan mendaftar ke uji coba gratis Google Cloud.

Cloud Shell

Anda akan menggunakan Cloud Shell, 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 Continue (dan Anda tidak akan pernah melihatnya lagi). Berikut tampilan layar sekali-tampil tersebut:

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

Virtual machine ini dimuat dengan semua alat pengembangan yang Anda perlukan. 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 itu 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

Pastikan Anda membuat set data dengan melihat properti set data 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. Buat 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 panel editor kode dan Cloud Shell.

cloudshell edit customer_transactions.csv

Catatan: Editor kode memerlukan cookie pihak ketiga agar berfungsi karena dimuat dari domain yang berbeda dengan 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 bq load untuk memuat file CSV 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 mem-parsing 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 tempat data harus dimuat.
• ./customer_transactions.csv—argumen posisi kedua—menentukan file mana yang akan dimuat. Selain file lokal, perintah bq load 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 untuk mempermudah.)

Anda menggunakan skema berikut dalam tabel customer_transactions:

• Id:string: ID pelanggan
• Zip:string: Kode pos AS
• Ttime:timestamp: Tanggal dan waktu transaksi dilakukan
• 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 kueri menggunakan UI web BigQuery, perintah bq, atau API. Kueri Anda dapat menggabungkan data Anda dengan set data mana pun (atau set data, asalkan berada di lokasi yang sama) yang izin membacanya Anda miliki.

Jalankan kueri SQL standar yang menggabungkan set data Anda dengan set data kode ZIP AS dan menjumlahkan transaksi menurut negara bagian AS. Gunakan perintah kueri bq untuk menjalankan 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 set data publik dan set data pribadi Anda. Pelajari lebih lanjut dengan membaca versi yang diberi komentar dari kueri yang sama ini:

#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 set data yang Anda buat dengan perintah bq rm. Gunakan flag -r untuk menghapus tabel apa pun yang ada di dalamnya.

bq rm -r bq_load_codelab

8. Selamat!

Anda telah mengupload tabel ke BigQuery dan mengkuerinya.

Yang telah Anda pelajari

• Menggunakan alat command line bq untuk berinteraksi dengan BigQuery.
• Menggabungkan data Anda dan set data 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.