Azure Synapse Notebook使用pyodbc连接SQL Server的驱动及权限问题

问题描述 #

使用pyodbc连接Azure Synapse专用SQL池时触发以下错误：

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

尝试通过%%sh执行apt命令安装驱动时，因权限不足失败，错误包括Permission denied和无法获取dpkg锁等。

可行解决方案 #

方案1：使用Synapse内置Spark SQL连接器（推荐） #

Synapse Spark环境内置了连接专用SQL池的连接器，无需手动安装ODBC驱动，直接通过Spark API即可读写数据：

# 读取专用SQL池中的数据
df = spark.read \
    .format("com.microsoft.sqlserver.jdbc.spark") \
    .option("url", "jdbc:sqlserver://<your-server-name>.database.windows.net:1433") \
    .option("dbtable", "<schema-name>.<table-name>") \
    .option("user", "<username>") \
    .option("password", "<password>") \
    .load()

# 写入数据到专用SQL池
df.write \
    .format("com.microsoft.sqlserver.jdbc.spark") \
    .option("url", "jdbc:sqlserver://<your-server-name>.database.windows.net:1433") \
    .option("dbtable", "<schema-name>.<target-table-name>") \
    .option("user", "<username>") \
    .option("password", "<password>") \
    .mode("append") \
    .save()

方案2：通过Conda安装ODBC驱动（无需root权限） #

Synapse Spark支持使用Conda包管理器安装依赖，不需要root权限，执行以下命令：

%%conda install -c conda-forge msodbcsql17 pyodbc -y

安装完成后，即可正常使用原pyodbc代码连接：

import pyodbc
server = 'myServer.database.windows.net'
database = 'myDB'
username = 'myUser'
password = 'myPassword'

cnxn = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    f'SERVER={server};DATABASE={database};'
    f'ENCRYPT=yes;UID={username};PWD={password}'
)

方案3：创建自定义Spark池（长期复用） #

如果需要所有笔记本都能直接使用ODBC驱动，可创建自定义Spark池，通过Bootstrap脚本预装驱动：

1. 在创建Spark池时，选择高级选项卡
2. 输入以下Bootstrap脚本（Synapse会以root权限自动执行）：

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

3. 创建完成后，所有使用该自定义池的笔记本都能直接使用pyodbc连接，无需重复安装。

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