Great Expectations中Snowflake数据源大小写敏感表/列名配置问题

现象说明 #

• 使用Great Expectations 0.15.41校验Snowflake数据源时，仅当column参数为小写（即便原列名是大写）时校验才生效。
• 有效配置示例：

"expectations": [
    {
        "expectation_type": "expect_column_values_to_be_unique",
        "kwargs": {
            "column": "email"
        }
    }
]

• 若column写为大写EMAIL，会触发报错：

great_expectations.exceptions.exceptions.MetricResolutionError: Error: The column "EMAIL" in BatchData does not exist.

• 当前Data Context核心配置：

{
  "datasources": {
    "my_datasource": {
      "class_name": "Datasource",
      "module_name": "great_expectations.datasource",
      "execution_engine": {
        "class_name": "SqlAlchemyExecutionEngine",
        "connection_string": "snowflake://..."
      },
      "data_connectors": {
        "configured_asset_data_connector": {
          "class_name": "ConfiguredAssetSqlDataConnector",
          "assets": {
            "my_table_asset": {
              "table_name": "SOME_TABLE",
              "schema_name": "PUBLIC"
            }
          }
        }
      }
    }
  },
  "stores": {...}
}

解决方法：配置use_quoted_name参数 #

设置use_quoted_name: True可保留表/列名的原始大小写，有两种配置方式：

1. 单个表资产级配置 #

针对特定表资产添加参数，修改后的assets块：

"assets": {
  "my_table_asset": {
    "table_name": "SOME_TABLE",
    "schema_name": "PUBLIC",
    "use_quoted_name": true
  }
}

2. Data Connector全局配置 #

如果所有关联资产都需要保持大小写，直接在Data Connector层级添加参数：

"data_connectors": {
  "configured_asset_data_connector": {
    "class_name": "ConfiguredAssetSqlDataConnector",
    "use_quoted_name": true,
    "assets": {
      "my_table_asset": {
        "table_name": "SOME_TABLE",
        "schema_name": "PUBLIC"
      }
    }
  }
}

配置完成后，Great Expectations会使用带引号的标识符访问Snowflake的表和列，此时用大写EMAIL作为column参数即可正常执行校验。

内容的提问来源于stack exchange，提问作者Joaquín L. Robles