大数据开发Hive中数据库的操作（第十八篇）

落叶飞逝的恋
90 阅读13分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第5天，点击查看活动详情

一、Hive数据库相关操作

这个数据的结构信息是存在在DBS表结构中

hive的数据都是存储在hdfs上，那这里的default数据库在HDFS上是如何体现的？

在 hive-site.xml 中有一个参数 hive.metastore.warehouse.dir

<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
</property>

http://192.168.234.100:9870/explorer.html#/user/hive/warehouse

image-20221007155707239

1.1、创建新的数据库

create database test;

image-20221007155943853

image-20221007155907695

二、Hive中表的操作

注意了：表中的数据是存储在hdfs中的，但是表的名称、字段信息是存储在metastore中的。这个表的结构信息是存在在TBLS表结构中，列的信息存储在COLUMNS_V2表结构中

  1. 创建表

    create table t1(id int);

  2. 查看表信息

    show tables;

  3. 查看表结构

    desc t1;

  4. 查看表的创建信息

    show create table t1;

    image-20221007160445849

  5. 修改表名

    alter table t1 rename to t1_bak;

  6. 加载数据

    在root目录下创建文件hivedata.data。里面的内容1 2 3 4

    load data local inpath '/root/hivedata.data' into table t1;

    image-20221007222517074

    查询加载的数据

    image-20221007222632816

  7. 表增加字段

    alter table t1 add columns(name string);

  8. 表增加注释

    create table t2(age int comment '年龄') comment '测试';

    image-20221007223125000

    此时发现，注释都是乱码的。因为hive数据库里面的表都是latin1编码的，中文本来就会显示乱码，但是又不能修改

    整个数据库里面所有表的编码，否则在使用hive的时候会出问题，那么只有考虑把存储字段注释和表注释相关的表的编码改为utf8。

    CREATE TABLE `COLUMNS_V2` (
  `CD_ID` bigint(20) NOT NULL,
  `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `TYPE_NAME` mediumtext,
  `INTEGER_IDX` int(11) NOT NULL,
  PRIMARY KEY (`CD_ID`,`COLUMN_NAME`),
  KEY `COLUMNS_V2_N49` (`CD_ID`),
  CONSTRAINT `COLUMNS_V2_FK1` FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

    其实只要修改表COLUMNS_V2和TABLE_PARAMS这两张表的编码。

    alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;

    alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

  9. hive的加载数据的分隔符

    hive是有默认的分隔符的，默认的行分隔符是 '\n' ，就是换行符，而默认的列分隔符呢，是 \001 。 \001 这个是ASCII码中的特殊不常使用的不可见字符，在文本中我们可以通过 ctrl+v 和 ctrl+a 来输入 \001。

    其实我们在创建表的时候指定一下分隔符就可以了。

    create table t3_new(
id int comment 'ID',
stu_name string comment 'name',
stu_birthday date comment 'birthday',
online boolean comment 'is online'
)row format delimited 
fields terminated by '\t' 
lines terminated by '\n';

三、Hive的数据类型

hive作为一个类似数据的框架，也有自己的数据类型，便于存储、统计、分析。hive中主要包含两大数据类型

  1. 基本数据类型

    int、string、boolean、double等

  2. 复合数据类型

    array、map、struct等

3.1、基本数据类型
数据类型开始支持版本
tinyint~
smallint~
int/integer~
bigint~
float~
double~
decimal~
timestamp0.8.0
date0.12.0
string~
varchar0.12.0
char0.13.0
boolean~
3.2、复合数据类型
数据类型开始支持版本格式
array0.14.0array<data_type>
map0.14.0map<primitive_type,data_type>
struct~struct<col_name:data_type, ...>
3.3、复合数据类型Array的应用

程序员有多个编程技能，比如会html、css、java等。每个程序员的技术栈是不一样的，如果每个技能栈都在表里面增加一列，这样是不合适的，后期学了新的技能，就需要新增列存储新的技能树了。

如果我们把技能树都拼接成一个字符串保存到一个字段中，这样针对存储层面来说没有问题，但是后期需要根据新增技能树而修改字段，这样操作起来不方便。这时候，在Hive中，可以使用Array来存储。数组中的元素怎么分割呢？通过 collection items terminated by ',' 指定的

create table coder(id int,
                   name string,
                   skill array<string>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
collection items terminated by '|';

image-20221012231355303

测试文件数据：

1,zhangsan,html|css|vue
2,lisi,java|c#

进行加载数据

load data local inpath '/root/data/coder.data' overwrite into table coder;

image-20221012231715797

image-20221012232847967

查询数组中的某一个元素，使用arrayName[index]

select id,name,skill[0] from coder;

image-20221012233020046

3.4、复合数据类型Map的应用

map集合里面存储的是键值对，每个键值对属于map集合的一个item。比如：有两个学生zhangsan、lisi，每个学生有语文、数学、英语，成绩如下：

1,zhangsan,chinese:80|math:90|english:100
2,lisi,chinese:89|english:70|math:88

create table student(id int,
                   name string,
                   subjects map<string,int>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
collection items terminated by '|'
map keys terminated by ':';

load data local inpath '/root/data/stu.data' overwrite into table student;

image-20221012233837360

查询所有学生的语文和数学成绩

select id,name,subjects['chinese'],subjects['math'] from student;

image-20221012233951836

3.5、复合数据类型Struct的应用

复合数据类型有点像Java中的对象

create table stu2(id int,
                   name string,
                   address struct<home_addr:string,office_addr:string>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
collection items terminated by '|'
lines terminated by '\n';

测试数据

1,zhangsan,bj|sh
2,lisi,sz|yz

加载数据

load data local inpath '/root/data/stu2.data' into table stu2;

image-20221015133742657

单独查询数据；

select id,name,address.home_addr from stu2;

image-20221015134641659

3.6、Struct和Map的区别
MapStruct
可以随意增加k-v对的个数k-v个数是固定的
在建表语句中需要指定k-v的类型建表语句中需要指定好所有的属性名称和类型
通过[]取值通过.(点)取值，类似java中的对象属性引用
源数据中需要带有k-v源数据中只需要有v即可

总体而言还是map比较灵活，但是会额外占用磁盘空间，因为他比struct多存储了数据的key。struct只需要存储value，比较节省空间，但是灵活性有限，后期无法动态增加k-v

3.7、思考题

在mysql中有一张表student(id,name)，还有一张表address(stu_id,home,school)，还有联系方式表(stu_id,mine,others)。如果把这三张表迁移到hive中，如何迁移？

  1. 一对一的迁移，mysql中有几张表，hive中就创建几张表

    优点：迁移成本低，包括DDL和业务逻辑，几乎不需要修改，可以直接使用

    缺点：产生大量的表连接，造成查询慢

  2. 一对多，mysql中的多张关联表可以创建为hive中的一张表

    优点：减少表连接操作

    缺点：迁移成本高，需要修改原有的业务逻辑

四、Hive中的表类型

  1. 内部表
  2. 外部表
  3. 分区表
  4. 桶表
4.1、内部表

hive中的默认表类型，表数据默认存储在warehouse目录中。在加载数据的过程中，实际数据会被移动到warehouse目录中。删除表时，表中的数据和元数据将会被同时删除

4.2、外部表

建表语句中包含External的表叫做外部表。外部表在加载数据的时候，实际数据并不会移动到warehouse目录中，只是与外部数据建立一个链接（映射关系）。当删除一个外部表时，只删除元数据，不删除表中的数据，仅删除表和数据之间的链接

hive.apache.org/

image-20221015145647514

cwiki.apache.org/confluence/…

cwiki.apache.org/confluence/…

create external table external_table(
  key string
)location '/data/external';

image-20221015152252510

hdfs查看创建的目录

hdfs dfs -ls /data/external

image-20221015152422940

内部表与外部表可以互相转换

  1. 内部表转外部表

    alter table 表名 set tblproperties (‘external’=‘true’);

  2. 外部表转内部表

    alter table 表名 set tblproperties (‘external’=‘false’);

在实际工作中，我们在hive中创建的表95%以上的都是外部表。大致工作流程如下：

  1. 先通过Flume采集数据，把数据上传到hdfs
  2. 然后在hive中创建外部表和hdfs上的数据绑定关系
  3. 这样就可以使用sql查询数据了。省略了load数据这步。因为先有数据，才创建的表
4.3、创建内部分区表

web服务器每天都产生一个日志数据文件，Flume把数据采集到HDFS中，每一天的数据存储到一个日期目录中，我们如果想查询某一天的数据的话，hive执行的时候默认会对所有文件扫描一遍，然后再过滤出来我们想要查询的那一天的数据（效率很低）

所有我们可以在让hive查询的时候，根据你要查询的日期，直接定位到对应的日期目录，这样就可以直接查询满足条件的数据了，效率进行了质的提升

此时我们就可以使用分区表了。分区可以理解为分类，通过分区把不同类型的数据放到不同目录中。

分区的标准就是指定分区字段，分区字段可以有一个或者多个，根据咱们刚才举得例子，分区字段就是日期

创建分区表

使用partitioned by指定区分字段

create table partition_demo(id int,
                   name string)
                   partitioned by (create_date string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
lines terminated by '\n'

image-20221016130219977

image-20221016124658134

数据格式：

1,张三
2,lisi

向分区表中加载数据（注意：在这里添加数据需要指定分区信息）

load data local inpath '/root/data/partition.data' into table partition_demo partition(create_date='20221016');

image-20221016125658093

image-20221016130442949

在hdfs存储

image-20221016130722625

  1. 手动在表中添加分区

    alter table partition_demo add partition(create_date='20221017');

    此时hdfs中多了一个目录，只不过这个分区目录中没有数据

  2. 删除分区

    alter table partition_demo drop partition(create_date='20221017');

根据分区条件查询数据

select * from partition_demo where create_date='20221016';

4.4、创建外部分区表
create external table ex_par(id int,
                   name string)
                   partitioned by (create_date string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
lines terminated by '\n'
location '/data/ex_par';

image-20221016150556698

加载数据

load data local inpath '/root/data/partition.data' into table ex_par partition(create_date='20221016');

image-20221016150727119

image-20221016150856955

外部分区表删除，只会删除元数据信息，不会删除hdfs里面的数据

绑定数据

实际工作中，我们把数据上传到了hdfs，如何进行绑定关系呢？

alter table ex_par add partition(create_date='20221016') location '/data/ex_par/create_date=20221016'

load data命令

load data…partition这条命令做了两件事情

  1. 上传数据

    hdfs dfs -mkdir /data/ex_par/create_date=20221016

    hdfs dfs -put 源数据目录 /data/ex_par/create_date=20221016（新的数据目录）

  2. 添加分区(绑定数据和分区之间的关系)

    alter table ex_par add partition(create_date='20221016') location '/data/ex_par/create_date=20221016'

五、桶表

桶表是对数据进行哈希取值，然后放到不同文件中存储。物理上，每个桶就是表（或者分区）里的一个文件

create table bucket_tb(id int) clustered by (id) into 4 buckets;

image-20221016154703441

这个时候往桶中加载数据的时候，就不能使用load data的方式了，而是需要使用其他表中的数据，那么给桶表加载数据的写法就有了新的变化了。类似的写法：

insert into table ... select..from...;

注意：在插入数据之前需要先设置开启桶操作，不然数据无法分到不同的桶里面。其实这里的分桶就是设置reduce任务的数量，因为你分了多少个桶，最终结果就会产生多个文件，最终结果文件的数量就和reduce任务的数量是挂钩的。设置完 set hive.enforce.bucketing = true 可以自动控制reduce的数量从而适配bucket的个数

创建数据bucket.data

1
2
3
4
5
6

创建普通的内部表

create table bucket_source(id int);

往普通的内部表里加载数据

load data local inpath '/root/data/bucket.data' into table bucket_source;

image-20221016155608205

image-20221016155925927

把bucket_source的数据加载到桶表里面

insert into table bucket_tb select id from bucket_source where id is not null;

上面语句会生成mapreduce任务

hive> insert into table bucket_tb select id from bucket_source where id is not null;
Query ID = root_20221016160146_99ffa73a-75f7-4337-b161-f1a9b3e3acab
Total jobs = 2
Launching Job 1 out of 2
Number of reduce tasks determined at compile time: 4
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1665893448730_0001, Tracking URL = http://bigdata01:8088/proxy/application_1665893448730_0001/
Kill Command = /root/software/hadoop-3.3.4/bin/mapred job  -kill job_1665893448730_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 4
2022-10-16 16:03:06,998 Stage-1 map = 0%,  reduce = 0%
2022-10-16 16:03:45,958 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.51 sec
2022-10-16 16:04:34,686 Stage-1 map = 100%,  reduce = 50%, Cumulative CPU 10.96 sec
2022-10-16 16:04:35,722 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 19.02 sec
MapReduce Total cumulative CPU time: 19 seconds 20 msec
Ended Job = job_1665893448730_0001
Loading data to table default.bucket_tb
Launching Job 2 out of 2
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1665893448730_0002, Tracking URL = http://bigdata01:8088/proxy/application_1665893448730_0002/
Kill Command = /root/software/hadoop-3.3.4/bin/mapred job  -kill job_1665893448730_0002
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 1
2022-10-16 16:06:04,508 Stage-3 map = 0%,  reduce = 0%
2022-10-16 16:06:29,015 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 2.08 sec
2022-10-16 16:07:02,718 Stage-3 map = 100%,  reduce = 100%, Cumulative CPU 4.83 sec
MapReduce Total cumulative CPU time: 4 seconds 830 msec
Ended Job = job_1665893448730_0002
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 4   Cumulative CPU: 19.02 sec   HDFS Read: 31658 HDFS Write: 864 SUCCESS
Stage-Stage-3: Map: 1  Reduce: 1   Cumulative CPU: 4.83 sec   HDFS Read: 9848 HDFS Write: 157 SUCCESS
Total MapReduce CPU Time Spent: 23 seconds 850 msec
OK
Time taken: 338.091 seconds

此时我们可以去hdfs里面查看4个文件

image-20221016160932689

此时我们可以看到是4个文件。

hdfs dfs -cat /user/hive/warehouse/bucket_tb/000000_0

image-20221016161100243

桶表的作用

  1. 数据抽样

    假如我们使用的是一个大规模的数据集,我们只想去抽取部分数据进行查看。使用bucket表可以变得更加的高效

    select * from bucket_tb tablesample(bucket 1 out of 4 on id);

    tablesample是抽样语句 语法解析：

    TABLESAMPLE(BUCKET x OUT OF y ON column)

    • y尽可能是桶表的bucket数的倍数或者因子
    • y必须要大于等于x y表示是把桶表中的数据随机分为多少桶 x表示取出第几桶的数据

  2. 提高查询效率

    join查询,可以避免产生笛卡尔积的操作

    select a.id,a.name,b.addr from a join b on a.id = b.id;

    如果a表和b表已经是分桶表，而且分桶的字段是id字段，那么做这个操作的时候就不需要再进行全表笛卡尔积了，因为分桶之后相同规则的id已经在相同的文件里面了。

六、视图

视图就是一张虚拟的表，它是对数据库的一个逻辑表示。主要作用：使用视图可以降低查询的复杂度。

6.1、创建视图
create view v1 as select id,name from ex_par;

查询视图：

select * from v1;

image-20221016161754757

它在hdfs是没有存储的，它在元数据里面是这样的

image-20221016162046713

删除视图：

drop view 视图名

avatar
文章
阅读
粉丝