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/';

Gonagle

Answer 1

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.

Como

It complains of rmr: Cannot move "wasb://{container name}@{storage name}.blob.core.windows.net/" to the trash, as it contains the trash. Consider using -skipTrash option – Gonagle

where do you set -skipTrash? – Gonagle

This used to be a bug but should now be fixed by using the PURGE command since hive 0.14 PURGE – Como

what is they syntax for purge with an insert overwrite? INSERT OVERWRITE TABLE table2 [PURGE] Select * from table1; – Gonagle

I think the error is thrown on the drop table command in your script thus it wold be DROP TABLE IF EXISTS table2 PURGE; – Como

Oh, the problem is not with the create table command, it is with the insert overwrite – Gonagle

if you havent deleted the table2 before with the command I posted earlier try INSERT INTO ... instead of INSERT OVERWRITE – Como

I've gone off the original topic now, so moving here: #30577583 – Gonagle

Can we create this table without having to specify the schema again? – Lorenz

Answer 2

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

Armstrong

I get this error in Hive 2.1.1 FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot create external table – Lorenz

This works if I remove the keyword EXTERNAL from the command. The external LOCATION is still accepted. – Lorenz

Answer 3

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.

Oarfish