hive的数据模型及数据类型

最新推荐文章于 2024-05-02 17:23:08 发布

杰灬杰

最新推荐文章于 2024-05-02 17:23:08 发布

阅读量3.8k

点赞数

本文链接：https://blog.csdn.net/qq_42493452/article/details/103177534

版权

1.Hive 数据抽象

hive 中主要包含以下几种数据模型：Database（数据库）、Table（表）、Partition（分区）、Bucket（桶）

Database（数据库）
Hive中的数据库包含一系列的数据库，每个数据库都对应于HDFS上的一个目录，默认的数据库为default，对应于HDFS目录是/user/hadoop/hive/warehouse，可以通过hive.metastore.warehouse.dir参数进行配置（hive-site.xml中配置）

Table（表）
Hive 中的表又分为内部表和外部表
Hive 中的每张表对应于HDFS上的一个目录，HDFS目录为：
/user/hadoop/hive/warehouse/[databasename.db]/table

Partition（分区）
Hive 中每个分区对应于HDFS上表文件夹的一个子文件夹，比如order_partition表中有一个分区event_month=2014-05，则分区的数据在hdfs中的存放目录为为/user/hadoop/hive/warehouse/[databasename.db]/order_partition/event_month=2014-05

Bucket（桶）
对指定的列计算其hash，根据hash值切分数据，目的是为了并行，每一个桶对应一个文件。比如将emp表empno列分散至10个桶中，首先对id列的值计算hash，对应hash值为0和10的数据存储的HDFS目录为：
/user/hadoop/hive/warehouse/[databasename.db]/emp/part-00000
而hash值为2的数据存储的HDFS 目录为：
/user/hadoop/hive/warehouse/[databasename.db]/emp/part-00002

2.Hive 数据数据类型

Hive 基本数据类型

图中红色字体的数据类型为hive中比较常用的

Hive 复杂数据类型

Array：数组，一组有序字段，字段的类型必须相同
Map：一组无序的键值对，键和值可以是任意类型，同一个map的键的类型必须相同，值的类型也必须相同
Struct：结构体，一组命名的字段，字段类型可以不相同

hive 分隔符

二、DDL 操作

DDL操作官方使用文档：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

1.Database DDL 操作

(1).创建

语法：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

[COMMENT database_comment]

[LOCATION hdfs_path]

[WITH DBPROPERTIES (property_name=property_value, ...)];

创建数据库test

对应的HDFS目录为：/user/hive/warehouse
Hive 中的数据库对应HDFS中的一个文件夹，文件夹命名格式为：数据库名.db

create database test;

如果test数据库不存在再创建

create database if not exists test1;

在指定路径下创建数据库

create database test2 location '/myhive/mydb';

创建数据库，并为数据库添加描述信息

create database test3 comment 'my test db'with dbproperties ('creator'='zhangsan','date'='2017-8-8');

(2).查询

查询所有的数据库

show databases;

查看以test开头的数据库

show databases like 'test*';

查看数据库的详细信息

desc database test3;

在上面的基础上如果想查看数据库更详细的信息，可以加个参数extended

desc database extended test3;

查看创建数据库sql语句

show create database test3;

切换数据库
如果不指定数据库，那么默认使用的就是default

use databasename;

(3).修改

语法：

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); -- (Note: SCHEMA added in Hive 0.14.0)

更改数据库的修改人

alter database test3 set dbproperties ('modifier'='lisi');

(4).删除

语法：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

删除数据库

删除数据库test3

drop database if exists test3;

删除时会报如下错误：
InvalidOperationException(message:Database test3 is not empty. One or more tables exist.)

如果数据库中有0或多个表时，不能直接删除，需要先删除表再删除数据库

如果想要删除含有表的数据库，在删除时加上cascade，表示级联删除（慎用），可以使用如下命令

drop database if exists test3 cascade;

2.Table DDL 操作

(1).创建

语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)

[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]

[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]

[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]

ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)

[STORED AS DIRECTORIES]

[

[ROW FORMAT row_format]

[STORED AS file_format]

| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)

]

[LOCATION hdfs_path]

[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)

[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

LIKE existing_table_or_view_name

[LOCATION hdfs_path];

data_type

: primitive_type

| array_type

| map_type

| struct_type

| union_type -- (Note: Available in Hive 0.7.0 and later)

primitive_type

: TINYINT

| SMALLINT

| INT

| BIGINT

| BOOLEAN

| FLOAT

| DOUBLE

| DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)

| STRING

| BINARY -- (Note: Available in Hive 0.8.0 and later)

| TIMESTAMP -- (Note: Available in Hive 0.8.0 and later)

| DECIMAL -- (Note: Available in Hive 0.11.0 and later)

| DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later)

| DATE -- (Note: Available in Hive 0.12.0 and later)

| VARCHAR -- (Note: Available in Hive 0.12.0 and later)

| CHAR -- (Note: Available in Hive 0.13.0 and later)

array_type

: ARRAY < data_type >

map_type

: MAP < primitive_type, data_type >

struct_type

: STRUCT < col_name : data_type [COMMENT col_comment], ...>

union_type

: UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later)

row_format

: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

[NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later)

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

file_format:

: SEQUENCEFILE

| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)

| RCFILE -- (Note: Available in Hive 0.6.0 and later)

| ORC -- (Note: Available in Hive 0.11.0 and later)

| PARQUET -- (Note: Available in Hive 0.13.0 and later)

| AVRO -- (Note: Available in Hive 0.14.0 and later)

| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

constraint_specification:

: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]

[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE

创建表

create table emp(

empno int,

ename string,

job string,

mgr int,

hiredate string,

sal double,

comm double,

deptno int

)row format delimited fields terminated by '\t';

导入数据

有一个emp.txt文件，里面的数据对应我们刚刚创建的表emp

7369 SMITH CLERK 7902 1980-12-17 800.00 207499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 307521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 307566 JONES MANAGER 7839 1981-4-2 2975.00 207654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 307698 BLAKE MANAGER 7839 1981-5-1 2850.00 307782 CLARK MANAGER 7839 1981-6-9 2450.00 107788 SCOTT ANALYST 7566 1987-4-19 3000.00 207839 KING PRESIDENT 1981-11-17 5000.00 107844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 307876 ADAMS CLERK 7788 1987-5-23 1100.00 207900 JAMES CLERK 7698 1981-12-3 950.00 307902 FORD ANALYST 7566 1981-12-3 3000.00 207934 MILLER CLERK 7782 1982-1-23 1300.00 108888 HIVE PROGRAM 7839 1988-1-23 10300.00

使用如下命令将emp.txt文件中的数据导入到表emp

load data local inpath '/home/hadoop/data/emp.txt' overwrite into table emp;

复制表（只拷贝表结构）

create table emp2 like emp;

复制表（拷贝表结构及数据，会运行MapReduce作业）

create table emp3 as select * from emp;

导入成功后查看表中数据

创建表emp到emp_copy，emp_copy中只包含三列：empno，ename，job

create table emp_copy as select empno,ename,job from emp;

(2).查询

查看数据库下的所有表

show tables;show tables 'emp*';

查看表结构

desc emp;

查看表结构的详细信息

desc extended emp;

上面查看到的表结构的详细信息格式较乱，可以使用formatted格式化查看（常用）

desc formatted emp;

查看表的创建语句

show create table emp;

(3).修改

语法：

ALTER TABLE table_name RENAME TO new_table_name;

修改表名emp2为emp_bak

alter table emp2 rename to emp_bak;

(4).删除

语法：

DROP TABLE [IF EXISTS] table_name [PURGE];

删除表emp_bak

drop table if exists emp_bak;

清空表中所有数据

truncate table emp_copy;

三、DML（Table）操作

DML 操作只是针对表的，DML 操作官方帮助文档
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML

1.内部表和外部表

(1).内部表

创建一张内部表 emp_managed

create table emp_managed as select * from emp;

查看表emp_managed在hdfs中是否存在

$ hadoop fs -ls /user/hive/warehouse/db_hive.db/

登录mysql（TBLS中存放了hive中的所有表）数据库，查看hive的元数据信息

select * from TBLS;

我们刚刚创建的一张内部表emp_managed

删除表emp_managed

drop table if exists emp_managed;

hive 中的emp_managed表会被删除

mysql 中hive的元数据被删除

表emp_managed 对应hdfs 中的文件夹被删除

(2).外部表

创建一张外部表

create external table emp_external(

empno int,

ename string,

job string,

mgr int,

hiredate string,

sal double,

comm double,

deptno int

)row format delimited fields terminated by '\t'

location '/hive_external/emp';

mysql 中查看hive的元数据

新创建的表emp_external中是没有数据的，我们将emp.txt文件上传到hdfs的/hive_external/emp目录下

$ hadoop fs -put emp.txt /hive_external/emp/

上传完成后，表emp_external就有数据了，使用sql查看

然后我们来删除这张表，它是一张外部表，注意和内部表有什么区别

hive 中表emp_external被删除

drop table if exists emp_external;

mysql 中元数据被删除

hdfs 中的文件并不会被删除

$ hadoop fs -ls /hive_external/emp/

总结：内部表与外部表的区别
如果是内部表，在删除时，MySQL中的元数据和HDFS中的数据都会被删除
如果是外部表，在删除时，MySQL中的元数据会被删除，HDFS中的数据不会被删除

2.表数据加载（load）

(1).加载数据到文件中

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

LOCAL：如果加上表示从本地加载数据，默认不加，从hdfs中加载数据
OVERWRITE：加上表示覆盖表中数据

从本地文件加载数据到hive表中
把emp.txt 文件加载到数据hive中的emp表中

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

如果再次执行一次load，那么表中的数据会有30条，会追加，可以加上overwrite在加载数据前删除原有数据

(2).从hdfs中加载数据到hive表中

在hdfs中创建目录/hive/data，然后将emp.txt文件上传到hdfs

$ hadoop fs -mkdir -p /data/hive

$ hadoop fs -put emp.txt /data/hive/

加载数据到表emp中

load data inpath '/hive/data/emp.txt' overwrite into table emp;

3.插入数据（insert … into）

向表 emp 中插入数据

insert into emp(empno,ename,job) values(1001,'TOM','MANAGER');insert into emp values(1002,'JOHN','CLERK',7786,'1995-8-28',1850.0,2.0,30);

4.查询数据（select）

emp 表中数据

查询部门编号为10的员工信息

select * from emp where deptno=10;

查询姓名为SMITH的员工

select * from emp where ename='SMITH';

查询员工编号小于等于7766的员工

select * from emp where empno <= 7766;

查询员工工资大于1000小于1500的员工

select * from emp where sal between 1000 and 1500;

查询前5条记录

select * from emp limit 5;

查询姓名为SCOTT或MARTIN的员工

select * from emp where ename in ('SCOTT','MARTIN');

查询有津贴的员工

select * from emp where comm is not null;

统计部门10下共有多少员工

select count(*) from emp where deptno=10;

结果：3

查询员工的最大、最小、平均工资及所有工资的和

select max(sal),min(sal),avg(sal),sum(sal) from emp;

结果：10300.0 800.0 2621.6666666666665 39325.0

查询每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

结果：
10 2916.6666666666665
20 2175.0
30 1566.6666666666667

查询平均工资大于2000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

结果：
10 2916.6666666666665
20 2175.0

查询员工的姓名和工资等级，按如下规则显示

sal小于等于1000，显示lower
sal大于1000且小于等于2000，显示middle
sal大于2000小于等于4000，显示high
sal大于4000，显示highest

select ename, sal, case when sal < 1000 then 'lower'when sal > 1000 and sal <= 2000 then 'middle'when sal > 2000 and sal <= 4000 then 'high'else 'highest' end from emp;

5.导出数据（insert…directory）

语法：

INSERT OVERWRITE [LOCAL] DIRECTORY directory1

[ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)

SELECT ... FROM ...

导出数据到本地

将表emp中的数据导出到本地目录/home/hadoop/data/tmp下

insert overwrite local directory '/home/hadoop/data/tmp' row format delimited fields terminated by '\t' select * from emp;

运行结果：到/home/hadoop/data/tmp目录下查看

导出数据到HDFS

将表emp中的数据导出到hdfs的/data/hive目录下

insert overwrite directory '/data/hive' row format delimited fields terminated by '\t' select * from emp;

结果：在hdfs中查看

Hadoop fs -cat /data/hive/000000_0;

6.分区表

Hive 可以创建分区表，主要用于解决由于单个数据表数据量过大进而导致的性能问题
Hive 中的分区表分为两种：静态分区和动态分区

(1).静态分区

静态分区由分为两种：单级分区和多级分区

单级分区

创建一种订单分区表

create table order_partition(

order_number string,

event_time string

)

partitioned by (event_month string)row format delimited fields terminated by '\t';

order.txt文件中数据如下

10703007267488 2014-05-01 06:01:12.334+01

10101043505096 2014-05-01 07:28:12.342+01

10103043509747 2014-05-01 07:50:12.33+01

10103043501575 2014-05-01 09:27:12.33+01

10104043514061 2014-05-01 09:03:12.324+01

将order.txt 文件中的数据加载到order_partition表中

方式一：使用load加载数据

load data local inpath '/home/hadoop/data/order.txt' overwrite into table order_partition partition (event_month='2014-05');

方式二：使用hadoop shell 加载数据

$ hadoop fs -mkdir -p /user/hive/warehouse/db_hive.db/order_partition/event_month=2014-06

$ hadoop fs -put /home/hadoop/data/order.txt /user/hive/warehouse/db_hive.db/order_partition/event_month=2014-06

上传完成后查看表order_partition

Select * from order_partition;

通过select查询可以看到并没有看到我们刚刚通过hdfs上传后的数据，原因是我们将文件上传到了hdfs，hdfs是有了数据，但hive中的元数据中还没有，执行如下命令更新后，再次查看数据

msck repair table order_partition;

对于分区表，不建议直接使用select * 查询，性能低，建议查询时加上条件，如果加上条件后它会直接从指定的分区中查找数据

select * from order_partition where event_month='2014-06';

多级分区：

创建表order_multi_partition

create table order_multi_partition(

order_number string,

event_time string）

partitioned by (event_month string, step string)

row format delimited fields terminated by '\t';

加载数据到表order_multi_partition

load data local inpath '/home/hadoop/data/order.txt' overwrite into table order_multi_partition partition (event_month='2014-05',step=1);

把step修改为2，再次加载数据

load data local inpath '/home/hadoop/data/order.txt' overwrite into table order_multi_partition partition (event_month='2014-05',step=2);

查看hdfs中的目录结构

hadoop fs -ls /user/hive/warehouse/db_hive.db/order_multi_partition/event_month=2014-05

单机分区和多级分区唯一的区别就是多级分区在hdfs中的目录为多级

(2).动态分区

hive 中默认是静态分区，想要使用动态分区，需要设置如下参数，笔者使用的是临时设置，你也可以写在配置文件（hive-site.xml）里，永久生效。临时配置如下

开启动态分区（默认为false，不开启）

set hive.exec.dynamic.partition=true;

指定动态分区模式，默认为strict，即必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。

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

创建表student

create table student(

id int,

name string,

tel string,

age int

)row format delimited fields terminated by '\t';

student.txt文件内容如下

1001 zhangsan 18310982765 18

1002 lisi 15028761946 20

1003 wangwu 13018909873 25

将文件student.txt中的内容加载到student表中

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

创建分区表stu_age_partition

create table stu_age_partition(

id int,

name string,

tel string

)

partitioned by (age int)row format delimited fields terminated by '\t';

将student表的数据以age为分区插入到stu_age_partition表，试想如果student表中的数据很多，使用insert一条一条插入数据，很不方便，所以这个时候可以使用hive的动态分区来实现

insert into table stu_age_partition partition (age) select id,name,tel,age from student;

查询时，建议加上分区条件，性能高

select * from stu_age_partition;

select * from stu_age_partition where age > 20;

四、复杂数据类型操作

之前的操作都是在hive1中做的，我们安装的hive版本为2.3.0，即支持hive1，也支持hive2，下面的我们来使用hive2进行操作，hive1和hive2的区别就是hive2中解决了hive1中的单点故障，可以搭建高可用的hive集群，hive2界面显示格式要比hive1直观、好看（类似于mysql中的shell界面）

要想使用hive2，需要先执行如下命令开启hive2服务

hiveserver2

开启后使用如下命令连接hive2，就能进行操作了

beeline -u jdbc:hive2://

1.Array

创建一张带有数组的表tb_array

create table tb_array(

name string,

work_locations array<string>)

row format delimited fields terminated by '\t'

collection items terminated by ',';

加载数据到表tb_array

hive_array.txt文件内容如下

zhangsan beijing,shanghai,tianjin,hangzhou

lisi changchu,chengdu,wuhan

load data local inpath '/home/hadoop/data/hive_array.txt' overwrite into table tb_array;

查询张三的第一个工作地点（数组下标从0开始）

select name,work_locations[0] from tb_array where name='zhangsan';

查询每个人工作地点的数量（size）

select name,size(work_locations) from tb_array;

2.Map

创建一个带有map类型的表tb_map

create table tb_map(

name string,

scores map<string,int>

)

row format delimited fields terminated by '\t'

collection items terminated by ','map keys terminated by ':';

加载数据到表tb_map

hive_map.txt文件内容如下

zhangsan math:80,chinese:89,english:95

lisi chinese:60,math:80,english:99

load data local inpath '/home/hadoop/data/hive_map.txt' overwrite into table tb_map;

查询所有学生的英语成绩

select name,scores['english'] from tb_map;

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

select name,scores['english'],scores['math'] from tb_map;

3.Struct

创建一张带有结构体的表

create table tb_struct(

ip string,

userinfo struct<name:string,age:int>

)row format delimited fields terminated by '#'

collection items terminated by ':';

加载文件hive_struct.txt中的数据到表tb_struct

hive_struct.txt文件内容如下

192.168.1.1#zhangsan:40

192.168.1.2#lisi:50

192.168.1.3#wangwu:60

192.168.1.4#zhaoliu:70

load data local inpath '/home/hadoop/data/hive_struct.txt' overwrite into table tb_struct;

查询姓名及年龄

select userinfo.name,userinfo.age from tb_struct;

杰灬杰

关注

0
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
hive的数据模型及数据类型

1.Hive 数据抽象hive 中主要包含以下几种数据模型：Database（数据库）、Table（表）、Partition（分区）、Bucket（桶）Database（数据库）Hive中的数据库包含一系列的数据库，每个数据库都对应于HDFS上的一个目录，默认的数据库为default，对应于HDFS目录是/user/hadoop/hive/warehouse，可以通过hive.metas...
复制链接

扫一扫