HIVE之数据类型与DDL语句参考笔记

超、凢脫俗 2023-10-02 21:38 20阅读 0赞

#### 一、Hive的数据类型 ####

###### 1.Hive的基本数据类型 ######

注：一个字节（Byte）能表示8个二进制位，即最大为11111111，换成十进制表示的最大的数就是255  
![在这里插入图片描述][9e54d438dd0d428cb8b9c361a0ca23d9.png]  
Hive的String相当于数据库的varchar类型，是一个可变字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储 2GB 的字符数

###### 2.Hive的集合数据类型 ######

![在这里插入图片描述][cdcbd7edddfe478191f0a8c2f6295131.png]

###### 3.根据文本数据类型创建表的语句（小例子入门实操） ######

例子：  
songsong,bingbing\_lili,xiao song:18\_xiaoxiao song:19,hui long guan\_beijing  
yangyang,caicai\_susu,xiao yang:18\_xiaoxiao yang:19,chao yang\_beijing

建表语句：  
create table test(  
name string,  
friends array,  
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 ’ \_ ’ --Array的分隔符  
map keys terminated by ‘:’ – MAP 中的 key 与 value 的分隔符  
lines terminated by ‘\\n’; – 行分隔符

###### 4.类型转换 ######

1）隐式类型转换规则如下  
（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成  
INT，INT 可以转换成 BIGINT。 （2）所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。 （3）TINYINT、SMALLINT、INT 都可以转换为 FLOAT。 （4）BOOLEAN 类型不可以转换为任何其它的类型。  
2）可以使用 CAST 操作显示进行数据类型转换  
例如 CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1；如果强制类型转换失败，如执行  
CAST(‘X’ AS INT)，表达式返回空值 NULL。

#### 二、DDL数据库数据定义 ####

###### 1.创建数据库 ######

CREATE DATABASE [IF NOT EXISTS] database_name
    [COMMENT database_comment]
    [LOCATION hdfs_path]
    [WITH DBPROPERTIES (property_name=property_value, ...)];

为表添加注释；  
创建数据库的存储位置，默认为/user/hive/warehouse/\*.db；  
额外的参数信息，可以写一下当前表的作者等等。

###### 2.显示数据库 ######

1）显示数据库
    hive> show databases;
    2）过滤显示查询的数据库
    hive> show databases like 'db_hive*';

###### 3.查看数据库详情 ######

1）显示数据库信息
    hive> desc database db_hive;
    2）显示数据库详细信息，extended
    hive> desc database extended db_hive;

###### 4.切换与修改数据库 ######

1）切换
    hive (default)> use db_hive;
    2）修改
    用户可以使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对属性值，
    来描述这个数据库的属性信息
    hive (default)> alter database db_hive 
    set dbproperties('createtime'='20170830');

###### 5.删除数据库 ######

1）删除空数据库
    hive>drop database db_hive2;
    2）如果删除的数据库不存在，最好采用 if exists 判断数据库是否存在
    hive> drop database if exists db_hive2;
    3）如果数据库不为空，可以采用 cascade 命令，强制删除
    hive> drop database db_hive cascade;

#### 三、DDL表的定义语句 ####

###### 1.创建表的基本语法 ######

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 允许用户复制现有的表结构，但是不复制数据

###### 2.管理表（内部表）与外部表 ######

1.内部表  
默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive 会（或多或  
少地）控制着数据的生命周期。  
应用场景：当我们删除一个管理表时，Hive也会删除这个表中的数据。管理表不适合和其他工具共享  
数据。  
2.外部表  
因为表是外部表，所以 Hive 并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。即hdfs中的数据还在，但是select查询不到表信息（透过mysql查看已经删除了此表）。

总结：管理表和外部表的使用场景  
每天将收集到的网站日志定期流入 HDFS 文本文件。在外部表（原始日志表）的基础上  
做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过 SELECT+INSERT 进入内部表。  
3.内外部表的互相转换

（1）查询表的类型
    hive (default)> desc formatted student2;
    Table Type: MANAGED_TABLE
    （2）修改内部表 student2 为外部表
    alter table student2 set tblproperties('EXTERNAL'='TRUE');
    （3）查询表的类型
    hive (default)> desc formatted student2;
    Table Type: EXTERNAL_TABLE
    （4）修改外部表 student2 为内部表
    alter table student2 set tblproperties('EXTERNAL'='FALSE');
    （5）查询表的类型
    hive (default)> desc formatted student2;
    Table Type: MANAGED_TABLE
    注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

###### 3.修改表 ######

1.重命名表

1）语法
    ALTER TABLE table_name RENAME TO new_table_name
    2）实操案例
    hive (default)> alter table dept_partition2 rename to dept_partition3;

2.增加/修改/替换列信息

语法：  
（1）更新列  
ALTER TABLE table\_name CHANGE \[COLUMN\] col\_old\_name col\_new\_name  
column\_type \[COMMENT col\_comment\] \[FIRST|AFTER column\_name\]  
（2）增加和替换列  
ALTER TABLE table\_name ADD|REPLACE COLUMNS (col\_name data\_type \[COMMENT  
col\_comment\], …)

添加列
    hive (default)> alter table dept add columns(deptdesc string);
    更新列
    hive (default)> alter table dept change column deptdesc desc string;
    替换列
    hive (default)> alter table dept replace columns(deptno string, dname
    string, loc string)

注：  
更新列即使不更新类型，也要写上；  
替换列会把表中的所有列进行替换，所以一般使用更新列

###### 4.删除与查询表 ######

查询表
    hive (default)> show tables;
    删除表
    hive (default)> drop table dept;

[9e54d438dd0d428cb8b9c361a0ca23d9.png]: https://img-blog.csdnimg.cn/9e54d438dd0d428cb8b9c361a0ca23d9.png
[cdcbd7edddfe478191f0a8c2f6295131.png]: https://img-blog.csdnimg.cn/cdcbd7edddfe478191f0a8c2f6295131.png