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?
3 Answers
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.
Any table you create in hive whether its internal or external file is moved to '/user/hive/warehouse' or whatever you specify in
hive.metastore.warehouse.dir
in hive-site.xml
External table is created- to prevent the data loss when someone drop the table accidentally. Try to create 2 external tables and browse the filesystem. You can easily understand the concept.
I think you have created external table-2 without specifying LOCATION. Try using below syntax
CREATE EXTERNAL TABLE [db_name.]table_name
[(col_name data_type [COMMENT col_comment], ...)]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];
