KB485907: Enable bulk insert and improve report execution performance against Hive, Impala, and Spark gateways

Description

Some use cases require a large amount of data to be inserted into a database table. A typical example in Strategy is a data mart report, or a multi-statement SQL report where a temp table is created to store intermediate results. If the data of a temporary table is inserted using an 'insert into values' query, you can enable parameterized queries to turn on bulk insert and therefore improve performance.
 

 
Sample parameterized query SQL:

INSERT INTO DMTABLE (Customer_ID, Customer_Name) VALUES (?, ?)

Solutions

Follow the solutions below to improve bulk insert performance after parameterized queries are enabled for the following gateways:
 

• Hive
• Spark and Impala

Hive

Bulk insert is supported in Hive ODBC and JDBC with the following settings in addition to enabling parameterized queries:
 

• NATIVE QUERY: OFF
• VLDB Syntax Change
• Tuning Performance via PARAM_QUERY_ROWSET_SIZE

 
NATIVE QUERY: OFF
 

• For Intelligence servers on a Linux machine, find the ODBC.ini file and change the DSN for Hive Connection setting. Set the property to "UseNativeQuery=0".
   
  
• For Intelligence servers on Windows machines, open the ODBC Data Source Administrator, configure the Hive DSN, open Advanced Options, and deselect Use Native Query as shown below.
   
  

VLDB Syntax Change
 

• In Developer, open the database instance for your Hive connection. Select the Database connection type. The example below uses Hive 2.x.
   
  

• Upgrade the Database connection type's configuration. Click Upgrade to see the database types script file. The example below uses Z:\BIN\x64\DATABASE.PDS.
   
  

• Change the insert pattern for the your Hive database type. In this example, it is Hive 2.x.
   
  Open the database type script file you found in step 2. Edit the file, find the Hive 2.x DSSOBJECT section, and locate the

  "Insert Into Values Pattern"

  property. Change the value to

  "insert into #0 values (##0)"

  and save the file.
   
  Before the pattern change:
   
  

•  
  After the pattern change:
   
  

• Go back to upgrade database connection type in step 2. Click Load to read the script and in the left column, select your Hive 2.x connection type and move it to right. Click OK.
   
  

• Restart the Intelligence server. Execute the report that triggers the temp table insert to Hive. The Hive Monitor WebUI, the actual insert statement inserts multiple rows in one statement. If a large number of rows are inserted, the insert is grouped with a few hundreds rows per statement.

 
Tuning Performance via PARAM_QUERY_ROWSET_SIZE
 

• You can also test the performance for an ODBC driver by changing the batch size. Modify

  PARAM_QUERY_ROWSET_SIZE

  in the ODBCConfig.ini file and add a new section similar to the one shown in the image below.
   
  
• To change the

  PARAM_QUERY_ROWSET_SIZE

  parameter, add the lines shown below (adjusting for a number of rows to insert at a time) to the ODBCConfig.ini file located at:
   
  
• Windows platform:

  <Installation Path>\Common Files\Strategy

• Linux platform:

  <Installation Path>/MicroStrategy/install

• Developer Machine: z:\bin\x64\

Strategy's in-house test results for Hive:
 

Spark and Impala

Bulk insert is supported in Spark and Impala ODBC and JDBC with the following settings once parameterized queries are turned ON:
 

• NATIVE QUERY: OFF
• Tuning Performance via PARAM_QUERY_ROWSET_SIZE

 
NATIVE QUERY: OFF
 
While creating the DSN, make sure Use Native Query is not selected.
 
Tuning Performance via PARAM_QUERY_ROWSET_SIZE
 
You can also test the performance for an ODBC driver by changing the batch size. Modify the batch size in the z:\bin\x64\ODBCConfig.ini file and add a new section similar to the one shown in the image below. You must enter the database ID, database name, and the batch size you want to test the driver with.
 

 
Strategy's in-house test results for Spark: