Panduan terjemahan 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;

Untuk mencegah error selama operasi pembagian, pertimbangkan untuk menggunakan SAFE_DIVIDE atau IEEE_DIVIDE.

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

<< Bilangan bulat atau byte

A << B, dengan B harus berjenis sama dengan A

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

>> Bilangan bulat atau byte

A >> B, dengan B harus berjenis sama dengan A

Semua jenis angka

Catatan: Untuk mencegah error selama operasi pembagian, sebaiknya gunakan SAFE_DIVIDE atau 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)

Sintaksis yang sama dengan Hive. Untuk mengetahui informasi selengkapnya tentang aturan konversi jenis BigQuery, lihat Aturan konversi.

Jika casting gagal, Anda akan melihat error. Untuk memiliki perilaku yang sama seperti Hive, gunakan SAFE_CAST.

Catatan: Saat melakukan casting dengan aman tanpa error, gunakan SAFE_CAST.

BigQuery tidak mendukung argumen lainnya yang ditentukan 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

Catatan: Di BigQuery, menghilangkan nama kolom dalam pernyataan INSERT hanya berfungsi jika nilai untuk semua kolom dalam tabel target disertakan dalam urutan naik berdasarkan posisi ordinal-nya.

[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

Catatan: Semua pernyataan UPDATE di BigQuery memerlukan kata kunci WHERE, yang diikuti dengan kondisi.

Pernyataan DELETE BigQuery memerlukan sebuah klausa 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,

...

Catatan: Anda harus mencantumkan semua kolom yang perlu diperbarui.

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, ... )

Untuk mempelajari cara mengizinkan BigQuery membaca data dari file CSV, JSON, AVRO, PARQUET, atau ORC, lihat Membuat tabel eksternal Cloud Storage.

Mendukung format ekspor CSV, JSON, AVRO, dan PARQUET. Untuk mengetahui informasi selengkapnya, lihat Format ekspor dan jenis kompresi.

[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;

Untuk mengetahui informasi selengkapnya, lihat pernyataan ALTER TABLE ADD PRIMARY KEY.

partition_spec:

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

Untuk informasi selengkapnya, lihat Mengelola tabel berpartisi.

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



Untuk mengetahui informasi selengkapnya, lihat Menghapus partisi.

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;

Saat mempartisi berdasarkan tanggal, hapus tanda komentar pada perintah berikut:

PARTITION BY dt

OPTIONS(

description="Table Description",

Saat mempartisi berdasarkan tanggal, hapus tanda komentar pada perintah berikut. Sebaiknya gunakan require_partition saat tabel dipartisi.

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'

Bentuk LIKE dari CREATE TABLE memungkinkan Anda menyalin definisi tabel yang ada dengan tepat.

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;

)'

Untuk mengetahui informasi selengkapnya, lihat Membuat dan menggunakan tabel yang dikelompokkan.

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, ...)

• Hapus dan buat tabel lagi dengan skema yang sama.
• Tetapkan disposisi tulis untuk tabel ke WRITE_TRUNCATE jika operasi pemotongan adalah kasus penggunaan umum untuk tabel tertentu.
• Gunakan pernyataan CREATE OR REPLACE TABLE.
• Gunakan pernyataan DELETE from table_name WHERE 1=1.

Catatan: Partisi tertentu juga dapat dipotong. Untuk mengetahui informasi selengkapnya, lihat Menghapus partisi.

[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