Geekly articles weekly

Interacción de R con bases de datos en el ejemplo de Microsoft SQL Server y otros DBMS

Dado que la mayor parte de la información comercial se almacena en bases de datos. En cualquier lenguaje de programación que escriba, debe realizar varias acciones con ellos.


En este artículo hablaré sobre dos interfaces para trabajar con bases de datos en R. La mayoría de los ejemplos demuestran trabajar con Microsoft SQL Server, sin embargo, todos los ejemplos de código funcionarán con otras bases de datos, tales como: MySQL, PostgreSQL, SQLite, ClickHouse , Google BigQuery, etc.


imagen


Contenido



Software requerido


Para repetir todos los ejemplos de trabajo con el DBMS descritos en el artículo, necesitará el siguiente software gratuito que se detalla a continuación:


  1. Lenguaje R ;
  2. Entorno de desarrollo RStudio ;
  3. Sistema de gestión de bases de datos, para elegir:
    3.1. Microsoft SQL Server
    3.2. MySQL
    3.3. PostgreSQL

Paquete DBI


El paquete DBI es la forma más popular y conveniente de interactuar con bases de datos en R.


DBI proporciona un conjunto de funciones con las que puede administrar bases de datos. Pero para conectarse a las bases de datos, debe instalar paquetes adicionales que sean controladores para varios sistemas de administración de bases de datos (DBMS).


Lista de funciones básicas de DBI


  • dbConnect - conexión a la base de datos;
  • dbWriteTable : escribe una tabla en la base de datos;
  • dbReadTable : carga una tabla desde una base de datos;
  • dbGetQuery : carga el resultado de la ejecución de la consulta;
  • dbSendQuery - enviando una consulta a la base de datos;
  • dbFetch : extrae elementos de un conjunto de resultados;
  • dbExecute : ejecución de solicitudes para actualizar / eliminar / insertar datos en tablas;
  • dbGetInfo : solicita información sobre el resultado de la solicitud o conexión;
  • dbListFields : solicitud de una lista de campos de tabla;
  • dbListTables : consulta una lista de tablas de base de datos;
  • dbExistsTable : verifica la presencia de una tabla en la base de datos;
  • dbRemoveTable : elimina una tabla de la base de datos;
  • dbDisconnect : desconectarse de la base de datos.

Conectividad de base de datos


Para interactuar con las bases de datos, primero debe conectarse a ellas. Dependiendo del DBMS con el que planea trabajar, necesitará un paquete adicional, a continuación encontrará una lista de los más utilizados.


  • odbc : controlador para conectarse a través de la interfaz ODBC;
  • RSQLite - Controlador para SQLite;
  • RMySQL / RMariaDB - Controlador para MySQL y MariaDB;
  • RPostgreSQL - Controlador para PosrtgreSQL;
  • bigrquery - Controlador para Google BigQuery;
  • RClickhouse / clickhouse - Controlador para ClickHouse;
  • RMSSQL : el controlador para Microsoft SQL Server (MS SQL), en el momento de la escritura, solo está presente en GitHub .

El paquete DBI viene con el paquete básico R, pero los paquetes que son controladores de base de datos deben instalarse utilizando el comando install.packages(" ") .


Para instalar paquetes desde GitHub, también necesitará un paquete adicional: devtools . Por ejemplo, el paquete RMSSQL no está publicado actualmente en el repositorio principal del paquete R, use el siguiente código para instalarlo:


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

Un ejemplo de conexión a Microsoft SQL Server usando el paquete odbc


Antes de usar cualquier paquete en una sesión R, primero debe conectarse usando la función de library(" ") .


No sin razón elegí Microsoft SQL Server como el DBMS principal, que proporcionará la mayoría de los ejemplos en este artículo. El hecho es que esta es una base de datos bastante popular, pero al mismo tiempo todavía no tiene un controlador para conectarse desde R publicado en CRAN.


Pero afortunadamente, SQL Server, como casi cualquier otra base de datos, tiene una interfaz ODBC (English Open Database Connectivity) para conectarse. Hay varios paquetes para conectarse al DBMS a través de la interfaz ODBC en R. Primero, veremos la conexión a través del paquete odbc .


Fácil conexión a la base de datos a través de la interfaz 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)

En la función dbConnect() , debe pasar la función, que es el controlador para conectarse al DBMS ( odbc() ), como primer argumento para drv . Dichas funciones generalmente se llaman igual que el DBMS y vienen con paquetes que son controladores para DBI .


A continuación, debe enumerar los parámetros de conexión. Para conectarse a MS SQL a través de ODBC, debe especificar los siguientes parámetros:


  • Controlador: nombre del controlador ODBC;
  • Servidor: dirección IP del servidor SQL;
  • Base de datos: el nombre de la base de datos para conectarse;
  • UID: nombre de usuario de la base de datos;
  • PWD - Contraseña;
  • Puerto: el puerto con el que conectarse, SQL Server tiene un puerto predeterminado de 1433.

Un controlador ODBC para conectarse a Microsoft SQL Server se incluye con Windows, pero puede tener un nombre diferente. Puede ver la lista de controladores instalados en el Administrador de fuente de datos ODBC. Puede iniciar el administrador de la fuente de datos en Windows 10 de la siguiente manera:


  • Versión de 32 bits: %systemdrive%\Windows\SysWoW64\Odbcad32.exe
  • Versión de 64 bits: %systemdrive%\Windows\System32\Odbcad32.exe


También puede obtener una lista de todos los controladores instalados en su PC utilizando la función 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

Puede descargar los controladores ODBC para otros DBMS en los siguientes enlaces:



Para varios DBMS, el nombre de los parámetros para la conexión puede ser diferente, por ejemplo:


  • PostgreSQL / MySQL / MariaDB: usuario, contraseña, host, puerto, dbname;
  • GoogleBigQuery - proyecto, conjunto de datos;
  • ClickHouse: usuario, contraseña, base de datos, puerto, host;

Con el administrador de la fuente de datos ODBC, puede ejecutar el asistente para crear una fuente de datos ODBC. Para hacer esto, simplemente abra el administrador, vaya a la pestaña "DSN personalizado" y haga clic en el botón "Agregar ...".



Al crear un origen de datos con un administrador, debe asignarle un nombre, DSN (Nombre del origen de datos).



En el ejemplo anterior, creamos una fuente de datos con el DSN "my_test_source". Ahora podemos usar esta fuente para conectarnos a Microsoft SQL Server y no especificar otros parámetros de conexión en el código.


Conexión a la base de datos a través de la interfaz odbc usando DSN
 #   DSN con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password")

Puede ver los nombres de todas las fuentes de datos ODBC creadas en su PC utilizando la función 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

Un ejemplo de conexión a Microsoft SQL Server usando el paquete RMSSQL


RMSSQL no RMSSQL publicado en CRAN, por lo que puede instalarlo desde GitHub utilizando el paquete devtools .


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

Ejemplo de conexión con el controlador DBI RMSSQL
 #    library(RJDBC) library(RMSSQL) library(DBI) #  RMSSQL con <- dbConnect(MSSQLServer(), host = 'localhost', user = 'my_username', password = 'my_password', dbname = "mybase")

En la mayoría de los casos, utilizando el paquete DBI para trabajar con bases de datos, se conectará de esta manera. Es decir instale uno de los paquetes de controladores necesarios, pasando la función dbConnect como el valor del argumento drv , la función del controlador para conectarse al DBMS que necesita.


Un ejemplo de conexión a MySQL, PostgreSQL, SQLite y 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")

Cómo ocultar contraseñas de bases de datos en scripts R


Arriba, di algunos ejemplos que pueden usarse para conectarse a cualquier base de datos, pero hay un inconveniente en ellos, de esta forma todos los accesos a la base de datos, incluidas las contraseñas, se almacenan como texto en los propios scripts.


Si todas sus secuencias de comandos se implementan y se ejecutan exclusivamente localmente en su PC, y está protegido con contraseña al mismo tiempo, lo más probable es que no haya ningún problema. Pero si trabaja con alguien en el mismo servidor, almacenar las contraseñas de las bases de datos en el texto de sus scripts no es la mejor solución.


Cada sistema operativo tiene una utilidad para administrar credenciales. Por ejemplo, en Windows, este es el Administrador de credenciales. Puede agregar la contraseña que usa para conectarse a la base de datos a través del paquete de keyring a este repositorio. El paquete es multiplataforma y el ejemplo anterior funcionará en cualquier sistema operativo, al menos en Windows, MacOS y 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")

Es decir usando la función key_set_with_value() , agrega una contraseña al almacenamiento de credenciales, y usando key_get() solicita, y solo el usuario que la agregó a la tienda puede solicitar una contraseña. Con el keyring puede almacenar contraseñas no solo de bases de datos, sino también de cualquier servicio, así como tokens de autorización cuando trabaje con la API.


Crear tablas y escribir en la base de datos


La función dbWriteTable() realiza la escritura en la base de datos.


Argumentos a la función dbWriteTable() :


Los argumentos requeridos están en negrita, las cursivas son opcionales


  • conn : objeto de conexión DBMS creado con la función dbConnect ;
  • nombre : el nombre de la tabla en el DBMS en el que se escribirán los datos;
  • valor : una tabla (un objeto de la clase data.frame / data.table / tibble_frame) en R, cuyos datos se escribirán en el DBMS;
  • row.names : agrega una columna row_names con números de fila a la tabla, por defecto es FALSE .
  • sobrescribir : sobrescriba la tabla si la tabla con el nombre especificado en el argumento de nombre ya está presente en el DBMS; el valor predeterminado es FALSO ;
  • append : agrega datos si una tabla con el nombre especificado en el argumento de nombre ya está presente en el DBMS; el valor predeterminado es FALSE ;
  • field.types : acepta un vector con nombre y establece el tipo de datos en cada campo al escribir en el DBMS, por defecto es NULL ;
  • temporal : le permite crear tablas temporales en el DBMS, que estarán disponibles hasta que se desconecte la conexión con la base de datos; el valor predeterminado es FALSO .

Un ejemplo de escritura de datos en un DBMS a través de 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 ver tablas en la base de datos, use la función dbListTables() , para eliminar tablas dbRemoveTable()


Un ejemplo de consulta de una lista de tablas y eliminación de una tabla de un DBMS
 #   library(odbc) #    con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") #    dbListTables(con) #   iris dbRemoveTable(con, "iris") #     dbDisconnect(con)

Leer datos de un DBMS


Con DBI puede consultar tablas completas o el resultado de ejecutar su consulta SQL. Las dbReadTable() y dbGetQuery() se utilizan para realizar estas operaciones.


Ejemplo de consulta de una tabla de iris desde un DBMS
 #   library(odbc) #    con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") #   iris   iris dbiris <- dbReadTable(con, "iris") #     dbDisconnect(con)

Un ejemplo de cargar el resultado de ejecutar SQL desde un 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)

Manipulación de datos en DBMS (DML)


La función dbGetQuery() anteriormente se usa exclusivamente para consultar muestras de datos (SELECT).


Para las operaciones de manipulación de datos, como UPDATE, INSERT, DELETE, la función dbExecute() existe en DBI .


Código de muestra para manipular datos en un 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)

Transacciones en el DBMS


Una transacción es una operación de lectura y escritura secuencial. El final de una transacción puede ser guardar cambios (commit, commit) o ​​cancelar cambios (rollback, rollback). En relación con la base de datos, una transacción es una serie de consultas, que se tratan como una sola consulta.

Cita del artículo "Transacciones y los mecanismos de su control"


Una transacción encapsula varias declaraciones SQL en una unidad elemental. En DBI inicio de una transacción se inicia con dbBegin() y luego se confirma con dbCommit() o se cancela con dbRollback() . En cualquier caso, el DBMS garantiza que: todas o ninguna de las declaraciones se aplicarán a los datos.


Por ejemplo, durante la transacción, agreguemos 51 filas a la tabla de iris, luego cambie el valor de Sepal.Width a 5 líneas y eliminemos 43 líneas de la tabla.


Ejemplo de código de transacción
 #   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

Ejemplo de código de cancelación de transacción
 #   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

Paquete RODBC


El paquete RODBC proporciona una interfaz independiente para conectarse y trabajar con un DBMS a través de una interfaz ODBC.


RODBC no RODBC compatible con DBI , es decir no puede usar el objeto de conexión creado usando RODBC en las funciones proporcionadas por el paquete DBI .


Características clave del paquete RODBC


  • odbcConnect - Conexión al DBMS a través de DSN;
  • odbcDriverConnect - Conexión a la base de datos a través de la cadena de conexión;
  • sqlQuery : envía una consulta al DBMS y obtiene el resultado de su ejecución. Admite consultas de cualquier tipo: SELECCIONAR, ACTUALIZAR, INSERTAR, ELIMINAR.
  • sqlFetch - Obtiene la tabla completa del DBMS;
  • sqlTables : sqlTables una lista de tablas en la base de datos.
  • sqlSave : sqlSave una nueva tabla en la base de datos o agrega nuevos datos a una tabla existente;
  • sqlUpdate : actualización de datos en una tabla que ya existe en el DBMS;
  • sqlDrop - Eliminar una tabla en un DBMS;
  • odbcClose - Finaliza una conexión a un DBMS.

Un ejemplo de trabajo con RODBC


Desde mi punto de vista, RODBC menos funcional que DBI , pero tiene todas las funciones necesarias para trabajar con un DBMS.


Un ejemplo de interacción con un DBMS a través de 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()

Transaccional


Por defecto, la transaccionalidad en RODBC desactivada. La gestión de transacciones se lleva a cabo mediante dos funciones.


  • odbcSetAutoCommit - Cambia entre los modos de operación DBMS normal y transaccional;
  • odbcEndTran - Confirma o cancela una transacción.

odbcSetAutoCommit función odbcSetAutoCommit y desactiva el modo de transacción mediante el argumento autoCommit .


Ejemplo de trabajo en modo transaccional en 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)

Conclusión


Los dos métodos para trabajar con bases de datos en los idiomas R, DBI y RODBC descritos en el artículo son bastante universales y funcionarán con casi cualquier DBMS.


La única diferencia en la operación entre diferentes DBMS es el proceso de conexión. Para los DBMS más populares, hay paquetes separados que son controladores. Para el resto del DBMS, es necesario configurar la conexión a través de la interfaz ODBC utilizando RODBC odbc o RODBC . Todas las demás manipulaciones, independientemente del DBMS que haya elegido, no se modificarán. Una excepción es enviar consultas SQL, dependiendo del dialecto SQL admitido por el DBMS con el que está trabajando.

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

More articles:


All Articles