本想用Bash?结果被Shell的正则表达式恶心到了!还是Python好
思想
把【配置、sqoop、mysql】三者分离
Python2
mysql_to_hdfs.py
用法:把一大堆SQL写进
_queries,SQL之间用双换行分隔,日期用{ymd}
注意:SQL的日期要用双引号,SQL注释要顶格
#!/usr/bin/python2
"""
Usage
mysql_to_hdfs.py [ymd]
e.g.
mysql_to_hdfs.py
mysql_to_hdfs.py 2021-01-01
"""
import os, sys, datetime, re
# constant
IP_ADDR = 'hadoop100'
BASENAME = 'b0'
USERNAME = 'root'
PASSWORD = '123456'
HDFS = '/h0/%s' % BASENAME
QUEUE_NAME = ''
JOB_NAME = ''
# yesterday
yesterday = datetime.date.today() - datetime.timedelta(days=1)
# command line parameters
a = sys.argv
if len(a) > 1:
ymd = datetime.datetime.strptime(a[1], '%Y-%m-%d').strftime('%Y-%m-%d')
else:
ymd = yesterday.strftime('%Y-%m-%d')
def os_system(s):
if os.name == 'posix':
os.system(' '.join(s.split()))
else:
print(' '.join(s.split()))
def remove_mysql_comments(mysql):
mysql = re.sub('^#.+', '', mysql)
mysql = re.sub('^-- .+', '', mysql)
mysql = re.sub(r'^/\*[\s\S]*\*/', '', mysql)
return ' '.join(mysql.split('\n'))
def get_tb_from_sql(query):
tb = re.findall(r'FROM\s+([a-z_0-9]+)\s+', query, re.I)
return tb[0]
def sqoop_import(query):
query = remove_mysql_comments(query)
tb_name = get_tb_from_sql(query)
hdfs = '%s/%s/%s' % (HDFS, tb_name, ymd)
return r'''
sqoop import
-D mapred.job.queue.name={queue_name}
-D mapred.job.name={job_name}
--connect jdbc:mysql://{ip_addr}:3306/{basename}
--username {username} --password {password}
--delete-target-dir --target-dir {hdfs}
--query '{query} AND $CONDITIONS'
--null-string '\\N' --null-non-string '\\N'
--num-mappers 1
'''.format(
queue_name=QUEUE_NAME,
job_name=JOB_NAME,
ip_addr=IP_ADDR,
basename=BASENAME,
username=USERNAME,
password=PASSWORD,
hdfs=hdfs,
query=query,
)
def sqoop_imports(queries):
queries = re.split('\n{2,}', queries.strip())
for query in queries:
if 'WHERE' not in query:
query += ' WHERE 1=1 '
os_system(sqoop_import(query))
_queries = '''
#full
SELECT * FROM t1
-- append
SELECT * FROM t2 WHERE DATE_FORMAT(create_time,"%Y-%m-%d")="{ymd}"
/*
append
amend
*/
SELECT * FROM t3
WHERE (DATE_FORMAT(create_time,"%Y-%m-%d")="{ymd}"
OR DATE_FORMAT(update_time,"%Y-%m-%d")="{ymd}")
'''.format(ymd=ymd)
sqoop_imports(_queries)
ads_to_mysql.py
用法:把一大堆【表名和作为更新标识的列名】写进
_lines,用换行分隔
#!/usr/bin/python2
import os
# constant
IP_ADDR = 'hadoop100'
BASENAME = 'b0'
USERNAME = 'root'
PASSWORD = '123456'
HDFS = '/%s/ads_' % BASENAME
QUEUE_NAME = ''
JOB_NAME = ''
def os_system(s):
if os.name == 'posix':
os.system(' '.join(s.split()))
else:
print(' '.join(s.split()))
def sqoop_export(tb_name, update_key=''):
hdfs = HDFS + tb_name
if update_key:
update_key = '--update-mode allowinsert --update-key ' + update_key
return r'''
sqoop export
-D mapred.job.queue.name={queue_name}
-D mapred.job.name={job_name}
--connect jdbc:mysql://{ip_addr}:3306/{basename}
--username {username} --password {password}
--export-dir {hdfs}
--table {tb_name}
{update_key}
--input-null-string '\\N' --input-null-non-string '\\N'
--num-mappers 1
'''.format(
queue_name=QUEUE_NAME,
job_name=JOB_NAME,
ip_addr=IP_ADDR,
basename=BASENAME,
username=USERNAME,
password=PASSWORD,
hdfs=hdfs,
tb_name=tb_name,
update_key=update_key
)
def main(lines):
for line in lines.strip().split('\n'):
os_system(sqoop_export(*line.strip().split()))
_lines = '''
t1
t2 create_time
t3 create_time,update_time
'''
main(_lines)
待优化
开发、测试、生产 环境 自动切换
https://yellow520.blog.youkuaiyun.com/article/details/121105488
待添加的参数
Sqoop的底层运行任务是MR中Map,默认是4个MapTask
此时涉及到MapTask在YARN上的队列名和作业名
-D mapred.job.queue.name
-D mapred.job.name
待添加的模式
--hive-import
--hive-import和先import到HDFS再LOAD到HIVE有啥区别?
--hive-import更直接,代码更少,支持压缩,除此之外:更快?支持并行?支持列式存储?
安全问题
通常密码这种东西不应该明文写到脚本
优化方案:将密码等配置写入文件,并上传到HDFS,脚本通过读取配置来获取密码
执行问题
os.system出错后会继续执行,因此要使用subprocess.check_output
Python3
# coding:utf-8
from sys import argv
from datetime import date, timedelta
from subprocess import check_output
class Sqoop:
def __init__(self, **kwargs):
# 从命令行获取参数:https://blog.youkuaiyun.com/Yellow_python/article/details/122088401
self.parameters = kwargs
for a in argv[1:]:
k, v = a.split('=', 1)
self.parameters[k.strip()] = v.strip()
@property
def yesterday(self):
yesterday = date.today() - timedelta(days=1)
return yesterday.strftime('%Y-%m-%d')
@property
def ymd(self):
"""获取日期,格式YYYY-MM-DD,默认昨天"""
return self.parameters.get('ymd', self.yesterday)
@staticmethod
def sqoop(cmd):
print(cmd)
return check_output(' '.join(cmd.split()), shell=True).strip()
def sqoop_export(self, table, hdfs, columns, update_key):
cmd = r'''
sqoop export
--connect jdbc:mysql://{host}:{port}/{database}
--username {user} --password {password}
--table {table}
--num-mappers 1
--input-fields-terminated-by '\t'
--input-null-string '\\N' --input-null-non-string '\\N'
--update-mode allowinsert
--update-key {update_key}
--columns {columns}
--export-dir '{hdfs}'
'''.format(
host=self.parameters.get('host', 'localhost'),
port=self.parameters.get('port', 3306),
user=self.parameters.get('user', 'root'),
password=self.parameters.get('password'),
database=self.parameters.get('database'),
hdfs=hdfs,
table=table,
columns=columns,
update_key=update_key,
)
self.sqoop(cmd)
"""
DROP TABLE IF EXISTS `pv`;
CREATE TABLE `pv`(
`ymd` DATE PRIMARY KEY,
`pv_1d` INT COMMENT 'page view',
`pv_3d` INT COMMENT '近3天PV',
`pv_7d` INT COMMENT '近7天PV',
`uv_1d` INT COMMENT 'unique visitor',
`uv_3d` INT COMMENT '近3天UV',
`uv_7d` INT COMMENT '近7天UV'
) COMMENT '页面浏览';
"""
s = Sqoop(
host='hadoop107',
database='analy_ec',
password='密码',
)
s.sqoop_export(
table='pv',
hdfs='hdfs://hadoop105:8020/user/hive/warehouse/ec.db/ads/pv',
columns='ymd,pv_1d,pv_3d,pv_7d,uv_1d,uv_3d,uv_7d',
update_key='ymd'
)
"""
Sqoop数据同步类
待完善参数:
-D mapred.job.queue.name
-D mapred.job.name
"""
from my_utils.shell import ParameterUtil
class Sqoop(ParameterUtil):
def sqoop(self, cmd):
# self.debug(cmd)
return self.evaluate(' '.join(cmd.split()))
def hive_import(self, hive_tb, query, partition_key='ymd', partition_value=None):
return self.sqoop(r'''
{sqoop} import
--hive-import
--hive-overwrite
--null-string '\\N' --null-non-string '\\N'
--num-mappers 1
--hive-drop-import-delims
--connect jdbc:mysql://{host}:{port}/{mysql_db}?serverTimezone=Asia/Shanghai
--username '{mysql_user}'
--password '{mysql_pwd}'
--hive-database {hive_db}
--hive-table {hive_tb}
--hive-partition-key {partition_key}
--hive-partition-value {partition_value}
--query '{query} AND $CONDITIONS'
--target-dir /temp/sqoop/{hive_db}/{hive_tb}
--delete-target-dir
--compress
'''.format(
sqoop=self.get('sqoop', 'sqoop'),
host=self.get('host', 'localhost'),
port=self.get('port', '3306'),
mysql_db=self.parameters['mysql_db'],
mysql_user=self.get('mysql_user', 'root'),
mysql_pwd=self.parameters['mysql_pwd'],
hive_db=self.parameters['hive_db'],
hive_tb=hive_tb,
partition_key=partition_key,
partition_value=partition_value or self.ymd,
query=query.replace("'", '"').format(ymd=self.ymd),
))
def sqoop_import(self, mysql_tb, query):
return self.sqoop(r'''
{sqoop} import
--connect jdbc:mysql://{host}:{port}/{mysql_db}
--username '{mysql_user}'
--password '{mysql_pwd}'
--target-dir /temp/sqoop/{mysql_db}/{mysql_tb}
--delete-target-dir
--query '{query} AND $CONDITIONS'
--null-string '\\N' --null-non-string '\\N'
--num-mappers 1
--compress
'''.format(
sqoop=self.get('sqoop', 'sqoop'),
host=self.get('host', 'localhost'),
port=self.get('port', '3306'),
mysql_db=self.parameters['mysql_db'],
mysql_user=self.get('mysql_user', 'root'),
mysql_pwd=self.parameters['mysql_pwd'],
mysql_tb=mysql_tb,
query=query.replace("'", '"').format(ymd=self.ymd),
))
def hive_export(self, export_dir, mysql_tb, columns, update_key='id'):
return self.sqoop(r'''
{sqoop} export
--connect jdbc:mysql://{host}:{port}/{mysql_db}
--username '{mysql_user}'
--password '{mysql_pwd}'
--table {mysql_tb}
--num-mappers 1
--input-fields-terminated-by '\t'
--input-null-string '\\N'
--input-null-non-string '\\N'
--update-mode allowinsert
--update-key {update_key}
--columns {columns}
--export-dir '{export_dir}'
--staging-table 'sqoop_export_{mysql_tb}'
--clear-staging-table
'''.format(
sqoop=self.get('sqoop', 'sqoop'),
host=self.get('host', 'localhost'),
port=self.get('port', '3306'),
mysql_db=self.parameters['mysql_db'],
mysql_user=self.get('mysql_user', 'root'),
mysql_pwd=self.parameters['mysql_pwd'],
mysql_tb=mysql_tb,
columns=columns,
update_key=update_key,
export_dir=export_dir,
))
228



被折叠的 条评论
为什么被折叠?



