HQL DDL语句

之宇不咸

已于 2024-01-06 21:39:55 修改

阅读量953

点赞数 20

文章标签： hive

于 2024-01-06 21:36:57 首次发布

本文链接：https://blog.csdn.net/weixin_51931576/article/details/135431284

版权

本文详细介绍了Hive的数据定义语言，建表语法，包括原生和复杂数据类型，以及Hive的本质——将HDFS文件映射为表并使用SQL操作。还涵盖了分区表的使用、元数据问题解决、备份与修改表的相关操作，以及HDFS与内部/外部表的区别。

摘要由CSDN通过智能技术生成

定义:

数据定义语言, 主要是用来操作数据库, 数据表, 字段等的

建表语法:

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

常用的数据类型:

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

hive 本质：

就是将HDFS中的文件映射成一张表，然后使用类SQL的语言操作它（HDFS文件），底层会转化为MR程序，交由yarn来调度执行。（当然也可以hive on spark 等，暂时还没学到。。。）

hive 默认分隔符：

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

建表过程：

1.建库：

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

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

查询表结构语句：

-- 1.
desc 表名;

-- 2. 
show create table 表名;

-- 3. 推荐，可以查询到具体表结构，其中: Table Type 表示表类型, MANAGED_TABLE(受管理表, 即: 内部表), EXTERNAL_TABLE(外部表)

desc formatted 表名;

解决Hive注释中文乱码问题：

产生原因：

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

解决方案:

保持码表一致即可, 我们去MySQL中修改Hive元数据信息的码表, 改为: UTF-8

具体步骤：

1: 先通过DataGrip连接node1机器的MySQL, 当然, 也可以直接在finalshell中连接MySQL数据库.

2: 在MySQL中执行如下的内容即可, 详见MySQL的文件. 细节: 还要去hive-site.xml文件中修改码表.

3: 重启metastore服务和 hiveserver2服务. -- Step4: 在Hive中把刚才的表删掉, 然后重建, 再次查询, 注释(描述信息)不乱码了.

-- ---下面修改是在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>

备份表：

-- 1. 底层会转成MR程序, 备份表和原表, 字段名, 数据类型, 数据都会保持一致. 用的是默认分隔符:'\001'

create table 表名 as select * from 要备份的表;

-- 2. 底层会转成MR程序, 备份表和原表, 字段名, 数据类型, 数据都会保持一致. 用的是指定分隔符:'，'

create table 表名 row fomart delimited fields terminated by ','
select * from 要备份的表;

-- 3. 备份表和原表, 字段名, 数据类型 都会保持一致，但没有数据。即 空表 ！ 底层不会转MR程序

create table 表名 like 要备份的表;

修改表相关：

-- 1. 修改表名 rename to

alter table 旧表名 rename to 新表名;

-- 2. 修改表的存储路径 location

alter table 表 set location '存储路径';

-- 3. 修改表属性 , 内部表和外部表的相互转换.  以下均为固定格式, 且 必须大写 

-- 3.1 将 内部表 修改为 外部表
 
alter table 表 set tblproperties('EXTERNAL'='TURE);

-- 3.2 将 外部表 修改为 内部表

alter table 表 set tblproperties('EXTERNAL'='FALSE');

-- 4. 添加列 

alter table 表 add columns (列名 数值类型, 列名 数值类型 .....);

-- 5. 修改列名

alter table 表 change 旧列名 新列名 数值类型;        -- 不加数值类型会报错

-- 6. 删除数据

-- 6.1 删除表

-- 删除 内部表 会全部删除, 包括元数据和源文件. 删除 外部表 只能删除元数据,源文件还在HDFS中

drop 表名;    

-- 6.2 删除表数据(清空数据)  

-- delete from 表;  会报错, hive 不支持

truncate table 表;      -- 注意: 只能删除内部表的数据

内部表和外部表的区别:

1. 格式不同

直接创建的hive表就是内部表，如果要创建外部表，必须使用 external 关键字。

2. 删除时是否会删除源文件

删除内部表，除了删除元数据，还会删除源文件。

删除外部表，只会删除元数据，源文件还在HDFS中。

3. 应用场景不同

删除hive的时候，同时要删除源文件，就使用内部表，否则就使用外部表。

注意：若删除外部表之后，又重新创建表名, 数据类型, 字段数量等和原表(删除前的外部表)一致的表，则重新建表之后，就有数据。

分区表：

分区表详解：

概述/作用：

分区 = 分文件夹，即把一个整体拆分成 n个文件夹，避免全表扫描，提高查询效率。

格式:

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';

-- 往分区表中添加数据, 静态分区, 即: 分区字段 = 值, 类似于 静态IP, 都是手动写死的.

load data local inpath '/export/data/hivedata/archer.txt' into table t_all_hero_part partition (role='sheshou');
load data local inpath '/export/data/hivedata/assassin.txt' into table t_all_hero_part partition (role='cike');
load data local inpath '/export/data/hivedata/mage.txt' into table t_all_hero_part partition (role='fashi');
load data local inpath '/export/data/hivedata/support.txt' into table t_all_hero_part partition (role='fuzhu');
load data local inpath '/export/data/hivedata/tank.txt' into table t_all_hero_part partition (role='tanke');
load data local inpath '/export/data/hivedata/warrior.txt' into table t_all_hero_part partition (role='zhanshi');

动态分区：

-- 注意 ：动态分区时, 要关闭严格模式, 因为严格模式要求: 在动态分区的时候, 至少要有1个静态分区.
-- 例如: partition(role) 动态分区,  partition(role='sheshou') 静态分区,   partition(role='sheshou', year,month) 1个静态分区, 2个动态分区

set hive.exec.dynamic.partition.mode=nonstrict;  -- 关闭严格模式

-- 1. 建表, 动态分区表.
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';

-- 2. 往(动态)分区表中添加数据, insert + select 方式.
insert into t_all_hero_part_dynamic partition (role)  -- 动态分区
select *, role_main from t_all_hero;  -- role_main是分区列, 内容一样的数据会被放到一起.

分区表总结:

分区总结:

1. 分区 = 分文件夹, 目的是: 避免全表扫描, 降低扫描的次数, 提高查询效率.

2. 分区字段必须是表中没有的字段.

3. Hive默认开启了严格模式, 要求: 动态分区时, 至少指定1个静态分区. partition(role) 动态分区 partition(role='fashi') 静态分区

4. 实际开发中, 可以是单级分区, 也可以是多级分区, 多级分区一般不会超过3层, 且大多数是年月日分区.

-- 0. 创建学生分区表.
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 ',';

-- 1. 往学生分区表中添加数据.
insert into stu_part partition (year='2023', month='05')   -- 静态分区
select * from stu;

select * from stu_part;

-- 2. 查看表所有的分区.
show partitions stu_part;

-- 3. 手动添加分区   add partition
alter table stu_part add partition(year='2023', month='03');    -- 手动添加1个分区

alter table stu_part add
    partition(year='2023', month='01')
    partition(year='2022', month='01')
    partition(year='2022', month='03')
    partition(year='2021', month='05')
    partition(year='2023', month='09'); -- 手动添加多个分区

-- 4. 修改分区  rename partition
alter table stu_part partition (year='2023', month='09') rename to partition (year='2021', month='11');

-- 5. 删除分区.   drop partition
alter table stu_part drop partition(month='01');      -- 删除(每年)所有的 1月分区
alter table stu_part drop partition(year='2021');     -- 删除2021年所有的分区
alter table stu_part drop partition(year='2023',month='03');     -- 删除2023年03月的分区

补充：

在静态分区和动态分区导入数据时，涉及DML语句，特此补充说明：

/*
HQL DML语句介绍:
    它指的是 数据操作语言, 主要是用来 操作表数据的, 对表数据进行 增删改操作, 这里特指的是: 如何往Hive表中新增数据.

    数据导入:
        流向:
            Linux, HDFS => Hive
        方式:
            1. 通过 load data方式实现.
            2. 通过 insert + select 方式实现, 相对更灵活.

        load data方式详解:
            格式:
                load data [local] inpath '源文件的路径' [overwrite] into table 表名;
            解释:
                1. 加local指的是本地路径, 即: Linux文件路径.  路径前缀要加 file:///  可以省略不写.
                   不加local指的是HDFS路径, 路径前缀要加 hdfs://node1:8020/  可以省略不写.
                2. 不写overwrite是追加写入, 如果写了(overwrite)则是覆盖写入.

        insert + select 方式详解:
            格式:
                insert [into | overwrite] table 表名 partition(分区字段=值)
                select 列1, 列2... from 表名;
            细节:
                1. into是追加写入, 后续的table可以省略.
                   overwrite是覆盖写入, 后续的table不能省略.
                2. 插入的时候, 要求和查询出的列的个数, 对应的数据类型必须保持一致, 至于列名, 表名无所谓.
                3. insert + select方式底层回转MR程序来执行.
    数据导出:
        格式:
            insert overwrite [local] directory '目的地目录的路径' row format delimited fields terminated by '行格式分隔符'
            select 列1, 列2... from 表名;
        细节:
            1. 加local是Linux路径, 不加则是HDFS路径.
            2. 导出时, 是覆盖导出, 可以指定字段分隔符.
*/

-- 1. 数据导入之 load方式

-- 0. 切库
use day06;
-- 1. 创建学生表.
create table stu(
    id int,
    name string,
    gender string,
    age int,
    sno string
) row format delimited fields terminated by ',';

create table stu_hdfs(
    id int,
    name string,
    gender string,
    age int,
    sno string
) row format delimited fields terminated by ',';

-- 2. 通过load方式加载数据到 stu表中.
-- 2.1 从Linux中加载数据到stu表中, 追加写入.
load data local inpath '/export/data/hivedata/students.txt' into table stu;     -- 不转MR, Linux的源文件还在, 相当于拷贝一份给HDFS.

-- 2.2 从Linux中加载数据到stu表中, 覆盖写入.
load data local inpath '/export/data/hivedata/students.txt' overwrite into table stu;     -- 不转MR, Linux的源文件还在, 相当于拷贝一份给HDFS.

-- 2.3 从HDFS中加载数据到stu表中. 追加写入 和 覆盖写入都一样, 就是看有没有写 overwrite单词.
load data inpath '/wordcount/students.txt' into table stu_hdfs;      -- 不转MR, HDFS的源文件不在了, 相当于: 剪切.

-- 3. 查询表数据.
select * from stu;
select * from stu_hdfs;



--  数据导入之 insert + select方式

-- 格式: insert into 表名 select 列1, 列2.. from 表名;
-- 总结: insert + select 方式要求 列的个数, 对应列的数据类型均要求一致, 对列名和表名无要求.
-- 1. 建表.
create table stu_insert(
    id int,
    name string,
    gender string,
    age int,
    sno string
) row format delimited fields terminated by ',';

-- 2. insert + select方式导入数据.
insert into table stu_insert select * from stu;   -- 会转MR程序, into方式是 追加写入, table可以省略.
insert overwrite table stu_insert select * from stu;   -- 会转MR程序, overwrite方式是 覆盖写入, table不可以省略.

insert overwrite table stu_insert
select hp_max, name, attack_range, mp_max, role_main from t_archer;   -- 因为列的个数, 对应的数据类型相同, 所以插入成功.

insert overwrite table stu_insert select * from t_archer;   -- 因为列的个数, 对应的数据类型不同, 所以插入失败.

-- 3. 查询表数据.
select * from stu_insert;


-- 数据导出  directory

/*
数据导出:
    格式:
        insert overwrite [local] directory '目的地目录的路径' row format delimited fields terminated by '行格式分隔符'
        select 列1, 列2... from 表名;
    细节:
        1. 加local是Linux路径, 不加则是HDFS路径.
        2. 导出时, 是覆盖导出, 可以指定字段分隔符.
*/
-- 1. 查看表数据.
show tables;
select * from stu;      -- 44条数据.

-- 2. 将stu的表数据导出到 Linux目录中.
insert overwrite local directory '/export/data/hivedata' select * from stu;     -- 覆盖导出, 默认分隔符'\001'

insert overwrite local directory '/export/data/hivedata2' row format delimited fields terminated by '#'
select * from stu;     -- 覆盖导出, 指定分隔符为'#'

insert overwrite local directory '/export1/data2/hivedata3' row format delimited fields terminated by '!'
select * from stu;     -- 覆盖导出, 指定分隔符为'#'

-- 3. 将stu的表数据导出到 HDFS目录中.
insert overwrite directory '/wordcount' row format delimited fields terminated by '$'
select id, name, age from stu;