Syntaxe SQL de Google BigQuery : Un guide complet

Qu'est-ce que Google BigQuery ? Que peut-il apporter à votre organisation ? BigQuery SQL est-il une compétence utile ? Où pouvez-vous l'apprendre et où pouvez-vous vous exercer ?

Très peu d'organisations aujourd'hui ne sont pas informatisées. De nombreux processus font désormais appel à l'IoT (Internet des objets), où toutes sortes d'appareils sont mis en réseau et alimentent continuellement les systèmes informatiques en données en temps réel. Il en résulte une vaste quantité de données disponibles pour la prise de décision. C'est ce que l'on appelle souvent un lac de données.

Les principaux défis posés par ce flux de données sont de savoir comment et où les stocker et comment les mettre à la disposition des décideurs. BigQuery de Google est sans doute l'une des meilleures réponses à ces défis.

Les compétences en SQL BigQuery sont un atout important pour votre portefeuille. Si vous souhaitez apprendre BigQuery, par où commencer ? BigQuery prend en charge les normes ANSI SQL. La première étape consiste donc à acquérir des compétences SQL de base.

Si vous ne connaissez pas encore SQL, le cours LearnSQL.fr's SQL pour les débutants vous permettra de vous mettre rapidement à niveau. Vous apprendrez d'importants concepts de base de données ; à la fin du cours, vous serez en mesure d'extraire, d'agréger et d'analyser les données d'une ou de plusieurs tables pour obtenir des informations significatives. Le cours dure environ 10 heures et comprend 129 exercices interactifs qui vous apprennent à résoudre des problèmes réels avec SQL.

Qu'est-ce que Google BigQuery ?

Google BigQuery est une solution d'entreposage de données sans serveur et hautement évolutive. Elle peut analyser des téraoctets de données en quelques secondes et des pétaoctets de données en quelques minutes. Mais qu'est-ce que cela signifie réellement ?

Entrepôt de données

Les données sont généralement stockées pour l'un des deux objectifs suivants :

• soutenir les opérations quotidiennes d'une entreprise
• soutenir la prise de décision.

Le premier de ces objectifs est connu sous le nom de données opérationnelles, et il est souvent stocké par plusieurs systèmes informatiques différents qui effectuent diverses tâches nécessaires à l'organisation. La deuxième catégorie est celle des données analytiques. Idéalement, ces données devraient être stockées dans un endroit central où elles sont accessibles à toute personne ayant besoin d'informations.

Ce lieu central est connu sous le nom d'entrepôt de données, et il doit être optimisé pour rechercher et analyser rapidement de grands volumes de données. Vous utiliserez un processus connu sous le nom d'ETL (Extract, Transform, Load) pour transférer les données opérationnelles dans l'entrepôt.

En tant qu'entrepôt de données, BigQuery présente plusieurs avantages :

• Il dispose d'excellents outils ETL.
• Il utilise le système de fichiers Colossus, qui est conçu pour les données volumineuses ; il est facile d'ajouter de l'espace en cas de besoin.
• Il utilise SQL comme langage d'interrogation, ce qui le rend compatible avec les meilleurs outils de Business Intelligence (BI) tels que Google Data Studio.
• Google Data Studio permet de présenter les données de différentes manières.

Recherches rapides

BigQuery utilise le moteur de recherche Dremel pour traiter rapidement de grands volumes de données. Comme je l'ai mentionné précédemment, BigQuery peut analyser des pétaoctets (un pétaoctet équivaut à peu près à un million de gigaoctets) en quelques minutes. Comment y parvient-il ?

Chaque fois que vous effectuez une recherche dans BigQuery, des milliers de threads de travail se répartissent la tâche, ce qui permet d'analyser simultanément d'énormes quantités de données. Les résultats sont ensuite fusionnés et renvoyés à l'utilisateur.

Architecture sans serveur

Si vous utilisez BigQuery, vous ne louez pas de serveur. Cela signifie que vous n'avez pas à gérer l'infrastructure, à vous préoccuper des sauvegardes et de la sécurité, ou à vous demander si vous avez besoin de plus de disques durs. Vos données font partie du grand lac de données - ou de l'océan de données !

Cet océan de données numériques est réparti sur d'innombrables serveurs dans le monde entier. Si vous avez besoin de plus d'espace, Google l'alloue et le gère. Vous ne payez que ce que vous utilisez.

Qu'en est-il de la puissance de traitement nécessaire pour effectuer les calculs ? Google alloue la puissance de traitement au fur et à mesure des besoins. Le logiciel de réseau à grande vitesse de Google, Jupiter, est spécialement conçu pour une communication rapide entre les threads ; vous ne payez que pour les créneaux de traitement que vous utilisez.

Qu'en est-il de la sécurité ? Vous pouvez créer des vues pour vos données et spécifier exactement qui peut voir quoi. Les données étant cryptées, tant lors de leur stockage que lors de leur transmission, elles sont à l'abri des intrus.

Mise en pratique de BigQuery

Si vous connaissez déjà le langage SQL, vous pouvez essayer BigQuery dès maintenant. Si ce n'est pas le cas, vous pouvez toujours essayer certains des exemples de la section suivante pour avoir une bonne idée de ce qu'il peut faire. Mais pour tirer pleinement parti de BigQuery, il est préférable d'améliorer vos compétences en SQL en vous inscrivant à un cours complet sur le langage SQL. LearnSQL.fr La piste d'apprentissage de BigQuery Le SQL de A à Z comprend 7 cours qui vous emmènent du débutant à l'expert.

Où et comment pouvez-vous mettre en pratique ces compétences dans BigQuery ? Tout le monde peut créer un projet sur BigQuery gratuitement. Vous ne payez que lorsque vous voulez charger et traiter de grandes quantités de données. Le bac à sable BigQuery vous permet d'insérer vos propres données de test. Vous pouvez également accéder à un large éventail de bases de données publiques pour jouer avec.

Pour commencer, visitez la page BigQuery de Google Cloud Console. Si vous n'êtes pas encore connecté à un compte Google, vous serez peut-être invité à le faire. La première fois que vous accédez à cette console, vous verrez un message comme celui-ci :

Cliquez sur CRÉER UN PROJET et donnez un nom à votre projet ou acceptez la suggestion de Google.

Vous avez maintenant besoin de données pour vous entraîner. Heureusement, BigQuery propose de nombreuses données publiques. Pour y accéder, vous devez ajouter les ensembles de données publiques à votre espace de travail SQL. En haut à gauche de la page, vous verrez ceci :

Cliquez sur AJOUTER. Un écran contextuel apparaît, vous offrant plusieurs choix. Cliquez sur celui-ci :

Le nom du projet vous sera demandé. Saisissez bigquery-public-data et cliquez sur 'STAR'. Vous devriez voir ce projet listé dans l'explorateur de projets à gauche de l'écran. Vous êtes maintenant prêt à commencer l'exploration. Si vous cliquez sur la flèche à côté de bigquery-public-data, vous verrez une liste de toutes les bases de données de ce projet. Et si vous cliquez sur une base de données, vous verrez une liste de ses tables. Les exemples de la section suivante de cet article sont tirés de la base de données census_bureau_international.

Si vous avez déjà de l'expérience avec SQL, c'est tout ce dont vous avez besoin pour commencer. Toutefois, si vous êtes un novice en SQL, vous souhaiterez peut-être vous exercer dans un environnement où l'aide est disponible et où vous êtes guidé dans la recherche de solutions aux problèmes. Dans ce cas, je vous recommande la piste de pratique de LearnSQL.fr, où vous aurez l'occasion de résoudre plus de 80 défis réels.

Syntaxe de BigQuery

Maintenant que vous savez comment vous configurer avec BigQuery, jetons un coup d'œil à la syntaxe de BigQuery. Mais tout d'abord, comme vous le savez peut-être déjà, le langage SQL comporte de nombreux dialectes différents, propres aux différents produits de base de données. Pour en savoir plus sur les différents dialectes SQL, cliquez ici.

L'American National Standards Institute (ANSI) définit les normes recommandées pour SQL ; chaque dialecte peut ou non mettre en œuvre l'ensemble de ces normes. Beaucoup d'entre eux ont leurs propres ajouts au langage pour permettre plus de fonctionnalités.

À l'origine, BigQuery disposait d'une version très peu standard de SQL, adaptée à ses besoins. Cette version est aujourd'hui appelée "Legacy SQL" et vous ne devrez l'apprendre que si vous souhaitez travailler pour une entreprise qui l'utilise encore. BigQuery utilise désormais Google SQL, qui est très proche de la norme ANSI. Si vous êtes déjà familiarisé avec un autre dialecte (tel que MS SQL Server ou MySQL), vous trouverez très peu de différences dans l'utilisation de BigQuery.

La principale différence réside dans le fait que BigQuery dispose de fonctionnalités statistiques supplémentaires et prend en charge des structures de données complexes telles que JSON et les tableaux.

Voyons quelques exemples d'exécution de requêtes dans la Cloud Console. Si vous n'avez jamais utilisé SQL auparavant et que vous avez du mal à suivre les exemples, cet article sur les commandes SQL les plus importantes pourrait vous être utile.

Vous trouverez la syntaxe complète de toutes les commandes SQL de Google ici.

Exécution de commandes dans la BigQuery Cloud Console

À droite de l'explorateur de projet dans la BigQuery Cloud Console, vous verrez une fenêtre dans laquelle vous pouvez exécuter des commandes Google SQL. Elle se présente comme suit :

Vous saisissez votre requête dans la fenêtre de requête, puis vous cliquez sur "RUN" dans la barre d'actions située en haut. Si vous avez tapé une commande SQL valide, vous verrez les données que vous avez demandées dans la fenêtre de résultats. Dans le cas contraire, un message d'erreur s'affiche.

Commandes SQL simples

L'extraction et l'analyse des données n'utilisent qu'une seule commande SQL : l'instruction SELECT. Dans sa forme la plus simple, la commande est SELECT * FROM tablename, où tablename est le nom d'une des tables de votre base de données. L'astérisque (*) indique à la commande de renvoyer toutes les colonnes de la table.

Dans Google SQL - à moins que les données ne proviennent de votre propre projet - vous préfixez le nom de la table par le nom du projet et de la base de données. Dans les exemples, je vais accéder aux données de la base de données census_bureau_international dans le projet bigquery-public-data. L'une des tables que j'utiliserai s'appelle midyear_population. Je devrai me référer à cette table en tant que :

bigquery-public-data.census_bureau_international.midyear_population

Pour plus de commodité, vous pouvez donner un alias à ce nom assez long afin de ne pas avoir à le taper sans cesse. Dans l'exemple ci-dessous, je lui donne l'alias midyear.

bigquery-public-data.census_bureau_international.midyear_population AS midyear

Lorsque vous effectuez des tests sur des tables qui peuvent être très volumineuses, il est conseillé de limiter le nombre de lignes renvoyées. Sinon, vous risquez de dépasser les limites de la version gratuite de BigQuery.

Pour voir tout ce qui se trouve dans les 10 premières lignes de ce tableau, vous devez saisir cette commande dans la fenêtre de requête, puis cliquer sur RUN:

SELECT * FROM 
bigquery-public-data.census_bureau_international.midyear_population 
AS midyear
LIMIT 10

Essayez par vous-même. À moins que vous n'ayez fait une erreur de frappe, votre écran devrait ressembler à ceci :

Vous pouvez utiliser la barre de défilement pour faire défiler les résultats.

Examinons maintenant quelques-unes des fonctions optionnelles de la déclaration SELECT.

• La clause WHERE vous permet de ne sélectionner que les lignes qui répondent à certains critères.
• Au lieu d'afficher toutes les colonnes (c'est-à-dire d'utiliser l'astérisque), vous pouvez énumérer les noms des colonnes que vous souhaitez voir. Dans l'image ci-dessus, les noms des colonnes (country_code, country_name, year, et midyear_population) sont affichés en haut de chaque colonne dans la fenêtre de résultats.
• Vous pouvez spécifier l'ordre des lignes dans l'ensemble des résultats à l'aide de la fonction ORDER BY

Essayez d'exécuter cette requête pour voir uniquement les noms de pays et les populations pour 1975, triés par nom de pays.

SELECT
midyear.country_name, midyear_population
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
WHERE
year = 1975
ORDER BY country_name

Si vous avez tout fait correctement, vous devriez voir ceci :

SQL dispose de beaucoup plus de fonctionnalités. Deux des plus utiles sont :

• L'utilisation de fonctions d'agrégation, telles que SUM() pour trouver les totaux, AVG() pour trouver les moyennes, et MIN() et MAX() pour trouver les valeurs les plus petites/les plus grandes. Les agrégats sont généralement utilisés en conjonction avec la clause GROUP BY, qui définit les groupes auxquels s'appliquent les fonctions d'agrégation. Si les données ne sont pas regroupées, les agrégats s'appliquent à l'ensemble des données.
• Récupération de données dans deux tables ou plus, à condition qu'elles aient en commun une ou plusieurs colonnes qui peuvent être utilisées pour les joindre.

Essayons quelques exemples de requêtes. Premièrement :

SELECT
midyear.country_name, AVG(midyear.midyear_population)
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
GROUP BY country_name
ORDER BY country_name

Cette requête indique la population moyenne en milieu d'année pour chaque pays sur l'ensemble de la période.

La requête suivante reprend les données de deux tables : la table midyear_population et la table birth_death_growth_rates. Vous pouvez alors voir le taux de croissance ainsi que la population.

Vous devez les relier à deux colonnes correspondantes : le pays et l'année. Voici la requête, suivie de ses résultats :

SELECT
midyear.country_name, midyear.midyear_population, 
growth.growth_rate
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
JOIN
bigquery-public-data.census_bureau_international.birth_death_growth_rates
AS growth
ON growth.country_name = midyear.country_name
AND growth.year = midyear.year
WHERE midyear.year = 1975
ORDER BY country_name

Pour toutes ces requêtes, BigQuery utilise la syntaxe standard ANSI ; les requêtes fonctionneraient également dans n'importe quel dialecte SQL.

Quelques commandes SQL avancé

Entrer en profondeur dans la syntaxe des instructions SQL complexes dépasse le cadre de cet article, mais j'aimerais inclure quelques requêtes avancées qui ont la même syntaxe dans Google SQL que dans d'autres dialectes (tels que MS SQL Server).

Les fonctions de fenêtre sont utilisées pour visualiser des lignes individuelles par rapport à des agrégats de l'ensemble des données. Cet exemple classe les pays en fonction de leur population - le pays ayant la population la plus élevée est classé 1.

SELECT
midyear.country_name, midyear.midyear_population, RANK() OVER (ORDER BY midyear_population DESC)
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
WHERE
year = 1975
ORDER BY country_name

Vos résultats devraient ressembler à ceci :

Parmi les autres fonctionnalités avancées, citons les extensions GROUP BY, qui vous permettent d'afficher des agrégats à plus d'un niveau. Là encore, la syntaxe de BigQuery suit les normes ANSI ; si vous avez appris ces fonctionnalités dans MS SQL, vous pourrez les utiliser dans BigQuery.

Voici un exemple. Cet exemple affichera les totaux au niveau de l'âge et du pays.

SELECT
m.country_name, m.age, SUM(m.population)
FROM
bigquery-public-data.census_bureau_international.midyear_population_agespecific
AS m
WHERE m.year = 1975
GROUP BY ROLLUP (m.country_name, m.age)
ORDER BY m.country_name, m.age

Les résultats ressemblent à ceci. Les zéros indiquent que cette ligne de résultats est un total.

Fonctionnalités supplémentaires de BigQuery

BigQuery possède de nombreuses fonctionnalités que l'on ne retrouve pas dans la plupart des dialectes. Celles-ci le rendent particulièrement utile pour traiter des données volumineuses provenant de nombreuses sources différentes.

Il dispose de plusieurs types de données supplémentaires :

• Geography - Stocke les coordonnées géographiques et peut être utilisé pour calculer la distance entre deux lieux.
• Interval - Stocke un intervalle de temps.
• JSON - Un format utilisé par de nombreuses applications.
• Struct - Un conteneur pour contenir des champs ordonnés.

BigQuery vous permet également de travailler avec des tableaux.

Lorsque l'on travaille avec de très grands ensembles de données, il est particulièrement utile de pouvoir extraire des échantillons aléatoires de données. Voici un exemple de prélèvement d'un échantillon aléatoire dans le tableau midyear_population.

SELECT
AVG (m.life_expectancy)
FROM
bigquery-public-data.census_bureau_international.mortality_life_expectancy
AS m
TABLESAMPLE SYSTEM (10 PERCENT)

Au lieu de parcourir l'ensemble du tableau, qui est volumineux, cet exemple prélèvera un échantillon statistique de 10 % du tableau. Comme l'échantillon est aléatoire, vous pouvez obtenir des résultats légèrement différents à chaque fois que vous l'exécutez. La sortie peut ressembler à ceci :

Pour en savoir plus sur les fonctionnalités supplémentaires de BigQuery SQL, consultez la documentation du produit.

Travailler avec Google BigQuery : La prochaine étape

L'océan numérique s'élargit d'année en année et de plus en plus d'employeurs recherchent des compétences en BigQuery. Cela vaut la peine de devenir un expert dans ce domaine. Mais comment ?

Améliorez vos compétences en SQL en suivant des cours. LearnSQL.fr propose un large éventail de cours couvrant tous les sujets, des débutants aux plus avancés. Vous pouvez rechercher des cours spécifiques ou simplement parcourir notre offre.

LearnSQL.frLe cours de pratique d'AOL vous apprend à être agile dans la résolution de toutes sortes de problèmes avec le langage SQL. Vous devriez également passer beaucoup de temps à expérimenter avec les ensembles de données publiques dans Google Cloud Console. Si vous avez votre propre entreprise et que vous souhaitez mettre en place un programme de formation SQL pour votre personnel, nous pouvons également vous aider.

BigQuery est là pour rester, alors commencez à devenir un expert dès maintenant !