Hive(二)：数据操作

逃离我推掉我的手 2023-02-17 02:20 43阅读 0赞

### 文章目录 ###

*  一、Hive数据定义
 *   *  1.1 基本数据类型
     *  1.2 集合数据类型
     *  1.3 复杂数据类型案例实操
     *  1.4 类型转化
 *  二、DDL数据定义
 *   *  2.1 数据库相关操作
     *  2.2 表操作
     *   *  2.2.1 创建表
         *  2.2.2 管理表（内部表）和外部表
         *  2.2.3 分区表
         *  2.2.4 修改、删除表
 *  三、DML数据操作
 *   *  3.1 数据导出
     *  3.2 数据导入

# 一、Hive数据定义 #

## 1.1 基本数据类型 ##

<table> 
 <thead> 
  <tr> 
   <th>Hive数据类型</th> 
   <th>Java数据类型</th> 
   <th>长度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT</td> 
   <td>byte</td> 
   <td>1byte有符号整数</td> 
  </tr> 
  <tr> 
   <td>SMALINT</td> 
   <td>short</td> 
   <td>2byte有符号整数</td> 
  </tr> 
  <tr> 
   <td>INT</td> 
   <td>int</td> 
   <td>4byte有符号整数</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>long</td> 
   <td>8byte有符号整数</td> 
  </tr> 
  <tr> 
   <td>BOOLEAN</td> 
   <td>boolean</td> 
   <td>布尔类型，true或者false</td> 
  </tr> 
  <tr> 
   <td>FLOAT</td> 
   <td>float</td> 
   <td>单精度浮点数</td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td>double</td> 
   <td>双精度浮点数</td> 
  </tr> 
  <tr> 
   <td>STRING</td> 
   <td>string</td> 
   <td>字符系列。可以指定字符集。可以使用单引号或者双引号。</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td></td> 
   <td>时间类型</td> 
  </tr> 
  <tr> 
   <td>BINARY</td> 
   <td></td> 
   <td>字节数组</td> 
  </tr> 
 </tbody> 
</table>

对于`Hive`的`String`类型相当于数据库的`varchar`类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

## 1.2 集合数据类型 ##

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>描述</th> 
   <th>语法示例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>STRUCT</td> 
   <td>和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。</td> 
   <td>struct() 例如struct&lt;street:string, city:string&gt;</td> 
  </tr> 
  <tr> 
   <td>MAP</td> 
   <td>MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键-&gt;值对是’first’-&gt;’John’和’last’-&gt;’Doe’，那么可以通过字段名[‘last’]获取最后一个元素</td> 
   <td>map() 例如map&lt;string, int&gt;</td> 
  </tr> 
  <tr> 
   <td>ARRAY</td> 
   <td>数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。</td> 
   <td>Array() 例如array</td> 
  </tr> 
 </tbody> 
</table>

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

## 1.3 复杂数据类型案例实操 ##

① 假设某表有如下一行，我们用`JSON`格式来表示其数据结构。在`Hive`下访问的格式为

{ 
        "name": "songsong",
        "friends": ["bingbing" , "lili"] , //列表Array, 
        "children": {                       //键值Map,
            "xiao song": 18 ,
            "xiaoxiao song": 19
        }
        "address": {                       //结构Struct,
            "street": "hui long guan" ,
            "city": "beijing" 
        }
    }

② 基于上述数据结构，我们在`Hive`里创建对应的表，并导入数据。 创建本地测试文件`test.txt`

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

③ `Hive`上创建测试表`test`

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';`：行分隔符

## 1.4 类型转化 ##

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

**隐式类型转换规则：**

1.  任何整数类型都可以隐式地转换为一个范围更广的类型，如`TINYINT`可以转换成`INT`，`INT`可以转换成`BIGINT`
2.  所有整数类型、`FLOAT`和`STRING`类型都可以隐式地转换成`DOUBLE`
3.  `TINYINT`、`SMALLINT`、`INT`都可以转换为`FLOAT`
4.  `BOOLEAN`类型不可以转换为任何其它的类型

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

例如`CAST('1' AS INT)`将把字符串’1’ 转换成整数1；如果强制类型转换失败，表达式返回空值`NULL`。

0: jdbc:hive2://hadoop102:10000> select '1'+2, cast('1'as int) + 2;

# 二、DDL数据定义 #

## 2.1 数据库相关操作 ##

**创建数据库：**

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

字段解释：  
`[IF NOT EXISTS]`：数据库如果存在，则不新增

`[COMMENT database_comment]`：添加数据库相关注释

`[LOCATION hdfs_path]`：指定数据库在`HDFS`上存储的位置，默认位置为`/user/hive/warehouse/*.db`

**显示数据库**: `show databases;`

**过滤显示查询的数据库**：`show databases like 'db_hive*';`

**显示数据库信息：** `desc database db_hive;`

**切换当前数据库：** `use db_hive;`

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

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');

**删除数据库：** `drop database db_hive2;`

**删除非空数据库：** `drop database db_hive cascade;`

## 2.2 表操作 ##

### 2.2.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** ：为表初始化数据
8.  **STORED AS**：指定存储文件类型。常用的存储文件类型：`SEQUENCEFILE`（二进制序列文件）、`TEXTFILE`（文本）、`RCFILE`（列式存储格式文件）。如果文件数据是纯文本，可以使用`STORED AS TEXTFILE`。如果数据需要压缩，使用`STORED AS SEQUENCEFILE`。
9.  **LOCATION** ：指定表在`HDFS`上的存储位置
10. **AS**：后跟查询语句，根据查询结果创建表
11. **LIKE**：允许用户复制现有的表结构，但是不复制数据

### 2.2.2 管理表（内部表）和外部表 ###

**内部表：**

默认创建的表都是所谓的管理表，有时也被称为内部表，`Hive`会控制着数据的生命周期。`Hive`默认情况下会将这些表的数据存储在由配置项`hive.metastore.warehouse.dir`所定义的目录的子目录下。当我们删除一个管理表时，`Hive`也会删除这个表中数据。管理表不适合和其他工具共享数据。

普通创建表：`create table if not exists student( id int, name string)`

查询表的类型：

hive (default)> desc formatted student;
    Table Type:             MANAGED_TABLE

**外部表**：

因为表是外部表，所以`Hive`并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

创建外部表：`create external table if not exists student( id int, name string)`

查询表的类型：

hive (default)> desc formatted student;
    Table Type:            EXTERNAL_TABLE

**管理表和外部表的使用场景：**

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

**管理表与外部表的互相转换：**

修改表为外部表：`alter table student set tblproperties('EXTERNAL'='TRUE');`  
修改表为管理表：`alter table student set tblproperties('EXTERNAL'='FALSE');`

### 2.2.3 分区表 ###

分区表实际上就是对应一个`HDFS`文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。`Hive`中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过`WHERE`子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

**创建分区表：**

hive (default)> create table dept_partition(
    deptno int, dname string, loc string
    )
    partitioned by (month string)
    row format delimited fields terminated by '\t';

注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

**加载数据到分区表中：**

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');

**查询分区表中数据**：`hive (default)> select * from dept_partition where month='201709';`

**增加分区**：`hive (default)> alter table dept_partition add partition(month='201706') ;`

**删除分区**：`hive (default)> alter table dept_partition drop partition (month='201704');`

**查看分区表有多少分区**：`hive> show partitions dept_partition;`

**查看分区表结构**：

hive> desc formatted dept_partition;
    # Partition Information 
    # col_name data_type comment 
    month                   string

### 2.2.4 修改、删除表 ###

**重命名表**：`ALTER TABLE table_name RENAME TO new_table_name`

**添加列**：`hive (default)> alter table dept_partition add columns(deptdesc string);`

**更新列**：`hive (default)> alter table dept_partition change column deptdesc desc int;`

**删除表**：`hive (default)> drop table dept_partition;`

**清除表数据：**`hive (default)> truncate table student;`

# 三、DML数据操作 #

## 3.1 数据导出 ##

**① Insert导出**

将查询的结果导出到本地：

hive (default)> insert overwrite local directory '/opt/module/datas/export/student' 
    	select * from student;

将查询的结果格式化导出到本地：

hive(default)>insert overwrite local directory '/opt/module/datas/export/student1'
               ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             select * from student;

将查询的结果格式化导出到本地：

hive(default)>insert overwrite local directory '/opt/module/datas/export/student1'
               ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             select * from student;

**② Hadoop 命令导出到本地**

hive (default)> dfs -get /user/hive/warehouse/student/month=201709/000000_0
    /opt/module/datas/export/student3.txt;

**③ Hive Shell 命令导出**

[root@hadoop100 hive]$ bin/hive -e 'select * from default.student;' >
     /opt/module/datas/export/student4.txt;

**④ Export 导出到 HDFS 上**

(defahiveult)> export table default.student to
     '/user/hive/warehouse/export/student';

**⑤ Sqoop 导出**

## 3.2 数据导入 ##

**① 向表中装载数据（Load）**

**语法：**

hive> load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student
    [partition (partcol1=val1,…)];

1.  **load data**：表示加载数据
2.  **local**：表示从本地加载数据到`Hive`表；否则从`HDFS`加载数据到`Hive`表
3.  **inpath**：表示加载数据的路径
4.  **overwrite**：表示覆盖表中已有数据，否则表示追加
5.  **into table**：表示加载到哪张表
6.  **student**：表示具体的表
7.  **partition**：表示上传到指定分区

**② 通过查询语句向表中插入数据（Insert）**

hive (default)> insert overwrite table student partition(month='201708')
                 select id, name from student where month='201709';

`insert into`：以追加数据的方式插入到表或分区，原有数据不会删除  
`insert overwrite`：会覆盖表或分区中已存在的数据

**③ 查询语句中创建表并加载数据（As Select）**

create table if not exists student3
    as select id, name from student;

**④ 创建表时通过Location指定加载数据路径**

hive (default)> create external table if not exists student5(
                  id int, name string
                  )
                  row format delimited fields terminated by '\t'
                  location '/student;

**⑤ Import数据到指定 Hive 表中**

hive (default)> import table student2 partition(month='201709') from
     '/user/hive/warehouse/export/student';