Impala Tuning Summary

1
2
3

select straight_join x from medium join small join (select * from big 
    where c1 < 10) as big
       where medium.id = small.id and small.id = big.id;

1
2
3
4
5
6
7
8
9

SHOW TABLE STATS table_name;
SHOW COLUMN STATS table_name;
show partitions table_name;

-- hive
ANALYZE TABLE COMPUTE STATISTICS FOR COLUMNS;

-- optimize
COMPUTE STATS table_name;

1

hdfs cacheadmin -addPool four_gig_pool -owner impala -limit 4000000000

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- Cache the entire table (all partitions).
alter table census set cached in 'pool_name';

-- Remove the entire table from the cache.
alter table census set uncached;

-- Cache a portion of the table (a single partition).
-- If the table is partitioned by multiple columns (such as year, month, day),
-- the ALTER TABLE command must specify values for all those columns.
alter table census partition (year=1960) set cached in 'pool_name';

-- Cache the data from one partition on up to 4 hosts, to minimize CPU load on any
-- single host when the same data block is processed multiple times.
alter table census partition (year=1970)
  set cached in 'pool_name' with replication = 4;

-- At each stage, check the volume of cached data.
-- For large tables or partitions, the background loading might take some time,
-- so you might have to wait and reissue the statement until all the data
-- has finished being loaded into the cache.
show table stats census;
+-------+-------+--------+------+--------------+--------+
| year  | #Rows | #Files | Size | Bytes Cached | Format |
+-------+-------+--------+------+--------------+--------+
| 1900  | -1    | 1      | 11B  | NOT CACHED   | TEXT   |
| 1940  | -1    | 1      | 11B  | NOT CACHED   | TEXT   |
| 1960  | -1    | 1      | 11B  | 11B          | TEXT   |
| 1970  | -1    | 1      | 11B  | NOT CACHED   | TEXT   |
| Total | -1    | 4      | 44B  | 11B          |        |
+-------+-------+--------+------+--------------+--------+

1
2
3
4
5

--data_cache=/data/0,/data/1:500GB

LRU (Least Recently Used--the default)
LIRS (Inter-referenece Recency Set)
--data_cache_eviction_policy=policy

1
2
3
4
5

sudo sysctl -w vm.drop_caches=3 vm.drop_caches=0
vm.drop_caches = 3
vm.drop_caches = 0

sudo dd if=/dev/sda bs=1M of=/dev/null count=1k

1

impala-shell -i impalad-1.mydomain.com -k -b loadbalancer-1.mydomain.com

1
2
3
4
5

--scratch_dirs="/dir1:200GB:0, /dir2:1024GB:1, /dir3:1024GB:1, /dir4:1024GB:1"
--disk_spill_compression_codec=LZ4
--disk_spill_punch_holes=true

--scratch_dirs=hdfs://hdfsnn/tmp:300G,/local_buffer_dir:512M,/local_dir:10G

1
2
3
4
5

Server
    URI
    Database
        Table
            Column

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

<allocations>
    <queue name="root">
        <aclSubmitApps> </aclSubmitApps>
        <queue name="default">
                       <!-- Equivalent to the "max memory" parameter, which defines the maximum memory for the cluster scope -->
            <maxResources>40000 mb, 0 vcores, 0 disks</maxResources>
            <aclSubmitApps>*</aclSubmitApps>
        </queue>
        <queue name="dev">
                      <!-- Defines the maximum memory of a cluster-wide resource pool 80G -->
                      <!-- The user mapping relationship is also specified -->
            <maxResources>80000 mb, 0 vcores, 0 disks</maxResources>
            <aclSubmitApps>user1,user2 dev,tech,admin</aclSubmitApps>
        </queue>
    </queue>
    <queuePlacementPolicy>
        <rule name="specified" create="false"/>
        <rule name="default" />
    </queuePlacementPolicy>
</allocations>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

<?xml version="1.0" encoding="UTF-8"?>
<configuration>

<!--  Configuration of the first queue -->
<property>
    <name>llama.am.throttling.maximum.placed.reservations.root.default</name>
    <value>100</value>
</property>
<property>
    <name>llama.am.throttling.maximum.queued.reservations.root.default</name>
    <value>100</value>
</property>
<property>
    <name>impala.admission-control.pool-queue-timeout-ms.root.default</name>
    <value>5000</value>
</property>
<property>
    <name>impala.admission-control.pool-default-query-options.root.default</name>
    <value>mem_limit=2000m,query_timeout_s=20</value>
</property>



<!-- The configuration of another queue -->
<property>
    <name>llama.am.throttling.maximum.placed.reservations.root.dev</name>
    <value>50</value>
</property>
<property>
    <name>llama.am.throttling.maximum.queued.reservations.root.dev</name>
    <value>50</value>
</property>

<!-- Set the timeout period of the queue -->
<property>
    <name>impala.admission-control.pool-queue-timeout-ms.root.dev</name>
    <value>5000</value>
</property>
<property>
    <name>impala.admission-control.pool-default-query-options.root.dev</name>
    <value>mem_limit=4000m, query_timeout_s=40</value>
</property>
</configuration>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- What goes inside the < > for an ARRAY is a single type, either a scalar or another
-- complex type (ARRAY, STRUCT, or MAP).
CREATE TABLE array_t
(
  id BIGINT,
  a1 ARRAY <STRING>,
  a2 ARRAY <BIGINT>,
  a3 ARRAY <TIMESTAMP>,
  a4 ARRAY <STRUCT <f1: STRING, f2: INT, f3: BOOLEAN>>
)
STORED AS PARQUET;

-- What goes inside the < > for a MAP is two comma-separated types specifying the types of the key-value pair:
-- a scalar type representing the key, and a scalar or complex type representing the value.
CREATE TABLE map_t
(
  id BIGINT,
  m1 MAP <STRING, STRING>,
  m2 MAP <STRING, BIGINT>,
  m3 MAP <BIGINT, STRING>,
  m4 MAP <BIGINT, BIGINT>,
  m5 MAP <STRING, ARRAY <STRING>>
)
STORED AS PARQUET;

-- What goes inside the < > for a STRUCT is a comma-separated list of fields, each field defined as
-- name:type. The type can be a scalar or a complex type. The field names for each STRUCT do not clash
-- with the names of table columns or fields in other STRUCTs. A STRUCT is most often used inside
-- an ARRAY or a MAP rather than as a top-level column.
CREATE TABLE struct_t
(
  id BIGINT,
  s1 STRUCT <f1: STRING, f2: BIGINT>,
  s2 ARRAY <STRUCT <f1: INT, f2: TIMESTAMP>>,
  s3 MAP <BIGINT, STRUCT <name: STRING, birthday: TIMESTAMP>>
)
STORED AS PARQUET;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

desc array_t;
Query: describe array_t
+------+------------------+---------+
| name | type             | comment |
+------+------------------+---------+
| id   | bigint           |         |
| a1   | array<string>    |         |
| a2   | array<bigint>    |         |
| a3   | array<timestamp> |         |
| a4   | array<struct<    |         |
|      |   f1:string,     |         |
|      |   f2:int,        |         |
|      |   f3:boolean     |         |
|      | >>               |         |
+------+------------------+---------+

desc map_t;
Query: describe map_t
+------+---------------------------+---------+
| name | type                      | comment |
+------+---------------------------+---------+
| id   | bigint                    |         |
| m1   | map<string,string>        |         |
| m2   | map<string,bigint>        |         |
| m3   | map<bigint,string>        |         |
| m4   | map<bigint,bigint>        |         |
| m5   | map<string,array<string>> |         |
+------+---------------------------+---------+

desc struct_t;
Query: describe struct_t
+------+----------------------+---------+
| name | type                 | comment |
+------+----------------------+---------+
| id   | bigint               |         |
| s1   | struct<              |         |
|      |   f1:string,         |         |
|      |   f2:bigint          |         |
|      | >                    |         |
| s2   | array<struct<        |         |
|      |   f1:int,            |         |
|      |   f2:timestamp       |         |
|      | >>                   |         |
| s3   | map<bigint,struct<   |         |
|      |   name:string,       |         |
|      |   birthday:timestamp |         |
|      | >>                   |         |
+------+----------------------+---------+

1
2
3

SELECT s1.f1, s1.f2, s2.f1, s2.f2, s3.key, 
    s3.value.name, s3.value.birthday     
FROM struct_t, struct_t.s2, struct_t.s3

1
2
3
4
5
6
7
8

create function my_lower(string) returns string location '/user/hive/udfs/hive.jar' symbol='org.apache.hadoop.hive.ql.udf.UDFLower';

select my_lower('Some String NOT ALREADY LOWERCASE');
+----------------------------------------------------+
| udfs.my_lower('some string not already lowercase') |
+----------------------------------------------------+
| some string not already lowercase                  |
+----------------------------------------------------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

#ifndef IMPALA_UDF_SAMPLE_UDF_H
#define IMPALA_UDF_SAMPLE_UDF_H

#include <impala_udf/udf.h>

using namespace impala_udf;

IntVal AddUdf(FunctionContext* context, const IntVal& arg1, const IntVal& arg2);

#endif

1
2
3
4
5
6
7
8
9

#include "udf-sample.h"

// In this sample we are declaring a UDF that adds two ints and returns an int.
IntVal AddUdf(FunctionContext* context, const IntVal& arg1, const IntVal& arg2) {
  if (arg1.is_null || arg2.is_null) return IntVal::null();
  return IntVal(arg1.val + arg2.val);
}

// Multiple UDFs can be defined in the same file

1

$ hdfs dfs -put ./udf_samples/libudfsample.so /user/hive/udfs/libudfsample.so

1
2
3
4
5
6
7
8

create function has_vowels (string) returns boolean location '/user/hive/udfs/libudfsample.so' symbol='HasVowels';

select has_vowels('abc');
+------------------------+
| udfs.has_vowels('abc') |
+------------------------+
| true                   |
+------------------------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

create aggregate function sum_of_squares(bigint) returns bigint
  > location '/user/hive/udfs/libudasample.so'
  > init_fn='SumOfSquaresInit'
  > update_fn='SumOfSquaresUpdate'
  > merge_fn='SumOfSquaresMerge'
  > finalize_fn='SumOfSquaresFinalize';
  
select 1*1 + 2*2 + 3*3 + 4*4;
+-------------------------------+
| 1 * 1 + 2 * 2 + 3 * 3 + 4 * 4 |
+-------------------------------+
| 30                            |
+-------------------------------+  

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

explain select count(*) from hudi01;
Query: explain select count(*) from hudi01
+------------------------------------------------------------+
| Explain String                                             |
+------------------------------------------------------------+
| Max Per-Host Resource Reservation: Memory=8.00KB Threads=2 |
| Per-Host Resource Estimates: Memory=10MB                   |
| Codegen disabled by planner                                |
|                                                            |
| PLAN-ROOT SINK                                             |
| |                                                          |
| 01:AGGREGATE [FINALIZE]                                    |
| |  output: sum_init_zero(hudi_test.hudi01.stats: num_rows) |
| |  row-size=8B cardinality=1                               |
| |                                                          |
| 00:SCAN HDFS [hudi_test.hudi01]                            |
|    HDFS partitions=1/1 files=5 size=2.24KB                 |
|    row-size=8B cardinality=5                               |
+------------------------------------------------------------+

1
2
3
4
5
6
7
8

summary;
+--------------+--------+-------+----------+----------+-------+------------+----------+---------------+------------------+
| Operator     | #Hosts | #Inst | Avg Time | Max Time | #Rows | Est. #Rows | Peak Mem | Est. Peak Mem | Detail           |
+--------------+--------+-------+----------+----------+-------+------------+----------+---------------+------------------+
| F00:ROOT     | 1      | 1     | 0ns      | 0ns      |       |            | 0 B      | 0 B           |                  |
| 01:AGGREGATE | 1      | 1     | 1.00ms   | 1.00ms   | 1     | 1          | 20.00 KB | 16.00 KB      | FINALIZE         |
| 00:SCAN HDFS | 1      | 1     | 29.00ms  | 29.00ms  | 5     | 5          | 28.00 KB | 1.00 MB       | hudi_test.hudi01 |
+--------------+--------+-------+----------+----------+-------+------------+----------+---------------+------------------+

1
2
3
4
5
6
7

SHOW TABLE STATS hudi01;
Query: SHOW TABLE STATS hudi01
+-------+--------+--------+--------------+-------------------+---------+-------------------+-------------------------------------------------------------+-----------+
| #Rows | #Files | Size   | Bytes Cached | Cache Replication | Format  | Incremental stats | Location                                                    | EC Policy |
+-------+--------+--------+--------------+-------------------+---------+-------------------+-------------------------------------------------------------+-----------+
| 32    | 5      | 2.24KB | NOT CACHED   | NOT CACHED        | PARQUET | false             | hdfs://nameservice1/apps/hive/warehouse/hudi_test.db/hudi01 | NONE      |
+-------+--------+----

1
2
3
4
5
6
7
8

SHOW COLUMN STATS hudi01;
Query: SHOW COLUMN STATS hudi01
+--------+--------+------------------+--------+----------+----------+--------+---------+
| Column | Type   | #Distinct Values | #Nulls | Max Size | Avg Size | #Trues | #Falses |
+--------+--------+------------------+--------+----------+----------+--------+---------+
| id     | INT    | -1               | -1     | 4        | 4.0      | -1     | -1      |
| name   | STRING | -1               | -1     | -1       | -1.0     | -1     | -1      |
+--------+--------+------------------+--------+----------+----------+--------+---------+

1
2
3
4
5
6

SHOW PARTITIONS sales_data
+-------+-------+-------+--------+------+--------------+-------------------+--------+-------------------+----------+-----------+
| year  | month | #Rows | #Files | Size | Bytes Cached | Cache Replication | Format | Incremental stats | Location | EC Policy |
+-------+-------+-------+--------+------+--------------+-------------------+--------+-------------------+----------+-----------+
| Total |       | -1    | 0      | 0B   | 0B           |                   |        |                   |          |           |
+-------+-------+-------+--------+------+--------------+-------------------+--------+-------------------+----------+-----------+

1
2
3
4
5

CREATE EXTERNAL TABLE ice_hadoop_cat
STORED AS ICEBERG
TBLPROPERTIES('iceberg.catalog'='hadoop.catalog',
              'iceberg.catalog_location'='/path/to/catalog',
              'iceberg.table_identifier'='namespace.table');