摘要:本篇教程探讨了大数据技术 Impala SQL语言元素,希望阅读本篇文章以后大家有所收获,帮助大家对大数据技术的理解更加深入。
本篇教程探讨了大数据技术 Impala SQL语言元素,希望阅读本篇文章以后大家有所收获,帮助大家对大数据技术的理解更加深入。
<
Impala SQL 语言元素(Elements)
Impala SQL 方言支持一组标准元素(a range of standard elements),加上许多大数据方面的扩展,用于数据加载和数据仓库方面。
注意:
在之前的 Impala beta 版中,在 impala-shell 中每一语句结束时的分号是可选的。现在 impala-shell 支持多行命令,以便于从脚本文件中复制粘贴代码,这样每一语句结束时的分号是必需的。
下面章节演示了 Impala 中 SQL 语言的主要语句、子句以及其他元素。
继续阅读:
ALTER TABLE Statement
ALTER VIEW Statement
AVG Function
BETWEEN Operator
BIGINT Data Type
BOOLEAN Data Type
Comments
Comparison Operators
COMPUTE STATS Statement
COUNT Function
CREATE DATABASE Statement
CREATE FUNCTION Statement
CREATE TABLE Statement
CREATE VIEW Statement
DESCRIBE Statement
DISTINCT Operator
DOUBLE Data Type
DROP DATABASE Statement
DROP FUNCTION Statement
DROP TABLE Statement
DROP VIEW Statement
EXPLAIN Statement
External Tables
FLOAT Data Type
GROUP BY Clause
HAVING Clause
Hints
INSERT Statement
INT Data Type
Internal Tables
INVALIDATE METADATA Statement
Joins
LIKE Operator
LIMIT Clause
LOAD DATA Statement
MAX Function
MIN Function
NDV Function
NULL
OFFSET Clause
ORDER BY Clause
REFRESH Statement
REGEXP Operator
RLIKE Operator
SELECT Statement
SHOW Statement
SMALLINT Data Type
STRING Data Type
SUM Function
TIMESTAMP Data Type
TINYINT Data Type
UNION Clause
USE Statement
VALUES Clause
Views
WITH Clause
ALTER TABLE 语句
ALTER TABLE 语句用来修改现有表的结构或属性。在 Impala 里,这是一个逻辑操作,更新了 Impala 和 Hive 共用的 metastore 数据库中表的元数据; ALTER TABLE 语句不会对实际的数据文件进行重写、移动等操作。因此,你可能需要相应的物理文件系统操作才能实现移动数据文件到不同的 HDFS 目录,重写数据文件来包含其他字段,或转换成不同的文件格式。
重命名表:
ALTER TABLE old_name RENAME TO new_name;
对于内部表,这一操作实际地修改了包含数据文件的 HDFS 目录名;原始目录将不再存在。通过修改表名前面的数据库名,你可以把一个数据库中的内部表(包括对应的数据目录)移动到另一个数据库。例如:
create database d1;
create database d2;
create database d3;
use d1;
create table mobile (x int);
use d2;
-- 移动其他数据库中的表到当前数据库
alter table d1.mobile rename to mobile;
use d1;
-- 移动一个数据库中的表达哦另一个数据库
alter table d2.mobile rename to d3.mobile;
修改 Impala 查找表的相关数据文件的物理位置:
ALTER TABLE table_name SET LOCATION 'hdfs_path_of_directory';
指定的路径是数据文件所在的完整路径,或者是不存在被创建的路径。Impala 不会创建该表名下面的额外子目录。Impala 不会移动任意数据文件到新位置,也不会修改这一目录下现存的数据文件。
修改 TBLPROPERTIES 和 SERDEPROPERTIES 列的键值对:
?12ALTER TABLE table_name SET TBLPROPERTIES ('key1'='value1', 'key2'='value2', ...);ALTER TABLE table_name SET SERDEPROPERTIES ('key1'='value1', 'key2'='value2', ...);
TBLPROPERTIES 子句是将任意用户指定数据项与特定表关联起来的主要方法(The TBLPROPERTIES clause is primarily a way to associate arbitrary user-specified data items with a particular table)。SERDEPROPERTIES 子句设置表如何读写,在 Hive 中许多情况下需要,实际在 Impala 中未使用(The SERDEPROPERTIES clause sets up metadata defining how tables are read or written, needed in some cases by Hive but not actually used by Impala)。参考 CREATE TABLE Statement 了解这些子句的详细信息。参考 Setting Statistics Manually through ALTER TABLE 中的使用表属性来微调相关表统计信息的性能的例子(for an example of using table properties to fine-tune the performance-related table statistics)。
重组表中的列:
ALTER TABLE table_name ADD COLUMNS (column_defs);
ALTER TABLE table_name REPLACE COLUMNS (column_defs);
ALTER TABLE table_name CHANGE column_name new_name new_spec;
ALTER TABLE table_name DROP column_name;
其中 column_spec 与 CREATE TABLE 语句中相同:列名,列数据类型,以及可选的列备注。你可以一次添加多个列。无论是添加单个列还是多个列,都需要用括号括起来。当替换列时,原有列的定义都被废弃。你可能会在收到一组新的有不同数据类型或不同顺序的列的数据文件时使用这一技术(数据文件会被保留,因此当新列与旧列不兼容时,需要在执行进一步的查询前,使用 INSERT OVERWRITE 或 LOAD DATA OVERWRITE 语句替换所有的数据)。
你可以使用 CHANGE 子句重命名某一个列,或转换现存的列为其他类型,例如在 STRING 和 TIMESTAMP 之间转换,或者在 INT 与 BIGINT 之间转换。一次只能删除一个列;想要删除多个列,需要执行多次 ALTER TABLE 语句,或者在单个 ALTER TABLE ... REPLACE COLUMNS 语句中定义一组新的列。
修改 Impala 中表期望的文件格式(To change the file format that Impala expects table data to be in):
ALTER TABLE table_name SET FILEFORMAT { PARQUET | PARQUETFILE | TEXTFILE | RCFILE | SEQUENCEFILE }
因为本操作只是修改表的元数据,对现存的数据,你必须使用 Impala 之外的 Hadoop 技术对已有的数据进行转换。之后再在 Impala 中使用 INSERT 语句创建的数据将使用新的格式。你不能指定文本文件的分隔符;文本文件的分隔符必须是逗号。
为了添加或删除表的分区, 表必须已经是分区表(也就是说,使用带 PARTITIONED BY 子句创建的表)。分区是一个 HDFS 中的实际目录,目录名包含特定列的值(partition key,分区键)。假如必要,Impala 的 INSERT 语句会创建分区,因此 ALTER TABLE ... ADD PARTITION 语句的主要用途是通过移动或复制已有的数据文件到分区对应的 HDFS 目录来导入数据。DROP PARTITION 子句用于删除一组指定分区键值对应的 HDFS 目录和对应的数据文件;例如,假如你总是分析最近 3 个月数据的价值,在每个月初你就可以删除掉不再需要的最老的那个分区的数据。删除分区会减少表相关的元数据数量,减轻计算查询计划的复杂度,从而可以简化和提升分区表的查询速度,特别是连接查询。下面展示了 ADD PARTITION 和 DROP PARTITION 子句。
-- 创建一个空的分区模式的表
create table part_t (x int) partitioned by (month string);
-- 创建一个空分区,下面将从其他源复制数据文件到这个分区
alter table part_t add partition (month='January');
-- 变更相关数据后,执行 REFRESH 语句使得数据对 Impala 可见
refresh part_t;
-- 然后,添加下一月份
alter table part_t add partition (month='February');
-- 现在不再需要一月份数据
alter table part_t drop partition (month='January');
-- 假如表是根据月份、年份分区,执行类似语句:
-- alter table part_t drop partition (year=2003,month='January');
-- 这将需要 12 个 ALTER TABLE 语句来删除 2003 整年的数据
分区键的值可以是任意常数表达式,不需要引用标中的列(The value specified for a partition key can be an arbitrary constant expression, without any references to columns).例如:
alter table time_data add partition (month=concat('Decem','ber'));
alter table sales_data add partition (zipcode = cast(9021 * 10 as string));
使用注意:
在 ALTER TABLE 语句中,必须包括所有的分区列(Whenever you specify partitions in an ALTER TABLE statement, you must include all the partitioning columns in the specification)。
对于内部表(Impala 管理表)和外部表(数据文件在任意位置)来说,之前的绝大多数操作是一致的。唯一的列外是重命名表;对外部表来说,相关的数据目录不会被重命名或移动。
假如为了负载均衡的原因,在 impala-shell 会话中连接到了不同的 Impala 节点,可以启用 SYNC_DDL 查询选项以使得每一 DDL 语句在新的或修改的元数据被所有 Impala 节点接受前等待,直到都被接受后才返回。参考 SYNC_DDL 了解详细信息。
注意:
重组表和其关联的数据文件的另一种方法是使用 CREATE TABLE 语句创建一个与原始表不同的表,然后使用 INSERT 语句复制转换或重新排序的数据到新表。ALTER TABLE 的优势是避免了数据文件的重复复制,允许你使用熟悉的 Hadoop 技术以一种节省空间的方式来重组巨大容量的数据。
语句类型: DDL
ALTER VIEW 语句
修改视图里的查询,或相关的数据库和/或视图的名称。
因为视图是一种纯逻辑结构(一个查询的别名)没有实际的数据,ALTER VIEW 只执行 metastore 数据库中元数据的修改,不涉及 HDFS 中的任意数据文件。
ALTER VIEW [database_name.]view_name AS select_statement
ALTER VIEW [database_name.]view_name RENAME TO [database_name.]view_name
假如为了负载均衡的原因,在 impala-shell 会话中连接到了不同的 Impala 节点,可以启用 SYNC_DDL 查询选项以使得每一 DDL 语句在新的或修改的元数据被所有 Impala 节点接受前等待,直到都被接受后才返回。参考 SYNC_DDL 了解详细信息。
例子:
create table t1 (x int, y int, s string);
create table t2 like t1;
create view v1 as select * from t1;
alter view v1 as select * from t2;
alter view v1 as select x, upper(s) s from t2;
执行 DESCRIBE FORMATTED 语句来查看视图的定义,这将显示原始 CREATE VIEW 中的查询:
[localhost:21000] > create view v1 as select * from t1;
[localhost:21000] > describe formatted v1;
Query finished, fetching results ...
+------------------------------+------------------------------+----------------------+
| name | type | comment |
+------------------------------+------------------------------+----------------------+
| # col_name | data_type | comment |
| | NULL | NULL |
| x | int | None |
| y | int | None |
| s | string | None |
| | NULL | NULL |
| # Detailed Table Information | NULL | NULL |
| Database: | views | NULL |
| Owner: | cloudera | NULL |
| CreateTime: | Mon Jul 08 15:56:27 EDT 2013 | NULL |
| LastAccessTime: | UNKNOWN | NULL |
| Protect Mode: | None | NULL |
| Retention: | 0 | NULL |
| Table Type: | VIRTUAL_VIEW | NULL |
| Table Parameters: | NULL | NULL |
| | transient_lastDdlTime | 1373313387 |
| | NULL | NULL |
| # Storage Information | NULL | NULL |
| SerDe Library: | null | NULL |
| InputFormat: | null | NULL |
| OutputFormat: | null | NULL |
| Compressed: | No | NULL |
| Num Buckets: | 0 | NULL |
| Bucket Columns: | [] | NULL |
| Sort Columns: | [] | NULL |
| | NULL | NULL |
| # View Information | NULL | NULL |
| View Original Text: | SELECT * FROM t1 | NULL | | View Expanded Text: | SELECT * FROM t1 | NULL |
+------------------------------+------------------------------+----------------------+
Returned 29 row(s) in 0.05s
语句类型: DDL
AVG 函数
返回一组数字的均值的聚合函数。它唯一的参数是一个数值列、或者基于列返回数值的函数或表达式(Its single argument can be numeric column, or the numeric result of a function or expression applied to the column value)。指定列中值为 NULL 的行将被忽略。假如表为空,或者输入 AVG 的值都是 NULL,则 AVG 返回 NULL。
当查询包含 GROUP BY 子句,返回每一个分组组合的一个值。
返回类型: DOUBLE
例子:
-- 计算所有非空行的均值
insert overwrite avg_t values (2),(4),(6),(null),(null);
-- 上面表中的均值是 4: (2+4+6) / 3. 两个 NULL 值被忽略
select avg(x) from avg_t;
-- 计算特定行的均值(Average only certain values from the column)
select avg(x) from t1 where month = 'January' and year = '2013';
-- 在计算均值前进行计算
select avg(x/3) from t1;
-- 在计算均值前对列进行函数运算
-- 这里把所有 NULL 的行替换为 0
-- 这样值为 NULL 的行也会作为均值计算的因子
select avg(isnull(x,0)) from t1;
-- 对 string 列使用某些返回值为数字的函数,然后计算均值
-- 假如某行 s 的值包含 NULL,则 length(s) 函数也返回 NULL,该行被忽略
select avg(length(s)) from t1;
-- 也可以与 DISTINCT 和/或 GROUP BY 组合使用
-- 返回多于一个的结果
select month, year, avg(page_visits) from web_stats group by month, year;
-- 在执行计算前过滤重复的值
select avg(distinct x) from t1;
-- 执行计算后过滤输出的值
select avg(x) from t1 group by y having avg(x) between 1 and 20;
BETWEEN 操作符
在 WHERE 子句中,将表达式与下限和上限比较。当表达式大于等于下限,并且小于等于上限,则表达式比较成功。假如上限下限互换,也就是说下限大于上限,那么就不匹配任何值。
语法: expression BETWEEN lower_bound AND upper_bound
数据类型: 通常使用数字类型。适用于任何类型但不是很实用的 BOOLEAN(Works with any data type, although not very practical for BOOLEAN values)。 (BETWEEN false AND true 会匹配所有的 BOOLEAN 值)。必要时使用 CAST() 函数来确保下限和上限值是兼容的数据类型。假如必要的时候调用 string 或 date/time 函数来提取或转换相关的比较部分,特别是值可以转换成数字的时候。
使用注意:使用短字符串操作数时要当心(Be careful when using short string operands)。以上限的字符串开始的长字符串将不被包含,因为它被认为是大于上限(A longer string that starts with the upper bound value will not be included, because it is considered greater than the upper bound)。例如,BETWEEN 'A' and 'M' 将不会匹配字符串 'Midway'。假如必要,使用例如 upper(), lower(), substr(), trim(), 等等函数以确保比较如预期执行。
例子:
-- 返回1到6月的值,包括1跟6月.
select c1 from t1 where month between 1 and 6;
-- 返回以'A' 到 'M' 开头的名字
-- 只检测第一个字符以确保所有以 'M' 开头的名称符合
-- 进行大小写敏感的比较以配合不同大小写约定的名称(Do a case-insensitive comparison to match names with various capitalization conventions)
select last_name from customers where upper(substr(last_name,1,1)) between 'A' and 'M';
-- 返回每个月第一周的数据
select count(distinct visitor_id)) from web_traffic where dayofmonth(when_viewed) between 1 and 7;
BIGINT 数据类型
8字节的整数类型,用于 CREATE TABLE 和 ALTER TABLE 语句。
范围: -9223372036854775808 .. 9223372036854775807。没有无符号子类型。
转换: Impala 自动转换为浮点类型(FLOAT or DOUBLE)。 使用 CAST() 函数转换成 TINYINT, SMALLINT, INT, STRING, or TIMESTAMP。数值 N 转换成 TIMESTAMP 时,是转换成从 1970年1月1日开始的 N 秒。
相关信息: INT Data Type, SMALLINT Data Type, TINYINT Data Type, Mathematical Functions
BOOLEAN 数据类型
用于 CREATE TABLE 和 ALTER TABLE 语句的数据类型,表示一个单一的 true/false 的选择。
范围: TRUE or FALSE。不要使用引号引起 TRUE 和 FALSE 的字符值。你可以使用大写、小写或混合格式的值。表中返回的值都是小写的 true 或 false。
转换: Impala 不会自动转换其他类型为 BOOLEAN。可以使用 CAST() 转换任意 integer 或 float-point 类型为 BOOLEAN: 0 表示 false,其他非零值转化为 true。STRING 不能转换为 BOOLEAN,尽管 BOOLEAN 可以转换为 STRING,其中 true 对应 '1' 而 false 对应 '0'。
相关信息: Conditional Functions
注释
Impala 支持大家熟悉的 SQL 注释风格:
从 -- 开始的到行尾都被作为注释而忽略。这种类型的注释可以单独出现在一行上,或者所有或部分语句之后
从 /* 开始到下一个 */ 结束的文字都被作为注释而忽略。这种类型的注释可以跨越多行。这种类型的注释可以在语句中或者语句之前、之后出现在一行或多行
例如:
-- 本行是表的注释
create table ...;
/*
本还是查询的多行注释
*/
select ...;
select * from t /* 这是查询的嵌入式注释 */ where ...;
select * from t -- 这是多行命令中的尾部注释
where ...;
比较操作
Impala 支持大家熟悉的比较操作用于检测相等、存在并为列数据类型排序:
=, !=, <>
IS NULL, IS NOT NULL
<, <=,>, >=
BETWEEN lower_bound AND upper_bound
LIKE, REGEXP (仅支持STRING)
COMPUTE STATS 语句
采集关于表和相关列与分区中数据的数据量和分布(Gathers information about volume and distribution of data in a table and all associated columns and partitions)。这些信息被存放在 metastore 数据库中,被 Impala 用于帮助优化查询。假设 Impala 可以判断表的大小,有许多或几个的不同的值,那么它可以为连接查询或插入操作组织适当的并行操作。了解这一语句采集的几种信息,参见 Table Statistics。
使用注意:
原来 Impala 依靠用户运行 Hive ANALYZE TABLE 语句,但这一方法采集的统计信息被证明是缓慢和不可靠的。Impala 的 COMPUTE STATS 语句是从底层向上构建,以提高可用性和用户友好度。你可以运行一个单独的 Impala COMPUTE STATS 语句来采集包括 table 和 column 的统计信息,而不是为表和列的统计信息分别运行 Hive ANALYZE TABLE 语句。
COMPUTE STATS 也可以采集 HBase 表的信息。采集的 HBase 表的统计信息与 HDFS-backed 表的有所不同,但当 HBase 表执行连接查询时,统计信息仍被用于优化。
相关信息参见 SHOW Statement, Table Statistics, and Column Statistics。
例子:
本例中展示了 T1 和 T2 两个表,其中 T1.ID 和 T2.PARENT 存在父子关系,有少量的不同的值链接。T1 是小表,而 T2 大概有 100K 行。最初,统计信息包括物理度量如文件的数量,总大小,以及定长列如 INT 类型的大小度量。未知值表示为 -1。为每个表运行 COMPUTE STATS 之后,SHOW STATS 语句中有更多信息可用。假如你运行一个涉及这两个表的连接查询,你需要统计这两个表以获得最优化的查询。
[localhost:21000] > show table stats t1;
Query: show table stats t1
+-------+--------+------+--------+
| #Rows | #Files | Size | Format |
+-------+--------+------+--------+
| -1 | 1 | 33B | TEXT |
+-------+--------+------+--------+
Returned 1 row(s) in 0.02s
[localhost:21000] > show table stats t2;
Query: show table stats t2
+-------+--------+----------+--------+
| #Rows | #Files | Size | Format |
+-------+--------+----------+--------+
| -1 | 28 | 960.00KB | TEXT |
+-------+--------+----------+--------+
Returned 1 row(s) in 0.01s
[localhost:21000] > show column stats t1;
Query: show column stats t1
+--------+--------+------------------+--------+----------+----------+
| Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size |
+--------+--------+------------------+--------+----------+----------+
| id | INT | -1 | -1 | 4 | 4 |
| s | STRING | -1 | -1 | -1 | -1 |
+--------+--------+------------------+--------+----------+----------+
Returned 2 row(s) in 1.71s
[localhost:21000] > show column stats t2;
Query: show column stats t2
+--------+--------+------------------+--------+----------+----------+
| Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size |
+--------+--------+------------------+--------+----------+----------+
| parent | INT | -1 | -1 | 4 | 4 |
| s | STRING | -1 | -1 | -1 | -1 |
+--------+--------+------------------+--------+----------+----------+
Returned 2 row(s) in 0.01s
[localhost:21000] > compute stats t1;
Query: compute stats t1
+-----------------------------------------+
| summary |
+-----------------------------------------+
| Updated 1 partition(s) and 2 column(s). |
+-----------------------------------------+
Returned 1 row(s) in 5.30s
[localhost:21000] > show table stats t1;
Query: show table stats t1
+-------+--------+------+--------+
| #Rows | #Files | Size | Format |
+-------+--------+------+--------+
| 3 | 1 | 33B | TEXT |
+-------+--------+------+--------+
Returned 1 row(s) in 0.01s
[localhost:21000] > show column stats t1;
Query: show column stats t1
+--------+--------+------------------+--------+----------+----------+
| Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size |
+--------+--------+------------------+--------+----------+----------+
| id | INT | 3 | 0 | 4 | 4 |
| s | STRING | 3 | 0 | -1 | -1 |
+--------+--------+------------------+--------+----------+----------+
Returned 2 row(s) in 0.02s
[localhost:21000] > compute stats t2;
Query: compute stats t2
+-----------------------------------------+
| summary |
+-----------------------------------------+
| Updated 1 partition(s) and 2 column(s). |
+-----------------------------------------+
Returned 1 row(s) in 5.70s
[localhost:21000] > show table stats t2;
Query: show table stats t2
+-------+--------+----------+--------+
| #Rows | #Files | Size | Format |
+-------+--------+----------+--------+
| 98304 | 1 | 960.00KB | TEXT |
+-------+--------+----------+--------+
Returned 1 row(s) in 0.03s
[localhost:21000] > show column stats t2;
Query: show column stats t2
+--------+--------+------------------+--------+----------+----------+
| Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size |
+--------+--------+------------------+--------+----------+----------+
| parent | INT | 3 | 0 | 4 | 4 |
| s | STRING | 6 | 0 | -1 | -1 |
+--------+--------+------------------+--------+----------+----------+
Returned 2 row(s) in 0.01s
COUNT 函数
返回满足一定条件记录的行数或非空行的行数的聚合函数:
COUNT(*) 统计包含 NULL 值的所有行数
COUNT(column_name) 只计算该列中值非空的行数
可以结合使用 COUNT 与 DISTINCT 在计算前消除重复值,并计算多个列组合的值
当查询中包含 GROUP BY 子句时,we
Return type: BIGINT
Examples:
-- 表中有多少行,不关心是否有 NULL 值
select count(*) from t1;
-- 表中有多少 c1 列的值不为空的行
select count(c1) from t1;
-- 计算满足条件的行数
-- 另外, * 包括 NULL, 因此 COUNT(*) 可能比 COUNT(col) 的值大.
select count(*) from t1 where x > 10;
select count(c1) from t1 where x > 10;
-- 可以与 DISTINCT 和/或 GROUP BY 操作联合使用
-- 联合使用 COUNT 和 DISTINCT 查找唯一值的个数
-- 在 COUNT(DISTINCT ...) 语法中必须使用列名而不是 *
-- c1 包含空值的行不会统计
select count(distinct c1) from t1;
-- c1 或 c2 中包含空值的每一行都不会统计(Rows with a NULL value in _either_ column are not counted)
select count(distinct c1, c2) from t1;
-- 返回多个结果
select month, year, count(distinct visitor_id) from web_stats group by month, year;
CREATE DATABASE 语句
在 Impala 里,数据库是:
逻辑结构,包含在自己命名空间下组合在一起的相关的表(A logical construct for grouping together related tables within their own namespace)。你可以每个应用、一组相关的表或一轮实验都使用单独的数据库(You might use a separate database for each application, set of related tables, or round of experimentation)
物理结构,对应 HDFS 中的目录树(A physical construct represented by a directory tree in HDFS)。表(内部表),分区,和数据文件都在该目录下分配。你可以备份、计算空间使用情况、或使用 DROP DATABASE 语句删除它(目录为空时)
创建数据的语法如下:
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS]database_name[COMMENT 'database_comment']
[LOCATION hdfs_path];
数据库实际对应 HDFS 中 Impala 数据目录中的目录,目录名为数据库名+.db。假如 HDFS 中相关目录不存在则自动创建。所有数据库和它们相关的目录是顶层对象,没有逻辑或物理嵌套(All databases and their associated directories are top-level objects, with no physical or logical nesting)。
使用注意:
当创建数据库之后,在 impala-shell 会话中,使用 USE 语句切换为当前数据库。你可以不加数据库名前缀访问当前数据库中的表。
当第一次使用 impala-shell 连接到 Impala,默认的开始数据库是 (在执行任意的 CREATE DATABASE 或 USE 语句前) default。
当创建数据库之后,你的 impala-shell 会话或其他的连接到相同节点的 impala-shell 会话可以立即访问该数据库。当通过其他节点的 Impala 守护进程访问该数据库时,应先执行 INVALIDATE METADATA 语句
假如为了负载均衡的原因,在 impala-shell 会话中连接到了不同的 Impala 节点,可以启用 SYNC_DDL 查询选项以使得每一 DDL 语句在新的或修改的元数据被所有 Impala 节点接受前等待,直到都被接受后才返回。参考 SYNC_DDL 了解详细信息。
例子:
create database first;
use first;
create table t1 (x int);
create database second;
use second;
-- 数据库中的表以数据库名作为它的命名空间
-- 不同的数据库中可以有同名的表
create table t1 (s string);
create database temp;
-- 创建数据库后没有使用 USE 语句切换数据库,而是通过数据库名前缀来标识表
create table temp.t2 (x int, y int);
use database temp;
create table t3 (s string);
-- 当数据库被 USE 语句选中时,无法删除
drop database temp; ERROR: AnalysisException: Cannot drop current default database: temp -- The always-available database 'default' is a convenient one to USE.
use default;
-- 删除数据库可以快速删除下面的所有表
drop database temp;
语句类型: DDL
CREATE FUNCTION 语句
创建一个用户定义函数(UDF),当执行 SELECT 或 INSERT 操作时,可以实现自定义的逻辑。
语法:
创建标量函数(scalar UDF)与聚合函数(UDA)的语法不同。标量函数每行调用一次,执行单个函数(which is called once for each row and implemented by a single function),而用户定义聚合函数执行多个函数跨越多组行的中间结果(which is implemented by multiple functions that compute intermediate results across sets of rows)。
执行 CREATE FUNCTION 语句创建标量函数:
CREATE FUNCTION [IF NOT EXISTS] [db_name.]function_name([arg_type[,arg_type...])
RETURNS return_type LOCATION 'hdfs_path'
SYMBOL='symbol_or_class'
执行 CREATE AGGREGATE FUNCTION 语句创建 UDA:
CREATE [AGGREGATE] FUNCTION [IF NOT EXISTS] [db_name.]function_name([arg_type[,arg_type...])
RETURNSreturn_typeLOCATION 'hdfs_path'
[INIT_FN='function]
UPDATE_FN='function MERGE_FN='function [FINALIZE_FN='function]
标量与聚合函数:
最简单的一种用户定义函数每次调用时返回一个标量值,典型的是结果集中每一行返回一个值。这种普通的函数通常称为 UDF。用户定义聚合函数(UDA) 是一种特别的基于多行的内容产生单一值的 UDF。通常 UDA 会与 GROUP BY子句联合使用,压缩大的结果集到一个小的结果集,甚至对整表产生一个概述列(This general kind of function is what is usually meant by UDF. User-defined aggregate functions (UDAs) are a specialized kind of UDF that produce a single value based on the contents of multiple rows. You usually use UDAs in combination with a GROUP BY clause to condense a large result set into a smaller one, or even a single row summarizing column values across an entire table)。
使用 CREATE AGGREGATE FUNCTION 语句创建 UDA。 仅当创建 UDA 而不是 标量 UDF 时,INIT_FN, UPDATE_FN, MERGE_FN, FINALIZE_FN, INTERMEDIATE 子句被使用。
使用 *_FN 子句指定的函数在函数处理的不同阶段进行调用。
Initialize: 在 INIT_FN 子句中指定的函数完成初始化设置,例如初始化内部数据结果的成员变量。This function is often a stub for simple UDAs. 你可以忽略这一子句则会执行一个默认(无操作)函数操作。
Update: 在 UPDATE_FN 子句中指定的函数会在原始结果集的每一行调用一次,也就是说,在执行 GROUP BY 子句之前被执行。 对于 GROUP BY 子句返回的每一个不同的值,会调用一个单独的函数实例(A separate instance of the function is called for each different value returned by the GROUP BY clause)。 The final argument passed to this function is a pointer, to which you write an updated value based on its original value and the value of the first argument.
Merge: 在 MERGE_FN 子句中指定的函数被调用任意次数,与不同节点或不同线程的 Impala 并行读取和处理数据文件产生的中间结果有关。The final argument passed to this function is a pointer, to which you write an updated value based on its original value and the value of the first argument.
Finalize: 在 FINALIZE_FN 子句执行释放之前的 UDF 申请的资源,例如释放内存,关闭之前打开的文件句柄,诸如此类。This function is often a stub for simple UDAs. 你可以忽略这一子句则会执行一个默认(无操作)函数操作。
假如你对每一个相关的函数使用一致的命名约定,Impala 基于最初的子句可以自动的确定函数名称,因此其余的是可选的(If you use a consistent naming convention for each of the underlying functions, Impala can automatically determine the names based on the first such clause, so the others are optional)。
关于 UAD 的点对点(end-to-end)的例子,参见 User-Defined Functions for Impala.
使用注意:
你可以使用 C++ 或 JAVA 编写 Impala UDF。对于 Impala 来说,C++ UDFs 是新的、推荐的格式,可以利用本地代码提供更高性能。Impala 和 Hive 都兼容基于 JAVA(Java-based) 的 UDFs,最适合重用现存的 Hive UDFs (Impala 可以运行基于 Java 的 Hive UDFs 但不支持 Hive UDAs)
UDF 对应一个 .so 或 .jar 文件,存放在 HDFS 中,并由 CREATE FUNCTION 语句分发到每一个 Impala 节点
当 SQL 语句执行时,Impala 根据处理结果集中所有行的需要调用相关的代码。所有的 UDF 被分为是确定的,也就是说,当传入相同的参数值时总是返回相同的结果。当从之前的调用中结果值已知,Impala 可能跳过也可能不跳过 UDF 的某些调用。因此,不要依赖于 UDF 特定的调用次数,也不要基于一些外部因素如当前时间、随机数函数或当 Impala 执行查询过程中值会发生变化的外部数据源而返回不同的结果(Impala calls the underlying code during SQL statement evaluation, as many times as needed to process all the rows from the result set. All UDFs are assumed to be deterministic, that is, to always return the same result when passed the same argument values. Impala might or might not skip some invocations of a UDF if the result value is already known from a previous call. Therefore, do not rely on the UDF being called a specific number of times, and do not return different result values based on some external factor such as the current time, a random number function, or an external data source that could be updated while an Impala query is in progress)
UDF 函数中的参数名不重要,重要的是它们的数量、位置和数据类型
你可以通过创建多版本的使用不同参数签名的函数来重载相同的函数名,但基于安全的原因,不允许创建与内部函数重名的 UDF 函数
在 UDF 代码里,函数返回一个 struct。其中 struct 包含两个字段.。第一个字段是 boolean 类型,表示返回值是否为 NULL(当本字段返回 true 时,返回值被解释为 NULL)。第二个字段与函数的返回类型数据类型相同,当函数返回值不为 NULL 时持有返回值
In the UDF code, you represent the function arguments as an initial pointer to a UDF context structure, followed by references to zero or more structs, corresponding to each of the arguments. Each struct has the same 2 fields as with the return value, a boolean field representing whether the argument is NULL, and a field of the appropriate type holding any non-NULL argument value.
关于 UDF 的例子代码和编译说明,参考 Impala 提供的例子目录
因为 对于 UDF 函数主体的文件存放在 HDFS中,自动对所有的 Impala 节点可用。你不需要在服务器之间手工复制 UDF 相关文件
因为 Impala 目前不支持 ALTER FUNCTION 语句,假如你需要重命名函数,移动到其他数据库,或者修改它的前面或其他属性,应先对原函数执行 DROP FUNCTION 语句进行删除,然后执行以期望的属性执行 CREATE FUNCTION 语句创建函数
因为每一个 UDF 与特定的数据库相关,因此在执行任何 CREATE FUNCTION 语句之前,应先执行 USE 语句指定数据库,或者使用 db_name.function_name 来指定数据库
假如为了负载均衡的原因,在 impala-shell 会话中连接到了不同的 Impala 节点,可以启用 SYNC_DDL 查询选项以使得每一 DDL 语句在新的或修改的元数据被所有 Impala 节点接受前等待,直到都被接受后才返回。参考 SYNC_DDL 了解详细信息。
语句类型: DDL
兼容性:
Impala 可以运行 Hive 中创建的 UDF,只要它使用 Impala-compatible 数据类型 (没有组合或嵌套列类型)。Hive 可以运行 Impala 中创建的基于 JAVA(Java-based)的 UDF,而不能使用 C++编写的 UDF。
更多信息: 参见 User-Defined Functions for Impala 了解更多 Impala 中 UDF 的背景信息,使用介绍和例子。
CREATE TABLE 语句
创建表并指定它的列的语法如下:
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name[(col_namedata_type[COMMENT 'col_comment'], ...)]
[COMMENT 'table_comment']
[PARTITIONED BY (col_namedata_type[COMMENT 'col_comment'], ...)]
[
[ROW FORMATrow_format] [STORED ASfile_format]
]
[LOCATION 'hdfs_path']
[WITH SERDEPROPERTIES ('key1'='value1', 'key2'='value2', ...)]
[TBLPROPERTIES ('key1'='value1', 'key2'='value2', ...)]
data_type
:primitive_typeprimitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| STRING
| TIMESTAMP
row_format
: DELIMITED [FIELDS TERMINATED BY 'char' [ESCAPED BY 'char']]
[LINES TERMINATED BY 'char']
file_format:
PARQUET | PARQUETFILE
| TEXTFILE
| SEQUENCEFILE
| RCFILE
内部表和外部表:
Impala 默认创建 "内部" 表--由 Impala 管理表相关的数据文件,当表删除时同时实际删除数据文件。假如使用了 EXTERNAL 子句,Impala 将创建 "外部" 表--通常由 Impala 外部产生数据文件,并从它们原来的 HDFS 中的位置进行查询,当删除表时 Impala 不处理这些数据文件
分区表:
PARTITIONED BY 子句根据一个或多个指定列的值拆分数据文件。Impala 查询可以使用分区元数据来最小化读取和网络之间传递的数据,特别是连接查询时。更多信息参见 Partitioning.
指定文件格式:
STORED AS 子句标识了相关数据文件的格式。目前 Impala 可以查询超出其可以创建与插入数据的文件格式。对于 Impala 当前不支持的格式,在 Hive 中执行创建或数据载入操作。例如,Impala 可以创建 SequenceFile 表但是无法载入数据。这也是 Impala 处理各种压缩文件的特定格式(There are also Impala-specific procedures for using compression with each kind of file format)。关于与不同数据文件格式协作的详细信息,参见 How Impala Works with Hadoop File Formats。
默认(不使用 STORED AS 子句时)的,Impala 中表创建的数据文件是以 Ctrl-A 作为分隔符的文本文件。使用 ROW FORMAT 子句指定使用不同的分隔符生成或从文件提取数据,如 tab 或 |,或指定不同的行结束符,如回车或换行。当指定分隔符和行结束符号时,用 '\t' 表示 tab,'\n' 表示回车, '\r' 表示换行。
ESCAPED BY 子句对通过 INSERT 语句插入到 Impala TEXTFILE 表的数据文件和已有的直接放置到 Impala 表目录中的文件都有效(你可以使用以下方式提取已有文件的数据:使用 CREATE EXTERNAL TABLE ... LOCATION 语句, 使用 LOAD DATA 语句, 或通过 HDFS 操作如 hdfs dfs -put file hdfs_path)。选择一种不会在文件中其他部分使用的字符作为转义字符,当字段值中出现分隔符时放在每个分隔符实例之前(Choose an escape character that is not used anywhere else in the file, and put it in front of each instance of the delimiter character that occurs within a field value)。被引号引起的字段表示 Impala 不需要对包含嵌入的分隔符的进行解析(Surrounding field values with quotation marks does not help Impala to parse fields with embedded delimiter characters);引号标志它是组成整个列值的一部分。如下想用 \ 作为转义符,需要在 impala-shell 中使用 ESCAPED BY '\\' 进行指定。
克隆表:
使用以下语句,创建一个与其他表具有相同的列、备注、以及其他属性的空表。CREATE TABLE ... LIKE 语句包含一组子句集,当前只支持 LOCATION, COMMENT, STORED AS 子句:
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE [db_name.]table_name
[COMMENT 'table_comment']
[STORED ASfile_format]
[LOCATION 'hdfs_path']
Note: 希望在一个操作中同时克隆表结构和数据,则使用下面介绍的 CREATE TABLE AS SELECT 语句。
当使用 CREATE TABLE ... LIKE 语句克隆现有表的结构时,新表与原表使用相同的文件格式,因此假如你想要使用不同的文件格式时使用 STORED AS 子句指定新的文件格式。
通常 Impala 不能直接创建 HBase 表,但 Impala 可以使用 CREATE TABLE ... LIKE 语句克隆 HBase 表,保留原始表的文件格式和元数据。
使用 CREATE TABLE ... LIKE 语句克隆 Avro 表时可能有一些例外情况。例如无法用此技术克隆一个设置了 Avro schema 但是没有列的 Avro 表。当有疑问时,在 Hive 中测试运行 CREATE TABLE ... LIKE 操作;如果也有问题,那么他通常在 Impala 中也不会正常。
如果原始表是分区表,新表会继承相同的分区键列。因为新表初始化为空表,它没有继承原始表的实际分区。使用插入数据或执行 ALTER TABLE ... ADD PARTITION 语句在新表上创建分区。
因为 CREATE TABLE ... LIKE 只是操作了表的元数据而不是表的物理数据,可以在之后执行 INSERT INTO TABLE 语句从原始表复制一些数据到新表,也可以同时转换为新的文件格式(对于一些文件格式,Impala 可以通过 CREATE TABLE ... LIKE 进行创建,但是不能插入这些文件格式的数据;这时候就必须使用 Hive 加载数据。参考 How Impala Works with Hadoop File Formats 了解详细信息)。
CREATE TABLE AS SELECT:
CREATE TABLE AS SELECT 语法同时完成创建基于原始表所定义的列的新表,从原始表复制数据到新表,而且不需要单独执行 INSERT 语句。这一语句如此流行,都有自己的缩写 "CTAS"。CREATE TABLE AS SELECT 语法如下:
CREATE [EXTERNAL] TABLE [IF NOT EXISTS]db_name.]table_name
[COMMENT 'table_comment']
[STORED ASfile_format]
[LOCATION 'hdfs_path']
AS
select_statement
参考 SELECT Statement 了解关于 CTAS 语句中 SELECT 位置的语法信息。
新创建的表继承了从原始表中选出的列名,也可以通过在查询中指定列别名来修改。列和表的注释都不会从原始表中继承。
下面例子演示了如何克隆原始表所有数据、列和/或行的部分子集,重排列的顺序,重命名列,用表达式构建新列等:
-- 创建新表并复制所有数据
CREATE TABLE clone_of_t1 AS SELECT * FROM t1;
-- 与 CREATE TABLE LIKE 功能相同
CREATE TABLE empty_clone_of_t1 AS SELECT * FROM t1 WHERE 1=0;
-- 复制部分数据
CREATE TABLE subset_of_t1 AS SELECT * FROM t1 WHERE x > 100 AND y LIKE 'A%';
CREATE TABLE summary_of_t1 AS SELECT c1, sum(c2) AS total, avg(c2) AS average FROM t1 GROUP BY c2;
-- 修改文件格式
CREATE TABLE parquet_version_of_t1 AS SELECT * FROM t1 STORED AS PARQUET;
-- 创建与原始表不同列顺序、列名和数据类型的表
CREATE TABLE some_columns_from_t1 AS SELECT c1, c3, c5 FROM t1;
CREATE TABLE reordered_columns_from_t1 AS SELECT c4, c3, c1, c2 FROM t1;
CREATE TABLE synthesized_columns AS SELECT upper(c1) AS all_caps, c2+c3 AS total, "California" AS state FROM t1;
作为 CTAS 操作的一部分,你可以采用任意 Impala 可写的数据文件格式(当前支持 TEXTFILE 和 PARQUET).。但不能设置文本文件表的底层属性(lower-level properties)如分隔符。尽管可以使用分区表作为源表并从中复制数据,但是不能设置新表的分区子句。
Visibility and Metadata:
你可以通过 TBLPROPERTIES 子句关联任意对象到表的元数据。这会产生一组逗号分隔的键值对并保存到 metastore 数据库中。创建之后可以使用 ALTER TABLE 语句来修改这些属性。当前 Impala 查询不使用表属性字段里的这些数据。一些与其他 Hadoop 组件的交互需要设置 TBLPROPERTIES 字段为特定的值,例如创建 Avro 表或 HBase 表(通常在 Hive 中创建这些特定类型的表,因为这些表需要一些当前 Impala 不支持的额外子句)。
你也可以通过 WITH SERDEPROPERTIES 子句指定键值对来关联表的 SerDes 属性。Impala 有自己的内置的所支持文件的序列化和反序列化器,因此不使用这个属性。为了与 Hive 兼容,一些文件格式的转换需要特定的属性值。
执行 DESCRIBE table_name 语句查看表的列定义和列备注,例如在执行 CREATE TABLE ... LIKE 或 CREATE TABLE ... AS SELECT 语句之前。使用 DESCRIBE FORMATTED table_name 语句来查看更详细的信息,如数据文件的位置和如 ROW FORMAT 和 STORED AS 等子句的值。要查看整体表的注释(而不是单独列的注释)也需要使用 DESCRIBE FORMATTED 语句。
当创建了表之后,当前 impala-shell 会话与其他连接到相同节点的 impala-shell 会话可以立刻看到这个表。当连接到其他节点,通过 Impala 守护进程查询这个表之前,应先执行 INVALIDATE METADATA 语句。
Hive considerations:
Impala 查询会使用表和列的元数据,如表的行数或列的不同值个数。在 Impala 1.2.2 之前,当创建完表和载入相应的数据后,需要在 Hive 中执行 ANALYZE TABLE 语句来收集这些元数据信息。在 Impala 1.2.2 及以上版本,Impala 中 COMPUTE STATS 语句可以生成这些统计信息,不再需要在 Hive 中运行。
Note:
Impala 的 CREATE TABLE 语句不能创建 HBase 表,因为当前不支持 HBase 表所需的 STORED BY 子句。可以在 Hive 中创建表,在 Impala 中查询。关于 Impala 使用 HBase 表的信息,参考 Using Impala to Query HBase Tables。
CREATE VIEW 语句
CREATE VIEW 语句为复杂的查询创建一个简写。所基于的查询可以执行连接、表达式、重排序列、列别名和其他的 SQL 功能,可以是一个难于理解和维护的查询。
因为视图是个纯粹的逻辑结构(查询的别名)没有对应实体的数据,所以 ALTER VIEW 只会修改 metastore 数据库中的元数据,而不会涉及 HDFS 中的任意数据文件。
CREATE VIEW view_name[(column_list)]
AS select_statement
CREATE VIEW 语句可用以一下场景:
将最长最复杂的 SQL 查询转换为一行查询,你可以在应用中、脚本中、或 impala-shell 交互式查询中执行针对视图的简单的查询。例如:
select * from view_name;
select * from view_name order by c1 desc limit 10;
原始查询越复杂越难以阅读,使用视图的简单查询越有效果。
隐藏底层表和列,当这些发生变化时减少维护量。这时候,只需要用新的名称重建视图,所有查询视图而不是底层表的语句就可以不需要修改就正常运行
测试优化技术并把优化后的查询提供给所有应用使用(To experiment with optimization techniques and make the optimized queries available to all applications)。例如.你发现了一个 WHERE 条件,连接顺序,连接提示(join hints),等等组合中在一起能获得最佳性能的一类查询,你可以建立一个使用这一最佳组合技术的视图。应用可以对视图进行相对简单的查询,而不是重复着一遍又一遍的繁杂而优化的逻辑。如何之后发现了比原始查询更好的优化方式,重建这个视图,所有应用可以立刻从中受益
简化整类的相关查询,特别是在多个表执行连接,对列进行复杂计算、以及其他语法导致整个查询难以理解和调试查询。例如你可以创建一个视图,连接几个表,使用几个 WHERE 条件过滤数据,并从结果集选择几列。应用可以在这个视图上上执行仅仅是 LIMIT, ORDER BY 等类似简单子句上不同的查询。
对于需要一遍遍重复的查询的复杂子句,例如在查询项, ORDER BY, GROUP BY 子句,你可以使用 WITH 子句作为创建视图的替代方案(you can use the WITH clause as an alternative to creating a view)。
假如为了负载均衡的原因,在 impala-shell 会话中连接到了不同的 Impala 节点,可以启用 SYNC_DDL 查询选项以使得每一 DDL 语句在新的或修改的元数据被所有 Impala 节点接受前等待,直到都被接受后才返回。参考 SYNC_DDL 了解详细信息。
例子:
create view v1 as select * from t1;
create view v2 as select c1, c3, c7 from t1;
create view v3 as select c1, cast(c3 as string) c3, concat(c4,c5) c5, trim(c6) c6, "Constant" c8 from t1;
create view v4 as select t1.c1, t2.c2 from t1 join t2 on (t1.id=t2.id);
create view some_db.v5 as select * from some_other_db.t1;
Statement type: DDL
DESCRIBE 语句
DESCRIBE 语句显示表的元数据,例如列的名称和数据类型。语法为:
DESCRIBE [FORMATTED] table
也可以使用简写 DESC。
DESCRIBE FORMATTED 显示更多信息,显示格式与 Apache Hive 类似。扩展信息包括底层详细信息如表是内部表还是外部表,何时创建,文件格式,HDFS 中数据文件位置,对象是表还是视图,以及(对视图来说)从视图定义中获得的查询语句。
Note: Compressed 字段表是否包含压缩数据的可靠标志。通常总是 No,因为仅当会话载入数据的时候使用压缩设置,并不会持久保存到表的元数据中。
本文由职坐标整理发布,学习更多的大数据技术相关知识,请关注职坐标大技术云计算大技术技术频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-IT技术咨询与就业发展一体化服务 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号