To improve write performance for Hive tables stored on S3, use Cloudera Manager to set the parameters listed below. SeeSetting Parameters as Service-Wide Defaults with Cloudera Manager.

Thefs.s3a parameters are used to tune theS3A Connector inside theHadoop code base. The S3A Connector configurations control the number of threads used to issue concurrent upload and copy requests. A single instance of the S3A Connector is used with a HiveServer2instance, so different Hive queries can share the same connector instance. The same thread pool is used to issue upload and copy requests. This means that thefs.s3aparameters cannot be set on a per-query basis. Instead, set them for each HiveServer2 instance. In contrast, the thread pool controlled byhive.mv.files.thread iscreated for each query separately.

Dynamic partitioning is a Hive feature that enables dynamic insertions ofdata into partitions based on the value of a column in a record. It is useful for bulk creating or updating partitions. Prior to CDH 5.11, performance of Hive queries that performed dynamicpartitioning on S3 was diminished because partitions were loaded into the target table one at a time. CDH 5.11 optimizations change the underlying logic so that partitions are loaded in parallel.

Use the following parameter to tune performance on a wide range of workloads that use dynamic partitioning. This parameter can be set with Cloudera Manager at the service level or on aper-query basis using the HiveSET command. SeeSetting the Hive Dynamic Partition Loading Parameter as a Service-Wide Default with ClouderaManager.

INSERT OVERWRITE queries write data to a specific table or partition, overwriting any existing data. When Hive detects existing data in the targetdirectory, it moves the existing data to theHDFS trash directory. Moving data to the trashdirectory can significantly degrade performance when it is run on S3. In CDH 5.11, an optimization is added to move data to the trash directory in parallel by using the following parameter. UseCloudera Manager to set this parameter as a service-wide default or use the HiveSET command to set the parameter on a per-query basis. SeeSetting the Hive INSERT OVERWRITE Performance Tuning Parameter as a Service-Wide Default withCloudera Manager.

If listing input files becomes a bottleneck for the Hive query, increase the values forhive.exec.input.listing.max.threads andmapreduce.input.fileinputformat.list-status.num-threads. This bottleneck might occur if the query takes a long time to list input directories or to run split calculations whenreading several thousand partitions. However, do not set these parameters to values over50 to avoid putting excessive load on S3, which might lead to throttlingissues.

Configure Hive to perform metadata collection in parallel when reading table partitions on S3 using the HiveSET command.

For example, to set the maximum number of threads that Hive uses to list input files to20 and the number of threads used by theFileInputFormat class when listing and fetching block locations for input to5:

set hive.exec.input.listing.max.threads=20set mapreduce.input.fileinputformat.list-status.num-threads=5

Use Cloudera Manager to sethive.exec.input.listing.max.threads andmapreduce.input.fileinputformat.list-status.num-threadsas service-wide defaults.

To set hive.exec.input.listing.max.threads:

1. In the Cloudera Manager Admin Console, go to the Hive service.
2. In the Hive service page, click theConfiguration tab.
3. On the Configuration page, click theHiveServer2 scope.
4. Click thePerformance category.
5. Search forInput Listing Max Threads and enter the value you want to set as a service-wide default.
6. ClickSave Changes.

To set mapreduce.input.fileinputformat.list-status.num-threads:

1. In the Cloudera Manager Admin Console, go to the MapReduce service.
2. In the MapReduce service page, click theConfiguration tab.
3. Search forMapReduce Service Advanced Configuration Snippet (Safety Valve) for mapred-site.xml and enter the parameter, value, and description:

   <property>   <name>mapreduce.input.fileinputformat.list-status.num-threads</name>   <value>number_of_threads</value>   <description>Number of threads used to list and fetch block locations for input paths   specified by FileInputFormat</description></property>

4. ClickSave Changes.

Thehive.metastore.fshandler.threads parameter can be increased if theMSCK REPAIR TABLE command is taking excessive time toscan S3 for potential partitions to add. Do not set this parameter to a value higher than30 to avoid putting excessive load on S3, which can lead to throttlingissues.

Increase the value set for thehive.msck.repair.batch.size parameter if you receive the following exception:

SocketTimeoutException: Read timed out

This exception is thrown by HiveServer2 when a metastore operation takes longer to complete than the time specified for thehive.metastore.client.socket.timeout parameter. If you simply increase the timeout, it must be set across all metastore operations and requires restarting the metastore service. Itis preferable to increase the value set forhive.msck.repair.batch.size, which specifies the number of partition objects that are added to the metastore at one time.Increasinghive.msck.repair.batch.size to3000 can help mitigate timeout exceptions returned when runningMSCK commands. Set to a lower value if you have multipleMSCK commands running in parallel.

Use the HiveSET command to specify how many partition objects are sent per batch from the HiveServer2 service to the Hive metastore service at the sessionlevel.

For example, to specify that batches containing 3,000 partition objects each are sent:

set hive.msck.repair.batch.size=3000

Use Cloudera Manager to set thehive.metastore.fshandler.threads and thehive.msck.repair.batch.size parameters asservice-wide defaults:

1. In the Cloudera Manager Admin Console, go to the Hive service.
2. In the Hive service page, click theConfiguration tab.
3. On the Configuration page, search for each parameter to set them.
4. ClickSave Changes.