Panduan terjemahan Apache Hive SQL

Dokumen ini menjelaskan persamaan dan perbedaan sintaksis SQL antara Apache Hive dan BigQuery untuk membantu Anda merencanakan migrasi. Untuk memigrasikan skrip SQL secara massal, gunakan terjemahan SQL batch. Untuk menerjemahkan kueri ad hoc, gunakan terjemahan SQL interaktif.

Dalam beberapa kasus, tidak ada pemetaan langsung antara elemen SQL di Hive dan BigQuery. Namun, pada umumnya, BigQuery menawarkan elemen alternatif ke Hive untuk membantu Anda mencapai fungsi yang sama, seperti yang ditunjukkan dalam contoh dalam dokumen ini.

Audiens yang dituju untuk dokumen ini adalah arsitek perusahaan, administrator database, developer aplikasi, dan spesialis keamanan IT. Anda dianggap familier dengan Hive.

Jenis data

Hive dan BigQuery memiliki sistem jenis data yang berbeda. Pada umumnya, Anda dapat memetakan jenis data di Hive ke jenis data BigQuery dengan beberapa pengecualian, seperti MAP dan UNION. Hive mendukung casting jenis yang lebih implisit daripada BigQuery. Akibatnya, penerjemah SQL batch menyisipkan banyak casting eksplisit.

Hive	BigQuery
TINYINT	INT64
SMALLINT	INT64
INT	INT64
BIGINT	INT64
DECIMAL	NUMERIC
FLOAT	FLOAT64
DOUBLE	FLOAT64
BOOLEAN	BOOL
STRING	STRING
VARCHAR	STRING
CHAR	STRING
BINARY	BYTES
DATE	DATE
-	DATETIME
-	TIME
TIMESTAMP	DATETIME/TIMESTAMP
INTERVAL	-
ARRAY	ARRAY
STRUCT	STRUCT
MAPS	STRUCT dengan nilai kunci (kolom REPEAT)
UNION	STRUCT dengan berbagai jenis
-	GEOGRAPHY
-	JSON

Sintaksis kueri

Bagian ini membahas perbedaan sintaksis kueri antara Hive dan BigQuery.

Pernyataan SELECT

Sebagian besar pernyataan Hive SELECT kompatibel dengan BigQuery. Tabel berikut berisi daftar perbedaan kecil:

Kasus	Hive	BigQuery
Subkueri	SELECT * FROM ( SELECT 10 as col1, "test" as col2, "test" as col3 ) tmp_table;	SELECT * FROM ( SELECT 10 as col1, "test" as col2, "test" as col3 );
Pemfilteran kolom	SET hive.support.quoted.identifiers=none; SELECT `(col2|col3)?+.+` FROM ( SELECT 10 as col1, "test" as col2, "test" as col3 ) tmp_table;	SELECT * EXCEPT(col2,col3) FROM ( SELECT 10 as col1, "test" as col2, "test" as col3 );
Melakukan exploding array	SELECT tmp_table.pageid, adid FROM ( SELECT 'test_value' pageid, Array(1,2,3) ad_id) tmp_table LATERAL VIEW explode(tmp_table.ad_id) adTable AS adid;	SELECT tmp_table.pageid, ad_id FROM ( SELECT 'test_value' pageid, [1,2,3] ad_id) tmp_table, UNNEST(tmp_table.ad_id) ad_id;

Klausa FROM

Klausa FROM dalam kueri mencantumkan referensi tabel tempat data dipilih. Dalam Hive, referensi tabel yang mungkin dipakai meliputi tabel, tampilan, dan subkueri. BigQuery juga mendukung semua referensi tabel ini.

Anda dapat mereferensikan tabel BigQuery dalam klausa FROM menggunakan hal berikut:

• [project_id].[dataset_id].[table_name]
• [dataset_id].[table_name]
• [table_name]

BigQuery juga mendukung referensi tabel tambahan:

• Versi historis definisi dan baris tabel yang menggunakan FOR SYSTEM_TIME AS OF
• Jalur kolom, atau jalur apa pun yang me-resolve ke kolom dalam jenis data (seperti STRUCT)
• Array yang diratakan

Operator perbandingan

Tabel berikut berisi detail cara mengonversi operator dari Hive ke BigQuery:

Fungsi atau operator	Hive	BigQuery
- Minus unary * Perkalian / Pembagian + Penambahan - Pengurangan	Semua jenis angka	Semua jenis angka. Untuk mencegah error selama operasi pembagian, pertimbangkan untuk menggunakan SAFE_DIVIDE atau IEEE_DIVIDE.
~ Bitwise not | Bitwise OR & Bitwise AND ^ Bitwise XOR	Jenis data Boolean	Jenis data Boolean.
Shift kiri	shiftleft(TINYINT|SMALLINT|INT a, INT b) shiftleft(BIGINT a, INT b)	<< Bilangan bulat atau byte A << B, dengan B harus berjenis sama dengan A
Shift kanan	shiftright(TINYINT|SMALLINT|INT a, INT b) shiftright(BIGINT a, INT b)	>> Bilangan bulat atau byte A >> B, dengan B harus berjenis sama dengan A
Modulus (sisa)	X % Y Semua jenis angka	MOD(X, Y)
Pembagian bilangan bulat	A DIV B dan A/B untuk presisi mendetail	Semua jenis angka. Catatan: Untuk mencegah error selama operasi pembagian, sebaiknya gunakan SAFE_DIVIDE atau IEEE_DIVIDE.
Negasi unary	!, NOT	NOT
Jenis yang mendukung perbandingan kesetaraan	Semua jenis primitif	Semua jenis yang sebanding dan STRUCT.
	a <=> b	Tidak didukung. Menerjemahkan ke bahasa berikut: (a = b AND b IS NOT NULL OR a IS NULL)
	a <> b	Tidak didukung. Menerjemahkan ke bahasa berikut: NOT (a = b AND b IS NOT NULL OR a IS NULL)
Operator relasional ( =, ==, !=, <, >, >= )	Semua jenis primitif	Semua jenis yang sebanding.
Perbandingan string	RLIKE, REGEXP	Fungsi bawaan REGEXP_CONTAINS. Menggunakan sintaksis ekspresi reguler untuk fungsi string BigQuery untuk pola ekspresi reguler.
[NOT] LIKE, [NOT] BETWEEN, IS [NOT] NULL	A [NOT] BETWEEN B AND C, A IS [NOT] (TRUE|FALSE), A [NOT] LIKE B	Sama seperti Hive. Selain itu, BigQuery juga mendukung operator IN.

Kondisi JOIN

Hive dan BigQuery mendukung jenis gabungan berikut:

• [INNER] JOIN

• LEFT [OUTER] JOIN

• RIGHT [OUTER] JOIN

• FULL [OUTER] JOIN

• CROSS JOIN dan cross join koma implisit yang setara

Untuk mengetahui informasi selengkapnya, lihat Operasi penggabungan dan Penggabungan Hive.

Konversi jenis dan casting

Tabel berikut berisi detail cara mengonversi fungsi dari Hive ke BigQuery:

Fungsi atau operator	Hive	BigQuery
Casting jenis	Jika casting gagal, `NULL` akan ditampilkan.	Sintaksis yang sama dengan Hive. Untuk mengetahui informasi selengkapnya tentang aturan konversi jenis BigQuery, lihat Aturan konversi. Jika casting gagal, Anda akan melihat error. Untuk memiliki perilaku yang sama seperti Hive, gunakan SAFE_CAST.
Panggilan fungsi SAFE		Jika Anda mengawali panggilan fungsi dengan SAFE, fungsi tersebut akan menampilkan NULL, bukan melaporkan kegagalan. Misalnya, SAFE.SUBSTR('foo', 0, -2) AS safe_output; akan menampilkan NULL. Catatan: Saat melakukan casting dengan aman tanpa error, gunakan SAFE_CAST.

Jenis konversi implisit

Saat bermigrasi ke BigQuery, Anda harus mengonversi sebagian besar konversi implisit Hive menjadi konversi eksplisit BigQuery, kecuali untuk jenis data berikut, yang dikonversi secara implisit oleh BigQuery.

Dari jenis BigQuery	Ke jenis BigQuery
INT64	FLOAT64, NUMERIC, BIGNUMERIC
BIGNUMERIC	FLOAT64
NUMERIC	BIGNUMERIC, FLOAT64

BigQuery juga melakukan konversi implisit untuk literal berikut:

Dari jenis BigQuery	Ke jenis BigQuery
Literal STRING (misalnya "2008-12-25")	DATE
Literal STRING (misalnya "2008-12-25 15:30:00")	TIMESTAMP
Literal STRING (misalnya "2008-12-25T07:30:00")	DATETIME
Literal STRING (misalnya "15:30:00")	TIME

Jenis konversi eksplisit

Jika Anda ingin mengonversi jenis data Hive yang tidak dikonversi secara implisit oleh BigQuery, gunakan fungsi CAST(expression AS type) BigQuery atau salah satu dari fungsi konversi DATE dan TIMESTAMP.

Functions

Bagian ini membahas fungsi umum yang digunakan di Hive dan BigQuery.

Fungsi agregat

Tabel berikut menampilkan pemetaan antara agregat Hive umum, agregat statistik, dan fungsi agregat perkiraan dengan BigQuery yang setara:

Hive	BigQuery
count(DISTINCT expr[, expr...])	count(DISTINCT expr[, expr...])
percentile_approx(DOUBLE col, array(p1 [, p2]...) [, B]) WITHIN GROUP (ORDER BY expression)	APPROX_QUANTILES(expression, 100)[OFFSET(CAST(TRUNC(percentile * 100) as INT64))] BigQuery tidak mendukung argumen lainnya yang ditentukan Hive.
AVG	AVG
X | Y	BIT_OR / X | Y
X ^ Y	BIT_XOR / X ^ Y
X & Y	BIT_AND / X & Y
COUNT	COUNT
COLLECT_SET(col), \ COLLECT_LIST(col)	ARRAY_AGG(col)
COUNT	COUNT
MAX	MAX
MIN	MIN
REGR_AVGX	AVG( IF(dep_var_expr is NULL OR ind_var_expr is NULL, NULL, ind_var_expr) )
REGR_AVGY	AVG( IF(dep_var_expr is NULL OR ind_var_expr is NULL, NULL, dep_var_expr) )
REGR_COUNT	SUM( IF(dep_var_expr is NULL OR ind_var_expr is NULL, NULL, 1) )
REGR_INTERCEPT	AVG(dep_var_expr) - AVG(ind_var_expr) * (COVAR_SAMP(ind_var_expr,dep_var_expr) / VARIANCE(ind_var_expr) )
REGR_R2	(COUNT(dep_var_expr) * SUM(ind_var_expr * dep_var_expr) - SUM(dep_var_expr) * SUM(ind_var_expr)) / SQRT( (COUNT(ind_var_expr) * SUM(POWER(ind_var_expr, 2)) * POWER(SUM(ind_var_expr),2)) * (COUNT(dep_var_expr) * SUM(POWER(dep_var_expr, 2)) * POWER(SUM(dep_var_expr), 2)))
REGR_SLOPE	COVAR_SAMP(ind_var_expr, dep_var_expr) / VARIANCE(ind_var_expr)
REGR_SXX	SUM(POWER(ind_var_expr, 2)) - COUNT(ind_var_expr) * POWER(AVG(ind_var_expr),2)
REGR_SXY	SUM(ind_var_expr*dep_var_expr) - COUNT(ind_var_expr) * AVG(ind) * AVG(dep_var_expr)
REGR_SYY	SUM(POWER(dep_var_expr, 2)) - COUNT(dep_var_expr) * POWER(AVG(dep_var_expr),2)
ROLLUP	ROLLUP
STDDEV_POP	STDDEV_POP
STDDEV_SAMP	STDDEV_SAMP, STDDEV
SUM	SUM
VAR_POP	VAR_POP
VAR_SAMP	VAR_SAMP, VARIANCE
CONCAT_WS	STRING_AGG

Fungsi analisis

Tabel berikut menunjukkan pemetaan antara fungsi analisis Hive umum dengan BigQuery yang setara:

Hive	BigQuery
AVG	AVG
COUNT	COUNT
COVAR_POP	COVAR_POP
COVAR_SAMP	COVAR_SAMP
CUME_DIST	CUME_DIST
DENSE_RANK	DENSE_RANK
FIRST_VALUE	FIRST_VALUE
LAST_VALUE	LAST_VALUE
LAG	LAG
LEAD	LEAD
COLLECT_LIST, \ COLLECT_SET	ARRAY_AGG ARRAY_CONCAT_AGG
MAX	MAX
MIN	MIN
NTILE	NTILE(constant_integer_expression)
PERCENT_RANK	PERCENT_RANK
RANK ()	RANK
ROW_NUMBER	ROW_NUMBER
STDDEV_POP	STDDEV_POP
STDDEV_SAMP	STDDEV_SAMP, STDDEV
SUM	SUM
VAR_POP	VAR_POP
VAR_SAMP	VAR_SAMP, VARIANCE
VARIANCE	VARIANCE ()
WIDTH_BUCKET	Fungsi yang ditentukan pengguna (UDF) dapat digunakan.

Fungsi tanggal dan waktu

Tabel berikut menunjukkan pemetaan antara fungsi tanggal dan waktu Hive umum serta BigQuery yang setara:

DATE_ADD	DATE_ADD(date_expression, INTERVAL int64_expression date_part)
DATE_SUB	DATE_SUB(date_expression, INTERVAL int64_expression date_part)
CURRENT_DATE	CURRENT_DATE
CURRENT_TIME	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_DATETIME direkomendasikan, karena nilai ini bebas zona waktu dan identik dengan CURRENT_TIMESTAMP \ CURRENT_TIMESTAMP di Hive.
EXTRACT(field FROM source)	EXTRACT(part FROM datetime_expression)
LAST_DAY	DATE_SUB( DATE_TRUNC( DATE_ADD( date_expression, INTERVAL 1 MONTH ), MONTH ), INTERVAL 1 DAY)
MONTHS_BETWEEN	DATE_DIFF(date_expression, date_expression, MONTH)
NEXT_DAY	DATE_ADD( DATE_TRUNC( date_expression, WEEK(day_value) ), INTERVAL 1 WEEK )
TO_DATE	PARSE_DATE
FROM_UNIXTIME	UNIX_SECONDS
FROM_UNIXTIMESTAMP	FORMAT_TIMESTAMP
YEAR \ QUARTER \ MONTH \ HOUR \ MINUTE \ SECOND \ WEEKOFYEAR	EXTRACT
DATEDIFF	DATE_DIFF

BigQuery menawarkan fungsi tanggal dan waktu tambahan berikut:

CURRENT_DATETIME DATE_FROM_UNIX_DATE DATE_TRUNC DATETIME DATETIME_TRUNC FORMAT_DATE FORMAT_DATETIME FORMAT_TIME

FORMAT_TIMESTAMP PARSE_DATETIME PARSE_TIME STRING TIME TIME_ADD TIME_DIFF TIME_SUB TIME_TRUNC TIMESTAMP TIMESTAMP_ADD

TIMESTAMP_DIFF TIMESTAMP_MICROS TIMESTAMP_MILLIS TIMESTAMP_SECONDS TIMESTAMP_SUB TIMESTAMP_TRUNC UNIX_DATE UNIX_MICROS UNIX_MILLIS UNIX_SECONDS

Fungsi string

Tabel berikut menunjukkan pemetaan antara fungsi string Hive dan BigQuery yang setara:

Hive	BigQuery
ASCII	TO_CODE_POINTS(string_expr)[OFFSET(0)]
HEX	TO_HEX
LENGTH	CHAR_LENGTH
LENGTH	CHARACTER_LENGTH
CHR	CODE_POINTS_TO_STRING
CONCAT	CONCAT
LOWER	LOWER
LPAD	LPAD
LTRIM	LTRIM
REGEXP_EXTRACT	REGEXP_EXTRACT
REGEXP_REPLACE	REGEXP_REPLACE
REPLACE	REPLACE
REVERSE	REVERSE
RPAD	RPAD
RTRIM	RTRIM
SOUNDEX	SOUNDEX
SPLIT	SPLIT(instring, delimiter)[ORDINAL(tokennum)]
SUBSTR, \ SUBSTRING	SUBSTR
TRANSLATE	TRANSLATE
LTRIM	LTRIM
RTRIM	RTRIM
TRIM	TRIM
UPPER	UPPER

BigQuery menawarkan fungsi string tambahan berikut:

BYTE_LENGTH CODE_POINTS_TO_BYTES ENDS_WITH FROM_BASE32 FROM_BASE64 FROM_HEX NORMALIZE NORMALIZE_AND_CASEFOLD

REPEAT SAFE_CONVERT_BYTES_TO_STRING SPLIT STARTS_WITH STRPOS TO_BASE32 TO_BASE64 TO_CODE_POINTS

Fungsi matematika

Tabel berikut menunjukkan pemetaan antara fungsi matematika Hive dan BigQuery yang setara:

Hive	BigQuery
ABS	ABS
ACOS	ACOS
ASIN	ASIN
ATAN	ATAN
CEIL	CEIL
CEILING	CEILING
COS	COS
FLOOR	FLOOR
GREATEST	GREATEST
LEAST	LEAST
LN	LN
LNNVL	Gunakan dengan ISNULL.
LOG	LOG
MOD (% operator)	MOD
POWER	POWER, POW
RAND	RAND
ROUND	ROUND
SIGN	SIGN
SIN	SIN
SQRT	SQRT
HASH	FARM_FINGERPRINT, MD5, SHA1, SHA256, SHA512
STDDEV_POP	STDDEV_POP
STDDEV_SAMP	STDDEV_SAMP
TAN	TAN
TRUNC	TRUNC
NVL	IFNULL(expr, 0), COALESCE(exp, 0)

BigQuery menawarkan fungsi matematika tambahan berikut:

• DIV
• IEEE_DIVIDE
• IS_INF
• IS_NAN
• LOG10
• SAFE_DIVIDE

Fungsi logika dan bersyarat

Tabel berikut menunjukkan pemetaan antara fungsi logika dan kondisional Hive serta BigQuery yang setara:

Hive	BigQuery
CASE	CASE
COALESCE	COALESCE
NVL	IFNULL(expr, 0), COALESCE(exp, 0)
NULLIF	NULLIF
IF	IF(expr, true_result, else_result)
ISNULL	IS NULL
ISNOTNULL	IS NOT NULL
NULLIF	NULLIF

UDF dan UDAF

BigQuery mendukung UDF, tetapi tidak mendukung fungsi agregat yang ditentukan pengguna (UDAF).

Sintaksis DML

Bagian ini membahas perbedaan sintaksis bahasa pengolahan data (DML) antara Hive dan BigQuery.

Pernyataan INSERT

Sebagian besar pernyataan INSERT Hive kompatibel dengan BigQuery. Tabel berikut menunjukkan pengecualian:

Hive	BigQuery
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]	INSERT INTO table (...) VALUES (...); Catatan: Di BigQuery, menghilangkan nama kolom dalam pernyataan INSERT hanya berfungsi jika nilai untuk semua kolom dalam tabel target disertakan dalam urutan naik berdasarkan posisi ordinal-nya.
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0) SELECT ... FROM ...	BigQuery tidak mendukung operasi sisipkan-timpa. Sintaksis Hive ini dapat dimigrasikan ke pernyataan TRUNCATE dan INSERT.

BigQuery menerapkan kuota DML yang membatasi jumlah pernyataan DML yang dapat Anda jalankan setiap hari. Untuk memanfaatkan kuota sebaik mungkin, pertimbangkan pendekatan berikut:

• Gabungkan beberapa baris dalam satu pernyataan INSERT, bukan satu baris untuk setiap operasi INSERT.

• Gabungkan beberapa pernyataan DML (termasuk INSERT) menggunakan pernyataan MERGE.

• Gunakan CREATE TABLE ... AS SELECT untuk membuat dan mengisi tabel baru.

Pernyataan UPDATE

Sebagian besar pernyataan UPDATE Hive kompatibel dengan BigQuery. Tabel berikut menunjukkan pengecualian:

Hive	BigQuery
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]	UPDATE table SET column = expression [,...] [FROM ...] WHERE TRUE Catatan: Semua pernyataan UPDATE di BigQuery memerlukan kata kunci WHERE, yang diikuti dengan kondisi.

Pernyataan DELETE dan TRUNCATE

Anda dapat menggunakan pernyataan DELETE atau TRUNCATE untuk menghapus baris dari tabel tanpa memengaruhi skema tabel atau indeks.

Di BigQuery, pernyataan DELETE harus memiliki klausa WHERE. Untuk mengetahui informasi selengkapnya tentang DELETE di BigQuery, lihat contoh DELETE.

Hive	BigQuery
DELETE FROM tablename [WHERE expression]	DELETE FROM table_name WHERE TRUE Pernyataan DELETE BigQuery memerlukan sebuah klausa WHERE .
TRUNCATE [TABLE] table_name [PARTITION partition_spec];	TRUNCATE TABLE [[project_name.]dataset_name.]table_name

Pernyataan MERGE

Pernyataan MERGE dapat menggabungkan operasi INSERT, UPDATE, dan DELETE menjadi satu pernyataan upsert dan menjalankan operasi. Operasi MERGE harus cocok dengan maksimal satu baris sumber untuk setiap baris target.

Hive	BigQuery
MERGE INTO AS T USING AS S ON WHEN MATCHED [AND ] THEN UPDATE SET WHEN MATCHED [AND ] THEN DELETE WHEN NOT MATCHED [AND ] THEN INSERT VALUES	MERGE target USING source ON target.key = source.key WHEN MATCHED AND source.filter = 'filter_exp' THEN UPDATE SET target.col1 = source.col1, target.col2 = source.col2, ... Catatan: Anda harus mencantumkan semua kolom yang perlu diperbarui.

Pernyataan ALTER

Tabel berikut berisi detail tentang cara mengonversi pernyataan CREATE VIEW dari Hive ke BigQuery:

Fungsi	Hive	BigQuery
Rename table	ALTER TABLE table_name RENAME TO new_table_name;	Tidak didukung. Solusinya adalah menggunakan tugas penyalinan dengan nama yang Anda inginkan sebagai tabel tujuan, lalu menghapus yang lama. bq copy project.dataset.old_table project.dataset.new_table bq rm --table project.dataset.old_table
Table properties	ALTER TABLE table_name SET TBLPROPERTIES table_properties; table_properties: : (property_name = property_value, property_name = property_value, ... ) Table Comment: ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);	{ALTER TABLE | ALTER TABLE IF EXISTS} table_name SET OPTIONS(table_set_options_list)
SerDe properties (Serialize and deserialize)	ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties]; ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties; serde_properties: : (property_name = property_value, property_name = property_value, ... )	Serialisasi dan deserialisasi dikelola oleh layanan BigQuery dan tidak dapat dikonfigurasi oleh pengguna. Untuk mempelajari cara mengizinkan BigQuery membaca data dari file CSV, JSON, AVRO, PARQUET, atau ORC, lihat Membuat tabel eksternal Cloud Storage. Mendukung format ekspor CSV, JSON, AVRO, dan PARQUET. Untuk mengetahui informasi selengkapnya, lihat Format ekspor dan jenis kompresi.
Table storage properties	ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS;	Tidak didukung untuk pernyataan ALTER.
Skewed table	Skewed: ALTER TABLE table_name SKEWED BY (col_name1, col_name2, ...) ON ([(col_name1_value, col_name2_value, ...) [, (col_name1_value, col_name2_value), ...] [STORED AS DIRECTORIES]; Not Skewed: ALTER TABLE table_name NOT SKEWED; Not Stored as Directories: ALTER TABLE table_name NOT STORED AS DIRECTORIES; Skewed Location: ALTER TABLE table_name SET SKEWED LOCATION (col_name1="location1" [, col_name2="location2", ...] );	Penyeimbangan penyimpanan untuk kueri performa dikelola oleh layanan BigQuery dan tidak dapat dikonfigurasi.
Table constraints	ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column, ...) DISABLE NOVALIDATE; ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column, ...) REFERENCES table_name(column, ...) DISABLE NOVALIDATE RELY; ALTER TABLE table_name DROP CONSTRAINT constraint_name;	ALTER TABLE [[project_name.]dataset_name.]table_name ADD [CONSTRAINT [IF NOT EXISTS] [constraint_name]] constraint NOT ENFORCED; ALTER TABLE [[project_name.]dataset_name.]table_name ADD PRIMARY KEY(column_list) NOT ENFORCED; Untuk mengetahui informasi selengkapnya, lihat pernyataan ALTER TABLE ADD PRIMARY KEY.
Add partition	ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...]; partition_spec: : (partition_column = partition_col_value, partition_column = partition_col_value, ...)	Tidak didukung. Partisi tambahan ditambahkan sesuai kebutuhan saat data dengan nilai baru dalam kolom partisi dimuat. Untuk informasi selengkapnya, lihat Mengelola tabel berpartisi.
Rename partition	ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;	Tidak didukung.
Exchange partition	-- Move partition from table_name_1 to table_name_2 ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec) WITH TABLE table_name_1; -- multiple partitions ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec, partition_spec2, ...) WITH TABLE table_name_1;	Tidak didukung.
Recover partition	MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];	Tidak didukung.
Drop partition	ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...] [IGNORE PROTECTION] [PURGE];	Didukung menggunakan metode berikut: bq rm 'mydataset.table_name$partition_id' DELETE from table_name$partition_id WHERE 1=1 Untuk mengetahui informasi selengkapnya, lihat Menghapus partisi.
(Un)Archive partition	ALTER TABLE table_name ARCHIVE PARTITION partition_spec; ALTER TABLE table_name UNARCHIVE PARTITION partition_spec;	Tidak didukung.
Table and partition file format	ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;	Tidak didukung.
Table and partition location	ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";	Tidak didukung.
Table and partition touch	ALTER TABLE table_name TOUCH [PARTITION partition_spec];	Tidak didukung.
Table and partition protection	ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE NO_DROP [CASCADE]; ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE OFFLINE;	Tidak didukung.
Table and partition compact	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] COMPACT 'compaction_type'[AND WAIT] [WITH OVERWRITE TBLPROPERTIES ("property"="value" [, ...])];	Tidak didukung.
Table and artition concatenate	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] CONCATENATE;	Tidak didukung.
Table and partition columns	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] UPDATE COLUMNS;	Tidak didukung untuk pernyataan ALTER TABLE.
Column name, type, position, and comment	ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];	Tidak didukung.

Sintaksis DDL

Bagian ini membahas perbedaan sintaksis Bahasa Definisi Data (DDL) antara Hive dan BigQuery.

Pernyataan CREATE TABLE dan DROP TABLE

Tabel berikut berisi detail tentang cara mengonversi pernyataan CREATE TABLE dari Hive ke BigQuery:

Jenis	Hive	BigQuery
Tabel terkelola	create table table_name ( id int, dtDontQuery string, name string )	CREATE TABLE `myproject`.mydataset.table_name ( id INT64, dtDontQuery STRING, name STRING )
Tabel yang dipartisi	create table table_name ( id int, dt string, name string ) partitioned by (date string)	CREATE TABLE `myproject`.mydataset.table_name ( id INT64, dt DATE, name STRING ) PARTITION BY dt OPTIONS( partition_expiration_days=3, description="a table partitioned by date_col" )
Create table as select (CTAS)	CREATE TABLE new_key_value_store ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe" STORED AS RCFile AS SELECT (key % 1024) new_key, concat(key, value) key_value_pair, dt FROM key_value_store SORT BY new_key, key_value_pair;	CREATE TABLE `myproject`.mydataset.new_key_value_store Saat mempartisi berdasarkan tanggal, hapus tanda komentar pada perintah berikut: PARTITION BY dt OPTIONS( description="Table Description", Saat mempartisi berdasarkan tanggal, hapus tanda komentar pada perintah berikut. Sebaiknya gunakan require_partition saat tabel dipartisi. require_partition_filter=TRUE ) AS SELECT (key % 1024) new_key, concat(key, value) key_value_pair, dt FROM key_value_store SORT BY new_key, key_value_pair'
Create Table Like: Bentuk LIKE dari CREATE TABLE memungkinkan Anda menyalin definisi tabel yang ada dengan tepat.	CREATE TABLE empty_key_value_store LIKE key_value_store [TBLPROPERTIES (property_name=property_value, ...)];	Tidak didukung.
Tabel yang dikelompokkan dalam bucket (dikelompokkan dalam cluster dalam terminologi BigQuery)	CREATE TABLE page_view( viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User' ) COMMENT 'This is the page view table' PARTITIONED BY(dt STRING, country STRING) CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' COLLECTION ITEMS TERMINATED BY '\002' MAP KEYS TERMINATED BY '\003' STORED AS SEQUENCEFILE;	CREATE TABLE `myproject` mydataset.page_view ( viewTime INT, dt DATE, userId BIGINT, page_url STRING, referrer_url STRING, ip STRING OPTIONS (description="IP Address of the User") ) PARTITION BY dt CLUSTER BY userId OPTIONS ( partition_expiration_days=3, description="This is the page view table", require_partition_filter=TRUE )' Untuk mengetahui informasi selengkapnya, lihat Membuat dan menggunakan tabel yang dikelompokkan.
Tabel skewed (tabel dengan satu atau beberapa kolom memiliki nilai skewed)	CREATE TABLE list_bucket_multiple (col1 STRING, col2 int, col3 STRING) SKEWED BY (col1, col2) ON (('s1',1), ('s3',3), ('s13',13), ('s78',78)) [STORED AS DIRECTORIES];	Tidak didukung.
Tabel sementara	CREATE TEMPORARY TABLE list_bucket_multiple ( col1 STRING, col2 int, col3 STRING);	Anda dapat melakukannya menggunakan waktu habis masa berlaku sebagai berikut: CREATE TABLE mydataset.newtable ( col1 STRING OPTIONS(description="An optional INTEGER field"), col2 INT64, col3 STRING ) PARTITION BY DATE(_PARTITIONTIME) OPTIONS( expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC", partition_expiration_days=1, description="a table that expires in 2020, with each partition living for 24 hours", labels=[("org_unit", "development")] )
Tabel transaksional	CREATE TRANSACTIONAL TABLE transactional_table_test(key string, value string) PARTITIONED BY(ds string) STORED AS ORC;	Semua perubahan tabel di BigQuery mematuhi ACID (atomicity, consistency, isolation, durability).
Menghapus tabel	DROP TABLE [IF EXISTS] table_name [PURGE];	{DROP TABLE | DROP TABLE IF EXISTS} table_name
Memotong tabel	TRUNCATE TABLE table_name [PARTITION partition_spec]; partition_spec: : (partition_column = partition_col_value, partition_column = partition_col_value, ...)	Tidak didukung. Solusi berikut tersedia: Hapus dan buat tabel lagi dengan skema yang sama. Tetapkan disposisi tulis untuk tabel ke WRITE_TRUNCATE jika operasi pemotongan adalah kasus penggunaan umum untuk tabel tertentu. Gunakan pernyataan CREATE OR REPLACE TABLE. Gunakan pernyataan DELETE from table_name WHERE 1=1. Catatan: Partisi tertentu juga dapat dipotong. Untuk mengetahui informasi selengkapnya, lihat Menghapus partisi.

Pernyataan CREATE EXTERNAL TABLE dan DROP EXTERNAL TABLE

Untuk dukungan tabel eksternal di BigQuery, lihat Pengantar sumber data eksternal.

Pernyataan CREATE VIEW dan DROP VIEW

Tabel berikut berisi detail tentang cara mengonversi pernyataan CREATE VIEW dari Hive ke BigQuery:

Hive	BigQuery
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ] [COMMENT view_comment] [TBLPROPERTIES (property_name = property_value, ...)] AS SELECT ...;	{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW} view_name [OPTIONS(view_option_list)] AS query_expression
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name [DISABLE REWRITE] [COMMENT materialized_view_comment] [PARTITIONED ON (col_name, ...)] [ [ROW FORMAT row_format] [STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] ] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value, ...)] AS ;	CREATE MATERIALIZED VIEW [IF NOT EXISTS] \ [project_id].[dataset_id].materialized_view_name -- cannot disable rewrites in BigQuery [OPTIONS( [description="materialized_view_comment",] \ [other materialized_view_option_list] )] [PARTITION BY (col_name)] --same as source table

Pernyataan CREATE FUNCTION dan DROP FUNCTION

Tabel berikut memberikan detail tentang cara mengonversi prosedur tersimpan dari Hive ke BigQuery:

Hive	BigQuery
CREATE TEMPORARY FUNCTION function_name AS class_name;	CREATE { TEMPORARY | TEMP } FUNCTION function_name ([named_parameter[, ...]]) [RETURNS data_type] AS (sql_expression) named_parameter: param_name param_type
DROP TEMPORARY FUNCTION [IF EXISTS] function_name;	Tidak didukung.
CREATE FUNCTION [db_name.]function_name AS class_name [USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];	Didukung untuk project yang diizinkan sebagai fitur alfa. CREATE { FUNCTION | FUNCTION IF NOT EXISTS | OR REPLACE FUNCTION } function_name ([named_parameter[, ...]]) [RETURNS data_type] AS (expression); named_parameter: param_name param_type
DROP FUNCTION [IF EXISTS] function_name;	DROP FUNCTION [ IF EXISTS ] function_name
RELOAD FUNCTION;	Tidak didukung.

Pernyataan CREATE MACRO dan DROP MACRO

Tabel berikut memberikan detail tentang cara mengonversi pernyataan SQL prosedural yang digunakan dalam membuat makro dari Hive ke BigQuery dengan deklarasi dan penetapan variabel:

Hive	BigQuery
CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;	Tidak didukung. Dalam beberapa kasus, ini dapat diganti dengan UDF.
DROP TEMPORARY MACRO [IF EXISTS] macro_name;	Tidak didukung.

Kode dan pesan error

Kode error Hive dan Kode error BigQuery berbeda. Jika logika aplikasi Anda menemukan error, hilangkan sumber error karena BigQuery tidak menampilkan kode error yang sama.

Di BigQuery, menggunakan tampilan INFORMATION_SCHEMA atau logging audit untuk memeriksa error adalah hal yang umum.

Jaminan konsistensi dan transaction isolation

Hive dan BigQuery mendukung transaksi dengan semantik ACID. Transaksi diaktifkan secara default di Hive 3.

Semantik ACID

Hive mendukung snapshot isolation. Saat Anda menjalankan kueri, kueri tersebut akan diberi snapshot database yang konsisten, yang akan digunakan hingga akhir eksekusinya. Hive memberikan semantik ACID lengkap di tingkat baris, sehingga satu aplikasi dapat menambahkan baris saat aplikasi lain membaca dari partisi yang sama tanpa mengganggu satu sama lain.

BigQuery menyediakan kontrol konkurensi optimis (yang pertama commit akan menang) dengan snapshot isolation, di mana kueri membaca data terakhir yang di-commit sebelum kueri dimulai. Pendekatan ini menjamin tingkat konsistensi yang sama untuk setiap baris dan mutasi, dan di seluruh baris dalam pernyataan DML yang sama, sekaligus menghindari deadlock. Untuk beberapa update DML ke tabel yang sama, BigQuery akan beralih ke kontrol konkurensi pesimis. Tugas pemuatan dapat berjalan secara terpisah dan menambahkan tabel; namun, BigQuery tidak menyediakan batas transaksi atau sesi eksplisit.

Transaksi

Hive tidak mendukung transaksi multi-pernyataan. Ini tidak mendukung pernyataan BEGIN, COMMIT, dan ROLLBACK. Di Hive, semua operasi bahasa di-commit secara otomatis.

BigQuery mendukung transaksi multi-pernyataan di dalam satu kueri atau di beberapa kueri saat Anda menggunakan sesi. Transaksi multi-pernyataan memungkinkan Anda melakukan operasi mutasi, seperti menyisipkan atau menghapus baris dari satu atau beberapa tabel dan meng-commit atau me-roll back perubahan. Untuk informasi selengkapnya, lihat Transaksi multi-pernyataan.