MSSQLServer 数据采集
简介
采集 MSSQLServer 数据上报到 DataFlux 中
前置条件
- 已安装 MSSQLServer 2017 及以上版本。
配置
创建ServiceBroker实体
采用Service Broker
的方式实现异步操作,需要创建Service Broker
相关实体,包括消息、队列、合约和服务。
'/demo/message' 是官方推荐的 URL 式命名,也可以使用其他替代。
CREATE MESSAGE TYPE [/demo/message]
VALIDATION = NONE
GO
CREATE CONTRACT [/demo/message_contract]
(
[/demo/message] SENT BY INITIATOR,
)
GO
CREATE QUEUE [message_queue]
GO
CREATE SERVICE [/demo/message_service] ON QUEUE [message_queue]
(
[/demo/message_contract]
)
GO
创建测试数据表
创建数据表用以绑定触发器和测试连通性。
CREATE TABLE serverinfo (
hostname VARCHAR(128),
ip VARCHAR(16),
cpucore INT );
INSERT INTO serverinfo (hostname, ip, cpucore) VALUES('ubuntu-db', '192.168.0.11', 4);
启动OLA
在 MSSQLServer 中发送 HTTP 请求,需要开启 OLA(Ole Automation Procedures)自动化过程。开启方式如下:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
发送 HTTP 请求函数
建立 HTTP 连接时,HOST 和 Header 要根据 dataway 文档进行填写。
sp_OAMethod
的参数为 DataWay 的接收 URL,需要根据 DataWay 文档来进行配置
根据 DataWay 的文档,为 HTTP 添加对应的 Header;
CREATE FUNCTION send_http (@content VARCHAR(4096))
returns VARCHAR(1024)
AS
BEGIN
DECLARE @obj INT,
@hr INT,
@msg VARCHAR(1024);
EXEC @hr = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0', @obj OUT;
EXEC @hr = sp_OAMethod @obj, 'open', NULL, 'POST', 'http://DataWayIP:PORT/v1/write/metrics?template=&token=&shortrp=&precision=ms', 'false';
EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'User-Agent', 'test-agent';
EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'X-Trace-Id', 'test-traceid';
EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'X-Datakit-UUID', 'test-UUID';
EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL,'X-Version', 'test-version';
EXEC @hr = sp_OAMethod @obj, 'send', NULL, @content;
EXEC @hr = sp_OAGetProperty @obj, 'responseText', @msg OUT;
EXEC @hr = sp_OADestroy @obj;
return @msg;
END
创建触发器
触发器规则使用insert
,并绑定到第二步创建的数据表'serverinfo'。
行协议是 DataWay 接收数据的通用格式,包括Measurement
、Tag
、Field
和Timestamp
四个部分,格式如下:
weather,location=us-midwest temperature=82 1465839830100400200
| -------------------- -------------- |
| | | |
| | | |
+-----------+--------+-+---------+-+---------+
|measurement|,tag_set| |field_set| |timestamp|
+-----------+--------+-+---------+-+---------+
例如:serverinfo,hostname=macbook-pro ip="127.0.0.1",cpucore=8i 1582874030123
行协议文档,需要注意:
英文状态下逗号、双引号、等于号和空格都需要转义,在前面加转义字符
\
Field 的值如果是整型,需要在后面加个英文字符
i
,以示跟浮点型的区别,比如上面的cpucore
触发器会将数据拼装成行协议格式的字符串,发送到Service Broker
的message_queue
中。
CREATE TRIGGER trig ON serverinfo AFTER INSERT
AS
DECLARE @handle UNIQUEIDENTIFIER,
@content VARCHAR(4096),
@hostname VARCHAR(128),
@ip VARCHAR(16),
@cpucore INT,
@timestamp BIGINT;
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE [/demo/message_service]
TO SERVICE '/demo/message_service'
ON CONTRACT [/demo/message_contract]
WITH ENCRYPTION = OFF;
SET @host = (SELECT host FROM INSERTED);
SET @ip = (SELECT ip FROM INSERTED);
SET @cpucore = (SELECT cpucore FROM INSERTED);
SET @timestamp = (SELECT DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', SYSUTCDATETIME()));
SET @content = (SELECT 'serverinfo, hostname=' + @hostname + ' ip="' + @ip +'",cpucore=' + CAST(@cpucore AS VARCHAR(10)) + 'i ' + CAST(@timestamp AS VARCHAR(13)) + '\n')
-- 如果 SEND 语句不是批处理或存储过程中的第一个语句,则必须使用分号 (;) 终止前面的语句。
;SEND ON CONVERSATION @handle
MESSAGE TYPE [/demo/message]
(@content);
GO
消费ServiceBroker队列数据
从message_queue
中消费一条数据,并将其当做参数调用send_http()
,由该函数将数据发送到 DataWay。
CREATE PROCEDURE consumer
AS
BEGIN
SET NOCOUNT ON;
DECLARE @handle UNIQUEIDENTIFIER,
@typename SYSNAME,
@body VARCHAR(4096);
BEGIN TRANSACTION;
RECEIVE TOP(1)
@handle = conversation_handle,
@typename = message_type_name,
@body = message_body
FROM message_queue;
PRINT @body
SELECT dbo.send_http(@body);
COMMIT TRANSACTION;
END
GO