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

Parameter Name	Description	Settings	Default
hive.mv.files.thread	Important: Only tune this parameter when you have confirmed that thread pool parallelism is impacting performance. Before making any changes, contact Cloudera Support for guidance. Sets the number of threads used to move files in a move task. Increasing the value of this parameter increases the number of parallel copies that can run on S3. A separate thread pool is used for each Hive query. When running only a few queries in parallel, you can increase this parameter for greater per-query write throughput. However, when you run a large number of queries in parallel, decrease this parameter to avoid thread exhaustion. To disable multi-threaded file moves, set this parameter to 0. This can prevent thread contention on HiveServer2. This parameter also controls renames on HDFS, so increasing this value increases the number of threads responsible for renaming files on HDFS.	Range between: 0 and 40	15
hive.blobstore.use.blobstore. as.scratchdir	When set to true, this parameter enables the use of scratch directories directly on S3.   Important: Enabling this parameter might degrade performance slightly, but is useful if the HDFS cluster is not large enough to hold the intermediate data from a Hive query.	true | false	false

The fs.s3a parameters are used to tune the S3A Connector inside the Hadoop 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 HiveServer2 instance, 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 the fs.s3a parameters cannot be set on a per-query basis. Instead, set them for each HiveServer2 instance. In contrast, the thread pool controlled by hive.mv.files.thread is created for each query separately.

Dynamic partitioning is a Hive feature that enables dynamic insertions of data 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 dynamic partitioning 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 a per-query basis using the Hive SET command. See Setting the Hive Dynamic Partition Loading Parameter as a Service-Wide Default with Cloudera Manager.

INSERT OVERWRITE queries write data to a specific table or partition, overwriting any existing data. When Hive detects existing data in the target directory, it moves the existing data to the HDFS trash directory. Moving data to the trash directory 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. Use Cloudera Manager to set this parameter as a service-wide default or use the Hive SET command to set the parameter on a per-query basis. See Setting the Hive INSERT OVERWRITE Performance Tuning Parameter as a Service-Wide Default with Cloudera Manager.

  Important: This optimization only applies to INSERT OVERWRITE queries that insert data into tables or partitions where already there is existing data.

Parameter Name	Description	Settings	Default
hive.mv.files.thread	Set this parameter to control the number of threads used to delete existing data in the HDFS trash directory for INSERT OVERWRITE queries.   Important: Originally, this parameter only controlled the number of threads used by HiveServer2 to move data from the staging directory to another location. This parameter can also be used to tune Hive write performance on S3 tables. See Hive S3 Write Performance Tuning Parameters.	Range between: 0 and 40	15

If listing input files becomes a bottleneck for the Hive query, increase the values for hive.exec.input.listing.max.threads and mapreduce.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 when reading several thousand partitions. However, do not set these parameters to values over 50 to avoid putting excessive load on S3, which might lead to throttling issues.

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

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

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

Use Cloudera Manager to set hive.exec.input.listing.max.threads and mapreduce.input.fileinputformat.list-status.num-threads as 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 the Configuration tab.
3. On the Configuration page, click the HiveServer2 scope.
4. Click the Performance category.
5. Search for Input Listing Max Threads and enter the value you want to set as a service-wide default.
6. Click Save 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 the Configuration tab.
3. Search for MapReduce 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. Click Save Changes.

The hive.metastore.fshandler.threads parameter can be increased if the MSCK REPAIR TABLE command is taking excessive time to scan S3 for potential partitions to add. Do not set this parameter to a value higher than 30 to avoid putting excessive load on S3, which can lead to throttling issues.

Increase the value set for the hive.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 the hive.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. It is preferable to increase the value set for hive.msck.repair.batch.size, which specifies the number of partition objects that are added to the metastore at one time. Increasing hive.msck.repair.batch.size to 3000 can help mitigate timeout exceptions returned when running MSCK commands. Set to a lower value if you have multiple MSCK commands running in parallel.

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

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 the hive.metastore.fshandler.threads and the hive.msck.repair.batch.size parameters as service-wide defaults:

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