使用ADF HANA连接器动态范围分区时遇无效列名错误求助

错误详情 #

ErrorCode=SapHanaFailToGetBoundsDueToInvalidQuery,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Fail to get bounds by using query. Make sure your query can be nested while using partition reading and your partition column data type is acceptable.,Source=Microsoft.DataTransfer.Runtime.SapHanaConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [S1000] [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;260 invalid column name: FISCALYR: line 4 col 60 (at pos 530),Source=libodbcHDB.dll,'

复制活动输入配置 #

{
    "source": {
        "type": "SapHanaSource",
        "query": "select\r\n\"COCD\" as BUKRS,\r\n\"ASSETNBR\" as ANLN1,\r\n\"ASSETSUBNUMB\" as ANLN2,\r\n\"FISCALYR\" as GJAHR,\r\n\"REALDEPRAREA\" as AFABE,\r\n\"DEPRLASTPOST\" as AFBLPE,\r\n\"DEPRPERDPOST\" as AFBANZ,\r\n\"FISCALYREXPR\" as \r\nFROM \"_SYS_BIC\".\"intel.finance.private/AssetValueHistoricView\" WHERE ?AdfHanaDynamicRangePartitionCondition",
        "partitionOption": "SapHanaDynamicRange",
        "partitionSettings": {
            "partitionColumnName": "FISCALYR"
        }
    },
    "sink": {
        "type": "ParquetSink",
        "storeSettings": {
            "type": "AzureBlobFSWriteSettings"
        },
        "formatSettings": {
            "type": "ParquetWriteSettings"
        }
    },
    "enableStaging": false,
    "parallelCopies": 20,
    "translator": {
        "type": "TabularTranslator",
        "typeConversion": true,
        "typeConversionSettings": {
            "allowDataTruncation": true,
            "treatBooleanAsNumber": false
        }
    }
}

故障原因及解决方法 #

1. SQL语法存在非法错误 #

查询中"FISCALYREXPR" as 后未指定别名，直接衔接FROM语句，导致SQL本身语法无效。ADF执行分区边界查询时嵌套该非法SQL，必然触发报错。

• 修复：为FISCALYREXPR补充合法别名，例如"FISCALYREXPR" as GJAHR_EXPR，确保SQL可单独在HANA客户端执行成功。

2. 分区列配置与查询逻辑不匹配 #

查询已将原视图列FISCALYR别名改为GJAHR，但分区配置的partitionColumnName仍指定为FISCALYR。ADF生成分区边界查询时，会基于自定义查询的结果集嵌套查询，此时结果集中已不存在FISCALYR列，因此触发“无效列名”错误。

• 修复：将partitionColumnName改为查询后的别名GJAHR，或在查询中保留原列名（移除as GJAHR别名）。

3. 自定义查询无法被嵌套执行 #

使用动态范围分区时，ADF会自动生成嵌套查询获取分区列的min/max值（例如SELECT MIN(分区列), MAX(分区列) FROM (你的自定义查询)）。如果自定义查询无法被嵌套执行，也会触发该错误。

• 验证方法：手动执行嵌套查询，确保SELECT MIN(GJAHR), MAX(GJAHR) FROM (你的完整查询)能在HANA客户端正常运行。

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