关于 Impala 使用方法的一些测试，包括加载数据、查看数据库、聚合关联查询、子查询等等。

1. 准备测试数据

以下测试以 impala 用户来运行：

$ su - impala
-bash-4.1$ whoami
impala
$ hdfs dfs -ls /user
Found 5 items
drwxr-xr-x   - hdfs   hadoop          0 2014-09-22 18:36 /user/hdfs
drwxrwxrwt   - mapred hadoop          0 2014-07-23 21:37 /user/history
drwxr-xr-x   - hive   hadoop          0 2014-08-04 16:57 /user/hive
drwxr-xr-x   - impala hadoop          0 2014-10-24 10:13 /user/impala
drwxr-xr-x   - root   hadoop          0 2014-09-22 10:22 /user/root

准备一些测试数据，tab1.csv 文件内容如下：

1,true,123.123,2012-10-24 08:55:00 
2,false,1243.5,2012-10-25 13:40:00
3,false,24453.325,2008-08-22 09:33:21.123
4,false,243423.325,2007-05-12 22:32:21.33454
5,true,243.325,1953-04-22 09:11:33

tab1.csv 文件内容如下：

1,true,12789.123
2,false,1243.5
3,false,24453.325
4,false,2423.3254
50,true,243.325
60,false,243565423.325
70,true,243.325
80,false,243423.325
90,true,243.325

将这两个表上传到 hdfs：

$ hdfs dfs -mkdir -p sample_data/tab1 sample_data/tab2

$ hdfs dfs -put tab1.csv /user/impala/sample_data/tab1
$ hdfs dfs -ls /user/impala/sample_data/tab1
Found 1 items
-rw-r--r--   3 impala hadoop        193 2014-10-24 10:13 /user/impala/sample_data/tab1/tab1.csv

$ hdfs dfs -put tab2.csv /user/impala/sample_data/tab2
$ hdfs dfs -ls /user/impala/sample_data/tab2
Found 1 items
-rw-r--r--   3 impala hadoop        158 2014-10-24 10:13 /user/impala/sample_data/tab2/tab2.csv

在 impala 中建表，建表语句如下：

DROP TABLE IF EXISTS tab1;
CREATE EXTERNAL TABLE tab1 (
   id INT,
   col_1 BOOLEAN,
   col_2 DOUBLE,
   col_3 TIMESTAMP
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/impala/sample_data/tab1';

DROP TABLE IF EXISTS tab2;
CREATE EXTERNAL TABLE tab2 (
   id INT,
   col_1 BOOLEAN,
   col_2 DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/impala/sample_data/tab2';

DROP TABLE IF EXISTS tab3;
CREATE TABLE tab3 (
   id INT,
   col_1 BOOLEAN,
   col_2 DOUBLE,
   month INT,
   day INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

其中 tab1 和 tab2 都是外部表，tab3 是内部表。

将上面 sql 保存在 init.sql 语句，然后运行下面命令进行创建表：

$ impala-shell -i localhost -f init.sql

也可以进入到 impala-shell 命令行模式，直接运行 sql 语句。

2. 查看表结构

查看所有数据库：

[192.168.56.121:21000] > show databases;
Query: show databases
+------------------+
| name             |
+------------------+
| _impala_builtins |
| default          |          
| testdb           |
+------------------+
Returned 3 row(s) in 0.05s

查看默认数据库下的所有表：

[192.168.56.121:21000] > show tables;
Query: show tables
+------+
| name |
+------+
| tab1 |
| tab2 |
| tab3 |
+------+
Returned 3 row(s) in 0.01s

查看 tab1 表结构：

[192.168.56.121:21000] > describe tab1;
Query: describe tab1
+-------+-----------+---------+
| name  | type      | comment |
+-------+-----------+---------+
| id    | int       |         |
| col_1 | boolean   |         |
| col_2 | double    |         |
| col_3 | timestamp |         |
+-------+-----------+---------+
Returned 4 row(s) in 0.07s

3. impala-shell 命令

使用 impala-shell 进入命令行交互模式：

$ impala-shell -i localhost

传入一个文件：

$ impala-shell -i localhost -f init.sql

执行指定的 sql：

$ impala-shell -i localhost -q 'select count(*) from tab1;'

4. 导入数据并查询

导入数据：

准备数据
创建表
加数据导入到创建的表

查询数据：

[192.168.56.121:21000] > SELECT * FROM tab1;
Query: select * FROM tab1
+----+-------+------------+-------------------------------+
| id | col_1 | col_2      | col_3                         |
+----+-------+------------+-------------------------------+
| 1  | true  | 123.123    | 2012-10-24 08:55:00           |
| 2  | false | 1243.5     | 2012-10-25 13:40:00           |
| 3  | false | 24453.325  | 2008-08-22 09:33:21.123000000 |
| 4  | false | 243423.325 | 2007-05-12 22:32:21.334540000 |
| 5  | true  | 243.325    | 1953-04-22 09:11:33           |
+----+-------+------------+-------------------------------+
Returned 5 row(s) in 0.24s
[192.168.56.121:21000] > SELECT * FROM tab2;
Query: select * FROM tab2
+----+-------+---------------+
| id | col_1 | col_2         |
+----+-------+---------------+
| 1  | true  | 12789.123     |
| 2  | false | 1243.5        |
| 3  | false | 24453.325     |
| 4  | false | 2423.3254     |
| 50 | true  | 243.325       |
| 60 | false | 243565423.325 |
| 70 | true  | 243.325       |
| 80 | false | 243423.325    |
| 90 | true  | 243.325       |
+----+-------+---------------+
Returned 9 row(s) in 0.44s
[192.168.56.121:21000] > SELECT * FROM tab2 LIMIT 5;
Query: select * FROM tab2 LIMIT 5
+----+-------+-----------+
| id | col_1 | col_2     |
+----+-------+-----------+
| 1  | true  | 12789.123 |
| 2  | false | 1243.5    |
| 3  | false | 24453.325 |
| 4  | false | 2423.3254 |
| 50 | true  | 243.325   |
+----+-------+-----------+
Returned 5 row(s) in 0.44s

带 OFFSET 语句查询

带 OFFSET 语句查询，需要和 order by 一起使用，起始编号从 0 开始往后偏移，offset 为 0 时，其结果和去掉 offset 的 limit 结果一致。

测试如下：

[192.168.56.121:21000] > SELECT * FROM tab2 order by id LIMIT 3 offset 0;
Query: select * FROM tab2 order by id LIMIT 3 offset 0
+----+-------+-----------+
| id | col_1 | col_2     |
+----+-------+-----------+
| 1  | true  | 12789.123 |
| 2  | false | 1243.5    |
| 3  | false | 24453.325 |
+----+-------+-----------+
Returned 3 row(s) in 0.45s
[192.168.56.121:21000] > SELECT * FROM tab2 order by id LIMIT 3 offset 2;
Query: select * FROM tab2 order by id LIMIT 3 offset 2
+----+-------+-----------+
| id | col_1 | col_2     |
+----+-------+-----------+
| 3  | false | 24453.325 |
| 4  | false | 2423.3254 |
| 50 | true  | 243.325   |
+----+-------+-----------+
Returned 3 row(s) in 0.45s

5. join 连接查询

5.1 左外连接：

[192.168.56.121:21000] > SELECT tab1.id,tab1.col_1,tab2.col_2 FROM tab1 LEFT OUTER JOIN tab2 USING (id);
+----+-------+-----------+
| id | col_1 | col_2     |
+----+-------+-----------+
| 1  | true  | 12789.123 |
| 2  | false | 1243.5    |
| 3  | false | 24453.325 |
| 4  | false | 2423.3254 |
| 5  | true  | NULL      |
+----+-------+-----------+
Returned 5 row(s) in 1.12s

以上 SQL 语句等同于下面语句，用法同样适用于多个字段：

SELECT tab1.id,tab1.col_1,tab2.col_2 FROM tab1 LEFT OUTER JOIN tab2 where tab1.id=tab2.id;

由上可以看到左边表 tab1 的记录都查询出来了，右边表 tab2 只查询出跟 tab1 关联的记录。

5.2 内连接

[192.168.56.121:21000] > SELECT tab1.id,tab1.col_1,tab2.col_2 FROM tab1 INNER JOIN tab2 USING (id);
+----+-------+-----------+
| id | col_1 | col_2     |
+----+-------+-----------+
| 1  | true  | 12789.123 |
| 2  | false | 1243.5    |
| 3  | false | 24453.325 |
| 4  | false | 2423.3254 |
+----+-------+-----------+
Returned 4 row(s) in 0.53s

以上语句可以修改为：

-- 下面语句都是内连接
SELECT tab1.id,tab1.col_1,tab2.col_2 FROM tab1 JOIN tab2 USING (id);
SELECT tab1.id,tab1.col_1,tab2.col_2 FROM tab1 , tab2 where tab1.id=tab2.id ;

查询结果为：

[192.168.56.121:21000] > SELECT tab1.id,tab1.col_1,tab2.col_2 FROM tab1 , tab2 where tab1.id=tab2.id ;
Query: select tab1.id,tab1.col_1,tab2.col_2 FROM tab1 , tab2 where tab1.id=tab2.id
+----+-------+-----------+
| id | col_1 | col_2     |
+----+-------+-----------+
| 1  | true  | 12789.123 |
| 2  | false | 1243.5    |
| 3  | false | 24453.325 |
| 4  | false | 2423.3254 |
+----+-------+-----------+
Returned 4 row(s) in 0.38s

如果去掉 where 语句，会提示错误：

[192.168.56.121:21000] > select tab1.id,tab1.col_1,tab2.col_2 FROM tab1 , tab2;
Query: select tab1.id,tab1.col_1,tab2.col_2 FROM tab1 , tab2
ERROR: NotImplementedException: Join with 'default.tab2' requires at least one conjunctive equality predicate. To perform a Cartesian product between two tables, use a CROSS JOIN.

5.3 自连接

impala 允许自连接，例如：

-- Combine fields from both parent and child rows.
SELECT lhs.id, rhs.parent, lhs.c1, rhs.c2 FROM tree_data lhs, tree_data rhs WHERE lhs.id = rhs.parent;

5.4 交叉连接

为了避免产生大量的结果集，impala 不允许下面形式的笛卡尔连接：

SELECT ... FROM t1 JOIN t2;
SELECT ... FROM t1, t2;

如果，你的确想使用笛卡尔连接，建议使用 cross join：

[192.168.56.121:21000] > select tab1.id,tab1.col_1,tab2.col_2 FROM tab1 CROSS JOIN tab2 where tab1.id<3;
Query: select tab1.id,tab1.col_1,tab2.col_2 FROM tab1 CROSS JOIN tab2 where tab1.id<3
+----+-------+---------------+
| id | col_1 | col_2         |
+----+-------+---------------+
| 1  | true  | 12789.123     |
| 1  | true  | 1243.5        |
| 1  | true  | 24453.325     |
| 1  | true  | 2423.3254     |
| 1  | true  | 243.325       |
| 1  | true  | 243565423.325 |
| 1  | true  | 243.325       |
| 1  | true  | 243423.325    |
| 1  | true  | 243.325       |
| 2  | false | 12789.123     |
| 2  | false | 1243.5        |
| 2  | false | 24453.325     |
| 2  | false | 2423.3254     |
| 2  | false | 243.325       |
| 2  | false | 243565423.325 |
| 2  | false | 243.325       |
| 2  | false | 243423.325    |
| 2  | false | 243.325       |
+----+-------+---------------+
Returned 18 row(s) in 0.41s

5.5 等值连接和非等值连接

默认地，impala的两表连接需要一个等值的比较，或者使用 ON、USING、WHERE 语句。在Impala 1.2.2 之后，非等值连接也支持。同样需要避免因为产生大量的结果集而造成内存溢出。一旦你想使用非等值连接，建议使用 cross 连接并增加额外的 where 语句。

[192.168.56.121:21000] > select tab1.id,tab1.col_1,tab1.col_2,tab2.col_2 FROM tab1 CROSS JOIN tab2 where tab1.col_2 >tab2.col_2 ;
+----+-------+------------+-----------+
| id | col_1 | col_2      | col_2     |
+----+-------+------------+-----------+
| 2  | false | 1243.5     | 243.325   |
| 2  | false | 1243.5     | 243.325   |
| 2  | false | 1243.5     | 243.325   |
| 3  | false | 24453.325  | 12789.123 |
| 3  | false | 24453.325  | 1243.5    |
| 3  | false | 24453.325  | 2423.3254 |
| 3  | false | 24453.325  | 243.325   |
| 3  | false | 24453.325  | 243.325   |
| 3  | false | 24453.325  | 243.325   |
| 4  | false | 243423.325 | 12789.123 |
| 4  | false | 243423.325 | 1243.5    |
| 4  | false | 243423.325 | 24453.325 |
| 4  | false | 243423.325 | 2423.3254 |
| 4  | false | 243423.325 | 243.325   |
| 4  | false | 243423.325 | 243.325   |
| 4  | false | 243423.325 | 243.325   |
+----+-------+------------+-----------+
Returned 16 row(s) in 0.41s

查询出来的结果会有一些重复的记录，这个时候可以通过 distinct 去重。

5.6 半连接

左半连接是为了实现 in 语句，左边的记录会查询出来，而不管右边表有多少匹配的记录。Impala 2.0版本之后，支持右半连接。

[192.168.56.121:21000] > SELECT tab1.id,tab1.col_1,tab2.col_2 FROM tab1 LEFT SEMI JOIN tab2 USING (id);
Query: select tab1.id,tab1.col_1,tab2.col_2 FROM tab1 LEFT SEMI JOIN tab2 USING (id)
+----+-------+-----------+
| id | col_1 | col_2     |
+----+-------+-----------+
| 1  | true  | 12789.123 |
| 2  | false | 1243.5    |
| 3  | false | 24453.325 |
| 4  | false | 2423.3254 |
+----+-------+-----------+
Returned 4 row(s) in 0.41s

5.7 自然连接（不支持）

Impala 不支持 NATURAL JOIN 操作，以避免产生不一致或者大量的结果。自然连接不适应 ON 和 USING 语句，而是自动的关联所有列相同值的记录。这种连接是不建议的，特别是当表结构发生变化的时候，如添加或者删除列的时候，会产生不一样的结果集。

-- 'NATURAL' is interpreted as an alias for 't1' and Impala attempts an inner join,
-- resulting in an error because inner joins require explicit comparisons between columns.
SELECT t1.c1, t2.c2 FROM t1 NATURAL JOIN t2;
ERROR: NotImplementedException: Join with 't2' requires at least one conjunctive equality predicate.
  To perform a Cartesian product between two tables, use a CROSS JOIN.

-- If you expect the tables to have identically named columns with matching values,
-- list the corresponding column names in a USING clause.
SELECT t1.c1, t2.c2 FROM t1 JOIN t2 USING (id, type_flag, name, address);

5.8 反连接（Impala 2.0 / CDH 5.2 以上版本）

Impala 2.0 / CDH 5.2 以上版本中支持反连接，包括左反连接和右反连接。左反连接的意思是返回左边表不在右边表中的记录。

找出 tab2 的 id 不在 tab1 中的记录：

[192.168.56.121:21000] > SELECT tab2.id FROM tab2 LEFT ANTI JOIN tab1 USING (id);
+----+
| id |
+----+
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
+----+
Returned 5 row(s) in 0.41s

6. 聚合查询

聚合关联查询：

[192.168.56.121:21000] > select tab1.col_1, MAX(tab2.col_2), MIN(tab2.col_2) FROM tab2 JOIN tab1 USING (id) GROUP BY col_1 ORDER BY 1 LIMIT 5 ;
+-------+-----------------+-----------------+
| col_1 | max(tab2.col_2) | min(tab2.col_2) |
+-------+-----------------+-----------------+
| false | 24453.325       | 1243.5          |
| true  | 12789.123       | 12789.123       |
+-------+-----------------+-----------------+

聚合关联子查询：

[192.168.56.121:21000] > select tab2.* FROM tab2, (SELECT tab1.col_1, MAX(tab2.col_2) AS max_col2 FROM tab2, tab1 WHERE tab1.id = tab2.id GROUP BY col_1) subquery1 WHERE subquery1.max_col2 = tab2.col_2 ;
+----+-------+-----------+
| id | col_1 | col_2     |
+----+-------+-----------+
| 1  | true  | 12789.123 |
| 3  | false | 24453.325 |
+----+-------+-----------+
Returned 2 row(s) in 0.54s

Impala 2版本中，支持where 条件子查询，包括 IN 、EXISTS 和比较符的子查询：

select tab2.* from tab2 where tab2.id IN (select max(id) from tab1)
select tab2.* from tab2 where tab2.id EXISTS (select max(id) from tab1)
select tab2.* from tab2 where tab2.id > (select max(id) from tab1)

插入查询：

[192.168.56.121:21000] > insert OVERWRITE TABLE tab3 SELECT id, col_1, col_2, MONTH(col_3), DAYOFMONTH(col_3) FROM tab1 WHERE YEAR(col_3) = 2012 ;
Inserted 2 rows in 0.44s

这时候查询 tab3 的记录：

[192.168.56.121:21000] > SELECT * FROM tab3;
+----+-------+---------+-------+-----+
| id | col_1 | col_2   | month | day |
+----+-------+---------+-------+-----+
| 1  | true  | 123.123 | 10    | 24  |
| 2  | false | 1243.5  | 10    | 25  |
+----+-------+---------+-------+-----+

Impala查询功能测试

1. 准备测试数据

2. 查看表结构

3. impala-shell 命令

4. 导入数据并查询

5. join 连接查询

5.1 左外连接：

5.2 内连接

5.3 自连接

5.4 交叉连接

5.5 等值连接和非等值连接

5.6 半连接

5.7 自然连接（不支持）

5.8 反连接（Impala 2.0 / CDH 5.2 以上版本）

6. 聚合查询

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Impala查询功能测试

1. 准备测试数据

2. 查看表结构

3. impala-shell 命令

4. 导入数据并查询

5. join 连接查询

5.1 左外连接：

5.2 内连接

5.3 自连接

5.4 交叉连接

5.5 等值连接和非等值连接

5.6 半连接

5.7 自然连接（不支持）

5.8 反连接（Impala 2.0 / CDH 5.2 以上版本）

6. 聚合查询

热门文章

最新文章

相关课程

相关电子书

相关实验场景