Increment_Backup_To_Hive
一个增量备份关系数据库(MySQL, PostgreSQL, SQL Server, SQLite, Oracle 等)到 hive 的 php 脚本工具
原理
由于 sqoop 可定制性太差,本工具针对增量备份场景,备份某张表时只需要用户填写几个关键参数,就能自动化生成 hive 表,把脚本加入 cron 就能实现每天增量备份了。增量备份时,脚本主要根据表的自增主键来查询新增数据,然后把新增的数据按照 hive 表的格式导出成文本,最后调用 hive 命令直接把文本导入 hive 内部。支持压缩表+多分区+字段增减。
环境
脚本内部会调用 hive 命令,所以必须运行在安装 hive 的 Linux 主机上。你需要安装 PHP5.4 以上的版本,推荐安装 PHP7.x
使用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目录下,并且修改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
$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