Search code examples
impala

How to duplicate a cloudera impala table backed by avro?

I have a table "test" that I want to duplicate/copy into another table "test_copy". The "test1" table is backed by Avro, which prevents me from doing the following:

CREATE TABLE test_copy LIKE test"
INSERT INTO TABLE test_copy SELECT * FROM test

I can create the table LIKE test, but I am unable to insert records into the table. 

ERROR: IllegalStateException: Unsupported TableSink format AVRO

I do not care about the avro, all I want is to be able to have a duplicate table and being able to query the records.

Is this possible?

Solution

  • Use CREATE TABLE AS SELECT. This will create a new table containing the results of the SELECT query. Unless you also specify STORED AS [format], the new table will be stored in text files which is the default file format.

    For example:

    CREATE TABLE test_copy AS SELECT * FROM test1;

    See the CREATE TABLE documentation for more details about the CREATE TABLE DDL statement.