KB259058: Tuning Impala for optimization in MicroStrategy Analytics Enterprise 10.x

This technote provides information on how to tune Cloudera's Impala with Strategy Analytics Enterprise 10.x
 
PARQUET FILE FORMAT
Impala can query different Hadoop file formats. The file format used for an Impala table has significant performance consequences.  Some file formats include compression support that affects the size of data on the disk and, consequently, the amount of I/O and CPU resources required to deserialize data. The amounts of I/O and CPU resources required can be a limiting factor in query performance since querying often begins with moving and decompressing data.
Parquet is a column-oriented binary file format recommended by Cloudera for highly efficient processing of large-scale queries. Parquet is especially good for queries scanning particular columns within a table, for example to query "wide" tables with many columns, or to perform aggregation operations such as SUM() and AVG() that need to process most or all of the values from a column. Each data file contains the values for a set of rows (the "row group"). Within a data file, the values from each column are organized so that they are all adjacent, enabling good compression for the values from that column. Queries against a Parquet table can retrieve and analyze these values from any column quickly and with minimal I/O.
https://www.cloudera.com/content/cloudera/en/documentation/cloudera-impala/v2-0-x/topics/impala_parquet.html
 
More information about the benefits of the Parquet file format can be found here: http://www.slideshare.net/julienledem/th-210pledem
By default Impala will create tables in text format but can create tables in Parquet file format when specifying the “STORED AS” clause.
Impala can optimize queries on Parquet tables, especially join queries, better when statistics are available for all the tables. Issue the COMPUTE STATS statement for each table after substantial amounts of data are loaded into or appended to it. See COMPUTE STATS Statement for details.
Strategy recommends organizing tables in Impala in Parquet format for large scale analytical processing. Other file formats might provide better performance in specific use cases (e.g. a table in text format might allow for near-real time analytical processing when the underlying text file is appended by another application).

 

Strategy’s VLDB settings to generate PARQUET format in Temporary tables
By default Strategy will generate temporary tables in text format under the assumption that the temporary table is generally small, and a conversion into Parquet format will add more overhead than the extra processing required by using the default file format.
In specific reporting situations this behavior can be over-written on a report-by-report basis by specifying the following VLDB parameters:
 

 
For example:

CREATE TABLE ZZMQ00 (                
       MONTH_ID INT, 
       SUBCAT_ID DOUBLE)
STORED AS PARQUET;

INSERT INTO TABLE ZZMQ00
SELECT A12.MONTH_ID     
       A13.SUBCAT_ID SUBCAT_ID 
FROM...... 

 
 
 
HDFS caching
HDFS caching provides performance and scalability benefits in production environments where Impala queries and other Hadoop jobs operate on quantities of data much larger than the physical RAM on the data nodes, making it impractical to rely on the Linux OS cache, which only keeps the most recently used data in memory. Data read from the HDFS cache avoids the overhead of checksumming and memory-to-memory copying involved when using data from the Linux OS cache.
Impala can use the HDFS caching feature to make more effective use of RAM, so that repeated queries can take advantage of data "pinned" in memory regardless of how much data is processed overall. The HDFS caching feature lets you designate a subset of frequently accessed data to be pinned permanently in memory, remaining in the cache across multiple queries and never being evicted. This technique is suitable for tables or partitions that are frequently accessed and are small enough to fit entirely within the HDFS memory cache.
Strategy recommends turning on HDFS caching assuming sufficient memory resources are available on the Impala cluster. First we decide how much memory to devote to the HDFS cache on each host. The total memory available for cached data is the sum of the cache sizes on all the hosts. We then issue hdfs cacheadmin commands to set up one or more cache pools, owned by the same user as the impalad daemon (typically impala). For example:
 

hdfs cacheadmin -addPool four_gig_pool -owner impala -limit 4000000000

 
The optimal configuration from a performance point of view is to reserve sufficient memory to load all table data for Strategy query processing into memory.  
Assuming this is not possible, Strategy recommends loading the most frequently accessed tables/partitions into memory. It is possible to find out the most commonly accessed tables using Strategy Enterprise Manager.
Begin by choosing which tables or partitions to cache. For example, these might be lookup tables that are accessed by many different join queries, or partitions corresponding to the most recent time period that are analyzed by different reports or ad-hoc queries.
In your SQL statements, you specify logical divisions such as tables and partitions to be cached. Impala translates these requests into HDFS-level directives that apply to particular directories and files.
Strategy does not recommend using the HDFS cache for temporary tables generated in multi-pass reports because these are highly temporary in nature and will benefit from OS level caching.
For further details on HDFS Caching please refer to CDH documentation.
HDFS Caching is transparent to Strategy; hence once the settings are done on the Impala server side, the performance substantially improves on Strategy side. There is no explicit setting on the Strategy side to reflect HDFS Caching.