Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Create empty table if no data in source table

avatar
Expert Contributor
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

avatar
Explorer

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

avatar
Explorer

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.

avatar

@palgaba You mean without major issue?

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

avatar

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

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

avatar
Expert Contributor

@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