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 字串函式規則運算式的規則運算式模式。
[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

BigQuery 支援UDF，但不支援使用者定義的匯總函式 (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 陳述式

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

下表詳細說明如何將 CREATE TABLE 陳述式從 Hive 轉換為 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, ...)];	不支援。
分割排序的資料表 (在 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 陳述式

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

下表詳細說明如何將用於建立巨集的程序 SQL 陳述式，從 Hive 轉換為使用變數宣告和指派的 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 支援單一查詢內的多個陳述式交易，或跨多個查詢的交易。多陳述式交易可讓您執行變異作業，例如在一個或多個資料表中插入或刪除資料列，並提交或回復變更。詳情請參閱「多語句交易」。