Apache Hive SQL translation guide

This document details the similarities and differences in SQL syntax between Apache Hive and BigQuery to help you plan your migration. To migrate your SQL scripts in bulk, use batch SQL translation. To translate ad hoc queries, use interactive SQL translation.

In some cases, there's no direct mapping between a SQL element in Hive and BigQuery. However, in most cases, BigQuery offers an alternative element to Hive to help you achieve the same functionality, as shown in the examples in this document.

The intended audience for this document is enterprise architects, database administrators, application developers, and IT security specialists. It assumes that you're familiar with Hive.

Data types

Hive and BigQuery have different data type systems. In most cases, you can map data types in Hive to BigQuery data types with a few exceptions, such as MAP and UNION. Hive supports more implicit type casting than BigQuery. As a result, the batch SQL translator inserts many explicit casts.

Hive	BigQuery
TINYINT	INT64
SMALLINT	INT64
INT	INT64
BIGINT	INT64
DECIMAL	NUMERIC
FLOAT	FLOAT64
DOUBLE	FLOAT64
BOOLEAN	BOOL
STRING	STRING
VARCHAR	STRING
CHAR	STRING
BINARY	BYTES
DATE	DATE
-	DATETIME
-	TIME
TIMESTAMP	DATETIME/TIMESTAMP
INTERVAL	-
ARRAY	ARRAY
STRUCT	STRUCT
MAPS	STRUCT with key values (REPEAT field)
UNION	STRUCT with different types
-	GEOGRAPHY
-	JSON

Query syntax

This section addresses differences in query syntax between Hive and BigQuery.

SELECT statement

Most Hive SELECT statements are compatible with BigQuery. The following table contains a list of minor differences:

Case	Hive	BigQuery
Subquery	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 );
Column filtering	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 );
Exploding an array	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;

FROM clause

The FROM clause in a query lists the table references from which data is selected. In Hive, possible table references include tables, views, and subqueries. BigQuery also supports all these table references.

You can reference BigQuery tables in the FROM clause by using the following:

• [project_id].[dataset_id].[table_name]
• [dataset_id].[table_name]
• [table_name]

BigQuery also supports additional table references:

• Historical versions of the table definition and rows using FOR SYSTEM_TIME AS OF
• Field paths, or any path that resolves to a field within a data type (such as a STRUCT)
• Flattened arrays

Comparison operators

The following table provides details about converting operators from Hive to BigQuery:

Function or operator	Hive	BigQuery
- Unary minus * Multiplication / Division + Addition - Subtraction	All number types	All number types. To prevent errors during the divide operation, consider using SAFE_DIVIDE or IEEE_DIVIDE.
~ Bitwise not | Bitwise OR & Bitwise AND ^ Bitwise XOR	Boolean data type	Boolean data type.
Left shift	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
Right shift	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
Modulus (remainder)	X % Y All number types	MOD(X, Y)
Integer division	A DIV B and A/B for detailed precision	All number types. Note: To prevent errors during the divide operation, consider using SAFE_DIVIDE or IEEE_DIVIDE.
Unary negation	!, NOT	NOT
Types supporting equality comparisons	All primitive types	All comparable types and STRUCT.
	a <=> b	Not supported. Translate to the following: (a = b AND b IS NOT NULL OR a IS NULL)
	a <> b	Not supported. Translate to the following: NOT (a = b AND b IS NOT NULL OR a IS NULL)
Relational operators ( =, ==, !=, <, >, >= )	All primitive types	All comparable types.
String comparison	RLIKE, REGEXP	REGEXP_CONTAINS built-in function. Uses BigQuery regex syntax for string functions for the regular expression patterns.
[NOT] LIKE, [NOT] BETWEEN, IS [NOT] NULL	A [NOT] BETWEEN B AND C, A IS [NOT] (TRUE|FALSE), A [NOT] LIKE B	Same as Hive. In addition, BigQuery also supports the IN operator.

JOIN conditions

Both Hive and BigQuery support the following types of joins:

• [INNER] JOIN

• LEFT [OUTER] JOIN

• RIGHT [OUTER] JOIN

• FULL [OUTER] JOIN

• CROSS JOIN and the equivalent implicit comma cross join

For more information, see Join operation and Hive joins.

Type conversion and casting

The following table provides details about converting functions from Hive to BigQuery:

Function or operator	Hive	BigQuery
Type casting	When a cast fails, `NULL` is returned.	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.
SAFE function calls		If you prefix function calls with SAFE, the function returns NULL instead of reporting failure. For example, SAFE.SUBSTR('foo', 0, -2) AS safe_output; returns NULL. Note: When casting safely without errors, use SAFE_CAST.

Implicit conversion types

When migrating to BigQuery, you need to convert most of your Hive implicit conversions to BigQuery explicit conversions except for the following data types, which BigQuery implicitly converts.

From BigQuery type	To BigQuery type
INT64	FLOAT64, NUMERIC, BIGNUMERIC
BIGNUMERIC	FLOAT64
NUMERIC	BIGNUMERIC, FLOAT64

BigQuery also performs implicit conversions for the following literals:

From BigQuery type	To BigQuery type
STRING literal (for example, "2008-12-25")	DATE
STRING literal (for example, "2008-12-25 15:30:00")	TIMESTAMP
STRING literal (for example, "2008-12-25T07:30:00")	DATETIME
STRING literal (for example, "15:30:00")	TIME

Explicit conversion types

If you want to convert Hive data types that BigQuery doesn't implicitly convert, use the BigQuery CAST(expression AS type) function or either of the DATE and TIMESTAMP conversion functions.

Functions

This section covers common functions used in Hive and BigQuery.

Aggregate functions

The following table shows mappings between common Hive aggregate, statistical aggregate, and approximate aggregate functions with their BigQuery equivalents:

Hive	BigQuery
count(DISTINCT expr[, expr...])	count(DISTINCT expr[, expr...])
percentile_approx(DOUBLE col, array(p1 [, p2]...) [, B]) WITHIN GROUP (ORDER BY expression)	APPROX_QUANTILES(expression, 100)[OFFSET(CAST(TRUNC(percentile * 100) as INT64))] BigQuery doesn't support the rest of the arguments that Hive defines.
AVG	AVG
X | Y	BIT_OR / X | Y
X ^ Y	BIT_XOR / X ^ Y
X & Y	BIT_AND / X & Y
COUNT	COUNT
COLLECT_SET(col), \ COLLECT_LIST(col)	ARRAY_AGG(col)
COUNT	COUNT
MAX	MAX
MIN	MIN
REGR_AVGX	AVG( IF(dep_var_expr is NULL OR ind_var_expr is NULL, NULL, ind_var_expr) )
REGR_AVGY	AVG( IF(dep_var_expr is NULL OR ind_var_expr is NULL, NULL, dep_var_expr) )
REGR_COUNT	SUM( IF(dep_var_expr is NULL OR ind_var_expr is NULL, NULL, 1) )
REGR_INTERCEPT	AVG(dep_var_expr) - AVG(ind_var_expr) * (COVAR_SAMP(ind_var_expr,dep_var_expr) / VARIANCE(ind_var_expr) )
REGR_R2	(COUNT(dep_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)))
REGR_SLOPE	COVAR_SAMP(ind_var_expr, dep_var_expr) / VARIANCE(ind_var_expr)
REGR_SXX	SUM(POWER(ind_var_expr, 2)) - COUNT(ind_var_expr) * POWER(AVG(ind_var_expr),2)
REGR_SXY	SUM(ind_var_expr*dep_var_expr) - COUNT(ind_var_expr) * AVG(ind) * AVG(dep_var_expr)
REGR_SYY	SUM(POWER(dep_var_expr, 2)) - COUNT(dep_var_expr) * POWER(AVG(dep_var_expr),2)
ROLLUP	ROLLUP
STDDEV_POP	STDDEV_POP
STDDEV_SAMP	STDDEV_SAMP, STDDEV
SUM	SUM
VAR_POP	VAR_POP
VAR_SAMP	VAR_SAMP, VARIANCE
CONCAT_WS	STRING_AGG

Analytical functions

The following table shows mappings between common Hive analytical functions with their BigQuery equivalents:

Hive	BigQuery
AVG	AVG
COUNT	COUNT
COVAR_POP	COVAR_POP
COVAR_SAMP	COVAR_SAMP
CUME_DIST	CUME_DIST
DENSE_RANK	DENSE_RANK
FIRST_VALUE	FIRST_VALUE
LAST_VALUE	LAST_VALUE
LAG	LAG
LEAD	LEAD
COLLECT_LIST, \ COLLECT_SET	ARRAY_AGG ARRAY_CONCAT_AGG
MAX	MAX
MIN	MIN
NTILE	NTILE(constant_integer_expression)
PERCENT_RANK	PERCENT_RANK
RANK ()	RANK
ROW_NUMBER	ROW_NUMBER
STDDEV_POP	STDDEV_POP
STDDEV_SAMP	STDDEV_SAMP, STDDEV
SUM	SUM
VAR_POP	VAR_POP
VAR_SAMP	VAR_SAMP, VARIANCE
VARIANCE	VARIANCE ()
WIDTH_BUCKET	A user-defined function (UDF) can be used.

Date and time functions

The following table shows mappings between common Hive date and time functions and their BigQuery equivalents:

DATE_ADD	DATE_ADD(date_expression, INTERVAL int64_expression date_part)
DATE_SUB	DATE_SUB(date_expression, INTERVAL int64_expression date_part)
CURRENT_DATE	CURRENT_DATE
CURRENT_TIME	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_DATETIME is recommended, as this value is timezone-free and synonymous with CURRENT_TIMESTAMP \ CURRENT_TIMESTAMP in Hive.
EXTRACT(field FROM source)	EXTRACT(part FROM datetime_expression)
LAST_DAY	DATE_SUB( DATE_TRUNC( DATE_ADD( date_expression, INTERVAL 1 MONTH ), MONTH ), INTERVAL 1 DAY)
MONTHS_BETWEEN	DATE_DIFF(date_expression, date_expression, MONTH)
NEXT_DAY	DATE_ADD( DATE_TRUNC( date_expression, WEEK(day_value) ), INTERVAL 1 WEEK )
TO_DATE	PARSE_DATE
FROM_UNIXTIME	UNIX_SECONDS
FROM_UNIXTIMESTAMP	FORMAT_TIMESTAMP
YEAR \ QUARTER \ MONTH \ HOUR \ MINUTE \ SECOND \ WEEKOFYEAR	EXTRACT
DATEDIFF	DATE_DIFF

BigQuery offers the following additional date and time functions:

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

String functions

The following table shows mappings between Hive string functions and their BigQuery equivalents:

Hive	BigQuery
ASCII	TO_CODE_POINTS(string_expr)[OFFSET(0)]
HEX	TO_HEX
LENGTH	CHAR_LENGTH
LENGTH	CHARACTER_LENGTH
CHR	CODE_POINTS_TO_STRING
CONCAT	CONCAT
LOWER	LOWER
LPAD	LPAD
LTRIM	LTRIM
REGEXP_EXTRACT	REGEXP_EXTRACT
REGEXP_REPLACE	REGEXP_REPLACE
REPLACE	REPLACE
REVERSE	REVERSE
RPAD	RPAD
RTRIM	RTRIM
SOUNDEX	SOUNDEX
SPLIT	SPLIT(instring, delimiter)[ORDINAL(tokennum)]
SUBSTR, \ SUBSTRING	SUBSTR
TRANSLATE	TRANSLATE
LTRIM	LTRIM
RTRIM	RTRIM
TRIM	TRIM
UPPER	UPPER

BigQuery offers the following additional string functions:

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

Math functions

The following table shows mappings between Hive math functions and their BigQuery equivalents:

Hive	BigQuery
ABS	ABS
ACOS	ACOS
ASIN	ASIN
ATAN	ATAN
CEIL	CEIL
CEILING	CEILING
COS	COS
FLOOR	FLOOR
GREATEST	GREATEST
LEAST	LEAST
LN	LN
LNNVL	Use with ISNULL.
LOG	LOG
MOD (% operator)	MOD
POWER	POWER, POW
RAND	RAND
ROUND	ROUND
SIGN	SIGN
SIN	SIN
SQRT	SQRT
HASH	FARM_FINGERPRINT, MD5, SHA1, SHA256, SHA512
STDDEV_POP	STDDEV_POP
STDDEV_SAMP	STDDEV_SAMP
TAN	TAN
TRUNC	TRUNC
NVL	IFNULL(expr, 0), COALESCE(exp, 0)

BigQuery offers the following additional math functions:

• DIV
• IEEE_DIVIDE
• IS_INF
• IS_NAN
• LOG10
• SAFE_DIVIDE

Logical and conditional functions

The following table shows mappings between Hive logical and conditional functions and their BigQuery equivalents:

Hive	BigQuery
CASE	CASE
COALESCE	COALESCE
NVL	IFNULL(expr, 0), COALESCE(exp, 0)
NULLIF	NULLIF
IF	IF(expr, true_result, else_result)
ISNULL	IS NULL
ISNOTNULL	IS NOT NULL
NULLIF	NULLIF

UDFs and UDAFs

BigQuery supportsUDFs but not user-defined aggregate functions (UDAFs).

DML syntax

This section addresses differences in data manipulation language (DML) syntax between Hive and BigQuery.

INSERT statement

Most Hive INSERT statements are compatible with BigQuery. The following table shows exceptions:

Hive	BigQuery
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]	INSERT INTO table (...) VALUES (...); 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.
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0) SELECT ... FROM ...	BigQuery doesn't support the insert-overwrite operations. This Hive syntax can be migrated to TRUNCATE and INSERT statements.

BigQuery imposes DML quotas that restrict the number of DML statements that you can execute daily. To make the best use of your quota, consider the following approaches:

• Combine multiple rows in a single INSERT statement, instead of one row for each INSERT operation.

• Combine multiple DML statements (including INSERT) by using a MERGE statement.

• Use CREATE TABLE ... AS SELECT to create and populate new tables.

UPDATE statement

Most Hive UPDATE statements are compatible with BigQuery. The following table shows exceptions:

Hive	BigQuery
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]	UPDATE table SET column = expression [,...] [FROM ...] WHERE TRUE Note: All UPDATE statements in BigQuery require a WHERE keyword, followed by a condition.

DELETE and TRUNCATE statements

You can use DELETE or TRUNCATE statements to remove rows from a table without affecting the table schema or indexes.

In BigQuery, the DELETE statement must have a WHERE clause. For more information about DELETE in BigQuery, see DELETE examples.

Hive	BigQuery
DELETE FROM tablename [WHERE expression]	DELETE FROM table_name WHERE TRUE BigQuery DELETE statements require a WHERE clause.
TRUNCATE [TABLE] table_name [PARTITION partition_spec];	TRUNCATE TABLE [[project_name.]dataset_name.]table_name

MERGE statement

The MERGE statement can combine INSERT, UPDATE, and DELETE operations into a single upsert statement and perform the operations. The MERGE operation must match one source row at most for each target row.

Hive	BigQuery
MERGE INTO AS T USING AS S ON WHEN MATCHED [AND ] THEN UPDATE SET WHEN MATCHED [AND ] THEN DELETE WHEN NOT MATCHED [AND ] THEN INSERT VALUES	MERGE target USING source 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.

ALTER statement

The following table provides details about converting CREATE VIEW statements from Hive to BigQuery:

Function	Hive	BigQuery
Rename table	ALTER TABLE table_name RENAME TO new_table_name;	Not supported. A workaround is to use a copy job with the name that you want as the destination table, and then delete the old one. bq copy project.dataset.old_table project.dataset.new_table bq rm --table project.dataset.old_table
Table properties	ALTER TABLE table_name SET TBLPROPERTIES table_properties; table_properties: : (property_name = property_value, property_name = property_value, ... ) Table Comment: ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);	{ALTER TABLE | ALTER TABLE IF EXISTS} table_name SET OPTIONS(table_set_options_list)
SerDe properties (Serialize and deserialize)	ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties]; ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties; serde_properties: : (property_name = property_value, property_name = property_value, ... )	Serialization and deserialization is managed by the BigQuery service and isn't user configurable. 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.
Table storage properties	ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS;	Not supported for the ALTER statements.
Skewed table	Skewed: ALTER TABLE table_name SKEWED BY (col_name1, col_name2, ...) ON ([(col_name1_value, col_name2_value, ...) [, (col_name1_value, col_name2_value), ...] [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", ...] );	Balancing storage for performance queries is managed by the BigQuery service and isn't configurable.
Table constraints	ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column, ...) DISABLE NOVALIDATE; ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column, ...) REFERENCES table_name(column, ...) DISABLE NOVALIDATE RELY; ALTER TABLE table_name DROP CONSTRAINT constraint_name;	ALTER TABLE [[project_name.]dataset_name.]table_name ADD [CONSTRAINT [IF NOT EXISTS] [constraint_name]] constraint NOT ENFORCED; ALTER TABLE [[project_name.]dataset_name.]table_name ADD PRIMARY KEY(column_list) NOT ENFORCED; For more information, see ALTER TABLE ADD PRIMARY KEY statement.
Add partition	ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...]; partition_spec: : (partition_column = partition_col_value, partition_column = partition_col_value, ...)	Not supported. Additional partitions are added as needed when data with new values in the partition columns are loaded. For more information, see Managing partitioned tables.
Rename partition	ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;	Not supported.
Exchange partition	-- Move partition from table_name_1 to table_name_2 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;	Not supported.
Recover partition	MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];	Not supported.
Drop partition	ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...] [IGNORE PROTECTION] [PURGE];	Supported using the following methods: bq rm 'mydataset.table_name$partition_id' DELETE from table_name$partition_id WHERE 1=1 For more information, see Delete a partition.
(Un)Archive partition	ALTER TABLE table_name ARCHIVE PARTITION partition_spec; ALTER TABLE table_name UNARCHIVE PARTITION partition_spec;	Not supported.
Table and partition file format	ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;	Not supported.
Table and partition location	ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";	Not supported.
Table and partition touch	ALTER TABLE table_name TOUCH [PARTITION partition_spec];	Not supported.
Table and partition protection	ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE NO_DROP [CASCADE]; ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE OFFLINE;	Not supported.
Table and partition compact	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] COMPACT 'compaction_type'[AND WAIT] [WITH OVERWRITE TBLPROPERTIES ("property"="value" [, ...])];	Not supported.
Table and artition concatenate	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] CONCATENATE;	Not supported.
Table and partition columns	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] UPDATE COLUMNS;	Not supported for the ALTER TABLE statements.
Column name, type, position, and comment	ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];	Not supported.

DDL syntax

This section addresses differences in Data Definition Language (DDL) syntax between Hive and BigQuery.

CREATE TABLE and DROP TABLE statements

The following table provides details about converting CREATE TABLE statements from Hive to BigQuery:

Type	Hive	BigQuery
Managed tables	create table table_name ( id int, dtDontQuery string, name string )	CREATE TABLE `myproject`.mydataset.table_name ( id INT64, dtDontQuery STRING, name STRING )
Partitioned tables	create table table_name ( id int, dt string, name string ) partitioned by (date string)	CREATE TABLE `myproject`.mydataset.table_name ( id INT64, dt DATE, name STRING ) PARTITION BY dt OPTIONS( partition_expiration_days=3, description="a table partitioned by date_col" )
Create table as select (CTAS)	CREATE TABLE new_key_value_store 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;	CREATE TABLE `myproject`.mydataset.new_key_value_store When partitioning by date, uncomment the following: PARTITION BY dt OPTIONS( 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'
Create Table Like: The LIKE form of CREATE TABLE lets you copy an existing table definition exactly.	CREATE TABLE empty_key_value_store LIKE key_value_store [TBLPROPERTIES (property_name=property_value, ...)];	Not supported.
Bucketed sorted tables (clustered in BigQuery terminology)	CREATE TABLE page_view( 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;	CREATE TABLE `myproject` mydataset.page_view ( viewTime INT, dt DATE, userId BIGINT, page_url STRING, referrer_url STRING, ip STRING OPTIONS (description="IP Address of the User") ) PARTITION BY dt CLUSTER BY userId OPTIONS ( partition_expiration_days=3, description="This is the page view table", require_partition_filter=TRUE )' For more information, see Create and use clustered tables.
Skewed tables (tables where one or more columns have skewed values)	CREATE TABLE list_bucket_multiple (col1 STRING, col2 int, col3 STRING) SKEWED BY (col1, col2) ON (('s1',1), ('s3',3), ('s13',13), ('s78',78)) [STORED AS DIRECTORIES];	Not supported.
Temporary tables	CREATE TEMPORARY TABLE list_bucket_multiple ( col1 STRING, col2 int, col3 STRING);	You can achieve this using expiration time as follows: 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")] )
Transactional tables	CREATE TRANSACTIONAL TABLE transactional_table_test(key string, value string) PARTITIONED BY(ds string) STORED AS ORC;	All table modifications in BigQuery are ACID (atomicity, consistency, isolation, durability) compliant.
Drop table	DROP TABLE [IF EXISTS] table_name [PURGE];	{DROP TABLE | DROP TABLE IF EXISTS} table_name
Truncate table	TRUNCATE TABLE table_name [PARTITION partition_spec]; partition_spec: : (partition_column = partition_col_value, partition_column = partition_col_value, ...)	Not supported. The following workarounds are available: 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.

CREATE EXTERNAL TABLE and DROP EXTERNAL TABLE statements

For external table support in BigQuery, see Introduction to external data sources.

CREATE VIEW and DROP VIEW statements

The following table provides details about converting CREATE VIEW statements from Hive to BigQuery:

Hive	BigQuery
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ] [COMMENT view_comment] [TBLPROPERTIES (property_name = property_value, ...)] AS SELECT ...;	{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW} view_name [OPTIONS(view_option_list)] AS query_expression
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name [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 ;	CREATE MATERIALIZED VIEW [IF NOT EXISTS] \ [project_id].[dataset_id].materialized_view_name -- cannot disable rewrites in BigQuery [OPTIONS( [description="materialized_view_comment",] \ [other materialized_view_option_list] )] [PARTITION BY (col_name)] --same as source table

CREATE FUNCTION and DROP FUNCTION statements

The following table provides details about converting stored procedures from Hive to BigQuery:

Hive	BigQuery
CREATE TEMPORARY FUNCTION function_name AS class_name;	CREATE { TEMPORARY | TEMP } FUNCTION function_name ([named_parameter[, ...]]) [RETURNS data_type] AS (sql_expression) named_parameter: param_name param_type
DROP TEMPORARY FUNCTION [IF EXISTS] function_name;	Not supported.
CREATE FUNCTION [db_name.]function_name AS class_name [USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];	Supported for allowlisted projects as an alpha feature. CREATE { FUNCTION | FUNCTION IF NOT EXISTS | OR REPLACE FUNCTION } function_name ([named_parameter[, ...]]) [RETURNS data_type] AS (expression); named_parameter: param_name param_type
DROP FUNCTION [IF EXISTS] function_name;	DROP FUNCTION [ IF EXISTS ] function_name
RELOAD FUNCTION;	Not supported.

CREATE MACRO and DROP MACRO statements

The following table provides details about converting procedural SQL statements used in creating macro from Hive to BigQuery with variable declaration and assignment:

Hive	BigQuery
CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;	Not supported. In some cases, this can be substituted with a UDF.
DROP TEMPORARY MACRO [IF EXISTS] macro_name;	Not supported.

Error codes and messages

Hive error codes and BigQuery error codes are different. If your application logic is catching errors, eliminate the source of the error because BigQuery doesn't return the same error codes.

In BigQuery, it's common to use the INFORMATION_SCHEMA views or audit logging to examine errors.

Consistency guarantees and transaction isolation

Both Hive and BigQuery support transactions with ACID semantics. Transactions are enabled by default in Hive 3.

ACID semantics

Hive supports snapshot isolation. When you execute a query, the query is provided with a consistent snapshot of the database, which it uses until the end of its execution. Hive provides full ACID semantics at the row level, letting one application add rows when another application reads from the same partition without interfering with each other.

BigQuery provides optimistic concurrency control (first to commit wins) with snapshot isolation, in which a query reads the last committed data before the query starts. This approach guarantees the same level of consistency for each row and mutation, and across rows within the same DML statement, while avoiding deadlocks. For multiple DML updates to the same table, BigQuery switches to pessimistic concurrency control. Load jobs can run independently and append tables; however, BigQuery doesn't provide an explicit transaction boundary or session.

Transactions

Hive doesn't support multi-statement transactions. It doesn't support BEGIN, COMMIT, and ROLLBACK statements. In Hive, all language operations are auto-committed.

BigQuery supports multi-statement transactions inside a single query or across multiple queries when you use sessions. A multi-statement transaction lets you perform mutating operations, such as inserting or deleting rows from one or more tables and either committing or rolling back the changes. For more information, see Multi-statement transactions.