EXPLODE_BITMAP
更新时间：2025-10-16
描述
explode_bitmap 表函数，接受一个位图（bitmap）类型的数据，将位图中的每个 bit（位）映射为单独的行。通常用于处理位图数据，将位图中的每个元素展开成单独的记录。需配合 Lateral View 使用。
explode_bitmap_outer 与 explode_bitmap 类似，但在处理空值或 NULL 时，行为有所不同。它允许空位图或 NULL 位图的记录存在，并在返回结果中将空位图或者 NULL 位图展开为 NULL 行。
语法
SQL
1EXPLODE_BITMAP(<bitmap>)
2EXPLODE_BITMAP_OUTER(<bitmap>)参数
| 参数 | 说明 |
|---|---|
<bitmap> |
bitmap 类型 |
返回值
返回位图中每一位对应的行，其中每一行包含一个位值。
举例
SQL
1CREATE TABLE example1 (
2 k1 INT
3)DUPLICATE KEY(k1)
4DISTRIBUTED BY HASH(k1) BUCKETS AUTO
5PROPERTIES (
6"replication_allocation" = "tag.location.default: 1");
SQL
1insert into example1 values(1),(2),(3),(4),(5),(6);
SQL
1select k1 from example1 order by k1;
Text
1+------+
2| k1 |
3+------+
4| 1 |
5| 2 |
6| 3 |
7| 4 |
8| 5 |
9| 6 |
10+------+
SQL
1select k1, e1 from example1 lateral view explode_bitmap(bitmap_empty()) tmp1 as e1 order by k1, e1;
2Empty set
SQL
1select k1, e1 from example1 lateral view explode_bitmap(bitmap_from_string("1")) tmp1 as e1 order by k1, e1;
Text
1+------+------+
2| k1 | e1 |
3+------+------+
4| 1 | 1 |
5| 2 | 1 |
6| 3 | 1 |
7| 4 | 1 |
8| 5 | 1 |
9| 6 | 1 |
10+------+------+
SQL
1select k1, e1 from example1 lateral view explode_bitmap(bitmap_from_string("1,2")) tmp1 as e1 order by k1, e1;
Text
1+------+------+
2| k1 | e1 |
3+------+------+
4| 1 | 1 |
5| 1 | 2 |
6| 2 | 1 |
7| 2 | 2 |
8| 3 | 1 |
9| 3 | 2 |
10| 4 | 1 |
11| 4 | 2 |
12| 5 | 1 |
13| 5 | 2 |
14| 6 | 1 |
15| 6 | 2 |
16+------+------+
SQL
1select k1, e1 from example1 lateral view explode_bitmap(bitmap_from_string("1,1000")) tmp1 as e1 order by k1, e1;
Text
1+------+------+
2| k1 | e1 |
3+------+------+
4| 1 | 1 |
5| 1 | 1000 |
6| 2 | 1 |
7| 2 | 1000 |
8| 3 | 1 |
9| 3 | 1000 |
10| 4 | 1 |
11| 4 | 1000 |
12| 5 | 1 |
13| 5 | 1000 |
14| 6 | 1 |
15| 6 | 1000 |
16+------+------+
SQL
1select k1, e1, e2 from example1
2lateral view explode_bitmap(bitmap_from_string("1,1000")) tmp1 as e1
3lateral view explode_split("a,b", ",") tmp2 as e2 order by k1, e1, e2;
Text
1+------+------+------+
2| k1 | e1 | e2 |
3+------+------+------+
4| 1 | 1 | a |
5| 1 | 1 | b |
6| 1 | 1000 | a |
7| 1 | 1000 | b |
8| 2 | 1 | a |
9| 2 | 1 | b |
10| 2 | 1000 | a |
11| 2 | 1000 | b |
12| 3 | 1 | a |
13| 3 | 1 | b |
14| 3 | 1000 | a |
15| 3 | 1000 | b |
16| 4 | 1 | a |
17| 4 | 1 | b |
18| 4 | 1000 | a |
19| 4 | 1000 | b |
20| 5 | 1 | a |
21| 5 | 1 | b |
22| 5 | 1000 | a |
23| 5 | 1000 | b |
24| 6 | 1 | a |
25| 6 | 1 | b |
26| 6 | 1000 | a |
27| 6 | 1000 | b |
28+------+------+------+
SQL
1CREATE TABLE example (
2 k1 INT,
3 v1 bitmap
4)DUPLICATE KEY(k1)
5DISTRIBUTED BY HASH(k1) BUCKETS AUTO
6PROPERTIES (
7"replication_allocation" = "tag.location.default: 1");
SQL
1insert into example values(1,to_bitmap('10101')),(2,to_bitmap('0')),(3,to_bitmap(NULL));
SQL
1SELECT id, k, v
2FROM example
3LATERAL VIEW explode_json_object(value_json) exploded_table AS k , v;
Text
1+------+-------+
2| k1 | bit |
3+------+-------+
4| 2 | 0 |
5| 1 | 10101 |
6+------+-------+
SQL
1SELECT id, k, v
2FROM example
3LATERAL VIEW explode_json_object_outer(value_json) exploded_table AS k, v;
Text
1+------+-------+
2| k1 | bit |
3+------+-------+
4| 2 | 0 |
5| 1 | 10101 |
6| 3 | NULL |
7+------+-------+