dbr

DBR: Connexions et requêtes de base de données pratiques de R

Vignette à venir, jusque-là, veuillez vérifier le discours présenté à l'utilisateur! Conférence 2018:

• diapositives
• vidéo

Configuration d'un fichier de configuration pour les connexions de la base de données

Pour pouvoir se connecter à une base de données, les paramètres de connexion doivent être spécifiés dans un fichier YAML, par exemple pour une base de données SQLite à créer dans un fichier temporaire:

 sqlite :
  drv : !expr RSQLite::SQLite()
  dbname : !expr tempfile()

Par défaut, dbr recherchera un fichier nommé db_config.yaml dans le répertoire de travail actuel, qui peut être remplacé via l'option globale dbr.db_config_path , par exemple à l'exemple de configuration regroupé dans ce package:

options( dbr.db_config_path = system.file( ' example_db_config.yaml ' , package = ' dbr ' ))

Un exemple plus complexe du fichier de démonstration YAML décrivant une connexion MySQL à une base de données hébergée par RStudio (avec nom d'utilisateur et mot de passe public):

 shinydemo :
  drv : !expr RMySQL::MySQL()
  host : shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com
  username : guest
  password : guest
  dbname : shinydemo

Remarque qu'au lieu de chaînes simples, vous pouvez également spécifier des mots de passe cryptés par KMS, d'autres secrets et paramètres, par exemple:

 redshift :
  host : !aws_kms |
    KMSencryptedciphertext...
  port : 5439
  dbname : dbname
  user : username
  drv : !expr RPostgreSQL::PostgreSQL()
  password : !aws_kms |
    KMSencryptedciphertext...
  s3_copy_bucket : !attr |-
    's3://openmail-model/temp'
  s3_copy_iam_role : !attr |-
    arn:aws:iam::accountid:role/redshift_role 

Interrogation des bases de données

Une fois que les paramètres de connexion sont chargés à partir d'un fichier de configuration, les requêtes SQL sont aussi simples que de spécifier l'instruction SQL et le nom de la connexion:

db_query( ' show tables ' , ' shinydemo ' )
# > INFO [2019-01-06 01:06:18] Connecting to shinydemo
# > INFO [2019-01-06 01:06:19] Executing:**********
# > INFO [2019-01-06 01:06:19] show tables
# > INFO [2019-01-06 01:06:19] ********************
# > INFO [2019-01-06 01:06:19] Finished in 0.1336 secs returning 3 rows
# > INFO [2019-01-06 01:06:19] Closing connection to shinydemo
# >   Tables_in_shinydemo
# > 1                City
# > 2             Country
# > 3     CountryLanguage

Pour une utilisation plus avancée, par exemple, les connexions de la base de données de mise en cache, vérifiez ?db_connect et la vignette mentionnée ci-dessus.

Modèles SQL

Pour réutiliser SQL Chunks, vous pouvez répertorier vos requêtes SQL (ou parties de celui-ci) dans un fichier YAML structuré, comme dans l'exemple de configuration groupé à example_sql_chunks.yaml

Utilisez sql_chunk_files

sql_chunk_files(system.file( ' example_sql_chunks.yaml ' , package = ' dbr ' ))

Ensuite, vous pouvez vous référer à toute clé de cette définition par une chaîne qui se compose des clés dans la hiérarchie séparées par un point, en regardant la définition ci-dessous (partie d' example_sql_chunks.yaml ):

 dbr :
  shinydemo :
    countries :
      count : SELECT COUNT(*) FROM Country

Obtenir la clé count pour l'élément countries dans la section shinydemo de dbr , vous pouvez faire quelque chose comme:

sql_chunk( ' dbr.shinydemo.countries.count ' )
# > SELECT COUNT(*) FROM Country

Et passez-le tout de suite à db_query :

 countries <- db_query(sql_chunk( ' dbr.shinydemo.countries.count ' ), ' shinydemo ' )
# > INFO [2019-01-06 01:33:33] Connecting to shinydemo
# > INFO [2019-01-06 01:33:34] Executing:**********
# > INFO [2019-01-06 01:33:34] SELECT COUNT(*) FROM Country
# > INFO [2019-01-06 01:33:34] ********************
# > INFO [2019-01-06 01:33:34] Finished in 0.1291 secs returning 1 rows
# > INFO [2019-01-06 01:33:34] Closing connection to shinydemo

Les morceaux SQL peuvent également être définis dans des fichiers en dehors du YAML avec les extensions de fichiers sql , et référencés avec la balise !include dans le fichier YAML, par exemple:

 dbr :
  shinydemo :
    countries :
      europe : !include europe.sql

Cela lira le contenu de europe.sql et le rendra disponible en tant que sql_chunk('dbr.shinydemo.countries.count') .

Outre les fichiers, un dossier avec des fichiers sql peut également être inclus - dans ce cas, le nom de fichier de base (sans l'extension de fichier sql ) deviendra la clé sous la clé donnée. Par exemple, considérez cette définition YAML:

 cities : !include cities.sql

Chargera tous les fichiers du dossier cities.sql et les rendront disponibles sous europe , ce qui entraîne un YAML intermédiaire comme:

 cities: !include cities.sql
  europe: |-
    SELECT Name
    FROM City
    WHERE CountryCode IN (
      {sql_chunk('dbr.shinydemo.countries.europe', indent_after_linebreak = 2)})
  europe_large: |-
    SELECT Name
    FROM City
    WHERE
      Population > 1000000 AND
      Name IN (
        {sql_chunk('dbr.shinydemo.cities.europe', indent_after_linebreak = 4)}))

Si la clé d'un répertoire !include est ~! , alors les clés sont disponibles dans le nœud parent, alors par exemple

 cities:
  ~!: !include cities.sql

Ne créerait pas réellement la clé cities , mais seulement les clés europe et europe_large dans le nœud racine.

Comme vous pouvez le voir ci-dessus, le principal pouvoir de cette approche de modèles est que vous pouvez facilement réutiliser des morceaux SQL, par exemple pour la liste des pays européens dans:

 cities <- db_query(sql_chunk( ' dbr.shinydemo.cities.europe ' ), ' shinydemo ' )
# > INFO [2019-01-06 01:32:02] Connecting to shinydemo
# > INFO [2019-01-06 01:32:02] Executing:**********
# > INFO [2019-01-06 01:32:02] SELECT Name
# > FROM City
# > WHERE CountryCode IN (
# >   SELECT Code
# >   FROM Country
# >   WHERE Continent = 'Europe')
# > INFO [2019-01-06 01:32:02] ********************
# > INFO [2019-01-06 01:32:02] Finished in 0.1225 secs returning 643 rows
# > INFO [2019-01-06 01:32:02] Closing connection to shinydemo

Où la sous-requête liée au Country a été spécifiée dans la clé dbr.shinydemo.countries.europe selon:

 SELECT Name
FROM City
WHERE CountryCode IN (
  {sql_chunk( ' dbr.shinydemo.countries.europe ' , indent_after_linebreak = 2 )})

Le paramètre indent_after_linebreak est juste pour les mises à jour cosmétiques dans la requête pour FROM et WHERE sur le même caractère dans l'instruction SQL.

Exemple encore plus complexe / imbriqué:

sql_chunk( ' dbr.shinydemo.cities.europe_large ' )
# > SELECT Name
# > FROM City
# > WHERE
# >   Population > 1000000 AND
# >   Name IN (
# >     SELECT Name
# >     FROM City
# >     WHERE CountryCode IN (
# >       SELECT Code
# >       FROM Country
# >       WHERE Continent = 'Europe')))