Hive基础【数据库的增删改查，表的增删改查（hive的DDL操作）】

hike
446 阅读8分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

文章目录

Hive使用

Hive的JDBC访问

  • 启动beeline客户端，出现各种版本号说明连接成功

    beeline -u jdbc:hive2://hadoop101:10000 -n hike

    连接成功可以当做mysql来使用

  • 创建数据库

    create table student(id int,mame string);

  • 插入几条数据

    insert into table student values(1001,"zhangsan");

  • 查看数据

    select * from student;

    select id,count(*) from student group by id;

hive的数据类型

基本数据类型

在这里插入图片描述

集合数据类型

在这里插入图片描述

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

案例实操

  • 在hive上创建测试表
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的分隔符

lines terminated by ‘\n’; – 行分隔符

  • 将数据上传到HDFS的几种方式

    • 直接在hadoop网页端的/user/hive/warehouse/test目录下点击上传按钮
    • 在linux控制端load data local inpath '/opt/module/datas/test.txt' into table test;
    • 表格存储路径默认在HDFS文件系统的/user/hive/warehouse路径下也可以使用desc formatted test;命令查看

  • 对于集合数据的使用

    • select name,friends[1],children["xiao song"],address.street from test;

类型转化

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。

  • 隐式类型转换规则（自动转换）

    （1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。

    （2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。

    select “1” + 2;结果为3.0。

    （3）TINYINT、SMALLINT、INT都可以转换为FLOAT。

    （4）BOOLEAN类型不可以转换为任何其它的类型。

  • 可以使用CAST操作显示进行数据类型转换

    select '1'+2, cast('1'as int) + 2;前者将1自动转换为DOUBLE，后者将1强制转换成INT。

DDL数据定义

创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]		--一些对数据库的描述信息
[LOCATION hdfs_path]			--存储的路径，此目录就是这个数据库，这个数据库就是那个目录，两者是映射关系
[WITH DBPROPERTIES (property_name=property_value, ...)]; 	--数据库的一些属性

create database test
comment "just for test"
location '/test.xxx'
with dbproperties("aaa"="bbb");

查询数据库

  • 显示数据库

    show databases;

  • 查询数据库具体信息

    desc database test;

  • 查看数据库扩展信息

    desc database extended test;

  • 切换当前数据库

    use 数据库名

修改数据库

数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。只能修改数据库的属性

alter database test set dbproperties("111"="222","333"="444");

删除数据库

  • 如果数据库不是空的，不能删除

    drop database test;

  • 强制删库

    drop database test cascade;

创建表

  • 建表语法

    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] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

  • 字段说明

    （1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

    （2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

    （3）COMMENT：为表和列添加注释。

    （4）PARTITIONED BY创建分区表

    （5）CLUSTERED BY创建分桶表

    （6）SORTED BY不常用，对桶中的一个或多个列另外排序

    （7）ROW FORMAT

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

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

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

    用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。

    SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。

    （8）STORED AS指定存储文件类型

    常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）

    如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

    （9）LOCATION ：指定表在HDFS上的存储位置。

    （10）AS：后跟查询语句，根据查询结果创建表。

    （11）LIKE允许用户复制现有的表结构，但是不复制数据。

  • 例子

    create table test
(id int comment "ID",name string comment "Name")
comment "Test Table"
row format delimited fields terminated by '\t'
location "/test.db"
tblproperties("aaa"="bbb");

查看表

desc formatted test;

修改表

  • 重命名表

    alter table test rename to testRename;

  • 修改列

    alter table testrename change id id string;

    把id列数据类型改为string类型

  • 增加列

    alter table testrename add columns(class string);

  • 重写所有的列

    alter table testrename replace columns (id double,name string);

很少使用，因为hive处理的一般是历史数据，比如订单历史数据，信息不改变，元信息也就不改变

  • 删除表

    drop table testrename;

  • 用查询结果建表
    create table stu_res as select * from stu_par where id=1001;

内外部表

外部表

  • 建立外部表

    create external table test(id int,name string)
row format delimited fields terminated by '\t';

    元数据存储在mysql上，数据存储在hdfs上，元数据和数据一起组成一张表，数据和元数据的生命周期由hive决定，但在外部表中hive只对元数据有管理权限，对这张表的数据没有删除的权限。简单来说，外部表在删表的时候不会把数据一起删除掉，内部表则会删除数据。

    • 使用场景

      hive可能和其他的框架共享一些日志数据，与其他框架共享数据的时候使用外部表。

  • 外部表和内部表的转换

    如果向创建外部表，但命令写错了，这种情况下不能直接删除表，因为其中的数据也会被删除

create external table test(id int,name string)
row format delimited fields terminated by '\t';

​ 可以将其转换成外部表

alter table test set tblproperties("EXTERNAL"="TRUE");

​ 将外部表转换成内部表

alter table test set tblproperties("EXTERNAL"="false");

分区表

hive有一个巨大的缺点：数据没有索引，在查找所有数据时，需要全表扫描，在数据量很大的时候，做全表扫描的时间复杂度太高，hive对此有两个解决办法：分区和分桶。

分区表：hive可以根据某一列值的不同，把数据分成几个区（文件夹），查询时在一起显示，但是存储在不同的文件中，查表时，选择分区，可以减小数据扫描量。

  • 创建分区表

    create table stu_par(id int,name string)
partitioned by (class string)
row format delimited fields terminated by '\t';

  • 向不同分区插入数据

    load data local inpath '/opt/module/datas/student.txt' into table stu_par
partition (class='01');
load data local inpath '/opt/module/datas/student.txt' into table stu_par
partition (class='02');
load data local inpath '/opt/module/datas/student.txt' into table stu_par
partition (class='03');

  • 查找表

    select * from stu_par where class='01';	--扫描16行数据
select * from stu_par where id=1001;	--扫描48行数据

  • 查询分区表的分区

    show partitions stu_par;

  • hdfs上的数据提前准备好，但是没有原数据，修复方式

    • 添加分区

      alter table stu_par add partition(class='04');

    • 直接修复（最常用）

      msck repair table stu_par;

    • 上传数据时候直接带上分区信息

      load data local inpath '/opt/module/datas/student.txt' into table stu_par
partition (class='01');

二级分区表

  • 创建二级分区表

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

  • 插入数据，指定到二级分区(多层文件夹)

    load data local inpath '/opt/module/datas/student.txt' into table stu_par2
partition (grade='01',class='01');

  • 一次增加多个分区

    alter table stu_par add partition(class='06')partition(class='07');

  • 删除分区

    alter table stu_par drop partition(class='06'),partition(class='07');

hive配套数据

avatar
文章
阅读
粉丝