Increment_Backup_To_Hive

一个增量备份关系数据库(MySQL, PostgreSQL, SQL Server, SQLite, Oracle 等)到 hive 的 php 脚本工具

原理

由于 sqoop 可定制性太差，本工具针对增量备份场景，备份某张表时只需要用户填写几个关键参数，就能自动化生成 hive 表，把脚本加入 cron 就能实现每天增量备份了。增量备份时，脚本主要根据表的自增主键来查询新增数据，然后把新增的数据按照 hive 表的格式导出成文本，最后调用 hive 命令直接把文本导入 hive 内部。支持压缩表+多分区+字段增减。

环境

1. 脚本内部会调用 hive 命令，所以必须运行在安装 hive 的 Linux 主机上。你需要安装 PHP5.4 以上的版本，推荐安装 PHP7.x
2. 使用PDO扩展来查询关系数据库，你需要确认你的 PHP 安装了PDO扩展以及对应数据库适配器。MySQL 需要PDO+pdo_mysql+mysqlnd扩展，PostgreSQL 需要PDO+pdo_pgsql+pgsql扩展，SQL Server 需要PDO+pdo_dblib等。用php -m来查看你是否安装了对应扩展

用法

• 下载本 repo 到安装 hive 和 php 的 linux 主机上，进入 databases 目录，可以看到有一个 test_database 的样例，里面有一个备份 MySQL 表的例子 test_table1.php （用户测试时导入databases/test_database/test_table1.sql这个建表文件到 MySQL 中即可）。假如你要备份一个名叫my_database数据库中的my_table1表，那么就在 databases 目录下新建一个名为 my_database 的目录，复制databases/test_database/config.ini过来到databases/my_database目录下并且修改 PDO 数据库连接参数。然后复制/databases/test_database/test_table1.php到databases/my_database目录下并且改名为my_table1.php，打开文件my_table1.php并且按照你的需要修改参数，这些参数的意义见“参数意义”内容。
• my_table1.php参数修改好了以后就执行php my_table1.php create，这个操作根据数据源自动生成创建 hive 表的 sql 文件databases/my_database/data/my_table1-schema.sql，你可以根据需要修改之。一旦执行php my_table1.php create完成后，那么你就不能再修改my_table1.php的参数了。如果你想从新来过才能修改my_table1.php的参数，仍然执行php my_table1.php create，这会删除旧的 hive 表以及databases/my_database/data目录下该表有关的数据，然后开始全新的建表。
• 接下来执行php my_table1.php backup就能进行备份了测试了，如果没有错误的话你可以按回车键就能使备份安全停止，程序需要花一段时间才能退出（最好不要使用Ctrl+C的方式来打断备份），为了能每天定时备份你需要复制databases/test_database/cron.sh到databases/my_database目录下，并且修改cron.sh中对应的内容为my_table1.php，这样只需要把 cron.sh 加入系统 cron 就能每天增量备份了。比如你希望每天凌晨 1 点运行 cron.sh ，那么在 crontab 中加入0 1 * * * /bin/bash /path/to/cron.sh即可。要检查 cron 备份是否出错只需要查看databases/my_database/cron_error.log的内容即可，更详细的 log 在databases/test_database/log目录下。

参数意义

• $TABLE:要备份的表名

• $TABLE_AUTO_INCREMENT_ID :表中用来进行增量备份的自增 INT 列，由于会使用类似SELECT * FROM table WHERE id>=M AND id<M+1000这种遍历方式，所以自增 INT 列必须加上索引。如果该表没有自增 INT 列，设置$TABLE_AUTO_INCREMENT_COLUMN = null;即可，此时会使用SELECT * FROM table LIMIT M,1000这种遍历方式，如果记录数太大性能会急剧下降，而且数据只能插入不能删除

• $TABLE_BATCH:每次从数据源读多少行数据

• $HIVE_DB:导入 hive 数据库名，没有则自动创建

• $HIVE_TABLE:要创建的 hive 表名

• $HIVE_FORMAT:创建 hive 表的格式，如果本身表体积就不大可以直接使用默认的 TEXTFILE 纯文本格式，此时设置$HIVE_FORMAT = null；对于占用磁盘太大的表使用 RCFILE 格式压缩，此时设置$HIVE_FORMAT = "RCFILE";即可；使用 RCFILE 格式时，脚本在创建了名为table的 RCFILE 格式的 hive 表之后会再创建一个名为table__tmp的 TEXTFILE 的临时 hive 表，从数据源把数据导入了table__tmp表之后再转存到table表，最后清空table__tmp表

• $ROW_CALLBACK_PARTITIONS:hive 表的分区策略，有 2 种情况。 第一：不要分区，此时设置$ROW_CALLBACK_PARTITIOnS= null;即可 第二：根据数据源读到的每行字段来确定分区，此时自己设置一个以表的行数据为参数的回调函数的数组即可，数组键为分区名(分区类型只能为 STRING)，比如：

 (a),假如 created_date 字段代表插入时间，类型为 TIMESTAMP，按照天分区 $ROW_CALLBACK_PARTITIOnS= [ 'partition_day' => function(Array $row) { $created_date = empty($row['created_date'])?'0000-00-00 00:00:00':$row['created_date']; $partition = substr($created_date, 0, 10); return $partition; } ]; (b),多分区情况下，假如 created_date 字段代表插入时间，类型为 INT，按照月分区；假如 province 字段代表省，按照省分区 $ROW_CALLBACK_PARTITIOnS= [ 'partition_month' => function(Array $row) { $created_date = empty($row['created_date'])? 0:$row['created_date']; $created_date_str = date('Y-m-d H:i:s', $created_date) $partition = substr($created_date, 0, 7); return $partition; }, 'partition_province' => function(Array $row) { $province = empty($row['province'])? "default":$row['province']; return $province; } ]; (c),表中没有分区字段，现在按照备份时间进行按天分区 $ROW_CALLBACK_PARTITIOnS= [ 'partition_day' => function(Array $row) { $created_date = time() $created_date_str = date('Y-m-d H:i:s', $created_date) $partition = substr($created_date, 0, 7); return $partition; } ]; 

• $ROW_CALLBACK_CHANGE:如果从数据源读到的行数据和 hive 中不一样，比如你对自动生成的 hive 表增减了一些字段，此时你需要对每一行的数据进行处理满足 hive 表的格式，返回的数组$row 的字段顺序必须和对应的 hive 表一致，如果不一致程序会检测到错误并退出，比如：

(a),假如数据源表有"id, tel, birthday"这 3 个字段，你修改了自动生成的 hive 建表文件，把`tel`字段进行加密，把 birthday 改成 birth_year 字段，你的 hive 字段为"id, tel, birth_year"。 $ROW_CALLBACK_CHANGE=function (Array $row) { //$row 数组为：['id'=>1, 'tel'=>'15888888888', 'birthday'=>'1990-01-01']; $row['tel'] = my_encrypt_fun($row['tel']); $row['birth_year']= substr($row['birthday'], 0, 4); unset($row['birthday']; //$row 数组为：['id'=>1, 'tel'=>'encrypted content', 'birth_year'=>'1990'];和 hive 表结构一致 return $row; } 

• $EXPORTED_FILE_BUFFER:文本文件缓存大小(Byte)，脚本会把数据缓存到本地文件中，最后再统一导入 hive，默认的 null 为 8G
• $WORK_DIR:设置工作目录，必须为__DIR__

注意

• 自动生成的 hive 表的所有字段类型都是 STRING，用 STRING 保存关系数据库的 INT,FLOAT,DECIMAL 等并没有问题，但是如果是二进制 BLOB 的话需要用$ROW_CALLBACK_CHANGE参数来 base64 编码

github

https://github.com/gouchaoer/Increment_Backup_To_Hive