datax 配置postgresql到HDFS一键生成脚本

最新推荐文章于 2023-11-02 16:14:52 发布

qq_45688530

最新推荐文章于 2023-11-02 16:14:52 发布

阅读量567

点赞数 1

文章标签： postgresql hdfs mysql

本文链接：https://blog.csdn.net/qq_45688530/article/details/125412026

版权

该脚本主要用于从PostgreSQL数据库到HDFS的数据迁移。它连接到指定的PostgreSQL数据库，获取表的元数据，转换为适合HDFS存储的格式，并生成JSON配置文件。配置文件包含了数据迁移的读取器（PostgreSQLreader）和写入器（HDFSwriter）参数，用于数据通道工具如DataX。此外，脚本还支持通过命令行参数指定源数据库和表名。

摘要由CSDN通过智能技术生成

vim ~/bin/gen_import_config.py

# coding=utf-8
import json
import getopt
import os
import sys
import psycopg2

#MySQL相关配置，需根据实际情况作出修改
postgreSQL_host = "ip地址"
postgreSQL_port = "5432"
postgreSQL_user = "用户名"
postgreSQL_passwd = "密码"

#HDFS NameNode相关配置，需根据实际情况作出修改
hdfs_nn_host = "ip地址"
hdfs_nn_port = "8020"

#生成配置文件的目标路径，可根据实际情况作出修改
output_path = "/opt/module/datax/job/import"

def get_connection():
return psycopg2.connect(host=postgreSQL_host, port=int(postgreSQL_port), user=postgreSQL_user, password=postgreSQL_passwd)

def get_mysql_meta(database, table):
connection = get_connection()
cursor = connection.cursor()
sql = "SELECT COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS WHERE TABLE_SCHEMA=%s AND TABLE_NAME=%s ORDER BY ORDINAL_POSITION"
cursor.execute(sql, [database, table])
fetchall = cursor.fetchall()
cursor.close()
connection.close()
return fetchall

def get_mysql_columns(database, table):
return map(lambda x: x[0], get_mysql_meta(database, table))

def get_hive_columns(database, table):
def type_mapping(mysql_type):
mappings = {
"bigint": "bigint",
"int": "bigint",
"smallint": "bigint",
"tinyint": "bigint",
"decimal": "string",
"double": "double",
"float": "float",
"binary": "string",
"char": "string",
"varchar": "string",
"datetime": "string",
"time": "string",
"timestamp": "string",
"date": "string",
"text": "string"
}
return mappings[mysql_type]

meta = get_mysql_meta(database, table)
return map(lambda x: {"name": x[0], "type": type_mapping(x[1].lower())}, meta)

def generate_json(source_database, source_table):
job = {
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [{
"reader": {
"name": "postgresqlreader",
"parameter": {
"username": postgreSQL_user,
"password": postgreSQL_passwd,
"column": get_mysql_columns(source_database, source_table),
"splitPk": "",
"connection": [{
"table": [source_table],
"jdbcUrl": ["jdbc:postgresql://" + postgreSQL_host + ":" + postgreSQL_port + "/" + source_database]
}]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS": "hdfs://" + hdfs_nn_host + ":" + hdfs_nn_port,
"fileType": "text",
"path": "${targetdir}",
"fileName": source_table,
"column": get_hive_columns(source_database, source_table),
"writeMode": "append",
"fieldDelimiter": "\t",
"compress": "gzip"
}
}
}]
}
}
if not os.path.exists(output_path):
os.makedirs(output_path)
with open(os.path.join(output_path, ".".join([source_database, source_table, "json"])), "w") as f:
json.dump(job, f)

def main(args):
source_database = ""
source_table = ""

options, arguments = getopt.getopt(args, '-d:-t:', ['sourcedb=', 'sourcetbl='])
for opt_name, opt_value in options:
if opt_name in ('-d', '--sourcedb'):
source_database = opt_value
if opt_name in ('-t', '--sourcetbl'):
source_table = opt_value

generate_json(source_database, source_table)

if __name__ == '__main__':
main(sys.argv[1:])