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를 대신 사용하세요.

참고: 오류 없이 안전하게 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