A final update on solution 3: 1. when you use beeline to connect to a hiveserver2 instance (let's name this hiveserver2 instance hiveserver2-instance1) and submit statement like "select udfTest(20210101) from testTableA", if the udf itself contains java codes to connect to the same hiveserver2 instance hiveserver2-instance1 and executes any statement, it will make the hiveserver2-instance1 not function properly; 2. when you use beeline to connect to a hiveserver2 instance( let's name this hiveserver2 instance hiveserver2-instance1) and submit statement like "select udfTest(20210101) from testTableA", if the udf itself contains java codes to connect to the another hiveserver2 instance like hiveserver2-instance2 and executes any statement, then both hiveserver2-instance1 and hiveserver2-instance2 will function properly; 3. when you use hive service --cli to submit statement like "select udfTest(20210101) from testTableA", and the udf calls other hiveserver2 instances like hiveserver2-instance1, then both hive cli and the hiveserver2 instance will funciton properly; 4. when you use beeline to connect to a hiveserver2 instance( let's name this hiveserver2 instance hiveserver2-instance1) and submit statement like "select udfTest(user_code) from testTableA", if the udf itself contains java codes to connect to the same hiveserver2 instance like hiveserver2-instance1 and executes any statement, then hiveserver2-instance1 will function properly. The root cause is whether you are using the same hiveserver2 instance as both hive sql client and hive sql server: this is not the case for scenario 2 and 3, where you use different hiveserver2 or hive service --cli; this is also not the case for scenario 4: in this case a mr/tez/spark job is generated and scheduled to run in a yarn container, which acts as the sql client and connects back to the hiveserver2 to submit sqls; but this is the case for scenario 1, as when hiveserver2 analyze and compile the sql statement "select udfTest(20210101) from testTableA" , it finds that no map task need be generated (as we are using constant 20210101 here, no table records need be fetched), so as part of the analyze and compile process, it connects to itself and tries to execute the sql call itself, which makes it both the sql client and sql server. So to sum up, use sql calls against hiveserver2 inside udf is not a goot practice.
... View more
