Configuración dunha conexión ODBC en Linux

Segundo recolle a wikipedia, o obxectivo de ODBC é facer posible o acceso a calquera dato dende culquera aplicación, sen importar qué sistema de xestión de bases de datos (DBMS) almacene os datos. Así o software funciona de dous xeitos, cun software no cliente, ou unha filosofía cliente-servidor. No primeiro modo, o controlador interpreta as conexións e chamadas SQL e tradúceas dende a API ODBC cara a DBMS. No segundo modo, para conectarse á base de datos, xérase unha DSN dentro do ODBC que define os parámetros, ruta e características da conexión según os datos que solicite o creador ou fabricante. DSN é unha palabra abreviada do inglés (Data Source Name) (Nome Fonte de datos ou Nome de orixe de datos), que representa todo o relativo a unha fonte de datos configurada polo usuario para conectarse a unha base de datos. En resumo e simplificando moito, é un conxunto de arquivos que nos permiten a conexión á unha base de datos dende o noso sistema modificando unha serie de parámetros dependendo da base de datos á que un se queira conectar.

A configuración dunha conexión ODBC en Linux consta de fundamentalmente de tres partes:

1. A instalación e configuración dos controladores que permiten a conexión coas bases de datos
2. A conexión ás bases de datos coas que se desexa traballar
3. Conexión a unha base de datos con RStudio empregando o DSN xerado

A instalación e configuración dos controladores que permiten a conexión coas bases de datos
Instalamos o sistema unixODBC
Para levar a cabo esta parte é necesario ter en conta a necesidade de instalar controladores privativos ou non dependendo do motor da base de datos ao que un desexe conectarse. Por norma xeral, as distribucións Linux inclúen dentro dos seus repositorios as ferramentas necesarias para conectarse as bases de datos como SQLite, PostgreSQL, MySQL ou MariaDB. Para isto é necesario instalar os paquetes correspondentes, en primeiro lugar unixODBC:

openSUSE:

zypper in unixODBC

Debian:

apt-get install unixODBC

Isto instala unha serie de aruqivos que nos permiten definir a conexión, especialmente odbc.ini e odbcinst.ini. Nun sistema Debian atoparánse dentro da carpeta /etc ou, se é un sistema openSUSE, en /etc/unixODBC. O contido destes arquivos é:

• odbcinst: Contén a dirección, baixo un nome, dos controladores que se gardan detro de /usr
• odbc.ini: Arquivo que se pode xerar dentro do directorio do usuario como .odbc.ini, ou modificar dentro de /etc. Isto dependerá de si se quere unha mesma conexión para todo o sistema e todos os usuarios do mesmo, ou só para un usuario. Contén a dirección DSN á base de datos. Esta debe conter como controlador un dos que figura dentro de odbcinst. Unha vez declarado o controlador DSN, só é necsareio chamalo con nome que se puxo para a conexión á base de datos en cuestión

Unha vez instalado poderase comprobar os arquivos de configuración da conexión executando:

odbcinst -j

Deste comando obtense unha saída que nos permite comprobar a versión do arquivo ODBC que se acaba de instalar, así como as rutas absolutas dos arquivos de configuración e empregar. Por exemplo, nun caso concreto a saída é:

unixODBC 2.3.9
DRIVERS............: /etc/unixODBC/odbcinst.ini
SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini
FILE DATA SOURCES..: /etc/unixODBC/ODBCDataSources
USER DATA SOURCES..: /home/usuario/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Instalación de diferentes controladores para bases de datos
Dentro dun sistema openSUSE, os controladores para as bases de datos:

zypper in sqliteodbc
zypper in psqlODBC
zypper in mariadb-connector-odbc

Dentro dun sistema Debian:

apt-get install libsqliteodbc
apt-get install odbc-postgresql
apt-get install odbc-mariadb

Para instalar controladores para motores de bases de datos privativos, como son o caso de Microsoft SQL Server e Snowflake, hai que descargar os controladores das páxinas correspondentes e levar a cabo a instalación. Unha alternativa a isto e instalar o paquete FreeTDS que permite a conexión a bases de datos privativas. Para a súa instalación:

openSUSE

zypper in freetds-config freetds-tools

Debian

apt-get install freetds-bin freetds-common

Unha vez instalado, edétase o arquivo /etc/freetds.conf e engadese unha conexión a unha base de datos. Por exemplo:

[MSSQL_FREETDS]
host = ip_ou_nome_da_maquina
port = 1433
tds version = auto

No caso do controlador da base de datos Snowflake, é posible que nos dé un erro porque non atopa unha libreŕia en concreto. Para solucionar este problema só hai que apuntar o arquivo á librería correcta. Editase o arquivo /usr/lib/snowflake/odbc/lib/simba.snowflake.ini e compróbase que esté apuntando ao odbc correcto. No caso de que o erro sexa que /usr/lib/x86_64-linux-gnu/libodbcinst.so.1 non existe, hai que modificar este arquivo e que apunte á librería que si se instalou no sistema co controlador, por exemplo á librería número 2. Modficamos o arquivo deixando a liña como /usr/lib/x86_64-linux-gnu/libodbcinst.so.2

Agora compróbase que no arquivo odbcinst, dentro do directorio etc, se xeraran as cadeas que se conectan aos controladores das bases de datos e apuntamos o nome que teñen asignado. Por exemplo:

[SQLITE3]
Description=SQLite ODBC 3.X
Driver=/usr/lib64/libsqlite3odbc.so
Setup=/usr/lib64/libsqlite3odbc.so
Threading=2
FileUsage=1
UsageCount=1

[PSQL]
Description=PostgreSQL
Driver64=/usr/lib64/psqlodbcw.so
UsageCount=1

[FREETDS]
Description=FreeTDS unixODBC Driver
Driver=/usr/lib64/libtdsodbc.so.0
Setup=/usr/lib64/libtdsodbc.so.0
UsageCount=1

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1
UsageCount=1

[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so
DriverODBCVer=03.52
SQLLevel=1
UsageCount=1

A conexión ás bases de datos coas que se desexa traballar
A continuación defínese a conexión DSN para a base de datos. Este arquivo ten varias peculiriedades dependendo da base de datos á que se queira conectar, pero deber posuir entre corchetes ([]) o nome da conexión DSN que se vai empregar, unha descripción e un «Driver» que é o nome ue ten o controlador entre corchetes dentro do arquivo odbcinst.ini. Algúns exemplos:

[SQLITE3]
Description=SQLite3 ODBC Connection
Driver=SQLITE3
Database=/home/usuario/dbsample.db
Timeout=2000

[POSTGRESQL]
Description=PostgreSQL ODBC Connection
Driver=PSQL
Username=nome_usuario_db_postgresql
Password=clave_usuario_db_postgresql
Servername=ip_db_postgresql
Database=nome_base_datos_db_postgresql
Port=porto_db_postgresql (polo xeral 5432)

[MSSQL ODBC FREETDS]
Desctiption = ODBC FreeTDS
Driver = FREETDS
Servername = MSSQL_FREETDS

[MSSQL ODBC MICROSOFT]
Driver=ODBC Driver 17 for SQL Server
Server=ip_db_mssql
Port=porto_db_mssql (polo xeral 1433)
Database=nome_base_datos_db_mssql
UID=nome_usuario_db_mssql
PWD=clave_usuario_db_mssql
Encrypt=No
TrustServerCertificate=Yes
ServerForceEncryption=No
KeepAlive=30
KeepAliveInternal=1

[SNOWFLAKE]
Driver=SnowflakeDSIIDriver
Port=porto_db_snowflake (polo xeral 443)
SSL=ON
Server=direccion_db_snowflake.com
Warehouse=warehouse_db_snowflake
Database=nome_base_datos_db_snowflake

Unha vez rematado comprobase que as conexións están correctamente configuradas empregando o comando isql:

isql SQLITE3
isql POSTGRESQL
isql "MSSQL ODBC FREETDS" nome_usuario_db_mssql clave_usuario_db_mssql
isql "MSSQL ODBC MICROSOFT" nome_usuario_db_mssql clave_usuario_db_mssql
isql SNOWFLAKE

Conexión a unha base de datos con RStudio empregando o DSN xerado
Para rematar, para unha conexión mediante o DSN xerado mediante RStudio:

library(DBI)
conexion_sqlite3 <- dbConnect(drv = odbc::odbc(), dsn = "SQLITE3", timeout = 10)
conexion_postgresql <- dbConnect(drv = odbc::odbc(), dsn = "POSTGRESQL", timeout = 10
conexion_freetds <- dbConnect(drv = odbc::odbc(), dsn = "MSSQL ODBC FREETDS", timeout = 10, UID = nome_usuario_db_mssql, PWD = clave_usuario_db_mssql)
conexion_microsoft <- dbConnect(drv = odbc::odbc(), dsn = "MSSQL ODBC MICROSOFT", timeout = 10, UID = nome_usuario_db_mssql, PWD = clave_usuario_db_mssql)
conexion_snowflake <- dbConnect(drv = odbc::odbc(), dsn = "SNOWFLAKE", timeout = 10)

No caso do DSN xerado para Microsoft SQL Server, hai que incluír o nome do usuario e a clave xerada porque non o recoñece correctamente dende o DSN. Dita clave pode ser introducida directamente ou configurando outros sistemas tal e como se indica na páxina de RStudio (https://solutions.posit.co/connections/db/best-practices/managing-credentials/)

FONTES:

https://es.wikipedia.org/wiki/Open_Database_Connectivity
https://es.wikipedia.org/wiki/Data_Source_Name