0

I have a table database1.table1

Table 'database1.table1' has 2 columns and one partition column: columnA, columnB partitioned by columnC

I need to create a table 'database2.table2' with no partition but with two columns columnA, columnB. I need to COPY the data from database1.table1 into database2.table2 (without deleting any data in database1.table1)

I tried the following but the data gets moved. I need to just copy the data

CREATE TABLE DATABASE2.TABLE2 
SELECT COLUMNA, COLUMNB
FROM DATABASE1.TABLE1

Note: I need the table with actual data and I can not create an EXTERNAL table.

Mohan
  • 867
  • 2
  • 7
  • 25
  • An external table "has data". I'm not sure I understand what you mean by that point – OneCricketeer Apr 04 '16 at 13:13
  • I believe external table are more like database views which does not store any actual data – Mohan Apr 06 '16 at 10:05
  • No, external tables do contain data. The only thing that external tables differ in is whether their data is deleted from disk when you drop a table. http://stackoverflow.com/questions/17038414/difference-between-hive-internal-tables-and-external-tables – OneCricketeer Apr 06 '16 at 11:35

1 Answers1

1

You should be able to copy the data almost the way you were saying. Just add 'AS'. See the CREATE AS SELECT documentation. You're saying that the data is moved, but that can't be. CREATE AS SELECT will trigger a map reduce job. Notice in the docs that you can also change the data format, use a different serde, and your own partitioning (or no partitioning, like you're asking). 

USE database2;
CREATE TABLE table2 
  AS SELECT columna,columnb 
  FROM database1.table1
Roberto Congiu
  • 5,123
  • 1
  • 27
  • 37
  • My mistake. The source table content was deleted by some other process. This happened immediately after I have applied the create command by coincidence. – Mohan Apr 06 '16 at 10:04