#Azure et #Snowflake votre PaaS vers la modernisation BI – #2 copie des données brutes en base

Dans l’article précédent , nous avons effectué la première étape permettant de copier, sur un Azure Blob Storage Account, l’ensemble des données relatives aux courses des taxis jaunes New Yorkais.

Cette opération a été réalisée avec Azure Data Factory en une dizaine de minute, nous permettant ainsi de récupérer 113 fichiers CSV d’une taille totale de 237 Go.

Maintenant que mes données brutes ont été rapatriées, voyons comment les charger dans Snowflake.nyctlc_snowflake_copy

Via un navigateur Web, je me connecte à mon compte Snowflake. Je commence par créer un virtual Warehouse qui sera dédié aux traitements de type ELT.

nyctlc_snow_elt_cluster

Cette opération est bien sur scriptable en SQL

CREATE WAREHOUSE ELT_WH WITH 
WAREHOUSE_SIZE = 'SMALL' 
WAREHOUSE_TYPE = 'STANDARD' 
AUTO_SUSPEND = 300 
AUTO_RESUME = TRUE 
MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 1 
SCALING_POLICY = 'STANDARD';

Les tailles des Warehouse sont comme des tailles de T-Shirt cela va du XS au 4XL. . Ca c’est pour la partie scale up/down (élasticité verticale). Une fois la taille adéquate identifiée en fonction de la charge de travail, vous pouvez aussi jouer sur du scale in/out (élasticité horizontale) afin que le Warehouse s’adapte automatiquement aux augmentations/diminutions de concurrence d’accès, vous pouvez aller jusqu’à un maximum de 10 clusters par Warehouse.

Toutes modifications de taille de Warehouse et/ou de nombre de clusters sont quasi instantanées, tout comme les opérations d’arrêt/redémarrage. Vous n’avez rien à faire le système s’adapte automatiquement en fonction de la charge de travail et de votre paramétrage.

Le coût d’un cluster XS est de 1 crédit/heure et il vous en coûtera 128 crédits/heure pour un 4XL. Rendez-vous sur le site de Snowflake pour obtenir les informations tarifaire sur le coût de stockage, et le côut de compute (1 crédit/heure). Il est à noté que la facturation s’effectue à la seconde.

Après cette digression, revenons à nos moutons. Maintenant que j’ai un Warehouse, je créé une base de données pour le Data Provider et dans cette base je créé un schéma pour la Data Source., respectivement DP_NYCTLC_DB et DS_YELLOW.

CREATE DATABASE IF NOT EXISTS "DP_NYCTLC_DB";
CREATE SCHEMA IF NOT EXISTS "DP_NYCTLC_DB"."DS_YELLOW";
USE "DP_NYCTLC_DB"."DS_YELLOW";

Pour charger les fichiers CSV contenu sur mon stockage externe de staging de type Azure Blob Storage Account, j’ai besoin de déclarer un format de fichier dans lequel je spécifie les différentes caractéstiques (type, lignes d’entête à ignorer, séparateur de colonnes, séparateur de ligne, format de date, etc.)

CREATE OR REPLACE FILE FORMAT "FMT_YELLOW_TRIPS_CSV_01"
    TYPE = CSV 
    SKIP_HEADER = 2
    COMPRESSION = AUTO
    RECORD_DELIMITER = '\n'
    FIELD_DELIMITER = ','
    SKIP_HEADER = 2
    DATE_FORMAT = AUTO
    TIME_FORMAT = AUTO
    TIMESTAMP_FORMAT = AUTO
    ESCAPE_UNENCLOSED_FIELD = NONE
    TRIM_SPACE = TRUE
    ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE
    EMPTY_FIELD_AS_NULL = TRUE
    ENCODING = UTF8
;

Afin de pouvoir me connecter, il me faut générer une clé SAS Token. Ce qui me permet de créer un point de montage sur le container adéquat :

CREATE OR REPLACE STAGE STG_AZU
  url='azure://infeenylabsta01.blob.core.windows.net/snowflake-stage/nyctlc/ds-yellow/trips/'
  credentials=(azure_sas_token='?sv=2018-03-28&ss=b&srt=sco&sp=rwdlac&se=2050-12-31T08:10:45Z&st=2019-03-08T00:10:45Z&spr=https&sig=mrNtMfdqfflkj43FDs4ûqQrfvgweD%2F6BtJ%2BEggU%3D')
;

Il est ensuite nécessaire de créer les tables dans lesquelles les données brutes doivent être insérées. Pour rappel, les données des courses des taxis new yorkais utilisent 4 format de structure différente:

  • De janvier 2009 à décembre 2012
CREATE TABLE IF NOT EXISTS "TBL_YELLOW_TRIPS_01"
(
    VENDOR_CD STRING
    ,PICKUP_DT DATETIME
    ,DROPOFF_DT DATETIME
    ,PASSENGER_COUNT_NM INT
    ,TRIP_DISTANCE_NM DOUBLE
    ,PICKUP_LONG_NM DOUBLE
    ,PICKUP_LAT_NM DOUBLE
    ,RATE_CODE_ID INT
    ,STORE_AND_FORWARD_LB STRING
    ,DROPOFF_LONG_NM DOUBLE
    ,DROPOFF_LAT_NM DOUBLE
    ,PAYMENT_TYPE_CD STRING
    ,FARE_AMOUNT_NM DOUBLE
    ,SURCHARGE_AMOUNT_NM DOUBLE
    ,MTA_TAX_AMOUNT_NM DOUBLE
    ,TIP_AMOUNT_NM  DOUBLE
    ,TOLLS_AMOUNT_NM  DOUBLE
    ,TOTAL_AMOUNT_NM DOUBLE
);
>/pre>
  • De janvier 2015 à juin 2016
CREATE TABLE IF NOT EXISTS "TBL_YELLOW_TRIPS_02"
(
    VENDOR_ID INT
    ,PICKUP_DT DATETIME
    ,DROPOFF_DT DATETIME
    ,PASSENGER_COUNT_NM INT
    ,TRIP_DISTANCE_NM DOUBLE
    ,PICKUP_LONG_NM DOUBLE
    ,PICKUP_LAT_NM DOUBLE
    ,RATE_CODE_ID INT
    ,STORE_AND_FORWARD_LB STRING
    ,DROPOFF_LONG_NM DOUBLE
    ,DROPOFF_LAT_NM DOUBLE
    ,PAYMENT_TYPE_ID INT
    ,FARE_AMOUNT_NM DOUBLE
    ,EXTRA_AMOUNT_NM DOUBLE
    ,MTA_TAX_AMOUNT_NM DOUBLE
    ,TIP_AMOUNT_NM  DOUBLE
    ,TOLLS_AMOUNT_NM  DOUBLE
    ,SURCHARGE_AMOUNT_NM DOUBLE
    ,TOTAL_AMOUNT_NM DOUBLE
);
>/pre>
  • De juillet 2016 à décembre 2016
CREATE TABLE IF NOT EXISTS "TBL_YELLOW_TRIPS_03"
(
    VENDOR_ID INT
    ,PICKUP_DT DATETIME
    ,DROPOFF_DT DATETIME
    ,PASSENGER_COUNT_NM INT
    ,TRIP_DISTANCE_NM DOUBLE
    ,RATE_CODE_ID INT
    ,STORE_AND_FORWARD_LB STRING
    ,PICKUP_LOCATION_ID INT
    ,DROPOFF_LOCATION_ID INT
    ,PAYMENT_TYPE_ID INT
    ,FARE_AMOUNT_NM DOUBLE
    ,EXTRA_AMOUNT_NM DOUBLE
    ,MTA_TAX_AMOUNT_NM DOUBLE
    ,TIP_AMOUNT_NM  DOUBLE
    ,TOLLS_AMOUNT_NM  DOUBLE
    ,SURCHARGE_AMOUNT_NM DOUBLE
    ,TOTAL_AMOUNT_NM DOUBLE
    ,FILLER1_LB STRING
    ,FILLER2_LB STRING
);
>/pre>
  •  A partie de janvier 2017
CREATE TABLE IF NOT EXISTS "TBL_YELLOW_TRIPS_04"
(
    VENDOR_ID INT
    ,PICKUP_DT DATETIME
    ,DROPOFF_DT DATETIME
    ,PASSENGER_COUNT_NM INT
    ,TRIP_DISTANCE_NM DOUBLE
    ,RATE_CODE_ID INT
    ,STORE_AND_FORWARD_LB STRING
    ,PICKUP_LOCATION_ID INT
    ,DROPOFF_LOCATION_ID INT
    ,PAYMENT_TYPE_ID INT
    ,FARE_AMOUNT_NM DOUBLE
    ,EXTRA_AMOUNT_NM DOUBLE
    ,MTA_TAX_AMOUNT_NM DOUBLE
    ,TIP_AMOUNT_NM  DOUBLE
    ,TOLLS_AMOUNT_NM  DOUBLE
    ,SURCHARGE_AMOUNT_NM DOUBLE
    ,TOTAL_AMOUNT_NM DOUBLE
);
>/pre>

Pour effectuer la copie des données depuis mon blob storage vers chacune des tables, j’emploi l’ordre SQL COPY INTO et je définie pour chaque table de destination le pattern de fichier à charger via des expressions régulières :

COPY INTO "TBL_YELLOW_TRIPS_01"
FROM  @STG_AZU
PATTERN = '.*(200[9]|201[0-4]).*.csv'
FILE_FORMAT = (FORMAT_NAME = FMT_YELLOW_TRIPS_CSV_01)
ON_ERROR = CONTINUE;

COPY INTO "TBL_YELLOW_TRIPS_02"
FROM  @STG_AZU
PATTERN = '.*(2015|2016\-0[1-6]).*.csv'
FILE_FORMAT = (FORMAT_NAME = FMT_YELLOW_TRIPS_CSV_01)
ON_ERROR = CONTINUE;

COPY INTO "TBL_YELLOW_TRIPS_03"
FROM  @STG_AZU
PATTERN = '.*(2016\-0[7-9]|2016\-1[0-2]).*.csv'
FILE_FORMAT = (FORMAT_NAME = FMT_YELLOW_TRIPS_CSV_01)
ON_ERROR = CONTINUE;

COPY INTO "TBL_YELLOW_TRIPS_04"
FROM  @STG_AZU
PATTERN = '.*(201[7-9]|20[2-4][0-9]).*.csv'
FILE_FORMAT = (FORMAT_NAME = FMT_YELLOW_TRIPS_CSV_01)
ON_ERROR = CONTINUE;

Il est temps d’exécuter l’ensemble du script SQL via l’inteface Web Snowflake :

nyctlc_snowflake_worksheet_load_raw_data

Au final, les temps d’exécution obtenus (en incluant le redémarrage et l’arrêt du warehouse) :

  • Cluster S : 12min40s
  • Cluster XL : 4min51s

nyctlc_snowflake_copy_result

Avant de clôturer cet article, jetons un œil sur les données chargées et la cure d’amincissement obtenue

nyctlc_snowflake_db_tables

Au final, je retrouve bien les 1 469 021 153 lignes le tout stocké sur 40,6 Go au lieu des 237Go initiaux.

Dans le prochain article, nous passerons à l’étape de transformation de données en combinant données structurées (les courses que nous venons de charger) et semi-structurées en intégrant les différents référentiels au format JSON.

 

Répondre

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google

Vous commentez à l'aide de votre compte Google. Déconnexion /  Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s