HiveSQL学习

最新推荐文章于 2023-10-16 06:00:00 发布

uilotus

最新推荐文章于 2023-10-16 06:00:00 发布

阅读量1.8k

点赞数

分类专栏： Hadoop

本文链接：https://blog.youkuaiyun.com/uilotus/article/details/38796617

版权

Hadoop 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了HiveSQL，包括DDL（数据定义语言）操作，如创建、删除和修改表，以及DML（数据操作语言）和DQL（数据查询语言）操作，如加载数据、查询和数据处理。Hive基于Hadoop，提供SQL查询功能，适合大规模数据分析，但不支持实时查询和UPDATE、DELETE操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Hive 是基于Hadoop构建的一套数据仓库分析系统，他以SQL查询的方式来分析存储在Hadoop分布式文件系统中的数据，将结构化的数据文件映射成一张表，并且提供了完整的SQL查询功能，可以将SQL语句转化成为MapReduce任务运行。这一套SQL就是HiveSQL。对于不熟悉MapReduce的人来说，使用类似SQL的语句就可以很方便地通过查询语句来分析数据。而MapReduce的开发人员，也可以把自己编写的mapper和reducer作为插件支持hive进行更加复杂的数据分析工作。

HiveSQL与关系数据库中的SQL略有不同：HiveSQL支持绝大多数的DDL，DML以及连接查询、条件查询和聚合函数。但是HIVE不支持OLTP，也不支持实时查询。

一、 DDL操作(Data Definition Language)

DDL

•建表

•删除表

•修改表结构

•创建／删除视图

•创建数据库

•显示命令

1. 创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[ STORED AS file_format ]
[LOCATION hdfs_path]

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常

•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）

•LIKE 允许用户复制现有的表结构，但是不复制数据

•COMMENT可以为表与字段增加描述

•ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

STORED AS

SEQUENCEFILE

| TEXTFILE

| RCFILE

| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。

1）. External：外部表和内部表

Hive的数据表分为两种：外部表和内部表。内部表是Hive创建并通过load data inpath加载到数据库中的表。这种表可以理解为数据和表结构都保存在一起的表。当你通过DROP TABLE table_name 删除元数据中表结构的同时，表中的数据也同样会从hdfs中被删除。

外部表指在表结构创建以前，数据已经保存在hdfs中了，通过创建表结构，将数据格式化到表的结构里。当DROP TABLE table_name 的时候，hive仅仅会删除元数据的表结构，而不会删除hdfs上的文件，所以，相比内部表，外部表可以更放心大胆的使用。

内部表的创建：

CREATE TABLE database.table1 
(
column1 STRING COMMENT 'comment1',
column2 INT COMMENT 'comment2'
);

外部表创建，下面是hdfs中文件不用LZO压缩，纯文本保存时，如何创建外部表：

CREATE EXTERNAL TABLE IF NOT EXISTS database.table1 
(
column1 STRING COMMENT 'comment1',
column2 STRING COMMENT 'comment2'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION 'hdfs:///data/dw/asf/20120201';

下面是，当hdfs中的文件用LZO压缩后，如何创建外部表，当然你需要hadoop-gpl的支持才能以文本形式读取lzo文件。

CREATE EXTERNAL TABLE IF NOT EXISTS database.table1
(
column1 STRING COMMENT 'comment1',
column2 STRING COMMENT 'comment2'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
STORED AS INPUTFORMAT "com.hadoop.mapred.DeprecatedLzoTextInputFormat" OUTPUTFORMAT "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"
LOCATION 'hdfs:///data/dw/asf/20120201';

2）创建分区表：

在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。分区表指的是在创建表时指定的partition的分区空间。如果需要创建有分区的表，需要在create表的时候调用可选参数partitioned by，详见表创建的语法结构。

技术细节：

a、一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。

b、表和列名不区分大小写。

c、分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。

d、分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。

单分区表：

create table day_table (id int, content string) partitioned by (dt string);

多分区表：

create table day_hour_table (id int, content string) partitioned by (dt string, hour string);

双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。表文件夹目录示意图（多分区表）：

e、添加分区表语法（表已创建，在此基础上添加分区）

ALTER TABLE table_name ADD
  partition_spec [ LOCATION 'location1' ]
  partition_spec [ LOCATION 'location2' ] ...

partition_spec:
  : PARTITION (partition_col = partition_col_value,
       partition_col = partiton_col_value, ...)

ALTER TABLE day_table ADD
    PARTITION (dt='2008-08-08', hour='08')
      location '/path/pv1.txt'
    PARTITION (dt='2008-08-08', hour='09')
      location '/path/pv2.txt';

f、删除分区：

ALTER TABLE table_name DROP
    partition_spec, partition_spec,...

用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。例：

ALTER TABLE day_hour_table
    DROP PARTITION (dt='2008-08-08', hour='09');
     g、 数据加载进分区表中语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE]
    INTO TABLE tablename
    [PARTITION (partcol1=val1, partcol2=val2 ...)]
LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table  PARTITION(dt='2008-08- 08', hour='08');
LOAD DATA local INPATH '/user/hua/*' INTO TABLE day_hour  partition(dt='2010-07- 07');

当数据被加载至表中时，不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录，文件存放在该分区下。
	h、 基于分区的查询的语句
SELECT day_table.*
    FROM day_table
    WHERE day_table.dt>= '2008-08-08';

查看分区语句：

hive> show partitions day_hour_table;
OK
dt=2008-08-08/hour=08
dt=2008-08-08/hour=09
dt=2008-08-09/hour=09
总结：1、在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在最子集的目录中。

	  2、 总的说来partition就是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。

	3、 外部表也是一种表，普通表有分区，外部表也是有分区的。所以如果是基于分区表创建的外部表一定要对外部表执行ALTER TABLE table_name ADD PARTITION。否则是根本访问不到数据的。

3). 创建Bucket表

CREATE TABLE par_table(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(date STRING, pos STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED ‘\t’
   FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

http://blog.youkuaiyun.com/wisgood/article/details/17186107

create table bucketed_user(id int,name string) clustered by (id) sorted by(name) into 4 buckets row format delimited fields terminated by '\t' stored as textfile;

4）复制空表

CREATE TABLE empty_key_value_store
LIKE key_value_store;

显示所有表：

 SHOW TABLES;

2. 删除表和截断表

删除表会移除表的元数据和数据，而HDFS上的数据，如果配置了Trash，会移到.Trash/Current目录下。

删除外部表时，表中的数据不会被删除。

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

截断表：
从表或者表分区删除所有行，不指定分区，将截断表中的所有分区，也可以一次指定多个分区，截断多个分区。

TRUNCATE TABLE table_name;
TRUNCATE TABLE table_name PARTITION (dt='20080808');

3. 修改表

修改表结构

•增加分区、删除分区

•重命名表

•修改列的名字、类型、位置、注释

•增加/更新列

•增加表的元数据信息

表添加一列：

hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);

添加一列并增加列字段注释

hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

重命名表：

hive> ALTER TABLE events RENAME TO 3koobecaf;

删除列：

hive> DROP TABLE pokes;

增加、删除分区

•增加

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...

partition_spec:

: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

•删除

ALTER TABLE table_name DROP partition_spec, partition_spec,...

重命名表

•ALTER TABLE table_name RENAME TO new_table_name

修改列的名字、类型、位置、注释：

•ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

•这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合

增加/更新列

•ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

• ADD是代表新增一字段，字段位置在所有列后面(partition列前)

REPLACE则是表示替换表中所有字段。

增加表的元数据信息

•ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:

:[property_name = property_value…..]

•用户可以用这个命令向表中增加metadata

改变表文件格式与组织

•ALTER TABLE table_name SET FILEFORMAT file_format

•ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS

•这个命令修改了表的物理存储属性

创建／删除视图

•CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)] AS SELECT

•增加视图

•如果没有提供表名，视图列的名字将由定义的SELECT表达式自动生成

•如果修改基本表的属性，视图中不会体现，无效查询将会失败

•视图是只读的，不能用LOAD/INSERT/ALTER

•DROP VIEW view_name

•删除视图

创建数据库

•CREATE DATABASE name

显示命令

•show tables;

•show databases;

•show partitions ;

•show functions

•describe extended table_name dot col_name

二、DML 操作:元数据存储

hive不支持用insert语句一条一条的进行插入操作，也不支持 update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。

•向数据表内加载文件

•将查询结果插入到Hive表中

•0.8新特性 insert into

向数据表内加载文件

•LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

•Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。

•filepath

•相对路径，例如：project/data1

•绝对路径，例如： /user/hive/project/data1

•包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1

例如：

hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

加载本地数据，同时给定分区信息

•加载的目标可以是一个表或者分区。如果表包含分区，必须指定每一个分区的分区名

•filepath 可以引用一个文件（这种情况下，Hive 会将文件移动到表所对应的目录中）或者是一个目录（在这种情况下，Hive 会将目录中的所有文件移动至表所对应的目录中）

LOCAL关键字

•指定了LOCAL，即本地

•load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。用户也可以为本地文件指定一个完整的 URI，比如：file:///user/hive/project/data1.

•load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置

例如：加载本地数据，同时给定分区信息：

hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

• 没有指定LOCAL

如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。否则

•如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI

•如果路径不是绝对的，Hive 相对于 /user/ 进行解释。 Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径中

加载DFS数据，同时给定分区信息：

hive> LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');
The above command will load data from an HDFS file/directory to the table. Note that loading data from HDFS will result in moving the file/directory. As a result, the operation is almost instantaneous.

OVERWRITE

•指定了OVERWRITE

•目标表（或者分区）中的内容（如果有）会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

•如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

使用insert into table 数据只做增加操作；使用insert overwrite table 将删除当前指定的数据存储目录的所有数据（即只会删除指定分区数据不会删除其他分区的数据），再导入新的数据。

将查询结果插入Hive表

•将查询结果插入Hive表

•将查询结果写入HDFS文件系统

•基本模式

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

•多插入模式

FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

•自动分区模式

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

将查询结果写入HDFS文件系统

•INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]

•

数据写入文件系统时进行文本序列化，且每列用^A 来区分，\n换行

INSERT INTO

•INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

三、DQL 操作:数据查询SQL

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[ CLUSTER BY col_list

| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMIT number]

使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL，表示查询所有记录。DISTINCT表示去掉重复的记录

•

•Where 条件

•类似我们传统SQL的where 条件

•目前支持 AND,OR ,0.9版本支持between

•IN, NOT IN

•不支持EXIST ,NOT EXIST

ORDER BY与SORT BY的不同

•ORDER BY 全局排序，只有一个Reduce任务

•SORT BY 只在本机做排序

Limit

•Limit 可以限制查询的记录数

SELECT * FROM t1 LIMIT 5

•实现Top k 查询

•下面的查询语句查询销售记录最大的 5 个销售代表。

SET mapred.reduce.tasks = 1
SELECT * FROM test SORT BY amount DESC LIMIT 5

•REGEX Column Specification

SELECT 语句可以使用正则表达式做列选择，下面的语句查询除了 ds 和 hr 之外的所有列：

SELECT `(ds|hr)?+.+` FROM test

例如

按先件查询

hive> SELECT a.foo FROM invites a WHERE a.ds='<DATE>';

将查询数据输出至目录：

hive> INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='<DATE>';

将查询结果输出至本地目录：

hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

选择所有列到本地目录：

hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;
hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROM profiles a;
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM invites a WHERE a.ds='<DATE>';
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM invites a;
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a;

将一个表的统计结果插入另一个表中：

hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(1) WHERE a.foo > 0 GROUP BY a.bar;
hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;
JOIN
hive> FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;

FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

将文件流直接插入文件：

hive> FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';
This streams the data in the map phase through the script /bin/cat (like hadoop streaming). Similarly - streaming can be used on the reduce side (please see the Hive Tutorial or examples)

3.2 基于Partition的查询

•一般 SELECT 查询会扫描整个表，使用 PARTITIONED BY 子句建表，查询就可以利用分区剪枝（input pruning）的特性

•Hive 当前的实现是，只有分区断言出现在离 FROM 子句最近的那个WHERE 子句中，才会启用分区剪枝

3.3 Join

Syntax

join_table:
   table_reference JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition

table_reference:
    table_factor
| join_table

table_factor:
    tbl_name [alias]
| table_subquery alias
| ( table_references )

join_condition:
    ON equality_expression ( AND equality_expression )*

equality_expression:
    expression = expression

•Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。Hive 不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务

•LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况

•LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现

•join 时，每次 map/reduce 任务的逻辑是这样的：reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统

•实践中，应该把最大的那个表写在最后

join 查询时，需要注意几个关键点

•只支持等值join

•SELECT a.* FROM a JOIN b ON (a.id = b.id)

•SELECT a.* FROM a JOIN b
ON (a.id = b.id AND a.department = b.department)

•可以 join 多于 2 个表，例如

SELECT a.val, b.val, c.val FROM a JOIN b
ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

•如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务

LEFT，RIGHT和FULL OUTER

•例子

•SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

•如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在 join 子句中写

•

•容易混淆的问题是表分区的情况

• SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key)
WHERE a.ds='2010-07-07' AND b.ds='2010-07-07‘

•如果 d 表中找不到对应 c 表的记录，d 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 d 表中不能找到匹配 c 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关

•解决办法

•SELECT c.val, d.val FROM c LEFT OUTER JOIN d
ON (c.key=d.key AND d.ds='2009-07-07' AND c.ds='2009-07-07')

LEFT SEMI JOIN

•LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行

•

•SELECT a.key, a.value
FROM a
WHERE a.key in
(SELECT b.key
FROM B);

可以被重写为：

SELECT a.key, a.val
FROM a LEFT SEMI JOIN b on (a.key = b.key)

UNION ALL

•用来合并多个select的查询结果，需要保证select中字段须一致

•select_statement UNION ALL select_statement UNION ALL select_statement ...

4. 从SQL到HiveQL应转变的习惯

1、Hive不支持等值连接

•SQL中对两表内联可以写成：

•select * from dual a,dual b where a.key = b.key;

•Hive中应为

•select * from dual a join dual b on a.key = b.key;

而不是传统的格式：

SELECT t1.a1 as c1, t2.b1 as c2FROM t1, t2 WHERE t1.a2 = t2.b2

2、分号字符

•分号是SQL语句结束标记，在HiveQL中也是，但是在HiveQL中，对分号的识别没有那么智慧，例如：

•select concat(key,concat(';',key)) from dual;

•但HiveQL在解析语句时提示：

FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in function specification

•解决的办法是，使用分号的八进制的ASCII码进行转义，那么上述语句应写成：

•select concat(key,concat('\073',key)) from dual;

3、IS [NOT] NULL

•SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.

4、Hive不支持将数据插入现有的表或分区中，

仅支持覆盖重写整个表，示例如下：

 
        INSERT OVERWRITE TABLE t1  
 SELECT * FROM t2;

4、hive不支持INSERT INTO xxx VALUES(XXXX), UPDATE, DELETE操作

这样的话，就不要很复杂的锁机制来读写数据。
INSERT INTO syntax is only available starting in version 0.8。INSERT INTO就是在表或分区中追加数据。

5、hive支持嵌入mapreduce程序，来处理复杂的逻辑

如：

 
        FROM (  
 MAP doctext USING 'python wc_mapper.py' AS (word, cnt)  
 FROM docs  
 CLUSTER BY word  
 ) a  
 REDUCE word, cnt USING 'python wc_reduce.py';  
 
      

--doctext: 是输入

--word, cnt: 是map程序的输出

--CLUSTER BY: 将wordhash后，又作为reduce程序的输入

并且map程序、reduce程序可以单独使用，如：

 
        FROM (  
 FROM session_table  
 SELECT sessionid, tstamp, data  
 DISTRIBUTE BY sessionid SORT BY tstamp  
 ) a  
 REDUCE sessionid, tstamp, data USING 'session_reducer.sh';  
 
      

--DISTRIBUTE BY: 用于给reduce程序分配行数据

6、hive支持将转换后的数据直接写入不同的表，还能写入分区、hdfs和本地目录。

这样能免除多次扫描输入表的开销。

 
        FROM t1  
   
 INSERT OVERWRITE TABLE t2  
 SELECT t3.c2, count(1)  
 FROM t3  
 WHERE t3.c1 <= 20  
 GROUP BY t3.c2  
   
 INSERT OVERWRITE DIRECTORY '/output_dir'  
 SELECT t3.c2, avg(t3.c1)  
 FROM t3  
 WHERE t3.c1 > 20 AND t3.c1 <= 30  
 GROUP BY t3.c2  
   
 INSERT OVERWRITE LOCAL DIRECTORY '/home/dir'  
 SELECT t3.c2, sum(t3.c1)  
 FROM t3  
 WHERE t3.c1 > 30  
 GROUP BY t3.c2;  
 
      

5. 实际示例

创建一个表

CREATE TABLE u_data (
userid INT,
movieid INT,
rating INT,
unixtime STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '/t'
STORED AS TEXTFILE;

下载示例数据文件，并解压缩
wget http://www.grouplens.org/system/files/ml-data.tar__0.gz
tar xvzf ml-data.tar__0.gz

加载数据到表中:

LOAD DATA LOCAL INPATH 'ml-data/u.data'
OVERWRITE INTO TABLE u_data;

统计数据总量:

SELECT COUNT(1) FROM u_data;

现在做一些复杂的数据分析:

创建一个 weekday_mapper.py: 文件，作为数据按周进行分割
import sys
import datetime

for line in sys.stdin:
line = line.strip()
userid, movieid, rating, unixtime = line.split('/t')

生成数据的周信息

weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '/t'.join([userid, movieid, rating, str(weekday)])

使用映射脚本

//创建表，按分割符分割行中的字段值
CREATE TABLE u_data_new (
userid INT,
movieid INT,
rating INT,
weekday INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '/t';
//将python文件加载到系统
add FILE weekday_mapper.py;

将数据按周进行分割

INSERT OVERWRITE TABLE u_data_new
SELECT
TRANSFORM (userid, movieid, rating, unixtime)
USING 'python weekday_mapper.py'
AS (userid, movieid, rating, weekday)
FROM u_data;

SELECT weekday, COUNT(1)
FROM u_data_new
GROUP BY weekday;

处理Apache Weblog 数据

将WEB日志先用正则表达式进行组合，再按需要的条件进行组合输入到表中
add jar ../build/contrib/hive_contrib.jar;

CREATE TABLE apachelog (
host STRING,
identity STRING,
user STRING,
time STRING,
request STRING,
status STRING,
size STRING,
referer STRING,
agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|//[[^//]]*//]) ([^ /"]*|/"[^/"]*/") (-|[0-9]*) (-|[0-9]*)(?: ([^ /"]*|/"[^/"]*/") ([^ /"]*|/"[^/"]*/"))?",
"output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
)
STORED AS TEXTFILE;