本地Python脚本查询Azure Synapse湖数据库遇权限问题求助

一、对应AWS流程的Azure完整实现步骤 #

1. 创建Azure Synapse工作区 #

• 必须关联ADLS Gen2存储账户（支持分层分区存储），存储账户选择BlobStorage类型，启用Hierarchical namespace
• 确保存储账户的容器中已有分区格式的Parquet数据（如year=2000/month=1/day=1/file1.parquet）

2. 创建湖数据库与分区外部表（对应AWS Glue Catalog） #

Synapse湖数据库对应Glue Catalog，外部表对应Glue Crawler生成的表，需手动创建（或用Synapse爬取功能替代Crawler）：

步骤1：创建数据库范围凭据

用于Synapse访问ADLS Gen2存储，在Synapse Studio的SQL脚本窗口执行：

CREATE DATABASE SCOPED CREDENTIAL ADLSGen2Credential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '你的存储账户SAS令牌'; -- 令牌需包含读取权限，有效期足够

步骤2：创建外部数据源

指向存储账户的目标路径：

CREATE EXTERNAL DATA SOURCE ADLSGen2DataSource
WITH (
    LOCATION = 'abfss://<容器名>@<存储账户名>.dfs.core.windows.net/<数据根路径>',
    CREDENTIAL = ADLSGen2Credential,
    TYPE = HADOOP
);

步骤3：创建Parquet文件格式

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

步骤4：创建分区外部表

定义非分区列和分区列（year/month/day）：

CREATE EXTERNAL TABLE dbo.tablename (
    -- 示例非分区列，根据实际数据调整
    user_id INT,
    event_time DATETIME,
    event_type STRING
)
PARTITIONED BY (year INT, month INT, day INT)
WITH (
    LOCATION = 'abfss://<容器名>@<存储账户名>.dfs.core.windows.net/<数据根路径>/',
    DATA_SOURCE = ADLSGen2DataSource,
    FILE_FORMAT = ParquetFormat
);

步骤5：刷新分区元数据

同步存储中的分区数据到表：

MSCK REPAIR TABLE dbo.tablename;

二、解决本地Python查询的凭据错误 #

你遇到的Cannot find the CREDENTIAL错误，核心原因是外部表依赖的凭据未正确关联，或连接Synapse的SQL用户无访问权限，按以下步骤修复：

1. 排查凭据与数据源关联 #

• 执行EXEC sp_tables_ex 'dbo.tablename'查看外部表绑定的数据源
• 执行SELECT * FROM sys.database_scoped_credentials确认凭据存在，若不存在重新创建

2. 授予SQL用户权限 #

给代码中使用的SQL用户（username）授予外部表查询权限：

GRANT SELECT ON OBJECT::dbo.tablename TO [你的SQL用户名];
-- 若用户需要访问凭据，额外执行：
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::ADLSGen2Credential TO [你的SQL用户名];

3. 修正Python连接脚本 #

确保连接参数正确，用pandas.read_sql简化代码：

import pyodbc
import pandas as pd

# 替换为你的实际参数
driver = '{ODBC Driver 17 for SQL Server}'
server = '<Synapse SQL端点，格式：<工作区名>.sql.azuresynapse.net>'
database = '<湖数据库名>'
username = '<SQL用户名>'
password = '<SQL密码>'

connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'

try:
    with pyodbc.connect(connection_string) as conn:
        query = "SELECT TOP 10 * FROM dbo.tablename"
        df = pd.read_sql(query, conn)
        print(df)
except Exception as e:
    print(f"错误：{e}")

三、替代方案：使用Synapse官方SDK查询 #

如果pyodbc仍有问题，可使用Azure官方SDK适配Synapse环境：

from azure.synapse.analytics import SynapseAnalyticsClient
from azure.identity import ClientSecretCredential
import pandas as pd

# 替换为你的Azure AD服务主体信息
tenant_id = '<租户ID>'
client_id = '<客户端ID>'
client_secret = '<客户端密钥>'
synapse_workspace_url = 'https://<工作区名>.dev.azuresynapse.net'

# 认证并执行查询
credential = ClientSecretCredential(tenant_id, client_id, client_secret)
client = SynapseAnalyticsClient(synapse_workspace_url, credential)

query_result = client.sql_script.execute_sql_script(
    database_name='<湖数据库名>',
    sql_script='SELECT TOP 10 * FROM dbo.tablename'
)

# 转换为DataFrame
df = pd.DataFrame(query_result.result_set.rows, columns=[col.name for col in query_result.result_set.columns])
print(df)

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