Cannot change hive.exec.max.dynamic.partitions in Spark

Question

I'm trying to insert some data in a table that will have 1500 dynamic partitions and I receive this error:

 Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: 
 Number of dynamic partitions created is 1500, which is more than 1000. 
 To solve this try to set hive.exec.max.dynamic.partitions to at least 1500.

So, I try to: SET hive.exec.max.dynamic.partitions=2048 but I still get the same error.

How can I change this value from Spark?

Code:

this.spark.sql("SET hive.exec.dynamic.partition=true")
this.spark.sql("set hive.exec.dynamic.partition.mode=nonstrict")
this.spark.sql("SET hive.exec.max.dynamic.partitions=2048")
this.spark.sql(
    """
      |INSERT INTO processed_data
      |PARTITION(event, date)
      |SELECT c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,event,date FROM csv_data DISTRIBUTE BY event, date
    """.stripMargin
).show()

Using Spark 2.0.0 standalone mode. Thank you!

Answer 1

From spark 2.x version, adding hive set properties in Spark CLI may not work. Please add your hive set properties in hive-site.xml of your both spark and hive conf directories.

adding below property in hive-site.xml file should resolve your issue.

<name>hive.exec.max.dynamic.partitions</name>
<value>2048</value>
<description></description>

Note: restart hiveserver2 and spark history server if it didn't work.