Menentukan nilai kolom default

Halaman ini menjelaskan cara menetapkan nilai default untuk kolom dalam tabel BigQuery. Saat Anda menambahkan baris ke tabel yang tidak berisi data untuk kolom dengan nilai default, nilai default akan ditulis ke kolom tersebut.

Ekspresi nilai default

Ekspresi nilai default untuk kolom harus berupa literal atau salah satu dari fungsi berikut:

• CURRENT_DATE
• CURRENT_DATETIME
• CURRENT_TIME
• CURRENT_TIMESTAMP
• GENERATE_UUID
• RAND
• SESSION_USER
• ST_GEOGPOINT

Anda dapat membuat nilai default STRUCT atau ARRAY dengan fungsi ini, seperti [CURRENT_DATE(), DATE '2020-01-01'].

Fungsi dievaluasi saat data ditulis ke tabel. Jenis nilai default harus cocok atau dikonversi ke jenis kolom tempat nilai diterapkan. Jika tidak ada nilai default yang ditetapkan, nilai defaultnya adalah NULL.

Menetapkan nilai default

Anda dapat menetapkan nilai default untuk kolom saat membuat tabel baru. Gunakan pernyataan DDL CREATE TABLE dan tambahkan kata kunci DEFAULT serta ekspresi nilai default setelah nama dan jenis kolom. Contoh berikut membuat tabel bernama simple_table dengan dua kolom STRING, a dan b. Kolom b memiliki nilai default 'hello'.

CREATE TABLE mydataset.simple_table (   a STRING,   b STRING DEFAULT 'hello');

Saat Anda menyisipkan data ke dalam simple_table yang menghilangkan kolom b, nilai default 'hello' akan digunakan—misalnya:

INSERT mydataset.simple_table (a) VALUES ('val1'), ('val2');

Tabel simple_table berisi nilai berikut:

 +------+-------+ | a    | b     | +------+-------+ | val1 | hello | | val2 | hello | +------+-------+ 

Jika kolom memiliki jenis STRUCT, Anda harus menetapkan nilai default untuk seluruh kolom STRUCT. Anda tidak dapat menetapkan nilai default untuk subset kolom. Nilai default untuk array tidak boleh NULL atau berisi elemen NULL. Contoh berikut membuat tabel bernama complex_table dan menetapkan nilai default untuk kolom struct_col yang berisi kolom bertingkat, termasuk jenis ARRAY:

CREATE TABLE mydataset.complex_table (   struct_col STRUCT<x STRUCT<x1 TIMESTAMP, x2 NUMERIC>, y ARRAY<DATE>>     DEFAULT ((CURRENT_TIMESTAMP(), NULL),              [DATE '2022-01-01', CURRENT_DATE()]) );

Anda tidak dapat menetapkan nilai default yang melanggar batasan pada kolom, seperti nilai default yang tidak sesuai dengan jenis parameter atau nilai default NULL saat mode kolom adalah REQUIRED.

Mengubah nilai default

Untuk mengubah nilai default bagi kolom, pilih salah satu opsi berikut:

Menetapkan nilai default untuk sebuah kolom hanya akan memengaruhi penyisipan di masa mendatang pada tabel. Tindakan ini tidak mengubah data tabel yang ada. Contoh berikut menetapkan nilai default kolom a ke SESSION_USER();

ALTER TABLE mydataset.simple_table ALTER COLUMN a SET DEFAULT SESSION_USER();

Jika Anda menyisipkan baris ke dalam simple_table yang menghapus kolom a, pengguna sesi saat ini akan digunakan.

INSERT mydataset.simple_table (b) VALUES ('goodbye');

Tabel simple_table berisi nilai berikut:

 +------------------+---------+ | a                | b       | +------------------+---------+ | val1             | hello   | | val2             | hello   | | [email protected] | goodbye | +------------------+---------+ 

Menghapus nilai default

Untuk menghapus nilai default bagi kolom, pilih salah satu opsi berikut:

Menggunakan pernyataan DML dengan nilai default

Anda dapat menambahkan baris dengan nilai default ke tabel menggunakan pernyataan DML INSERT. Nilai default digunakan jika nilai untuk kolom tidak ditentukan, atau jika kata kunci DEFAULT digunakan sebagai pengganti ekspresi nilai. Contoh berikut membuat tabel dan menyisipkan baris dengan setiap nilainya adalah nilai default:

CREATE TABLE mydataset.mytable (   x TIME DEFAULT CURRENT_TIME(),   y INT64 DEFAULT 5,   z BOOL);  INSERT mydataset.mytable (x, y, z) VALUES (DEFAULT, DEFAULT, DEFAULT);

Tabel mytable terlihat seperti berikut:

 +-----------------+---+------+ | x               | y | z    | +-----------------+---+------+ | 22:13:24.799555 | 5 | null | +-----------------+---+------+ 

Kolom z tidak memiliki nilai default, sehingga NULL digunakan sebagai default. Jika nilai default merupakan fungsi, seperti CURRENT_TIME(), nilai tersebut akan dievaluasi pada saat nilai ditulis. Memanggil INSERT dengan nilai default untuk kolom x sekali lagi akan menghasilkan nilai yang berbeda untuk TIME. Dalam contoh berikut, hanya kolom z yang memiliki nilai yang ditetapkan secara eksplisit, dan kolom yang dihilangkan menggunakan nilai defaultnya:

INSERT mydataset.mytable (z) VALUES (TRUE);

Tabel mytable terlihat seperti berikut:

 +-----------------+---+------+ | x               | y | z    | +-----------------+---+------+ | 22:13:24.799555 | 5 | null | | 22:18:29.890547 | 5 | true | +-----------------+---+------+ 

Anda dapat memperbarui tabel dengan nilai default menggunakan pernyataan DML MERGE. Contoh berikut membuat dua tabel dan memperbarui salah satunya dengan pernyataan MERGE:

CREATE TABLE mydataset.target_table (   a STRING,   b STRING DEFAULT 'default_b',   c STRING DEFAULT SESSION_USER()) AS (   SELECT     'val1' AS a, 'hi' AS b, '[email protected]' AS c   UNION ALL   SELECT     'val2' AS a, 'goodbye' AS b, SESSION_USER() AS c );  CREATE TABLE mydataset.source_table (   a STRING DEFAULT 'default_val',   b STRING DEFAULT 'Happy day!') AS (   SELECT     'val1' AS a, 'Good evening！' AS b   UNION ALL   SELECT     'val3' AS a, 'Good morning!' AS b );  MERGE mydataset.target_table T USING mydataset.source_table S ON T.a = S.a WHEN NOT MATCHED THEN   INSERT(a, b) VALUES (a, DEFAULT);

Hasilnya adalah sebagai berikut:

 +------+-----------+--------------------+ | a    | b         | c                  | +------+-----------+--------------------+ | val1 | hi        | [email protected]     | | val2 | goodbye   | [email protected] | | val3 | default_b | [email protected] | +------+-----------+--------------------+ 

Anda dapat memperbarui tabel dengan nilai default menggunakan pernyataan DML UPDATE. Contoh berikut memperbarui tabel source_table sehingga setiap baris pada kolom b sama dengan nilai defaultnya:

UPDATE mydataset.source_table SET b =  DEFAULT WHERE TRUE;

Hasilnya adalah sebagai berikut:

 +------+------------+ | a    | b          | +------+------------+ | val1 | Happy day! | | val3 | Happy day! | +------+------------+ 

Menambahkan tabel

Anda dapat menggunakan perintah bq query dengan flag --append_table untuk menambahkan hasil kueri ke tabel tujuan yang memiliki nilai default. Jika kueri menghilangkan kolom dengan nilai default, nilai default akan ditetapkan. Contoh berikut menambahkan data yang menentukan nilai untuk kolom z saja:

bq query \     --nouse_legacy_sql \     --append_table \     --destination_table=mydataset.mytable \     'SELECT FALSE AS z UNION ALL SELECT FALSE AS Z'

Tabel mytable menggunakan nilai default untuk kolom x dan y:

 +-----------------+---+-------+ |        x        | y |   z   | +-----------------+---+-------+ | 22:13:24.799555 | 5 |  NULL | | 22:18:29.890547 | 5 |  true | | 23:05:18.841683 | 5 | false | | 23:05:18.841683 | 5 | false | +-----------------+---+-------+ 

Memuat data

Anda dapat memuat data ke tabel dengan nilai default menggunakan perintah bq load atau pernyataan LOAD DATA. Nilai default diterapkan saat data yang dimuat memiliki lebih sedikit kolom daripada tabel tujuan. Nilai NULL dalam data yang dimuat tidak dikonversi ke nilai default.

Format biner, seperti AVRO, Parquet, atau ORC, memiliki skema file yang dienkode. Jika skema file menghilangkan beberapa kolom, nilai default akan diterapkan.

Format teks, seperti JSON dan CSV, tidak memiliki skema file yang dienkode. Untuk menentukan skema menggunakan alat command line bq, Anda dapat menggunakan flag --autodetect atau memberikan skema JSON. Untuk menentukan skema menggunakan pernyataan LOAD DATA, Anda harus memberikan daftar kolom. Berikut adalah contoh yang hanya memuat kolom a dari file CSV:

LOAD DATA INTO mydataset.insert_table (a) FROM FILES(   uris = ['gs://test-bucket/sample.csv'],   format = 'CSV');

Write API

Storage Write API hanya mengisi nilai default jika skema stream penulisan tidak memiliki kolom yang terdapat dalam skema tabel tujuan. Dalam hal ini, kolom yang tidak ada akan diisi dengan nilai default di kolom untuk setiap penulisan. Jika kolom ada dalam skema stream penulisan, tetapi tidak ada dalam data itu sendiri, kolom yang hilang akan diisi dengan NULL. Misalnya, Anda menulis data ke tabel BigQuery dengan skema berikut:

[   {     "name": "a",     "mode": "NULLABLE",     "type": "STRING",   },   {     "name": "b",     "mode": "NULLABLE",     "type": "STRING",     "defaultValueExpression": "'default_b'"   },   {     "name": "c",     "mode": "NULLABLE",     "type": "STRING",     "defaultValueExpression": "'default_c'"   } ]

Skema stream penulisan berikut tidak memiliki kolom c yang ada di tabel tujuan:

[   {     "name": "a",     "type": "STRING",   },   {     "name": "b",     "type": "STRING",   } ]

Misalnya Anda melakukan streaming nilai berikut ke tabel:

{'a': 'val_a', 'b': 'val_b'} {'a': 'val_a'}

Hasilnya adalah sebagai berikut:

 +-------+-------+-----------+ | a     | b     | c         | +-------+-------+-----------+ | val_a | val_b | default_c | | val_a | NULL  | default_c | +-------+-------+-----------+ 

Skema stream penulisan berisi kolom b, sehingga nilai default default_b tidak digunakan meskipun tidak ada nilai yang ditentukan untuk kolom tersebut. Karena skema stream penulisan tidak berisi kolom c, setiap baris di kolom c diisi dengan nilai default tabel tujuan default_c.

Skema stream penulisan berikut cocok dengan skema tabel yang Anda tuliskan:

[   {     "name": "a",     "type": "STRING",   },   {     "name": "b",     "type": "STRING",   }   {     "name": "c",     "type": "STRING",   } ]

Misalnya Anda melakukan streaming nilai berikut ke tabel:

{'a': 'val_a', 'b': 'val_b'} {'a': 'val_a'}

Skema stream penulisan tidak melewatkan kolom apa pun yang terdapat dalam tabel tujuan, sehingga tidak ada nilai default kolom yang diterapkan, terlepas dari apakah kolom diisi dalam data yang di-streaming:

 +-------+-------+------+ | a     | b     | c    | +-------+-------+------+ | val_a | val_b | NULL | | val_a | NULL  | NULL | +-------+-------+------+ 

Anda dapat menentukan setelan nilai default tingkat koneksi di default_missing_value_interpretation dalam pesan AppendRowsRequest. Jika nilai ditetapkan ke DEFAULT_VALUE, nilai yang tidak ada akan mengambil nilai default meskipun kolom ditampilkan dalam skema pengguna.

Anda juga dapat menentukan nilai default tingkat permintaan pada peta missing_value_interpretations dalam pesan AppendRowsRequest. Setiap kunci adalah nama kolom, dan nilainya menunjukkan cara menafsirkan nilai yang hilang.

Misalnya, peta {'col1': NULL_VALUE, 'col2': DEFAULT_VALUE} berarti semua nilai yang hilang di col1 ditafsirkan sebagai NULL, dan semua nilai yang hilang di col2 ditafsirkan sebagai nilai default yang ditetapkan untuk col2 dalam skema tabel.

Jika kolom tidak ada dalam peta ini dan memiliki nilai yang hilang, nilai yang hilang akan ditafsirkan sebagai NULL.

Kunci hanya dapat berupa nama kolom tingkat atas. Kunci tidak boleh berupa subkolom struct, seperti col1.subfield1.

Menggunakan metode API insertAll

Metode API tabledata.insertAll mengisi nilai default di tingkat baris saat data ditulis ke tabel. Jika baris tidak memiliki kolom dengan nilai default, nilai default akan diterapkan ke kolom tersebut.

Misalnya, Anda memiliki skema tabel berikut:

[   {     "name": "a",     "mode": "NULLABLE",     "type": "STRING",   },   {     "name": "b",     "mode": "NULLABLE",     "type": "STRING",     "defaultValueExpression": "'default_b'"   },   {     "name": "c",     "mode": "NULLABLE",     "type": "STRING",     "defaultValueExpression": "'default_c'"   } ]

Misalnya Anda melakukan streaming nilai berikut ke tabel:

{'a': 'val_a', 'b': 'val_b'} {'a': 'val_a'} {}

Hasilnya adalah sebagai berikut:

 +-------+------------+-----------+ | a     | b          | c         | +-------+------------+-----------+ | val_a | val_b      | default_c | | val_a | default_b  | default_c | | NULL  | default_b  | default_c | +-------+------------+-----------+ 

Baris pertama yang disisipkan tidak berisi nilai untuk kolom c, sehingga nilai default default_c ditulis ke kolom c. Baris kedua yang disisipkan tidak berisi nilai untuk kolom b atau c, sehingga nilai defaultnya ditulis ke kolom b dan c. Baris ketiga yang disisipkan tidak berisi nilai. Nilai yang ditulis ke kolom a adalah NULL karena tidak ada nilai default lain yang ditetapkan. Nilai default default_b dan default_c ditulis ke kolom b dan c.

Melihat nilai default

Guna melihat nilai default untuk kolom, buat kueri tampilan INFORMATION_SCHEMA.COLUMNS. Kolom column_default berisi nilai default untuk kolom. Jika tidak ada nilai default yang ditetapkan, nilai tersebut adalah NULL. Contoh berikut menunjukkan nama kolom dan nilai default untuk tabel mytable:

SELECT   column_name,   column_default FROM   mydataset.INFORMATION_SCHEMA.COLUMNS WHERE   table_name = 'mytable';

Hasilnya serupa dengan berikut ini:

 +-------------+----------------+ | column_name | column_default | +-------------+----------------+ | x           | CURRENT_TIME() | | y           | 5              | | z           | NULL           | +-------------+----------------+ 

Batasan

• Anda dapat membaca dari tabel dengan nilai default menggunakan Legacy SQL, tetapi Anda tidak dapat menulis ke tabel dengan nilai default menggunakan Legacy SQL.
• Anda tidak dapat menambahkan kolom baru dengan nilai default ke tabel yang ada. Namun, Anda dapat menambahkan kolom tanpa nilai default, lalu mengubah nilai defaultnya dengan menggunakan pernyataan DDL ALTER COLUMN SET DEFAULT.
• Anda tidak dapat menyalin dan menambahkan tabel sumber ke tabel tujuan yang memiliki lebih banyak kolom daripada tabel sumber, dan kolom tambahan memiliki nilai default. Sebagai gantinya, Anda dapat menjalankan INSERT destination_table SELECT * FROM source_table untuk menyalin data.

Langkah berikutnya

• Untuk informasi selengkapnya tentang pemuatan data ke BigQuery, lihat Pengantar pemuatan data.