Apache Hive SQL translation guide

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;

To prevent errors during the divide operation, consider using SAFE_DIVIDE or IEEE_DIVIDE.

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

<< Integer or bytes

A << B, where B must be same type as A

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

>> Integer or bytes

A >> B, where B must be same type as A

All number types

Note: To prevent errors during the divide operation, consider using SAFE_DIVIDE or 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)

Same syntax as Hive. For more information about BigQuery type conversion rules, see Conversion rules.

If cast fails, you see an error. To have the same behavior as Hive, use SAFE_CAST instead.

Note: When casting safely without errors, use SAFE_CAST.

BigQuery doesn't support the rest of the arguments that Hive defines.

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

Note: In BigQuery, omitting column names in the INSERT statement only works if values for all columns in the target table are included in ascending order based on their ordinal positions.

[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

Note: All UPDATE statements in BigQuery require a WHERE keyword, followed by a condition.

BigQuery DELETE statements require a WHERE clause.

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,

...

Note: You must list all columns that need to be updated.

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

To learn how to let BigQuery read data from CSV, JSON, AVRO, PARQUET, or ORC files, see Create Cloud Storage external tables.

Supports CSV, JSON, AVRO, and PARQUET export formats. For more information, see Export formats and compression types.

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

For more information, see ALTER TABLE ADD PRIMARY KEY statement.

partition_spec:

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

For more information, see Managing partitioned tables.

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



For more information, see Delete a partition.

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;

When partitioning by date, uncomment the following:

PARTITION BY dt

description="Table Description",

When partitioning by date, uncomment the following. It's recommended to use require_partition when the table is partitioned.

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'

The LIKE form of CREATE TABLE lets you copy an existing table definition exactly.

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;

)'

For more information, see Create and use clustered tables.

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

• Drop and create the table again with the same schema.
• Set write disposition for table to WRITE_TRUNCATE if the truncate operation is a common use case for the given table.
• Use the CREATE OR REPLACE TABLE statement.
• Use the DELETE from table_name WHERE 1=1 statement.

Note: Specific partitions can also be truncated. For more information, see Delete a partition.

[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