启用Unity Catalog的Azure Databricks中ODBC Driver安装失败求助

问题背景 #

尝试用pyodbc连接SQL Server时触发驱动缺失错误：

import pyodbc
sql_server_url='server_url'
sqlDB='db-dev'
SQL_DB_USER='user'
SQL_DB_PASS='123'
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + sql_server_url +';DATABASE=' + sqlDB + ';UID='+SQL_DB_USER+';PWD='+SQL_DB_PASS)

报错信息：

('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

直接通过%sh命令安装驱动时，因权限限制失败：

%sh
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

错误提示：

E: This command can only be used by root.
/bin/bash: line 1: /etc/apt/sources.list.d/mssql-release.list: Permission denied
sudo: a terminal is required to read the password; either use the -S option to read from standard input or configure an askpass helper
sudo: a terminal is required to read the password; either use the -S option to read from standard input or configure an askpass helper

该操作在标准Databricks版本中正常，但在启用Unity Catalog的Runtime 11.3中受限。

解决方案 #

启用Unity Catalog的Databricks Runtime限制了交互式shell的root权限操作，必须通过集群初始化脚本以root权限完成驱动安装，步骤如下：

1. 创建并上传初始化脚本 #

使用dbutils.fs.put命令将安装脚本上传到DBFS：

dbutils.fs.put("/databricks/init_scripts/install_sql_odbc.sh", """
#!/bin/bash
# 添加Microsoft官方密钥与源配置
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
# Runtime 11.3基于Ubuntu 18.04，需对应正确的源版本
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
# 更新源并静默安装ODBC驱动
apt-get update
ACCEPT_EULA=Y apt-get -q -y install msodbcsql17
""", True)

2. 配置集群加载初始化脚本 #

• 打开目标集群的配置页面，进入高级选项 -> 初始化脚本
• 点击「添加」，选择「DBFS」并输入路径：dbfs:/databricks/init_scripts/install_sql_odbc.sh
• 保存配置并重启集群，脚本会在集群启动阶段自动以root权限执行安装

3. 验证安装结果 #

集群重启后，运行以下命令确认驱动已成功安装：

%sh
ls /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17*.so

若输出类似/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.2.1.so的文件路径，说明安装完成，此时重新运行pyodbc连接代码即可正常工作。

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