赞
踩
目录
ETL(Extratction,Transformation and Loading)是构建数据仓库的基础技术,也是批量数据交换的基础技术,是将数据从源抽取、转换、整合、清洗并加载到目标的过程。
1、Excel
2、文本编辑器
- with tmp as (
- SELECT
- A.CONSTRAINT_NAME tmp1,
- A.table_name tmp2,
- A.COLUMN_NAME tmp3
- FROM
- all_cons_columns A,
- all_constraints b
- WHERE
- A.CONSTRAINT_NAME = b.CONSTRAINT_NAME
- AND b.constraint_type = 'P'
- -- AND A.OWNER = 'XRAPUSER'
- -- AND A.TABLE_NAME = 'AGZX_HLWPJ'
- )SELECT
- '62.64.13.44' AS IP
- ,'xxx' AS SYS
- ,A.OWNER
- ,A.TABLE_NAME AS TABLE_NAME_EN --英文表名
- ,B.COMMENTS AS TABLE_NAME_CN --中文表名
- ,A.COLUMN_ID --字段序号
- ,A.COLUMN_NAME AS COLUMN_NAME_EN --字段名称
- ,C.COMMENTS AS COLUMN_NAME_CN --字段注释
- ,A.DATA_TYPE --字段类型
- ,A.CHAR_LENGTH --字段长度
- ,A.DATA_LENGTH --数据长度
- ,A.DATA_PRECISION --数据精度
- ,A.DATA_SCALE --小数位
- ,case when tmp.tmp1 is not null then 'Y' else null end as primary_key
- FROM ALL_TAB_COLUMNS A --表与字段信息
-
- LEFT JOIN ALL_TAB_COMMENTS B --表名信息
- ON B.OWNER = A.OWNER
- AND B.TABLE_NAME = A.TABLE_NAME
-
- LEFT JOIN ALL_COL_COMMENTS C --字段名信息
- ON C.OWNER = A.OWNER
- AND C.TABLE_NAME = A.TABLE_NAME
- AND C.COLUMN_NAME = A.COLUMN_NAME
-
- left join tmp
- on tmp.tmp2 = A.TABLE_NAME
- and tmp.tmp3 = A.COLUMN_NAME
-
- WHERE A.OWNER = 'XRAPUSER'
- ORDER BY A.OWNER,A.TABLE_NAME,A.COLUMN_ID
- SELECT
- "62.64.39.135" AS IP
- ,"sfbz" AS SYS
- ,TABLE_SCHEMA
- ,TABLE_NAME AS TABLE_NAME_EN
- ," " AS TABLE_NAME_CN
- ,ORDINAL_POSITION
- ,COLUMN_NAME AS COLUMN_NAME_EN
- ,COLUMN_COMMENT AS COLUMN_NAME_CN
- ,DATA_TYPE
- ,CHARACTER_MAXIMUM_LENGTH
- ,CHARACTER_OCTET_LENGTH
- ,NUMERIC_PRECISION
- ,NUMERIC_SCALE
- ," " as primary_key
- ,COLUMN_TYPE
- FROM information_schema.columns
- where table_name like "result_%" and table_schema="dzzyk" order by TABLE_NAME_EN ,ORDINAL_POSITION
下列例子中的KTR流程,思路打通,其实开发还是比较顺畅,如果有需要下面流程参考的大佬可以一起交流,关于Kettle具体使用各个组件的方法,后续可以再出文章介绍。
本流程主要是使用Oracle的ORA_ROW_SCN字段来做增量的,可以参考。
当然,具体要使用这个流程的话,还有中间表需要建立:
全量请求证件号码接口,数据入库:
增量请求抓拍数据接口,数据入库:
对CS端开发的任务进行周期性、一次性调度。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。