让决策更智能
新一代智能数据分析平台

第三方数据源

观远产品部发表于:2021年03月18日 20:25:59更新于:2021年03月18日 20:28:46

如果数据来源于第三方数据源,则可通过配置不同settings.json来实现数据的抽取。目前已支持的第三方数据源包括淘宝、餐道、智慧图。

1. 用餐道作为数据源

当需要抽取餐道第三方数据源时,只需在settings.json配置包括连接信息、定时调度、数据表名以及表字段信息等4大块主要信息。

1.1 配置餐道连接信息

首先配置餐道的连接信息包括:

{     
    "candao": {         
        "url": "http://open6.can-dao.com/OpenAction",         
        "accessKey": "123123",         
        "secret": "123123"     
    } 
}

其中:

  • url: 数据库的连接地址

  • accesskey: 数据库的访问凭证密钥之一,与secret key组成一对

  • secret: 数据库的访问凭证密码之一

1.2 设置定时调度

参见定时调度说明

{     
    //定时调度设置     
    "schedule_cron": {         
        "daily": "0+0+4+2+*+?"     
    } 
}

1.3 定义数据表

定义需要抽取的数据表名。餐单默认抽取order_info 和 product_info 两张表,这个map表示后者两张表在后续配置文件中对应的内容

{     
    "table_name": {         
        "order_info": "orderinfo",         
        "product_info": "product"     
    } 
}

1.4 定义数据表字段信息

在上面定义数据表中的table_name后,需要定义每个数据表字段信息。columns表示要抽取哪些字段,isPrimaryKey表示该字段是否为主键,可以有多个是true,表示联合主键,参见配置主键(去重)。其他字段中,如果有type,则表示指明了该字段的类型,没指定则表示由系统自动识别。

{     
    "orderinfo": {         
        "columns": [             
            {"name": "orderId", "type":"int","isPrimaryKey": true},             
            {"name": "brandName"},             
            {"name": "storeName"},             
            {"name": "extStoreId"},             
            {"name": "storeId"},             
            {"name": "orderNo","type":"String"},             
            {"name": "sn"},             
            {"name": "extId"},             
            {"name": "thirdSn"},             
            {"name": "orderDate"},             
            {"name": "orderStatus"},             
            {"name": "fromType"},             
            {"name": "sendType"},             
            {"name": "payType"},             
            {"name": "name"},             
            {"name": "registerPhone"},             
            {"name": "phone"},             
            {"name": "productPrice","type":"float"},             
            {"name": "realTimeProductPrice","type":"float"},             
            {"name": "price","type":"float"},             
            {"name": "merchantPrice","type":"float"},             
            {"name": "deliveryFee","type":"float"},             
            {"name": "mealFee","type":"float"},             
            {"name": "merchantBearPrice","type":"float"},             
            {"name": "thirdPlatformBearPrice","type":"float"},             
            {"name": "address"},             
            {"name": "longitude"},             
            {"name": "latitude"},             
            {"name": "userNote"},             
            {"name": "invoice"},             
            {"name": "taxNo"},             
            {"name": "isStoreFirstOrder"},             
            {"name": "isBrandFirstOrder"},             
            {"name": "driverName"},             
            {"name": "orderDrivers"},             
            {"name": "driverStatus"}         
        ]     
    },     
    "product": {         
        "columns": [             
            {"name": "orderId", "type":"int","isPrimaryKey": true},             
            {"name": "productNo", "type":"int","isPrimaryKey": true},             
            {"name": "pid"},             
            {"name": "extId", "type": "String"},             
            {"name": "typeId"},             
            {"name": "name"},             
            {"name": "nameEn"},             
            {"name": "aliasName"},             
            {"name": "price", "type": "float"},             
            {"name": "totalPrice", "type": "float"},             
            {"name": "realTimePrice", "type": "float"},             
            {"name": "realTimeTotalPrice", "type": "float"},             
            {"name": "num"},             
            {"name": "editType"},             
            {"name": "tagUid"},             
            {"name": "mealFee", "type": "float"},             
            {"name": "tagName"},             
            {"name": "bagNo"},             
            {"name": "listRequirements"}         
        ]     
    } 
}

2. 用智慧图作为数据源

当需要抽取智慧图第三方数据源时,只需在settings.json配置包括连接信息、定时调度、数据表名以及表字段信息等4大块主要信息,与餐道类似。

2.1 配置智慧图连接信息

首先配置智慧图的连接信息,与餐道类似,连接信息包括:

{     
    "zhihuitu_additional": {         
        "url": "https://crm-api.rtmap.com/openapi/openApi/",         
        "action":"couponInstance/list",         
        "couponBusinessEntityId": 123,         
        "signature": "123"     
    } 
}

其中:

  • url: 数据库的连接地址

  • action: API的操作类型

  • couponBusinessEntityId: 商业实体ID

  • signature: 签名密钥

2.2 设置定时调度

参见定时调度说明

{     
    //定时调度设置     
    "schedule_cron": {         
        "daily": "0+0+0+2+*+?"     
    } 
}

2.3 定义数据表

{     
    "table_name": {         
        "member/findVipInfo": "vipInfo",         
        "member/exportVipPoints": "vipPoint",         
        "operation/findVipSell": "vipSell"       
    } 
}

2.4 定义数据表字段信息

智慧图需要提供除了2.3定义的数据表的每个数据表字段信息外,还需要提供附带表的信息,即goodsList 和 paymentList。

{     
    "vipInfo": {         
        "columns": [             
            {"name": "id", "type":"long"},             
            {"name": "uid", "type":"String","isPrimaryKey": true},             
            {"name": "vip_code", "type":"String"},             
            {"name": "grade_code", "type":"String"},             
            {"name": "vip_type"},             
            {"name": "vip_charge"},             
            {"name": "vip_channel"},             
            {"name": "vip_pict","type":"String"},             
            {"name": "vip_phone"},             
            {"name": "vip_id_number_type"},             
            {"name": "vip_id_number"},             
            {"name": "vip_name"},             
            {"name": "vip_nickname"},             
            {"name": "vip_sex"},             
            {"name": "vip_nationality"},             
            {"name": "vip_birthday"},             
            {"name": "vip_age"},             
            {"name": "vip_workspace"},             
            {"name": "vip_email"},             
            {"name": "vip_contact_wechat"},             
            {"name": "vip_contact_phone"},             
            {"name": "vip_contact_message"},             
            {"name": "vip_contact_email"},             
            {"name": "vip_contact_letter"},             
            {"name": "vip_car_no"},             
            {"name": "vip_contact_address"},             
            {"name": "vip_province"},             
            {"name": "vip_city"},             
            {"name": "vip_area"},             
            {"name": "vip_street"},             
            {"name": "vip_memo"},             
            {"name": "vip_status"},             
            {"name": "store_code"},             
            {"name": "vip_manager"},             
            {"name": "channel_code"},             
            {"name": "vip_first_consume_time"},             
            {"name": "vip_last_consume_time"},             
            {"name": "vip_registration_time"},             
            {"name": "vip_year_date"},             
            {"name": "vip_effective_date"},             
            {"name": "vip_constellation"},             
            {"name": "vip_profession"},             
            {"name": "vip_income"},             
            {"name": "vip_duty"},             
            {"name": "vip_culture"},             
            {"name": "vip_blood_type"},             
            {"name": "sum_qty"},             
            {"name": "year_qty"},             
            {"name": "sum_amount"},             
            {"name": "year_amount"},             
            {"name": "sum_member_points"},             
            {"name": "current_member_points"},             
            {"name": "sum_grow_value"},             
            {"name": "year_grow_value"}         
        ]     
    },     
    "vipPoint": {         
        "columns": [             
            {"name": "id", "type":"long","isPrimaryKey": true},             
            {"name": "change_member_points"},             
            {"name": "trans_type"},             
            {"name": "change_explain"},             
            {"name": "create_user"},             
            {"name": "occur_time"},             
            {"name": "gmt_create","isPrimaryKey": true},             
            {"name": "change_cause"},             
            {"name": "change_cause_code"},             
            {"name": "vip_phone"},             
            {"name": "vip_code"},             
            {"name": "vip_id_number_type"},             
            {"name": "vip_id_number"},             
            {"name": "vip_channel"},             
            {"name": "channel_code"}         
        ]     
    },     
    "vipSell": {         
        "columns": [             
            {"name": "id", "type":"long","isPrimaryKey": true},             
            {"name": "occur_time","type":"timestamp"},             
            {"name": "company_code"},             
            {"name": "store_code"},             
            {"name": "shop_code"},             
            {"name": "grade_code"},             
            {"name": "channel_code"},             
            {"name": "vip_uid"},             
            {"name": "vip_name"},             
            {"name": "vip_phone"},             
            {"name": "trans_type"},             
            {"name": "billno", "type":"String","isPrimaryKey": true},             
            {"name": "pos_cashier"},             
            {"name": "pos_billno"},             
            {"name": "original_bllno"},             
            {"name": "amount"},             
            {"name": "discount"},             
            {"name": "reality_amount"},             
            {"name": "pay_amount"},             
            {"name": "points_amount"},             
            {"name": "member_points"},             
            {"name": "refund_member_points"},             
            {"name": "src_type"},             
            {"name": "create_user"},             
            {"name": "gmt_create"}         
        ]     
    },     
    "goodsList": {         
        "columns": [             
            {"name": "id", "type":"long","isPrimaryKey": true},             
            {"name": "billno", "type":"String","isPrimaryKey": true},             
            {"name": "goods_code"},             
            {"name": "goods_name"},             
            {"name": "qty"},             
            {"name": "sale_price"},             
            {"name": "amount"},             
            {"name": "discount"},             
            {"name": "amount_percent"}         
        ]     
    },     
    "paymentList": {         
        "columns": [             
            {"name": "id", "type":"long","isPrimaryKey": true},             
            {"name": "billno", "type":"String","isPrimaryKey": true},             
            {"name": "payment_code"},             
            {"name": "payment_name"},             
            {"name": "payment_account"},             
            {"name": "payment_no"},             
            {"name": "payment_amount"},             
            {"name": "is_member_points"},             
            {"name": "amount_percent"}         
        ]       
    } 
}

3. 用淘宝作为数据源

当需要抽取淘宝第三方数据源时,只需在settings.json配置包括连接信息、定时调度以及表字段信息等3大块主要信息,与餐道类似。不同点在于淘宝中的每个文件夹都是一个表,只需创建好相应的文件夹名称即可,无需定义数据表名。 

taobao-datasource.png

3.1 配置淘宝连接信息

首先配置淘宝的连接信息,与餐道类似,连接信息包括:

{     
    "taobao": {         
        "auth_url":"https://oauth.taobao.com/authorize",         
        "server_url": "https://eco.taobao.com/router/rest",         
        "redirect_url":"http://121.40.247.182:25257/taobaoisv/auth_callback",         
        "app_key": "123",         
        "app_secret": "123"     
    } 
}

其中:

  • auth_url: 数据库的认证地址

  • server_url: 数据库的服务器地址

  • redirect_url: 数据库的重定向地址

  • app_key: 数据库的访问凭证密钥之一,与secret key组成一对

  • app_secret: 数据库的访问凭证密钥之一

3.2 设置定时调度

参见定时调度说明。此外淘宝数据源支持针对每个文件夹(即数据表)进行定时,只需在使用“文件夹名称:定时调度配置”即可。例如:下面所示的tradesSold,就表示tradesSold文件夹的配置内容每天01:01进行更新。

{     
    "schedule_cron": {         
        "daily": "1+1+1+1+*+?",         
        "tradesSold": "1+1+0+*+*+?",         
        "tb_order_api_realtime":"0+30+03+?+*+MON"     
    } 
}

此外,淘宝数据表还支持实时抽取,例如tb_order_api_realtime表,只需在该表文件中的setting.json中设置"backwardMinutes"即可,该参数表示抽取的数据需要往前倒回多少分钟,例如

{     
    "backwardMinutes":10 //10表示往前到回10分钟 
}

3.3 定义数据表字段信息

淘宝每个表都需要在该表文件夹中声明settings.json,并在settings.json中声明该表的action和表字段信息。例如traderates表:

{     
    "action":"taobao.traderates.get",     
    "table":{         
        "columns": [             
            {"name": "tid", "type":"String","isPrimaryKey": true},             
            {"name": "oid", "type":"String","isPrimaryKey": true},             
            {"name": "role", "type":"String","isPrimaryKey": true},             
            {"name": "nick", "type":"String"},             
            {"name": "result", "type":"String"},             
            {"name": "created", "type":"timestamp"},             
            {"name": "rated_nick", "type":"String"},             
            {"name": "item_title", "type":"String"},             
            {"name": "item_price", "type":"double"},             
            {"name": "content", "type":"String"},             
            {"name": "reply", "type":"String"},             
            {"name": "num_iid", "type":"String"},             
            {"name": "valid_score", "type":"String"}         
        ]     
    } 
}

    您需要登录后才可以回复