hive external partitioned table

6
votes

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????

1
hadoophivebigdatahiveql

1 Answers

6
votes

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 .