Forum Discussion
Post ProdigyTime Out Incremental refresh importing data from AWS Athena using SIMBA Athena 64 bit ODBC
My company has moved all of data tables from a windows server to AWS Cloud.
I used to be able to use an incremental refresh for a table that has 130 million rows and 8 columns of data , using a ODBC SQL Server connection connected to a SQL Server on a Windows Server. The initial import if published from desktop , would take approx 30 to 45 minutes .
The same table now in AWS connected via the Simba Athena ODBC driver , using ADFS , will not work , it just keeps timing out after 3 to 4 hours.
The most rows of data I have successfully got this to work is just 30 million rows.
This is a major blocker for my comapny as we have invested in a premium licence , which we assumed would enable us to work with larger datasets.
Has anybody experienced this issue and found a workaround please.
8 Replies
PandadevThankyou , I am trying to get an EC2 environment set up , when this is actioned , I will resume with my testing
- ibarrau
Super User
Hi. Let's see. It might be a good idea to handle millions of rows with the properly engine. I mean that it is recommended to use a data warehouse like Snowflake for analytics query in millions of rows. Before jumping an think that is a PowerBi issue, you need to run performance test on your new environment. For example, how much time it takes to select * from the big table in S3 with Athena? if this takes hours, then it's now a PowerBi issue. (I mean select all the rows and not just limit 200).
On the other hand you need a data gateway to take that much data. Where are you hosting your gateway to use ODBC? have you installed the standard one? this might also take a lot of time if it's not on the same environment (an EC2 in AWS)
Last, I'm not sure you can do incremental refresh with Athena. I don't think the source has query folding that is required for that.
I hope that helps
- Pandadev
When I run the query from AWS web portal in Athena , it takes 35 minutes to run.
When publishing it to PowerBI I could only get the Personal GateWay to work , as it need my windows creds to be able to connect via ADFS.
I used the same personal gateway when connecting to the Remote SQL server , which worked.
The incremental refersh does appear to work fine , when I pull in 60 days of data which is about 8 million rows , the full refresh took 30 mins , and now the incremental refresh of 3 days of data takes about 8 mins.
- ibarrau
Ok. Where is that personal gateway hosted? we need to be sure the movement of data is the faster and closer we can. I would say that it needs to be in the AWS cloud.
Are you completely sure you can't use a standard gateway? I'm not familiarized with ADFS but I have connected AWS without ADFS many times using standard gateway.
Regards
Hi, I am facing the same issue, can you please let know if any solution or work arounds?
