External Hive table on GCP dataproc not readng data from GCP bucket

user_2965883 0
Viewed 378

I have data in a GCP bucket in the following format:

gs://bucket/my_table/data_date=2021-03-26/000
gs://bucket/my_table/data_date=2021-03-26/001
gs://bucket/my_table/data_date=2021-03-27/000
gs://bucket/my_table/data_date=2021-03-27/001

I'm creating an external table from the data using:

CREATE EXTERNAL TABLE `my_db.my_table`(
  `col1` string, 
  `col2` string, 
PARTITIONED BY ( 
  `data_date` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'field.delim'='\t', 
  'serialization.format'='\t') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'gs://bucket/my_table/'

There is no error while creating the table:

hive > CREATE EXTENAL TABLE ...
Time Taken: 0.012 seconds
OK

However, I'm not able to see any data. None of the following commands return anything even though there are data files in the bucket.

hive> show partitions my_db.my_table;
Ok
Time taken: 0.191 seconds

hive> select * from my_db.my_table;
Ok
Time taken: 0.191 seconds

I don't see any errors as well. I've verified and I do have read access to the bucket.

google-cloud-dataproc google-cloud-platform hive
1 Answers

You need to repair the table to retrieve all the existing partitions in an external table. The repair command recovers all the partitions and updates the Hive metastore.

MSCK REPAIR TABLE TABLE_NAME

You can read more about the repair command here.

user_71486380
Related