Impala查询功能测试

关于 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  |
+----+-------+---------+-------+-----+
时间： 2024-11-05 12:28:52

Impala查询功能测试的相关文章

cloudera impala如何获取执行任务数？

问题描述 在cm中看到hue做的impala查询,点击查询之后,日志显示任务进度,执行任务数/总任务数.不知该任务进度与总任务度如何获取.impala执行查询,求如何获取运行进度方法. 解决方案

Impala与HBase整合实践

我们知道,HBase是一个基于列的NoSQL数据库,它可以实现的数据的灵活存储.它本身是一个大表,在一些应用中,通过设计RowKey,可以实现对海量数据的快速存储和访问.但是,对于复杂的查询统计类需求,如果直接基于HBase API来实现,性能非常差,或者,可以通过实现MapReduce程序来进行查询分析,这也继承了MapReduce所具备的延迟性. 实现Impala与HBase整合,我们能够获得的好处有如下几个: 可以使用我们熟悉的SQL,像操作传统关系型数据库一样,很容易给出复杂查询.统计分

Impala简介（整理）

        一.定义(来自百度百科)         Impala是Cloudera公司主导开发的新型查询系统,它提供SQL语义,能查询存储在Hadoop的HDFS和HBase中的PB级大数据.已有的Hive系统虽然也提供了SQL语义,但由于Hive底层执行使用的是MapReduce引擎,仍然是一个批处理过程,难以满足查询的交互性.相比之下,Impala的最大特点也是最大卖点就是它的快速.         二.Impala组成         1.客户端:包括JDBC.ODBC.Hue.Im

当前数据仓库建设过程

一个典型的企业数据仓库通常包含数据采集.数据加工和存储.数据展现等几个过程,本篇文章将按照这个顺序记录部门当前建设数据仓库的过程. 1. 数据采集和存储 采集数据之前,先要定义数据如何存放在 hadoop 以及一些相关约束.约束如下: 所有的日志数据都存放在 hdfs 上的 /logroot 路径下面 hive 中数据库命名方式为 dw_XXXX,例如:dw_srclog 存放外部来源的原始数据,dw_stat 存放统计结果的数据 原始数据都加工成为结构化的文本文件,字段分隔符统一使用制表符,并

使用Amazon EMR和Tableau分析和可视化数据

引言 针对不同格式和大小的数据,Hadoop生态圈提供了丰富的工具进行分析并提取价值.最初,Hadoop生态圈专注于分析大批量数据,提供了类似MapReduce.Pig和Hive等组件.而当下,Hadoop已提供了大量用于交互式数据查询的工具,比如Impala.Drill和Presto.本篇文章将教会你如何使用Amazon Elastic MapReduce(Amazon EMR)来分析Amazon Simple Storage Service(Amazon S3)上存储的数据,并使用Table

基于Hadoop的数据仓库Hive基础知识

Hive是基于Hadoop的数据仓库工具,可对存储在HDFS上的文件中的数据集进行数据整理.特殊查询和分析处理,提供了类似于SQL语言的查询语言–HiveQL,可通过HQL语句实现简单的MR统计,Hive将HQL语句转换成MR任务进行执行. 一.概述 1-1 数据仓库概念 数据仓库(Data Warehouse)是一个面向主题的(Subject Oriented).集成的(Integrated).相对稳定的(Non-Volatile).反应历史变化(Time Variant)的数据集合,用于支持

Impala提升3～90倍查询效率的秘诀是什么？

&http://www.aliyun.com/zixun/aggregation/37954.html">nbsp; Cloudera Impala是一项基于Hadoop的实时检索引擎开源项目,其效率十分高,较Hive提升了有3-90倍. 为什么是代码生成? 这一切的基础是最优的查询,引擎一定是原生的应用,因为它们是针对你的数据格式来开发的,而且仅仅支持你的查询.例如以下是一个理想的代码: 1 2 3 select count(*) from tbl where col like

Cloudera Impala：基于Hadoop的实时查询开源项目

正在纽约进行的大数据技术会议Strata Conference + Hadoop World传来消息,Cloudera发布了实时查询开源项目Impala 1.0 beta版,称比原来基于MapReduce的Hive SQL查询速度提升3-90倍(详情可以参考此文中的"How much faster are Impala queries than Hive ones, really?"部分),而且更加灵活易用.Impala是高角羚的意思,这种羚羊主要分布在东非. 同时,这个项目也将以Cl

Impala与Hive的对比分析

1. Impala架构 Impala是Cloudera在受到Google的Dremel启发下开发的实时交互SQL大数据查询工具,Impala没有再使用缓慢的 Hive+MapReduce批处理,而是通过使用与商用并行关系数据库中类似的分布式查询引擎(由Query Planner.Query Coordinator和Query Exec Engine三部分组成),可以直接从HDFS或HBase中用SELECT.JOIN和统计函数查询数据,从而大大降低了延迟.其架构如图 1所示,Impala主要由I