【Hive】DDL语句详解-知无不言

最新推荐文章于 2024-04-24 09:00:13 发布

小威blog

最新推荐文章于 2024-04-24 09:00:13 发布

阅读量199

点赞数 1

文章标签： hive 数据库 hadoop

本文链接：https://blog.csdn.net/weixin_46244703/article/details/131117678

版权

学会了Hive的一些操作，发现Hive建过表，后续的数据并不是向MySQL是通过自己手写进行插入的，而是将TXT文件解析成为数据表的操作。其实是HDFS将文件映射成Hive表，然后然后通过写类SQL的语句来操作该文件，底层会被解析成为MR程序。

Hive的本质

把HDFS文件映射成一张Hive表, 然后通过写类SQL的语句来操作它(HDFS文件), 底层会被解析成MR程序, 交由Yarn来调度执行.

DDL(Data Define Language)是什么？

见名知意，数据定义语言==Data Define Language。

对数据库的操作：包含创建(Create)数据库，数据表、修改数据库 (Alter table)

对数据表的操作：内部表(Managed Table)及外部表(External)，分区表(Partition)和分桶表

数据库Operation

create database if not exists 数据库名;  -- 创建数据库

show databases;  -- 查看系统中所有数据库

use 数据库名;  -- 切换数据库

drop database 数据库名 casecade;  -- 如果数据库下面有表，使用casecade进行级联删除数据库

切割符

Hive表默认切割符是'\001'符号, 在windows中显示为 SOH, 在HDFS中显示为小口, 在Linux中显示为 ^A

数据类型

常用的数据类型:
    原生类型:
        int         整数
        double      小数
        string      字符串
        timestamp   时间戳
        date        日期(年月日)
    复杂类型:
        array       数组类型
        map         字段(映射)类型
        struct      结构体
        union       联合体

Comment乱码

原因是: 我们的Hive表是UTF-8码表, Hive的元数据存在MySQL中, 用的GBK码表, 码表不一致导致的.

解决方案: 保持码表一致即可, 我们去MySQL中修改Hive元数据信息的码表, 改为: UTF-8
具体步骤:

Step1: 先通过DataGrip连接node1机器的MySQL, 账号: root, 密码: 123456 当然, 也可以直接在CRT中连接MySQL数据库.
Step2: 在MySQL中执行如下的内容即可, 详见MySQL的文件. 细节: 还要去hive-site.xml文件中修改下码表.
Step3: 重启metastore服务和 hiveserver2服务.
Step4: 在Hive中把刚才的表删掉, 然后重建, 再次查询, 注释(描述信息)不乱码了.

具体步骤如下：

show databases ;
use hive3;
-- 下面修改是在MySQL中修改，因为MySQL记录维护着元数据

# （1）修改表字段注解和表注解
use hive3;
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;

# （2）修改分区字段注解

alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

# （3）修改索引注解

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

# ---下面这个在Hive的配置文件中修改
-- cd /export/server/hive/conf
-- 修改 hive-site.xml 文件

<!-- 存储元数据mysql相关配置 -->
<property>
	<name>javax.jdo.option.ConnectionURL</name>
	<value>jdbc:mysql://node1:3306/hive3?createDatabaseIfNotExist=true&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
</property>

建表语法

建表语法:
create [external] table [if not exists] 表名(
列名数据类型 [ comment ‘描述信息’],
列名数据类型 [ comment ‘描述信息’],
列名数据类型 [ comment ‘描述信息’],
…
) comment ‘表的描述信息’
分区
分桶(桶内排序 + 分桶个数)
行格式切割符
存储格式(行存储, 列存储)
存储位置
表的属性信息;

建表, 表字段, 数据类型等要和 HDFS源文件保持一致

建表并制定分隔符为\t

create table archers(
    id string comment 'ID',
    name string comment '英雄',
    hp_max string comment '最大生命',
    mp_max string comment '最大法力',
    attack_max string comment '最高物攻',
    defense_max string comment '最大物防',
    attack_range string comment '攻击范围',
    role_main string comment '主要定位',
    role_assist string comment '次要定位'
) comment '射手信息'
row format delimited fields terminated by '\t';

上传源文件(5种方式)

上传源文件到Hive表所在的 HDFS路径下

load data local inpath '/root/archer.txt' overwrite into table archers; 
-- 从linux写入(有local，覆盖写入(有overwrite
load data inpath '/wordcount/archer.txt' into table archers;  
-- 从hdfs中写入到hdfs中，追加写入

哪里来的5种方式，细数一下

上传Linux文件到hdfs中，有两种方式，追加写入和覆盖写入

上传hdfs文件到hdfs中，有两种方式，追加写入和覆盖写入

通过hdfs端口8979上传文件，这是Hadoop3.x的特性，2.x好像不行

查看表结构信息

三种方式

desc archers;
desc formatted archers;
show create table archers;  -- 一般比较常用

其它的建表方式

create table archer1 as select * from archers;  -- 会转mr执行，且是默认分隔符
create table archer2 row format delimited fields terminated by ',' as select * from archers; -- 会转mr执行
create table archer3 like archers;  -- 创建一个空表，但是字段名称跟archer一样

修改表相关操作

alter table archer1 rename to archers1;  -- 修改表名

alter table archers1 set location '/root/aa';  -- 这只会修改表的映射路径，实际文件并没有在/root/aa下，而还在默认的地方。

alter table archers1 set tblproperties('EXTERNAL'='TRUE'); -- 修改表为外部表
alter table archers1 set tblproperties('EXTERNAL'='FALSE');  -- 修改表为内部表

alter table archer2 add columns (address string, tel string);  -- 向表中添加地址和电话字段
alter table archer2 change address addr string;  -- 修改表中的字段address为addr

drop table archer2;  -- 删除表的元数据（支持删除内部表和外部表的元数据
truncate table archer2;  -- 清空表（只能清空内部表，不能清空外部表，因为没有权限
-- 注意hive中没有delte from archer2  这种操作方式

面试题: 内部表和外部表的区别是什么?

格式不同.
直接创建Hive表默认就是内部表, 如果要创建外部表, 必须用 external 关键字.
删除时是否会删除源文件.
删除内部表的时候, 除了会删除元数据, 还会删除源文件.
删除外部表的时候, 只会删除元数据, 但是源文件(HDFS上)还在.
应用场景不同.
删除Hive的时候, 如果也要同步删除源文件, 就用内部表. 否则就用外部表, 例如: Hbase 和 Hive的映射.

分区表之静态分区

分区表详解:
概述/作用:
1. 分区 = 分文件夹, 即: 把1个整体拆分成 n个文件夹, 避免全表扫描, 提高查询效率.
2. 例如: 公司成立10年所有的数据都在1个文件夹中放着, 查找数据, 每次都要全表扫描, 效率相对较低.
可以按照年月分区, 把每年每月的数据都放一起, 这样查找时只要找指定的内容即可, 降低扫描次数, 提高效率.
格式:
create table 表名() partitioned by(分区字段1 数据类型, 分区字段2 数据类型…) row format …;

细节:
分区字段必须是表中没有的字段.

create table t_all_hero_part (
    id string comment 'ID',
    name string comment '英雄',
    hp_max string comment '最大生命',
    mp_max string comment '最大法力',
    attack_max string comment '最高物攻',
    defense_max string comment '最大物防',
    attack_range string comment '攻击范围',
    role_main string comment '主要定位',
    role_assist string comment '次要定位'
)
comment '王者荣耀英雄表'
partitioned by (role string)   -- 按照角色主要定位分区, 分区字段必须是表中没有的字段
row format delimited fields terminated by '\t';

load data local inpath '/root/archer.txt' into table t_all_hero_part partition (role='sheshou');
-- 自定义分区为sheshou，（射手，也就是在数据库下面新建一个文件夹名为sheshou

分区表之动态分区

create table t_all_hero_part_dynamic (
    id string comment 'ID',
    name string comment '英雄',
    hp_max string comment '最大生命',
    mp_max string comment '最大法力',
    attack_max string comment '最高物攻',
    defense_max string comment '最大物防',
    attack_range string comment '攻击范围',
    role_main string comment '主要定位',
    role_assist string comment '次要定位'
)
comment '王者荣耀英雄表'
partitioned by (role string)   -- 按照角色主要定位分区, 分区字段必须是表中没有的字段
row format delimited fields terminated by '\t';

-- 细节: 动态分区时, 要关闭严格模式, 因为严格模式要求: 在动态分区的时候, 至少要有1个静态分区.
set hive.exec.dynamic.partition.mode;  -- 查询是否是严格模式，查询结果为strict
set hive.exec.dynamic.partition.mode=nonstrict;  -- 关闭严格模式

insert into t_all_hero_part_dynamic partition (role)
select *,role_main 3 t_all_hero;  -- 通过一个insert + select将一个已经存在的表转换为动态分区表

分区表二级分区

create table stu_part(
    id int,
    name string,
    gender string,
    age int,
    sno string
)
partitioned by (year string, month string)  -- 按照年, 月分区.
row format delimited fields terminated by ',';


insert into stu_part partition (year='2023',month='03')
select * from stu;  -- 指定往二级分区中插入数据
show partitions stu_part;  -- 查看数据表中所有分区
alter table stu_part add partition (year='2022',month='02');  -- 想分区表中添加新的分区
alter table stu_part partition(year='2022',month='02') rename to partition (year='2022',month='03');  -- 修改分区
alter table stu_part drop partition (year='2022',month='03');  -- 删除分区

小威blog

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【Hive】DDL语句详解-知无不言

学会了Hive的一些操作，发现Hive建过表，后续的数据并不是向MySQL是通过自己手写进行插入的，而是将TXT文件解析成为数据表的操作。其实是HDFS将文件映射成Hive表，然后然后通过写类SQL的语句来操作该文件，底层会被解析成为MR程序。
复制链接

扫一扫