Geekly articles weekly

Interação de R com bancos de dados no exemplo do Microsoft SQL Server e outros DBMS

Como a maior parte das informações comerciais é armazenada em bancos de dados. Em qualquer linguagem de programação que você escreve, você deve executar várias ações com elas.


Neste artigo, falarei sobre duas interfaces para trabalhar com bancos de dados em R. A maioria dos exemplos demonstra como trabalhar com o Microsoft SQL Server, no entanto, todos os exemplos de código funcionarão com outros bancos de dados, como: MySQL, PostgreSQL, SQLite, ClickHouse , Google BigQuery etc.


imagem


Conteúdo



Software necessário


Para repetir todos os exemplos de trabalho com o DBMS descritos no artigo, você precisará do seguinte software livre listado abaixo:


  1. Linguagem R ;
  2. Ambiente de desenvolvimento do RStudio ;
  3. Sistema de Gerenciamento de Banco de Dados, para escolher:
    3.1 Microsoft SQL Server
    3.2 MySQL
    3.3 PostgreSQL

Pacote DBI


O pacote DBI é a maneira mais popular e conveniente de interagir com os bancos de dados em R.


DBI fornece um conjunto de funções com as quais você pode gerenciar bancos de dados. Mas, para se conectar aos bancos de dados, você precisa instalar pacotes adicionais que são drivers para vários sistemas de gerenciamento de banco de dados (DBMS).


Lista de funções básicas do DBI


  • dbConnect - conexão com o banco de dados;
  • dbWriteTable - escreve uma tabela no banco de dados;
  • dbReadTable - carregando uma tabela de um banco de dados;
  • dbGetQuery - carregando o resultado da execução da consulta;
  • dbSendQuery - enviando uma consulta ao banco de dados;
  • dbFetch - extrai elementos de um conjunto de resultados;
  • dbExecute - execução de solicitações para atualizar / excluir / inserir dados em tabelas;
  • dbGetInfo - solicita informações sobre o resultado da solicitação ou conexão;
  • dbListFields - solicitação de uma lista de campos da tabela;
  • dbListTables - consulta uma lista de tabelas de banco de dados;
  • dbExistsTable - verifica a presença de uma tabela no banco de dados;
  • dbRemoveTable - exclui uma tabela do banco de dados;
  • dbDisconnect - desconecta do banco de dados.

Conectividade do banco de dados


Para interagir com bancos de dados, você deve primeiro se conectar a eles. Dependendo do DBMS com o qual planeja trabalhar, você precisará de um pacote adicional. Abaixo está uma lista dos pacotes mais usados.


  • odbc - Driver para conexão via interface ODBC;
  • RSQLite - Driver para SQLite;
  • RMySQL / RMariaDB - Driver para MySQL e MariaDB;
  • RPostgreSQL - Driver para PosrtgreSQL;
  • bigrquery - Driver para Google BigQuery;
  • RClickhouse / clickhouse - Driver para ClickHouse;
  • RMSSQL - O driver para Microsoft SQL Server (MS SQL), no momento da escrita, está presente apenas no GitHub .

O pacote DBI vem com o pacote R básico, mas os pacotes que são drivers de banco de dados devem ser instalados usando o comando install.packages(" ") .


Para instalar pacotes do GitHub, você também precisará de um pacote adicional - devtools . Por exemplo, o pacote RMSSQL não está publicado no repositório principal do pacote R, use o seguinte código para instalá-lo:


 install.packages("devtools") devtools::install_github("bescoto/RMSSQL")

Um exemplo de conexão com o Microsoft SQL Server usando o pacote odbc


Antes de usar qualquer pacote em uma sessão R, ele deve primeiro ser conectado usando a função de library(" ") .


Não foi à toa que escolhi o Microsoft SQL Server como o DBMS principal, que fornecerá a maioria dos exemplos deste artigo. O fato é que esse é um banco de dados bastante popular, mas, ao mesmo tempo, ainda não possui um driver para conexão do R publicado no CRAN.


Felizmente, porém, o SQL Server, como quase qualquer outro banco de dados, possui uma interface ODBC (English Open Database Connectivity) para conexão. Há vários pacotes para conectar-se ao DBMS através da interface ODBC em R. Primeiro, veremos a conexão através do pacote odbc .


Fácil conexão com o banco de dados via interface odbc
 #   odbc install.packages("odbc") #   library(odbc) #   MS SQL con <- dbConnect(drv = odbc(), Driver = "SQL Server", Server = "localhost", Database = "mybase", UID = "my_username", PWD = "my_password", Port = 1433)

Na função dbConnect() , você precisa passar a função, que é o driver para conectar-se ao DBMS ( odbc() ), como o primeiro argumento para drv . Tais funções são geralmente chamadas de DBMS e vêm com pacotes que são drivers para DBI .


Em seguida, você precisa listar os parâmetros de conexão. Para conectar-se ao MS SQL via ODBC, você deve especificar os seguintes parâmetros:


  • Driver - Nome do driver ODBC;
  • Servidor - endereço IP do servidor SQL;
  • Banco de Dados - O nome do banco de dados ao qual se conectar;
  • UID - nome de usuário do banco de dados;
  • PWD - Senha;
  • Porta - a porta à qual se conectar, com o SQL Server, a porta padrão é 1433.

Um driver ODBC para conectar-se ao Microsoft SQL Server está incluído no Windows, mas pode ter um nome diferente. Você pode exibir a lista de drivers instalados no ODBC Data Source Administrator. Você pode iniciar o administrador da fonte de dados no Windows 10 da seguinte maneira:


  • Versão de 32 bits: %systemdrive%\Windows\SysWoW64\Odbcad32.exe
  • Versão de 64 bits: %systemdrive%\Windows\System32\Odbcad32.exe


Você também pode obter uma lista de todos os drivers instalados no seu PC usando a função odbcListDrivers() .


  name attribute value <chr> <chr> <chr> 1 SQL Server APILevel 2 2 SQL Server ConnectFunctions YYY 3 SQL Server CPTimeout 60 4 SQL Server DriverODBCVer 03.50 5 SQL Server FileUsage 0 6 SQL Server SQLLevel 1 7 SQL Server UsageCount 1 8 MySQL ODBC 5.3 ANSI Driver UsageCount 1 9 MySQL ODBC 5.3 Unicode Driver UsageCount 1 10 Simba ODBC Driver for Google BigQuery Description Simba ODBC Driver for Google BigQuery2.0 # ... with 50 more rows

Você pode baixar drivers ODBC para outros DBMSs nos seguintes links:



Para vários DBMSs, o nome dos parâmetros para conexão pode ser diferente, por exemplo:


  • PostgreSQL / MySQL / MariaDB - usuário, senha, host, porta, dbname;
  • GoogleBigQuery - projeto, conjunto de dados;
  • ClickHouse - usuário, senha, banco de dados, porta, host;

Usando o administrador da fonte de dados ODBC, você pode executar o assistente para criar uma fonte de dados ODBC. Para fazer isso, basta abrir o administrador, vá para a guia "DSN personalizado" e clique no botão "Adicionar ...".



Ao criar uma fonte de dados usando um administrador, você indica um DSN (nome da fonte de dados).



No exemplo acima, criamos uma fonte de dados com o DSN "my_test_source". Agora podemos usar essa fonte para conectar-se ao Microsoft SQL Server e não especificar outros parâmetros de conexão no código.


Conexão ao banco de dados via interface odbc usando DSN
 #   DSN con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password")

Você pode ver os nomes de todas as fontes de dados ODBC criadas no seu PC usando a função odbcListDataSources() .


  name description <chr> <chr> 1 BQ Simba ODBC Driver for Google BigQuery 2 BQ_main Simba ODBC Driver for Google BigQuery 3 BQ ODBC Simba ODBC Driver for Google BigQuery 4 OLX Simba ODBC Driver for Google BigQuery 5 Multicharts Simba ODBC Driver for Google BigQuery 6 PostgreSQL35W PostgreSQL Unicode(x64) 7 hillel_bq Simba ODBC Driver for Google BigQuery 8 blog_bq Simba ODBC Driver for Google BigQuery 9 MyClientMSSQL SQL Server 10 local_mssql SQL Server 11 MSSQL_localhost SQL Server 12 my_test_source SQL Server 13 Google BigQuery Simba ODBC Driver for Google BigQuery

Um exemplo de conexão com o Microsoft SQL Server usando o pacote RMSSQL


RMSSQL não RMSSQL publicado no CRAN, portanto você pode instalá-lo no GitHub usando o pacote devtools .


 install.packages("devtools") devtools::install_github("bescoto/RMSSQL")

Exemplo de conexão usando o driver DBI RMSSQL
 #    library(RJDBC) library(RMSSQL) library(DBI) #  RMSSQL con <- dbConnect(MSSQLServer(), host = 'localhost', user = 'my_username', password = 'my_password', dbname = "mybase")

Na maioria dos casos, usando o pacote DBI para trabalhar com bancos de dados, você se conectará dessa maneira. I.e. instale um dos pacotes de driver necessários, transmitindo a função dbConnect como o valor do argumento drv , a função de driver para conectar-se ao DBMS necessário.


Um exemplo de conexão com MySQL, PostgreSQL, SQLite e BigQuery
 #   MySQL library(RMySQL) con <- dbConnect(MySQL(), host = 'localhost', user = 'my_username', password = 'my_password', dbname = "mybase", host = "localhost") #   PostrgeSQL library(RPostgreSQL) con <- dbConnect(PostgreSQL(), host = 'localhost', user = 'my_username', password = 'my_password', dbname = "mybase", host = "localhost") #   PostrgeSQL library(RPostgreSQL) con <- dbConnect(PostgreSQL(), host = 'localhost', user = 'my_username', password = 'my_password', dbname = "mybase", host = "localhost") #   SQLite library(RSQLite) # connection or create base con <- dbConnect(drv = SQLite(), "localhost.db") #   Google BigQuery library(bigrquery) con <- dbConnect(drv = bigquery(), project = "my_proj_id", dataset = "dataset_name")

Como ocultar senhas de banco de dados em scripts R


Acima, dei alguns exemplos que podem ser usados ​​para conectar-se a qualquer banco de dados, mas há uma desvantagem: neste formulário, todos os acessos ao banco de dados, incluindo senhas, são armazenados como texto nos próprios scripts.


Se todos os seus scripts forem implantados e executados exclusivamente localmente no seu PC e estiverem protegidos por senha ao mesmo tempo, provavelmente não haverá problema. Mas se você trabalha com alguém no mesmo servidor, o armazenamento de senhas dos bancos de dados no texto de seus scripts não é a melhor solução.


Todo sistema operacional possui um utilitário para gerenciar credenciais. Por exemplo, no Windows, este é o Credential Manager. Você pode adicionar a senha usada para conectar-se ao banco de dados por meio do pacote de keyring neste repositório. O pacote é multiplataforma e o exemplo acima funcionará em qualquer sistema operacional, pelo menos no Windows, MacOS e Linux.


 # install.packages("keyring") #   library(keyring) library(RMSSQL) #   key_set_with_value(service = "mssql", username = "my_username", password = "my_password") #   RMSSQL con <- dbConnect(MSSQLServer(), host = 'localhost', user = 'my_username', password = key_get("mssql", "my_username"), dbname = "mybase")

I.e. usando a função key_set_with_value() , você adiciona uma senha ao armazenamento de credenciais e, usando key_get() solicita-a, e somente o usuário que a adicionou à loja pode solicitar uma senha. Usando o keyring você pode armazenar senhas não apenas dos bancos de dados, mas também de quaisquer serviços, além de tokens de autorização ao trabalhar com a API.


Criando tabelas e gravando no banco de dados


A dbWriteTable() no banco de dados é realizada pela função dbWriteTable() .


Argumentos para a função dbWriteTable() :


Os argumentos necessários estão em negrito, o itálico é opcional


  • conn - objeto de conexão DBMS criado usando a função dbConnect ;
  • nome - o nome da tabela no DBMS na qual os dados serão gravados;
  • valor - uma tabela (um objeto da classe data.frame / data.table / tibble_frame) em R, cujos dados serão gravados no DBMS;
  • row.names - Adiciona uma coluna row_names com números de linhas à tabela, o padrão é FALSE .
  • substituir - Substitua a tabela se a tabela com o nome especificado no argumento name já estiver presente no DBMS, o valor padrão é FALSE ;
  • append - Anexa dados se uma tabela com o nome especificado no argumento name já estiver presente no DBMS, o valor padrão é FALSE ;
  • field.types - Aceita um vetor nomeado e define o tipo de dados em cada campo ao gravar no DBMS, o padrão é NULL ;
  • temporary - Permite criar tabelas temporárias no DBMS, que estarão disponíveis até a conexão com o banco de dados ser desconectada; o valor padrão é FALSE .

Um exemplo de gravação de dados em um DBMS através do DBI
 #   library(odbc) #     DSN con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") #     iris,         R  iris dbWriteTable(conn = con, name = "iris", value = iris) #     dbDisconnect(con)

Para visualizar tabelas no banco de dados, use a função dbListTables() para excluir as tabelas dbRemoveTable()


Um exemplo de consulta a uma lista de tabelas e exclusão de uma tabela de um DBMS
 #   library(odbc) #    con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") #    dbListTables(con) #   iris dbRemoveTable(con, "iris") #     dbDisconnect(con)

Lendo dados de um DBMS


Usando DBI você pode consultar tabelas inteiras ou o resultado da execução de sua consulta SQL. As dbReadTable() e dbGetQuery() são usadas para executar essas operações.


Um exemplo de consulta de uma tabela de íris de um DBMS
 #   library(odbc) #    con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") #   iris   iris dbiris <- dbReadTable(con, "iris") #     dbDisconnect(con)

Um exemplo de carregamento do resultado da execução SQL de um DBMS
 #   library(odbc) #    con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") #     setosa <- dbGetQuery(con, "SELECT * FROM iris WHERE Species = 'setosa'") #     dbDisconnect(con)

Manipulação de dados no DBMS (DML)


A função dbGetQuery() acima é usada exclusivamente para consultar amostras de dados (SELECT).


Para operações de manipulação de dados, como UPDATE, INSERT, DELETE, a função dbExecute() existe no DBI .


Código de amostra para manipulação de dados em um DBMS
 #   library(odbc) #    con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") #   (INSERT) dbExecute(con, "INSERT INTO iris (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species]) VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')") #   (UPDATE) dbExecute(con, "UPDATE iris SET [Species] = 'old_value' WHERE row_names = 51") #     (DELETE) dbExecute(con, "DELETE FROM iris WHERE row_names = 51") #     dbDisconnect(con)

Transações no DBMS


Uma transação é uma operação sequencial de leitura e gravação. O final de uma transação pode ser salvar alterações (confirmar, confirmar) ou cancelar alterações (reversão, reversão). Em relação ao banco de dados, uma transação é uma série de consultas, que são tratadas como uma única consulta.

Cite no artigo "Transações e os mecanismos de seu controle"


Uma transação encapsula várias instruções SQL em uma unidade elementar. No DBI início de uma transação é iniciado com dbBegin() e, em seguida, confirmado com dbCommit() ou cancelado com dbRollback() . Em qualquer caso, o DBMS garante que: todas ou nenhuma das instruções será aplicada aos dados.


Por exemplo, durante a transação, vamos adicionar 51 linhas à tabela iris, depois alterar o valor Sepal.Width para 5 linhas e excluir 43 linhas da tabela.


Exemplo de código de transação
 #   library(odbc) #    con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") #      dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)") # row_names Sepal.Length Sepal.Width Petal.Length Petal.Width Species # 1 5 5.0 3.6 1.4 0.2 setosa # 2 43 4.4 3.2 1.3 0.2 setosa # 3 51 7.0 3.2 4.7 1.4 versicolor #    dbBegin(con) #   dbExecute(con, "INSERT INTO iris (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species]) VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')") #   dbExecute(con, "UPDATE iris SET [Sepal.Width] = 8 WHERE row_names = 5") #   43 dbExecute(con, "DELETE FROM iris WHERE row_names = 43") #   dbCommit(con) #      dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)") # row_names Sepal.Length Sepal.Width Petal.Length Petal.Width Species # 1 5 5 8.0 1.4 0.2 setosa # 2 51 7 3.2 4.7 1.4 versicolor # 3 51 5 3.3 1.7 0.3 new_values

Exemplo de código de cancelamento de transação
 #   library(odbc) #    con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") #      dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)") # row_names Sepal.Length Sepal.Width Petal.Length Petal.Width Species # 1 5 5.0 3.6 1.4 0.2 setosa # 2 43 4.4 3.2 1.3 0.2 setosa # 3 51 7.0 3.2 4.7 1.4 versicolor #    dbBegin(con) #   dbExecute(con, "INSERT INTO iris (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species]) VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')") #   dbExecute(con, "UPDATE iris SET [Sepal.Width] = 8 WHERE row_names = 5") #   43 dbExecute(con, "DELETE FROM iris WHERE row_names = 43") #   dbRollback(con) #      dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)") # row_names Sepal.Length Sepal.Width Petal.Length Petal.Width Species # 1 5 5.0 3.6 1.4 0.2 setosa # 2 43 4.4 3.2 1.3 0.2 setosa # 3 51 7.0 3.2 4.7 1.4 versicolor

Pacote RODBC


O pacote RODBC fornece uma interface independente para conectar e trabalhar com um DBMS por meio de uma interface ODBC.


RODBC não RODBC compatível com DBI , ou seja, você não pode usar o objeto de conexão criado usando o RODBC nas funções fornecidas pelo pacote DBI .


Principais recursos do pacote RODBC


  • odbcConnect - Conexão ao DBMS via DSN;
  • odbcDriverConnect - Conexão ao banco de dados através da cadeia de conexão;
  • sqlQuery - Enviando uma consulta ao DBMS e obtendo o resultado de sua execução. Suporta consultas de qualquer tipo: SELECT, UPDATE, INSERT, DELETE.
  • sqlFetch - Retorna a tabela inteira do DBMS;
  • sqlTables - Obtenha uma lista de tabelas no banco de dados.
  • sqlSave - Crie uma nova tabela no banco de dados ou adicione novos dados a uma tabela existente;
  • sqlUpdate - Atualizando dados em uma tabela que já existe no DBMS;
  • sqlDrop - Exclui uma tabela em um DBMS;
  • odbcClose - odbcClose uma conexão com um DBMS.

Um exemplo de trabalho com o RODBC


Do meu ponto de vista, o RODBC menos funcional que o DBI , mas possui todas as funções necessárias para trabalhar com um DBMS.


Um exemplo de interação com um DBMS através do RODBC
 #   library(RODBC) #   con_string <- odbcDriverConnect(connection = "Driver=SQL Server;Server=localhost;Database=mybase;UID=my_username;PWD=my_password;Port=1433") #   DSN con_dsn <- odbcConnect(dsn = "my_test_source", uid = "my_username", pwd = "my_password") #     sqlSave(con_dsn, dat = iris, tablename = "iris") #     iris sqlSave(con_dsn, dat = iris, tablename = "iris", append = TRUE) #   4  sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) #      R iris[1, 5] <- "virginica" #     sqlUpdate(con_dsn, dat = iris, tablename = "iris") #   4     sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) #   sqlDrop(con_dsn, sqtable = "iris") #     odbcCloseAll()

Transacional


Por padrão, a transacionalidade no RODBC desativada. O gerenciamento de transações é realizado por duas funções.


  • odbcSetAutoCommit - Alterna entre os modos de operação DBMS normal e transacional;
  • odbcEndTran - Confirme ou cancele uma transação.

A odbcSetAutoCommit e desativação do modo de transação é feita pela função odbcSetAutoCommit usando o argumento autoCommit .


Exemplo de trabalho no modo transacional no RODBC
 #   library(RODBC) #   DSN con_dsn <- odbcConnect(dsn = "my_test_source", uid = "my_username", pwd = "my_password") #     sqlSave(con_dsn, dat = iris, tablename = "iris") #    odbcSetAutoCommit(con_dsn, autoCommit = FALSE) #   4  sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) # rownames SepalLength SepalWidth PetalLength PetalWidth Species # 1 1 5.1 3.5 1.4 0.2 setosa # 2 2 4.9 3.0 1.4 0.2 setosa # 3 3 4.7 3.2 1.3 0.2 setosa # 4 4 4.6 3.1 1.5 0.2 setosa #      R iris[1, 5] <- "virginica" #     sqlUpdate(con_dsn, dat = iris, tablename = "iris") #   4  sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) # rownames SepalLength SepalWidth PetalLength PetalWidth Species # 1 1 5.1 3.5 1.4 0.2 virginica # 2 2 4.9 3.0 1.4 0.2 setosa # 3 3 4.7 3.2 1.3 0.2 setosa # 4 4 4.6 3.1 1.5 0.2 setosa #   odbcEndTran(con_dsn, commit = FALSE) #   4  sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) # rownames SepalLength SepalWidth PetalLength PetalWidth Species # 1 1 5.1 3.5 1.4 0.2 setosa # 2 2 4.9 3.0 1.4 0.2 setosa # 3 3 4.7 3.2 1.3 0.2 setosa # 4 4 4.6 3.1 1.5 0.2 setosa #     sqlUpdate(con_dsn, dat = iris, tablename = "iris") #   odbcEndTran(con_dsn, commit = TRUE) #   4     sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) # rownames SepalLength SepalWidth PetalLength PetalWidth Species # 1 1 5.1 3.5 1.4 0.2 virginica # 2 2 4.9 3.0 1.4 0.2 setosa # 3 3 4.7 3.2 1.3 0.2 setosa # 4 4 4.6 3.1 1.5 0.2 setosa #     odbcClose(con_dsn)

Conclusão


Os dois métodos de trabalhar com bancos de dados nas linguagens R, DBI e RODBC descritos no artigo são bastante universais e funcionarão com quase todos os DBMS.


A única diferença na operação entre diferentes DBMSs é o processo de conexão. Para os DBMSs mais populares, existem pacotes separados que são drivers. Para o restante do DBMS, é necessário configurar a conexão através da interface ODBC usando RODBC odbc ou RODBC . Todas as outras manipulações, independentemente do DBMS que você escolheu, permanecerão inalteradas. Uma exceção é o envio de consultas SQL, dependendo do dialeto SQL suportado pelo DBMS com o qual você está trabalhando.

Source: https://habr.com/ru/post/pt461063/

More articles:


All Articles