CTAS x data source x cross-account x structured fields

[kepler]

Hello. Thanks for AWS wrangler. We've starting using it not long ago and it's really helpful. However, I've hit a roadblock, for which I'd appreciate some guidance.

This is our current setup:

• We have an S3 parquet dataset
• Connected to a Glue catalog
• And to Athena

All populated using awswrangler.

Our data has some columns/fields that are lists.

Now the service account reading this data (wr.athena.read_sql_query) got migrated to a different AWS account id. We configured cross-account access (ref).

And now we've hit a dead end because to access the data we have to specify a data_source, but when we do, we cannot use CTAS, which prevents fetching the columns with lists.

So my question is: how can we solve both at the same time? That is, to avoid the deadlock of

awswrangler.exceptions.InvalidArgumentCombination: Queries with ctas_approach=True (default) does not support data_source values different than None and 'AwsDataCatalog'. Please check the related tutorial for more details (https:/awslabs/aws-data-wrangler/blob/main/tutorials/006%20-%20Amazon%20Athena.ipynb)

and

awswrangler.exceptions.UnsupportedType: List data type is not support with ctas_approach=False. Please use ctas_approach=True for List columns.                                                                                                                                │

?

Is there any other better and simple approach for cross-account catalog access? (compatible with awswrangler?)

(This is related to #426, but in our case DeleteTable permission is not the blocker.)

Thanks for any advice.

[kukushking]

Thanks for opening this! Let me check whether those limitations are still in place. They are enforced by wrangler but it might be something changed since the last time we reviewed this.

[kukushking]

Hi @kepler - apologies for the long wait. Good news - you should be able to use UNLOAD #1015 #1038 instead of CTAS. You can try it out by installing from the feat-athena-unload branch and give it a try:

pip uninstall awswrangler -y
pip install git+https:/awslabs/aws-data-wrangler.git@feat-athena-unload

wr.athena.read_sql_query(
    sql="SELECT * FROM test",
    database="test",
    data_source="test",
    ctas_approach=False,
    unload_approach=True,
    s3_output="s3://.../"
)

Out[21]: 
   id value        date
0   1   foo  2020-01-01
1   2   boo  2020-01-02

I just did a quick cross-account test and it worked fine. The feature is expected to be available in the next release.