Hive数据库及表操作(三)

1分桶表操作

(1)创建分桶表

创建用户表”user_info”,并根据user_id进行分桶,桶的数量为6,命令如下:

create table user_info (user_id int,name string)

              clustered by(user_id)

               into 6 buckets

               row format delimited fields terminated by '\t';

创建成功后,查看”user_info”的描述信息,命令及主要描述信息如下:

hive (default)> desc formatted user_info;

OK

col_name data_type comment

# col_name             data_type            comment                 

user_id              int                                      

name                 string                                       

# Detailed Table Information    

Database:            default               

Owner:               hadoop                

CreateTime:          Mon Mar 30 22:04:23 CST 2020  

LastAccessTime:      UNKNOWN               

Retention:           0                     

Location:            hdfs://mycluster/home/hadoop/hive/data/user_info  

Table Type:          MANAGED_TABLE         

Table Parameters:    

COLUMN_STATS_ACCURATE {\"BASIC_STATS\":\"true\"}

numFiles             0                   

numRows              0                   

rawDataSize          0                   

totalSize            0                   

transient_lastDdlTime 1585577063             

# Storage Information    

SerDe Library:       org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe  

InputFormat:         org.apache.hadoop.mapred.TextInputFormat  

OutputFormat:        org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat  

Compressed:          No                    

Num Buckets:         6                     

Bucket Columns:      [user_id]

(2)向分桶表导入数据

在本地目录/home/hadoop/下创建数据文件user_info.txt,写入以下数据(列之间以tab键分隔)：

1001 zhangsan

1002 liugang

1003 lihong

1004 xiaoming

1005 zhaolong

NULL wangwu

1007 sundong

1008 jiangdashan

1009 zhanghao

1010 lisi1001

 

执行以下命令,将数据文件user_info.txt导入表”user_info”:

 load data local inpath '/home/hadoop/user_info.txt' into table user_info;   

发现报错信息如下:
FAILED: SemanticException Please load into an intermediate table and use 'insert... select' to allow Hive to enforce bucketing. Load into bucketed tables are disabled for safety reasons. If you know what you are doing, please sethive.strict.checks.bucketing to false and that hive.mapred.mode is not set to 'strict' to proceed. Note that if you may get errors or incorrect results if you make a mistake while using some of the unsafe features.

根据上述错误描述信息可以得知,Hive默认不支持通过load命令导入数据到分桶表(在Hive旧版本中默认可以使用LOAD命令导入,但仅仅是将数据复制或者移动到表目录下,并不会平均分配到各个桶中),代替的是先将数据导入一张中间表(可以是普通表,也可以是临时表)中,然后通过”INSERT....SELECT”的方式,将普通表的数据导入到分桶表.

(3)创建中间表

执行以下命令,创建一张中间表”user_info_tmp”:

create table user_info_tmp(user_id int ,name string)

               row format delimited fields terminated by '\t';

(4)向中间表导入数据

执行下面命令,将数据导入到表”user_info_tmp”中:

load data local inpath '/home/hadoop/user_info.txt'

               into table user_info_tmp;

 

insert into table user_info

              select user_id,name from user_info_tmp;

 

/home/hadoop/hive/data/user_info

/home/hadoop/hive/data/user_info/000000_0

/home/hadoop/hive/data/user_info/000001_0

/home/hadoop/hive/data/user_info/000002_0

/home/hadoop/hive/data/user_info/000003_0

/home/hadoop/hive/data/user_info/000004_0

/home/hadoop/hive/data/user_info/000005_0

/home/hadoop/hive/data/user_info_tmp

/home/hadoop/hive/data/user_info_tmp/user_info.txt

 

hadoop fs -cat  /home/hadoop/hive/data/user_info/000001_0  查看数据文件

1009 zhanghao

1003 lihong

 

hadoop fs -cat /home/hadoop/hive/data/user_info/000002_0

1010 lisi1001

1004 xiaoming

 

 

 

 

 

                        Hive查询

Hive查询语句的语法如下:

select [all|distinct] select_expr,select_expr,...

from table_reference

[where where_condition]

[group by col_list]

[order by col_list]

[cluster by col_list

[cluster by col_list| [distribute by col_list] [sort by col_list]

]

[limit [offset,] rows]

Select 语句可以是联合查询或另一个查询的子查询的一部分。Table_reference表示查询的输入,可以是常规表,视图,表连接或子查询。

select字句查询

1.1where字句

where条件是布尔表达式。例如,下面的查询只返回来自美国地区的金额大于10的销售记录。Hive在where字句中支持许多操作符和自定义函数.

Select * from sales where amount > 10 and region = “US”

1.2all和distinct字句

all和distinct选项指定是否应该返回重复的行,默认为all(返回所有匹配的行)。distinct指定从结果集中删除重复行(注意,Hive从版本1.1.0开始支持distinct)

1.3having字句

having字句主要用于对group by语句产生的分组条件过滤

select col1 from t1 group by col1 having sum(col2) > 10;

1.4limit字句

limit字句可用于限制select语句返回的行数,limit有一个或两个数值参数,它们都必须是非负整数常量。第一个参数指定要返回的开始行的偏移量(即行号,从0开始),第二个参数指定要返回的最大行数,当给定一个参数时,表示最大行数,偏移默认值0,

以下查询返回表customers中的前5条数据

select * from customers limit 5

 

以下查询返回表customers中创建时间最早的前5条数据(order by 默认升序排列):

select * from customers order by create_date limit 5;

 

以下查询返回表customers中创建时间最早的第3到7第条数据

select * from customers order by create_date limit 3,7

 

5group by字句

group by 字句用于对表中的列进行分组查询

例如,统计表users中不同性别的用户数量,查询结果显示性别和数量。因此,可以根据性别字段进行分组:

select users.gender,count(*) from users group by users.gender;

在group by查询中也可以同时指定多个聚合函数,但是多个函数需要指定同一列:

select users.gender,count(distinct users.userid),count(*),sum(distinct user.userid)

from users

group by users.gender;

6order by 和sort by

order by 与rdbms中的order by 一样,对全局结果进行排序,这就意味着,使用order by,所有数据只能通过一个reducer进行处理(多个reducer无法保证全局有序),当数据量特别大时，处理结果将非常缓慢。

sort by 只是对进入reducer中的数据进行排序,相当于局部排序。可以保证每个reducer的输出数据都是有序的,但保证不了全局数据的顺序.

当reducer的数量为1时,使用order by 和sort by的排序结果是一样的,但如果reducer的数量大于1,排序结果将不同。

例如,在数据库test_db中有一张用户表user_info,数据如下:

 

设置reducer数量为1

hive (default)> set mapred.reduce.tasks=1;

使用order by 对表安装user_id字段进行降序排序,如下:

select * from user_info order by user_id desc;

user_info.user_id user_info.name

1010 lisi1001

1009 zhanghao

1008 jiangdashan

1007 sundong

1006 wangwu

1005 zhaolong

1004 xiaoming

1003 lihong

1002 liugang

1001 zhangsan

 

查看日志:

Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 1

使用sort by对表按照user_id字段进行降序排序,如下:

select * from user_info sort by user_id desc;

user_info.user_id user_info.name

1010 lisi1001

1009 zhanghao

1008 jiangdashan

1007 sundong

1006 wangwu

1005 zhaolong

1004 xiaoming

1003 lihong

1002 liugang

1001 zhangsan

 

查看日志:

Stage-Stage-1: Map: 2  Reduce: 1   Cumulative CPU: 10.62 sec   HDFS Read: 11003 HDFS Write: 344 SUCCESS

 

在默认情况下,上述两个语句的查询结果一致,都使用了一个reducer进行处理(Hive会根据数据量自动分配reducer的数量)

现在执行以下命令,将reducer数量设置为2

set mapred.reduce.tasks=2

然后再次使用sort by 安装user_id字段进行降序排序,部分日志信息如下：

hive (default)> set mapred.reduce.tasks = 2;

日志信息:

adoop job information for Stage-1: number of mappers: 2; number of reducers: 2

输出结果：

user_info.user_id user_info.name

1010 lisi1001

1009 zhanghao

1008 jiangdashan

1007 sundong

1005 zhaolong

1004 xiaoming

1001 zhangsan

1006 wangwu

1003 lihong

1002 liugang

 

 

7distribute by 和 cluster by

我们已经知道,MapReducer中的数据都是以键值对的方式进行组织的。默认情况下,MapReduce会根据键的哈希值均匀地将键值对分配到多个reducer中。而distribute by 的作用主要是控制键值对是如何划分到reducer中。使用distribute by 可以保证某一列具有相同值的记录被分发到同一个reducer中进行处理,然后可以结合sort by对每一个reducer的数据进行排序,从而达到期望的输出结果。

创建订单信息表

create table order_info (order_id string,user_id int,order_price float,order_date date)

               row format delimited fields terminated by '\t';

desc order_info  查看创建的订单信息表的信息

 

在本地目录中创建order_content.txt测试数据信息:

001     1       55.5    2018-09-01

002     2       73.4    2018-09-01

003     3       99.2    2018-09-02

004     1       34.5    2018-09-02

005     1       77.58   2018-09-02

006     2       45.56   2018-09-02

007     3       77.5    2018-09-03

008     2       28.0    2018-09-03

009     1       36.0    2018-09-03

加载信息到数据库表

load data local inpath '/home/hadoop/order_content.txt' into table order_info;

设置reduce的个数,例如设置为10。

hive (default)> set mapred.reduce.tasks=10;

select t.user_id,t.order_id,t.order_date from order_info t

distribute by t.user_id          -- 根据user_id分发

sort by t.order_date desc;       -- 分发后根据每一个reducer按照order_date分组

语句执行后的结果

t.user_id t.order_id t.order_date

1 009 2018-09-03

1 005 2018-09-02

1 004 2018-09-02

1 001 2018-09-01

2 008 2018-09-03

2 006 2018-09-02

2 002 2018-09-01

3 007 2018-09-03

3 003 2018-09-02

同一个user_id排列在了一起,且同一个user_id按照日期降序排列

cluster by 同时具备distribute by 和sort by 的功能,但排序只能是升序排列,且不能指定desc或者asc。若上述distribute by 和sort by后面的字段相同,且采用升序排列,则可以使用cluster by代替。查询语句及结果如下:

t.user_id t.order_id t.order_date

1 009             2018-09-03

1 005 2018-09-02

1 004 2018-09-02

1 001 2018-09-01

2 008 2018-09-03

2 006 2018-09-02

2 002 2018-09-01

3 007 2018-09-03

3 003 2018-09-02

8 union子句

union用于将多个select 语句的结果合并到单个结果集中。union子句的语法如下:

select_statement union [all|distinct] select_statement union[all|distinct]

select_statement

在1.2.0之前的Hive版本只支持union all,不会发生重复上删除,结果包含所有select语句中的所有匹配行。在Hive1.2.0和之后版本中,union的默认行为是从结果中删除重复的行,也就是默认union distinct,指定了重复行的删除,当然,也可以在同一个查询中混合union all 和union distinct。

 

JOIN连接查询

Hive中没有主外键之分,但是可以进行多表关联查询。

用户表

create table t_user(uid int,name string)

              row format delimited fields terminated by '\t';

订单表

 create table t_order(oid int,proname string,uid int)

              row format delimited fields terminated by '\t';

1内连接

内连接使用关键字join...on通过关联字段连接两张表,且同一个关联字段的值,两张表中的数据都存在才会显示查询结果中显示(类似MySQL中的INNER JOIN)。

例如有一个这样的需求:查询用户的下单信息,需要显示用户信息和订单信息,未下单的用户不显示。

我们可以将用户表与订单表根据字段uid进行关联查询

 

load data local inpath '/home/hadoop/t_user.txt' into table t_user;

load data local inpath '/home/hadoop/t_order.txt' into table t_order;

 

查询语句及结果如下:

select * from t_user

       join t_order on t_user.uid=t_order.uid;

 

t_user.uid t_user.name t_order.oid t_order.proname t_order.uid

1 张三 1 手机 1

1 张三 2 平板 1

2 李四 3 音箱 2

从输出的结果可以看出,用户张三有两笔订单,用户李四有一笔订单,王五由于没有数据订单,在查询结果中未显示.

下面这条语句与上面的等价

select * from t_user,t_order

              where t_user.uid=t_order.uid;

2左外链接

在外链接以左表为准,使用关键字LEFT OUTER JOIN...ON通过关联字段连接右表,与内连接不同的是,左表中的所有数据都会显示,若左表中关联字段的值在右表中不存在,则右表中不存在的关联数据将置为空值NULL(类似MYSQL中的LEFT JOIN).

例如有这样一个需求: 查询所有用户的下单信息,需要显示用户信息和订单信息,未下单用户同样需要显示.

我们可以以用户表为左表,订单表为右表进行左外链接查询,命令及结果如下:

select * from t_user

              left outer join t_order

              on t_user.uid = t_order.uid;

 

t_user.uid t_user.name t_order.oid t_order.proname t_order.uid

1 张三 1 手机 1

1 张三 2 平板 1

2 李四 3 音箱 2

3 王五 NULL NULL NULL

从该结果中可以看出,左表的用户数据都存在,二用户”王五”由于在右表中没有其关联的数据,因此以空值NULL代替

3右外链接

右外链接与左外链接正好相反,以右表为准,使用关键字RIGHT OUTER JOIN...ON通过关联字段连接左表,有表中的所有数据都会显示,若右表关联字段的值在左表中不存在,则左表中不存在的关联数据位置将被置为NULL(类似MYSQL中的RIGHT JOIN)

例如有一个这样的需求：查询所有的订单信息,并显示订单所属用户信息.

我们可以以用户表为左表,订单表为右表进行右外链接查询

select * from t_user

              right outer join t_order

              on t_user.uid = t_order.uid;

t_user.uid t_user.name t_order.oid t_order.proname t_order.uid

1 张三 1 手机 1

1 张三 2 平板 1

2 李四 3 音箱 2

从上述结果中可以看出,右表的订单数据都存在,而右表关联字段uid的值在左表中也都存在，因此左表关联数据不存在空值NULL的情况,但用户”王五”由于在右表中没有其关联的数据,因此未显示。

4全外链接

全外链接是左外链接与右外链接的综合,使用关键字FULL OUTER JOIN...ON通过关联字段连接两张表,将会显示两张表中的所有数据。若其中一张表中的关联字段的值在另外一张表中不存在,则另外一张表的查询数据将以空值NULL代替.

例如有这样一个需求:查询所有用户的所有订单信息,需要显示所有用户及所有订单.

我们可以使用全外连接将用户表与订单表进行关联查询

select * from t_user

              full outer join t_order

              on t_user.uid = t_order.uid;

 

上述查询结果显示了所有用户及所有订单信息,没有关联的数据则显示为空值NULL.

5半连接

半外链接使用关键字left semi jion...on通过关联字段连接右表,与外连接不同的是,半连接的查询结果只显示左表的内容,即显示与右表相关联的左表数据(类似于MYSQL中的in和exists查询).

例如有这样一个需求:查询有订单数据的所有用户信息(即无订单的用户信息不需要显示)

我们可以以用户表为左表,订单表为右表进行半连接查询.

select * from t_user

              left semi join t_order

              on t_user.uid = t_order.uid;

结果如下

t_user.uid t_user.name

1 张三

2 李四

从上述结果中可以看出,只显示了左表的数据,左表中的用户”王五”由于右表中没有其关联的数据,因此未显示.

此外,上述查询命令也可以使用in关键字代替

select * from t_user

              >where uid in(select uid from t_order);

还可以使用exists关键字代替,如下:

 select * from t_user

              where exists (

              select * from t_order

              where t_order.uid = t_user.uid

  );

 

 

 

配置的含义   

该配置的功能:

<property>

         <name>yarn.scheduler.minimum-allocation-mb</name>

         <value>2048</value>

         <description>default value is 1024</description>

</property>