阿里云（kafka-＞flink-＞rds mysql-＞datav）

1. 背景

使用阿里云做了一个demo，从数据的生产到数据的展现，一条数据的链路

kafka->flink->rds mysql->datav

2. 数据的产生

首先使用python脚本生成一个实时的数据流存放到阿里云的kafka中

# -*- coding: utf-8 -*-
"""
Created on Sun Jun 27 20:11:50 2021

@author: 44373
"""
import msvcrt
import random
import configparser , time , hmac , hashlib , logging , os , sys , random
from linkkit import linkkit
from datetime import datetime

LOG_FORMAT = "%(thread)d %(asctime)s  %(levelname)s %(filename)s %(lineno)d %(message)s"
DATE_FORMAT = "%m/%d/%Y-%H:%M:%S-%p"
logging.basicConfig(format=LOG_FORMAT , datefmt=DATE_FORMAT)

lk = linkkit.LinkKit(
    host_name="",
    product_key="",
    device_name="",
    device_secret="")

lk.thing_setup('./默认模块.json')   #物模型路径

# 连接
def on_connect(session_flag , rc , userdata) :
    logging.info("on_connect:%d,rc:%d,userdata:" % (session_flag , rc))

# 断开连接
def on_disconnect(rc , userdata) :
    logging.info("on_disconnect:rc:%d,userdata:" % rc)

# Topic消息
def on_topic_message(topic , payload , qos , userdata) :
    logging.info("on_topic_message:" + topic + " payload:" + str(payload) + " qos:" + str(qos))

# 订阅
def on_subscribe_topic(mid , granted_qos , userdata) :
    logging.info("on_subscribe_topic mid:%d, granted_qos:%s" %
                 (mid , str(','.join('%s' % it for it in granted_qos))))

# 发布消息
def on_publish_topic(mid , userdata) :
    logging.info("on_publish_topic mid:%d" % mid)
    
# 上报属性
def on_thing_prop_post(request_id , code , data , message , userdata) :
    logging.info("on_thing_prop_post request id:%s, code:%d message:%s, data:%s,userdata:%s" %
                 (request_id , code , message , data , userdata))


lk.on_connect = on_connect
lk.on_disconnect = on_disconnect
lk.on_thing_enable = on_thing_enable
lk.on_subscribe_topic = on_subscribe_topic
#lk.on_unsubscribe_topic = on_unsubscribe_topic
lk.on_topic_message = on_topic_message
lk.on_publish_topic = on_publish_topic
#lk.on_thing_call_service = on_thing_call_service
#lk.on_thing_event_post = on_thing_event_post
#lk.on_thing_prop_changed = on_thing_prop_changed
lk.on_thing_prop_post = on_thing_prop_post
lk.connect_async()
lk.start_worker_loop()
time.sleep(2)  # 延时


a = 1
while True:
#    if ord(msvcrt.getch()) in [68, 100]:
#        break
    now_time_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    # now_time = datetime.strptime(now_time_str, "%Y-%m-%d %H:%M:%S")
    # prop_data = {
    #     "tmp_hum:iTs": now_time_str,
    #     "tmp_hum:temperature": 55.2+random.randint(-10,10),
    #     "tmp_hum:humidity": 66.3+random.randint(-10,10)
    #     }
    prop_data = {
        "iTs": now_time_str,
        "temperature": 55.2+random.randint(-10,10),
        "humidity": 66.3+random.randint(-10,10)
        }
    rc ,request_id = lk.thing_post_property(prop_data)
    print('run:', a)
    print(prop_data)
    a += 1
    time.sleep(30)

代码块

{
  "schema": "",
  "profile": {
    "version": "1.5",
    "productKey": "a1QtKtiXgO5"
  },
  "properties": [
    {
      "identifier": "humidity",
      "name": "湿度",
      "accessMode": "rw",
      "required": false,
      "dataType": {
        "type": "float",
        "specs": {
          "min": "0",
          "max": "100",
          "unit": "%RH",
          "unitName": "相对湿度",
          "step": "0.1"
        }
      }
    },
    {
      "identifier": "temperature",
      "name": "温度",
      "accessMode": "rw",
      "required": false,
      "dataType": {
        "type": "float",
        "specs": {
          "min": "-20",
          "max": "100",
          "unit": "°C",
          "unitName": "摄氏度",
          "step": "0.1"
        }
      }
    },
    {
      "identifier": "iTs",
      "name": "时间",
      "accessMode": "rw",
      "required": false,
      "dataType": {
        "type": "text",
        "specs": {
          "length": "255"
        }
      }
    }
  ],
  "events": [
    {
      "identifier": "post",
      "name": "post",
      "type": "info",
      "required": true,
      "desc": "属性上报",
      "method": "thing.event.property.post",
      "outputData": [
        {
          "identifier": "humidity",
          "name": "湿度",
          "dataType": {
            "type": "float",
            "specs": {
              "min": "0",
              "max": "100",
              "unit": "%RH",
              "unitName": "相对湿度",
              "step": "0.1"
            }
          }
        },
        {
          "identifier": "temperature",
          "name": "温度",
          "dataType": {
            "type": "float",
            "specs": {
              "min": "-20",
              "max": "100",
              "unit": "°C",
              "unitName": "摄氏度",
              "step": "0.1"
            }
          }
        },
        {
          "identifier": "iTs",
          "name": "时间",
          "dataType": {
            "type": "text",
            "specs": {
              "length": "255"
            }
          }
        }
      ]
    }
  ],
  "services": [
    {
      "identifier": "set",
      "name": "set",
      "required": true,
      "callType": "async",
      "desc": "属性设置",
      "method": "thing.service.property.set",
      "inputData": [
        {
          "identifier": "humidity",
          "name": "湿度",
          "dataType": {
            "type": "float",
            "specs": {
              "min": "0",
              "max": "100",
              "unit": "%RH",
              "unitName": "相对湿度",
              "step": "0.1"
            }
          }
        },
        {
          "identifier": "temperature",
          "name": "温度",
          "dataType": {
            "type": "float",
            "specs": {
              "min": "-20",
              "max": "100",
              "unit": "°C",
              "unitName": "摄氏度",
              "step": "0.1"
            }
          }
        },
        {
          "identifier": "iTs",
          "name": "时间",
          "dataType": {
            "type": "text",
            "specs": {
              "length": "255"
            }
          }
        }
      ],
      "outputData": []
    },
    {
      "identifier": "get",
      "name": "get",
      "required": true,
      "callType": "async",
      "desc": "属性获取",
      "method": "thing.service.property.get",
      "inputData": [
        "humidity",
        "temperature",
        "iTs"
      ],
      "outputData": [
        {
          "identifier": "humidity",
          "name": "湿度",
          "dataType": {
            "type": "float",
            "specs": {
              "min": "0",
              "max": "100",
              "unit": "%RH",
              "unitName": "相对湿度",
              "step": "0.1"
            }
          }
        },
        {
          "identifier": "temperature",
          "name": "温度",
          "dataType": {
            "type": "float",
            "specs": {
              "min": "-20",
              "max": "100",
              "unit": "°C",
              "unitName": "摄氏度",
              "step": "0.1"
            }
          }
        },
        {
          "identifier": "iTs",
          "name": "时间",
          "dataType": {
            "type": "text",
            "specs": {
              "length": "255"
            }
          }
        }
      ]
    }
  ],
  "functionBlocks": [
    {
      "functionBlockId": "tmp_hum",
      "functionBlockName": "device2_model",
      "gmtCreated": 1624793314627,
      "productKey": "a1QtKtiXgO5"
    }
  ]
}

3. kafka查看数据

4. 编写flink sql

--********************************************************************--
-- Author:         Write your name here
-- Created Time:   2021-06-28 17:26:42
-- Description:    Write your description here
--********************************************************************--
CREATE TEMPORARY TABLE kafka_source (
   iTs VARCHAR,
   temperature varchar,
   humidity varchar
) WITH (
  'connector' = 'kafka',
  'topic' = '',
  'properties.bootstrap.servers' = '',
  'properties.group.id' = '',
  'format'='json',
  'scan.startup.mode' = ''
) ;


CREATE TEMPORARY TABLE kafkasink (
    iTs VARCHAR,
   temperature varchar,
   humidity varchar
) WITH (
   'connector' = '',
   'password' = '',
   'tableName' = '',
   'url' = '',
   'userName' = ''
);
INSERT INTO kafkasink SELECT JSON_VALUE(iTs,'$.value') AS iTs,JSON_VALUE(temperature,'$.value') AS temperature, JSON_VALUE(humidity,'$.value') AS humidity FROM kafka_source;

在此遇到一种情况：数据入库的时候数据类型是json格式如：

{"temperature":{"time":1624871857849,"value":49.2},"humidity":{"time":1624871857849,"value":61.3},"iTs":{"time":1624871857849,"value":"2021-06-28 17:17:37"}}

查找文档之后解决：https://help.aliyun.com/document_detail/207984.html?spm=5176.21213303.J_6028563670.21.35d03edaEW9nt9&scm=20140722.S_help%40%40%E6%96%87%E6%A1%A3%40%40207984.S_hot.ID_207984-RL_json-OR_s%2Bmain-V_1-P0_2

5. 在rds中查询数据

6.数据的展示

将iTs作为横坐标，温湿度分别为纵坐标，展示两张图

动态的显示温度和湿度的模拟值

https://datav.aliyuncs.com/share/ef9c235d2373fcb3063e03212d802123