第7周 · DML数据操作

DML数据操作

1.1数据导入

数据导入Hive表的方式有很多种。可以使用load data操作，通过把文件复制或移动到表的目录中，从而把数据导入Hive的表中。也可以用Insert 语句把数据从一个Hive表填充到另外一个表，或者在新建表的时候使用CTAS（CREATE TABLE ...AS SELECT）结构。

1.1.1使用insert语句插入数据

1）语法

INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

示例

直接通过Insert语句将数据插入表，具体操作如图1-1所示。

insert into table temperature values ('03013','2022',18);

📷 图1-1 insert方式加载数据

由图可以看出，通过Insert方式往表插入数据，底层执行的是MapReduce作业。

1.1.2 使用load语句加载数据

（1）语法

hive>

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)];

（2）关键字说明：

1）local：表示从本地加载数据到Hive表；否则从HDFS加载数据到Hive表。

2）overwrite：表示覆盖表中已有数据，否则表示追加。

3）partition：表示上传到指定分区，若目标是分区表，需指定分区。

（3）示例

1）准备数据集

现在准备一个名为temperature.log文件，里面包含美国各个气象站每年的气温数据，数据的第一列为气象站ID，第二列为年份，第三列为气温值。具体样本数据如下所示：

03103,1980,41

03103,1981,98

03103,1982,70

03103,1983,74

03103,1984,77

再准备一个名为station.log文件，里面包含美国各个气象站的详细信息，数据的第一列为气象站ID，第二列为纬度，第三列为经度，第四列为海拔，第五列为州编码。具体样本数据如下所示：

03013,38.0700,101.6881,1129.0,CO

03016,39.5264,107.7264,1685.5,CO

03017,39.8328,104.6575,1650.2,CO

03024,35.6950,101.3950,930.9,TX

03026,39.2447,101.2842,1277.7,CO

然后将temperature.log和station.log文件上传至hadoop1节点的/home/hadoop/shell/data目录下（目录实现创建）。

2）数据上传至HDFS

首先在HDFS中创建/weather目录，然后将temperature.log和station.log文件上传至该目录下，具体操作如图1-2所示。

bin/hdfs dfs -mkdir /weather

bin/hdfs dfs -put /home/hadoop/shell/data/temperature.log /weather

bin/hdfs dfs -put /home/hadoop/shell/data/station.log /weather

📷 图1-2数据上传至HDFS

3)数据导入

通过load data命令将HDFS中的temperature.log和station.log文件分别加载到temperature和station表，具体操作如图1-3所示。

load data inpath '/weather/temperature.log' overwrite into table temperature;

📷 图1-3 load方式加载数据

如果将本地文件加载到Hive表中，可以使用如下语句：

load data local inpath '/home/hadoop/shell/data/temperature.log' overwrite into table temperature;

load data local inpath '/home/hadoop/shell/data/station.log' overwrite into table station;

1.1.3将查询结果插入表中

（1）语法

INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement;

关键字说明：

1）INTO：将结果追加到目标表

2）OVERWRITE：用结果覆盖原有数据

（2）示例

通过like子句创建表temperature2，然后使用select子句查询temperature表数据并插入temperature2，具体操作如图1-4所示。

create table temperature2 like temperature;

Inser overwrite table temperature2 select * from temperature;

📷 图1-4 select方式插入数据

1.1.4上传数据文件导入数据

可以直接上传相应数据文件到对应目录，便可完成数据导入。

示例：

1）建表

通过create方式创建表temperature3，并指定表的HDFS路径，具体操作如图1-5所示。

create table if not exists temperature3

(id string comment '气象站id',year string comment '年',temperature int comment '气温')

comment '天气表'

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

STORED AS TEXTFILE

location '/user/hive/warehouse/mydb/temperature3';

📷 图1-5创建表temperature3

2）加载数据

通过HDFS的put命令，将本地文件上传至temperature3表的location位置即可完成表的数据加载，具体操作如图1-6所示。

bin/hdfs dfs -put /home/hadoop/shell/data/temperature.log /user/hive/warehouse/mydb/temperature3

📷 图1-6本地文件上传至HDFS

3）表查询

通过select语句查询temperature3表中的数据，具体操作如图1-7所示。

select * from temperature3 limit 3;

📷 图1-7查询temperature3表数据

1.2数据导出

数据导出Hive表的方式也有很多种。可以使用Insert语句把数据从Hive表中导出到本地文件系统或者HDFS，也可以使用CTAS结构将一个Hive表中的数据导出到另外一个表。

1.1.1使用INSERT OVERWRITE LOCAL DIRECTORY语句将temperature表中的数据导出到本地文件系统

（1）语法

INSERT OVERWRITE [LOCAL] DIRECTORY directory

[ROW FORMAT row_format] [STORED AS file_format] select_statement;

（2）示例

具体操作如图1-80所示。

INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/shell/data/temperature.log.20220330' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from temperature;

📷 图1-8 表数据导出到本地文件系统

备注：LOCAL关键字表示将数据导出到本地文件系统，如果去掉LOCAL表示将数据导出到HDFS。

1.1.2使用CTAS结构把Hive查询的结果导出到一个新创建的表

具体操作如图1-9所示。

create table temperature4 as select * from temperature;

📷 图1-9表数据导出到新表

1.1.3直接通过hive -e命令将temperature表中的数据导出到本地文件系统

具体操作如图1-10所示。

bin/hive -e "select * from weather.temperature" >> /home/hadoop/shell/data/temperature.log.20220331

📷 图1-10表数据导出到本地文件系统

也可以先将Hive查询语句封装到temperature.sql文件，具体操作如图1-11所示。

📷 图1-11 将Hive查询语句封装到sql文件中

然后在bash中，通过hive -f命令将temperature表中的数据导出到本地文件系统，具体操作如图1-12所示。

📷 图1-12表数据导出到本地文件系统

1.3 数据备份与恢复

Hive自带了数据的备份和恢复命令，不止数据，包括表结构也可以一同导出。

1.3.1 数据备份

通过export命令对temperature表中的数据进行备份，具体操作如图1-13所示。

export table temperature to '/user/hive/warehouse/mydb/backup/temperature';

📷 图1-13数据备份

Hive执行export命令就是将表结构存储在_metadata文件，并且直接将Hive数据文件复制到备份目录。

1.3.2数据恢复

通过import命令恢复temperature表中的备份，具体操作如图1-14所示。

import table temperature_new from '/user/hive/warehouse/mydb/backup/temperature';

📷 图1-14数据恢复

temperature_new表原来并不存在，当Hive进行import操作时会根据_metadata文件里的信息自动创建，这样便于做Hive表的数据备份恢复或者迁移操作。

是否应为“本地文文件”，请作者加以核实。

II