Guia de tradução de SQL do Apache Hive

Este documento detalha as semelhanças e diferenças de sintaxe SQL entre o Apache Hive e o BigQuery para ajudar você a planejar sua migração. Para migrar scripts SQL em massa, use a tradução de SQL em lote. Para converter consultas ad hoc, use a tradução interativa do SQL.

Em alguns casos, não há nenhum mapeamento direto entre um elemento SQL do Hive e do BigQuery. No entanto, na maioria dos casos, o BigQuery oferece um elemento alternativo ao Hive para ajudar você a conseguir a mesma funcionalidade, como mostrado nos exemplos deste documento.

O público-alvo deste documento são arquitetos empresariais, administradores de bancos de dados, desenvolvedores de aplicativos e especialistas em segurança de TI. Ele pressupõe que você esteja familiarizado com o Hive.

Tipos de dados

O Apache Hive e o BigQuery têm sistemas de tipos de dados diferentes. Na maioria dos casos, é possível mapear tipos de dados no Hive para tipos de dados do BigQuery com algumas exceções, como MAP e UNION. O Hive é compatível com mais transmissões implícitas do que o BigQuery. Como resultado, o tradutor de SQL em lote insere muitas transmissões explícitas.

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 com chaves-valor (campo REPEAT)
UNION STRUCT com tipos diferentes
- GEOGRAPHY
- JSON

Sintaxe das consultas

Nesta seção, mostramos as diferenças de sintaxe de consulta entre o Hive e o BigQuery.

Instrução SELECT

A maioria das instruções SELECT do Hive é compatível com o BigQuery A tabela a seguir mostra uma lista de pequenas diferenças:

Caso Hive BigQuery
Subconsulta

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

Filtragem de colunas

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

Como explodir uma matriz

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;

Cláusula FROM

A cláusula FROM em uma consulta lista as referências de tabela a partir das quais os dados são selecionados. No Hive, possíveis referências de tabela incluem tabelas, visualizações e subconsultas. O BigQuery também aceita todas essas referências de tabelas.

É possível referenciar tabelas do BigQuery na cláusula FROM usando o seguinte:

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

O BigQuery também aceita referências a outras tabelas:

Operadores de comparação

A tabela a seguir fornece detalhes sobre a conversão de operadores do Hive para o BigQuery:

Função ou operador Hive BigQuery
- Unário menos
* Multiplicação
/ Divisão
+ Adição
- Subtração
Todos os tipos de números Todos os tipos de números.

Para evitar erros durante a operação de divisão, use SAFE_DIVIDE ou IEEE_DIVIDE.

~ bit a bit não
| bit a bit OU
& bit a bit E
^ bit a bit XOR
Tipo de dados booleano Tipo de dados booleano.
Deslocado para a esquerda

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

<< Inteiro ou bytes

A << B, em que B precisa ser do mesmo tipo que A

Deslocado para a direita

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

>> Inteiro ou bytes

A >> B, em que B precisa ser do mesmo tipo que A

Módulo (restante) X % Y

Todos os tipos de números

MOD(X, Y)
Divisão inteira A DIV B e A/B para precisão detalhada Todos os tipos de números.

Observação: para evitar erros durante a operação de divisão, use SAFE_DIVIDE ou IEEE_DIVIDE.

Negação unária !, NOT NOT
Tipos compatíveis com comparações de igualdade Todos os tipos primitivos Todos os tipos comparáveis e STRUCT.
a <=> b Incompatível. Traduzir para o seguinte:

(a = b AND b IS NOT NULL OR a IS NULL)

a <> b Incompatível. Traduzir para o seguinte:

NOT (a = b AND b IS NOT NULL OR a IS NULL)

Operadores relacionais ( =, ==, !=, <, >, >= ) Todos os tipos primitivos Todos os tipos comparáveis.
Comparação de strings RLIKE, REGEXP REGEXP_CONTAINS função integrada. Usa a sintaxe regex do BigQuery para funções de string nos padrões de expressão regular.
[NOT] LIKE, [NOT] BETWEEN, IS [NOT] NULL A [NOT] BETWEEN B AND C, A IS [NOT] (TRUE|FALSE), A [NOT] LIKE B Igual ao Hive. Além disso, o BigQuery também é compatível com o operador IN.

Condições JOIN

O Hive e o BigQuery são compatíveis com os seguintes tipos de mesclagens:

Para mais informações, consulte Operação de mesclagem e Mesclagens do Hive.

Conversão e transmissão de tipo

A tabela a seguir fornece detalhes sobre como converter funções do Hive para o BigQuery:

Função ou operador Hive BigQuery
Tipografia Quando uma transmissão falha, "NULL" é retornado.

Mesma sintaxe do Hive. Para mais informações sobre as regras de conversão de tipo do BigQuery, consulte Regras de conversão.

Se a transmissão falhar, você verá um erro. Para ter o mesmo comportamento do Hive, use SAFE_CAST.

SAFE chamadas de funções Se você prefixar chamadas de função com SAFE, a função retornará NULL em vez de relatar falhas. Por exemplo, SAFE.SUBSTR('foo', 0, -2) AS safe_output; retorna NULL.

Observação: ao transmitir com segurança e sem erros, use SAFE_CAST.

Tipos de conversão implícitos

Ao migrar para o BigQuery, você precisa converter a maioria das conversões implícitas do Hive em conversões explícitas do BigQuery, exceto pelos tipos de dados a seguir, que o BigQuery converte implicitamente.

Do tipo do BigQuery Para o tipo do BigQuery
INT64 FLOAT64, NUMERIC, BIGNUMERIC
BIGNUMERIC FLOAT64
NUMERIC BIGNUMERIC, FLOAT64

O BigQuery também realiza conversões implícitas para os seguintes literais:

Do tipo do BigQuery Para o tipo do BigQuery
STRING literal (por exemplo, "2008-12-25") DATE
STRING literal (por exemplo, "2008-12-25 15:30:00") TIMESTAMP
STRING literal (por exemplo, "2008-12-25T07:30:00") DATETIME
STRING literal (por exemplo, "15:30:00") TIME

Tipos de conversão explícitos

Se você quiser converter tipos de dados do Hive que o BigQuery não converte implicitamente, use a função CAST(expression AS type) do BigQuery ou as funções de conversão DATE e TIMESTAMP.

Funções

Esta seção aborda funções comuns usadas no Hive e no BigQuery.

Funções de agregação

A tabela a seguir mostra os mapeamentos entre funções comuns agregadas o Hive, agregadas estáticas e agregadas aproximadas com os equivalentes do BigQuery:

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))]

O BigQuery não é compatível com os outros argumentos definidos no Hive.

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

Funções analíticas

A tabela a seguir mostra mapeamentos entre funções analíticas comuns do Hive com os equivalentes no BigQuery:

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 Uma função definida pelo usuário (UDF) pode ser usada.

Funções de data e hora

A tabela a seguir mostra mapeamentos entre funções comuns de data e hora do Hive e os equivalentes no BigQuery:

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 é recomendado, já que esse valor não tem um fuso horário e é sinônimo de CURRENT_TIMESTAMP \ CURRENT_TIMESTAMP no 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

O BigQuery oferece as seguintes funções extras de data/hora:

Funções de string

A tabela a seguir mostra os mapeamentos entre as funções de string do Hive e os equivalentes do BigQuery:

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

O BigQuery oferece as seguintes funções extras de string:

Funções matemáticas

A tabela a seguir mostra os mapeamentos entre as funções matemáticas do Hive e os equivalentes do BigQuery:

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 com 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)

O BigQuery oferece as seguintes funções matemáticas extras:

Funções lógicas e condicionais

A tabela a seguir mostra os mapeamentos entre as funções lógicas e condicionais do Hive e os equivalentes no BigQuery:

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 e UDAFs

O BigQuery oferece suporte a UDFs, mas não a funções de agregação definidas pelo usuário (UDAFs, na sigla em inglês).

Sintaxe DML

Esta seção aborda as diferenças na sintaxe da linguagem de manipulação de dados (DML, na sigla em inglês) entre o Hive e o BigQuery.

Instrução INSERT

A maioria das instruções INSERT do Hive é compatível com o BigQuery. A tabela a seguir mostra as exceções:

Hive BigQuery
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...] INSERT INTO table (...) VALUES (...);

Observação: no BigQuery, a omissão de nomes de coluna na instrução INSERT funcionará somente se os valores de todas as colunas na tabela de destino forem incluídos em ordem crescente com base nas posições ordinais.

INSERT OVERWRITE [LOCAL] DIRECTORY directory1

[ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)

SELECT ... FROM ...

O BigQuery não oferece suporte às operações de inserção/substituição. A sintaxe do Hive pode ser migrada para instruções TRUNCATE e INSERT.

O BigQuery impõe cotas DML que restringem o número de instruções DML que podem ser executadas diariamente. Para fazer o melhor uso da sua cota, considere as seguintes abordagens:

  • Combine várias linhas em uma única instrução INSERT, em vez de uma linha para cada operação INSERT.

  • Combine várias instruções DML (incluindo INSERT) usando uma instrução MERGE.

  • Use CREATE TABLE ... AS SELECT para criar e preencher novas tabelas.

Instrução UPDATE

A maioria das instruções UPDATE do Hive é compatível com o BigQuery. A tabela a seguir mostra as exceções:

Hive BigQuery
UPDATE tablename SET column = value [, column = value ...] [WHERE expression] UPDATE table

SET column = expression [,...]

[FROM ...]

WHERE TRUE

Observação: todas as instruções UPDATE do BigQuery exigem uma palavra-chave WHERE, seguida por uma condição.

Declarações DELETE e TRUNCATE.

É possível usar instruções DELETE ou TRUNCATE para remover linhas de uma tabela sem afetar o esquema ou os índices dela.

No BigQuery, a instrução DELETE precisa ter uma cláusula WHERE. Para mais informações sobre DELETE no BigQuery, consulte exemplos de DELETE.

Hive BigQuery
DELETE FROM tablename [WHERE expression] DELETE FROM table_name WHERE TRUE

As instruções DELETE do BigQuery exigem uma cláusula WHERE .

TRUNCATE [TABLE] table_name [PARTITION partition_spec]; TRUNCATE TABLE [[project_name.]dataset_name.]table_name

Instrução MERGE

A instrução MERGE pode combinar operações INSERT, UPDATE e DELETE em uma única instrução upsert e executar as operações. A operação MERGE precisa corresponder a uma linha de origem no máximo para cada linha de destino.

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,

...

Observação: liste todas as colunas que precisam ser atualizadas.

Instrução ALTER

A tabela a seguir fornece detalhes sobre a conversão de instruções CREATE VIEW do Hive para o BigQuery:

Função Hive BigQuery
Rename table ALTER TABLE table_name RENAME TO new_table_name; Incompatível. Uma solução alternativa é usar um job de cópia com o nome que você quer como a tabela de destino e, em seguida, excluir o antigo.

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

A serialização e desserialização são gerenciadas pelo serviço BigQuery e não podem ser configuradas pelo usuário.

Para saber como permitir que o BigQuery leia dados de arquivos CSV, JSON, AVRO, PARQUET ou ORC, consulte Criar tabelas externas do Cloud Storage.

Compatível com os formatos de exportação CSV, JSON, AVRO e PARQUET. Para mais informações, consulte Formatos de exportação e tipos de compactação.

Table storage properties ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS; Não há suporte para as instruções ALTER.
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", ...] );

O balanceamento de armazenamento para consultas de desempenho é gerenciado pelo serviço BigQuery e não é configurável.
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;

Para mais informações, consulte a instrução ALTER TABLE ADD PRIMARY KEY.

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

Incompatível. Quando os dados com novos valores nas colunas das partições são carregados, outras partições são adicionadas conforme necessário.

Para mais informações, consulte Como gerenciar tabelas particionadas.

Rename partition ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec; Incompatível.
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;

Incompatível.
Recover partition MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS]; Incompatível.
Drop partition ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...] [IGNORE PROTECTION] [PURGE]; Compatível com os seguintes métodos:
  • bq rm 'mydataset.table_name$partition_id'
  • DELETE from table_name$partition_id WHERE 1=1

  • Para mais informações, consulte Excluir uma partição.

(Un)Archive partition ALTER TABLE table_name ARCHIVE PARTITION partition_spec; ALTER TABLE table_name UNARCHIVE PARTITION partition_spec; Incompatível.
Table and partition file format ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format; Incompatível.
Table and partition location ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location"; Incompatível.
Table and partition touch ALTER TABLE table_name TOUCH [PARTITION partition_spec]; Incompatível.
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;

Incompatível.
Table and partition compact ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] COMPACT 'compaction_type'[AND WAIT]

[WITH OVERWRITE TBLPROPERTIES ("property"="value" [, ...])];

Incompatível.
Table and artition concatenate ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] CONCATENATE; Incompatível.
Table and partition columns ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] UPDATE COLUMNS; Não há suporte para as instruções ALTER TABLE.
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]; Incompatível.

Sintaxe DDL

Nesta seção, abordamos as diferenças na sintaxe da linguagem de definição de dados (DDL, na sigla em inglês) entre o Hive e o BigQuery.

Instruções CREATE TABLE e DROP TABLE.

A tabela a seguir fornece detalhes sobre a conversão de instruções CREATE TABLE do Hive para o BigQuery:

Tipo Hive BigQuery
Tabelas gerenciadas create table table_name (

id int,

dtDontQuery string,

name string

)

CREATE TABLE `myproject`.mydataset.table_name (

id INT64,

dtDontQuery STRING,

name STRING

)

Tabelas particionadas 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

Ao particionar por data, remova a marca de comentário do seguinte:

PARTITION BY dt

OPTIONS(

description="Table Description",

Ao particionar por data, remova a marca de comentário a seguir. É recomendável usar require_partition quando a tabela estiver particionada.

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:

A forma LIKE de CREATE TABLE permite copiar uma definição de tabela existente.

CREATE TABLE empty_key_value_store

LIKE key_value_store [TBLPROPERTIES (property_name=property_value, ...)];

Incompatível.
Tabelas classificadas em buckets (em cluster na terminologia do BigQuery) 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

)'

Para mais informações, consulte Criar e usar tabelas em cluster.

Tabelas distorcidas (tabelas em que uma ou mais colunas têm valores distorcidos) 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];

Incompatível.
Tabelas temporárias CREATE TEMPORARY TABLE list_bucket_multiple (

col1 STRING,

col2 int,

col3 STRING);

Você pode fazer isso usando o prazo de validade da seguinte forma:

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")]

)

Tabelas transacionais CREATE TRANSACTIONAL TABLE transactional_table_test(key string, value string) PARTITIONED BY(ds string) STORED AS ORC; Todas as modificações de tabelas no BigQuery são compatíveis com ACID (atomicidade, consistência, isolamento e durabilidade).
Remover tabela DROP TABLE [IF EXISTS] table_name [PURGE]; {DROP TABLE | DROP TABLE IF EXISTS}

table_name

Truncar tabela TRUNCATE TABLE table_name [PARTITION partition_spec];

partition_spec:

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

Incompatível. As seguintes soluções estão disponíveis:

  • Solte e crie a tabela novamente com o mesmo esquema.
  • Defina a disposição de gravação da tabela como WRITE_TRUNCATE se a operação de truncamento for um caso de uso comum para a tabela especificada.
  • Use a instrução CREATE OR REPLACE TABLE.
  • Use a instrução DELETE from table_name WHERE 1=1.

Observação: também é possível truncar partições específicas. Para mais informações, consulte Excluir uma partição.

Instruções CREATE EXTERNAL TABLE e DROP EXTERNAL TABLE.

Para suporte a tabelas externas no BigQuery, consulte Introdução a fontes de dados externas.

Instruções CREATE VIEW e DROP VIEW.

A tabela a seguir fornece detalhes sobre a conversão de instruções CREATE VIEW do Hive para o 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

Instruções CREATE FUNCTION e DROP FUNCTION.

A tabela a seguir fornece detalhes sobre como converter os procedimentos armazenados do Hive para o 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; Incompatível.
CREATE FUNCTION [db_name.]function_name AS class_name

[USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];

Oferece suporte a projetos na lista de permissões como um recurso Alfa.

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; Incompatível.

Instruções CREATE MACRO e DROP MACRO.

A tabela a seguir fornece detalhes sobre a conversão de instruções SQL processuais usadas na criação de macro do Hive para o BigQuery com declaração e atribuição de variáveis:

Hive BigQuery
CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression; Incompatível. Em alguns casos, ela pode ser substituída por uma UDF.
DROP TEMPORARY MACRO [IF EXISTS] macro_name; Incompatível.

Mensagens e códigos de erro

Os códigos de erro do Hive e do BigQuery são diferentes. Se a lógica do aplicativo estiver detectando erros, elimine a origem do erro porque o BigQuery não retorna os mesmos códigos de erro.

No BigQuery, é comum usar as visualizações INFORMATION_SCHEMA ou a geração de registros de auditoria para examinar os erros.

Garantias de consistência e isolamento da transação

O Hive e o BigQuery são compatíveis com transações com semântica ACID. Transações são ativadas por padrão no Hive 3.

Semântica ACID

O Hive é compatível com isolamento de snapshots. Ao executar uma consulta, ela recebe um snapshot consistente do banco de dados, que é usado até o final da execução. O Hive fornece semântica ACID completa no nível da linha, permitindo que um aplicativo adicione linhas quando outro aplicativo lê a mesma partição sem interferir um no outro.

O BigQuery garante o controle de simultaneidade otimista (ganha o primeiro que confirmar) com o isolamento de snapshot, em que uma consulta lê os últimos dados confirmados antes do início da consulta. Essa abordagem garante o mesmo nível de consistência para cada linha e mutação e em todas as linhas dentro da mesma instrução DML, evitando impasses. Para várias atualizações de DML na mesma tabela, o BigQuery alterna para o controle de simultaneidade pessimista. Os jobs de carregamento podem ser executados de forma independente e anexar tabelas. No entanto, o BigQuery não apresenta um limite de transação ou sessão explícita.

Transações

O Hive não é compatível com transações de várias instruções. Ele não é compatível com instruções BEGIN, COMMIT e ROLLBACK. No Hive, todas as operações de linguagem são confirmadas automaticamente.

O BigQuery é compatível com transações de várias instruções dentro de uma única consulta ou em várias consultas ao usar as sessões. Uma transação de várias instruções permite executar operações de modificação, como inserir ou excluir linhas de uma ou mais tabelas e confirmar ou reverter as alterações. Para mais informações, consulte Transações de várias instruções.