Para realizar una conexion ODBC, antes de realizar el proceso en RStudio es necesario primero tener la base de datos y segundo crear un DSN de Usuario en nuestro computador, esto se realiza en el menú de “Origenes de Datos ODBC”, para esta conexion es necesario tener instalados los controladores del Gestor de Base de Datos a utlizar, en el caso de este ejemplo MySQL.
Para tener instalados los contraladores del Gestor de Base de Datos, es suficiente con instalar este como tal, por ejemplo MySQL, Microsoft Access, SQL Server, entre otros. Luego de esto buscamos en el inicio de Windows “Origenes de Datos ODBC”, al abrir esta ventana, en la pestaña controladores verificamos que estan instalados, volvemos a la pestaña “DSN de Usuario” y le damos clic en la opcion “Agregar…”, nos abrirá una nueva ventana en la cual seleccionaremos “MySQL ODBC 8.0 ANSI Driver” y daremos clic en finalizar. Se abrirá una ventana en la cual deberemos llenar lo siguiente:
Se le da clic a Test, y al ser la conexion exitosa se puede cerrar la ventana de “Origenes de Datos ODBC”.
Teniendo el paso anterior listo es necesario, en RStudio, en la consola o en un R Markdown, el primer paso es instalar los paquetes necesarios: odbc, DBI, RSQLite y sqldf, a traves del comando install.packages. Luego deben cargarse a RStudio, a traves del comando library.
## Loading required package: gsubfn## Loading required package: protoPara realizar la conexion existen dos formas, la primera en el lado derecho de la pantalla en RStudio, en las pestaña Connections existe un menú que dice New Connection, al seleccionarlo aparecera una pantalla emergente y se escoge el nombre del DSN que creamos para la conexion, se prueba con Test, y se finaliza con OK, teniendo la conexion creada. La otra forma de crear la conexion es hacerlo directamente con la funcion dbConnect, donde primero se define el nombre de la conexion y se le asigna a la funcion, donde en el argumento se agrega el tipo de conexion, en este caso ODBC, y entre comillas el nombre del DSN creado. De la siguiente forma:
#Crear Conexion
conexion<-dbConnect(odbc(),"conexion")Algunas de las funciones a utlizar en la base de datos sera mostrar las tablas y el contenido de estas, a traves de las funciones dbListTables y dbListFields, las cuales en el argumento se llenan con el nombre de la conexion definida en RStudio, y en el caso de la segunda se le agrega la tabla que interesa entre comillas, a continuacion se muestra como se utilizan:
#Mostrar tablas BD
dbListTables(conexion)## [1] "city" "country" "countrylanguage"#Mostrar atributos de las tablas
dbListFields(conexion,"country")## [1] "Code" "Name" "Continent" "Region"
## [5] "SurfaceArea" "IndepYear" "Population" "LifeExpectancy"
## [9] "GNP" "GNPOld" "LocalName" "GovernmentForm"
## [13] "HeadOfState" "Capital" "Code2"Tambien se puede transformar las tablas de base de datos a dataframe en R, mediante el comando dbReadTable, el cual en el argumento va el nombre de la conexion y el nombre de la tabla entre comillas, como en el ejemplo:
#Hacer tabla dataframe
language<-dbReadTable(conexion,"countrylanguage")Para realizar consultas se mostraran 3 formas distintas.
dbGetQueryLos argumentos de esta funcion seleccionan a la conexion ODBC y luego en lenguaje SQL seleciona la consulta a hacer, SELECT selecciona el atriuto a mostrar de la tabla y FROM desde que tabla se selecciona, como se muestra a continuacion:
#Forma 1
consulta1<-dbGetQuery(conexion,"SELECT Name, CountryCode FROM city")dbSendQuery y dbFetchLa segunda forma de hacer consultas incluye dos funciones en conjunto, la primera hace la consulta en si y la segunda muestra los primeros n resultados de la consulta y luego el resto. Los argumentos para la primera funcion es como en el caso anterior la conexion y la consulta en lenguaje SQL, y para la segunda funcion es el nombre que se le asigno a la consulta y la cantidad de registros a mostrar. Se muestra un ejemplo:
#Forma 2
consulta2<-dbSendQuery(conexion,"SELECT Name, CountryCode FROM city")
primeros10<-dbFetch(consulta2,n=10)
demas<-dbFetch(consulta2)sqldfPara poder utlizar esta funcion es necesario definir las tablas de la base de datos como dataframe, y el argumento es la consulta en lenguaje SQL. En los siguientes ejemplos, se agrega WHERE que le da condicion a la consulta y ORDER BY que le aplica el orden que el usuario quiera darle.
#Ejemplo 1
country<-dbReadTable(conexion,"country")## Warning in new_result(connection@ptr, statement): Cancelling previous queryconsulta3<-sqldf("SELECT Name, GovernmentForm, LifeExpectancy FROM country WHERE LifeExpectancy < 50 ORDER BY LifeExpectancy")
#Ejemplo 2
city<-dbReadTable(conexion,"city")
consulta4<-sqldf("SELECT Name, Population FROM city WHERE CountryCode = 'CHL' ORDER BY Name")Estas son consultas que se utilizan para donde existe informacion comun en dos tablas distintas, que tienen distintos atributos, pero tienen algo que las relaciona. Se utiliza sqldf y su argumento es el ya mencionado, teniendo en cuenta que ahora habran dos tablas se define el FROM desde la primera tabla con el nombre de la tabla y una letra que la caracterice, luego se define la segunda tabla con otra letra y se define WHERE como se muestra en el ejemplo:
cruzada<-sqldf("SELECT Language, (ROUND(Population*Percentage/100)) as Hablantes FROM country a, language b WHERE a.Code = b.CountryCode ORDER BY Hablantes")
cruzada2<-sqldf("SELECT Language, SUM(Hablantes) as Speakers FROM cruzada WHERE Hablantes > 100000000 GROUP BY Language ORDER BY Speakers DESC")Finalmente se puede hacer distintas combinaciones en base a los ejemplos mostrados anteriormente.