what's the best practice for pooling Hive JDBC connections

Question

I am using Hive JDBC driver to do sql like query against my HDFS data store. I've been trying to use c3p0 to handle the connection pooling. I am not so sure it's the right approach since Hive query can be taking quite long time sometimes which means the connection will be held for quite long not being released back to the pool, I am struggling to think of a right setting number for max number of connections in c3p0 configuration as well.

Is there any best practice for pooling the hive jdbc connection? c3p0? DBCP?

How about MAX_POOL_SIZE? Should it be bigger than normal setting for RDB ?

Answer 1

Oh great, so the question in the thread you mention was actually asked by me a long ago :) It'd be interesting to see how to use that in your use case.

But let me tell you Hive launches Hadoop jobs whenever required depending upon you query. So, if you want to execute multiple queries, which I believe is what you want to do here, you need to use a job scheduler that can run multiple jobs concurrently. Hadoop by default uses first-in-first-out(FIFO) scheduler, pulling jobs from a work queue. So, would like to switch to Fair scheduler or Capacity scheduler.

The core idea behind the fair share scheduler was to assign resources to jobs such that on average over time, each job gets an equal share of the available resources.

The capacity scheduler shares some of the principles of the fair scheduler but has distinct differences, too. First, capacity scheduling was defined for large clusters, which may have multiple, independent consumers and target applications.In capacity scheduling, instead of pools, several queues are created, each with a configurable number of map and reduce slots. Each queue is also assigned a guaranteed capacity (where the overall capacity of the cluster is the sum of each queue's capacity). Second, ability to prioritize jobs within a queue. Lastly, is the presence of strict access controls on queues.