aws-data-wrangler | Pandas on AWS - Easy integration with Athena Glue

I didn't use awswrangler. Instead I used the following code which I found on this github:

From the documentation, Ignored if dataset=False.. Adding dataset=True as an argument to your read_parquet call will do the trick

Once you create your session, you can use client as well. For example:

Data Wrangler uses Boto3 under the hood. And Boto3 will look for the AWS_DEFAULT_REGION env variable. So you have two options:

Set this in your ~/.aws/config file:

I updated my deploy script to delete the __pycache__ directory, and have got past this issue.

Got the idea from this video about using Pandas on AWS Lambda: https://www.youtube.com/watch?v=vf1m1ogKYrg

Without knowing the details of what Pandas is doing under the hood I suspect the issue is that it's creating a partitioned table (as suggested by the partition_cols=["date"] part of the command). A partitioned table doesn't just have one location, it has one location per partition.

This is probably what is going on: when you create the first table you end up with data on S3 looking something like this: s3://example/table1/date=20200317/file.parquet, and a partitioned table with a partition with a location s3://example/table1/date=20200317/. The table may have a location too, and it's probably s3://example/table1/, but this is mostly meaningless – it's not used for anything, it's just that Glue requires tables to have a location.

When you create the next table you get data in say s3://example/table2/date=20200318/file.parquet, and a table with a corresponding partition. What I assume you do next is copy the data from the first table to s3://example/table2/date=20200317/file.parquet (table1 -> table2 is the difference).

When you query the new table it will not look in this location, because it is not a location belonging to any of its partitions.

You can fix this in a number of ways:

• Perhaps you don't need the partitioning at all, what happens if you remove the partition_cols=["date"] part of the command? Do you still get a partitioned table? (check in the Glue console, or by running SHOW CREATE TABLE tableX in Athena). With an unpartitioned table you can move whatever data you want into the table's location and it will be found by Athena.
• Instead of moving the data you can add the partition from the first table to the new table, run something like this in Athena: ALTER TABLE table2 ADD PARTITION ("date" = '20200317') LOCATION 's3://example/table1/date=20200317/'.
• Instead add the partition to the old table, or both. It doesn't really matter and just depends on which name you want to use when you run queries. You could also have a table that you've set up manually that is your master table, and treat the tables created by Pandas as temporary. Once Pandas has created the data you add it as a partition to the master table and drop the newly created table. That way you can have a nice name for your table and not have a datestamp in the name.
• You can copy the data if you want the data to be all in one place, and then add the partition like above.
• Someone will probably suggest copying the data like above and then run MSCK REPAIR TABLE afterwards. That works, but it will get slower and slower as you get more partitions so it's not a scalable solution.