Python从0到1手搓一个ETL工具(附源代码)

先说使用方法，后附源代码，本例以Oracle为例

1. SQL编写：本例增量字段使用EVENT_TIME(也可以用其它增量字段)，需将原增量字段改名为UPDATE_TIME，ETL_TIME用于存储ETL执行时间

SELECT 
	TO_CHAR(A.EVENT_TIME-8/24,'YYYYMMDD') DATE_TIMEKEY,  --工厂一般以早上作为一天的开始，例如8点
	CASE WHEN TO_CHAR(A.EVENT_TIME-8/24,'HH24') < '12' 
		THEN TO_CHAR(A.EVENT_TIME-8/24,'YYYYMMDD')||'D'
		ELSE TO_CHAR(A.EVENT_TIME-8/24,'YYYYMMDD')||'N' 
	END SHIFT_TIMEKEY, 
	B.WEEK_TIMEKEY, B.MONTH_TIMEKEY, A.EVENT_TIME, A.MATERIAL_ID
	,TO_CHAR(EVENT_TIME,'YYYY-MM-DD HH24:MI:SS') UPDATE_TIME
    ,SYSDATE ETL_TIME
FROM  
	MAT_MADE_BY_TIME A
LEFT JOIN CALENDAR B 
	ON TO_CHAR(EVENT_TIME-8/24,'YYYYMMDD') = B.DAY_TIMEKEY 
WHERE 1=1
	AND EVENT_TIME > '2024-07-01 07:00:00' --首次数据开始时间
	AND EVENT_TIME < '2024-07-25 08:00:00' --首次数据截止时间

2. 创建表：字段及类型跟1中输出数据保持一致，注意在末尾增加一个字段ETL_TIME格式为Date格式(本例以Oracle格式为例)

注意数据量大的话建立适当分区、索引、主键等，再写入数据

CREATE TABLE RECIVE_TAB 
(DATE_TIMEKEY VARCHAR2(50),
SHIFT_TIMEKEY VARCHAR2(50),
WEEK_TIMEKEY VARCHAR2(50),
MONTH_TIMEKEY VARCHAR2(50),
UPDATE_TIME VARCHAR2(50),
ETL_TIME DATE
)

3. 首次建立一个ETL增量表，用于存储各ETL任务的增量数据，以及ETL SQL内容

CREATE TABLE ETL_INCREMENT 
   (	"TASK_ID" NUMBER, --任务唯一ID，不重复
	"OWNER" VARCHAR2(100),  --任务创建者
	"TABLE_NAME" VARCHAR2(100),  --目标表名称
	"INCREMENTLEVEL" VARCHAR2(100),  --用于存储增量字段，每次ETL运行后会更新，首次填写数据希望开始导入的时间点
	"READ_SQL" VARCHAR2(4000),  --用于存储ETL SQL
	"UPDATE_TIME" DATE DEFAULT SYSDATE --数据更新时间，每次ETL运行后会更新
    CONSTRAINT ETL_INCREMENT_PK PRIMARY KEY (TASK_ID)
   )

每创建一个ETL任务，在这个表中增加一行，并写入相应数据，注意READ_SQL字段中，

写入READ_SQL字段时注意几点：
1)最后一定要带上编号限制：WHERE TASK_ID = ?，否则就把所有人任务都更新成你的了;
2)将1中SQL的单引号变成两个单引号
3)增量字段>时间替换为{}，增量字段<时间注释掉(首次跑数据时间较长，如果不想一次跑完，就先不注释，填上跑数截止时间)

案例：

UPDATE ETL_INCREMENT 
SET READ_SQL = '
SELECT 
	TO_CHAR(A.EVENT_TIME-8/24,''YYYYMMDD'') DATE_TIMEKEY,  --工厂一般以早上作为一天的开始，例如8点
	CASE WHEN TO_CHAR(A.EVENT_TIME-8/24,''HH24'') < ''12'' 
		THEN TO_CHAR(A.EVENT_TIME-8/24,''YYYYMMDD'')||''D''
		ELSE TO_CHAR(A.EVENT_TIME-8/24,''YYYYMMDD'')||''N'' 
	END SHIFT_TIMEKEY, 
	B.WEEK_TIMEKEY, B.MONTH_TIMEKEY, A.EVENT_TIME, A.MATERIAL_ID
	,TO_CHAR(EVENT_TIME,''YYYY-MM-DD HH24:MI:SS'') UPDATE_TIME
	,TO_DATE(''{1}'',''YYYY-MM-DD HH24:MI:SS'') ETL_TIME --当前时间用{1}替换
FROM  
	MAT_MADE_BY_TIME A
LEFT JOIN CALENDAR B 
	ON TO_CHAR(EVENT_TIME-8/24,''YYYYMMDD'') = B.DAY_TIMEKEY 
WHERE 1=1
	AND EVENT_TIME > ''{0}'' --首次数据开始时间
	AND EVENT_TIME < ''2024-07-25 08:00:00'' --首次数据截止时间,数据跑到当前，则注释掉此行
'
WHERE TASK_ID = 1;

4. 首次建立一个ETL日志记录表

CREATE TABLE ETL_LOG
(	TASK_ID NUMBER, 
	OWNER VARCHAR2(100), 
	TABLE_NAME VARCHAR2(100), 
	START_TIMEKEY VARCHAR2(100), 
	END_TIMEKEY VARCHAR2(100), 
	COMMENTS VARCHAR2(4000), 
	START_TIME DATE, 
	END_TIME DATE, 
	TASK_TIME NUMBER, 
	TASK_PATH VARCHAR2(500) INVISIBLE, 
	UPDATE_TIME DATE DEFAULT SYSDATE
   )

数据库中准备工作完成，ETL每运行一次，会在日志表中增加一条记录

5. 在可访问数据库的桌面添加定时任务
1)将ETL工具文件拷贝到本地盘(ETL工具即通过源代码打包成的可执行文件，Python打包方法可参考Python打包exe文件(如何打包成较小文件)-CSDN博客)
2)路径：控制面板→系统和安全→管理工具→计划任务
3)新建一个基本任务：命名为task_id编码+表名称，选择执行时间和频率，ETL工具文件，添加参数：TASK_ID(直接填数字)
4)确认后可以右键手动执行一次，去看日志记录表ETL_LOG_PYTHON和增量表ETL_INCREMENT有无更新

6. 源代码附如下：

import cx_Oracle
import pandas as pd
from datetime import datetime,timedelta
#import argparse
import sys

starttime = datetime.now()
thistime = starttime.strftime('%Y-%m-%d %H:%M:%S')
comment = ''
task_id = 0
owner=''
table_name=''
update_condition=''
start_timekey=''
end_timekey=''

try:
    #获取ETL编号
    """
    parser = argparse.ArgumentParser(description="Demo of argparse")
    parser.add_argument('-t', '--task_id', help='INPUT task_id')
    args = parser.parse_args()
    task_id = args.task_id
    if not task_id:
        raise ValueError("未输入正确的task_id")
    """
    task_id = sys.argv[1]
    #数据库连接信息
    db1 = cx_Oracle.connect('username','password','101.101.101.102:1521/orcl')
    #读取task_id对应信息
    sql0 = """
    SELECT TASK_ID, OWNER, TABLE_NAME, INCREMENTLEVEL,read_sql FROM ETL_INCREMENT  
    WHERE TASK_ID = {}
    """.format(task_id)
    data = pd.read_sql(sql0,db1)
    _task_id,owner,table_name,start_timekey,read_sql = data.iloc[0]
    
    
    #写入SQL拼写
    read_sql = read_sql.replace('{0}',start_timekey).replace('{1}',thistime)
    write_sql = """
    INSERT INTO {} 
    {}
    """.format(table_name,read_sql)
    

    #写入数据
    cr1 = db1.cursor()
    cr1.execute(write_sql)
    db1.commit()
    #记录SQL执行条数
    sql_rows = cr1.rowcount
    comment += '导入成功,新增了{}条记录;'.format(sql_rows)
    
    #查找本次更新截止时间
    cr1.execute("select max(update_time) from {} where interface_time = :1".format(table_name),[starttime])
    end_timekey = cr1.fetchone()[0]

    #写入增量表
    if end_timekey is not None and end_timekey > start_timekey: 
        cr1.execute("update ETL_INCREMENT set INCREMENTLEVEL = :1,UPDATE_TIME = :1 where TASK_ID = :1",[end_timekey,starttime,task_id])
        db1.commit()
    cr1.close()
    db1.close()
except Exception as ex:
    comment += "导入失败,错误类型:{}".format(ex)
#---------------------以下内容通常无需改动----------------------
#获取程序运行的信息
import sys
import cx_Oracle
import socket
import os
import time
pcname = socket.gethostname()
ip = socket.gethostbyname(pcname)
file_address = sys.argv[0]
modify_time = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(os.path.getmtime(file_address)))
_taskname = os.path.basename(file_address).rsplit('.',1)[0] #文件名作为任务名称
task_path = "{}({})->{}({}更新)".format(pcname,ip,file_address,modify_time) 
#写入日志表
db = cx_Oracle.connect('username','password','101.101.101.102:1521/orcl')
cr = db.cursor()
endtime = datetime.now()
cr.execute('''INSERT INTO ETL_LOG(TASK_ID,OWNER,TABLE_NAME,START_TIMEKEY,END_TIMEKEY,COMMENTS,START_TIME,END_TIME,TASK_TIME,TASK_PATH) 
VALUES(:1,:1,:1,:1,:1,:1,:1,:1,:1,:1)
''',[task_id,owner,table_name,start_timekey,end_timekey,comment,starttime,endtime,(endtime-starttime).seconds,task_path])
db.commit()
cr.close()
db.close()