Error: Error while processing statement: FAILED: Hive Internal Error: java.lang.OutOfMemoryError(unable to create new native thread) (state=08S01,code=12)

[#1]

i am unable to execute a hql script through beeline. i am trying to perform insert overwrite into a partitioned table by using the below query format getting out of memory error .the table has more than 80 million records. Please help me to resolve this issue. 

SET hive.tez.container.size=24576;
SET hive.tez.java.opts=-Xmx22528m;

insert overwrite table xyz select * from xzy where trim(upper(name))!= 'test' OR trim(upper(loc))!= 'abc';

insert overwrite table def select * from def  where trim(upper(name))!= 'test' OR trim(upper(loc))!= 'abc';

[#2]

Hi @harnu ,

Could you add these below values to /etc/security/limits.conf file and see if this helpfull? If you already have these values try to increase it and rerun the query.

hive soft nofile 65000

hive hard nofile 65000

hive soft proc 80000

hive hard proc 80000

Update limits.conf with above settings and restart hiveserver2 service while stopping all the services on that node.

Please su - hive and then ulimit -a to check whether the parameters are updated for no of open files and max user process.

Regards,

Chethan YM

Was your question answered? Make sure to mark the answer as the accepted solution.

If you find a reply useful, say thanks by clicking on the thumbs up button.