Как подключить R к базе данных Redshift на Apple M3?

Введение и система

Я пытаюсь получить доступ к базе данных Redshift из R. Я использую компьютер Apple M3 Pro с Sonoma 14.5. У меня версия R 4.4.1. Я собираюсь указать имя базы данных (например, «dev»), URL-адрес хоста, номер порта, мое имя пользователя и пароль; Я ожидаю получить стабильное соединение, по которому я смогу запросить базу данных.

Я попробовал использовать несколько пакетов, описанных ниже.

Попытка 1: RPostgres

{RPostgres} — самый простой. Я использовал этот пост и официальную документацию пакета, чтобы помочь в настройке.

Сначала я установил необходимое системное программное обеспечение, запустив brew install libpq. Я также в другом месте увидел, что brew install postgresql тоже может помочь, поэтому они у меня установлены.

Я установил пакет с помощью install.packages("RPostgres"), а также установил его из исходного кода с помощью remotes::install_github("r-dbi/RPostgres") или install.packages("RPostgres", type = "source"). Затем я побежал:

con <- dbConnect(
  RPostgres::Redshift(),
  dbname = "dev", 
  host = Sys.getenv("RS_URLS"),
  port = 5439,
  user = Sys.getenv("RS_USER"),
  password = Sys.getenv("RS_PASS"),
  sslmode = "require"
)

Где переменные среды — это URL-адрес Redshift, имя пользователя и пароль для моей базы данных. Что бы я ни пробовал, я продолжаю получать ошибку:

Error: connection to server at "<host>" (<ip>), port 5439 failed: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Похоже, это известная и открытая проблема. Неважно, установил я из CRAN или из исходного кода — я получаю одну и ту же ошибку.

Попытка 2: RPostgreSQL

{RPostgreSQL} говорит, что он также будет использовать libpq в пакете Description на CRAN. Используя синтаксис из примера в документации:

drv <- dbDriver("PostgreSQL")
con <- dbConnect(
  drv,
  dbname = "dev",
  host = Sys.getenv("RS_URLS"),
  port = 5439,
  user = Sys.getenv("RS_USER"),
  password = Sys.getenv("RS_PASS")
)

Выдает ошибку:

Error in postgresqlNewConnection(drv, ...) : 
  RPosgreSQL error: could not connect <user>@<host>:5439 on dbname "dev": FATAL:  no pg_hba.conf entry for host "???", user "<user>", database "dev", SSL off

Обратите внимание на SSL: я успешно подключил Redshift к визуализатору базы данных на своем компьютере, и для этого требовалось, чтобы SSL был включен. Кэндиди, я понятия не имею, что такое SSL и что означает, но здесь нет для него опции (хотя она есть в {RPostgres}, но с этим пометкой все равно не удалось).

Я добился некоторого прогресса, обнаружив этот пост. ОП добавлял информацию SSL к имени своей базы данных. Я могу сделать то же самое:

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(
  drv,
  dbname = "'dbname=dev sll=require'",
  host = Sys.getenv("RS_URLS"),
  port = 5439,
  user = Sys.getenv("RS_USER"),
  password = Sys.getenv("RS_PASS")
)

Интересно, что это приводит не к ошибке, а к прерванному сеансу R (много общего с попыткой 4 с использованием RJDBC ниже).

Попытка 3: odbc

Posit (fka RStudio) официально рекомендует использовать ODBC через пакет {odbc}. Имеется исчерпывающая документация:

• Пакет-винетка для настройки
• Настройка драйвера
• Руководство Amazon по загрузке драйвера ODBC Redshift
• Формат подключения к Redshift

Начиная с виньетки, я пробежал brew install unixodbc и brew install psqlodbc. Однако я не думаю, что это тот драйвер, который стоит использовать, поскольку я хочу использовать Redshift. Поэтому я следовал инструкциям в этом руководстве Amazon по загрузке, установке и форматированию файлов .ini для драйвера.

По рекомендации Amazon я взял файл odbcinst.ini из подкаталога Setup установочного файла и скопировал его в свой домашний каталог, добавив к нему ., чтобы он был скрыт. Когда я cat этот файл, он читает:

[ODBC Drivers]
Amazon Redshift=Installed

[Amazon Redshift]
Description=Amazon Redshift ODBC Driver
Driver=/opt/amazon/redshift/lib/libamazonredshiftodbc.dylib

Проблема в том, что когда я запускаю odbcinst -j в терминале — согласно примеру установки — я не вижу правильных путей:

unixODBC 2.3.12
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/<username>/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

А если я cat первые два .ini файла, они пустые. Источник пользовательских данных .ini просто не существует.

Прочитав этот выпуск GitHub, я понял, что мне нужно изменить файл .Renviron, чтобы он искал правильный каталог и получал правильный .ini файл. Я обновил это до: ODBCSYSINI = "/Users/<username>" поскольку Amazon предложил мне переместить .ini в мой домашний каталог (поскольку он перезапишет файл, если кто-то получит новую загрузку или переустановит драйвер).

Итак, теперь, в Р.

Если у меня есть список пакетов {odbc} с драйверами, он читает правильный путь, по которому, как я знаю, существует драйвер:

> odbc::odbcListDrivers()
             name       attribute                                                value
1    ODBC Drivers Amazon Redshift                                            Installed
2 Amazon Redshift     Description                          Amazon Redshift ODBC Driver
3 Amazon Redshift          Driver /opt/amazon/redshift/lib/libamazonredshiftodbc.dylib

Я неоднократно туда ездил и знаю, что водитель там. Поэтому, когда я использую предложенный формат Redshift, я ввожу:

con <- dbConnect(
  odbc(),
  Driver       = "Amazon Redshift",
  servername   = Sys.getenv("RS_URLS"),
  database     = "dev",
  UID          = Sys.getenv("RS_USER"),
  PWD          = Sys.getenv("RS_PASS"),
  Port         = 5439
)

И я получаю ошибку:

Error: nanodbc/nanodbc.cpp:1138: 00000%0D%0A[Amazon][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function: [Amazon][Support] (50483) Could not load shared library, all attempted paths ("") failed

Погуглив, я наткнулся на другой документ Posit — хотя и для другой базы данных — который был нацелен на устранение неполадок для пользователей Mac. Предлагает найти, где находится libodbcinst.dylib, и добавить его в файл конфигурации драйвера. Для меня это /opt/amazon/redshift/lib/ и оно называется amazon.redshiftodbc.ini. По ссылке выше я добавил местоположение к libodbcinst.dylib и еще одну строку внизу:

ODBCInstLib=/usr/local/Cellar/unixodbc/2.3.12/lib/libodbcinst.dylib
DriverManagerEncoding=UTF-16

У этого файла есть еще одно место в /usr/local/lib/, но оно указывает на этот Cellar путь:

libodbcinst.dylib -> ../Cellar/unixodbc/2.3.12/lib/libodbcinst.dylib

Независимо от того, какой путь я указал в файле конфигурации, я получаю ту же ошибку, но теперь этот путь добавляется в сообщение об ошибке:

> con <- dbConnect(
+   odbc(),
+   Driver       = "Amazon Redshift",
+   servername   = Sys.getenv("RS_URLS"),
+   database     = "dev",
+   UID          = Sys.getenv("RS_USER"),
+   PWD          = Sys.getenv("RS_PASS"),
+   Port         = 5439
+ )
Error: nanodbc/nanodbc.cpp:1138: 00000
[Amazon][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function: [Amazon][Support] (50483) Could not load shared library, all attempted paths ("", "/usr/local/Cellar/unixodbc/2.3.12/lib/libodbcinst.dylib") failed 
> 

Я потратил много времени на то, чтобы возиться с файлами .ini, определять, куда они указывают и т. д., и не смог найти ничего, кроме этой ошибки.

Меня больше всего беспокоит то, что на странице загрузки Amazon написано:

В операционных системах Linux и macOS X для настройки параметров подключения ODBC используется диспетчер драйверов ODBC. Менеджеры драйверов ODBC используют файлы конфигурации для определения и настройки источников данных и драйверов ODBC. Используемый вами диспетчер драйверов ODBC зависит от используемой операционной системы:

менеджер драйверов unixODBC (для операционных систем Linux)

Диспетчер драйверов iODBC (для операционной системы macOS X)

Однако в инструкции по установке говорится:

Для Unix и MacOS драйверы ODBC должны быть скомпилированы с использованием unixODBC. Драйверы, скомпилированные с помощью iODBC, также могут работать, но не поддерживаются полностью.

Я не совсем уверен — вполне возможно, что это может быть еще одна проблема с неправильно указанными путями, несмотря на все мои усилия.

Попытка 4: RJDBC

{RJDBC} стоит последним, потому что мне не нравится зависеть от Java. Однако единственное «официальное» руководство от Amazon по подключению к R использует этот пакет. К сожалению, он был опубликован в 2015 году и заставил меня загрузить, вероятно, устаревший драйвер.

Я запускаю этот код из примера:

install.packages("RJDBC")
library(RJDBC)

# download Amazon Redshift JDBC driver
download.file(
  'http://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.9.1009.jar',
  'RedshiftJDBC41-1.1.9.1009.jar'
)

# connect to Amazon Redshift
driver <- JDBC(
  "com.amazon.redshift.jdbc41.Driver",
  "RedshiftJDBC41-1.1.9.1009.jar",
  identifier.quote = "`"
)

И R немедленно прерывает сеанс. Я нашел еще одно руководство для этого пакета от 2017 года, запускаю предложенный в нем код... и R снова прерывает сеанс, когда я дохожу до чтения вызова JDBC() в драйвере.

Что делать, если я не использую более старую версию драйвера? Я загружаю его прямо с по этой ссылке на Amazon. Затем я настроил код следующим образом:

library(RJDBC)

driver <- JDBC(
  "com.amazon.redshift.jdbc42.Driver",
  "redshift-jdbc42-2.1.0.29.jar"
)

url <- sprintf(
  "jdbc:redshift://%s:%s/%s?tcpKeepAlive=true&ssl=true&sslfactory=com.amazon.redshift.ssl.NonValidatingFactory",
  Sys.getenv("RS_URLS"),
  5439,
  "dev"
)

jconn <- dbConnect(driver, url, Sys.getenv("RS_USER"), Sys.getenv("RS_PASS"))

Где я отметил класс драйвера до 42 с 41. На этот раз RStudio не прерывает сеанс R и не взрывается, пока я не запущу dbConnect(). Я также попытался получить новое имя для класса драйвера, используя findDrivers(), как предложено в документации, но столкнулся с тем же прерванным сеансом при запуске dbConnect(). Я также попробовал включить сюда параметр identifier.quote — тот же результат.

Краткое содержание

Как подключить R к Redshift?

(И почему, черт возьми, это так сложно, если такие пакеты, как {bigrquery}, тривиально упрощают работу с другими базами данных?)