在AWS Athena中运行Great Expectations时查询资产报错求助

问题重现 #

使用表资产时GX验证逻辑可正常执行，但通过add_query_asset传入自定义SQL查询时，抛出如下错误：

MetricResolutionError: Textual SQL expression '%(5634247264 anon)s' should be explicitly declared as text('%(5634247264 anon)s')

解决方案 #

方法1：用SQLAlchemy text()显式声明查询 #

GX底层依赖SQLAlchemy处理SQL语句，Athena驱动要求自定义查询必须显式标记为文本SQL。具体修改步骤：

1. 先导入SQLAlchemy的text工具：

from sqlalchemy import text

2. 修改add_query_asset的query参数，用text()包裹SQL语句：

data_asset = data.add_query_asset(
    name="properties_q", 
    query=text("SELECT * FROM props WHERE id=1128")
)

方法2：补充连接字符串的驱动标识 #

确保连接字符串明确指定Athena驱动，添加driver=PyAthena参数：

conn_str = f"awsathena+rest://:@athena.{region_name}.amazonaws.com/{schema_name}?s3_staging_dir={s3_staging_dir}&driver=PyAthena"

方法3：升级依赖版本 #

旧版本的Great Expectations或pyathena可能存在兼容BUG，尝试升级到最新稳定版：

pip install --upgrade great-expectations pyathena

修改后的完整验证代码示例 #

from sqlalchemy import text

conn_str = f"awsathena+rest://:@athena.{region_name}.amazonaws.com/{schema_name}?s3_staging_dir={s3_staging_dir}&driver=PyAthena"
data = context.sources.add_sql(
    name="props_q", connection_string=conn_str
)
context.add_or_update_expectation_suite("data_q_expectation_suite")
data_asset = data.add_query_asset(
    name="properties_q", 
    query=text("SELECT * FROM props WHERE id=1128")
)
batch_req = data_asset.build_batch_request()
validator = context.get_validator(
    batch_request=batch_req,
    expectation_suite_name="data_q_expectation_suite",
)
validator.expect_column_values_to_not_be_null(column="id")

内容的提问来源于stack exchange，提问作者Muhammad Raihan Muhaimin