HiveSQL基础命令01

目录

一、建表结构

二、内部表和外部表区别

​编辑​

​编辑​

三、扩展_解决hive中文注释乱码问题

四、HQL DDL--备份表和删除表数据

1.备份表

2.删除表数据

五、HQL DDL--查看及修改表信息

六、HQL DQL语句--建表-默认切割符

七、HQL DDL语句--快速映射表

八、HQL DML语句--数据导入-load data方式

1.数据导入:Linux=>Hive表

2.数据导出:Hive表 =>  Linux

---

一、建表结构

create [external] table [if not exists] 表名(
    列名1 数据类型 [comment '字段的描述信息'],
    列名2 数据类型 [comment '字段的描述信息'],
    列名3 数据类型 [comment '字段的描述信息'],
    ......
) [comment '表的描述信息']
分区  [partitioned by(分区字段1 数据类型 comment '字段的描述信息', 分区字段2...)]
分桶  [clustered by(分桶字段1, 分桶字段2...) sorted by (排序字段1 asc | desc, 排序字段2...) into 桶的个数 buckets]
行格式切割符  [row format delimited | SerDe] '指定其它的SerDe类, 即: 不同的切割方式'
存储方式      [stored as TextFile | Orc]      行存储或者列存储
存储位置      [location hdfs的文件路径]
表属性信息    [tblproperties('属性名'='属性值')] 例如: 内外部表, 创建者信息, 压缩协议...
;

新建一个在dwm数据库下名为dwm_sold_goods_sold_dtl_i的表，以','为分隔符，并设置为列式存储，定义压缩格式为snappy

1.常用数据类型

HQL 常用的数据类型:
原生类型:
        int         整数
        double      小数
        string      字符串
        timestamp   时间戳, 单位: 毫秒
        date        日期

        decimal(长度,小数位数) 小数位数直接截取
复杂类型;
        array   列表(序列)
        map     映射
        struct  结构体
        union   联合体

二、内部表和外部表区别

1. 建表格式不同.
内部表: 直接创建即可, 默认就是内部表.
外部表: 建表是需要加 external关键字.
2. 权限不同, 是否会删除源文件.
内部表: 也叫受2管理表, 删除内部表时, 不仅会删除元数据(Hive中查不到了), 还会删除源文件(HDFS也查不到了)
外部表: 只会删除元数据(Hive中查不到了), 不会删除源文件(HDFS中还在)

细节:
1. 如果某个文件想持久存在(除了Hive用, 其它的框架 例如HBase, Spark也要用), 建Hive表时就选择外部表.
2. 如果某个HDFS文件是随着Hive表的删除而删除的, 就用内部表.
思考:
如果我把外部表删了, 又重新的把外部表创建了出来, 请问: 里边还有之前的数据吗?
答案:
如果表结构, 切割方式, 表的存储位置, 表名等信息不发生改变, 则: 创建出该表后, 默认就有之前的数据.

内部表students

​

外部表students_dy

​

点击红框，上传数据，外部表同理

​

​

三、扩展_解决hive中文注释乱码问题

遇到的问题: 列的描述信息出现乱码

具体步骤如下:
    1. 去MySQL的hive3数据库中, 修改Hive的码表信息.    因为Hive的元数据(表名, 列名, 数据类型, 描述信息等)都是在MySQL中存储的.

​

2. 去Linux中(即: node1机器), 修改hive软件的配置信息, 配置文件是: /export/server/hive/conf/hive-site.xml 文件.
把如下的内容, 添加到 <configuration>  </configuration>标签中即可.

<!-- 存储元数据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>

3. 在node1机器中, 关闭metastore服务, hiveserver2服务, 然后重启这两个服务.
kill -9 pid值  pid值
nohup hive --service metastore &
nohup hive --service hiveserver2 &

​

4. 删除刚才创建的hive表, 然后重新创建即可, 此时: 注释乱码问题已经解决了.

四、HQL DDL--备份表和删除表数据

1.备份表

方式1.只复制表结构

create table 备份表名 like 原表名；-- 底层不会转成MR任务, 而是直接执行.

方式2.复制结构和数据，不包括行切割方式。

create table 备份表名 as select 语句;-- 底层会转MR任务, 来执行.

​

​

​

2.删除表数据

truncated table 表名;
注释：语法只针对于内部表有效, 针对于外部表无效.删外部表会报错。

五、HQL DDL--查看及修改表信息

desc formatted  表名;     -- 查看详细信息.
show create table 表名;   -- 查看建表信息

alter table 旧表名 rename to 新表名; --修改表名
alter table 表名 set location 'HDFS路径'; --修改表的存储路径
alter table 表名 set tbproperties('EXTERNAL'='true'); --设置为外部表
alter table 表名 set tbproperties('EXTERNAL'='FALSE'); --设置为内部表
alter table 表名 add columns (新字段名 字段类型 comment '描述信息'); --增加列
alter table 表名 change 旧列名 新列名 数据类型; --修改列，注意: string不能直接转成int类型, 反之可以.
alter table 表名 replace columns (列1 数据类型,列2 数据类型...) --修改表中所有列名字类型和列的个数

六、HQL DQL语句--建表-默认切割符

create table 表名(...)
row format delimited fields terminated by '切割符号';

不写“row format delimited fields terminated by '切割符号'”这段代码, 则hive表会用默认的行格式切割符, 即: '\001', 它是1个特殊的字符.
在Linu文件x系统中, vim打开文件查看，显示为: ^A        快捷键: ctrl + v, ctrl + a
在windows文件系统中, 显示为: SOH
在HDFS文件系统中, 显示为: 口

七、HQL DDL语句--快速映射表

Hive的本质就是: 把HDFS文件映射成Hive表, 然后就可以写HQL来操作它了, 底层会被解析成MR任务, 交由Yarn调度执行, 所需的数据源及执行结果会保存到HDFS上...

建表

​

上传数据​

八、HQL DML语句--数据导入-load data方式

1.数据导入:Linux=>Hive表

insert into table 需要导入的表名 [partition by(分区字段1, 分区字段2...)]
select 语句;
--有into 可以省略table

如果导入的表中有内容,新数据会到旧内容的前面

insert overwrite table 需要导入的表名 [partition by(分区字段1, 分区字段2...)]
select 语句;
--table 不可省略

如果导入的表中有内容,新数据会覆盖旧内容

2.数据导出:Hive表 =>  Linux

insert overwrite [local] directory '存储该文件的路径'
[row format delimited fields terminated by '行格式分隔符']
select 语句;
--不写local就是HDFS路径, 写了就是Linux路径.