external table in athena does not get data from partitioned parquet files

Question

I am writing out a dataframe in Glue to S3

S3bucket_node3 = glueContext.write_dynamic_frame.from_options(
    frame=exploded_df,
    connection_type="s3",
    format="glueparquet",
    connection_options={
        "path": "s3://raviglue/abc-output/",
        "partitionKeys": ["logId"],
    },
    format_options={"compression": "snappy"},
    transformation_ctx="S3bucket_node3",
)

It writes out to s3 correctly.

I crawled the parquet files and took the schema of the table created from Athena

I am trying to create an external table on the parquet using:

CREATE EXTERNAL TABLE IF NOT EXISTS abc_wide_table (
utc                 	string,
sysid               	string,
level               	int,
itemtype            	string,
`logitem.int`         	int,
`logitem.struct.fname`	string,
`logitem.struct.sig`  	string,
`logitem.struct.key`  	string,
. . . . . . 
`logitem.string`      	string)
PARTITIONED BY(logId int)
STORED AS PARQUET
LOCATION 's3://raviglue/abc-output/'
tblproperties ("parquet.compress"="snappy");

The table creates but does not have any data.

Please suggest fix.

Thanks,
Ravi.

AWS-User-4042951 · Accepted Answer

a. Did you add the partition to Athena table, after creating the table manually, using any of these:
i. Use : MSCK REPAIR TABLE Table_Name;
[] MSCK REPAIR TABLE - https://docs.aws.amazon.com/athena/latest/ug/msck-repair-table.htmlii.
ii. Or Use like below
ALTER TABLE orders ADD
PARTITION (dt = '2016-05-14', country = 'IN') LOCATION 's3://mystorage/path/to/INDIA_14_May_2016/'
PARTITION (dt = '2016-05-15', country = 'IN') LOCATION 's3://mystorage/path/to/INDIA_15_May_2016/';
[] ALTER TABLE ADD PARTITION - https://docs.aws.amazon.com/athena/latest/ug/alter-table-add-partition.html

b. Additionally, you can compare the table definition for both crawler created table and manually created table
Then, check partition by running command :
i. Crawler Created table : SHOW PARTITIONS 
ii. Manually created table from Athena : SHOW PARTITIONS 
[] SHOW PARTITIONS - Synopsis - https://docs.aws.amazon.com/athena/latest/ug/show-partitions.html#synopsis

rePost-User-8741020 · Answer

Thanks, MSCK REPAIR TABLE pointed me to 2 problems - permissions for athena to add partitions and dont have uppercase letters in the partitioning key. It works now !

external table in athena does not get data from partitioned parquet files

Relevant content