bug: large tables cause queries to hang

[pnadolny13]

Under the hood (i.e. in the SDK) this tap is sending a select query to Athena when it wants to start retrieving rows but on very large tables in Athena that query looks like it needs to complete prior to any results being rendered. This isnt a problem on smaller tables but on large tables the tap seemingly hangs forever without returning records because the Athena query is still pending.

We need to have a way to query smaller result sets from Athena so we can make progress and incremental keys can be tracked for partial progress.

Using limit/offset queries is the first thing I think of but I also know this has some downsides (e.g. https://stackoverflow.com/questions/45114202/offset-on-aws-athena/45114359#45114359). I wonder if for our case the downsides are acceptable since we're not running complex aggregations, just selecting. I'm also a little worried about missing data in the case that new data is added between chunks while we're iterating. Potentially in this case we might want to make this an opt in feature and require a sortable replication key 🤔 , I'm not sure if that would solve it completely or not though.

From Edgar in slack:

Then offset+limit might work but we'd have to use REPEATABLE READ or SERIALIZABLE isolation to be safe

cc @edgarrmondragon @tayloramurphy

[pnadolny13]

@edgarrmondragon I explored this a bit and got a functioning branch going. Athena does support the OFFSET parameter https://docs.aws.amazon.com/athena/latest/ug/select.html but the docs say:

If the ORDER BY clause is present, the OFFSET clause is evaluated over a sorted result set, and the set remains sorted after the skipped rows are discarded. If the query has no ORDER BY clause, it is arbitrary which rows are discarded.

So we will have to enforce a sortable replication key. In my branch I let the user opt into pagination, set the batch size, and will throw an exception if a replication key isnt set if paginate is true.