Create empty table if no data in source table

[#1]

Hi i am doing some ETL with hiveQl
I am creating new tables based on external hive tables, but then the external hive table is empty no empty table is created. How do i create a empty table if there is no records in the external table om selecting from.
I am running the code from NIFI hiveql processor in nifi 2.x not supporting multiple hql statement. A oneliner would be beautiful


Her is a example 
DROP TABLE IF EXISTS myname_t1;

CREATE TABLE myname_t1 STORED AS ORC  LOCATION '/archive/data/myname/T1'AS

 select*,INPUT__FILE__NAME ETL_FILENAME from myname;

[#2 · Accepted solution]

Hi I´m not sure if I understand this exactly but I´m going to try to answer it.

If you want to create an empty table from an empty external table the only thing you have to do is:

• Create external table
  • create external table rangerhdfslog(j string) stored as textfile location '/ranger/audit/hdfs';
• Create table from external table
  • create table rangerhdfslog2 as select * from rangerhdfslog;

So, that should work with any major issue.

[#3]

@palgaba You mean without major issue?

[#4]

@Bala Vignesh N V
, yes sorry that is what I meant.

[#5]

@Bala Vignesh N V is correct.

create table <new table> as
select * from <external_table>;

Will create a new empty table with the same as the external columns. The external table has to be created prior to executing the CTAS.

[#6]

@Simon, I tried the same scenario in Spark-shell with Hive.. I am able to create orc table from another external table.. Attaching snap. createasselect.png

Can you pls provide logs or error displayed while "create as select" ?

[#7]

@nramanaiahHi, yes i found out the same,it seems to be happening when running hive through JDBC and ODBC. My problem is that i am running this in NIFI PutHiveQL processor