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.

Insert result of select statement into a Hive table

Insert result of select statement into a Hive table

Contributor

Hi all,

I was hoping someone could confirm whether something i'm trying to do is possible because i'm currently hitting multiple issues.

I would like to add a record into a hive table using and insert statement, within this insert statement I have one column which should add a count value to the table based off of the result of a query. My Hive SQL is below....

use ${database};
set hivevar:deltaCount = select count(*) from ${database}.${hive_table};


DROP TABLE IF EXISTS ${database}.process_status_stg_${hive_table};
create table ${database}.process_status_stg_${hive_table} (
taskName varchar(50) COMMENT 'Name of the task being run to populate data',
starttime varchar(50) COMMENT 'time of record addition',
status varchar(50) COMMENT 'status of the task',
workflowID varchar(50) COMMENT 'workflow ID that is running the task',
oozieErrorCode varchar(50) COMMENT 'Error code returned by Oozie',
recordsLoadedCount varchar(50) COMMENT 'records pulled in previous load') ;

insert into table ${database}.process_status_stg_${hive_table} values ('${hive_table}','${current_time}','${taskStatus}','${workflowID}','${errorCode}', (CASE ${taskStatus} WHEN 'COMPLETED' THEN '${hiveconf:deltaCount}' ELSE 'N/A' end as recordsLoadedCount));

Any help is much appreciated,

Thanks

1 REPLY 1
Highlighted

Re: Insert result of select statement into a Hive table

@Daniel Perry

Im not sure why you are trying this way.

But instead of

that insert statement which you have mentioned try something like this:

insert into table ${database}.process_status_stg_${hive_table}  select ('${hive_table}','${current_time}','${taskStatus}','${workflowID}','${errorCode}', (CASE ${taskStatus} WHEN 'COMPLETED' THEN '${hiveconf:deltaCount}' ELSE 'N/A' end as recordsLoadedCount)) from dummytable;

Hope it helps!!

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