Managing high partition workloads

You learn how to identify an error related to high partition workloads that require configuration of your Hive Virtual Warehouse to run successfully. You might need to configure your Virtual Warehouse to prevent these errors when inserting data into workloads with a large number of partitions and columns.

To prevent an error when inserting data into a high partition workload, such as a table having 5,000 partitions and 100 columns, configure the Hive Virtual Warehouse as described in the steps below.

The error message you might see when inserting data into a high partition workload that is not configured properly looks something like this:

ERROR : FAILED: Execution Error, return code 40000 from org.apache.hadoop.hive.ql.exec.MoveTask. MetaException(message:One or more instances could not be made persistent)

Try changing the size of your Hive Virtual Warehouse.
For example, if you experience the high partition error in a small Virtual Warehouse, try using a medium Virtual Warehouse and set min/max nodes to 40.
In the CDW UI, in Overview, go to the Virtual Warehouses tab, click , and select Edit corresponding to your Virtual Warehouse.
Click Configurations > HiveServer2.

Tune HS2 parameters in the Virtual Warehouse.

For example, search for and set the following properties, or if the property is not found, click Add Custom Configuration to add a custom configuration, and set it as follows:

set hive.optimize.sort.dynamic.partition.threshold=0;
set hive.thrift.client.max.message.size=2147483647;
set hive.metastore.client.skip.columns.for.partitions=true;
set hive.stats.autogather=false;
set hive.stats.column.autogather=false;
set hive.msck.repair.batch.size=[***TABLE SCHEMA SIZE***];

Go to the Database Catalogs tab, and select your Database Catalog.
Click Options , and select Edit.
Tune Metastore parameters in the Database Catalog.
For example, click Configurations > Metastore, search for and set the following properties, or if the property is not found, click + to add a custom configuration, and set it as follows.
```
hive.metastore.direct.sql.batch.size=[***TABLE SCHEMA SIZE***]
hive.txn.timeout=3600
hive.metastore.try.direct.sql=true
hive.metastore.try.direct.sql.ddl=true
```
Click Apply Changes.