4 min read

GdT-Pyr 4 - ETL

2019-01-14

L’objectif de ce document est de tester une approche E-T-L avec le logiciel R.

Code complet sous: https://forgemia.inra.fr/gdtpyr/gdt_pyr/-/tree/main/GDT_PyR4_Flux_de_donnees_R

Pour l’approche sous Python: https://forgemia.inra.fr/gdtpyr/gdt_pyr/-/tree/main/GDT_PyR4_Flux_de_donnees_Python

  library(httr)      # requetes HTTP
  library(dplyr)     # data-management
  library(lubridate) # gestion du format date
  library(DT)        # visualiser les tableaux dans un .Rmd

ETL - Extract Transform Load

Quelques liens:

ETL est plus un principe de data-management qu’un logiciel à proprement dit. On peut écrire un ETL dans n’importe quel langage. L’ETL est en fait la coordination des 3 étapes suivantes:

  • Extraction
  • Transformation
  • Load (chargement)

dans le but de rendre l’acquisition et le formatage des données pérennes et reproductible.

Exemple minimal

Pour tester la démarche ETL, je pars du site de stockage de fichiers de données de qualité de l’air permettant de récupérer des données libres:

Il est possible de récupérer des données journalières par requête HTTP via leur web service, malheureusement, l’utilisateur n’a accès qu’au 60 derniers jours… Donc si on souhaite une plage de temps plus large, il faut se tourner vers les archives, où l’on trouve des fichiers CSV journaliers:

Découpage en étape ETL:

  • E: J’accède au site internet via la librairie httr par une requête HTTP (fonction GET())
  • T: puis je sélectionne les données de la ville de Paris dans la station FR04012 et uniquement pour le paramètre pm10
  • L: je fais ceci pour les dates que je veux et stocke mon dataset pour utilisation future.
ptm <- proc.time()

  # exemple: https://openaq-data.s3.amazonaws.com/2018-03-01.csv
  # Get the page's source
  url <- "https://openaq-data.s3.amazonaws.com"
  
  # Sélection des fichiers du mois de mars 2018
  myDate<-ymd(paste0("2018","-","03","-",seq(1,30)))
  
  print(myDate)
##  [1] "2018-03-01" "2018-03-02" "2018-03-03" "2018-03-04" "2018-03-05"
##  [6] "2018-03-06" "2018-03-07" "2018-03-08" "2018-03-09" "2018-03-10"
## [11] "2018-03-11" "2018-03-12" "2018-03-13" "2018-03-14" "2018-03-15"
## [16] "2018-03-16" "2018-03-17" "2018-03-18" "2018-03-19" "2018-03-20"
## [21] "2018-03-21" "2018-03-22" "2018-03-23" "2018-03-24" "2018-03-25"
## [26] "2018-03-26" "2018-03-27" "2018-03-28" "2018-03-29" "2018-03-30"
  mydata<-data.frame()
  for (i in 1:length(myDate)){
    # Requête Extraction: 
    # je pourrais écrire une fonction extractFromOpenAqData()
    print(paste0(url,"/",myDate[i],".csv"))
    tmp<-GET(paste0(url,"/",myDate[i],".csv"))
    
    # Formatage en dataframe et sélection des lignes nécessaires:
    # je pourrais écrire une fonction transformCsvToDataframe()
    df <- read.csv(textConnection(content(tmp, 'text')))
    df2<-filter(df,city=="Paris" & location=="FR04012" & parameter=="pm10")
    
    # Stockage
    mydata<-rbind.data.frame(mydata,df2)
  }
## [1] "https://openaq-data.s3.amazonaws.com/2018-03-01.csv"
## Error in curl::curl_fetch_memory(url, handle = handle): Unrecognized content encoding type. libcurl understands deflate, gzip content encodings.
  print(str(mydata))
## 'data.frame':    0 obs. of  0 variables
## NULL
  # En minutes écoulées
print((proc.time() - ptm)/60)
## utilisateur     système      écoulé 
## 0.002166667 0.000000000 0.013500000
  # write.table() pour sauvegarder votre dataset et pouvoir le recharger a posteriori

  # visualisation du dataset pour ce Rmd...
  datatable(mydata,rownames=FALSE,options = list(scrollX=TRUE))
  # Je souhaite sauvegarder mes données dans une base de données en mémoire
  # type SQL
  library(RSQLite)
  # Create an ephemeral in-memory RSQLite database
  # bien lire la doc de la fonction dbConnect()
  con <- dbConnect(RSQLite::SQLite(), ":memory:")
  dbWriteTable(con, "airQuality", mydata)
## Error: x must be character or SQL
  dbListTables(con)
## character(0)
  dbListFields(con, "airQuality")
## Error: no such table: airQuality

Quelques exemples de requêtes à des bases de données

Base de données relationnelle type SQL, exemple(PostgreSQL)

  # Très rapide, meilleur que RODBC, prendre plutôt celui ci 
  # surtout pour un depot de données sur la base
  library(RPostgreSQL) # to insert dataframe in database
  drv <- dbDriver("PostgreSQL")
  phisdb <- dbConnect(drv, dbname = "myDB",host="147.00.0.00",user = "theUser",
                      password = "thePWD")
  myExpTable<-dbGetQuery(phisdb,paste("SELECT * from at_experiment_variable_method_unit 
                                      WHERE experiment_id='",
                                      experimentName,
                                      "' AND category='phenotyping'",sep=""))
  dbWriteTable(phisdb,"at_experiment_variable_method_unit",value = myExpTable, 
               append = TRUE,row.names = FALSE)
  
  
  # Très efficace car pas de stockage en mémoire, par contre les requètes 
  # ne sont pas en "SQL-like" et difficulté, pour le moment, 
  # pour écrire les requêtes complexes avec plusieurs jointures: à travailler
  library(dplyr)
  meteodb<-src_postgres(dbname="myDB",host="147.00.0.00",user="theUser",
                        password="thePWD")
  myRequete <- tbl(meteodb, from="measureMeteo") %>%
                select (date, value, sensor, codeVariable) %>%
                filter(codeVariable == 'air humidity:weather station:percentage' & 
                date >= '2012-02-11' & date <= '2012-03-10' & 
                site == 'http://mon-url') 
  mydata <- collect(myRequete)

Base de données noSQL: exemple mongo

mongo est le type de base de données noSQL à documents.

  # pour savoir comment écrire ma requete mongo, je fais des tests 
  # dans robo3T (avant robomongo)
  library(mongolite)
  myMongo<-mongo(collection="maCollection",db="m3p",url="mongodb://147.00.0.00:1111",verbose=FALSE)
  queryManip<-'{"context.plantPatternAlias.REPETITION": {"$exists":true}, 
                "context.plantPatternAlias.POSITION": {"$exists":true},
                "context.experiment":"http://mon-url/mon-essai",
                "images.1.labelView":"side0"}'
  dataManip<-'{"data.extent_y.value":1,"images.1.date":1,"context.genotypeAlias":1,
                 "context.plantPatternAlias.SEEDLOT":1,
                 "context.plantPatternAlias.REPETITION":1,
                 "context.plantPatternAlias.POSITION":1,
                 "context.plantPatternAlias.SCENARIO":1}'

  myManip<-myMongo$find(query = queryManip, fields = dataManip)

  # myManip est un dataframe à la structure complexe qu'il faut reformater avant utilisation

Session Informations

## R version 4.1.2 (2021-11-01)
## Platform: x86_64-w64-mingw32/x64 (64-bit)
## Running under: Windows 10 x64 (build 19044)
## 
## Matrix products: default
## 
## locale:
## [1] LC_COLLATE=French_France.1252  LC_CTYPE=French_France.1252   
## [3] LC_MONETARY=French_France.1252 LC_NUMERIC=C                  
## [5] LC_TIME=French_France.1252    
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
## [1] RSQLite_2.2.11  DT_0.20         lubridate_1.8.0 dplyr_1.0.7    
## [5] httr_1.4.2     
## 
## loaded via a namespace (and not attached):
##  [1] Rcpp_1.0.7        pillar_1.6.4      bslib_0.3.1       compiler_4.1.2   
##  [5] jquerylib_0.1.4   tools_4.1.2       bit_4.0.4         digest_0.6.29    
##  [9] memoise_2.0.1     jsonlite_1.8.0    evaluate_0.14     lifecycle_1.0.1  
## [13] tibble_3.1.6      pkgconfig_2.0.3   rlang_0.4.12      DBI_1.1.2        
## [17] crosstalk_1.2.0   curl_4.3.2        yaml_2.2.1        blogdown_1.7     
## [21] xfun_0.29         fastmap_1.1.0     stringr_1.4.0     knitr_1.37       
## [25] generics_0.1.1    vctrs_0.3.8       sass_0.4.0        htmlwidgets_1.5.4
## [29] bit64_4.0.5       tidyselect_1.1.1  glue_1.6.0        R6_2.5.1         
## [33] fansi_0.5.0       rmarkdown_2.11    bookdown_0.24     blob_1.2.2       
## [37] purrr_0.3.4       magrittr_2.0.1    htmltools_0.5.2   ellipsis_0.3.2   
## [41] assertthat_0.2.1  utf8_1.2.2        stringi_1.7.6     cachem_1.0.6     
## [45] crayon_1.4.2