Impala show tables

关于 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  |
+----+-------+---------+-------+-----+