Databricks 13.3新集群ODBC连接失败问题求助

代码示例 #

import pyodbc

server = 'serve'
username = 'username'
password = 'password'
driver = '{ODBC Driver 17 for SQL Server}'
schema = 'schema'
database = 'database'

cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+dbutils.secrets.get(scope = "samplesecrets", key = "sqluname")+';PWD='+ dbutils.secrets.get(scope = "samplesecrets", key = "sqlpassword"))

连接报错信息 #

cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+dbutils.secrets.get(scope = "samplesecrets", key = "sqluname")+';PWD='+ dbutils.secrets.get(scope = "samplesecrets", key = "sqlpassword"))
    
Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

已尝试的驱动安装操作 #

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get -q -y install msodbcsql17

安装报错信息 #

Reading package lists...
Building dependency tree...
Reading state information...
msodbcsql17 is already the newest version (17.8.1.1-1).
0 upgraded, 0 newly installed, 0 to remove and 81 not upgraded.
1 not fully installed or removed.
After this operation, 0 B of additional disk space will be used.
Setting up msodbcsql17 (17.8.1.1-1) ...
dpkg: error processing package msodbcsql17 (--configure):
 installed msodbcsql17 package post-installation script subprocess returned error exit status 127
Errors were encountered while processing:
 msodbcsql17
E: Sub-process /usr/bin/dpkg returned an error code (1)

解决步骤 #

1. 修复损坏的dpkg安装状态 #

先处理未完成的包配置流程：

sudo dpkg --configure -a
sudo apt-get install -f

如果仍有报错，强制重新安装驱动包：

sudo apt-get install --reinstall msodbcsql17

2. 适配Databricks 13.3的Ubuntu版本 #

Databricks 13.3基于Ubuntu 20.04，之前使用的Ubuntu 16.04源会导致兼容性问题，替换为对应版本的源后重新安装：

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get -q -y install msodbcsql17 unixodbc-dev

3. 验证驱动安装状态 #

执行命令检查驱动是否被正确识别：

odbcinst -j

查看输出的ODBC Drivers部分，确认ODBC Driver 17 for SQL Server存在且路径正确。

4. 调整pyodbc连接字符串（可选） #

如果驱动名称识别仍有问题，直接使用驱动文件的绝对路径替换驱动名称：

driver = '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1'
cnxn = pyodbc.connect(f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={dbutils.secrets.get(scope="samplesecrets", key="sqluname")};PWD={dbutils.secrets.get(scope="samplesecrets", key="sqlpassword")}')

5. 用集群初始化脚本持久化驱动（推荐） #

Databricks集群重启后临时安装的驱动会丢失，建议创建初始化脚本并配置集群自动执行：

1. 创建脚本文件init-sql-driver.sh，内容如下：

#!/bin/bash
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get -q -y install msodbcsql17 unixodbc-dev

2. 将脚本上传到DBFS路径（比如dbfs:/databricks/scripts/init-sql-driver.sh）
3. 在集群配置的「高级选项」->「初始化脚本」中添加该DBFS路径，集群每次启动时会自动安装驱动

内容的提问来源于stack exchange，提问作者ddcabz