Migrer vers le langage SQL standard

BigQuery accepte deux dialectes SQL : le SQL standard et l'ancien SQL. Cet article décrit les différences entre ces deux dialectes, y compris au niveau de la syntaxe, des fonctions et de la sémantique. Il fournit également des exemples de certains des points forts du langage SQL standard.

Comparaison entre l'ancien SQL et le SQL standard

Auparavant, BigQuery exécutait des requêtes à l'aide d'un dialecte SQL non standard appelé SQL BigQuery. Lors du lancement de BigQuery 2.0, BigQuery a ajouté la compatibilité avec le langage SQL standard et renommé SQL BigQuery en ancien SQL. Le langage SQL standard est le dialecte SQL privilégié pour l'interrogation de données stockées dans BigQuery.

Dois-je passer au langage SQL standard ?

Le passage de l'ancien SQL au langage SQL standard est recommandé, mais pas obligatoire. Par exemple, supposons que vous exécutiez un grand nombre de requêtes utilisant l'ancien SQL, mais que vous souhaitiez exploiter une fonctionnalité du langage SQL standard pour une nouvelle requête. Vous pouvez créer des requêtes utilisant le SQL standard qui s'exécutent parallèlement aux requêtes employant l'ancien SQL.

Activer le langage SQL standard

Lorsque vous exécutez une requête via BigQuery, vous pouvez choisir d'utiliser l'ancien SQL ou le langage SQL standard. Consultez la page Activer le langage SQL standard pour connaître les étapes permettant d'activer le SQL standard dans l'UI, la CLI ou l'API BigQuery, ou toute autre interface que vous utilisez.

Avantages du langage SQL standard

Le langage SQL standard est conforme à la norme SQL 2011 et inclut des extensions permettant d'interroger des données imbriquées et répétées. Il présente plusieurs avantages par rapport à l'ancien SQL, tels que les suivants :

Pour obtenir des exemples illustrant certaines de ces fonctionnalités, consultez la section Points forts du langage SQL standard.

Différences entre les types

Les types de l'ancien SQL possèdent un équivalent en SQL standard et inversement. Dans certains cas, le type a un nom différent. Le tableau suivant répertorie chaque type de données de l'ancien SQL et son équivalent en SQL standard.

Ancien SQL SQL standard Notes
BOOL BOOL
INTEGER INT64
FLOAT FLOAT64
STRING STRING
BYTES BYTES
RECORD STRUCT
REPEATED ARRAY
TIMESTAMP TIMESTAMP Consultez les différences concernant TIMESTAMP
DATE DATE L'ancien SQL est compatible de façon limitée avec le type de données DATE.
TIME TIME L'ancien SQL est compatible de façon limitée avec le type de données TIME.
DATETIME DATETIME L'ancien SQL est compatible de façon limitée avec le type de données DATETIME.

Pour en savoir plus sur le système de types du langage SQL standard, consultez la documentation de référence sur les types de données du langage SQL standard. Pour plus d'informations sur les types de données dans BigQuery, reportez-vous à la documentation de référence sur les types de données BigQuery.

Différences concernant TIMESTAMP

Le langage SQL standard dispose d'une plage de valeurs TIMESTAMP valides plus stricte que celle de l'ancien SQL. En SQL standard, les valeurs TIMESTAMP valides sont comprises entre 0001-01-01 00:00:00.000000 et 9999-12-31 23:59:59.999999. Lorsque vous utilisez le langage SQL standard, vous pouvez par exemple sélectionner les valeurs minimale et maximale d'un type de données TIMESTAMP :

#standardSQL
SELECT
  min_timestamp,
  max_timestamp,
  UNIX_MICROS(min_timestamp) AS min_unix_micros,
  UNIX_MICROS(max_timestamp) AS max_unix_micros
FROM (
  SELECT
    TIMESTAMP '0001-01-01 00:00:00.000000' AS min_timestamp,
    TIMESTAMP '9999-12-31 23:59:59.999999' AS max_timestamp
);

Cette requête renvoie -62135596800000000 en tant que min_unix_micros et 253402300799999999 en tant que max_unix_micros.

Si vous sélectionnez une colonne contenant des valeurs d'horodatage situées en dehors de cette plage, vous obtenez un message d'erreur :

#standardSQL
SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps;

Cette requête renvoie l'erreur suivante :

Cannot return an invalid timestamp value of -8446744073709551617
microseconds relative to the Unix epoch. The range of valid
timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]

Pour corriger l'erreur, vous pouvez spécifier et employer une fonction définie par l'utilisateur pour filtrer les horodatages non valides :

#standardSQL
CREATE TEMP FUNCTION TimestampIsValid(t TIMESTAMP) AS (
  t >= TIMESTAMP('0001-01-01 00:00:00') AND
  t <= TIMESTAMP('9999-12-31 23:59:59.999999')
);

SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps
WHERE TimestampIsValid(timestamp_column_with_invalid_values);

Une autre option consiste à utiliser la fonction SAFE_CAST avec la colonne d'horodatage. Exemple :

#standardSQL
SELECT SAFE_CAST(timestamp_column_with_invalid_values AS STRING) AS timestamp_string
FROM MyTableWithInvalidTimestamps;

Cette requête renvoie NULL au lieu d'une chaîne d'horodatage pour les valeurs d'horodatage non valides.

Différences syntaxiques

Échapper les mots clés réservés et les identifiants non valides

En ancien SQL, vous devez échapper les mots clés réservés et les identifiants qui contiennent des caractères non valides, tels que des espaces ( ) ou des traits d'union (-), à l'aide de crochets ([]). En SQL standard, vous devez échapper ces mots clés et identifiants à l'aide d'accents graves (`). Exemple :

#standardSQL
SELECT
  word,
  SUM(word_count) AS word_count
FROM
  `bigquery-public-data.samples.shakespeare`
WHERE word IN ('me', 'I', 'you')
GROUP BY word;

L'ancien SQL permet d'utiliser des mots clés réservés à certains endroits où ils ne sont pas autorisés par le langage SQL standard. Par exemple, la requête suivante qui emploie le SQL standard échoue en raison d'une erreur Syntax error (Erreur de syntaxe) :

#standardSQL
SELECT
  COUNT(*) AS rows
FROM
  `bigquery-public-data.samples.shakespeare`;

Pour corriger l'erreur, échappez l'alias rows à l'aide d'accents graves :

#standardSQL
SELECT
  COUNT(*) AS `rows`
FROM
  `bigquery-public-data.samples.shakespeare`;

Pour obtenir la liste des mots clés réservés et des éléments qui constituent des identifiants valides, consultez la page Structure lexicale du langage SQL standard.

Tables incluant un nom de projet complet

En ancien SQL, pour interroger une table comportant un nom de projet complet, vous devez utiliser le caractère "deux-points" (:) en tant que séparateur. Exemple :

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT 1;

En SQL standard, vous devez utiliser un point (.). Exemple :

#standardSQL
SELECT
  word
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

Si le nom de votre projet comprend un domaine, tel que example.com:myproject, vous devez utiliser example.com:myproject comme nom de projet, en incluant le caractère :.

Décorateurs de table et fonctions de caractère générique

Le langage SQL standard n'est pas compatible avec les fonctions TABLE_DATE_RANGE, TABLE_DATE_RANGE_STRICT et TABLE_QUERY.

Vous pouvez obtenir la même sémantique que TABLE_DATE_RANGE ou TABLE_QUERY en appliquant un filtre à la pseudo-colonne _TABLE_SUFFIX. Prenons par exemple la requête en ancien SQL suivante, qui compte le nombre de lignes présentes dans les tables GSOD 2010 et 2011 de l'Agence américaine d'observation océanique et atmosphérique :

#legacySQL
SELECT COUNT(*)
FROM TABLE_QUERY([bigquery-public-data:noaa_gsod],
                 'table_id IN ("gsod2010", "gsod2011")');

Voici une requête équivalente en SQL standard :

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.*`
WHERE _TABLE_SUFFIX IN ("gsod2010", "gsod2011");

Pour en savoir plus et obtenir des exemples qui utilisent TABLE_DATE_RANGE, consultez la section Migrer des fonctions de caractères génériques de table en ancien SQL.

Virgules de fin dans la liste SELECT

Contrairement à l'ancien SQL, le langage SQL standard ne permet pas d'utiliser des virgules de fin avant la clause FROM. Par exemple, la requête suivante n'est pas valide :

#standardSQL
SELECT
  word,
  corpus,  -- Error due to trailing comma
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

Pour corriger l'erreur, supprimez la virgule après corpus :

#standardSQL
SELECT
  word,
  corpus
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

Opérateur de virgule avec les tables

En ancien SQL, l'opérateur de virgule , est utilisé en tant qu'opérateur UNION ALL lorsqu'il est appliqué à des tables. En SQL standard, l'opérateur de virgule est employé en tant qu'opérateur JOIN. Prenons par exemple la requête en ancien SQL suivante :

#legacySQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y),
  (SELECT 2 AS x, "bar" AS y);

Voici une requête équivalente en SQL standard :

#standardSQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y UNION ALL
   SELECT 2 AS x, "bar" AS y);

Notez également qu'en SQL standard, UNION ALL associe les colonnes par position plutôt que par nom. La requête ci-dessus est équivalente à la suivante :

#standardSQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y UNION ALL
   SELECT 2, "bar");

En SQL standard, l'opérateur de virgule est couramment utilisé pour effectuer une jointure (JOIN) avec un tableau. Exemple :

#standardSQL
WITH T AS (
  SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
  SELECT 1, [4, 5])
SELECT
  x,
  y
FROM
  T,
  UNNEST(arr) AS y;

La requête ci-dessus renvoie le produit croisé de la table T avec les éléments de arr. Vous pouvez également exprimer la requête en SQL standard de la manière suivante :

#standardSQL
WITH T AS (
  SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
  SELECT 1, [4, 5])
SELECT
  x,
  y
FROM
  T
JOIN
  UNNEST(arr) AS y;

Dans cette requête, JOIN a la même signification que l'opérateur de virgule , qui sépare T de UNNEST(arr) AS y dans l'exemple ci-dessus.

Vues logiques

Vous ne pouvez pas employer le langage SQL standard pour interroger une vue logique définie avec l'ancien SQL, et inversement. Ceci est dû aux différences sémantiques et syntaxiques qui existent entre les deux dialectes. À la place, vous devez créer une vue qui utilise le langage SQL standard (éventuellement sous un nom différent) afin de remplacer une vue employant l'ancien SQL.

Par exemple, imaginons que la vue V soit définie avec l'ancien SQL de la manière suivante :

#legacySQL
SELECT *, UTC_USEC_TO_DAY(timestamp_col) AS day
FROM MyTable;

Imaginons également que la vue W soit définie avec l'ancien SQL de la manière suivante :

#legacySQL
SELECT user, action, day
FROM V;

Supposons maintenant que vous exécutiez quotidiennement la requête en ancien SQL ci-dessous, mais que vous souhaitiez la migrer afin d'utiliser le langage SQL standard :

#legacySQL
SELECT EXACT_COUNT_DISTINCT(user), action, day
FROM W
GROUP BY action, day;

Un chemin de migration possible consiste à créer des vues et à leur attribuer des noms différents. Voici la démarche à suivre :

Créez une vue nommée V2 à l'aide du SQL standard en utilisant le contenu suivant :

#standardSQL
SELECT *, EXTRACT(DAY FROM timestamp_col) AS day
FROM MyTable;

Créez une vue nommée W2 à l'aide du SQL standard en utilisant le contenu suivant :

#standardSQL
SELECT user, action, day
FROM V2;

Modifiez votre requête quotidienne pour qu'elle utilise le langage SQL standard et fasse plutôt référence à W2 :

#standardSQL
SELECT COUNT(DISTINCT user), action, day
FROM W2
GROUP BY action, day;

Vous pouvez également supprimer les vues V et W, puis les recréer sous le même nom à l'aide du SQL standard. Avec cette option, vous devrez toutefois migrer toutes vos requêtes référençant V ou W pour qu'elles utilisent le langage SQL standard.

Comparaison des fonctions

Vous trouverez ci-dessous une liste partielle des fonctions de l'ancien SQL et de leur équivalent en langage SQL standard :

Ancien SQL SQL standard Notes
INTEGER(x) SAFE_CAST(x AS INT64)
CAST(x AS INTEGER) SAFE_CAST(x AS INT64)
DATEDIFF(t1, t2) TIMESTAMP_DIFF(t1, t2, DAY)
NOW CURRENT_TIMESTAMP
STRFTIME_UTC_USEC(t, fmt) FORMAT_TIMESTAMP(fmt, t)
UTC_USEC_TO_DAY(t) TIMESTAMP_TRUNC(t, DAY)
REGEXP_MATCH(s, pattern) REGEXP_CONTAINS(s, pattern)
IS_NULL(x) x IS NULL
LEFT(s, len) SUBSTR(s, 0, len)
RIGHT(s, len) SUBSTR(s, -len)
s CONTAINS "foo" STRPOS(s, "foo") > 0 ou s LIKE '%foo%'
x % y MOD(x, y)
NEST(x) ARRAY_AGG(x)
ANY(x) ANY_VALUE(x)
GROUP_CONCAT_UNQUOTED(s, sep) STRING_AGG(s, sep)
SOME(x) IFNULL(LOGICAL_OR(x), false)
EVERY(x) IFNULL(LOGICAL_AND(x), true)
COUNT(DISTINCT x) APPROX_COUNT_DISTINCT(x) Consultez les remarques ci-dessous
EXACT_COUNT_DISTINCT(x) COUNT(DISTINCT x) Consultez les remarques ci-dessous
QUANTILES(x, buckets + 1) APPROX_QUANTILES(x, buckets)
TOP(x, num), COUNT(*) APPROX_TOP_COUNT(x, num)
NTH(index, arr) WITHIN RECORD arr[SAFE_ORDINAL(index)]
COUNT(arr) WITHIN RECORD ARRAY_LENGTH(arr)
HOST(url) NET.HOST(url) Consultez les différences ci-dessous
TLD(url) NET.PUBLIC_SUFFIX(url) Consultez les différences ci-dessous
DOMAIN(url) NET.REG_DOMAIN(url) Consultez les différences ci-dessous
PARSE_IP(addr_string) NET.IPV4_TO_INT64(NET.IP_FROM_STRING(addr_string))
FORMAT_IP(addr_int64) NET.IP_TO_STRING(NET.IPV4_FROM_INT64(addr_int64 & 0xFFFFFFFF))
PARSE_PACKED_IP(addr_string) NET.IP_FROM_STRING(addr_string)
FORMAT_PACKED_IP(addr_bytes) NET.IP_TO_STRING(addr_bytes)

Pour en savoir plus sur les fonctions du langage SQL standard, consultez la page Fonctions et opérateurs du langage SQL standard.

Comparaison des fonctions COUNT

L'ancien SQL et le SQL standard présentent tous deux une fonction COUNT. Sachez toutefois que chaque fonction se comporte différemment selon le dialecte SQL que vous utilisez.

En ancien SQL, COUNT(DISTINCT x) renvoie un nombre approximatif. En SQL standard, un nombre exact est renvoyé. Pour obtenir un nombre approximatif de valeurs distinctes plus rapidement tout en utilisant moins de ressources, employez la fonction APPROX_COUNT_DISTINCT.

Comparaison des fonctions d'URL

L'ancien SQL et le SQL standard comportent tous deux des fonctions permettant d'analyser des URL. En ancien SQL, ces fonctions correspondent à HOST(url), TLD(url) et DOMAIN(url). En SQL standard, vous pouvez utiliser NET.HOST(url), NET.PUBLIC_SUFFIX(url) et NET.REG_DOMAIN(url).

Améliorations par rapport aux fonctions de l'ancien SQL

  • Les fonctions d'URL du langage SQL standard peuvent analyser des URL commençant par "//".
  • Lorsque l'entrée n'est pas conforme à la RFC 3986 ou n'est pas une URL (par exemple, "mailto:?to=&subject=&body="), d'autres règles s'appliquent pour l'analyse. Plus spécifiquement, les fonctions d'URL du langage SQL standard peuvent analyser des entrées non standards qui ne commencent pas par "//", telles que "www.google.com". Pour des résultats optimaux, assurez-vous que les entrées sont des URL conformes à la RFC 3986.
  • La fonction NET.PUBLIC_SUFFIX renvoie des résultats sans point au début. Par exemple, elle renvoie "com" au lieu de ".com". Ce format est conforme à la liste des suffixes publics.
  • NET.PUBLIC_SUFFIX et NET.REG_DOMAIN acceptent les lettres majuscules et les noms de domaine internationalisés. En revanche, TLD et DOMAIN ne sont pas compatibles avec ces derniers (vous pouvez obtenir des résultats inattendus).

Différences mineures relatives aux cas spéciaux

  • Si l'entrée ne contient aucun suffixe de la liste des suffixes publics, NET.PUBLIC_SUFFIX et NET.REG_DOMAIN renvoient la valeur NULL, tandis que TLD et DOMAIN tentent d'estimer au mieux des valeurs non nulles.
  • Si l'entrée ne contient qu'un suffixe public qui n'est pas précédé d'une étiquette (par exemple, "http://com"), NET.PUBLIC_SUFFIX renvoie le suffixe public, tandis que TLD renvoie une chaîne vide. De même, NET.REG_DOMAIN renvoie la valeur NULL, tandis que DOMAIN renvoie le suffixe public.
  • Pour les entrées contenant des hôtes IPv6, NET.HOST ne supprime pas les parenthèses du résultat, comme spécifié dans la RFC 3986.
  • Pour les entrées contenant des hôtes IPv4, NET.REG_DOMAIN renvoie la valeur NULL, tandis que DOMAIN renvoie les trois premiers octets.

Exemples

Dans le tableau ci-dessous, les termes grisés indiquent les résultats identiques renvoyés en ancien SQL et en SQL standard.

URL (description) HOST NET.HOST TLD NET.PUBLIC _SUFFIX DOMAIN NET.REG_DOMAIN
"//google.com"
(commence par "//")
NULL "google.com" NULL "com" NULL "google.com"
"google.com"
(format non standard sans "//")
NULL "google.com" NULL "com" NULL "google.com"
"http://user:pass@word@x.com"
(format non standard comportant plusieurs "@")
"word@x.com" "x.com" ".com" "com" "word@x.com" "x.com"
"http://foo.com:1:2"
(format non standard comportant plusieurs ":")
"foo.com:1" "foo.com" ".com:1" "com" "foo.com" "foo.com"
"http://x.Co.uk"
(lettres majuscules)
"x.Co.uk" "x.Co.uk" ".uk" "Co.uk" "Co.uk" "x.Co.uk"
"http://a.b"
(suffixe public introuvable)
"a.b" "a.b" ".b" NULL "a.b" NULL
"http://com"
(l'hôte ne contient qu'un suffixe public)
"com" "com" "" "com" "com" NULL
"http://[::1]"
(hôte IPv6, aucun suffixe public)
"::1" "[::1]" "" NULL "::1" NULL
"http://1.2.3.4"
(hôte IPv4, aucun suffixe public)
"1.2.3.4" "1.2.3.4" "" NULL "1.2.3" NULL

Différences concernant le traitement des champs répétés

Un type REPEATED en ancien SQL est équivalent à un tableau ARRAY de ce type en SQL standard. Par exemple, REPEATED INTEGER est équivalent à ARRAY<INT64> en SQL standard. La section suivante décrit certaines des différences entre l'ancien SQL et le SQL standard concernant les opérations effectuées sur des champs répétés.

Éléments NULL et tableaux NULL

Le langage SQL standard est compatible avec les éléments de tableau NULL, mais génère une erreur s'il existe un élément de tableau NULL dans le résultat de la requête. Si le résultat comporte une colonne de tableau NULL, le langage SQL standard la stocke en tant que tableau vide.

Sélectionner des champs feuille répétés imbriqués

En ancien SQL, vous pouvez ajouter un point dans un champ répété imbriqué sans avoir à tenir compte de l'emplacement de la répétition. En SQL standard, cette méthode génère une erreur. Exemple :

#standardSQL
SELECT
  repository.url,
  payload.pages.page_name
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

La tentative d'exécution de la requête ci-dessus renvoie l'erreur suivante :

Cannot access field page_name on a value with type
ARRAY<STRUCT<action STRING, html_url STRING, page_name STRING, ...>>

Pour corriger l'erreur et renvoyer un tableau de noms de pages (page_name) dans le résultat, utilisez plutôt une sous-requête ARRAY. Exemple :

#standardSQL
SELECT
  repository.url,
  ARRAY(SELECT page_name FROM UNNEST(payload.pages)) AS page_names
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

Pour en savoir plus sur les tableaux et les sous-requêtes ARRAY, consultez la page Utiliser des tableaux.

Filtrer les champs répétés

En ancien SQL, vous pouvez filtrer les champs répétés directement à l'aide d'une clause WHERE. En SQL standard, vous pouvez exprimer une logique semblable grâce à un opérateur de virgule JOIN suivi d'un filtre. Prenons par exemple la requête en ancien SQL suivante :

#legacySQL
SELECT
  payload.pages.title
FROM
  [bigquery-public-data:samples.github_nested]
WHERE payload.pages.page_name IN ('db_jobskill', 'Profession');

Cette requête renvoie tous les titres (title) des pages dont le nom (page_name) correspond à db_jobskill ou Profession. Vous pouvez exprimer une requête similaire en SQL standard de la manière suivante :

#standardSQL
SELECT
  page.title
FROM
  `bigquery-public-data.samples.github_nested`,
  UNNEST(payload.pages) AS page
WHERE page.page_name IN ('db_jobskill', 'Profession');

La différence entre les requêtes en ancien SQL et en SQL standard précédentes est que si vous désactivez l'option Aplatir les résultats et exécutez la requête en ancien SQL, payload.pages.title apparaît comme REPEATED dans le résultat de la requête. Pour obtenir la même sémantique en langage SQL standard et renvoyer un tableau pour la colonne title, utilisez plutôt une sous-requête ARRAY :

#standardSQL
SELECT
  title
FROM (
  SELECT
    ARRAY(SELECT title FROM UNNEST(payload.pages)
          WHERE page_name IN ('db_jobskill', 'Profession')) AS title
  FROM
    `bigquery-public-data.samples.github_nested`)
WHERE ARRAY_LENGTH(title) > 0;

Cette requête crée un tableau de titres (title) dans lequel le nom des pages (page_name) est 'db_jobskill' ou 'Profession'. Elle filtre ensuite les lignes qui ne correspondent pas à cette condition à l'aide de ARRAY_LENGTH(title) > 0.

Pour en savoir plus sur les tableaux, consultez la page Utiliser des tableaux.

Structure des champs feuille imbriqués sélectionnés

Contrairement au langage SQL standard, l'ancien SQL conserve la structure des champs feuille imbriqués dans la liste SELECT lorsque l'option Aplatir les résultats est désactivée. Prenons par exemple la requête en ancien SQL suivante :

#legacySQL
SELECT
  repository.url,
  repository.has_downloads
FROM
  [bigquery-public-data.samples.github_nested]
LIMIT 5;

Cette requête renvoie les champs url et has_downloads au sein d'un enregistrement nommé repository lorsque l'option Aplatir les résultats n'est pas définie. Prenons ensuite la requête en SQL standard ci-dessous :

#standardSQL
SELECT
  repository.url,
  repository.has_downloads
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

Cette requête renvoie les champs url et has_downloads en tant que colonnes de premier niveau. Ces champs ne font pas partie d'une structure ni d'un enregistrement repository. Pour les renvoyer dans le cadre d'une structure, utilisez l'opérateur STRUCT :

#standardSQL
SELECT
  STRUCT(
    repository.url,
    repository.has_downloads) AS repository
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

Supprimer les répétitions avec FLATTEN

Le langage SQL standard ne dispose pas de la fonction FLATTEN de l'ancien SQL, mais l'opérateur JOIN (virgule) vous permet d'obtenir une sémantique semblable. Prenons par exemple la requête en ancien SQL suivante :

#legacySQL
SELECT
  repository.url,
  payload.pages.page_name
FROM
  FLATTEN([bigquery-public-data:samples.github_nested], payload.pages.page_name)
LIMIT 5;

Vous pouvez exprimer une requête similaire en SQL standard de la manière suivante :

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`,
  UNNEST(payload.pages) AS page
LIMIT 5;

Vous pouvez également utiliser JOIN au lieu de l'opérateur de virgule , :

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`
JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

Une différence importante est que la requête en ancien SQL renvoie une ligne dans laquelle payload.pages.page_name possède la valeur NULL si payload.pages est vide. En revanche, la requête en SQL standard ne renvoie pas de ligne si le champ payload.pages est vide. Pour obtenir exactement la même sémantique, utilisez une fonction LEFT JOIN ou LEFT OUTER JOIN. Exemple :

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`
LEFT JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

Pour en savoir plus sur les tableaux, consultez la page Utiliser des tableaux. Pour obtenir plus d'informations sur UNNEST, consultez la section relative à UNNEST.

Filtrer les lignes avec OMIT RECORD IF

La clause OMIT IF de l'ancien SQL vous permet de filtrer les lignes à l'aide d'une condition pouvant s'appliquer aux champs répétés. En SQL standard, vous pouvez modéliser une clause OMIT IF avec une clause EXISTS, une clause IN ou un filtre simple. Prenons par exemple la requête en ancien SQL suivante :

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  EVERY(payload.pages.page_name != 'db_jobskill'
        AND payload.pages.page_name != 'Profession');

Voici une requête équivalente en langage SQL standard :

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(payload.pages)
  WHERE page_name = 'db_jobskill'
    OR page_name = 'Profession');

Ici, la clause EXISTS renvoie true si au moins un élément de payload.pages possède le nom de page 'db_jobskill' ou 'Profession'.

Dans la même optique, supposons que la requête en ancien SQL utilise IN :

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF NOT
  SOME(payload.pages.page_name IN ('db_jobskill', 'Profession'));

En SQL standard, vous pouvez exprimer la requête à l'aide d'une clause EXISTS comprenant IN :

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(payload.pages)
  WHERE page_name IN ('db_jobskill', 'Profession'));

Prenons maintenant la requête en ancien SQL suivante, qui filtre les enregistrements comportant 80 pages et moins :

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

Ici, vous pouvez utiliser un filtre avec ARRAY_LENGTH en SQL standard :

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE
  ARRAY_LENGTH(payload.pages) > 80;

Notez que la fonction ARRAY_LENGTH s'applique directement au champ répété payload.pages plutôt qu'au champ imbriqué payload.pages.page_name, comme dans la requête en ancien SQL.

Pour en savoir plus sur les tableaux et les sous-requêtes ARRAY, consultez la page Utiliser des tableaux.

Différences sémantiques

La sémantique de certaines opérations diffère entre l'ancien SQL et le langage SQL standard.

Coercitions automatiques de types de données

L'ancien SQL et le SQL standard acceptent tous deux les coercitions (conversions automatiques) entre certains types de données. Par exemple, BigQuery convertit une valeur de type INT64 au format FLOAT64 si la requête la transmet à une fonction nécessitant une entrée de type FLOAT64. Le langage SQL standard n'accepte pas les coercitions ci-dessous, qui sont compatibles avec l'ancien SQL. Vous devez utiliser une fonction CAST explicite à la place.

  • Conversion d'un littéral INT64 au format TIMESTAMP. Utilisez plutôt TIMESTAMP_MICROS(micros_value).
  • Conversion d'un littéral STRING au format INT64, FLOAT64 ou BOOL. Utilisez plutôt CAST(str AS INT64), CAST(str AS FLOAT64) ou CAST(str AS BOOL).
  • Conversion d'un type de données STRING au format BYTES. Utilisez plutôt CAST(str AS BYTES).

Erreurs d'exécution

Certaines fonctions de l'ancien SQL renvoient NULL en cas d'entrée non valide, ce qui peut vous empêcher d'identifier les éventuels problèmes liés à la requête ou aux données. Le langage SQL standard est généralement plus strict et génère une erreur lorsqu'une entrée n'est pas valide.

  • Pour toutes les fonctions et les opérateurs mathématiques, l'ancien SQL ne vérifie pas la présence de dépassements. Le langage SQL standard ajoute des contrôles de dépassement et génère une erreur si un dépassement se produit lors d'un calcul. Cela inclut les opérateurs +, - et *, ainsi que les fonctions d'agrégation SUM, AVG et STDDEV, parmi d'autres éléments.
  • Le langage SQL standard génère une erreur si vous tentez d'effectuer une division par zéro, tandis que l'ancien SQL renvoie NULL. Pour renvoyer NULL pour une division par zéro en SQL standard, utilisez SAFE_DIVIDE.
  • Le langage SQL standard génère une erreur pour les fonctions CAST dans lesquelles le format d'entrée n'est pas valide ou se situe en dehors de la plage du type cible, tandis que l'ancien SQL renvoie NULL. Pour éviter de générer une erreur pour une conversion CAST non valide en SQL standard, utilisez SAFE_CAST.

Résultats répétés imbriqués

Les requêtes exécutées à l'aide du SQL standard préservent l'imbrication et la répétition des colonnes dans le résultat, et l'option Aplatir les résultats n'a aucun effet. Pour renvoyer des colonnes de premier niveau pour des champs imbriqués, utilisez l'opérateur .* sur les colonnes de structures. Exemple :

#standardSQL
SELECT
  repository.*
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

Pour renvoyer des colonnes de premier niveau pour des champs imbriqués répétés (tableaux ARRAY de structures STRUCT), utilisez une jointure (JOIN) qui exploite le produit croisé des lignes de la table et les éléments du champ imbriqué répété. Exemple :

#standardSQL
SELECT
  repository.url,
  page.*
FROM
  `bigquery-public-data.samples.github_nested`
JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

Pour en savoir plus sur les tableaux et les sous-requêtes ARRAY, consultez la page Utiliser des tableaux.

Conditions NOT IN et valeurs NULL

Contrairement au langage SQL standard, l'ancien SQL ne permet pas de traiter les valeurs NULL avec des conditions NOT IN. Prenons par exemple la requête en ancien SQL suivante, qui recherche le nombre de mots qui n'apparaissent pas en tant qu'emplacements dans l'exemple de table GitHub :

#legacySQL
SELECT COUNT(*)
FROM [bigquery-public-data.samples.shakespeare]
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM [bigquery-public-data.samples.github_nested]
);

La requête renvoie 163 716, ce qui signifie que 163 716 mots n'apparaissent pas en tant qu'emplacements dans la table GitHub. Prenons maintenant la requête en SQL standard suivante :

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM `bigquery-public-data.samples.github_nested`
);

La requête renvoie 0. Cette différence est due à la sémantique de NOT IN du langage SQL standard, qui renvoie NULL si l'une des valeurs à droite est NULL. Pour obtenir les mêmes résultats qu'avec la requête en ancien SQL, utilisez une clause WHERE pour exclure les valeurs NULL :

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM `bigquery-public-data.samples.github_nested`
  WHERE actor_attributes.location IS NOT NULL
);

La requête renvoie 163 716. Vous pouvez également employer une condition NOT EXISTS :

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare` AS t
WHERE NOT EXISTS (
  SELECT 1
  FROM `bigquery-public-data.samples.github_nested`
  WHERE t.word = actor_attributes.location
);

Cette requête renvoie également 163 716. Pour en savoir plus, consultez la section relative aux opérateurs de comparaison de la documentation, qui explique la sémantique de IN, NOT IN, EXISTS ainsi que d'autres opérateurs de comparaison.

Différences concernant les fonctions JavaScript définies par l'utilisateur

Les documents de la page Fonctions définies par l'utilisateur en SQL standard expliquent comment employer des fonctions JavaScript définies par l'utilisateur avec le langage SQL standard. Cette section décrit certaines des principales différences entre les fonctions définies par l'utilisateur de l'ancien SQL et du langage SQL standard.

Fonctions dans le texte de la requête

En SQL standard, vous devez utiliser CREATE TEMPORARY FUNCTION dans le corps de la requête au lieu de spécifier séparément les fonctions définies par l'utilisateur. Vous pouvez par exemple définir des fonctions séparément depuis l'onglet "UDF Editor" (Éditeur de fonction définie par l'utilisateur) de l'UI Web de BigQuery, ou via l'indicateur --udf_resource si vous utilisez la CLI bq.

Prenons la requête en SQL standard suivante :

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>)
  RETURNS FLOAT64 LANGUAGE js AS """
var sum_of_reciprocals = 0;
for (var i = 0; i < arr.length; ++i) {
  sum_of_reciprocals += 1 / arr[i];
}
return arr.length / sum_of_reciprocals;
""";

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

Cette requête définit une fonction JavaScript appelée HarmonicMean, puis l'applique à la colonne de tableau arr depuis T.

Pour en savoir plus sur les fonctions définies par l'utilisateur, consultez la page Fonctions définies par l'utilisateur en SQL standard.

Les fonctions opèrent sur des valeurs plutôt que sur des lignes

En ancien SQL, les fonctions JavaScript opèrent sur les lignes d'une table. En SQL standard, les fonctions JavaScript agissent sur des valeurs, comme le montre l'exemple ci-dessus. Pour transmettre une valeur de ligne à une fonction JavaScript à l'aide du langage SQL standard, vous devez définir une fonction qui exploite une structure ayant le même type de ligne que la table. Exemple :

#standardSQL
-- Takes a struct of x, y, and z and returns a struct with a new field foo.
CREATE TEMPORARY FUNCTION AddField(s STRUCT<x FLOAT64, y BOOL, z STRING>)
  RETURNS STRUCT<x FLOAT64, y BOOL, z STRING, foo STRING> LANGUAGE js AS """
var new_struct = new Object();
new_struct.x = s.x;
new_struct.y = s.y;
new_struct.z = s.z;
if (s.y) {
  new_struct.foo = 'bar';
} else {
  new_struct.foo = 'baz';
}

return new_struct;
""";

WITH T AS (
  SELECT x, MOD(off, 2) = 0 AS y, CAST(x AS STRING) AS z
  FROM UNNEST([5.0, 4.0, 3.0, 2.0, 1.0]) AS x WITH OFFSET off
)
SELECT AddField(t).*
FROM T AS t;

Cette requête définit une fonction JavaScript qui exploite une structure possédant le même type de ligne que T, puis crée une autre structure comportant un champ supplémentaire nommé foo. L'instruction SELECT transmet la ligne t en tant qu'entrée à la fonction et utilise .* pour renvoyer les champs de la structure obtenue dans le résultat.

Points forts du langage SQL standard

Cette section décrit certains des points forts du langage SQL standard par rapport à l'ancien SQL.

Composabilité à l'aide de clauses WITH

Certains des exemples en SQL standard de cette page emploient une clause WITH, qui permet d'extraire ou de réutiliser des sous-requêtes de noms. Exemple :

#standardSQL
WITH T AS (
  SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
)
SELECT x / (SELECT SUM(x) FROM T) AS weighted_x
FROM T;

Cette requête définit une sous-requête de noms T qui contient x valeurs de 1, 2, 3 et 4. Elle sélectionne x valeurs à partir de T et les divise par la somme de toutes les valeurs x présentes dans T. Cette requête est équivalente à une requête dans laquelle le contenu de T est intégré :

#standardSQL
SELECT
  x / (SELECT SUM(x)
       FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x)) AS weighted_x
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);

Pour analyser un autre exemple, prenons la requête suivante, qui utilise plusieurs sous-requêtes de noms :

#standardSQL
WITH T AS (
  SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
),
TPlusOne AS (
  SELECT x + 1 AS y
  FROM T
),
TPlusOneTimesTwo AS (
  SELECT y * 2 AS z
  FROM TPlusOne
)
SELECT z
FROM TPlusOneTimesTwo;

Cette requête définit une séquence de transformations des données d'origine, suivie d'une instruction SELECT sur TPlusOneTimesTwo. Elle est équivalente à la requête suivante, qui intègre les calculs :

#standardSQL
SELECT (x + 1) * 2 AS z
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);

Pour en savoir plus, consultez la section relative à la clause WITH dans la documentation.

Composabilité à l'aide de fonctions SQL

Le langage SQL standard accepte les fonctions SQL définies par l'utilisateur. Vous pouvez employer ces fonctions SQL pour définir des expressions courantes, puis les référencer à partir de la requête. Exemple :

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>) AS
(
  ARRAY_LENGTH(arr) / (SELECT SUM(1 / x) FROM UNNEST(arr) AS x)
);

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

Cette requête définit une fonction SQL appelée HarmonicMean, puis l'applique à la colonne de tableau arr depuis T.

Sous-requêtes dans d'autres emplacements

Le langage SQL standard accepte l'utilisation de sous-requêtes dans la liste SELECT, dans la clause WHERE, ainsi qu'à tout autre emplacement de la requête où une expression est attendue. Prenons par exemple la requête en SQL standard suivante, qui calcule le nombre de jours de chaleur à Seattle en 2015 :

#standardSQL
WITH SeattleWeather AS (
  SELECT *
  FROM `bigquery-public-data.noaa_gsod.gsod2015`
  WHERE stn = '994014'
)
SELECT
  COUNTIF(max >= 70) /
    (SELECT COUNT(*) FROM SeattleWeather) AS warm_days_fraction
FROM SeattleWeather;

La station météorologique de Seattle possède l'ID '994014'. La requête calcule le nombre de jours de chaleur en identifiant le nombre de jours lors desquels la température a atteint 70 degrés Fahrenheit (environ 21 degrés Celsius), puis en divisant ce nombre par le nombre total de jours enregistrés par la station en 2015.

Sous-requêtes corrélées

En SQL standard, les sous-requêtes peuvent référencer des colonnes corrélées (c'est-à-dire des colonnes issues de la requête externe). Prenons par exemple la requête en SQL standard suivante :

#standardSQL
WITH WashingtonStations AS (
  SELECT weather.stn AS station_id, ANY_VALUE(station.name) AS name
  FROM `bigquery-public-data.noaa_gsod.stations` AS station
  INNER JOIN `bigquery-public-data.noaa_gsod.gsod2015` AS weather
  ON station.usaf = weather.stn
  WHERE station.state = 'WA' AND station.usaf != '999999'
  GROUP BY station_id
)
SELECT washington_stations.name,
  (SELECT COUNT(*)
   FROM `bigquery-public-data.noaa_gsod.gsod2015` AS weather
   WHERE washington_stations.station_id = weather.stn
   AND max >= 70) AS warm_days
FROM WashingtonStations AS washington_stations
ORDER BY warm_days DESC;

Cette requête recherche le nom des stations météorologiques de l'État de Washington et calcule le nombre de jours lors desquels la température a atteint 70 degrés Fahrenheit. Notez que la liste SELECT contient une sous-requête qui référence washington_stations.station_id à partir du champ d'application externe, à savoir FROM WashingtonStations AS washington_stations.

Tableaux et structures

Les tableaux (ARRAY) et les structures (STRUCT) constituent des concepts importants en SQL standard. Pour illustrer ces deux concepts, prenons par exemple la requête suivante, qui calcule les deux articles les plus populaires quotidiennement dans l'ensemble de données HackerNews :

#standardSQL
WITH TitlesAndScores AS (
  SELECT
    ARRAY_AGG(STRUCT(title, score)) AS titles,
    EXTRACT(DATE FROM time_ts) AS date
  FROM `bigquery-public-data.hacker_news.stories`
  WHERE score IS NOT NULL AND title IS NOT NULL
  GROUP BY date)
SELECT date,
  ARRAY(SELECT AS STRUCT title, score
        FROM UNNEST(titles)
        ORDER BY score DESC
        LIMIT 2)
  AS top_articles
FROM TitlesAndScores
ORDER BY date DESC;

La clause WITH définit TitlesAndScores, qui contient deux colonnes. La première est un tableau de structures composé de deux champs : un titre d'article et un score. L'expression ARRAY_AGG renvoie un tableau de ces structures pour chaque jour.

L'instruction SELECT qui suit la clause WITH utilise une sous-requête ARRAY pour trier et renvoyer les deux articles les plus populaires de chaque tableau conformément au score, puis renvoie les résultats par ordre décroissant de date.

Pour en savoir plus sur les tableaux et les sous-requêtes ARRAY, consultez la page Utiliser des tableaux. Vous pouvez également vous reporter à la documentation de référence concernant les tableaux et les structures.

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.