Hive分区表实战 - 多分区字段

一、实战概述

• 本实战教程通过一系列Hive SQL操作，演示了如何在大数据环境下创建具有省市分区的大学表，并从本地文件系统加载不同地区的学校数据到对应分区。首先，创建名为school的数据库并切换至该数据库；接着，在数据库中定义一个名为university的分区表，其结构包括ID和名称两列，并按照省份和城市进行物理分区。随后，在本地创建多个包含各省市区学校数据的文本文件。

• 在完成数据文件准备后，依次将各地区学校数据文件按省市分区加载到university表中，利用LOAD DATA LOCAL INPATH语句实现数据高效地从本地导入到Hadoop分布式文件系统（HDFS）中的相应分区目录下。

• 最后，通过执行SQL查询语句验证数据加载的正确性，查看全表记录以及特定省份的所有记录，并通过HDFS命令行工具检查分区表对应的目录及文件。此外，还展示了如何在MySQL中查看Hive元数据信息，进一步理解Hive中分区表的实际存储结构和组织方式。整个实战过程旨在帮助用户掌握基于Hive的大规模数据管理和分区表应用技巧。

二、实战步骤

（一）创建学校数据库

• 执行语句：create database school;
  
• 执行语句：use school;，切换到school数据库
  

（二）创建省市分区的大学表

• 执行语句：CREATE TABLE university (id INT, name STRING) PARTITIONED BY (province STRING, city STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ';
  
• 该SQL语句用于在支持分区功能的数据库系统（如Hive、Impala等基于Hadoop的数据仓库）中创建一个名为university的表，并且定义了表的结构以及分区方案。不过，值得注意的是，标准的SQL语法并不直接支持PARTITIONED BY这样的关键字，这是大数据处理框架为了解决大规模数据存储和查询优化而扩展的语法。

CREATE TABLE university (
    id INT,
    name STRING
)

• 这部分定义了一个新表university，它有两个列：
  • id列是整数类型（INT）。
  • name列是字符串类型（STRING）。

PARTITIONED BY (province STRING, city STRING)

• 这部分指定了表的分区键。这意味着表中的数据会按照province和city这两个字段的值进行物理划分。每个分区对应一组特定省份和城市的记录集合。这样做可以提高针对特定省份或城市数据的查询性能，因为只需要扫描相关的数据分区即可，而不是整个表。

ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ' '

• 这部分定义了表数据的格式：

  • ROW FORMAT DELIMITED表示每一行数据都是以某种分隔符结束的。
  • FIELDS TERMINATED BY ' '说明各个字段之间用空格字符作为分隔符。

• 总结起来，这条SQL语句是在创建一个具有ID和名称两列的university表，并按照省份和城市进行了分区，且表中数据是以空格分隔的文本格式存储。这非常适合于处理大型、半结构化的日志文件或者需要按地理位置快速检索的大数据场景。

（三）在本地创建数据文件

1、创建四川成都学校数据文件

• 在master虚拟机上创建sc_cd_schools.txt文件
  

2、创建四川泸州学校数据文件

• 在master虚拟机上创建sc_lz_schools.txt文件
  

3、创建江苏南京学校数据文件

• 在master虚拟机上创建js_nj_schools.txt文件
  

4、创建江苏苏州学校数据文件

• 在master虚拟机上创建js_sz_schools.txt文件
  

（四）按省市分区加载学校数据

1、加载四川成都学校数据文件到四川成都分区

• 加载sc_cd_schools.txt到分区（province='sc', city='cd'）

• 执行语句：LOAD DATA LOCAL INPATH '/root/sc_cd_schools.txt' OVERWRITE INTO TABLE university PARTITION (province='sc', city='cd');
  

• 这段SQL语句是在大数据处理框架如Hive或Impala中使用的，用于从本地文件系统加载数据到已存在的分区表university中，并且会覆盖目标分区已有的数据。

  • LOAD DATA LOCAL INPATH '/root/sc_cd_schools.txt': 这部分指定要加载的数据文件路径。其中LOCAL关键字表示数据文件位于客户端（即运行该命令的机器）本地文件系统中的 /root/sc_cd_schools.txt 路径下。

  • OVERWRITE INTO TABLE university: OVERWRITE 表示如果目标表或者目标分区已有数据，则先删除原有数据再进行加载；INTO TABLE university 指定了将数据加载到名为 university 的表中。

  • PARTITION (province='sc', city='cd'): 由于 university 表是分区表，这里指定了要加载数据到的具体分区，即 province 字段为 'sc' （四川省的缩写）和 city 字段为 'cd' （成都市的缩写）。这意味着数据将会被加载到代表四川省成都市的分区里。

• 总结：该SQL语句的作用是从本地文件系统中读取 /root/sc_cd_schools.txt 文件中的数据，然后将其作为四川省成都市分区的数据，以覆盖的方式加载到名为 university 的分区表中。

2、加载四川泸州学校数据文件到四川泸州分区

• 加载sc_lz_schools.txt到分区（province='sc', city='lz'）

• 执行语句：LOAD DATA LOCAL INPATH '/root/sc_lz_schools.txt' OVERWRITE INTO TABLE university PARTITION (province='sc', city='lz');
  

3、加载江苏南京学校数据文件到江苏南京分区

• 加载js_nj_schools.txt到分区（province='js', city='nj'）
• 执行语句：LOAD DATA LOCAL INPATH '/root/js_nj_schools.txt' OVERWRITE INTO TABLE university PARTITION (province='js', city='nj');
  

4、加载江苏苏州学校数据文件到江苏苏州分区

• 加载js_sz_schools.txt到分区（province='js', city='sz'）
• 执行语句：LOAD DATA LOCAL INPATH '/root/js_sz_schools.txt' OVERWRITE INTO TABLE university PARTITION (province='js', city='sz');
  

（五）查看大学分区表记录

1、查看全部记录

• 执行语句：SELECT * FROM university;
  

2、查看江苏分区记录

• 执行语句：SELECT * FROM university WHERE province = 'js';
  

（六）查看大学分区表对应的目录及文件

• 执行命令：hdfs dfs -ls -R /user/hive/warehouse/school.db
  

（七）在MySQL里查看Hive元数据（分区信息）

1、登录MySQL Hive Metastore数据库

• 执行命令：mysql -uroot -p903213，登录MySQL，然后执行use hive;，打开hive元数据库
  

2、 查看university表分区元数据信息

• 执行语句：SELECT * FROM COLUMNS_V2;，university分区表的CD_ID值是33
  
• 执行语句：SELECT SD_ID, CD_ID, LOCATION FROM SDS WHERE CD_ID = 33;
  

三、实战总结

• 本实战演练通过创建分区表university，并按省市划分加载本地学校数据文件至Hive，展示了大数据环境下高效的数据管理与查询方法。利用分区技术优化存储与查询性能，并通过SQL验证数据加载正确性及查看分区信息，实现了对大规模教育数据的组织和分析。

四、课后练习

• 根据实战教程中的步骤，自行创建一个以国家和城市为分区键的公司信息表（如company），包含字段：id（整数类型）、name（字符串类型）和address（字符串类型）。然后，在本地创建不同国家城市公司的数据文件，并将这些数据按各自对应的国家和城市分区加载到Hive表中。最后，通过SQL查询验证数据加载完整性与正确性。