hive external partitioned table
You need not drop the table and re create it the second time:
As soon as you move or rename a external hdfs location of the table just do this :
msck repair table <table_name>
In your case the error was because, The hive metastore wasnt updated with the new path .
bunty
Updated on June 13, 2022Comments
-
bunty almost 2 years
First i created hive external table partitioned by code and date
CREATE EXTERNAL TABLE IF NOT EXISTS XYZ ( ID STRING, SAL BIGINT, NAME STRING, ) PARTITIONED BY (CODE INT,DATE STRING) ROW FORMAT SERDE 'parquet.hive.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT "parquet.hive.DeprecatedParquetInputFormat" OUTPUTFORMAT "parquet.hive.DeprecatedParquetOutputFormat" LOCATION '/old_work/XYZ';
and then i execute insert overwrite on this table taking data from other table
INSERT OVERWRITE TABLE XYZ PARTITION (CODE,DATE) SELECT * FROM TEMP_XYZ;
and after that i count the number of records in hive select count(*) from XYZ; it shows me 1000 records are there and then i rename or move the location '/old_work/XYZ' to '/new_work/XYZ'
and then i again drop the XYZ table and created again pointing location to new directory means '/new_work/XYZ'
CREATE EXTERNAL TABLE IF NOT EXISTS XYZ ( ID STRING, SAL BIGINT, NAME STRING, ) PARTITIONED BY (CODE INT,DATE STRING) ROW FORMAT SERDE 'parquet.hive.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT "parquet.hive.DeprecatedParquetInputFormat" OUTPUTFORMAT "parquet.hive.DeprecatedParquetOutputFormat" LOCATION '/new_work/XYZ';
But then when i execute select count(*) from XYZ table in hive , it shows 0 records , i think i missed something , please help me on this????