Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Create empty table if no data in source table

Solved Go to solution

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

Accepted Solutions

Re: Create empty table if no data in source table

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.

6 REPLIES 6

Re: Create empty table if no data in source table

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.

Re: Create empty table if no data in source table

@palgaba You mean without major issue?

Re: Create empty table if no data in source table

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

Re: Create empty table if no data in source table

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

Re: Create empty table if no data in source table

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

Re: Create empty table if no data in source table

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

Don't have an account?
Coming from Hortonworks? Activate your account here