dbr

DBR: conexões e consultas de banco de dados convenientes de R de R

Vinheta chegando, até então, verifique a palestra apresentada no usuário! Conferência de 2018:

• deslizamentos
• vídeo

Configurando um arquivo de configuração para as conexões de banco de dados

Para poder conectar -se a um banco de dados, os parâmetros de conexão devem ser especificados em um arquivo YAML, por exemplo, para que um banco de dados SQLite seja criado em um arquivo temp:

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

Por padrão, dbr procurará um arquivo chamado db_config.yaml no diretório de trabalho atual, que pode ser substituído por meio da opção Global dbr.db_config_path , por exemplo, para a configuração de exemplo, incluída neste pacote:

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

Um exemplo mais complexo do arquivo YAML Demo que descreve uma conexão MySQL com um banco de dados hospedado pelo RSTUDIO (com nome de usuário e senha públicos):

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

Observe que, em vez de seqüências simples, você também pode especificar senhas criptografadas com KMS, outros segredos e parâmetros, por exemplo,:

 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 

Bancos de dados de consulta

Depois que os parâmetros de conexão são carregados de um arquivo de configuração, fazer as consultas SQL são tão fáceis quanto especificar a instrução SQL e o nome da conexão:

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

Para uso mais avançado, por exemplo, conexões de banco de dados de cache, verifique ?db_connect e a vinheta mencionada acima.

Modelagem SQL

Para reutilizar os pedaços SQL, você pode listar suas consultas SQL (ou partes dele) em um arquivo YAML estruturado, como no exemplo de configuração de exemplo em exemplo, example_sql_chunks.yaml

Use sql_chunk_files para listar ou atualizar o (s) modelo (s) do modelo (s) do modelo (s) do modelo (s) atualmente usado, por exemplo

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

Em seguida, você pode se referir a qualquer chave nessa definição por uma string que consiste nas chaves na hierarquia separadas por um ponto, portanto, olhando para a definição abaixo (parte de example_sql_chunks.yaml ):

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

Obtendo a chave count do item countries na seção shinydemo da dbr , você pode fazer algo como:

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

E passe imediatamente para 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

Os pedaços SQL também podem ser definidos em arquivos fora da YAML com as extensões de arquivo sql e referenciadas com a tag !include no arquivo YAML, por exemplo:

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

Isso lerá o conteúdo da europe.sql e o disponibilizará como sql_chunk('dbr.shinydemo.countries.count') .

Além dos arquivos, uma pasta com arquivos sql também pode ser incluída - nesse caso, o nome do arquivo base (sem a extensão do arquivo sql ) se tornará a chave sob a chave fornecida. Por exemplo, considere esta definição YAML:

 cities : !include cities.sql

Carregará todos os arquivos da pasta cities.sql e os disponibilizará na europe , resultando em uma YAML intermediária como:

 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)}))

Se a chave de um diretório !include é ~! , então as chaves são disponibilizadas no nó pai, então por exemplo

 cities:
  ~!: !include cities.sql

Na verdade, não criaria a chave cities , mas apenas as teclas europe e europe_large no nó raiz.

Como você pode ver no exposto, o principal poder dessa abordagem de modelagem é que você pode reutilizar facilmente os pedaços de SQL, por exemplo, para a lista de países europeus em:

 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

Onde a subconsulta relacionada Country foi especificada na chave dbr.shinydemo.countries.europe conforme:

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

O parâmetro indent_after_linebreak é apenas para atualizações de cosméticos na consulta para se FROM e WHERE no mesmo caractere na instrução SQL.

Exemplo ainda mais complexo / aninhado:

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')))