Apache Hive SQL 转换指南

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;

为防止在除法运算期间发生错误，请考虑使用 SAFE_DIVIDE 或 IEEE_DIVIDE。

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 的类型相同

所有数字类型

注意：为防止在除法运算期间发生错误，请考虑使用 SAFE_DIVIDE 或 IEEE_DIVIDE。

(a = b AND b IS NOT NULL OR a IS NULL)

NOT (a = b AND b IS NOT NULL OR a IS NULL)

与 Hive 的语法相同。如需详细了解 BigQuery 类型转换规则，请参阅转换规则。

如果类型转换失败，您会看到错误。如需与 Hive 具有相同的行为，请改用 SAFE_CAST。

注意：如果进行安全类型转换且没有错误，请使用 SAFE_CAST。

BigQuery 不支持 Hive 定义的其余参数。

IF(dep_var_expr is NULL

OR ind_var_expr is NULL,

NULL, ind_var_expr)

)

IF(dep_var_expr is NULL

OR ind_var_expr is NULL,

NULL, dep_var_expr)

)

IF(dep_var_expr is NULL

OR ind_var_expr is NULL,

NULL, 1)

)

- AVG(ind_var_expr)

* (COVAR_SAMP(ind_var_expr,dep_var_expr)

/ VARIANCE(ind_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)))

dep_var_expr)

/ VARIANCE(ind_var_expr)

), MONTH ), INTERVAL 1 DAY)

)

• 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

• 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

注意：在 BigQuery 中，只有目标表中所有列的值根据其顺序位置按升序添加时，才能在 INSERT 语句中省略列名称。

[ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)

SELECT ... FROM ...

SET column = expression [,...]

[FROM ...]

WHERE TRUE

注意：BigQuery 中的所有 UPDATE 语句都需要 WHERE 关键字，后跟一个条件。

BigQuery DELETE 语句需要 WHERE 子句。

WHEN MATCHED [AND ] THEN UPDATE SET

WHEN MATCHED [AND ] THEN DELETE

WHEN NOT MATCHED [AND ] THEN INSERT VALUES

ON target.key = source.key

WHEN MATCHED AND source.filter = 'filter_exp' THEN

UPDATE SET

target.col1 = source.col1,

target.col2 = source.col2,

...

注意：您必须列出需要更新的所有列。

bq copy project.dataset.old_table project.dataset.new_table

bq rm --table project.dataset.old_table

table_properties:

: (property_name = property_value, property_name = property_value, ... )

Table Comment: ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);

table_name

SET OPTIONS(table_set_options_list)

ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;

serde_properties:

: (property_name = property_value, property_name = property_value, ... )

如需了解如何让 BigQuery 从 CSV、JSON、AVRO、PARQUET 或 ORC 文件读取数据，请参阅创建 Cloud Storage 外部表。

支持 CSV、JSON、AVRO 和 PARQUET 导出格式。如需了解详情，请参阅导出格式和压缩类型。

[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", ...] );

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

如需了解详情，请参阅 ALTER TABLE ADD PRIMARY KEY 语句。

partition_spec:

: (partition_column = partition_col_value, partition_column = partition_col_value, ...)

如需了解详情，请参阅管理分区表。

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;

• bq rm 'mydataset.table_name$partition_id'
• DELETE from table_name$partition_id WHERE 1=1



如需了解详情，请参阅删除分区。

ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE OFFLINE;

[WITH OVERWRITE TBLPROPERTIES ("property"="value" [, ...])];

id int,

dtDontQuery string,

name string

)

)

id int,

dt string,

name string

)

partitioned by (date string)

)

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;

按日期分区时，请取消备注以下内容：

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 形式，您可以准确复制现有的表定义。

LIKE key_value_store [TBLPROPERTIES (property_name=property_value, ...)];

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;

)'

如需了解详情，请参阅创建和使用聚簇表。

SKEWED BY (col1, col2) ON (('s1',1), ('s3',3), ('s13',13), ('s78',78)) [STORED AS DIRECTORIES];

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")]

)

table_name

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 语句。

注意：您也可以截断特定分区。如需了解详情，请参阅删除分区。

[COMMENT view_comment]

[TBLPROPERTIES (property_name = property_value, ...)]

AS SELECT ...;

AS query_expression

[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

;

[PARTITION BY (col_name)] --same as source table

[RETURNS data_type]

AS (sql_expression)

named_parameter:

param_name param_type

[USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];

CREATE { FUNCTION | FUNCTION IF NOT EXISTS | OR REPLACE FUNCTION }

function_name ([named_parameter[, ...]])

[RETURNS data_type]

AS (expression);

named_parameter:

param_name param_type