SPICE Refresh error with Athena

Question & Answer
,
1

I am trying to add Athena datasource to quicksight that has around 120 Million records. I plan to do the initial loading and setup incremental refresh after that. The data in Athena is imported (by GlueCrawler) from S3. We do not do have calculated fields (yet). Currently, the SPICE import fails with the following error:

## Refresh error details
Ingestion Id
6f106798-2a81-415e-a294-0a7d6c5213f3

Error type:

SQL_EXCEPTION [Learn more](https://docs.aws.amazon.com/console/quicksight/errors)

This is a general SQL error. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again.

Error details:

Query timeout [Execution ID: 7799bb9a-7ab7-496d-b430-78af86abf451]

So,

  1. Is this because of Athena timeout of 30 minutes ? If I work with AWS to increase the soft limit, will it fix the issue.
  2. Am I on the right track ? Once the initial load to SPICE is complete, will the refresh work as expected going forward ?
  3. Should make any changes to the data model in Athena (partition, indexing etc) to make this faster ?
2

Hi @karthikjay

You are on the right track.

  1. ‘SQL_Exception Query timeout’ exception that you shared is usually caused by timeout at the source, in your case, Athena. You can create a case to increase Athena timeout limit.

  2. Incremental refresh does import your entire Athena table, but only queries data as per the look back window you setup. Depending on the subset of data, Athena table optimization etc., incremental refresh might execute faster compared to a full refresh.

  3. An optimized source, including Athena tables is recommended. However, comparison of speed post optimization depends on the underlying data.