英文:
Azure Data Factory Lookup Activity Query Error The Operation Has Timed Out
问题
I'm using ORACLE DB，并且我有一个包含多个连接的select语句。我有一个Lookup Activity，它可以执行正常的select语句没有问题。与ORACLE DB的连接工作正常。
以下是Lookup中的查询。
SELECT
A.KEY as "keyValue",
A.QUEUE,
A.WORK_CREATED_DATE
FROM (
SELECT
main.KEY_DATE || '-' || main.KEY_TIME || '.' || main.KEY_MILSEC || main.RECORDCD || main.CRNODE AS "KEY",
trim(main.QUEUECD) AS "QUEUE",
main.KEY_DATE AS "WORK_CREATED_DATE"
FROM BI.WA4U999S main
INNER JOIN AWD.AWDBE meta ON
to_timestamp((main.KEY_DATE || main.KEY_TIME || main.KEY_MILSEC), 'YYYY-MM-DDHH24:MI:SSFF8') = meta.CRDATTIM
AND main.RECORDCD = meta.RECORDCD
AND main.CRNODE = meta.CRNODE
) A
我正在通过PREVIEW DATA在ADF中测试这个查询，并且有时在数据集中勾选了FIRST ROW ONLY。
我仍然需要在这个查询中添加很多表的JOIN，并且每当我在查询中添加这个JOIN语句时。
INNER JOIN AWD.W06U999S mainUser ON
trim(meta.USER_001) = trim(mainUser.USERID)
预览数据选项失败，并返回错误代码11408，操作已超时。我已经删除了TRIM函数，但仍然不起作用。
通过调试操作运行管道不是一个选项，因为Lookup Activity将运行超过一个小时，然后返回相同的错误。
我不确定是什么导致了这个问题，或者也许在ADF中处理多个表之间的这个JOIN语句有更好的方法。
英文:
I'm using ORACLE DB and I have a select statement that has multiple joins. I have a Lookup Activity that has no issue executing a normal select statement. Connections are working fine to ORACLE DB.
Here is the query inside the Lookup.
SELECT
A.KEY as "keyValue"
,A.QUEUE
,A.WORK_CREATED_DATE
FROM (SELECT
main.KEY_DATE || '-' || main.KEY_TIME || '.' || main.KEY_MILSEC || main.RECORDCD || main.CRNODE AS "KEY"
,trim(main.QUEUECD) AS "QUEUE"
,main.KEY_DATE AS "WORK_CREATED_DATE"
FROM BI.WA4U999S main
INNER JOIN AWD.AWDBE meta ON
to_timestamp((main.KEY_DATE || main.KEY_TIME|| main.KEY_MILSEC),'YYYY-MM-DDHH24:MI:SSFF8') = meta.CRDATTIM
AND main.RECORDCD = meta.RECORDCD
AND main.CRNODE = meta.CRNODE
) A
I'm testing this query in ADF through PREVIEW DATA and by checking sometimes the FIRST ROW ONLY in the dataset.
I still have a lot of tables needed to JOIN in this query and whenever I add this JOIN statement in the Query.
INNER JOIN AWD.W06U999S mainUser ON
trim(meta.USER_001) = trim(mainUser.USERID)
The Preview Data option is failing and returning an Error Code 11408 The Operation Has Timed Out. I've removed also the TRIM function but it still doesn't work.
Running the Pipeline via Debug Operation is not an option since the Lookup Activity will just run for more than an hour then returns the same error.
I'm not sure what's causing the issue or maybe there's a better way to handle this JOIN statement between multiple tables in ADF.
答案1
得分: 1
首先，"Lookup Activity" 限制最大查询结果为 5,000 行，所以您肯定超出了这个限制。通常人们使用查找操作是为了可以遍历数组中返回的每一行或对象。
但是，假设您的查询在 Oracle 直接运行正常，没有查询问题。这些项目应该帮助您解决问题，或至少排除问题（请记住，30万行仍然会导致查找错误，所以如果您将行数减少到 < 5k，您应该能够获得结果）：
祝好运！
英文:
First off, a Lookup Activity limits max query results to 5,000 rows, so you are definitely breaking that limit. Usually people use a Lookup so that they can iterate over every row or object returned in the array.
But,
Assuming your query is running fine against Oracle directly and there are no Query issues. These items should help you resolve your issue, or at the very least troubleshoot your issue (keep in mind, 300k rows will still result in a lookup error, so if you reduce your row volume to < 5k you should get results back):
Cheers!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论