hive(三)

HQL基础语法

Hive中的语句叫做HQL语句,是一种类似SQL的语句,基本上和SQL相同但是某些地方也是有很大的区别.

数据库操作

创建数据库

• 1.创建一个数据库,数据库在HDFS上的默认存储路径是/hive/warehouse/*.db。

create database hive01;

• 避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）

create database if not exists hive01;

• 创建数据库指定位置

create database if not exists hive01 location '/hive01.db';

修改数据库

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

alter database hive01 set dbproperties('createtime'='20220727');

查看数据库

• 显示所有数据库

show databases;

• 显示数据库使用like过滤

show databases like 'h*';

• 查看数据库详情

desc database hive01;
describe database extended hive01 ; -- 带属性

• 切换数据库

use hive01;

删除数据库

• 最简写法

drop database hive01;

• 如果删除的数据库不存在，最好使用if exists判断数据库是否存在。否则会报错：FAILED:SemanticException [Error 10072]: Database does not exist: db_hive

drop database if exists hive01;

• 如果数据库不为空，使用cascade命令进行强制删除

drop database if exists hive01 cascade;

Hive数据类型

Hive数据类型指的是表中列的字段类型;

整体分为两类︰原生数据类型( primitive data type)和复杂数据类型( complex data type ) 。

原生数据类型包括:数值类型、时间日期类型、字符串类型、杂项数据类型.

复杂数据类型包括:array数组、map映射、struct结构.

原生数据类型

对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

隐式类型转换

• 与标准SQL类似，HQL支持隐式和显式类型转换。
• 原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。
• 下表描述了类型之间允许的隐式转换∶

强制类型转换

使用CAST函数 cast(数据 as 新类型)

select cast( '100' as double); 会将100字符串转换为100整数值。
如果强制转换失败，例如select cast ('aa' as int );，该函数返回NULL。

复杂数据类型

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

数据库表基本操作

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 DELIMITED | SERDE serde_name WITH SERDEPROPERTIES(property_name=property_value,..)] 
[STORED AS file_format] 
[LOCATION hdfs_path]


[] 中括号的语法表示可选。
|  表示使用的时候，左右语法二选一。
建表语句中的语法顺序要和语法树中顺序保持一致。

字段简单说明
-  CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项 来忽略这个异常。
-  EXTERNAL  外部表
-  COMMENT： 为表和列添加注释。
-  PARTITIONED BY 创建分区表
-  CLUSTERED BY 创建分桶表
-  SORTED BY  排序不常用
- ROW FORMAT DELIMITED 使用默认序列化LazySimpleSerDe 进行指定分隔符
- SERDE 使用其他序列化类 读取文件
- STORED AS 指定文件存储类型
- LOCATION 指定表在HDFS上的存储位置。
- LIKE 允许用户复制现有的表结构，但是不复制数据

根据数据创建表

案例1:简单用户信息

1,admin,123456,男,18
2,zhangsan,abc123,男,23
3,lisi,654321,女,16

use hive01;

create table t_user(
    id int,
    uname string,
    pwd string,
    sex string,
    age int )
row format delimited fields terminated by ','; --指定 字段之间用 , 分隔

当我们创建表后 在HDFS上会产生对应的文件夹 
/user/hive/warehouse/hive01.db/t_user
但是此时里面没有任何文件 
我们可以将user.txt文件上传到这个位置

select  * from t_user;
+------------+---------------+-------------+-------------+-------------+
| t_user.id  | t_user.uname  | t_user.pwd  | t_user.sex  | t_user.age  |
+------------+---------------+-------------+-------------+-------------+
| 1          | admin         | 123456      | 男           | 18          |
| 2          | zhangsan      | abc123      | 男           | 23          |
| 3          | lisi          | 654321      | 女           | 16          |
+------------+---------------+-------------+-------------+-------------+

案例2:复杂人员信息

liuyan,tangtang_mimi,liuliu:18_yanyan:14,hui long guan_beijing
jinlian,dalang_qingqing,jinjin:18_lianlian:19_aa:20,chao yang_beijing

结构

{
    "name": "liuyan",
    "friends": ["tangtang" , "mimi"] ,       //列表Array, 
    "children": {                      //键值Map,
        "liuliu": 18 ,
        "yanyan": 14
    }
    "address": {                      //结构Struct,
        "street": "hui long guan" ,
        "city": "beijing" 
    }
}

建表

create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
-- 语法解释
row format delimited fields terminated by ','  -- 列分隔符
collection items terminated by '_'  	-- MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':'				-- MAP中的key与value的分隔符

复杂类型查询操作

1.数组

- arr[index]   取值
- size(arr)     长度
- 数组角标越界返回NULL
- explode(arr)

select name,friend[0],friend[1],friend[2] from t_user2;
+----------+-----------+-----------+-------+
|   name   |    _c1    |    _c2    |  _c3  |
+----------+-----------+-----------+-------+
| liuyan   | tangtang  | mimi      | NULL  |
| jinlian  | dalang    | qingqing  | NULL  |
+----------+-----------+-----------+-------+

select name,size(friend) as frendsize from  t_user2;
+----------+------------+
|   name   | frendsize  |
+----------+------------+
| liuyan   | 2          |
| jinlian  | 2          |
+----------+------------+

2.Map

map_keys(字段)  所有key
map_values(字段) 所有value

select map_keys(children),map_values(children) from t_user2;
+------------------------+----------+
|          _c0           |   _c1    |
+------------------------+----------+
| ["liuliu","yanyan"]    | [18,14]  |
| ["jinjin","lianlian"]  | [18,19]  |
+------------------------+----------+
select map_keys(children)[0],map_values(children)[0] from t_user2;
+---------+------+
|   _c0   | _c1  |
+---------+------+
| liuliu  | 18   |
| jinjin  | 18   |
+---------+------+
-- 根据key获取值
select children['liuliu'] from t_user2
+-------+
|  _c0  |
+-------+
| 18    |
| NULL  |
+-------+

3.Struct

select name,address.city,address.street from t_user2;
+----------+----------+---------------+
|   name   |   city   |    street     |
+----------+----------+---------------+
| liuyan   | beijing  | huilong guan  |
| jinlian  | beijing  | chao yang     |
+----------+----------+---------------+

Hive读写文件机制

Hive读取文件机制︰首先调用InputFormat（默认TextInputFormat )，返回一条一条kv键值对记录（默认是一行对应一条键值对)。然后调用SerDe(默认LazySimpleSerDe )的Deserializer，将一条记录中的value根据分隔符切分为各个字段。
Hive写文件机制:将Row写入文件时，首先调用SerDe(默认LazySimpleSerDe )的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中。

SerDe:Serializer,DeSerializer.

当我们使用 row format delimited时 使用默认的LazySimpleSerDe类来处理数据。

如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde_name指定其他的Serde类来处理数据,甚至支持用户自定义SerDe类。

LazySimpleSerDe是Hive默认的序列化类，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用。

Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符;

默认的分割符是' \001'，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的。

在vi编辑器中，连续按下Ctrl+v/Ctrl+a即可输入'\001'，显示^A, 在文本编辑器中将以SOH的形式显示

Location指定文件位置

Hive表默认的存储路径是通过hive-site.xml配置文件中hive.metastore.warehouse.dir属性指定的.

# 默认位置
/user/hive/warehouse

创建数据库,就会在warehouse下产生一个xx.db的文件夹,在哪个库下创建表,就会在对应的文件夹下产生对应表的文件夹.当然这是默认情况 我们可以在创建表时使用Location来指定表的位置.

create table t_user_location(
    id int,
    uname string,
    pwd string,
    sex string,
    age int )
row format delimited fields terminated by ','
location "/aaa";  -- 这里指定在根目录下的aaa文件夹

将数据上传到aaa文件夹中
hdfs dfs -put user.txt /aaa

Location可以指定创建库的位置 也可以指定创建表的位置.

查询修改删除表

-- 显示表信息
show tables;
show tables like '*user*';

desc t_user;   --字段  类型
desc formatted t_user;  -- 表详细信息

-- 修改表名
-- alter table old_table_name rename to new_table_name
-- alter table test02 rename to test;

--  添加列
-- alter table 表名  add  列名 数据类型;


-- 修改列
-- alter table 表名 change 旧列名 新列名 数据类型



-- 删除列  想删除哪列 就不写哪列 这里的删除就是用 ()中的内容替换原本的内容
select * from t_user_location;
alter  table  t_user_location replace columns (
    id int,
    uname string,
    pwd string,
    sex string);


-- 删除表
-- drop table 表名;

数据的导入

直接放到对应目录

当我们创建好一张表后,会有对应的文件夹 默认位置 /user/hive/warehouse/xx.db/表名
我们之前的方式是通过put或者mv直接将文件放入到对应的文件夹下 

user.txt
1,admin,123456,男,18
2,zhangsan,abc123,男,23
3,lisi,654321,女,16

use hive01;

create table t_user_import(
    id int,
    uname string,
    pwd string,
    sex string,
    age int
)row format delimited fields terminated by ',';

我们可以直接将user.txt 上传到指定的位置 
hdfs dfs -put user.txt /user/hive/warehouse/hive01.db/t_user_import
也可以将hdfs上的数据 移动 或者复制到指定的位置
hdfs dfs -mv  /user.txt /user/hive/warehouse/hive01.db/t_user_import
hdfs dfs -cp  /user.txt /user/hive/warehouse/hive01.db/t_user_import

表的文件夹下可以有多个文件 都可以显示

使用load方式

相比较直接暴力放数据的行为 官方更推荐使用load的方式加载数据.

将本地文件加载到对应表 注意:此处的本地不是客户端的本地 指的是HS2服务所在的主机的本地.

指定LOCAL，将在本地文件系统中查找文件路径。
若指定相对路径，将相对于用户的当前工作目录进行解释;
用户也可以为本地文件指定完整的URI-例如:file:///root/user.txt
/*
语法
     加载本地文件到 表  注意 这里的本地并不是客户端本地 而是hiveserver2所在的计算机
     load data local inpath '本地文件' into table  表名;  //不覆盖原有数据 如果有相同文件会自动改名
     load data local inpath '本地文件' overwrite into table  表名; //覆盖原有数据
    加载hdfs文件到 表
     load data  inpath '本地文件' into table  表名;  //不覆盖原有数据 如果有相同文件会自动改名
     load data  inpath '本地文件' overwrite into table  表名; //覆盖原有数据
 */
create table t_user_load(
    id int,
    uname string,
    pwd string,
    sex string,
    age int
)row format delimited fields terminated by ',';

-- 注意这是一个复制操作 本地的user.txt还存在   
load data local inpath "/root/user.txt" into table t_user_load;
-- 同名文件可以多次执行 会自动改名
load data local inpath "/root/user.txt" into table t_user_load;
-- 查询没有任何问题
select * from t_user_load;

-- 如果加上 overwite 会将原本文件中的所有文件覆盖
load  data local inpath "/root/user.txt" overwrite into table t_user_load;
-- 查询时只显示最后一次添加的内容
select * from t_user_load;

将HDFS文件加载到对应表 也不一定是HDFS也可以是其他文件系统

没有指定LOCAL关键字 如果filepath指向的是一个完整的URI，会直接使用这个URI 
如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.defaultFS指定的(不出意外，都是HDFS)。

-- 将hdfs上的根目录的user.txt 加载  注意这是一个移动的动作 原本位置的文件没有了
load data  inpath "/user.txt" into table t_user_load;
-- 加上overwirte还是覆盖操作
load data  inpath "/user.txt" overwrite into table t_user_load;

使用insert+select

使用insert into values方式虽然也可插入数据,但是插入的数据量小,每次都需要执行MR程序效率低下,不推荐使用.

insert into t_user_load values(1,'admin','123','男',100);

-- 如果报错  FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask
set hive.txn.stats.enabled=false
set hive.stats.autogather=false

我们可以使用insert+select方式

insert+select表示:将后面查询返回的结果作为内容插入到指定表中，注意OVERWRITE将覆盖已有数据。需要保证查询结果列的数目和需要插入数据表格的列数目一致.如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

create table test_insert(
    id int,
    name string
)row format delimited fields terminated by ",";

查询user表的id和name 将结果保存到test_insert中;
insert into table test_insert select id,uname from t_user_load;

查询user表的id和name 将结果保存到test_insert中 将原有数据覆盖
insert overwrite table  test_insert select id,uname from t_user_load;

Multiple Inserts 多重插入 核心:一次扫描,多次插入 目的就是减少扫描的次数 完成多次insert操作.

create table test_insert02(
    id int
)row format delimited fields terminated by ",";

create table test_insert03(
    name string
)row format delimited fields terminated by ",";

我想将user表中id一列插入到  test_insert02 表中 将user表中name一列插入到test_insert03表中.
我们可以这样写

insert into table  test_insert02 select id from t_user;
insert into table  test_insert03 select uname from t_user;
但是这样的话 会扫描t_user两次 

一次扫描,多次插入
from t_user
insert into table test_insert02
select id
insert into table test_insert03
select uname;

查询创建表加载

/*
	 create table 表名 as select 列名,列名 from 表;
*

create  table  t_tmp  as  select id , uname , age from t_user ;

使用import导入

export将表中的数据导出到指定的目录下 / 保持表原来的目录结构

使用import快速的导入数据到表中

用于数据的备份和迁移 , 导入的数据必须是export导出的数据

-- 将t_user数据导出到hdfs上 /aaa 文件夹中
export table t_user to '/aaa';
-- 创建表t_user_import 和t_user结构一样
create table t_user_import02 like t_user;
-- 使用import将导出的数据导入到 t_user_import02表中
import  table t_user_import02 from '/aaa'

数据的导出

insert导出

Hive支持将select查询的结果导出成文件存放在文件系统。注意:导出操作是一个OVERWRITE覆盖操作，慎重。

    格式
        -- 将查询的结果 导出到本地文件夹
        insert overwrite local directory '本地文件夹名' select 查询语句;
        -- 将查询的结果 导出HDFS 不写local
        insert overwrite  directory 'HDFS文件夹名' select 查询语句;

导出到本地

-- 将t_user表中的数据 导出到本地文件夹 这是一个覆盖操作 会覆盖指定文件夹下的所有 如果指定为/ 那么废了
insert overwrite local directory '/data/output' select * from t_user;

--以这种方式导出的文件是使用的是默认分隔符 '\001'
--我们可以自己指定分隔符
insert overwrite local directory '/data/output2'
row format delimited fields terminated by ',' 
select * from t_user;

导出到HDFS

-- 不写local导出到hdfs上 实际上使用的是 hadoop配置文件中参数fs.default.name设置的值
insert overwrite  directory '/data/output2'
row format delimited fields terminated by ',' 
select * from t_user;

其他导出方式

只要将数据获取出来保存即可 
可以选择使用hadoop下载命令 
 hdfs dfs -get /user/hive/warehouse/hive01.db/t_user  /data
 可以使用hive shell命令
 hive  -e  "use hive01 ; select * from t_user ;"  >>  res.data  保存sql结果
 Export导出到HDFS上
 export table t_user to '/aaa';