Apache Hive SQL 翻譯指南

本文詳述 Apache Hive 和 BigQuery 間 SQL 語法的相似與相異之處，協助您規劃遷移作業。如要大量遷移 SQL 指令碼，請使用批次 SQL 翻譯。如要翻譯臨時查詢，請使用互動式 SQL 翻譯。

在某些情況下，Hive 和 BigQuery 中的 SQL 元素之間沒有直接對應關係。不過在大多數情況下，BigQuery 會提供 Hive 的替代元素，協助您達成相同功能，如本文件中的範例所示。

這份文件適用於企業架構師、資料庫管理員、應用程式開發人員和 IT 安全專家。並假設您已熟悉 Hive。

資料類型

Hive 和 BigQuery 的資料類型系統不同。在大部分情況下，您可以將 Hive 中的資料類型對應至 BigQuery 資料類型，但仍有少數例外狀況，例如 MAP 和 UNION。Hive 支援的隱含型別轉換比 BigQuery 多。因此，批次 SQL 翻譯器會插入許多明確的轉換。

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 (REPEAT 欄位)
UNION	STRUCT 不同的類型
-	GEOGRAPHY
-	JSON

查詢語法

本節說明 Hive 和 BigQuery 之間的查詢語法差異。

SELECT 陳述式

大多數 Hive SELECT 陳述式都與 BigQuery 相容。下表列出一些細微差異：

充電盒	Hive	BigQuery
子查詢	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 );
欄篩選	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 );
展開陣列	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;

FROM 子句

查詢中的 FROM 子句會列出要從中選取資料的資料表參照。在 Hive 中，可能的表格參照包括表格、檢視區塊和子查詢。BigQuery 也支援所有這些資料表參照。

您可以使用下列項目，在 FROM 子句中參照 BigQuery 資料表：

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

BigQuery 也支援其他資料表參照：

• 使用 FOR SYSTEM_TIME AS OF 存取資料表定義和資料列的歷史版本
• 欄位路徑，或解析為資料類型內欄位的任何路徑 (例如 STRUCT)
• 整併陣列

比較運算子

下表詳細說明如何將 Hive 運算子轉換為 BigQuery 運算子：

函式或運算子	Hive	BigQuery
- 一元負號 * 乘法 / 除法 + 加法 - 減法	所有數字類型	所有數字類型。 如要防止除法運算發生錯誤，請考慮使用 SAFE_DIVIDE 或 IEEE_DIVIDE。
~ 位元 NOT | 位元 OR & 位元 AND ^ 位元 XOR	布林資料類型	布林資料類型。
左移	shiftleft(TINYINT|SMALLINT|INT a, INT b) shiftleft(BIGINT a, INT b)	<< 整數或位元組 A << B，其中 B 必須與 A 的類型相同
右移	shiftright(TINYINT|SMALLINT|INT a, INT b) shiftright(BIGINT a, INT b)	>> 整數或位元組 A >> B，其中 B 必須與 A 的類型相同
模數 (餘數)	X % Y 所有數字類型	MOD(X, Y)
整數除法	A DIV B 和 A/B，以取得詳細的精確度資訊	所有數字類型。 注意：為避免除法運算期間發生錯誤，建議使用 SAFE_DIVIDE 或 IEEE_DIVIDE。
一元否定	!、NOT	NOT
支援等值比較的型別	所有原始類型	所有可比較類型和 STRUCT。
	a <=> b	不支援。翻譯成下列語言： (a = b AND b IS NOT NULL OR a IS NULL)
	a <> b	不支援。翻譯成下列語言： NOT (a = b AND b IS NOT NULL OR a IS NULL)
關係運算子 ( =, ==, !=, <, >, >= )	所有原始型別	所有可比較類型。
字串比較	RLIKE、REGEXP	REGEXP_CONTAINS 內建函式。使用 BigQuery 字串函式的 regex 語法，設定規則運算式模式。
[NOT] LIKE, [NOT] BETWEEN, IS [NOT] NULL	A [NOT] BETWEEN B AND C, A IS [NOT] (TRUE|FALSE), A [NOT] LIKE B	與 Hive 相同。此外，BigQuery 也支援 IN 運算子。

JOIN 條件

Hive 和 BigQuery 都支援下列類型的聯結：

• [INNER] JOIN

• LEFT [OUTER] JOIN

• RIGHT [OUTER] JOIN

• FULL [OUTER] JOIN

• CROSS JOIN，以及等效的隱含逗號交叉聯結

詳情請參閱「彙整作業」和「Hive 彙整」。

類型轉換和轉型

下表詳細說明如何將 Hive 函式轉換為 BigQuery 函式：

函式或運算子	Hive	BigQuery
型別轉換	如果轉換失敗，系統會傳回 `NULL`。	語法與 Hive 相同。如要進一步瞭解 BigQuery 類型轉換規則，請參閱轉換規則。 如果投放失敗，系統會顯示錯誤。如要取得與 Hive 相同的行為，請改用 SAFE_CAST。
SAFE 個函式呼叫		如果您在函式呼叫前面加上 SAFE，函式會傳回 NULL，而不是回報失敗。舉例來說，SAFE.SUBSTR('foo', 0, -2) AS safe_output; 會傳回 NULL。 注意：如要安全地投放內容，且沒有發生錯誤，請使用 SAFE_CAST。

隱含轉換類型

遷移至 BigQuery 時，您需要將大部分的 Hive 隱含轉換轉換為 BigQuery 顯式轉換，但下列資料類型除外，因為 BigQuery 會隱含轉換這些類型。

從 BigQuery 類型	BigQuery 類型
INT64	FLOAT64、NUMERIC、BIGNUMERIC
BIGNUMERIC	FLOAT64
NUMERIC	BIGNUMERIC、FLOAT64

BigQuery 也會對下列常值執行隱含轉換：

從 BigQuery 類型	BigQuery 類型
STRING 字面值 (例如 "2008-12-25")	DATE
STRING 字面值 (例如 "2008-12-25 15:30:00")	TIMESTAMP
STRING 字面值 (例如 "2008-12-25T07:30:00")	DATETIME
STRING 字面值 (例如 "15:30:00")	TIME

明確轉換類型

如要轉換 BigQuery 不會隱含轉換的 Hive 資料類型，請使用 BigQuery CAST(expression AS type) 函式。

函式

本節將說明 Hive 和 BigQuery 中常用的函式。

匯總函式

下表列出常見的 Hive 匯總、統計匯總和近似匯總函式，以及對應的 BigQuery 函式：

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 不支援 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

分析函式

下表列出常見 Hive 分析函式與對應的 BigQuery 函式：

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	可以使用使用者定義函式 (UDF)。

日期和時間函式

下表顯示常見的 Hive 日期和時間函式，以及對應的 BigQuery 函式：

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，因為這個值不含時區，且與 Hive 中的 CURRENT_TIMESTAMP\ CURRENT_TIMESTAMP 意義相同。
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 提供下列其他日期和時間函式：

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

字串函式

下表列出 Hive 字串函式及其對應的 BigQuery 函式：

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 提供下列其他字串函式：

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

數學函式

下表列出 Hive 數學函式及其對應的 BigQuery 函式：

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	使用 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 提供下列其他數學函式：

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

邏輯和條件函式

下表顯示 Hive 邏輯和條件函式與 BigQuery 對應函式之間的對應關係：

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 和 UDAF

Apache Hive 支援以 Java 撰寫使用者定義函式 (UDF)。您可以將 UDF 載入 Hive，以便在一般查詢中使用。BigQuery UDF 必須以 GoogleSQL 或 JavaScript 編寫。建議將 Hive UDF 轉換為 SQL UDF，因為 SQL UDF 的效能較佳。如需使用 JavaScript，請參閱「JavaScript UDF 的最佳做法」。對於其他語言，BigQuery 支援遠端函式，可讓您從 GoogleSQL 查詢中，叫用 Cloud Run 函式或 Cloud Run 中的函式。

BigQuery 不支援使用者定義的匯總函式 (UDAF)。

DML 語法

本節說明 Hive 和 BigQuery 之間資料操縱語言 (DML) 語法的差異。

INSERT 陳述式

大多數 Hive INSERT 陳述式都與 BigQuery 相容。下表列出例外狀況：

Hive	BigQuery
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]	INSERT INTO table (...) VALUES (...); 注意：在 BigQuery 中，只有在目標資料表的所有資料欄值都包含在 INSERT 陳述式中，且根據序數位置遞增排序時，才能省略資料欄名稱。
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 不支援插入覆寫作業。這個 Hive 語法可以遷移至 TRUNCATE 和 INSERT 陳述式。

BigQuery 會設定 DML 配額，限制您每天可執行的 DML 陳述式數量。如要充分運用配額，請考慮下列方法：

• 在單一 INSERT 陳述式中合併多個資料列，而不是為每個 INSERT 作業合併一個資料列。

• 使用 MERGE 陳述式合併多個 DML 陳述式 (包括 INSERT)。

• 使用 CREATE TABLE ... AS SELECT 建立並填入新資料表。

UPDATE 陳述式

大多數 Hive UPDATE 陳述式都與 BigQuery 相容。下表列出例外狀況：

Hive	BigQuery
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]	UPDATE table SET column = expression [,...] [FROM ...] WHERE TRUE 注意：BigQuery 中的所有 UPDATE 陳述式都必須包含 WHERE 關鍵字，後接條件。

DELETE 和 TRUNCATE 陳述式

您可以使用 DELETE 或 TRUNCATE 陳述式從資料表移除資料列，不會影響資料表結構定義或索引。

在 BigQuery 中，DELETE 陳述式必須有 WHERE 子句。 如要進一步瞭解 BigQuery 中的 DELETE，請參閱DELETE 範例。

Hive	BigQuery
DELETE FROM tablename [WHERE expression]	DELETE FROM table_name WHERE TRUE BigQuery DELETE 陳述式必須包含 WHERE 子句。
TRUNCATE [TABLE] table_name [PARTITION partition_spec];	TRUNCATE TABLE [[project_name.]dataset_name.]table_name

MERGE 陳述式

MERGE 陳述式可將 INSERT、UPDATE 和 DELETE 作業合併成單一 upsert 陳述式，並執行這些作業。每個目標資料列最多只能對應一個來源資料列的 MERGE 作業。

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, ... 注意：您必須列出所有需要更新的資料欄。

ALTER 陳述式

下表詳細說明如何將 Hive 的 CREATE VIEW 陳述式轉換為 BigQuery：

功能	Hive	BigQuery
Rename table	ALTER TABLE table_name RENAME TO new_table_name;	不支援。解決方法是使用複製工作，並以您要的名稱做為目的地資料表，然後刪除舊資料表。 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, ... )	序列化和還原序列化作業由 BigQuery 服務管理，使用者無法設定。 如要瞭解如何讓 BigQuery 從 CSV、JSON、AVRO、PARQUET 或 ORC 檔案讀取資料，請參閱「建立 Cloud Storage 外部資料表」。 支援 CSV、JSON、AVRO 和 PARQUET 匯出格式。詳情請參閱「匯出格式與壓縮類型」。
Table storage properties	ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS;	不支援 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", ...] );	BigQuery 服務會管理儲存空間，以平衡查詢效能，因此無法設定。
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; 詳情請參閱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, ...)	不支援。載入資料時，如果資料分割欄有新值，系統會視需要新增其他資料分割。 詳情請參閱「管理分區資料表」。
Rename partition	ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;	不支援。
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;	不支援。
Recover partition	MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];	不支援。
Drop partition	ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...] [IGNORE PROTECTION] [PURGE];	支援下列方法： bq rm 'mydataset.table_name$partition_id' DELETE from table_name$partition_id WHERE 1=1 詳情請參閱「刪除磁碟分割區」。
(Un)Archive partition	ALTER TABLE table_name ARCHIVE PARTITION partition_spec; ALTER TABLE table_name UNARCHIVE PARTITION partition_spec;	不支援。
Table and partition file format	ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;	不支援。
Table and partition location	ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";	不支援。
Table and partition touch	ALTER TABLE table_name TOUCH [PARTITION partition_spec];	不支援。
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;	不支援。
Table and partition compact	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] COMPACT 'compaction_type'[AND WAIT] [WITH OVERWRITE TBLPROPERTIES ("property"="value" [, ...])];	不支援。
Table and artition concatenate	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] CONCATENATE;	不支援。
Table and partition columns	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] UPDATE COLUMNS;	不支援 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];	不支援。

DDL 語法

本節說明 Hive 和 BigQuery 之間資料定義語言 (DDL) 語法的差異。

CREATE TABLE 和 DROP TABLE 陳述式

下表詳細說明如何將 Hive 的 CREATE TABLE 陳述式轉換為 BigQuery：

類型	Hive	BigQuery
代管資料表	create table table_name ( id int, dtDontQuery string, name string )	CREATE TABLE `myproject`.mydataset.table_name ( id INT64, dtDontQuery STRING, name STRING )
分區資料表	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 如要依日期分區，請取消註解下列項目： PARTITION BY dt OPTIONS( description="Table Description", 如要依日期分區，請取消註解下列項目。如果資料表已分區，建議使用 require_partition。 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: CREATE TABLE 的 LIKE 形式可讓您完全複製現有的資料表定義。	CREATE TABLE empty_key_value_store LIKE key_value_store [TBLPROPERTIES (property_name=property_value, ...)];	不支援。
已排序的 Bucket 資料表 (在 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 )' 詳情請參閱「建立及使用叢集資料表」。
偏斜的資料表 (一或多個資料欄的值偏斜)	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];	不支援。
臨時資料表	CREATE TEMPORARY TABLE list_bucket_multiple ( col1 STRING, col2 int, col3 STRING);	您可以按照下列方式，使用到期時間達成此目的： 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")] )
交易資料表	CREATE TRANSACTIONAL TABLE transactional_table_test(key string, value string) PARTITIONED BY(ds string) STORED AS ORC;	BigQuery 中的所有資料表修改動作都符合 ACID (原子性、一致性、獨立性、持久性) 標準。
捨棄資料表	DROP TABLE [IF EXISTS] table_name [PURGE];	{DROP TABLE | DROP TABLE IF EXISTS} table_name
截斷資料表	TRUNCATE TABLE table_name [PARTITION partition_spec]; partition_spec: : (partition_column = partition_col_value, partition_column = partition_col_value, ...)	不支援。以下是可用的解決方法： 刪除資料表，然後使用相同結構定義重新建立。 如果截斷作業是指定資料表的常見用途，請將資料表的寫入處理方式設為 WRITE_TRUNCATE。 使用 CREATE OR REPLACE TABLE 陳述式。 使用 DELETE from table_name WHERE 1=1 陳述式。 注意：特定分割區也可能會遭到截斷。詳情請參閱「刪除磁碟分割區」。

CREATE EXTERNAL TABLE 和 DROP EXTERNAL TABLE 陳述式

如需 BigQuery 中的外部資料表支援，請參閱外部資料來源簡介。

CREATE VIEW 和 DROP VIEW 陳述式

下表詳細說明如何將 Hive 的 CREATE VIEW 陳述式轉換為 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

CREATE FUNCTION 和 DROP FUNCTION 陳述式

下表詳細說明如何將 Hive 的儲存程序轉換為 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;	不支援。
CREATE FUNCTION [db_name.]function_name AS class_name [USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];	這項功能目前為 Alpha 版，僅支援已加入許可清單的專案。 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;	不支援。

CREATE MACRO 和 DROP MACRO 陳述式

下表詳細說明如何將 Hive 中用於建立巨集的程序式 SQL 陳述式，轉換為 BigQuery 中含有變數宣告和指派的陳述式：

Hive	BigQuery
CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;	不支援。在某些情況下，這可以替換為 UDF。
DROP TEMPORARY MACRO [IF EXISTS] macro_name;	不支援。

錯誤代碼和訊息

Hive 錯誤代碼和 BigQuery 錯誤代碼不同。如果應用程式邏輯會擷取錯誤，請消除錯誤來源，因為 BigQuery 不會傳回相同的錯誤代碼。

在 BigQuery 中，通常會使用 INFORMATION_SCHEMA 檢視區塊或稽核記錄來檢查錯誤。

一致性保證和交易隔離

Hive 和 BigQuery 都支援 ACID 語意交易。在 Hive 3 中，交易預設為啟用。

ACID 語意

Hive 支援快照隔離。執行查詢時，系統會提供資料庫的一致性快照，查詢會使用這份快照，直到執行完畢為止。Hive 在資料列層級提供完整的 ACID 語意，讓一個應用程式新增資料列時，另一個應用程式可從相同分割區讀取資料，彼此不會相互干擾。

BigQuery 提供樂觀並行控制 (先提交者勝出) 和快照隔離，查詢會讀取查詢開始前最後提交的資料。這種做法可確保每列和變異，以及相同 DML 陳述式中的各列，都具有相同的一致性層級，同時避免死結。如果對同一資料表進行多項 DML 更新，BigQuery 會改用悲觀並行控制。載入工作可以獨立執行並附加資料表，但 BigQuery 不會提供明確的交易界線或工作階段。

交易

Hive 不支援多陳述式交易。不支援 BEGIN、COMMIT 和 ROLLBACK 陳述式。在 Hive 中，所有語言作業都會自動提交。

使用工作階段時，BigQuery 支援單一查詢內或多個查詢間的多重陳述式交易。多重陳述式交易可讓您執行變更作業，例如從一或多個資料表插入或刪除資料列，並提交或復原變更。詳情請參閱多重陳述式交易。