Shulou(Shulou.com)06/01 Report--

[toc]

RDBMS data is collected regularly to the preface of HDFS.

In fact, it is not difficult to use sqoop to import from MySQL to HDFS regularly, mainly the knowledge of the use of sqoop commands and the operation of Linux scripts.

Scenario in our scenario, the user data added to the database needs to be collected into HDFS every day. There is a time field in the database to identify the time when the user information is entered into the database, so the following data import operation also depends on this field. Data preparation

Prepare the following data in the MySQL database:

# # Operation create DATABASE IF NOT EXISTS db_log_statics;use db_log_statics;CREATE TABLE `troomuserinfo` (`id` bigint (20) NOT NULL, `name` varchar (20) COLLATE utf8_bin DEFAULT NULL, `address` varchar (20) COLLATE utf8_bin DEFAULT NULL, `time` date DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;insert into `troomuserinfo` (`id`, `name`, `address`, `time`) values ('1Zhangzhangsan', 'Beijing Chaoyang', '2018-04-05') Insert into `troomuserinfo` (`id`, `name`, `address`, `time`) values ('2Zhi Magi' Li Si', 'Henan Luoyang', '2018-04-05'); insert into `troomuserinfo` (`id`, `name`, `address`, `time`) values (' 3ZHANGCHENG 'Wangwu,' Guangdong Shaoyang', '2018-04-05'); insert into `troomuserinfo` (`id`, `name`, `address`, `time`) values (' 4Zhaoliu', 'Shandong Dezhou', '2018-04-07') Insert into `troomuserinfo` (`id`, `name`, `address`, `time`) values ('5Zhone`weekdays', 'Shandong Qingdao', '2018-04-07'); insert into `troomuserinfo` (`id`, `name`, `address`, `time`) values (' 6Zhi insert into 'Wei eight,' Fujian Xiamen', '2018-04-07'); insert into `troomuserinfo` (`id`, `name`, `address`, `time`) values (' 7Zhi Jing'Wang Ermazi', 'Shanxi Wutaishan', '2018-04-06') Insert into `troomuserinfo` (`id`, `name`, `address`, `time`) values ('8Zhi Magi' Wang Dahun', 'Shaanxi Yulin', '2018-04-06'); insert into `troomuserinfo` (`id`, `name`, `address`, `time`) values (' 9yunling 'Yanxiaoliu,' Yunnan Dali', '2018-04-06'); insert into `troomuserinfo` (`id`, `name`, `address`, `time`) values (' 10cm', 'Rebs', 'Hubei Xiantao', '2018-04-06') Scripting exportUser2HDFS.shemake day ago binbinapacheenv bash# shell variable source / home/uplooking/shells/db-mysql.conf# source system environment variable source ~ / .date variable today= `date +% Ymuri% mmurf% d`yesterday = `date-d "1 day ago" +% Ymuri% mmurf% d` / home/uplooking/app/sqoop/bin/sqoop import\-- connect jdbc:mysql://$ {stat_ipaddr}: ${stat _ port} / ${stat_dbname}\-- username ${stat_uname}\-- password ${stat_upwd}\-- target-dir hdfs://ns1/input/t_user/$ {yesterday}\-query "SELECT id Name, address, time FROM t_user_info WHERE time > ='${yesterday} 'AND time

< '${today}' AND \$CONDITIONS" \-m 1 --fields-terminated-by "," --split-by ","db-mysql.conf#统计库数据库ip地址stat_ipaddr=192.168.43.116#统计库端口stat_port=3306#统计库名称stat_dbname=db_log_statics#统计库用户名stat_uname=root#统计库密码stat_upwd=root编写定时任务crontab -e# 要求每天凌晨2点10分同步数据10 2 * * * /bin/bash /home/uplooking/shells/exportUser2HDFS.sh >

/ dev/null 2 > & 1 &

It should be noted that if you edit the shell script file remotely in Notepad++, it cannot be executed in Linux, because if you look at the file format: set ff in Linux, you will find that it is: fileformat=dos, while normally the file we edit in Linux should be: fileformat=unix, so the solution is: set ff=unix

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.