question Re: Create empty table if no data in source table in Archives of Support Questions (Read Only)

Create empty table if no data in source table

simon_jespersen — Thu, 17 Aug 2017 15:50:24 GMT

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;

Re: Create empty table if no data in source table

palgaba — Thu, 17 Aug 2017 20:43:56 GMT

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

balavignesh_nag — Thu, 17 Aug 2017 21:19:17 GMT

@palgaba You mean without major issue?

Re: Create empty table if no data in source table

SQLShaw — Thu, 17 Aug 2017 22:04:47 GMT

@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

palgaba — Thu, 17 Aug 2017 22:35:18 GMT

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

Re: Create empty table if no data in source table

nramanaiah — Fri, 18 Aug 2017 14:18:48 GMT

@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

simon_jespersen — Fri, 18 Aug 2017 15:19:17 GMT

@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