Insert result of select statement into a Hive table

[#1]

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

[#2]

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