CODEWITHSUNDEEP
hadoophivehiveqlazure-hdinsight
dheee
dheee

Reputation: 1648

Create new hive table from existing external portioned table

I have a external partitioned table with almost 500 partitions. I am trying to create another external table with same properties as of the old table. Then i want to copy all the partitions from my old table to the newly created table. below is my create table query. My old table is stored as TEXTFILE and i want to save the new one as ORC file.

'add jar json_jarfile;
 CREATE EXTERNAL TABLE new_table_orc (col1,col2,col3...col27)
 PARTITIONED BY (year string, month string, day string)
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
 WITH SERDEPROPERTIES (....)
 STORED AS orc
 LOCATION 'path';'

And after creation of this table. i am using the below query to insert the partitions from old table to new one.i only want to copy few columns from original table to new table 

'set hive.exec.dynamic.partition=true;
 set hive.exec.dynamic.partition.mode=nonstrict;
 INSERT OVERWRITE TABLE new_table_orc PARTITION (year,month,day) SELECT     col2,col3,col6,year,month,day FROM old_table;
 ALTER TABLE new_table_orc RECOVER PARTITIONS;'

i am getting below error.

'FAILED: SemanticException [Error 10044]: Line 2:23 Cannot insert into target table because column number/types are different 'day': Table insclause-0 has 27 columns, but query has 6 columns.'

Any suggestions?

Upvotes: 1

Views: 4061

Answers (1)

Joe K
Joe K

Reputation: 18424

Your query has to match the number and type of columns in your new table. You have created your new table with 27 regular columns and 3 partition columns, but your query only select six columns.

If you really only care about those six columns, then modify the new table to have only those columns. If you do want all columns, then modify your select statement to select all of those columns.

You also will not need the "recover partitions" statement. When you insert into a table with dynamic partitions, it will create those partitions both in the filesystem and in the metastore.

Upvotes: 2

Related Questions