【Hadoop】-Apache Hive使用语法与概念原理[15]

目录

一、数据库操作

二、数据表操作

2.1、数据导入（数据加载）

2.2、数据导出

2.3、分区表

2.4、分桶表

2.5、修改表

2.6、复杂类型操作

1、Array

2、map

3、struct

三、数据查询

1、基本查询

2、RLIKE正则匹配

3、UNION联合

4、sampling采样

四、函数

1、Mathematical Functions 数学函数 - 部分

2、Collection Functions集合函数 - 全部

3、Type Conversion Functions类型转换函数 - 全部

4、Date Functions日期函数 - 部分

---

一、数据库操作

• 创建数据库

create database if not exists myhive;

• 使用数据库

use myhive;

• 查看数据库详细信息

desc database myhive;

数据库本质上就是在HDFS之上的文件夹。

默认数据库的存放路径是HDFS的：/user/hive/warehouse内

• 创建数据库并指定hdfs存储位置

create database myhive2 location '/myhive2';

使用location关键字，可以指定数据库在HDFS的存储路径

• 删除一个空数据库，如果数据库下面有数据表，那么就会报错

drop database myhive;

• 强制删除数据库，包含数据库下面的表一起删除

drop database myhive2 cascade;

二、数据表操作

2.1、数据导入（数据加载）

• 方式一：从文件向表加载数据

load  data  [local]  inpath  'path'  [overwrite]  into  table  tablename;

----如果数据在hdfs，那么源文件会消失（本质上走的mv移动）

----如果数据在本地，需要带local，如果在hdfs就不用带了

----这个加载方式不会走MapReduce，小文件加载数据快 

• 方式二：从表向其他表加载数据

insert  into | overwrite  table  tablename  select  .......;

2.2、数据导出

• 方式一：通过insert  overwrite  语句

insert  overwrite  [local]  directory  'path' 

[row  format  delimited  fields  terminated  by  '']        -----自定义列分隔符

select  .....;

----带local，写入本地

----不带local，写入hdfs

方式二：

# -e  直接执行sql语句，将结果通过linux的重定向符号写入到指定文件中

bin/hive  -e  "sql语句">  result.txt        

# -f  直接执行sql脚本，将结果通过linux的重定向符号写入到指定文件中

bin/hive  -e  "sql脚本文件">  result.txt  

2.3、分区表

在大数据中，最常用的一种思想就是分治，我们可以把大的文件分割成一个个小的文件，这样每次操作一个小的文件就会很容易了。同样的道理，在hive当中也是支持这种思想，就是我们可以把大的数据，按照每天或者每小时进行切分成一个个的小文件，这样去操作小的文件就会容易得多。

1. 创建分区表

CREATE TABLE test_ext ( id int ) COMMENT 'partitioned table' PARTITION BY ( year string,

month string, day string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

• 单分区 

• 多分区 

2.4、分桶表

分桶和分区一样，也是一种通过改变表的存储模式，从而完成对表优化的一种调优方式。

但和分区不同，分区是将表拆分到不同的子文件夹中进行存储，而分桶是将表拆分到固定数量的不同文件中进行存储。

2.5、修改表

• 表重命名

alter  table  old_table_name  rename  to  new_table_name;

• 修改表属性值

ALTER TABLE table_name SET TBLPROPERTIES table_properties;

• 添加分区

ALTER TABLE tablename  ADD PARTITION (month='201101');

• 修改分区值

ALTER TABLE tablename PARTITION (month='202005') RENAME TO PARTITION (month='201105');

• 删除分区

ALTER TABLE tablename DROP PARTITION (month='201105')

• 添加列

ALTER TABLE table_name ADD COLUMNS (v1 int, v2 string);

• 修改列名

ALTER TABLE table_name CHANGE v1  v1new   INT;

• 清空表（ps：只可清空内部表）

TRUNCATE TABLE tablename;

2.6、复杂类型操作

1、Array

如下数据文件，有2个列，locations列包含多个城市:  

说明:name与locations之间制表符分隔，locations中元素之间逗号分隔

可以使用array数组类型，存储locations的数据

2、map

map类型其实就是简单的指代：Key-Value型数据格式。 有如下数据文件，其中members字段是key-value型数据。

字段与字段分隔符: “,”；需要map字段之间的分隔符："#"；map内部k-v分隔符：":"

3、struct

struct类型是一个复合类型，可以在一个列中存入多个子列，每个子列允许设置类型和名称

三、数据查询

1、基本查询

查询语句的基本语法

• 准备数据1：订单表

数据导入，数据保存于本地的yijia_orders.txt中。

create table yijia.orders(
	orderId bigint comment '订单id',
	orderNo string comment '订单编号',
	shopId bigint comment '门店id',
	userId bigint comment '用户id',
	orderStatus tinyint comment '订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:待发货 1:配送中 2:用户确认收货',
	goodsMoney double comment '商品金额',
	deliverMoney double comment '运费',
	totalMoney double comment '订单金额（包括运费）',
	realTotalMoney double comment '实际订单金额（折扣后金额）',
	payType tinyint comment '支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他',
	isPay tinyint comment '是否支付 0:未支付 1:已支付',
	userName string comment '收件人姓名',
	userAddress string comment '收件人地址',
	userphone string comment '收件人电话',
	createTime timestamp comment '下单时间',
	payTime timestamp comment '支付时间',
	totalPayFee int comment '总支付金额'
)row format delimited fields terminated by '\t';

--数据导入
load data local inpath '/home/hadoop/yijia_orders.txt' into table yijia.orders;

• 准备数据2：用户表

create table yijia.users(
	userId int,
	loginName string,
	loginSecret int,
	loginPwd string,
	userSex tinyint,
	userName string,
    trueName string,
    brithday date,
    userPhoto string,
    userQQ string,
    userPhone string,
    userScore int,
    userTotalScore int,
    userFrom tinyint,
    userMoney double,
    lockMoney double,
    createTime timestamp,
    payPwd string,
    rechargeMoney double
)row format delimited fields terminated by '\t';

• 查询所有

SELECT * FROM  yijia.orders;

• 查询单列

SELECT orderid, userid,goodsmoney from yijia.orders ;

• 查询数据量

select COUNT(*) from yijia.orders ; 

• 过滤广东省订单

select * from yijia.orders where useraddress like '%广东%';

• 找出广东省单笔营业额最大的订单

select * from yijia.orders where useraddress like '%广东%'
order by totalmoney desc limit 1;

• 统计未支付、已支付各自的人数

SELECT ispay, COUNT(*) from yijia.orders group by ispay ;

• 统计每个用户的平均订单消费额

SELECT userid, avg(totalmoney) from yijia.orders group by userid ;

• 统计每个用户的平均订单消费额，过滤大于10000的数据

SELECT userid, avg(totalmoney) as avg_money from yijia.orders group by userid HAVING avg_money > 10000;

• 订单表和用户表JOIN，找出用户username

SELECT o.orderid, o.userid, u.username from orders o join users u on o.userid  =u.userid ;

2、RLIKE正则匹配

正则表达式是一种规则集合，通过特定的规则字符描述，来判断字符串是否符合规则。

• 查找广东省的数据 

select * from yijia.orders where useraddress rlike '.*广东.*';

• 查找用户地址是：xx省 xx市 xx区的数据

select * from yijia.orders where useraddress rlike '..省 ..市 ..区';

• 查找用户姓为张、王、邓

select * from yijia.orders where username RLIKE '[张王邓].*';

• 查找手机号符合：188****0*** 规则

select * from yijia.orders where userphone RLIKE '188\\S{4}0\\S{3}'；

3、UNION联合

UNION 用于将多个 SELECT 语句的结果组合成单个结果集。 每个 select 语句返回的列的数量和名称必须相同。否则，将引发架构错误。

文件内容展示：

语法使用：

4、sampling采样

大数据体系下，在真正的企业环境中，很容易出现很大的表，比如体积达到TB级别。 对这种表一个简单的SELECT * 都会非常的慢，哪怕LIMIT 10想要看10条数据，也会走MapReduce流程 这个时间等待是不合适的。

TABLESAMPLE函数

四、函数

官方文档(https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-MathematicalFunctions)

Hive的函数分为两大类：内置函数（Built-in Functions）、用户定义函数UDF（User-Defined Functions）：

1、Mathematical Functions 数学函数 - 部分

2、Collection Functions集合函数 - 全部

3、Type Conversion Functions类型转换函数 - 全部

4、Date Functions日期函数 - 部分