CODEWITHSUNDEEP
amazon-web-servicesamazon-athena
idan
idan

Reputation: 2200

Athena query after create table get no result

I received List, and Read permissions to the S3 bucket, as you can see I'm able to see it from my UI and the file is parquet.

enter image description here

Now I'm try to query it by Athena so I used this statement to create a new table :

CREATE EXTERNAL TABLE IF NOT EXISTS `test_data`.`test_funnel` (
  `request_id` string,
  `request_time_ms` timestamp,
  `request_date` timestamp,
  `request_day` timestamp,

)
PARTITIONED BY (`dt` string, `is_won` int)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION 's3://data/data_a/agg_data_b/'
TBLPROPERTIES ('classification' = 'parquet');

When I tried to query this table I got no result, please advice

enter image description here

UPDATE: I ran MSCK REPAIR TABLE and got Partitions not in metastore: data_v2:dt=20210828/20210831223158/is_won=1 , query the data and still get no result.

enter image description here

Upvotes: 0

Views: 940

Answers (1)

Robert Kossendey
Robert Kossendey

Reputation: 7028

You partitioned your table. To make your meta store aware of those partitions you need to run MSCK REPAIR TABLE. After that you should be able to see the data.

Edit:

It seems like the way you partitioned is not Hive compatible. You have a folder level between your partition folder levels (20210905*** etc.) which is not Key-Value based.

A workaround is adding those partitions manually in Glue, which is only possible if there are not too many. Otherwise you need to change your partitioning.

Upvotes: 2

Related Questions