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（A と B は同じ型でなければなりません）

shiftright(TINYINT|SMALLINT|INT a, INT b)
shiftright(BIGINT a, INT b)

>>整数またはバイト

A >> B（A と B は同じ型でなければなりません）

すべての数値型

注: 除算中のエラーを防ぐには、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