hive第一天

1.准备

• 安装
• 启动
• 无论本地连接还是远程连接 都要启动元数据服务 检查端口 9083 正常
• hive --service metastore &

1.1本地连接

• bin/hive 本地启动
• show databases;
• show tables;

create table if not exists tb_a(
	uid int,
	name string,
	sal double,
	birthday date
);

hive>desc tb_a; --查看表结构

OK
uid                     int     
name                    string  
sal                     double 
birthday                date 

1.2远程连接方式

• 开启服务
  需要开启远程连接服务，服务提供两个端口10000 10002（web页面）

hiveserver2 & --后台开启
--查看端口
[root@linux001 apps]# netstat -nltp | grep 10000
tcp6       0      0 :::10000                :::*                    LISTEN      2584/java           
[root@linux001 apps]# netstat -nltp | grep 10002
tcp6       0      0 :::10002                :::*                    LISTEN      2584/java

亲求WEB页面

• 远程连接
  bin/beeline

beeline> !connect jdbc:hive2://linux001:10000
--输入用户名root
Enter username for jdbc:hive2://linux001:10000: root
--输入密码 回车
Enter password for jdbc:hive2://linux001:10000: 

show databases ;
show tables ;
desc tb_a ;
INFO  : Concurrency mode is disabled, not creating a lock manager
+-----------+------------+----------+
| col_name  | data_type  | comment  |
+-----------+------------+----------+
| uid       | int        |          |
| name      | string     |          |
| sal       | double     |          |
| birthday  | date       |          |
+-----------+------------+----------+

1.3入门

hive处理HDFS上的结构化数据

Found 1 items
-rw-r--r--   3 root supergroup         43 2021-05-14 09:41 /data/user/user.txt
1,zss,M,23
2,lss,F,33
3,wbb,M,45
4,tg,M,54

1. 根据数据创建表

create table tb_user(
    uid int ,
    name string ,
    gender string ,
    age int 
) 
row format  delimited  fields terminated by ',' -- 切割符
location '/data/user/'  -- 数据 
;
desc tb_user ;
+-----------+------------+----------+
| col_name  | data_type  | comment  |
+-----------+------------+----------+
| uid       | int        |          |
| name      | string     |          |
| gender    | string     |          |
| age       | int        |          |
+-----------+------------+----------+
select * from tb_user ;
+--------------+---------------+-----------------+--------------+
| tb_user.uid  | tb_user.name  | tb_user.gender  | tb_user.age  |
+--------------+---------------+-----------------+--------------+
| 1            | zss           | M               | 23           |
| 2            | lss           | F               | 33           |
| 3            | wbb           | M               | 45           |
| 4            | tg            | M               | 54           |
+--------------+---------------+-----------------+--------------+
--统计每个性别的平均年龄
 select gender,avg(age)from tb_user group by gender  ;  -- 底层是MR 程序  以gender为KEY age为value ,reduce聚合统计
+---------+---------------------+
| gender  |         _c1         |
+---------+---------------------+
| F       | 33.0                |
| M       | 40.666666666666664  |
+---------+---------------------+

desc  formatted  tb_user ;
+-------------------------------+----------------------------------------------------+-----------------------+
|           col_name            |                     data_type                      |        comment        |
+-------------------------------+----------------------------------------------------+-----------------------+
| # col_name                    | data_type                                          | comment               |
| uid                           | int                                                |                       |
| name                          | string                                             |                       |
| gender                        | string                                             |                       |
| age                           | int                                                |                       |
|                               | NULL                                               | NULL                  |
| # Detailed Table Information  | NULL                                               | NULL                  |
| Database:                     | default                                            | NULL                  |
| OwnerType:                    | USER                                               | NULL                  |
| Owner:                        | root                                               | NULL                  |
| CreateTime:                   | Fri May 14 09:46:33 CST 2021                       | NULL                  |
| LastAccessTime:               | UNKNOWN                                            | NULL                  |
| Retention:                    | 0                                                  | NULL                  |
| Location:                     | hdfs://doit01:8020/data/user                       | NULL                  |
| Table Type:                   | MANAGED_TABLE                                      | NULL                  |
| Table Parameters:             | NULL                                               | NULL                  |
|                               | bucketing_version                                  | 2                     |
|                               | numFiles                                           | 1                     |
|                               | totalSize                                          | 43                    |
|                               | transient_lastDdlTime                              | 1620956793            |
|                               | NULL                                               | NULL                  |
| # Storage Information         | NULL                                               | NULL                  |
| SerDe Library:                | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe | NULL                  |
| InputFormat:                  | org.apache.hadoop.mapred.TextInputFormat           | NULL                  |
| OutputFormat:                 | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | NULL                  |
| Compressed:                   | No                                                 | NULL                  |
| Num Buckets:                  | -1                                                 | NULL                  |
| Bucket Columns:               | []                                                 | NULL                  |
| Sort Columns:                 | []                                                 | NULL                  |
| Storage Desc Params:          | NULL                                               | NULL                  |
|                               | field.delim                                        | ,                     |
|                               | serialization.format                               | ,                     |
+-------------------------------+----------------------------------------------------+-----------------------+

2.DDL

2.1 数据库

-- 建库 / 列举
create  database if not exists  doit23 ;
show  databases ;  --****重点 
+----------------+
| database_name  |
+----------------+
| default        |
| doit23         |
+----------------+

-- 查看库 / 详细详细
desc  database doit23 ;
desc  database extended doit23 ; -- 详细详细 属性详细(没有看不到，可以看下面的修改数据库添加属性)

-- 删除库 
drop database doit23 ;
drop database db_hive cascade;   -- 强制删除一个有表的数据库
-- 切换库  / 查看当前使用的数据库
use  doit23 ;  -- 切换   --****重点
select current_database() ; -- 查看当前使用的数据库  --****重点
-- 修改数据库
alter database doit23 set dbproperties('author'='doit23') ;

2.2表操作

2.2.0 数据类型

数据类型 , 约束列字段数据 , 安全

Hive数据类型	Java数据类型	长度	例子
TINYINT	byte	1byte有符号整数	20
SMALLINT	short	2byte有符号整数	20
INT	int	4byte有符号整数	20
BIGINT	long	8byte有符号整数	20
BOOLEAN	boolean	布尔类型，true或者false	TRUE FALSE
FLOAT	float	单精度浮点数	3.14159
DOUBLE	double	双精度浮点数	3.14159
STRING	string	字符系列。可以指定字符集。可以使用单引号或者双引号。	‘now is the time’ “for all good men”
TIMESTAMP		时间类型
BINARY		字节数组

集合类型 复杂类型

array 数组

map 键值对

struct结构体 Bean

2.2.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]   row format delimited fields terminated by “分隔符”
[STORED AS file_format] 
[LOCATION hdfs_path]

– EXTERNA 外部的

– COMMENT 注释

– PARTITIONED BY 将数据分区管理 [文件夹] 比如以日期字段分区 , 相同的日期数据会分到同一个文件夹中欧冠 ,

– CLUSTERED BY 分桶字段

– SORTED BY排序

– LOCATION 指定表的位置(数据的位置)

– STORED AS file_format – text sequence … 其他的文件格式

2.2.1.1 普通表

create table  tb_teacher(
    id int ,
    name string ,
    sal double ,
    age int 
);
默认位置
hdfs://linux001:8020//user/hive/warehouse/doit23.db/tb_teacher

2.2.1.2 外部表 / 内部表

1 普通表

2 导入数据

3 删除表 数据删除(表目录和数据删除)

• 数据导入方式一 location 指定数据的位置
• 数据导入方式二 将数据上传到表目录下

drop table tb_uu ;

1. 创建外部表

create external table tb_1(
    id int ,
    name string ,
    gender string ,
    age int
)
row format delimited fields terminated by ','
location '/data/'
;

create external table tb_2(
    id int ,
    name string ,
    gender string ,
    age int
)
row format delimited fields terminated by ','
location '/data/'
;

2. 导入数据 直接指定数据的位置

3. 删除表 数据存在

总结 :

hive中创建表 默认是 内部表 , 删除表数据会删除 , 表–>表目录存储表数据

external修饰的表 外部表 , 共享数据 , 重要的数据使用 , 删除表 数据不会删除 location 指定数据的位置
场景:

原始数据,外部表

共享数据: 外部表

内部表和外部表转换

业务表 , 统计表 ,结果表 , 中间表 内部表 管理表 默认表

alter table tb_2 set tblproperties('EXTERNAL'='false') ;
alter table tb_2 set tblproperties('EXTERNAL'='true') ;

alter table tb_2 set tblproperties('EXTERNAL'='false') ;
alter table tb_2 set tblproperties('EXTERNAL'='true') ;

2.2.1.3 分区表

hive 根据建表的语法 ， 指定到表的路径位置 ， 数据是存储在表的路径下的

查询数据的时候 ， 加载表路径下的所有的文件 ，全部扫描加载 ， where city=北京

我们有大量的静态文件 , 数据是按照维度分文件存储,使用静态分区 , 优化表 ,提升查询效率
北京.txt

南京.txt

上海.txt

为了提高HIive的查询效率

静态分区

地域查询 日期查询 标签查询

数据如下:

bj.txt

1,log1,bj,2021-05-14
2,log2,bj,2021-05-14
3,log3,bj,2021-05-14
4,log4,bj,2021-05-14
5,log5,bj,2021-05-14

nj.txt

1,log1,nj,2021-05-13
2,log2,nj,2021-05-12
3,log3,nj,2021-05-13
4,log4,nj,2021-05-13
5,log5,nj,2021-05-13

sh.txt

1,log1,sh,2021-05-13
2,log2,sh,2021-05-12
3,log3,sh,2021-05-13
4,log4,sh,2021-05-13

-- 存储数据
-- 提升查询  分区表  以城市为分区
create table tb_log1(
    id int ,
    log string ,
    city string ,
    dt string
)
partitioned  by (city_name string) --  表的字段
row format delimited fields terminated by ',' ;
-- 将数据导入到表中 local是访问本地路径的，去掉local则是访问web页面
load data local inpath "/data/log/bj.txt" into table tb_log1 partition (city_name='bj') ;
load data local inpath "/data/log/nj.txt" into table tb_log1 partition (city_name='nj') ;
load data local inpath "/data/log/sh.txt" into table tb_log1 partition (city_name='sh') ;
+-------------+--------------+---------------+-------------+--------------------+
| tb_log1.id  | tb_log1.log  | tb_log1.city  | tb_log1.dt  | tb_log1.city_name  |
+-------------+--------------+---------------+-------------+--------------------+
| 1           | log1         | bj            | 2021-05-14  | bj                 |
| 2           | log2         | bj            | 2021-05-14  | bj                 |
| 3           | log3         | bj            | 2021-05-14  | bj                 |
| 4           | log4         | bj            | 2021-05-14  | bj                 |
| 5           | log5         | bj            | 2021-05-14  | bj                 |
| 1           | log1         | nj            | 2021-05-13  | nj                 |
| 2           | log2         | nj            | 2021-05-12  | nj                 |
| 3           | log3         | nj            | 2021-05-13  | nj                 |
| 4           | log4         | nj            | 2021-05-13  | nj                 |
| 5           | log5         | nj            | 2021-05-13  | nj                 |
| 1           | log1         | sh            | 2021-05-13  | sh                 |
| 2           | log2         | sh            | 2021-05-12  | sh                 |
| 3           | log3         | sh            | 2021-05-13  | sh                 |
| 4           | log4         | sh            | 2021-05-13  | sh                 |
+-------------+--------------+---------------+-------------+--------------------+

select * from tb_log1 where city = 'bj' ;  普通字段 

select * from tb_log1 where city_name='bj' ;  分区字段查询  -- 筛选文件夹文件夹  

普通字段查询执行计划 , 加载所有的数据 再过滤

执行计划 , 查看SQL的执行原理

+----------------------------------------------------+
|                      Explain                       |
+----------------------------------------------------+
| STAGE DEPENDENCIES:                                |
|   Stage-0 is a root stage                          |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         TableScan                                  |
|           alias: tb_log1                           |
|           Statistics: Num rows: 3 Data size: 2940 Basic stats: COMPLETE Column stats: NONE |
|           Filter Operator                          |
|             predicate: (city = 'bj') (type: boolean) |
|             Statistics: Num rows: 1 Data size: 980 Basic stats: COMPLETE Column stats: NONE |
|             Select Operator                        |
|               expressions: id (type: int), log (type: string), 'bj' (type: string), dt (type: string), city_name (type: string) |
|               outputColumnNames: _col0, _col1, _col2, _col3, _col4 |
|               Statistics: Num rows: 1 Data size: 980 Basic stats: COMPLETE Column stats: NONE |
|               ListSink                             |
|                                                    |
+----------------------------------------------------+

按照分区字段查询执行计划 , 只加载指定文件夹下的数据

+----------------------------------------------------+
|                      Explain                       |
+----------------------------------------------------+
| STAGE DEPENDENCIES:                                |
|   Stage-0 is a root stage                          |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         TableScan                                  |
|           alias: tb_log1                           |
|           Statistics: Num rows: 1 Data size: 1050 Basic stats: COMPLETE Column stats: NONE |
|           Select Operator                          |
|             expressions: id (type: int), log (type: string), city (type: string), dt (type: string), 'bj' (type: string) |
|             outputColumnNames: _col0, _col1, _col2, _col3, _col4 |
|             Statistics: Num rows: 1 Data size: 1050 Basic stats: COMPLETE Column stats: NONE |
|             ListSink                               |
|                                                    |
+----------------------------------------------------+
17 rows selected (0.313 seconds)

3 DML

3.1数据导入方式

3.1.1 hdfs dfs -put/mv data.data /表目录下

3.1.2 location 指定数据的位置

3.1.3 load 命令

-- 将本地的文件上传到表的目录下
load  data local  inpath  '/data/user.txt'  into table tb_load1 ;  -- 本地目录下的数据
-- load的HDFS数据, 将HDFS指定目录下的数据 mv到表目录下
load  data   inpath  '/data/user.txt'  into table tb_load1 ;  -- HDFS文件中的数据

3.1.4 create … as… selecte ;

create table res as select * from tb_name ; – 全量导入结果

create table  tb_load2 as select id , name , age from tb_load1 ;
+--------------+----------------+---------------+
| tb_load2.id  | tb_load2.name  | tb_load2.age  |
+--------------+----------------+---------------+
| 1            | zss            | 23            |
| 2            | lss            | 33            |
| 3            | wbb            | 45            |
| 4            | tg             | 54            |
| 5            | xg             | 33            |
+--------------+----------------+---------------+

3.1.5 insert 数据

insert into tb_load2  values(6,'tt',34);
insert into tb_load2  values(9,'tt2',34),(7,'ff',36),(8,'yy',24) ;
每次插入一条数据(多条)都会产生一个MR job , 输出一个小文件 , 在HDFS 中不要存储小文件
1) 给namenode带来压力
2) MR不擅长处理小文件

增量导入数据 的时候需要使用insert 方式

create  table  tb_load3(id int, name string , dt string,event string) ;

    数据 
    1,accout,age,name,url,appid,time,event
    创建一表  加载上面的数据 
     tag:   insert  into tb_load3   select id , name , dt , event from  where   ...

 insert into tb_load3  select id , name , current_date() , age from tb_load2 ;
+--------------+----------------+--------------+-----------------+
| tb_load3.id  | tb_load3.name  | tb_load3.dt  | tb_load3.event  |
+--------------+----------------+--------------+-----------------+
| 1            | zss            | 2021-05-14   | 23              |
| 2            | lss            | 2021-05-14   | 33              |
| 3            | wbb            | 2021-05-14   | 45              |
| 4            | tg             | 2021-05-14   | 54              |
| 5            | xg             | 2021-05-14   | 33              |
| 6            | tt             | 2021-05-14   | 34              |
| 9            | tt2            | 2021-05-14   | 34              |
| 7            | ff             | 2021-05-14   | 36              |
| 8            | yy             | 2021-05-14   | 24              |
+--------------+----------------+--------------+-----------------+

3.1.6 覆盖导入

load data local inpath '/data/' overwrite into table tb_load1 ;
load data  inpath '/data/' overwrite into table tb_load1 ;

insert  overwrite table  tb_load2 select id , name , age from tb_load1 ;

3.1.7 import 数据的迁移?? 导出

必须导入的数据是export导出的数据

create table tb_imp like default.tb_load1  ;  根据另一个数据库的表创建新表
import table tb_imp from "/data/load1_tb/"   -- 导入export导出的数据

3.2 数据导出 (少)

3.2.1 hdfs dfs -get 表目录中的数据

0: jdbc:hive2://linux001:10000>  dfs -get /路径    --直接将数据下载到默认本用户的家目录下 

hdfs  dfs -get  下载数据

3.2.2Insert 将数据导出到文件夹中

-- 将数据导出到本地文件夹
insert overwrite local directory  '/data/out1'
select * from tb_load1 ;
/data/out1/00000_0
1zssM23
2lssF33
3wbbM45
4tgM54
5xgF33

insert overwrite local directory  '/data/out2'
row format delimited fields terminated by ':'
select * from tb_load1;
[root@doit01 out2]# cat 000000_0 
1:zss:M:23
2:lss:F:33
3:wbb:M:45
4:tg:M:54
5:xg:F:33

-- 将数据导出到HDFS文件夹中
insert overwrite  directory  '/data/out'
row format delimited fields terminated by ':'
select * from tb_load1;

3.2.3 hive 脚本

定时执行脚本

--hive  -e  "select * from tb_load1"  数据展示在控制台
vi sql.txt
    select * from tb_load1 ;
--hive  -f  sql.txt      - 数据展示在控制台
hive -f sql.sh   >>  load.txt   将结果数据 输出到文件中
hive -e "select * from tb_load1"    >>  load.txt   将结果数据 输出到文件中

vi a.sh 
#!/bin/bash
hive  -e  "select * from tb_user" 
sh  a.sh  执行  结果输出在控制台 
vi a.sh 
#!/bin/bash
hive  -e  "select * from tb_user"  >>  res.txt 
sh  a.sh  执行   输出结果到文件中

定时器 , 定时的执行脚本 
crontab -e  进入到定时任务编辑中 
* * * * *      sh  a.sh 
   时间
分 时 日 月 周
30 * * * * 
0 8 * * * 
0 8 * * 1-5 
0 8 * * 1,3,5

3.2.4 export 导出

export  table tb_load1  to  "/data/load1_tb/" ; 
[root@doit01 /]# hdfs  dfs -ls  /data/load1_tb
Found 2 items
-rw-r--r--   3 root supergroup       1382 2021-05-14 16:34 /data/load1_tb/_metadata
drwxr-xr-x   - root supergroup          0 2021-05-14 16:34 /data/load1_tb/data