11

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/';
Roger
  • 2,063
  • 4
  • 32
  • 65

3 Answers3

14

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.

FtoTheZ
  • 386
  • 3
  • 8
  • 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 – Roger May 30 '15 at 15:18
  • where do you set -skipTrash? – Roger May 30 '15 at 15:19
  • This used to be a [bug](https://issues.apache.org/jira/browse/HIVE-6469) but should now be fixed by using the PURGE command since hive 0.14 [PURGE](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DropTable) – FtoTheZ May 30 '15 at 15:26
  • what is they syntax for purge with an insert overwrite? INSERT OVERWRITE TABLE table2 [PURGE] Select * from table1; – Roger Jun 01 '15 at 14:59
  • I think the error is thrown on the drop table command in your script thus it wold be `DROP TABLE IF EXISTS table2 PURGE;` – FtoTheZ Jun 01 '15 at 15:01
  • Oh, the problem is not with the create table command, it is with the insert overwrite – Roger Jun 01 '15 at 15:06
  • if you havent deleted the table2 before with the command I posted earlier try `INSERT INTO` ... instead of INSERT OVERWRITE – FtoTheZ Jun 01 '15 at 15:13
  • I've gone off the original topic now, so moving here: http://stackoverflow.com/questions/30577083/insert-overwrite-cannot-move-to-the-trash-as-it-contains-the-trash – Roger Jun 01 '15 at 15:15
  • Can we create this table without having to specify the schema again? – sudheeshix Jan 30 '19 at 10:51
3

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
Yuta Imai
  • 67
  • 1
  • 4
  • 1
    I get this error in Hive 2.1.1 `FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot create external table` – sudheeshix Jan 30 '19 at 10:52
  • 1
    This works if I remove the keyword EXTERNAL from the command. The external LOCATION is still accepted. – sudheeshix Jan 30 '19 at 11:00
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.

Sanjay Divgi
  • 41
  • 3