Create external table with select from other table

hive azure-hdinsight
Roger picture Roger · May 29, 2015 · Viewed 20k times · Source

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

FtoTheZ picture FtoTheZ · May 29, 2015

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.

Related questions

how to connect to HBase / Hadoop Database using C#

Recently, Exploring Microsoft HDInsight Hadoop for Windows.But don't know where to began and start using apache hadoop with c# / asp.net mvc. i know http://hadoopsdk.codeplex.com/ is best available resource to start, but can't find documentation to …

Read More
Create hive external table from partitioned parquet files in Azure HDInsights

I have data saved as parquet files in Azure blob storage. Data is partitioned by year, month, day and hour like: cont/data/year=2017/month=02/day=01/ I want to create external table in Hive using following create statement, which I …

Read More
How to delete and update a record in Hive

I have installed Hadoop, Hive, Hive JDBC. which are running fine for me. But I still have a problem. How to delete or update a single record using Hive because delete or update command of MySQL is not working in …

Read More