Create external table with select from other table

Question

I am using HDInsight and need to delete my clusters when I am finished running queries. However, I need the data I gather to survive for another day. I am working on queries that would create calculated columns from table1 and insert them into table2. First I wanted a simple test to copy the rows. Can you create an external table from a select statement?

drop table if exists table2;

create external table table2 as
select *  
from table1
STORED AS TEXTFILE LOCATION 'wasb://{container name}@{storage name}.blob.core.windows.net/';

Answer 1

I know this is too stale question but here is the solution.

CREATE EXTERNAL TABLE table2
STORED AS textfile
LOCATION wasb://....
AS SELECT * FROM table1

Answer 2

Since create external table with "as select" clause is not supported in Hive, first we need to create external table with complete DDL command and then load the data into the table. Please go through this for different data format supports.

create external table table_ext(col1 typ1,...)
STORED AS ORC
LOCATION 'table2'; // optional if not provided then default location is used

INSERT OVERWRITE TABLE table_ext Select * from table1;

make sure table_ext has same DDL as table1.

Answer 3

yes but you have to seperate it into two commands. First create the external table then fill it.

create external table table2(attribute STRING)
STORED AS TEXTFILE
LOCATION 'table2';

INSERT OVERWRITE TABLE table2 Select * from table1;

The schema of table2 has to be the same as the select query, in this example it consists only of one string attribute.