Support Questions

Find answers, ask questions, and share your expertise
Check out our newest addition to the community, the Cloudera Data Analytics (CDA) group hub.

NiFi PutHiveQL Error when try to use INSERT INTO SELECT

Super Collaborator

Hi ,

I created an external table pointing to HDFS location where files are stored in text format, i used NiFi to load files into HDFS. and i can query tables and can see the data. Now as a second step i created another external table as ORC format. i am trying to use PutHiveQL to do INSERT INTO ORC_table SELECT all_columns from Txt_table.

i am getting the error below. strange thing is if i take the SQL from ReplaceText process and execute it outside of Nifi (in beeswax) it is running fine. My Hive ConnectionPool works fine as it is able to execute the ALTER TABLE command sucessfuly in other places in the same flow.

any idea why.?

PutHiveQL[id=f7721f65-4a29-1c2b-d28f-5e05da067eda] Failed to update Hive for StandardFlowFileRecord[uuid=2ec42160-bd6a-4c45-932d-7a038b663036,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1486589044030-642665, container=default, section=617], offset=7897, length=1460],offset=0,name=GroupM_Nestle_Daily_20150424.csv,size=1460] due to java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask; it is possible that retrying the operation will succeed, so routing to retry: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask

Here is that part of the replacetext contains a SQL ..i tried different versions of the SQL with partitions , without partitions , but alway getting the same error.

insert into test_display PARTITION(trans_date='${md}') SELECT m_d, c_id, c_l, s_id, s_l, p_id, p_l, cr_id, cr_l, d_id, d_l, su_id, su_l, s, o_a_b, i_a_u, i_a_f_f_g, i_a, p_o_t_i, i_m_r_u, i_m_r_f_f_g, i_m_r, s_i_i, m_i_a, m_n_i_a, s_m_i, i_m_i_u, i_m_i_f_f_g, i_m_i, h_i_r, o_m_i, o_i, o_r, i_i_u, i_i_f_f_g, i_i, i_r_u, i_r_f_f_g, i_r, 8_o_f_1_s_i, 8_o_f_1_s_r, f_o_m_i, f_o_i_n_t_m, f_o_r_n_t_m, 1_s_f_o_i, 1_s_f_o_r, i_t_s, t_e_t_s, t_e_t_h, a_m_a, u_i, u_i_r, s_u_i_t_s, u_i_t_s, t_a_d_t_s, h, h_r, s_t_u_h_s, t_u_h_s, a_q, u_t, u_t_r, t_t_t_s, t_u_t_s, sc, s_r, s_t_t_s_s, t_u_s_s, t_a_p_d_t_s, a_p_d_t_s, c_u, c_f_f_g, c, c_r_u, c_r_f_f_g, c_r, m_s, i_t_g_5_s_i, i_t_g_5_s_r, i_t_g_10_s_i, i_t_g_10_s_r, i_t_g_15_s_i, i_t_g_15_s_r, i_t_g_30_s_i, i_t_g_30_s_r, i_t_g_1_m_i, i_t_g_1_m_r, b_t_f_i, b_t_f_r, g_b_t_f_i, g_b_t_f_r, o_o_f_i, o_o_f_r, o_o_s_i, o_o_s_r, m_o_a_i, m_o_a_r, m_o_t_i, m_o_t_r, iv_m_r, iv_r, a_b_r, i_b_r, d_c_t_r, sp_r, e_a_r, i_d_r, i_p_r, h_i, hu_r, h_a_i_m_i, h_a_i_m_r, h_a_v_i, h_a_v_r, h_a_8_o_f_1_s_i, h_a_8_o_f_1_s_r, h_a_f_o_m_i, h_a_f_o_m_r, h_a_f_o_i, h_a_f_o_r, h_a_f_o_o_l_a_i, h_a_f_o_o_l_a_r, h_a_2_s_f_o_i, h_a_2_s_f_o_r, g_p_i, n_t_r, o_b_r, l_n_r, t_o_t_h_r, n_r_r, a_h_l, a_s_i, a_s_c, ad_s_c, g_m_p, g_s_p, g_u_p, gr_s_p, g_a_c_p, g_a_p, g_c_p, g_d_a_i_p, g_i_d_p, g_d_p, g_h_s_p, gr_m_p, g_o_p, g_t_p, gr_t_p FROM temp_display where trans_date = '${md}'



Super Guru

Are there any errors in the Application Tracker logs, or HiveServer2 logs? Also, are you running NiFi as the same user as the one running beeswax? Is NiFi on the same computer as the Hive client (beeswax, e.g.)? Does it work if you run beeline rather than beeswax?

Super Collaborator
@Matt Burgess

Forgot to update ,yesterday I found out its due to permissions. I was running NiFi as "nifi" user where as beeswax as my user. when I tried to change hiveconnectionpool to use my keytab instead of nifi keytab and it did work. but don't know why nifi keytab its failing. Using the same nifi keytab I can load files in to HDFS , create external table and also can use alter table add partition command..its just failing on the Insert into select statement. this insert into select will create an ORC file from cvs files. nifi user has permissions to create files and folders..anyway still need to check permissions in ranger.will update here.

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.