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.

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

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

관련 콘텐츠