Search code examples
hadoopmapreducehiveexternal-tables

insert data to external table from an external table

While inserting data to an external table-2 from an external table-1 the data of external table-2 gets stored in /user/hive/warehouse/db-name/table-name/,but as an external table it should not store data into warehouse directory right?
Should we specify location for storing data to external table?

Solution

  • Yes, you will have to mention the location while creating the external table. You can simply do it in following way.

    Create the tables table1 and table2:

    CREATE EXTERNAL TABLE table1(col1 INT, col2 BIGINT,col3 STRING)
 ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
 STORED AS TEXTFILE
 LOCATION '<hdfs_location1>';

CREATE EXTERNAL TABLE table2(col21 INT, col22 BIGINT,col23 STRING)
     ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
     STORED AS TEXTFILE
     LOCATION '<hdfs_location2>';

    Now insert the data from table1 to table 2

    INSERT OVERWRITE TABLE table2(col21,col22,col23) SELECT * FROM table1

    It will copy the data from table 1 to table2 hdfs location.

    Please note that CTAS(Create table AS Select) is not supported for external tables.