Guide de traduction SQL d'Amazon Redshift

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

Ce guide 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. Nous partons du principe que vous connaissez bien Amazon Redshift.

Types de données

Cette section présente les équivalences entre les types de données Amazon Redshift et BigQuery.

Amazon Redshift BigQuery Remarques
Type de données Alias Type de données
SMALLINT INT2 INT64 Le SMALLINT d'Amazon Redshift est de 2 octets, tandis que le INT64 de BigQuery est de 8 octets.
INTEGER

INT, INT4

 INT64 Le INTEGER d'Amazon Redshift est de 4 octets, tandis que le INT64 de BigQuery est de 8 octets.
BIGINT INT8 INT64 Le BIGINT d'Amazon Redshift et le INT64 de BigQuery sont tous deux de 8 octets.
DECIMAL NUMERIC NUMERIC
REAL FLOAT4 FLOAT64 Le REAL d'Amazon Redshift est de 4 octets, tandis que le FLOAT64 de BigQuery est de 8 octets.
DOUBLE PRECISION

FLOAT8, FLOAT

 FLOAT64
BOOLEAN BOOL BOOL Le fichier BOOLEAN d'Amazon Redshift peut utiliser TRUE, t, true, y, yes et 1 comme valeurs littérales valides pour "true". Le type de données BOOL de BigQuery utilise un champ TRUE non sensible à la casse.
CHAR

CHARACTER, NCHAR, BPCHAR

STRING
VARCHAR

CHARACTER VARYING, NVARCHAR, TEXT

STRING
DATE DATE
TIMESTAMP TIMESTAMP WITHOUT TIME ZONE DATETIME
TIMESTAMPTZ

TIMESTAMP WITH TIME ZONE

TIMESTAMP Remarque : Dans BigQuery, les fuseaux horaires sont utilisés lors de l'analyse ou du formatage des horodatages à des fins d'affichage. Un horodatage au format de chaîne peut inclure un fuseau horaire, mais lorsque BigQuery analyse la chaîne, il stocke l'horodatage dans l'heure UTC équivalente. Lorsqu'un fuseau horaire n'est pas spécifié explicitement, le fuseau horaire par défaut est utilisé (UTC). Les noms de fuseau horaire ou les décalages par rapport à l'heure UTC utilisant le format (-|+)HH:MM sont acceptés, mais les abréviations de fuseau horaire telles que PDT ne le sont pas.
GEOMETRY GEOGRAPHY Compatibilité avec l'interrogation des données géospatiales.

BigQuery dispose également des types de données suivants, qui ne sont pas associés à une empreinte Amazon Redshift directe :

Types de conversion implicites

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

BigQuery effectue des conversions implicites pour les types de données suivants :

Depuis le type BigQuery Vers le type BigQuery

INT64

FLOAT64

INT64

NUMERIC

NUMERIC

FLOAT64

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

Depuis le type BigQuery Vers le type BigQuery
Littéral de STRING
(par exemple, "2008-12-25")

DATE

Littéral de STRING
(par exemple, "2008-12-25 15:30:00")

TIMESTAMP

Littéral de STRING
(par exemple, "2008-12-25T07:30:00")

DATETIME

Littéral de STRING
(par exemple, "15:30:00")

TIME

Types de conversions explicites

Vous pouvez convertir les types de données Amazon Redshift que BigQuery ne convertit pas implicitement à l'aide de la fonction CAST(expression AS type) de BigQuery ou l'une des fonctions de conversion DATE et TIMESTAMP.

Lors de la migration de vos requêtes, modifiez toutes les occurrences de la fonction CONVERT(type, expression) d'Amazon Redshift (ou la syntaxe ::) vers la fonctionCAST(expression AS type) de BigQuery comme indiqué dans le tableau de la section Fonctions de mise en forme des types de données.

Syntaxe des requêtes

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

Instruction SELECT

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

Amazon Redshift BigQuery

SELECT TOP number expression
FROM table

SELECT expression
FROM table
ORDER BY expression DESC
LIMIT number

SELECT
x/total AS probability,
ROUND(100 * probability, 1) AS pct
FROM raw_data

Remarque : Redshift permet de créer et de référencer un alias dans la même instruction SELECT.

SELECT
x/total AS probability,
ROUND(100 * (x/total), 1) AS pct
FROM raw_data

BigQuery accepte également les expressions suivantes dans les instructions SELECT, qui n'ont pas d'équivalent Amazon Redshift :

Clause FROM

Une clause FROM d'une requête répertorie les références de table à partir desquelles les données sont sélectionnées. Dans Amazon Redshift, les références de tables possibles incluent les tables, les vues et les sous-requêtes. Toutes ces références de table sont acceptées dans BigQuery.

Les tables BigQuery peuvent être référencées 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 :

Types JOIN

Amazon Redshift et BigQuery sont compatibles avec les types de jointure suivants :

Le tableau suivant contient une liste de différences mineures.

Amazon Redshift BigQuery

SELECT col
FROM table1
NATURAL INNER JOIN
table2

SELECT col1
FROM table1
INNER JOIN
table2
USING (col1, col2 [, ...])


Remarque: Dans BigQuery, les clauses JOIN nécessitent une condition JOIN, sauf si la clause est un CROSS JOIN ou que l'une des tables jointes est un champ dans un type de données ou un tableau.

Clause WITH

Une clause WITH BigQuery contient une ou plusieurs sous-requêtes nommées qui s'exécutent lorsqu'une instruction SELECT ultérieure y fait référence. Les clauses WITH d'Amazon Redshift se comportent de la même manière que BigQuery, à l'exception du fait que vous pouvez évaluer la clause une fois et réutiliser ses résultats.

Opérateurs d'ensemble

Il existe des différences mineures entre les opérateurs d'ensemble Amazon Redshift et les opérateurs d'ensemble BigQuery. Cependant, toutes les opérations d'ensemble réalisables dans Amazon Redshift sont reproductibles dans BigQuery.

Amazon Redshift BigQuery

SELECT * FROM table1
UNION
SELECT * FROM table2

SELECT * FROM table1
UNION DISTINCT
SELECT * FROM table2

Remarque : BigQuery et Amazon Redshift prennent en charge l'opérateur UNION ALL.

SELECT * FROM table1
INTERSECT
SELECT * FROM table2

SELECT * FROM table1
INTERSECT DISTINCT
SELECT * FROM table2

SELECT * FROM table1
EXCEPT
SELECT * FROM table2

SELECT * FROM table1
EXCEPT DISTINCT
SELECT * FROM table2

SELECT * FROM table1
MINUS
SELECT * FROM table2

SELECT * FROM table1
EXCEPT DISTINCT
SELECT * FROM table2

SELECT * FROM table1
UNION
SELECT * FROM table2
EXCEPT
SELECT * FROM table3

SELECT * FROM table1
UNION ALL
(
SELECT * FROM table2
EXCEPT
SELECT * FROM table3
)


Remarque : BigQuery nécessite des parenthèses pour séparer les différentes opérations d'ensemble. Si le même opérateur d'ensemble est répété, les parenthèses ne sont pas nécessaires.

Clause ORDER BY

Il existe des différences mineures entre les clauses ORDER BY D'Amazon Redshift et les clauses ORDER BY de BigQuery.

Amazon Redshift BigQuery
Dans Amazon Redshift, les NULL sont classées en dernier par défaut (ordre croissant). Dans BigQuery, les NULL sont classés par défaut en premier (par ordre croissant).

SELECT *
FROM table
ORDER BY expression
LIMIT ALL

SELECT *
FROM table
ORDER BY expression



Remarque : BigQuery n'utilise pas la syntaxe LIMIT ALL. Toutefois, ORDER BY trie toutes les lignes par défaut, ce qui entraîne le même comportement que la clause LIMIT ALL d'Amazon Redshift. Nous vous recommandons vivement d'inclure une clause LIMIT avec chaque clause ORDER BY. Le tri de toutes les lignes de résultats dégrade inutilement les performances d'exécution des requêtes.

SELECT *
FROM table
ORDER BY expression
OFFSET 10

SELECT *
FROM table
ORDER BY expression
LIMIT count OFFSET 10



Remarque : Dans BigQuery, OFFSET doit être utilisé avec un compte LIMIT. Veillez à définir la valeur de compte INT64 sur le nombre minimal de lignes ordonnées nécessaires. Le tri de toutes les lignes de résultats
dégrade inutilement les performances d'exécution des requêtes.

Conditions

Le tableau suivant présente les conditions Amazon Redshift ou prédicats qui sont spécifiques à Amazon Redshift et doivent être convertis dans leur équivalent BigQuery.

Amazon Redshift BigQuery

a = ANY (subquery)

a = SOME (subquery)

a IN subquery

a <> ALL (subquery)

a != ALL (subquery)

a NOT IN subquery

a IS UNKNOWN

expression ILIKE pattern

a IS NULL

LOWER(expression) LIKE LOWER(pattern)

expression LIKE pattern ESCAPE 'escape_char'

expression LIKE pattern


Remarque : BigQuery n'accepte pas les caractères d'échappement personnalisés. Vous devez utiliser deux barres obliques inverses (\\) comme caractères d'échappement pour BigQuery.

expression [NOT] SIMILAR TO pattern

IF(
LENGTH(
REGEXP_REPLACE(
expression,
pattern,
''
) = 0,
True,
False
)


Remarque : Si NOT est spécifié, encapsulez l'expression IF ci-dessus dans une expression NOT comme indiqué ci-dessous :

NOT(
IF(
LENGTH(...
)

expression [!] ~ pattern

[NOT] REGEXP_CONTAINS(
expression,
regex
)

Fonctions

Les sections suivantes répertorient les fonctions Amazon Redshift et leurs équivalents dans BigQuery.

Fonctions d'agrégation

Le tableau suivant présente les mappages des fonctions courantes d'agrégation, d'analyse agrégée et d'agrégation approximative d'Amazon Redshift avec leurs équivalents dans BigQuery.

Amazon Redshift BigQuery
APPROXIMATE COUNT(DISTINCT expression) APPROX_COUNT_DISTINCT(expression)
APPROXIMATE PERCENTILE_DISC(
percentile
) WITHIN GROUP (ORDER BY expression)		 APPROX_QUANTILES(expression, 100)
[OFFSET(CAST(TRUNC(percentile * 100) as INT64))]
AVG([DISTINCT] expression) AVG([DISTINCT] expression)
COUNT(expression) COUNT(expression)
LISTAGG(
[DISTINCT] aggregate_expression
[, delimiter] )
[WITHIN GROUP (ORDER BY order_list)] 		STRING_AGG(
[DISTINCT] aggregate_expression
[, delimiter]
[ORDER BY order_list] )
MAX(expression) MAX(expression)
MEDIAN(median_expression) PERCENTILE_CONT( median_expression, 0.5 ) OVER()
MIN(expression) MIN(expression)
PERCENTILE_CONT(
percentile
) WITHIN GROUP (ORDER BY expression)		 PERCENTILE_CONT(
median_expression,
percentile
) OVER()

Remarque : Ne couvre pas les cas d'utilisation d'agrégation.
STDDEV([DISTINCT] expression) STDDEV([DISTINCT] expression)
STDDEV_SAMP([DISTINCT] expression) STDDEV_SAMP([DISTINCT] expression)
STDDEV_POP([DISTINCT] expression) STDDEV_POP([DISTINCT] expression)
SUM([DISTINCT] expression) SUM([DISTINCT] expression)
VARIANCE([DISTINCT] expression) VARIANCE([DISTINCT] expression)
VAR_SAMP([DISTINCT] expression) VAR_SAMP([DISTINCT] expression)
VAR_POP([DISTINCT] expression) VAR_POP([DISTINCT] expression)

BigQuery propose également les fonctions d'agrégation, d'analyse analytique et d'agrégation approximative de BigQuery, comme indiqué ci-dessous, qui n'ont pas d'équivalent direct dans Amazon Redshift:

Fonctions d'agrégation bit à bit

Le tableau suivant présente les mappages des fonctions d'agrégation bit à bit courantes d'Amazon Redshift avec leurs équivalents dans BigQuery.

Amazon Redshift BigQuery
BIT_AND(expression) BIT_ADD(expression)
BIT_OR(expression) BIT_OR(expression)
BOOL_AND>(expression) LOGICAL_AND(expression)
BOOL_OR(expression) LOGICAL_OR(expression)

BigQuery propose également la fonction d'agrégation bit à bit suivante, qui n'a pas d'équivalent analogue dans Amazon Redshift :

Fonctions de fenêtrage

Le tableau suivant présente les mappages des fonctions de fenêtrage courantes d'Amazon Redshift avec leurs équivalents dans BigQuery. Les fonctions de fenêtrage dans BigQuery sont les suivantes : Fonctions d'agrégation analytique, Fonctions d'agrégation, Fonctions de navigation et Fonctions de numérotation.


Amazon Redshift BigQuery
AVG(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list frame_clause]
)		 AVG(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
COUNT(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)		 COUNT(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
CUME_DIST() OVER
(
[PARTITION BY partition_expression]
[ORDER BY order_list]
) 		CUME_DIST() OVER
(
[PARTITION BY partition_expression]
ORDER BY order_list
)
DENSE_RANK() OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
) 		DENSE_RANK() OVER
(
[PARTITION BY expr_list]
ORDER BY order_list
)
FIRST_VALUE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)		 FIRST_VALUE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
LAST_VALUE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)		 LAST_VALUE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)
LAG(value_expr [, offset]) OVER
(
[PARTITION BY window_partition]
ORDER BY window_ordering
) 		LAG(value_expr [, offset]) OVER
(
[PARTITION BY window_partition]
ORDER BY window_ordering
)
LEAD(value_expr [, offset]) OVER
(
[PARTITION BY window_partition]
ORDER BY window_ordering
) 		LEAD(value_expr [, offset]) OVER
(
[PARTITION BY window_partition]
ORDER BY window_ordering
)
LISTAGG(
[DISTINCT] expression
[, delimiter]
)
[WITHIN GROUP
(ORDER BY order_list)]
OVER (
[PARTITION BY partition_expression] )		 STRING_AGG(
[DISTINCT] aggregate_expression
[, delimiter] )
OVER (
[PARTITION BY partition_list]
[ORDER BY order_list] )
MAX(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)		 MAX(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
MEDIAN(median_expression) OVER
(
[PARTITION BY partition_expression] ) 		PERCENTILE_CONT(
median_expression,
0.5
)
OVER ( [PARTITION BY partition_expression] )
MIN(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)		 MIN(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
NTH_VALUE(expression, offset) OVER ( [PARTITION BY window_partition] [ORDER BY window_ordering frame_clause] ) NTH_VALUE(expression, offset) OVER
(
[PARTITION BY window_partition]
ORDER BY window_ordering
[frame_clause]
)
NTILE(expr) OVER
(
[PARTITION BY expression_list]
[ORDER BY order_list]
) 		NTILE(expr) OVER
(
[PARTITION BY expression_list]
ORDER BY order_list
)
PERCENT_RANK() OVER
(
[PARTITION BY partition_expression]
[ORDER BY order_list]
) 		PERCENT_RANK() OVER
(
[PARTITION BY partition_expression]
ORDER BY order_list
)
PERCENTILE_CONT(percentile)
WITHIN GROUP (ORDER BY expr) OVER
(
[PARTITION BY expr_list] ) 		PERCENTILE_CONT(expr, percentile) OVER
(
[PARTITION BY expr_list] )
PERCENTILE_DISC(percentile) WITHIN GROUP (ORDER BY expr) OVER
(
[PARTITION BY expr_list]
) 		PERCENTILE_DISC(expr, percentile) OVER
(
[PARTITION BY expr_list] )
RANK() OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
)		 RANK() OVER
(
[PARTITION BY expr_list]
ORDER BY order_list
)
RATIO_TO_REPORT(ratio_expression) OVER
(
[PARTITION BY partition_expression] ) 		ratio_expression SUM(ratio_expression) OVER
(
[PARTITION BY partition_expression] )
ROW_NUMBER() OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
) 		ROW_NUMBER() OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
)
STDDEV(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)		 STDDEV(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause] )
STDDEV_SAMP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)		 STDDEV_SAMP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
STDDEV_POP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
) 		STDDEV_POP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause] )
SUM(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
) 		SUM(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
VAR_POP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)		 VAR_POP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
VAR_SAMP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
) 		VAR_SAMP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
VARIANCE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)		 VARIANCE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)

Expressions conditionnelles

Le tableau suivant présente les mappages des expressions conditionnelles courantes d'Amazon Redshift avec leurs équivalents dans BigQuery.

Amazon Redshift BigQuery
CASEexpression
WHEN value THEN result
[WHEN...]
[ELSE else_result]
END		 CASE expression
WHEN value THEN result
[WHEN...]
[ELSE else_result]
END
COALESCE(expression1[, ...]) COALESCE(expression1[, ...])
DECODE(
expression,
search1, result1
[, search2, result2...]
[, default]
) 		CASE expression
WHEN value1 THEN result1
[WHEN value2 THEN result2]
[ELSE default]
END
GREATEST(value [, ...]) GREATEST(value [, ...])
LEAST(value [, ...]) LEAST(value [, ...])
NVL(expression1[, ...]) COALESCE(expression1[, ...])
NVL2(
expression,
not_null_return_value,
null_return_value
)		 IF(
expression IS NULL,
null_return_value,
not_null_return_value
)
NULLIF(expression1, expression2) NULLIF(expression1, expression2)

BigQuery propose également les expressions conditionnelles suivantes, qui n'ont pas d'équivalent direct dans Amazon Redshift :

Fonctions de date et heure

Le tableau suivant présente les mappages des fonctions de date et heure courantes d'Amazon Redshift avec leurs équivalents dans BigQuery. Les fonctions de date et heure de BigQuery incluent les fonctions de date, les fonctions datetime, les fonctions temporelles et les fonctions de code temporel.

Gardez à l'esprit que les fonctions qui semblent identiques dans Amazon Redshift et BigQuery peuvent renvoyer des types de données différents.

Amazon Redshift BigQuery
ADD_MONTHS(
date,
integer
)		 CAST( DATE_ADD(
date,
INTERVAL integer MONTH
)
AS TIMESTAMP
)
timestamptz_or_timestamp AT TIME ZONE timezone PARSE_TIMESTAMP(
"%c%z",
FORMAT_TIMESTAMP(
"%c%z",
timestamptz_or_timestamp,
timezone
)
)

Remarque : Les fuseaux horaires sont utilisés lors de l'analyse ou du formatage des horodatages à des fins d'affichage. Un horodatage au format de chaîne peut inclure un fuseau horaire, mais lorsque BigQuery analyse la chaîne, il stocke l'horodatage dans l'heure UTC équivalente. Lorsqu'un fuseau horaire n'est pas spécifié explicitement, le fuseau horaire par défaut est utilisé (UTC). Les noms de fuseau horaire ou les décalages par rapport à l'heure UTC sont acceptés, mais les abréviations de fuseau horaire (telles que PDT) ne le sont pas.
CONVERT_TIMEZONE(
[source_timezone],
target_timezone,
timestamp
)		 PARSE_TIMESTAMP(
"%c%z",
FORMAT_TIMESTAMP(
"%c%z",
timestamp,
target_timezone
)
)

Remarque : source_timezone correspond à UTC dans BigQuery.
CURRENT_DATE

Remarque : Renvoie la date de début de la transaction en cours dans le fuseau horaire de la session en cours (par défaut, UTC). 		CURRENT_DATE()

Remarque : Renvoie la date de début de l'instruction en cours dans le fuseau horaire UTC.
DATE_CMP(date1, date2) CASE
WHEN date1 = date2 THEN 0
WHEN date1 > date2 THEN 1
ELSE -1
END
DATE_CMP_TIMESTAMP(date1, date2) CASE
WHEN date1 = CAST(date2 AS DATE)
THEN 0
WHEN date1 > CAST(date2 AS DATE)
THEN 1
ELSE -1
END
DATE_CMP_TIMESTAMPTZ(date, timestamptz) CASE
WHEN date > DATE(timestamptz)
THEN 1
WHEN date < DATE(timestamptz)
THEN -1
ELSE 0
END
DATE_PART_YEAR(date) EXTRACT(YEAR FROM date)
DATEADD(date_part, interval, date) CAST(
DATE_ADD(
date,
INTERVAL interval datepart
)
AS TIMESTAMP
)
DATEDIFF(
date_part,
date_expression1,
date_expression2
) 		DATE_DIFF(
date_expression1,
date_expression2,
date_part
)
DATE_PART(date_part, date) EXTRACT(date_part FROM date)
DATE_TRUNC('date_part', timestamp) TIMESTAMP_TRUNC(timestamp, date_part)
EXTRACT(date_part FROM timestamp) EXTRACT(date_part FROM timestamp)
GETDATE() PARSE_TIMESTAMP(
"%c",
FORMAT_TIMESTAMP(
"%c",
CURRENT_TIMESTAMP()
)
)
INTERVAL_CMP(
interval_literal1,
interval_literal2
) 		Pour les intervalles dans Redshift, une année compte 360 jours. Dans BigQuery, vous pouvez utiliser la fonction définie par l'utilisateur suivante pour analyser un intervalle Redshift et le traduire en secondes.

CREATE TEMP FUNCTION
parse_interval(interval_literal STRING) AS (
(select sum(case
when unit in ('minutes', 'minute', 'm' )
then num * 60
when unit in ('hours', 'hour', 'h') then num
* 60 * 60
when unit in ('days', 'day', 'd' ) then num
* 60 * 60 * 24
when unit in ('weeks', 'week', 'w') then num
* 60 * 60 * 24 * 7
when unit in ('months', 'month' ) then num *
60 * 60 * 24 * 30
when unit in ('years', 'year') then num * 60
* 60 * 24 * 360
else num
end)
from (
select
cast(regexp_extract(value,
r'^[0-9]*\.?[0-9]+') as numeric) num,
substr(value, length(regexp_extract(value,
r'^[0-9]*\.?[0-9]+')) + 1) unit
from
UNNEST(
SPLIT(
replace(
interval_literal, ' ', ''), ',')) value
)));

Pour comparer des littéraux d'intervalle, procédez comme suit :

IF(
parse_interval(interval_literal1) >
parse_interval(interval_literal2),
1,
IF(
parse_interval(interval_literal1) >
parse_interval(interval_literal2),
-1,
0
)
)
LAST_DAY(date) DATE_SUB(
DATE_ADD(
date,
INTERVAL 1 MONTH
),
INTERVAL 1 DAY
)
MONTHS_BETWEEN(
date1,
date2
) 		DATE_DIFF(
date1,
date2,
MONTH
)
NEXT_DAY(date, day) DATE_ADD(
DATE_TRUNC(
date,
WEEK(day)
),
INTERVAL 1 WEEK
)
SYSDATE

Remarque : Renvoie l'horodatage de début pour la transaction en cours dans le fuseau horaire de la session en cours (UTC par défaut). 		CURRENT_TIMESTAMP()

Remarque : Renvoie l'horodatage de début pour l'instruction en cours dans le fuseau horaire UTC.
TIMEOFDAY() FORMAT_TIMESTAMP(
"%a %b %d %H:%M:%E6S %E4Y %Z",
CURRENT_TIMESTAMP())
TIMESTAMP_CMP(
timestamp1,
timestamp2
)		 CASE
WHEN timestamp1 = timestamp2
THEN 0
WHEN timestamp1 > timestamp2
THEN 1
ELSE -1
END
TIMESTAMP_CMP_DATE(
timestamp,
date
) 		CASE
WHEN
EXTRACT(
DATE FROM timestamp
) = date
THEN 0
WHEN
EXTRACT(
DATE FROM timestamp) > date
THEN 1
ELSE -1
END
TIMESTAMP_CMP_TIMESTAMPTZ(
timestamp,
timestamptz
)

Remarque : Redshift compare les horodatages dans le fuseau horaire défini par la session utilisateur. Le fuseau horaire par défaut d'une session utilisateur est UTC. 		CASE
WHEN timestamp = timestamptz
THEN 0
WHEN timestamp > timestamptz
THEN 1
ELSE -1
END

Remarque : BigQuery compare les horodatages dans le fuseau horaire UTC.
TIMESTAMPTZ_CMP(
timestamptz1,
timestamptz2
)

Remarque : Redshift compare les horodatages dans le fuseau horaire défini par la session utilisateur. Le fuseau horaire par défaut d'une session utilisateur est UTC. 		CASE
WHEN timestamptz1 = timestamptz2
THEN 0
WHEN timestamptz1 > timestamptz2
THEN 1
ELSE -1
END

Remarque : BigQuery compare les horodatages dans le fuseau horaire UTC.
TIMESTAMPTZ_CMP_DATE(
timestamptz,
date
)

Remarque : Redshift compare les horodatages dans le fuseau horaire défini par la session utilisateur. Le fuseau horaire par défaut d'une session utilisateur est UTC. 		CASE
WHEN
EXTRACT(
DATE FROM timestamptz) = date
THEN 0
WHEN
EXTRACT(
DATE FROM timestamptz) > date
THEN 1
ELSE -1
END

Remarque : BigQuery compare les horodatages dans le fuseau horaire UTC.
TIMESTAMPTZ_CMP_TIMESTAMP(
timestamptz,
Timestamp
)

Remarque : Redshift compare les horodatages dans le fuseau horaire défini par la session utilisateur. Le fuseau horaire par défaut d'une session utilisateur est UTC. 		CASE
WHEN timestamp = timestamptz
THEN 0
WHEN timestamp > timestamptz
THEN 1
ELSE -1
END

Remarque : BigQuery compare les horodatages dans le fuseau horaire UTC.
TIMEZONE(
timezone,
Timestamptz_or_timestamp
) 		PARSE_TIMESTAMP(
"%c%z", FORMAT_TIMESTAMP(
"%c%z",
timestamptz_or_timestamp,
timezone
)
)

Remarque : Les fuseaux horaires sont utilisés lors de l'analyse ou du formatage des horodatages à des fins d'affichage. Un horodatage au format de chaîne peut inclure un fuseau horaire, mais lorsque BigQuery analyse la chaîne, il stocke l'horodatage dans l'heure UTC équivalente. Lorsqu'un fuseau horaire n'est pas spécifié explicitement, le fuseau horaire par défaut est utilisé (UTC). Les noms de fuseau horaire ou les décalages par rapport à l'heure UTC (-HH:MM) sont acceptés, mais les abréviations de fuseau horaire (telles que PDT) ne le sont pas.
TO_TIMESTAMP(timestamp, format) PARSE_TIMESTAMP(
format,
FORMAT_TIMESTAMP(
format,
timestamp
)
)

Remarque : BigQuery applique un ensemble différent d'éléments de format. Les fuseaux horaires sont utilisés lors de l'analyse ou du formatage des horodatages à des fins d'affichage. Un horodatage au format de chaîne peut inclure un fuseau horaire, mais lorsque BigQuery analyse la chaîne, il stocke l'horodatage dans l'heure UTC équivalente. Lorsqu'un fuseau horaire n'est pas spécifié explicitement, le fuseau horaire par défaut est utilisé (UTC). Les noms de fuseau horaire ou les décalages par rapport à l'heure UTC (-HH:MM) sont acceptés dans la chaîne de format, mais les abréviations de fuseau horaire (telles que PDT) ne le sont pas.
TRUNC(timestamp) CAST(timestamp AS DATE)

BigQuery propose également les fonctions de date et heure suivantes, qui n'ont pas d'équivalent direct dans Amazon Redshift :

Opérateurs mathématiques

Le tableau suivant présente les mappages des opérateurs mathématiques courants d'Amazon Redshift avec leurs équivalents dans BigQuery.

Amazon Redshift BigQuery

X + Y

X + Y

X - Y

X - Y

X * Y

X * Y

X / Y


Remarque : Si l'opérateur
effectue une division de nombres entiers (en d'autres termes, si X et Y sont tous les deux des entiers), un entier est renvoyé. Si l'opérateur effectue une division de nombres non-entiers, un non-entier est renvoyé.		 En cas de division de nombres entiers :
CAST(FLOOR(X / Y) AS INT64)

En cas de division de nombres non-entiers :

CAST(X / Y AS INT64)


Remarque : La division dans BigQuery renvoie un nombre non-entier.
Pour éviter les erreurs dues à une opération de division (erreur de division par zéro), utilisez SAFE_DIVIDE(X, Y) ou IEEE_DIVIDE(X, Y).

X % Y

MOD(X, Y)


Remarque : Pour éviter les erreurs dues à une opération de division (division par zéro), utilisez SAFE.MOD(X, Y). SAFE.MOD(X, 0) fournit un résultat égal à 0.

X ^ Y

POW(X, Y)

POWER(X, Y)


Remarque : Contrairement à celui d'Amazon Redshift, l'opérateur ^ de BigQuery effectue une opération xor bit à bit.

| / X

SQRT(X)


Remarque : Pour éviter les erreurs dues à une opération de racine carrée (entrée négative), utilisez SAFE.SQRT(X). L'utilisation d'entrées négatives avec SAFE.SQRT(X) fournit un résultat de NULL.

|| / X

SIGN(X) * POWER(ABS(X), 1/3)


Remarque : La fonction POWER(X, Y) de BigQuery renvoie une erreur si la valeur de X est inférieure à 0 et que Y est une valeur non entière.

@ X

ABS(X)

X << Y

X << Y


Cet opérateur renvoie 0 ou une séquence d'octets de b'\x00' si le deuxième opérande Y est supérieur ou égal à la longueur en bits du premier opérande X (par exemple, 64 si X est de type INT64). Cet opérateur génère une erreur si Y est négatif.

X >> Y

X >> Y


Remarque : Décale le premier opérande X vers la droite. Cet opérateur n'effectue pas d'extension de bit de signe avec un type signé (il remplit les bits vacants à gauche avec des 0). Cet opérateur renvoie 0 ou une séquence d'octets de
b'\x00' si le deuxième opérande Y est supérieur ou égal à la longueur en bits du premier opérande X (par exemple, 64 si X est de type INT64). Cet opérateur génère une erreur si Y est négatif.

X & Y

X & Y

X | Y

X | Y

~X

~X

BigQuery propose également l'opérateur mathématique suivant, qui ne dispose pas d'un équivalent direct dans Amazon Redshift :

  • X ^ Y (au niveau du bit xor)

Fonctions mathématiques

Amazon Redshift BigQuery
ABS(number) ABS(number)
ACOS(number) ACOS(number)
ASIN(number) ASIN(number)
ATAN(number) ATAN(number)
ATAN2(number1, number2) ATAN2(number1, number2)
CBRT(number) POWER(number, 1/3)
CEIL(number) CEIL(number)
CEILING(number) CEILING(number)
CHECKSUM(expression) FARM_FINGERPRINT(expression)
COS(number) COS(number)
COT(number) 1/TAN(number)
DEGREES(number) number*180/ACOS(-1)
DEXP(number) EXP(number)
DLOG1(number) LN(number)
DLOG10(number) LOG10(number)
EXP(number) EXP(number)
FLOOR(number) FLOOR(number)
LNnumber) LN(number)
LOG(number) LOG10(number)
MOD(number1, number2) MOD(number1, number2)
PI ACOS(-1)
POWER(expression1, expression2) POWER(expression1, expression2)
RADIANS(number) ACOS(-1)*(number/180)
RANDOM() RAND()
ROUND(number [, integer]) ROUND(number [, integer])
SIN(number) SIN(number)
SIGN(number) SIGN(number)
SQRT(number) SQRT(number)
TAN(number) TAN(number)
TO_HEX(number) FORMAT('%x', number)
TRUNC(number [, integer])+-+++ TRUNC(number [, integer])

Fonctions de chaîne

Amazon Redshift BigQuery
string1 || string2 CONCAT(string1, string2)
BPCHARCMP(string1, string2) CASE
WHEN string1 = string2 THEN 0
WHEN string1 > string2 THEN 1
ELSE -1
END
BTRIM(string [, matching_string]) TRIM(string [, matching_string])
BTTEXT_PATTERN_CMP(string1, string2) CASE
WHEN string1 = string2 THEN 0
WHEN string1 > string2 THEN 1
ELSE -1
END
CHAR_LENGTH(expression) CHAR_LENGTH(expression)
CHARACTER_LENGTH(expression) CHARACTER_LENGTH(expression)
CHARINDEX(substring, string) STRPOS(string, substring)
CHR(number) CODE_POINTS_TO_STRING([number])
CONCAT(string1, string2) CONCAT(string1, string2)

Remarque : La fonction CONCAT(...) de BigQuery accepte la concaténation de
n'importe quel nombre de chaînes.
CRC32 Fonction définie par l'utilisateur personnalisée.
FUNC_SHA1(string) SHA1(string)
INITCAP INITCAP
LEFT(string, integer) SUBSTR(string, 0, integer)
RIGHT(string, integer) SUBSTR(string, -integer)
LEN(expression) LENGTH(expression)
LENGTH(expression) LENGTH(expression)
LOWER(string) LOWER(string)
LPAD(string1, length[, string2]) LPAD(string1, length[, string2])
RPAD(string1, length[, string2]) RPAD(string1, length[, string2])
LTRIM(string, trim_chars) LTRIM(string, trim_chars)
MD5(string) MD5(string)
OCTET_LENGTH(expression) BYTE_LENGTH(expression)
POSITION(substring IN string) STRPOS(string, substring)
QUOTE_IDENT(string) CONCAT('"',string,'"')
QUOTE_LITERAL(string) CONCAT("'",string,"'")
REGEXP_COUNT( source_string, pattern
[,position]
) 		ARRAY_LENGTH( REGEXP_EXTRACT_ALL(
source_string,
pattern
)
)

Si position est spécifié :

ARRAY_LENGTH( REGEXP_EXTRACT_ALL(
SUBSTR(source_string, IF(position <= 0, 1, position)),
pattern
)
)

Remarque : La bibliothèque re2 permet l'utilisation d'expressions régulières dans BigQuery. Consultez la documentation pour découvrir la syntaxe d'expression régulière à utiliser.
REGEXP_INSTR(
source_string,
pattern
[,position
[,occurrence]] ) 		IFNULL( STRPOS(
source_string, REGEXP_EXTRACT(
source_string,
pattern)
),0)

Si source_string est spécifié:

REGEXP_REPLACE(
source_string,

pattern,
replace_string
)

Si position est spécifié:

IFNULL( STRPOS(
SUBSTR(source_string, IF(position
<= 0, 1, position)), REGEXP_EXTRACT(
SUBSTR(source_string, IF(position <= 0, 1, position)),
pattern)
) + IF(position <= 0, 1, position) - 1, 0)

Si occurrence est spécifié:

IFNULL( STRPOS(
SUBSTR(source_string, IF(position
<= 0, 1, position)), REGEXP_EXTRACT_ALL(
SUBSTR(source_string, IF(position <= 0, 1, position)),
pattern
)[SAFE_ORDINAL(occurrence)]
) + IF(position <= 0, 1, position) - 1, 0)

Remarque: BigQuery fournit un support de l'expression régulière
en utilisant la bibliothèque re2 ; voir la documentation
de la syntaxe de son expression régulière
.
REGEXP_REPLACE( source_string,
pattern
[, replace_string [, position]]
) 		REGEXP_REPLACE(
source_string,
pattern,
""
)

Si source_string est spécifié:

REGEXP_REPLACE(
source_string,

pattern, replace_string
)

Si position est spécifié:

CASE
WHEN position > LENGTH(source_string) THEN source_string
WHEN position <= 0 THEN REGEXP_REPLACE(
source_string, pattern,
""
) ELSE
CONCAT( SUBSTR(
source_string, 1, position - 1), REGEXP_REPLACE(
SUBSTR(source_string, position), pattern,
replace_string
)
) END
REGEXP_SUBSTR( source_string, pattern
[, position
[, occurrence]] ) 		REGEXP_EXTRACT(
source_string, pattern
)

Si position est spécifié:

REGEXP_EXTRACT(
SUBSTR(source_string, IF(position <= 0, 1, position)),
pattern

)

Si occurrence est spécifié :

REGEXP_EXTRACT_ALL(
SUBSTR(source_string, IF(position <= 0, 1, position)),


pattern
)[SAFE_ORDINAL(occurrence)]

Remarque: BigQuery fournit un support de l'expression régulière en utilisant la bibliothèque re2 ; voir la documentation de la syntaxe de son expression régulière.
REPEAT(string, integer) REPEAT(string, integer)
REPLACE(string, old_chars, new_chars) REPLACE(string, old_chars, new_chars)
REPLICA(string, integer) REPEAT(string, integer)
REVERSE(expression) REVERSE(expression)
RTRIM(string, trim_chars) RTRIM(string, trim_chars)
SPLIT_PART(string, delimiter, part) SPLIT(
string
delimiter
)SAFE_ORDINAL(part)
STRPOS(string, substring) STRPOS(string, substring)
STRTOL(string, base)
SUBSTRING(
string,
start_position, number_characters ) 		SUBSTR(
string,
start_position, number_characters )
TEXTLEN(expression) LENGTH(expression)
TRANSLATE(
expression,
characters_to_replace, characters_to_substitute ) 		Peut être mis en œuvre à l'aide des fonctions définies par l'utilisateur :

CREATE TEMP FUNCTION
translate(expression STRING,
characters_to_replace STRING, characters_to_substitute STRING) AS ( IF(LENGTH(characters_to_replace) < LENGTH(characters_to_substitute) OR LENGTH(expression) <
LENGTH(characters_to_replace), expression,
(SELECT
STRING_AGG(
IFNULL(
(SELECT ARRAY_CONCAT([c],
SPLIT(characters_to_substitute, ''))[SAFE_OFFSET((
SELECT IFNULL(MIN(o2) + 1,
0) FROM
UNNEST(SPLIT(characters_to_replace,
'')) AS k WITH OFFSET o2
WHERE k = c))]
),
''),
'' ORDER BY o1)
FROM UNNEST(SPLIT(expression, ''))
AS c WITH OFFSET o1
))
);
TRIM([BOTH] string) TRIM(string)
TRIM([BOTH] characters FROM string) TRIM(string, characters)
UPPER(string) UPPER(string)

Fonctions de mise en forme des types de données

Amazon Redshift BigQuery
CAST(expression AS type) CAST(expression AS type)
expression :: type CAST(expression AS type)
CONVERT(type, expression) CAST(expression AS type)
TO_CHAR(
timestamp_expression, format
)		 FORMAT_TIMESTAMP(
format,
timestamp_expression
)

Remarque : La spécification d'une chaîne de format pour timestamp_expression diffère dans BigQuery et Amazon Redshift.
TO_CHAR(
numeric_expression,
format
)		 FORMAT(
format,
numeric_expression
)

Remarque : La spécification d'une chaîne de format pour timestamp_expression diffère dans BigQuery et Amazon Redshift.
TO_DATE(date_string, format) PARSE_DATE(date_string, format)

Remarque : La spécification d'une chaîne de format pour date_string diffère dans BigQuery et Amazon Redshift.
TO_NUMBER(string, format) CAST(
FORMAT(
format,
numeric_expression
) TO INT64
)

Remarque : La spécification d'une chaîne de format numérique diffère dans BigQuery et Amazon Redshift.

SAFE_CAST("apple" AS INT64) renvoie NULL. SAFE_CAST(expression AS typename)NULL

Syntaxe LMD

Cette section traite des différences de syntaxe du langage de manipulation de données entre Amazon Redshift et BigQuery.

Instruction INSERT

Amazon Redshift propose un mot clé DEFAULT configurable pour les colonnes. Dans BigQuery, la valeur DEFAULT des colonnes pouvant être vides est NULL, et DEFAULT n'est pas accepté pour les colonnes obligatoires. La plupart des instructions INSERT d'Amazon Redshift sont compatibles avec BigQuery. Le tableau suivant présente les exceptions.

Amazon Redshift BigQuery
INSERT INTO table (column1 [, ...])
DEFAULT VALUES		 INSERT [INTO] table (column1 [, ...])
VALUES (DEFAULT [, ...])
INSERT INTO table (column1, [,...]) VALUES (
SELECT ...
FROM ...
)		 INSERT [INTO] table (column1, [,...])
SELECT ...
FROM ...

BigQuery permet également d'insérer des valeurs à l'aide d'une sous-requête (où l'une des valeurs est calculée à l'aide d'une sous-requête), ce qui n'est pas compatible avec Amazon Redshift. Exemple :

INSERT INTO table (column1, column2)
VALUES ('value_1', (
SELECT column2
FROM table2
))

Instruction COPY

La commande COPY d'Amazon Redshift charge des données dans une table à partir de fichiers de données ou d'une table Amazon DynamoDB. BigQuery n'utilise pas la commande SQL COPY pour charger des données, mais vous pouvez utiliser l'un des outils et options non-SQL pour charger des données dans des tables BigQuery. Vous pouvez également utiliser les récepteurs de pipelines de données fournis dans Apache Spark ou Apache Beam pour écrire des données dans BigQuery.

Instruction UPDATE

La plupart des instructions UPDATE d'Amazon Redshift sont compatibles avec BigQuery. Le tableau suivant présente les exceptions.

Amazon Redshift BigQuery
UPDATE table
SET column = expression [,...] [FROM ...] 		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.
UPDATE table
SET column = DEFAULT [,...] [FROM ...]
[WHERE ...]		 UPDATE table
SET column = NULL [, ...]
[FROM ...]
WHERE ...

Remarque : La commande UPDATE de BigQuery n'est pas compatible avec les valeurs DEFAULT.

Si l'instruction Amazon Redshift UPDATE n'inclut pas de clause WHERE, l'instruction BigQuery UPDATE doit être conditionnée pour WHERE TRUE.

Instructions DELETE et TRUNCATE

Les instructions DELETE et TRUNCATE permettent de supprimer des lignes d'une table sans que cela n'affecte son schéma ou ses index.

Dans Amazon Redshift, l'instruction TRUNCATE est recommandée plutôt qu'une instruction DELETE non qualifiée, car elle est plus rapide et ne nécessite pas d'opérations VACUUM et ANALYZE par la suite. Cependant, vous pouvez utiliser les instructions DELETE pour obtenir le même effet.

Dans BigQuery, l'instruction DELETE doit comporter une clause WHERE. Pour en savoir plus sur l'instruction DELETE dans BigQuery, consultez les exemples d'instructions DELETE BigQuery dans la documentation sur le LMD.

Amazon Redshift BigQuery
DELETE [FROM] table_name

TRUNCATE [TABLE] table_name		 DELETE FROM table_name
WHERE TRUE

Les instructions BigQuery DELETE nécessitent une clause WHERE.
DELETE FROM table_name
USING other_table
WHERE table_name.id=other_table.id		 DELETE FROM table_name
WHERE table_name.id IN (
SELECT id
FROM other_table
)

DELETE FROM table_name
WHERE EXISTS (
SELECT id
FROM other_table
WHERE table_name.id = other_table.id )

Dans Amazon Redshift, USING permet de référencer des tables supplémentaires dans la clause WHERE. Vous pouvez effectuer cette opération dans BigQuery à l'aide d'une sous-requête dans la clause WHERE.

Instruction MERGE

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

Amazon Redshift ne peut pas prendre en charge une seule commande MERGE. Toutefois, une opération de fusion peut être effectuée dans Amazon Redshift en mettant en œuvre des opérations INSERT, UPDATE et DELETE dans une même transaction.

Opération de fusion en remplaçant les lignes existantes

Dans Amazon Redshift, vous pouvez effectuer un écrasement de toutes les colonnes de la table cible à l'aide d'une instruction DELETE, puis d'une instruction INSERT. L'instruction DELETE supprime les lignes qui doivent être mises à jour, puis l'instruction INSERT insère les lignes mises à jour. Les tables BigQuery sont limitées à 1 000 instructions LMD par jour. Vous devez donc regrouper les instructions INSERT, UPDATE et DELETE dans une même instruction MERGE, comme indiqué dans le tableau suivant.

Amazon Redshift BigQuery
Consultez la section Effectuer une opération de fusion en remplaçant les lignes existantes.

CREATE TEMP TABLE temp_table;

INSERT INTO temp_table
SELECT *
FROM source
WHERE source.filter = 'filter_exp';

BEGIN TRANSACTION;

DELETE FROM target
USING temp_table
WHERE target.key = temp_table.key;

INSERT INTO target
SELECT *
FROM temp_table;

END TRANSACTION;

DROP TABLE temp_table; 		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 : Toutes les colonnes doivent être répertoriées si vous souhaitez mettre à jour toutes les colonnes.
Consultez la section Effectuer une opération de fusion en spécifiant une liste de colonnes.

CREATE TEMP TABLE temp_table;

INSERT INTO temp_table
SELECT *
FROM source
WHERE source.filter = 'filter_exp';

BEGIN TRANSACTION;

UPDATE target SET
col1 = temp_table.col1,
col2 = temp_table.col2
FROM temp_table
WHERE target.key=temp_table.key;

INSERT INTO target
SELECT *
FROM 		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

Syntaxe LDD

Cette section traite des différences de syntaxe du langage de définition de données entre Amazon Redshift et BigQuery.

Instruction SELECT INTO

Dans Amazon Redshift, vous pouvez insérer les résultats d'une requête dans une nouvelle table à l'aide de l'instruction SELECT INTO, en combinant la création de table et l'insertion.

Amazon Redshift BigQuery
SELECT expression, ... INTO table
FROM ... 		INSERT table
SELECT expression, ...
FROM ...
WITH subquery_table AS ( SELECT ...
)
SELECT expression, ... INTO table
FROM subquery_table
... 		INSERT table
WITH subquery_table AS (
SELECT ...
)
SELECT expression, ...
FROM subquery_table
...
SELECT expression
INTO TEMP table
FROM ...

SELECT expression
INTO TEMPORARY table
FROM ... 		BigQuery propose plusieurs méthodes pour émuler des tables temporaires. Pour plus d'informations, consultez la section Tables temporaires.

Instruction CREATE TABLE

La plupart des instructions CREATE TABLE Amazon Redshift sont compatibles avec BigQuery, à l'exception des éléments de syntaxe suivants, qui ne sont pas utilisés dans BigQuery :

Amazon Redshift BigQuery
CREATE TABLE table_name (
col1 data_type1 NOT NULL,
col2 data_type2 NULL,
col3 data_type3 UNIQUE,
col4 data_type4 PRIMARY KEY,
col5 data_type5
)

Remarque : Les contraintes UNIQUE et PRIMARY KEY sont informatives et ne sont pas appliquées par le système Amazon Redshift.		 CREATE TABLE table_name (
col1 data_type1 NOT NULL,
col2 data_type2,
col3 data_type3,
col4 data_type4,
col5 data_type5,
)
CREATE TABLE table_name
(
col1 data_type1[,...]
table_constraints
)
where table_constraints are:
[UNIQUE(column_name [, ... ])]
[PRIMARY KEY(column_name [, ...])]
[FOREIGN KEY(column_name [, ...])
REFERENCES reftable [(refcolumn)]

Remarque : Les contraintes UNIQUE et PRIMARY KEY sont informatives et ne sont pas appliquées par le système Amazon Redshift. 		CREATE TABLE table_name
(
col1 data_type1[,...]
)
PARTITION BY column_name
CLUSTER BY column_name [, ...]

Remarque : BigQuery n'utilise pas les contraintes de table UNIQUE, PRIMARY KEY ou FOREIGN KEY. Pour obtenir une optimisation similaire à celle fournie par ces contraintes lors de l'exécution des requêtes, partitionnez et mettez en cluster vos tables BigQuery. CLUSTER BY accepte jusqu'à quatre colonnes.
CREATE TABLE table_name
LIKE original_table_name 		Consultez cet exemple pour apprendre à utiliser les tables INFORMATION_SCHEMA pour copier les noms de colonnes, les types de données et les contraintes NOT NULL vers une nouvelle table.
CREATE TABLE table_name
(
col1 data_type1
)
BACKUP NO

Remarque : Dans Amazon Redshift, le paramètre BACKUP NO est spécifié pour gagner du temps de traitement et réduire l'espace de stockage.		 L'option de table BACKUP NO n'est pas utilisée ou nécessaire, car BigQuery conserve automatiquement jusqu'à sept jours de versions historiques de toutes vos tables, sans effet sur le temps de traitement ou le stockage facturé.
CREATE TABLE table_name
(
col1 data_type1
)
table_attributes
where table_attributes are:
[DISTSTYLE {AUTO|EVEN|KEY|ALL}]
[DISTKEY (column_name)]
[[COMPOUND|INTERLEAVED] SORTKEY
(column_name [, ...])] 		BigQuery accepte le clustering, ce qui permet de stocker les clés dans un ordre trié.
CREATE TABLE table_name
AS SELECT ... 		CREATE TABLE table_name
AS SELECT ...
CREATE TABLE IF NOT EXISTS table_name ... CREATE TABLE IF NOT EXISTS
table_name
...

BigQuery est également compatible avec l'instruction LDD CREATE OR REPLACE TABLE, qui écrase une table si elle existe déjà.

L'instruction BigQuery CREATE TABLE accepte également les clauses suivantes, qui n'ont pas d'équivalent dans Amazon Redshift :

Pour en savoir plus sur l'instruction CREATE TABLE dans BigQuery, consultez les exemples d'instructions CREATE TABLE BigQuery dans la documentation sur le LMD.

Tables temporaires

Amazon Redshift est compatible avec les tables temporaires, qui ne sont visibles que dans la session en cours. Il existe plusieurs façons d'émuler des tables temporaires dans BigQuery :

  • Valeur TTL de l'ensemble de données : créez un ensemble de données ayant une durée de vie courte (par exemple, 1 heure) afin que les tables créées dans cet ensemble de données soient effectivement temporaires, car elles ne persisteront pas au-delà de la durée de vie d'un script. Vous pouvez ajouter le préfixe à tous les noms de tables de cet ensemble de données pour indiquer clairement que les tables sont temporaires.

  • Valeur TTL de la table : créez une table ayant une durée de vie courte à l'aide d'instructions LDD semblables à ce qui suit :

    CREATE TABLE
temp.name (col1, col2, ...)
OPTIONS (expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),
INTERVAL 1 HOUR));

Instruction CREATE VIEW

Le tableau suivant présente les équivalences entre Amazon Redshift et BigQuery pour l'instruction CREATE VIEW.

Amazon Redshift BigQuery
CREATE VIEW view_name AS SELECT ...code> CREATE VIEW view_name AS SELECT ...
CREATE OR REPLACE VIEW view_name AS SELECT ... CREATE OR REPLACE VIEW
view_name AS
SELECT ...
CREATE VIEW view_name
(column_name, ...)
AS SELECT ... 		CREATE VIEW view_name AS SELECT ...
Non compatible. CREATE VIEW IF NOT EXISTS c view_name
OPTIONS(view_option_list)
AS SELECT …

Crée une vue uniquement si la vue n'existe pas dans l'ensemble de données spécifié.
CREATE VIEW view_name
AS SELECT ...
WITH NO SCHEMA BINDING

Dans Amazon Redshift, une vue à liaison tardive est requise pour référencer une table externe.		 Dans BigQuery, tous les objets référencés doivent déjà exister pour créer une vue.

BigQuery vous permet d'interroger des sources de données externes.

Fonctions définies par l'utilisateur

Les fonctions définies par l'utilisateur (UDF) vous permettent de créer des fonctions pour des opérations personnalisées. Ces fonctions acceptent des colonnes d'entrée, effectuent des actions et renvoient le résultat de ces dernières sous la forme d'une valeur.

Amazon Redshift et BigQuery sont tous les deux compatibles avec les fonctions définies par l'utilisateur utilisant des expressions SQL. De plus, vous pouvez créer une UDF basée sur Python dans Amazon Redshift ou créer une UDF basée sur JavaScript dans BigQuery.

Reportez-vous au dépôt GitHub des utilitaires Google Cloud BigQuery pour obtenir une bibliothèque de fonctions BigQuery définies par l'utilisateur.

Syntaxe de CREATE FUNCTION

Le tableau suivant traite des différences de syntaxe de création d'UDF (fonctions définies par l'utilisateur) SQL entre Amazon Redshift et BigQuery.

Amazon Redshift BigQuery
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
IMMUTABLE
AS $$
sql_function_definition
$$ LANGUAGE sql 		CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) AS
sql_function_definition

Remarque : Dans une UDF SQL BigQuery, le type de données renvoyé est facultatif. BigQuery déduit le type renvoyé par votre fonction à partir du corps de la fonction SQL lorsqu'elle est appelée par une requête.
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE } AS $$
sql_function_definition
$$ LANGUAGE sql 		CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
AS sql_function_definition

Remarque : La volatilité des fonctions n'est pas un paramètre configurable dans BigQuery. Toute volatilité d'UDF BigQuery est équivalente à la volatilité IMMUTABLE d'Amazon Redshift (c'est-à-dire qu'elle n'effectue pas de recherches de base de données et n'utilise pas d'autres informations que celles directement présentes dans sa liste d'arguments).
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
IMMUTABLE
AS $$
SELECT_clause
$$ LANGUAGE sql

Remarque : Amazon Redshift ne prend en charge que les clauses SQL SELECT comme définition de fonction. De plus, la clause SELECT ne peut inclure aucune de clauses FROM, INTO, WHERE, GROUP BY, ORDER BY, et LIMIT. 		CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
AS sql_expression

Remarque : BigQuery prend en charge toutes les expressions SQL comme définition de fonction. Il n'est toutefois pas possible de référencer des tables, des vues ou des modèles.
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
IMMUTABLE
AS $$
sql_function_definition
$$ LANGUAGE sql 		CREATE [OR REPLACE] FUNCTION function_name ([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type AS sql_function_definition

Remarque : Il n'est pas nécessaire de spécifier un littéral de langage dans une UDF en langage GoogleSQL. BigQuery interprète l'expression SQL par défaut. En outre, les guillemets dollars ($$) is not supported in BigQuery.
CREATE [OR REPLACE] FUNCTION function_name (integer, integer) RETURNS integer IMMUTABLE AS $$ SELECT $1 + $2 $$ LANGUAGE sql CREATE [OR REPLACE] FUNCTION
function_name
(x INT64, y INT64)
RETURNS INT64
AS
SELECT x + y

Note: BigQuery UDFs require all input arguments to be named. The Amazon Redshift argument variables ($1, $2, …) are not supported in BigQuery.
CREATE [OR REPLACE] FUNCTION
function_name
(integer, integer)
RETURNS integer
IMMUTABLE
AS $$
SELECT $1 + $2
$$ LANGUAGE sql

Note: Amazon Redshift does not support ANY TYPE for SQL UDFs. However, it supports using the ANYELEMENT data type in Python-based UDFs. 		CREATE [OR REPLACE] FUNCTION
function_name
(x ANY TYPE, y ANY TYPE)
AS
SELECT x + y

Note: BigQuery supports using ANY TYPE as argument type. The function accepts an input of any type for this argument. For more information, see templated parameter in BigQuery.

BigQuery also supports the CREATE FUNCTION IF NOT EXISTS statement, which treats the query as successful and takes no action if a function with the same name already exists.

BigQuery's CREATE FUNCTION statement also supports creating TEMPORARY or TEMP functions, which do not have an Amazon Redshift equivalent.

See calling UDFs for details on executing a BigQuery-persistent UDF.

DROP FUNCTION syntax

The following table addresses differences in DROP FUNCTION syntax between Amazon Redshift and BigQuery.

Amazon Redshift BigQuery
DROP FUNCTION
function_name
( [arg_name] arg_type [, ...] ) [ CASCADE | RESTRICT ]		 DROP FUNCTION
dataset_name.function_name

Note: BigQuery does not require using the function's signature for deleting the function. Also, removing function dependencies is not supported in BigQuery.

BigQuery also supports the DROP FUNCTION IF EXISTS statement, which deletes the function only if the function exists in the specified dataset.

BigQuery requires that you specify the project_name if the function is not located in the current project.

UDF components

This section highlights the similarities and differences in UDF components between Amazon Redshift andBigQuery.

Component Amazon Redshift BigQuery
Name Amazon Redshift recommends using the prefix _f for function names to avoid conflicts with existing or future built-in SQL function names. In BigQuery, you can use any custom function name.
Arguments Arguments are optional. You can use name and data types for Python UDF arguments and only data types for SQL UDF arguments. In SQL UDFs, you must refer to arguments using $1, $2, and so on. Amazon Redshift also restricts the number of arguments to 32. Arguments are optional, but if you specify arguments, they must use both name and data types for both JavaScript and SQL UDFs. The maximum number of arguments for a persistent UDF is 256.
Data type Amazon Redshift supports a different set of data types for SQL and Python UDFs.
For a Python UDF, the data type might also be ANYELEMENT.

You must specify a RETURN data type for both SQL and Python UDFs.

See Data types in this document for equivalents between data types in Amazon Redshift and in BigQuery.		 BigQuery supports a different set of data types for SQL and JavaScript UDFs.
For a SQL UDF, the data type might also be ANY TYPE. For more information, see templated parameters in BigQuery.

The RETURN data type is optional for SQL UDFs.

See SQL type encodings in JavaScript for information on how BigQuery data types map to JavaScript data types.
Definition For both SQL and Python UDFs, you must enclose the function definition using dollar quoting, as in a pair of dollar signs ($$) d'Amazon Redshift pour indiquer le début et la fin des instructions de la fonction.

Pour les fonctions SQL définies par l'utilisateur, Amazon Redshift ne prend en charge qu'une clause SQL SELECT en tant que définition de fonction. De plus, la clause SELECT ne peut inclure aucune des clauses FROM, INTO, WHERE, GROUP
BY, ORDER BY et LIMIT .

Pour les fonctions Python définies par l'utilisateur, vous pouvez écrire un programme Python à l'aide de la bibliothèque standard Python 2.7 ou importer vos modules personnalisés en en créant un à l'aide de la commande CREATE LIBRARY .		 Dans BigQuery, vous devez placer le code JavaScript entre guillemets. Consultez les règles de citation pour en savoir plus.

Pour les UDF SQL, vous pouvez utiliser n'importe quelle expression SQL comme définition de fonction. Cependant, BigQuery ne permet pas de référencer des tables, des vues ou des modèles.

Pour les UDF JavaScript, vous pouvez inclure des bibliothèques de code externes directement en utilisant la section OPTIONS . Vous pouvez également utiliser l'outil de test d'UDF (fonction définie par l'utilisateur) BigQuery pour tester vos fonctions.
Langue Vous devez utiliser la valeur littérale LANGUAGE pour spécifier le langage en tant que sql pour les fonctions SQL définies par l'utilisateur ou plpythonu pour les fonctions Python définies par l'utilisateur. Vous n'avez pas besoin de spécifier LANGUAGE pour les UDF SQL, mais vous devez spécifier le langage js pour les UDF JavaScript.
État Amazon Redshift ne permet pas de créer des UDF (fonctions définies par l'utilisateur) temporaires.

Amazon Redshift offre une option permettant de définir la volatilité d'une fonction en utilisant des littéraux VOLATILE, STABLE ou IMMUTABLE . L'optimiseur de requête peut ainsi optimiser la requête.		 BigQuery est compatible avec les UDF (fonctions définies par l'utilisateur) persistantes et temporaires. Vous pouvez réutiliser une fonction persistante définie par l'utilisateur dans plusieurs requêtes, alors qu'une fonction temporaire définie par l'utilisateur ne peut être utilisée que dans une seule requête.

La volatilité des fonctions n'est pas un paramètre configurable dans BigQuery. Toute la volatilité des UDF (fonctions définies par l'utilisateur) BigQuery est équivalente à la volatilité IMMUTABLE d'Amazon Redshift.
Sécurité et privilèges Pour créer une UDF (fonction définie par l'utilisateur), vous devez disposer d'une autorisation d'utilisation sur le langage pour SQL ou plpythonu (Python). Par défaut, USAGE ON LANGUAGE SQL est accordé à PUBLIC, mais vous devez explicitement accorder USAGE ON LANGUAGE PLPYTHONU à des utilisateurs ou des groupes spécifiques.
De plus, vous devez être un super-utilisateur pour remplacer une UDF (fonction définie par l'utilisateur).		 Il n'est pas nécessaire d'accorder des autorisations explicites pour créer ou supprimer une UDF de n'importe quel type. Tout utilisateur disposant d'un rôle d'Éditeur de données BigQuery (c'est-à-dire qui comporte bigquery.routines.* parmi ses autorisations) peut créer ou supprimer des fonctions pour l'ensemble de données spécifié.

BigQuery est également compatible avec la création de rôles personnalisés. Vous pouvez gérer ce comportement en utilisant Cloud IAM.
Limites Consultez les limites des fonctions définies par l'utilisateur pour Python. Consultez la section Limites des UDF (fonctions définies par l'utilisateur).

Instructions SQL de métadonnées et de transactions

Amazon Redshift BigQuery
SELECT * FROM STL_ANALYZE WHERE name
= 'T';		 Non utilisé dans BigQuery. Vous n'avez pas besoin de collecter des statistiques afin d'améliorer les performances des requêtes. Pour obtenir des informations sur la distribution des données, vous pouvez utiliser des fonctions d'agrégation approximative.
ANALYZE [[ table_name[(column_name
[, ...])]]		 Non utilisé dans BigQuery.
LOCK TABLE table_name; Non utilisé dans BigQuery.
BEGIN TRANSACTION; SELECT ...
END TRANSACTION;		 BigQuery utilise l'isolation d'instantané. Pour en savoir plus, consultez la section Garanties de cohérence.
EXPLAIN ... Non utilisé dans BigQuery.

L'explication de plan de requête dans la console Google CLoud de BigQuery et la journalisation d'audit dans Cloud Monitoring sont des fonctionnalités similaires.
SELECT * FROM SVV_TABLE_INFO WHERE
table = 'T';		 SELECT * EXCEPT(is_typed) FROM
mydataset.INFORMATION_SCHEMA.TABLES;

Pour en savoir plus, consultez la page Présentation des vues INFORMATION_SCHEMA de BigQuery.
VACUUM [table_name] Non utilisé dans BigQuery. Les tables en cluster BigQuery sont automatiquement triées.

Instructions SQL multi-instructions et multilignes

Amazon Redshift et BigQuery acceptent les transactions (sessions) et donc les instructions séparées par un point-virgule qui sont systématiquement exécutées ensemble. Pour plus d'informations, consultez la section Transactions multi-instructions.

Instructions SQL procédurales

Instruction CREATE PROCEDURE

Amazon Redshift BigQuery
CREATE or REPLACE PROCEDURE CREATE PROCEDURE si un nom est requis.

Sinon, utilisez l'instruction sous le bloc BEGIN ou sur une seule ligne avec CREATE TEMP FUNCTION.
CALL CALL

Déclaration et attribution de variables

Amazon Redshift BigQuery
DECLARE DECLARE

Déclare une variable du type spécifié.
SET SET

Définit une variable comme ayant la valeur de l'expression fournie, ou définit plusieurs variables en même temps en fonction du résultat de plusieurs expressions.

Gestionnaires de conditions d'erreur

Dans Amazon Redshift, une erreur rencontrée lors de l'exécution d'une procédure stockée interrompt le flux d'exécution, met fin à la transaction et applique un rollback de la transaction. Ces résultats se produisent car les sous-transactions ne sont pas prises en charge. Dans une procédure stockée dans Amazon Redshift, la seule option handler_statement prise en charge est RAISE. Dans BigQuery, la gestion des erreurs est une fonctionnalité essentielle du flux de contrôle principal, semblable à ce que d'autres langages fournissent avec les blocs TRY ... CATCH.

Amazon Redshift BigQuery
BEGIN ... EXCEPTION WHEN OTHERS THEN BEGIN ... EXCEPTION WHEN ERROR THEN
RAISE RAISE
[ <<label>> ] [ DECLARE declarations ]
BEGIN
statements EXCEPTION
BEGIN
statements
EXCEPTION
WHEN OTHERS THEN
Handler_statements
END;		 BEGIN
BEGIN
...
EXCEPTION WHEN ERROR THEN SELECT 1/0;
END;

EXCEPTION WHEN ERROR THEN -- The exception thrown from the inner exception handler lands here. END;

Déclarations et opérations de curseurs

Étant donné que BigQuery n'accepte pas les curseurs ni les sessions, il n'utilise pas les instructions suivantes :

Si vous utilisez le curseur pour renvoyer un ensemble de résultats, vous pouvez obtenir un comportement similaire en utilisant les tables temporaires dans BigQuery.

Instructions SQL dynamiques

La fonctionnalité de script de BigQuery est compatible avec les instructions SQL dynamiques telles que celles présentées dans le tableau suivant.

Amazon Redshift BigQuery
EXECUTE EXECUTE IMMEDIATE

Instructions de flux de contrôle

Amazon Redshift BigQuery
IF..THEN..ELSIF..THEN..ELSE..END IF IF condition
THEN stmts
ELSE stmts
END IF
name CURSOR [ ( arguments ) ] FOR query Les curseurs ou les sessions ne sont pas utilisés dans BigQuery.
[< LOOP
sql_statement_list END LOOP;
WHILE condition LOOP stmts END LOOP WHILE condition
DO stmts
END WHILE
EXIT BREAK

Garanties de cohérence et isolation de transaction

Amazon Redshift et BigQuery sont tous deux atomiques, c'est-à-dire conformes à la norme ACID au niveau de chaque mutation sur de nombreuses lignes.

Transactions

Par défaut, Amazon Redshift accepte l'isolation sérialisable pour les transactions. Amazon Redshift vous permet de spécifier l'un des quatre niveaux d'isolation standards des transactions SQL, mais traite tous les niveaux d'isolation comme sérialisables.

BigQuery est également compatible avec les transactions. BigQuery permet d'assurer un contrôle de simultanéité optimiste (le premier à effectuer un commit a la priorité) 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 par ligne, par mutation et entre les lignes d'une même instruction LMD, tout en évitant les interblocages. Si plusieurs mises à jour LMD sont effectuées sur la même table, BigQuery bascule vers le contrôle de simultanéité pessimiste. Les jobs de chargement peuvent s'exécuter complètement indépendamment et ajouter des données aux tables.

Rollback

Si Amazon Redshift rencontre une erreur lors de l'exécution d'une procédure stockée, il annule toutes les modifications effectuées dans le cadre de la transaction. Vous pouvez également utiliser l'instruction de contrôle des transactions ROLLBACK dans une procédure stockée pour supprimer toutes les modifications.

Dans BigQuery, vous pouvez utiliser l'instruction ROLLBACK TRANSACTION.

Limites des bases de données

Vérifiez les derniers quotas et les dernières limites dans la documentation publique BigQuery. Les utilisateurs ayant un volume de requêtes important peuvent demander l'augmentation de nombreux quotas en contactant l'équipe d'assistance Cloud. Le tableau suivant présente une comparaison des limites des base de données Amazon Redshift et BigQuery.

Limite Amazon Redshift BigQuery
Tables dans chaque base de données pour les types de nœuds de cluster "large" et "xlarge" 9 900 Pas de restriction
Tables de chaque base de données pour les types de nœuds de cluster 8x plus larges 20 000 Pas de restriction
Bases de données définies par l'utilisateur que vous pouvez créer pour chaque cluster 60 Pas de restriction
Taille maximale des lignes 4 Mo 100 Mo