Guide de traduction SQL Apache Hive

Ce document décrit les similitudes et les différences de syntaxe SQL entre Apache Hive et BigQuery pour vous aider à planifier votre migration. Pour migrer vos scripts SQL de façon groupée, utilisez la traduction SQL par lot. Pour traduire des requêtes ad hoc, utilisez la traduction SQL interactive.

Dans certains cas, il n'existe pas de mappage direct entre un élément SQL dans Hive et BigQuery. Cependant, dans la plupart des cas, BigQuery propose un autre élément à Hive pour vous aider à obtenir les mêmes fonctionnalités, comme indiqué dans les exemples de ce document.

Ce document est destiné aux architectes d'entreprise, aux administrateurs de bases de données, aux développeurs d'applications et aux spécialistes de la sécurité informatique. Il part du principe que vous connaissez bien Hive.

Types de données

Hive et BigQuery disposent de systèmes de types de données différents. Dans la plupart des cas, vous pouvez mapper les types de données dans Hive vers les types de données BigQuery, à quelques exceptions près, telles que MAP et UNION. Hive accepte le casting de types plus implicite que BigQuery. Par conséquent, la traduction SQL par lot insère de nombreuses diffusions explicites.

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 avec des valeurs de clés (champ REPEAT)
UNION	STRUCT avec différents types
-	GEOGRAPHY
-	JSON

Syntaxe des requêtes

Cette section traite des différences de syntaxe des requêtes entre Hive et BigQuery.

Instruction SELECT

La plupart des instructions SELECT Hive sont compatibles avec BigQuery. Le tableau suivant contient une liste de différences mineures :

Case	Hive	BigQuery
Sous-requête	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 );
Filtrage de colonne	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 );
Éclater un tableau	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;

Clause FROM

La clause FROM d'une requête répertorie les références de table à partir desquelles les données sont sélectionnées. Dans Hive, les références de tables possibles incluent les tables, les vues et les sous-requêtes. BigQuery accepte également toutes ces références de table.

Vous pouvez référencer des tables BigQuery dans la clause FROM à l'aide des éléments suivants :

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

BigQuery accepte également d'autres références de table :

• Versions historiques de la définition de table et des lignes, en utilisant FOR SYSTEM_TIME AS OF
• Chemins d'accès de champ, ou tout chemin menant à un champ dans un type de données (par exemple, une STRUCT)
• Tableaux aplatis.

Opérateurs de comparaison

Le tableau suivant fournit des détails sur la conversion des opérateurs de Hive vers BigQuery :

Fonction ou opérateur	Hive	BigQuery
- Moins unaire * Multiplication / Division + Addition - Soustraction	Tous les types de nombres	Tous les types de nombres. Pour éviter les erreurs lors de l'opération de division, envisagez d'utiliser SAFE_DIVIDE ou IEEE_DIVIDE.
~ Opérateur NOT (PAS) au niveau du bit | Opérateur OR (OU) bit à bit & Opérateur AND (ET) au niveau du bit ^ Opérateur XOR (OU exclusif) au niveau du bit	Type de données "Booléen"	Type de données "Booléen".
Décalage à gauche	shiftleft(TINYINT|SMALLINT|INT a, INT b) shiftleft(BIGINT a, INT b)	<< Entier ou BYTES A << B, où B doit être du même type que A
Décalage à droite	shiftright(TINYINT|SMALLINT|INT a, INT b) shiftright(BIGINT a, INT b)	>> Entier ou BYTES A >> B, où B doit être du même type que A
Module (reste)	X % Y Tous les types de nombres	MOD(X, Y)
Division d'entiers	A DIV B et A/B pour une précision détaillée	Tous les types de nombres. Remarque : Pour éviter les erreurs lors de l'opération de division, envisagez d'utiliser SAFE_DIVIDE ou IEEE_DIVIDE.
Négation unaire	!, NOT	NOT
Types compatibles avec les comparaisons d'égalité	Tous les types primitifs	Tous les types comparables et STRUCT.
	a <=> b	Non compatible. Traduisez les éléments suivants : (a = b AND b IS NOT NULL OR a IS NULL)
	a <> b	Non compatible. Traduisez les éléments suivants : NOT (a = b AND b IS NOT NULL OR a IS NULL)
Opérateurs relationnels ( =, ==, !=, <, >, >= )	Tous les types primitifs	Tous les types comparables.
Comparaison de chaînes	RLIKE, REGEXP	Fonction intégrée REGEXP_CONTAINS. Utilise la syntaxe d'expression régulière BigQuery pour les fonctions de chaîne pour les modèles d'expression régulière.
[NOT] LIKE, [NOT] BETWEEN, IS [NOT] NULL	A [NOT] BETWEEN B AND C, A IS [NOT] (TRUE|FALSE), A [NOT] LIKE B	Identique à Hive. BigQuery est également compatible avec l'opérateur IN.

Conditions JOIN

Hive et BigQuery sont compatibles avec les types de jointures suivants :

• [INNER] JOIN

• LEFT [OUTER] JOIN

• RIGHT [OUTER] JOIN

• FULL [OUTER] JOIN

• CROSS JOIN et la jointure croisée séparée par des virgules implicite équivalente.

Pour en savoir plus, consultez les sections Opérations de jointure et Jointures Hive.

Casting et conversion des types de données

Le tableau suivant fournit des détails sur la conversion des fonctions de Hive vers BigQuery :

Fonction ou opérateur	Hive	BigQuery
Casting des types	Lorsqu'un casting échoue, la valeur "NULL" est renvoyée.	Syntaxe identique à Hive. Pour en savoir plus sur les règles de conversion de type BigQuery, consultez la page Règles de conversion. Si le casting échoue, une erreur s'affiche. Pour obtenir le même comportement que Hive, utilisez plutôt SAFE_CAST.
Appels de fonctions SAFE		Si vous ajoutez le préfixe SAFE aux appels de fonction, la fonction renvoie NULL au lieu de signaler l'échec. Par exemple, SAFE.SUBSTR('foo', 0, -2) AS safe_output; renvoie NULL. Remarque : Lorsque vous castez des données en toute sécurité sans erreur, utilisez SAFE_CAST.

Types de conversion implicites

Lors de la migration vers BigQuery, vous devez convertir la plupart de vos conversions implicites Hive en conversions explicites BigQuery, à l'exception des types de données suivants que BigQuery convertit implicitement.

Depuis le type BigQuery	Vers le type BigQuery
INT64	FLOAT64, NUMERIC et BIGNUMERIC
BIGNUMERIC	FLOAT64
NUMERIC	BIGNUMERIC, FLOAT64

BigQuery effectue également des conversions implicites pour les littéraux suivants :

Depuis le type BigQuery	Vers le type BigQuery
STRING littéral, par exemple "2008-12-25".	DATE
STRING littéral, par exemple "2008-12-25 15:30:00".	TIMESTAMP
STRING littéral, par exemple "2008-12-25T07:30:00".	DATETIME
STRING littéral, par exemple "15:30:00".	TIME

Types de conversions explicites

Si vous souhaitez convertir des types de données Hive que BigQuery ne convertit pas implicitement, utilisez la fonction CAST(expression AS type) de BigQuery ou l'une des fonctions de conversion DATE et TIMESTAMP.

Fonctions

Cette section couvre les fonctions courantes utilisées dans Hive et BigQuery.

Fonctions d'agrégation

Le tableau suivant présente les mappages des fonctions Hive d'agrégation, d'agrégation statistique et d'agrégation approximative courantes avec leurs équivalents dans 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))] BigQuery n'est pas compatible avec les autres arguments définis par 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

Fonctions analytiques

Le tableau suivant présente les mappages des fonctions d'analyse Hive courantes avec leurs équivalents dans 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	Une fonction définie par l'utilisateur (UDF) peut être utilisée.

Fonctions de date et heure

Le tableau suivant présente les mappages des fonctions de date et heure courantes de Hive et de leurs équivalents dans 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 est recommandé, car cette valeur est sans fuseau horaire et synonyme de CURRENT_TIMESTAMP \ CURRENT_TIMESTAMP dans 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 propose les fonctions de date et heure supplémentaires suivantes :

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

Fonctions de chaîne

Le tableau suivant présente les mappages entre les fonctions de chaîne Hive et leurs équivalents dans 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

BigQuery propose les fonctions de chaîne supplémentaires suivantes :

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

Fonctions mathématiques

Le tableau suivant présente les mappages entre les fonctions mathématiques Hive et leurs équivalents dans 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	Utilisez cette option avec 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 propose les fonctions mathématiques supplémentaires suivantes :

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

Fonctions logiques et conditionnelles

Le tableau suivant présente les mappages des fonctions logiques et conditionnelles Hive et de leurs équivalents dans 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

Fonctions définies par l'utilisateur et fonctions d'agrégation définies par l'utilisateur

BigQuery est compatible avec les UDF, mais pas avec les fonctions d'agrégation définies par l'utilisateur (UDAF).

Syntaxe LMD

Cette section traite des différences de syntaxe du langage de manipulation de données (LMD) entre Hive et BigQuery.

Instruction INSERT

La plupart des instructions INSERT Hive sont compatibles avec BigQuery. Le tableau suivant présente les exceptions :

Hive	BigQuery
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]	INSERT INTO table (...) VALUES (...); Remarque : Dans BigQuery, l'omission des noms de colonnes dans l'instruction INSERT ne fonctionne que si les valeurs de toutes les colonnes de la table cible sont incluses dans l'ordre croissant en fonction de leurs positions ordinales.
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 n'est pas compatible avec les opérations d'insertion et d'écrasement. Cette syntaxe Hive peut être migrée vers les instructions TRUNCATE et INSERT.

BigQuery impose des quotas LMD qui limitent le nombre d'instructions LMD que vous pouvez exécuter quotidiennement. Pour utiliser au mieux votre quota, envisagez les approches suivantes :

• Combinez plusieurs lignes en une seule instruction INSERT au lieu d'avoir une ligne pour chaque opération INSERT.

• Combinez plusieurs instructions LMD (y compris INSERT) à l'aide d'une instruction MERGE.

• Utilisez CREATE TABLE ... AS SELECT pour créer et remplir des tables.

Instruction UPDATE

La plupart des instructions UPDATE Hive sont compatibles avec BigQuery. Le tableau suivant présente les exceptions :

Hive	BigQuery
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]	UPDATE table SET column = expression [,...] [FROM ...] WHERE TRUE Remarque : Toutes les instructions UPDATE de BigQuery nécessitent un mot clé WHERE, suivi d'une condition.

Instructions DELETE et TRUNCATE

Vous pouvez utiliser des instructions DELETE ou TRUNCATE pour supprimer des lignes d'une table sans que cela n'affecte son schéma ou ses index.

Dans BigQuery, l'instruction DELETE doit comporter une clause WHERE. Pour en savoir plus sur DELETE dans BigQuery, consultez les exemples DELETE.

Hive	BigQuery
DELETE FROM tablename [WHERE expression]	DELETE FROM table_name WHERE TRUE Les instructions BigQuery DELETE nécessitent une clause WHERE .
TRUNCATE [TABLE] table_name [PARTITION partition_spec];	TRUNCATE TABLE [[project_name.]dataset_name.]table_name

Instruction MERGE

L'instruction MERGE peut combiner les opérations INSERT, UPDATE et DELETE en une seule instruction upsert et effectuer les opérations. L'opération MERGE doit correspondre à une ligne source au maximum pour chaque ligne cible.

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, ... Remarque : Vous devez répertorier toutes les colonnes à mettre à jour.

Instruction ALTER

Le tableau suivant fournit des détails sur la conversion des instructions CREATE VIEW de Hive vers BigQuery :

Fonction	Hive	BigQuery
Rename table	ALTER TABLE table_name RENAME TO new_table_name;	Non compatible. Pour contourner ce problème, utilisez un job de copie portant le nom que vous souhaitez utiliser comme table de destination, puis supprimez l'ancien. 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, ... )	La sérialisation et la désérialisation sont gérées par le service BigQuery et ne peuvent pas être configurées par l'utilisateur. Pour savoir comment autoriser BigQuery à lire des données à partir de fichiers CSV, JSON, AVRO, PARQUET ou ORC, consultez la page Créer des tables externes Cloud Storage. Compatible avec les formats d'exportation CSV, JSON, AVRO et PARQUET. Pour en savoir plus, consultez la page Formats d'exportation et types de compression.
Table storage properties	ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS;	Non compatible avec les instructions 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", ...] );	Le service d'équilibrage des requêtes pour les performances est géré par le service BigQuery et n'est pas 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; Pour en savoir plus, consultez la section Instruction 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, ...)	Non compatible. Des partitions supplémentaires sont ajoutées si nécessaire lorsque les données comportant de nouvelles valeurs dans les colonnes de partition sont chargées. Pour en savoir plus, consultez la page Gérer des tables partitionnées.
Rename partition	ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;	Non compatible.
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;	Non compatible.
Recover partition	MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];	Non compatible.
Drop partition	ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...] [IGNORE PROTECTION] [PURGE];	Compatible avec les méthodes suivantes : bq rm 'mydataset.table_name$partition_id' DELETE from table_name$partition_id WHERE 1=1 Pour en savoir plus, consultez la section Supprimer une partition.
(Un)Archive partition	ALTER TABLE table_name ARCHIVE PARTITION partition_spec; ALTER TABLE table_name UNARCHIVE PARTITION partition_spec;	Non compatible.
Table and partition file format	ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;	Non compatible.
Table and partition location	ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";	Non compatible.
Table and partition touch	ALTER TABLE table_name TOUCH [PARTITION partition_spec];	Non compatible.
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;	Non compatible.
Table and partition compact	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] COMPACT 'compaction_type'[AND WAIT] [WITH OVERWRITE TBLPROPERTIES ("property"="value" [, ...])];	Non compatible.
Table and artition concatenate	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] CONCATENATE;	Non compatible.
Table and partition columns	ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] UPDATE COLUMNS;	Non compatible avec les instructions 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];	Non compatible.

Syntaxe LDD

Cette section traite des différences de syntaxe du langage de définition de données (LDD) entre Hive et BigQuery.

Instructions CREATE TABLE et DROP TABLE

Le tableau suivant fournit des détails sur la conversion des instructions CREATE TABLE de Hive vers BigQuery :

Type	Hive	BigQuery
Tables gérées	create table table_name ( id int, dtDontQuery string, name string )	CREATE TABLE `myproject`.mydataset.table_name ( id INT64, dtDontQuery STRING, name STRING )
Tables partitionnées	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 Lors du partitionnement par date, annulez la mise en commentaire des éléments suivants : PARTITION BY dt OPTIONS( description="Table Description", Lors du partitionnement par date, annulez la mise en commentaire des éléments suivants : Il est recommandé d'utiliser require_partition lorsque la table est partitionnée. 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: Le format LIKE de CREATE TABLE vous permet de copier exactement une définition de table existante.	CREATE TABLE empty_key_value_store LIKE key_value_store [TBLPROPERTIES (property_name=property_value, ...)];	Non compatible.
Tables triées en buckets (dans la terminologie 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 )' Pour en savoir plus, consultez la page Créer et utiliser des tables en cluster.
Tables asymétriques (tables dans lesquelles une ou plusieurs colonnes ont des valeurs asymétriques)	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];	Non compatible.
Tables temporaires	CREATE TEMPORARY TABLE list_bucket_multiple ( col1 STRING, col2 int, col3 STRING);	Pour ce faire, utilisez le délai d'expiration comme suit : 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")] )
Tables transactionnelles	CREATE TRANSACTIONAL TABLE transactional_table_test(key string, value string) PARTITIONED BY(ds string) STORED AS ORC;	Toutes les modifications de table dans BigQuery sont conformes à la norme ACID (atomicité, cohérence, isolation, durabilité).
Supprimer la table	DROP TABLE [IF EXISTS] table_name [PURGE];	{DROP TABLE | DROP TABLE IF EXISTS} table_name
Tronquer la table	TRUNCATE TABLE table_name [PARTITION partition_spec]; partition_spec: : (partition_column = partition_col_value, partition_column = partition_col_value, ...)	Non compatible. Les solutions suivantes sont disponibles : Supprimez et créez à nouveau la table avec le même schéma. Définit la disposition d'écriture de la table sur WRITE_TRUNCATE si l'opération de troncation est un cas d'utilisation courant pour la table donnée. Utilisez l'instruction CREATE OR REPLACE TABLE. Utilisez l'instruction DELETE from table_name WHERE 1=1. Remarque : Des partitions spécifiques peuvent également être tronquées. Pour en savoir plus, consultez la section Supprimer une partition.

Instructions CREATE EXTERNAL TABLE et DROP EXTERNAL TABLE

Pour découvrir la compatibilité des tables externes dans BigQuery, consultez la page Présentation des sources de données externes.

Instructions CREATE VIEW et DROP VIEW

Le tableau suivant fournit des détails sur la conversion des instructions CREATE VIEW de Hive vers 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

Instructions CREATE FUNCTION et DROP FUNCTION

Le tableau suivant fournit des détails sur la conversion des procédures stockées de Hive vers 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;	Non compatible.
CREATE FUNCTION [db_name.]function_name AS class_name [USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];	Compatible avec les projets ajoutés à la liste d'autorisation en tant que fonctionnalité alpha. 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;	Non compatible.

Instructions CREATE MACRO et DROP MACRO

Le tableau suivant fournit des détails sur la conversion des instructions SQL procédurales utilisées lors de la création de macros de Hive vers BigQuery avec une déclaration et une attribution de variables :

Hive	BigQuery
CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;	Non compatible. Dans certains cas, il peut être remplacé par une fonction définie par l'utilisateur.
DROP TEMPORARY MACRO [IF EXISTS] macro_name;	Non compatible.

Codes et messages d'erreur

Les codes d'erreur Hive et les codes d'erreur BigQuery sont différents. Si votre logique d'application détecte des erreurs, éliminez la source de l'erreur, car BigQuery ne renvoie pas les mêmes codes d'erreur.

Dans BigQuery, il est courant de faire appel aux vues INFORMATION_SCHEMA ou à la journalisation d'audit pour examiner les erreurs.

Garanties de cohérence et isolation de transaction

Hive et BigQuery prennent en charge les transactions avec une sémantique ACID. Les transactions sont activées par défaut dans Hive 3.

Sémantique ACID

Hive est compatible avec l'isolation d'instantané. Lorsque vous exécutez une requête, celle-ci est fournie avec un instantané cohérent de la base de données, qu'elle utilise jusqu'à la fin de son exécution. Hive fournit une sémantique ACID complète au niveau des lignes, ce qui permet à une application d'ajouter des lignes lorsqu'une autre application lit la même partition sans interférer les unes avec les autres.

BigQuery permet un contrôle de simultanéité optimiste (le premier à effectuer un commit l'emporte) avec isolation d'instantané, où une requête lit les dernières données validées avant le démarrage de la requête. Cette approche garantit le même niveau de cohérence pour chaque ligne et mutation, et entre les lignes d'une même instruction LMD, tout en évitant les interblocages. En cas de mises à jour LMD multiples sur la même table, BigQuery bascule vers le contrôle de simultanéité pessimiste. Les jobs de chargement peuvent s'exécuter indépendamment et ajouter des tables. Cependant, BigQuery ne fournit pas de limite de transaction explicite ni de session.

Transactions

Hive n'est pas compatible avec les transactions contenant plusieurs instructions. Il n'est pas compatible avec les instructions BEGIN, COMMIT et ROLLBACK. Dans Hive, toutes les opérations de langage sont validées automatiquement.

BigQuery accepte les transactions multi-instructions dans une seule requête ou sur plusieurs requêtes lorsque vous utilisez des sessions. Une transaction multi-instructions vous permet d'effectuer des opérations de mutation, telles que l'insertion ou la suppression de lignes d'une ou de plusieurs tables, et la validation ou le rollback des modifications. Pour plus d'informations, consultez la section Transactions multi-instructions.