CODEWITHSUNDEEP
databaseclouderaimpala
Classified
Classified

Reputation: 6050

How to create partitions from one impala table to another

I want to clone an impala table called 'blah' in our database for test purposes (so I don't accidentally delete something I shouldn't). However, table 'blah' has partitions and it doesn't seem like the command I'm running is doing the trick. Can someone tell me any tricks to have the partitions automatically created/mimicked in the new table or do I need to manually create the partitions in my new table?

The cmd I'm using is:

CREATE TABLE blah_copy LIKE blah
INSERT INTO TABLE blah_copy SELECT * FROM blah

It looks to create the table for me when I run because they have the same number of columns

desc blah
desc blah_copy

However, when I try to copy the data (the insert statement above), it tells me 

ERROR: AnalysisException: Not enough partition columns mentioned in query. Missing columns are: www, xxx, yyy, zzz.

Looking at the cloudera documentation, it says:

If the original table is partitioned, the new table inherits the same partition key columns. Because the new table is initially empty, it does not inherit the actual partitions that exist in the original one. To create partitions in the new table, insert data or issue ALTER TABLE ... ADD PARTITION statements.

So that's where I'm at right now. Thanks in advance for any help you can give me.

Upvotes: 3

Views: 12928

Answers (3)

fc9.30
fc9.30

Reputation: 2571

You have to add a PARTITION (column) to your insert query

INSERT INTO TABLE blah_copy PARTITION (column) 
SELECT * FROM blah

Upvotes: 4

b1n0ys
b1n0ys

Reputation: 421

If you have latest impala version you could do 

create table blah_copy partitioned by (column_x, column_y, column_z) as select * from blah;

Upvotes: 4

Harper Koo
Harper Koo

Reputation: 627

  1. set hive.exec.dynamic.partition.mode=nonstrict
  2. CREATE TABLE blah_copy LIKE blah;
  3. INSERT INTO TABLE blah_copy partition(wName,xName,yName,zName) SELECT * FROM blah;

Upvotes: 2

Related Questions