I have an AWS athena table that has records which are returned by the standard "Select * from...." query. I do a "show create table on this and say create a new table test2".
The same select query on test2 always returns empty rows. Why is this happening?
Tables in athena save data in external sourse which in aws is S3. When you see the ddl of create table there is a LOCATION which point to the S3 bucket. If the LOCATION is different it is probably the reason that you see no rows when you execute a select on this table.
CREATE EXTERNAL TABLE `test_table`(
...
)
ROW FORMAT ...
STORED AS INPUTFORMAT ...
OUTPUTFORMAT ...
LOCATION s3://bucketname/folder/
If the location is correct, could be that you have to run MSCK REPAIR TABLE command to update the metadata in the catalog after you add Hive compatible partitions. From the doc.
Use the MSCK REPAIR TABLE command to update the metadata in the catalog after you add Hive compatible partitions. The MSCK REPAIR TABLE command scans a file system such as Amazon S3 for Hive compatible partitions that were added to the file system after the table was created
Make sure to check the Troubleshooting section as well. One thing that I was missing once was the glue:BatchCreatePartition policy on my IAM role.