大数据笔记--Hive（第二篇）

目录

一、基本SQL

1、SQL的执行方式

2、注意问题

3、可能错误

二、基本SQL命令

三、基本表结构

1、内部表和外部表

2、分区表

3、分桶表

4、分桶表insert可能出现问题

四、数据类型

1、概述

2、复杂类型

i、array类型

ii、map类型

iii、struct类型

---

一、基本SQL

1、SQL的执行方式

通过hive -e的方式来执行指定的SQL，例如hive -e 'create database demo;'

通过hive -f的方式来执行指定的SQL脚本，例如hive -f test.sql

进入Hive的命令行里来执行指定的SQL

2、注意问题

①、如果不指定，那么Hive默认将数据放在HDFS的/user/hive/warehouse目录下

②、在Hive中，每一个database都对应了一个单独的目录

③、在Hive启动的时候，自带一个default库。如果在建表的时候没有指定，那么默认也是将表放在default库下

④、alter database可以修改指定库的属性，但是不能修改库的库名以及存储位置

⑤、在Hive中，没有主键的概念，不支持主键

⑥、在Hive中，每一个table也对应了一个单独的目录

⑦、在Hive中，需要在建表的时候指定字段之间的间隔符号

⑧、insert into表示向表中追加数据；insert overwrite表示将表中的清空之后再添加当前的数据(覆盖)

⑨、需要注意的是，Hive中的数据会以文件的形式落地到HDFS上，在Hive的默认文件格式(textfile - 文本)下，不支持修改(update和delete)操作。如果需要Hive支持修改操作，那么需要在建表的时候指定文件格式为orc格式。但是在实际开发中，因为一般利用Hive存储历史数据，所以很少或者根本不对Hive中的数据进行修改，因此一般不适用orc格式。另外，orc格式虽然支持update和delete操作，但是效率非常低

3、可能错误

Ended Job = job_1647498932542_0003 with errors
Error during job, obtaining debugging information...
FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

 首先我们去hive下的log文件查看一下日志：

cd /home/software/hive-3.1.2/logs
cat hive.log

如果日志后面没有错误,我们在去yarn（hadoop01:8088）上查看：

根据相应的报错解决问题，我这里可能是内存不足。 

我们可以通过命令查看hive是否本地运行

set hive.exec.mode.local.auto;

设置为本地模式将其改成true即可
set hive.exec.mode.local.auto=true;

这里的修改只在本次的hive中生效，在重启hive进入后本地运行又变成false。

二、基本SQL命令

SQL	解释
create database demo;	创建demo库
create database if not exists demo4;	如果demo4库不存在，则创建
create database demo5 location '/demo5';	创建demo5库，同时指定存储位置
show databases;	查看所有的库
show databases like 'demo*';	查看demo开头的库
desc database demo;	描述demo库
desc database extended demo;	描述demo库的详细信息
use demo；	使用demo库
alter database demo set dbproperties ('data'='2022-3-17');	修改demo库的属性
drop database demo5；	删除demo5库
drop database demo3 cascade;	强制删除demo3库及其中的表
create table person (id int, name string, age int);	建立person表，包含id，name，age三个字段
insert into table person values(1, 'colin', 19);	插入数据
select * from person;	查询数据
load data local inpath '/home/person.txt' into table person;	从本地加载文件到Hive表中
drop table person;	删除表
create table person (id int, name string, age int) row format delimited fields terminated by ' ';	建表，指定字段之间的间隔符号为空格
create table p2 like person;	创建和person表结构一致的p2表
describe p2; 或者 desc p2;	描述p2
show tables;	查看所有的表
insert into table p2 select * from person where age >= 18;	从person中查询数据，将age>=18的数据放到p2表中
create table if not exists p3 like person;	如果p3表不存在，则创建和person结构一致的p3表
from person insert overwrite table p2 select * where age >= 18 insert into table p3 select * where id < 5;	从person表中查询数据，然后将查询出来的age>=18的数据覆盖到p2表中，同时将id<5的数据追加到p3表中
create table if not exists p4 as select * from person where age < 18;	创建p4表，同时在建表的时候，将person表中age<18的数据放进去
insert overwrite local directory '/home/hivedata' row format delimited fields terminated by '\t' select * from person where age >= 18;	将person表中age>=18的数据查询出来放到本地磁盘的/home/hivedata目录下
insert overwrite directory '/person' row format delimited fields terminated by ',' select * from person where id >= 6;	将person表中id>=6的数据查询出来放到HDFS的地址路径下
alter table person rename to p1;	重命名表

三、基本表结构

1、内部表和外部表

①、如果在Hive中手动建表手动添加数据(包括insert和load方式)，那么这种表称之为内部表

②、如果在Hive中手动建表来管理HDFS上已经存在的数据，那么这种表称之为外部表

③、相对而言，在工程或者项目建立的初期，一般会建立外部表来管理HDFS上已经存在的数据；但是当外部表建立之后，不代表数据能够直接处理使用，还需要对数据进行抽取整理等清洗操作，来建立能够实际使用的内部表

④、建立外部表

首先我们在hdfs上建立一个orders目录，里面放入我们要管理的文件order.txt;

 然后再hive中创建外部表来管理数据

create external table orders(orderid int, orderdate string, productid int, num int) row format delimited fields terminated by ' ' location '/orders'; 

⑤、查看表的详细信息

desc extended orders;

 或者

desc formatted orders;        在其中寻找Table Type:EXTERNAL_TABLE

⑥、在Hive中，删除内部表的时候，这个表对应的目录也会从HDFS上删除；删除外部表的时候，只是删除了元数据，而这个表对应的目录没有从HDFS上移除

2、分区表

①、分区表的作用通常是对数据来进行分类

②、建立分区表

create table cities (id int, name string) partitioned by (province string) row format delimited fields terminated by ' ' ;

③、加载数据

首先建立数据：

l

oad data local inpath '/home/data/anhui.txt' into table cities partition(province='anhui');

load data local inpath '/home/data/jiangsu.txt' into table cities partition(province='jiangsu');

④、在Hive中，每一个分区对应一个单独的目录

⑤、如果在查询数据的时候，指定了分区字段，那么分区表的查询效率就会高于未分区的表；如果在查询数据的时候，进行了跨分区查询，那么此时未分区表的查询效率就要高于分区表

⑥、删除分区

alter table cities drop partition(province = 'henan');

⑦、手动添加分区

alter table cities add partition (province='henan') location '/user/hive/warehouse/demo.db/cities/province=henan';

⑧、修复表

msck repair table cities;

⑨、修改分区名

alter table cities partition(province='__HIVE_DEFAULT_PARTITION__') rename to partition (province='sichuan');

⑩、在Hive的分区表中，分区字段在原始数据中并不存在，而是在加载数据的时候来手动指定。如果分区字段在原始数据中存在，那么需要考虑动态分区

动态分区 - 将未分区表中的数据查询出来放到分区表中

建立临时表去管理原始数据

create table cities_tmp (tid int, tcity_name string, tprovince string) row format delimited fields terminated by ' ';

将数据加载到临时表中

load data local inpath '/home/data/cities.txt' into table cities_tmp;

关闭严格模式，开启动态分区

set hive.exec.dynamic.partition.mode=nonstrict;

将未分区表中的数据查询出来放到分区表中

insert into table cities partition(province) select tid, tcity_name, tprovince from cities_tmp distribute by tprovince;

多字段来进行分区：

在Hive中，也允许对多字段来进行分区。此时，前一个字段形成的目录会包含后一个字段形成的目录。

多字段分区通常用于对数据来进行多级分类，例如省市县，学生的年级和班级，商品的分类等

原始数据

1 1 1 tom
1 1 2 sam
1 1 3 bob
1 1 4 alex
1 2 1 bruce
1 2 2 cindy
1 2 3 jack
1 2 4 john
2 1 1 tex
2 1 2 helen
2 1 3 charles
2 1 4 frank
2 2 1 david
2 2 2 simon
2 2 3 lucy
2 2 4 lily

建立临时表来管理数据

create table students_tmp (grade int, class int, id int, name string) row format delimited fields terminated by ' ';

加载数据

load data local inpath '/home/data/student.txt' into table students_tmp;

根据年纪和班级，将学生区分开 - 建立分区表

create table student(id int, name string) partitioned by (grade int, class int) row format delimited fields terminated by ' ';

动态分区

insert into student partition(grade, class) select id, name, grade, class from students_tmp distribute by grade, class;

3、分桶表

①、分桶表的作用是对数据进行抽样

②、在实际生产过程中，当数据量比较大，且又希望对数据进行快速分析获取分析结果，并且还能够容忍一定程度上的分析误差的时候，可以考虑对数据进行抽样处理。需要注意的是，在抽样的时候，抽样字段和要分析的字段之间不能有关联性。例如年龄和身高之间就是又关联性的，姓名和身高之间是没有关联性的

③、步骤

在Hive中，分桶机制默认是不开启的，首先需要开启分桶机制

set hive.enforce.bucketing = true;

建立分桶表 - 桶数指定的越多，执行分桶的时候耗费的资源越多

create table students_bucket(id int, name string) clustered by (name) into 4 buckets row format delimited fields terminated by '\t';

在Hive中，向分桶表中添加数据的时候，只能使用insert方式而不能使用load方式，使用load方式不会对数据进行分桶

insert overwrite table students_bucket select id, name from student;

对数据进行抽样

select * from students_bucket tablesample (bucket 1 out of 2 on name);

④、bucket x out of y，x表示从第一个桶的第几行开始抽，y表示抽样步长，即每几行抽一次

⑤、每一个桶都会对应一个单独的结果文件

4、分桶表insert可能出现问题

在本地模式下进行insert：

 不能在本地运行作业:reducers的数目(= 4)大于1

在本地模式下reducers必须为0或者1个；所以不能使用本地模式；

将本地模式将其改成false
set hive.exec.mode.local.auto=false; 

 如果还报错，我们首先去找错误：我们可以到hadoop日志查看有没有错误信息：

cd /home/software/hadoop-3.1.3/logs/userlogs/

 如果有错误

org.apache.hadoop.yarn.exceptions.YarnRuntimeException: java.lang.NullPointerException

 我们修改yarn-site.xml加上

   <property>
        <name>yarn.resourcemanager.webapp.address.rm1</name>
        <value>hadoop01:8088</value>
    </property>
    <property>
        <name>yarn.resourcemanager.webapp.address.rm2</name>
        <value>hadoop03:8088</value>
    </property>
        <property>
                <name>yarn.nodemanager.vmem-pmem-ratio</name>
                <value>3.0</value>
        </property>

四、数据类型

1、概述

在Hive中，提供了非常丰富的数据类型，大概可以分为两类：基本类型和复杂类型

基本类型包含：tinyint，smallint，int，bigint，float，double，boolean，string，timestamp，binary

复杂类型包含：array，map和struct

2、复杂类型

i、array类型

数组类型，对应了Java中的数组或者集合(List和Set)类型

原始数据：

1 bob,alex lucy,lily,jack
2 tom,sam,smith rose,john
3 peter,bruce david,kathy
4 helen,eden,iran cindy,grace,mike

建表语句：

create table battles (id int, groupa array<string>, groupb array<string>) row format delimited fields terminated by ' ' collection items terminated by ',';

加载数据：

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

非空查询：

select groupa[2] from battles where groupa[2] is not null;

ii、map类型

映射类型，对应了Java中的映射(Map)类型

 原始数据：

1 tom,87
2 job,69
3 alex,72
4 david,85
5 zoo,90

建表语句：

create table scores(id int, score map<string, int>) row format delimited fields terminated by ' ' map keys terminated by ',';

加载数据：

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

非空查询：

select score['david'] from scores where score['david'] is not null;

iii、struct类型

结构体类型，对应了Java中的对象，在使用的时候会将数据封装成一个类似于json串的结构

  原始数据：

1 tom,19,male sam,20,male
2 lily,18,female lucy,18,female
3 charles,19,male mark,21,male
4 joan,18,female james,20,male
5 linda,19,female matin,19,male

建表语句：

create table infos(groupid int, membera struct<name:string, age:int, gender:string>, memberb struct<name:string, age:int, gender:string>) row format delimited fields terminated by ' ' collection items terminated by ',';

加载数据：

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

非空查询：

select membera.name from infos;