工作流 定时同步

业务上的爬虫的文件的数据

graph LR
  数据源-->定时同步;
  定时同步-->Hive数仓;

定时同步工具 DataX
https://github.com/alibaba/DataX

初始化个人开发环境
创建开发目录 - 遵循规范每个人的代码要在自己的目录下去开发

# 创建个人目录
mkdir -p /zhiyun/lijinquan
cd /zhiyun/lijinquan
# 创建5个功能目录
# data - 存放数据文件
# jobs - 存放datax的配置文件
# sql - sql脚本
# shell - shell脚本
# python - python脚本
mkdir data jobs sql shell python

存储的规范和要求

按要求每个月存储一份历史数据, 至少保留24个月的版本数据

graph LR
   源表-->|DataX|Hive-ODS;
   Hive-ODS-->2025-05;
   Hive-ODS-->2025-06;
   Hive-ODS-->2025-07-当月就覆盖;
   Hive-ODS-->2025-08;

配置文件的编写

{
    "job": {
        "setting": {
            "speed": {
                 "channel": 3
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.02
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": "jd",
                        "password": "123456",
                        "column": [
                            "*"
                        ],
                        "connection": [
                            {
                                "table": [
                                    "yyj_nmpa"
                                ],
                                "jdbcUrl": [
                                    "jdbc:mysql://192.168.8.8:3306/jd"
                                ]
                            }
                        ]
                    }
                },
                "writer": {
                    "name": "hdfswriter",
                    "parameter": {
                        "defaultFS": "hdfs://hdp:8020",
                        "fileType": "orc",
                        "path": "/zhiyun/yaoyinjin/tmp/nmpa",
                        "fileName": "nmpa.data",
                        "column": [
                            {"name": "id", "type": "int"},
                            {"name": "link", "type": "string"},
                            {"name": "title", "type": "string"},
                            {"name": "index_id", "type": "string"},
                            {"name": "categories", "type": "string"},
                            {"name": "date_", "type": "string"},
                            {"name": "article", "type": "string"}
                        ],
                        "writeMode": "truncate",
                        "fieldDelimiter": "\t"
                    }
                }
            }
        ]
    }
}

抽取数据

注意抽取前需要提前创建HDFS目录 http://192.168.8.67:9870/

1	hadoop fs -mkdir -p /zhiyun/yaoyinjin/tmp/nmpa

Hive建表

-- 建立ODS数据库
create database if not exists ods_yaoyinjin location "/zhiyun/yaoyinjin/ods";
-- 建立NMPA分区表
-- ODS的表应该是外部表
-- 内部表删除表的时候删除数据, 外部表删除表不删除数据
create external table if not exists ods_yaoyinjin.nmpa(
id int,
link string,
title string,
index_id string,
categories string,
data_ string,
article string
) partitioned by (dt string)
row format delimited fields terminated by "\t"
lines terminated by "\n"
stored as orc
location "/zhiyun/yaoyinjin/ods/nmpa";