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


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.

asked a year ago957 views
2 Answers
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 <table_name> ii. Manually created table from Athena : SHOW PARTITIONS <table_name> [] SHOW PARTITIONS - Synopsis - https://docs.aws.amazon.com/athena/latest/ug/show-partitions.html#synopsis

answered a year ago
reviewed a year ago

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 !

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions