for loop in Large amount of Data in Workbench.

Svilen · 2023-10-03T13:54:41+00:00

There was an error rendering this rich post.

Hello all,

I'm tiring to pull large amount of data via Workbench, previously this was done with SSIS for loop container pulling only fraction of the time frame data at a time, and appending the results.
is there way to achieve this in workbench as I'm not able to find the proper documentation.

Find more posts tagged with

Workbench

Partitioning

Accepted answers

All comments

MarkSnodgrass

The partition feature in Workbench should help you accomplish it. Here is a link to the KB article.

https://domo-support.domo.com/s/article/360062446514?language=en_US

Svilen

Thank you Mark, i have tried it, but its not very detailed and i'm a little bit confused.
i'm tried to pull data for the last 14 days, via partition but it fails, if I pull for a single day its works ok.
looking into the logs, it seems to me like its try to pull the whole thing.

my date column shows results as 10/3/2023 12:00:00 AM
Data Type: DATETIME

part of my query that define the time frame.
WHERE a.SK_DATE_ID BETWEEN TO_NUMBER(TO_CHAR(TRUNC(SYSDATE-14), 'YYYYMMDD')) AND TO_NUMBER(TO_CHAR(TRUNC(SYSDATE), 'YYYYMMDD'))

Svilen

Committing upload '2' for execution '2' and stream '5095' with update method 'APPEND' and data tag '2023-10-01'
[10.04.23 5:42:25 AM] Beginning upload of part 1 for execution 2 and upload 3.
[10.04.23 5:42:25 AM] Creating upload for execution '2' and stream '5095'
[10.04.23 5:42:25 AM] Upload 3 upload progress: 100%
[10.04.23 5:42:26 AM] Committing upload '3' for execution '2' and stream '5095' with update method 'APPEND' and data tag '2023-10-02'
[10.04.23 5:42:26 AM] Beginning upload of part 1 for execution 2 and upload 4.
[10.04.23 5:42:26 AM] Creating upload for execution '2' and stream '5095'
[10.04.23 5:42:26 AM] Beginning upload of part 1 for execution 2 and upload 5.
[10.04.23 5:42:26 AM] Upload 4 upload progress: 100%
[10.04.23 5:42:26 AM] Committing upload '4' for execution '2' and stream '5095' with update method 'APPEND' and data tag '2023-10-03'
[10.04.23 5:42:26 AM] Upload 5 upload progress: 100%
[10.04.23 5:42:26 AM] Committing upload '5' for execution '2' and stream '5095' with update method 'APPEND' and data tag '2023-09-30'

its odd that i can pull data for 4 days, but when I extend to the full range of 14 days it times out, like i don't try to pull the data 1 by 1 but actually try to preview it at once and it times out.

Svilen

if anyone come across this post, just FYI: WB tries to pull 100k records each time, and have some defaults time out. If the query aggregate to much data, it might stop the request due to timeout , we workaround it via pulling the row data, and do the aggregation in Domo