基于 Amazon S3 和 HIVE 的统计
本文转自：https://www.dazhuanlan.com/2019/11/28/5ddf990c5fcb9/
当业务数量量越来越大时，通过在数据库中写 SQL 语句进行统计就变的越来越困难了，尽管可以通过不断优化 SQL 语句，比如，通过 EXPLAIN 查看执行计划，建立索引，子查询与连接查询选择，等等。但面对越来越大的数据量，还是会无力回天。
这时，可以将业务数据存储到 Hadoop文档系统 上，借助 HIVE 进行统计，然后，再将统计结果导入业务数据库，供业务调用。
大数据统计思路
当统计任务越来越多，并且数据量日益庞大，需要能够管理逻辑复杂的多个Hadoop作业。统计任务大致步骤如下：
- 通过 Sqoop 先将业务数据同步到 Hadoop文档系统 上；
- 对 Hadoop文档系统 上的业务数据，以分区表的形式存储到多张 Hive 表中；
- 进行 JOIN 处理，得到一个明细数据Hive大表（此步骤可选）；
- 借助 MapReduce 计算框架，对 Hive 中的表数据进行复杂的统计，得到统计后的的报表信息；
- 需要将统计分析得到的结果数据同步到业务系统中，供业务调用使用。
上述过程可以通过 workflow 系统来编排任务，最终生成一个 workflow 实例，然后每天定时启动运行这个实例即可。
这里假设业务数据存储在线上业务数据库 MySql 从库 mysql-slave-00 中，而 Hadoop文档系统 选择 Amazon S3， 经过统计后，结果存储到本地文本，或 MySql(mysql-statistics) 中，数据流向如下：
- 从 MySql ( mysql-slave-00 ) 到 Amazon S3
- 从 Amazon S3 到 Hive
- Statistics on Hive
- 从 Hive 到 MySql ( mysql-statistics ) or local text
后面各部分将从第 1 步骤开始说明。
从 MySql( mysql-slave-00 ) 到 Amazon S3
最新业务数据存储在 MySql ( mysql-slave-00 ) 中，需要 upload 到 Amazon S3。
假设要上传表 thu_student 中数据，在安装并配置好 Sqoop 好后，可以在 Shell 脚本中编辑好上传进程，如 sqoopMySqlThuStudent2S3。
注： 这里上传数据到 Amazon S3 使用了 Sqoop。Sqoop是一个用来将 Hadoop 和关系型数据库中的数据相互转移的工具，可以将一个关系型数据库（例如 ： MySQL, Oracle, Postgres等）中的数据导进到 Hadoop文档系统 中，也可以将 Hadoop文档系统 的数据导进到关系型数据库中。
若要上传 2015/04/01 的数据，在 CLI 中 cd 到 /home/whlminds/bin/ 目录（假设 Shell 脚本放置在此目录下），在 CLI 中执行下面命令：
./sqoopMySqlThuStudent2S3.sh 2015/04/01数据成功上传到 Amazon S3 后，假设将存在于存储桶 thu.whlminds.com 中，存储路径为 thu.whlminds.com/mysql/thu_student/2015/04/01 , 上传后结果类似：
- _SUCESS
- part-m-00000
- part-m-00001
- part-m-00002
- part-m-00003
part 中内容由多条原数据库表中记录组成，每一条记录中，字段值之间通过 t 来间隔：
5597092 150401235913fze459278 6459278 4045 ...
从Amazon S3 到 Hive
OK，数据到了 Amazon S3 ，下面就要导入 Hive 的表中为统计准备了，在导入 Hive 前需要在 Hive 中根据结构化的 Amazon S3 中记录，创建对应的 Hive 表。
Hive 中创建表
如果不指定数据库，Hive 会把表创建在 default 数据库下，假设有一个 Hive 的数据库 mydb，要创建表到 mydb,如下:
CREATE TABLE mydb.pokes(foo INT,bar STRING);
或者是
use mydb;
CREATE TABLE pokes(foo INT,bar STRING);
在 Hive 中有两种表，外部表与内部表；
- 创建表，有指定 EXTERNAL 就是外部表，没有指定就是内部表；
- 内部表数据存储在本地，在 drop 的时候会删除数据；
- 外部表在创建表时，指定了数据在 Hadoop文档系统 上的存储位置，并通过 metadata 描述了对应 Hadoop文档系统 上的数据在 Hive 外部表存储的细节，Hive 通过 mysql 存储 metadata，删除外部表时，仅删除了 metadata；
- 外部表和内部表一样，都可以有分区；
创建外部表：
CREATE EXTERNAL TABLE thu_student_s3(
id INT,
stu_no BIGINT,
name STRING,
age INT,
gender TINYINT)
COMMENT 'This is the student info table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'
STORED AS TEXTFILE
LOCATION 's3://thu.whlminds.com/mysql/thu_student';
创建内部表：
CREATE TABLE thu_student_intl(
id INT,
stu_no BIGINT,
name STRING,
age INT,
gender TINYINT)
PARTITIONED BY(major TINYINT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY 't'
STORED AS TEXTFILE;
向Hive表中导入数据
向 Hive 的表中上传数据时，可以在建表时指定数据源，也可以在建好表之后在通过 Hive QL 再 load。
- 建外部表时指定数据源
CREATE EXTERNAL TABLE thu_student_s3(
id INT,
stu_no BIGINT,
name STRING,
age INT,
gender TINYINT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' STORED AS TEXTFILE
LOCATION 's3://thu.whlminds.com/mysql/thu_student/2015/04/01/';
上面建外部表进程中，最后一句 LOCATION 's3://thu.whlminds.com/mysql/thu_student/2015/04/01/' ，即指定了 S3上的数据位置，其中，thu.whlminds.com 为存储桶，/mysql/thu_student/2015/04/01/ 为桶中存储目录。
- 建表后再 load 数据
导入本地数据：
hive> load data local inpath '/home/hadoop/hive/data_stu' into table thu_student_s3;
导入 S3 数据：
load data inpath 's3://thu.whlminds.com/mysql/thu_student/2015/04/01/' into table thu_student_s3
其中，data_stu 为存储了数据的文本文档，thu_student_s3 为 Hive 中的表。
Statistics on Hive
终于 Hive 中有表了，表中有数据了；）
关于如何对 Hive 表中数据通过 Hive QL 进行统计，请前往 GettingStarted-SQLOperations 学习使用，这里说明一点注意问题：
- 在 Hive 中，空值
NULL在底层默认是用N来存储的
可以看到底层数据将 NULL 存储成了 N 。
这样的设计存在一个问题是如果实际想存储 N，那么实际查询出来的也是 NULL 而不是 N 。
Hive 给出一种并非完美的解决方法就是可以自定义底层用什么字符来表示 NULL。
例如我想用字符 a 来表示 NULL ：
hive> alter table thu_student_s3 SET SERDEPROPERTIES('serialization.null.format' = 'a');
这时候底层的存储就变成了 a ，今后插入到这张表中的 a 查询出来就变成了 NULL 而不是 a 。
其实上面说的这个假 NULL 出现的原因就是在默认情况下（即用 N 表示 NULL），插入了 NULL 值，然后又用 SET SERDEPROPERTIES 语句修改了存储NULL的字符串。这时候表的属性修改了，但是底层存储的文档并没有修改。而 N 显示为 NULL 在 Hive 中又是一个特例，于是就出现了这个假 NULL，在开发过程中一定要注意。
从 Hive 到 MySql ( mysql-statistics ) or local text
统计得到结果后，假设结果存储在 Hive 的 thu_student_result 表中，可以将结果导到 mysql 数据库，或者存在本地文档中。
统计结果存入本地
- 方法一
hive> insert overwrite local directory '/home/hadoop/hive/thu_student_result_000'
> row format delimited fields terminated by 't'
> select * from thu_student_result;其中，得到的结果 thu_student_result_000 中每一条记录的字段间是用 t 分割的:
- 方法二
$ hive -e "select * from thu_student_result" >> /home/hadoop/hive/thu_student_result_001
在 thu_student_result_001 查看统计结果，其中，得到的结果中每一条记录的字段间也是用 t 分割的:
$ less thu_student_result_001
- 方法三
首先 touch 一个 sql 文档，写好统计进程，然后在 CLI 中执行如下命令：
$ hive -f stat_student.sql >> /home/hadoop/hive/thu_student_result_002
在 thu_student_result_002 中查看统计结果，其中，得到的结果也是用 t 分割的：
$ less thu_student_result_0002
统计结果导入 MySql
在向 mysql中导入数据时，需要根据以下步骤进行相关配置，Session级别：
- 添加JaR包
hive> add jar /home/hadoop/hive/lib/hive-contrib-0.13.1.jar
Added /home/hadoop/hive/lib/hive-contrib-0.13.1.jar to class
Added resource: /home/hadoop/hive/lib/hive-contrib-0.13.1.jar
hive> add jar /home/whlminds/sqoop/lib/mysql-connector-java-5.1.13.jar;
Added /home/whlminds/sqoop/lib/mysql-connector-java-5.1.13.jar to class
Added resource: /home/whlminds/sqoop/lib/mysql-connector-java-5.1.13.jar
- 在 Hive 中建立
UDF：
hive> CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput';- 在 Hive 中通过
UDF导出数据到 mysql:
hive> select dboutput('jdbc:mysql://xxxxxx.amazonaws.com.cn/thu_university','$username','$password',
> 'INSERT INTO thu_student_result_from_s3(id, stu_no, name) VALUES (?,?,?)', id, stu_no, name) from thu_student_result;
注： thu_university 为 mysql 业务数据库名，thu_student_result_from_s3 为数据库 thu_university 中的表名。 thu_student_result_from_s3(id, stu_no, name)，括号中的字段为 mysql 表 thu_student_result_from_s3 字段，values(?,?,?) 对应 Hive 统计结果的值，后面的 id, stu_no, name 为 hive 表中的字段，thu_student_result 表示 Hive 中的表名称。
另外，$username 和 $password 是 thu_university 用户名和密码。
通过以上步骤即可将 Hive 统计结果直接导入到 mysql 数据库中。
总结
大数据量统计通过在数据库中写SQL语句已经力所不及，可以通过将线上数据同步到 Hadoop文档系统，并使用 HVIE 进行统计分析，最后，将统计结果同步到业务数据库供使用。