Hive, create table ___ like ___ stored as ___

4
votes

I have a table in hive stored as text files. I want to move all the data into another table with the same schema but stored as sequence files. How do I create the second table? I wanted to use the hive create table like command but it doesn't support as sequencefile

hive> create table test_sq like test_t stored as sequencefile;
FAILED: ParseException line 1:33 missing EOF at 'stored' near 'test_t'

I am looking for a programmatic way so that I can replicate the same process for more tables.

2
hadoophive

2 Answers

5
votes
CREATE TABLE test_t LIKE test_sq;

It just copies the source table definition.The new table contains no rows. As you said you have to move all the data. In this case above query is not suitable;

try this,

CREATE TABLE test_sq row format delimited fields terminated by '|' STORED AS sequencefile AS select * from test_t;
  • Target cannot be partitioned table.
  • Target cannot be external table.
  • It copies the structure as well as the data

Note - if you don't want to give row format delimited then remove from query. You can give where clause also in query to copy selected rows;

0
votes

Try using create + insert together.

Use the normal DDL statement to create the table.

CREATE TABLE test2 (a INT) STORED AS SEQUENCEFILE

then use

INSERT INTO test2 AS SELECT * FROM test;

test is the table with Textfile as data format and 'test2' is the table with SEQUENCEFILE data format.