Support Questions

Find answers, ask questions, and share your expertise

Create empty table if no data in source table

Rising Star
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;
1 ACCEPTED SOLUTION

New Contributor

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.

View solution in original post

6 REPLIES 6

New Contributor

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.

@palgaba You mean without major issue?

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

@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.

Expert Contributor

@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" ?

Rising Star

@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