CREATE DATABASE IF NOT EXISTS wcs;
DROP DICTIONARY IF EXISTS wcs.DictionaryStreamEvents;
DROP DICTIONARY IF EXISTS wcs.DictionaryConnectionEvents;
DROP DICTIONARY IF EXISTS wcs.DictionaryCDNEvents;
DROP DICTIONARY IF EXISTS wcs.DictionaryHlsStreamEventType;
DROP DICTIONARY IF EXISTS wcs.DictionaryHlsStreamEventSeverity;
DROP DICTIONARY IF EXISTS wcs.DictionaryMixerEvents;
DROP DICTIONARY IF EXISTS wcs.DictionaryBufferStateTypes;
DROP TABLE IF EXISTS wcs.StreamEvent;
DROP TABLE IF EXISTS wcs.ConnectionEvent;
DROP TABLE IF EXISTS wcs.CDNEvent;
DROP TABLE IF EXISTS wcs.StreamEventTypes;
DROP TABLE IF EXISTS wcs.ConnectionEventTypes;
DROP TABLE IF EXISTS wcs.CDNEventTypes;
DROP TABLE IF EXISTS wcs.MediaSessionEvents;
DROP TABLE IF EXISTS wcs.HlsStreamEvents;
DROP TABLE IF EXISTS wcs.HlsSegmenterEvents;
DROP TABLE IF EXISTS wcs.HlsStreamEventSeverity;
DROP TABLE IF EXISTS wcs.HlsStreamEventType;
DROP TABLE IF EXISTS wcs.HlsClientEvents;
DROP TABLE IF EXISTS wcs.MixerEvent;
DROP TABLE IF EXISTS wcs.MixerEventTypes;
DROP TABLE IF EXISTS wcs.RtmpInBufferEvent;
DROP TABLE IF EXISTS wcs.AudioRecoveryEvent;
DROP TABLE IF EXISTS wcs.BufferStateTypes;
CREATE TABLE wcs.ConnectionEventTypes
(
`id` UInt32,
`type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
INSERT INTO wcs.ConnectionEventTypes VALUES (0, 'CONNECTED'), (1, 'DISCONNECTED');
CREATE TABLE wcs.StreamEventTypes
(
`id` UInt32,
`type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
INSERT INTO wcs.StreamEventTypes VALUES (0,'CREATED'),(1,'LOCAL_SDP_CREATED'),(2,'REMOTE_SDP_RECEIVED'),(3,'ICE_STARTED'),(4,'ICE_COMPLETE'),(5,'DTLS_STARTED'),(6,'DTLS_COMPLETE'),(7,'INITIALIZED'),(8,'DISPOSING'),(9,'DISPOSED'),(10,'AUDIO_RECEIVED'),(11,'VIDEO_RECEIVED'),(12,'VIDEO_KFRAME_RECEIVED'),(13,'AUDIO_RTCP_RECEIVED'),(14,'VIDEO_RTCP_RECEIVED'),(15,'RESOLUTION_RECEIVED'),(16,'VIDEO_ENCODER_CREATED'),(17,'AUDIO_ENCODER_CREATED'),(18,'VIDEO_ENCODER_DISPOSED'),(19,'AUDIO_ENCODER_DISPOSED'),(20,'TERMINATED'),(21,'AUDIO_SENT'),(22,'VIDEO_SENT'),(23,'VIDEO_JITTER_BUFFER_STALL'),(24,'SENT_PLI'),(25,'RECEIVED_PLI'),(26,'SYNC_BUFFER_FULL'),(27,'SYNC_FORCE_FAILED'),(28,'SYNC_SHIFT'),(29,'SYNC_DEVIATION'),(30,'VIDEO_STATS'),(31,'RECORD');
CREATE TABLE wcs.CDNEventTypes
(
`id` UInt32,
`type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
INSERT INTO wcs.CDNEventTypes VALUES (0, 'STATE'), (1, 'CDN_STATE'), (2, 'VERSION'), (3, 'ACL_REFRESH'), (4, 'ACL_UPDATE');
CREATE DICTIONARY wcs.DictionaryStreamEvents (
`id` UInt16,
`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'StreamEventTypes'
))
LAYOUT(FLAT())
LIFETIME(300);
CREATE DICTIONARY wcs.DictionaryConnectionEvents (
`id` UInt16,
`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'ConnectionEventTypes'
))
LAYOUT(FLAT())
LIFETIME(300);
CREATE DICTIONARY wcs.DictionaryCDNEvents (
`id` UInt16,
`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'CDNEventTypes'
))
LAYOUT(FLAT())
LIFETIME(300);
CREATE TABLE wcs.StreamEvent
(
`timestamp` UInt64,
`ip` IPv4,
`sessionId` String,
`mediaSessionId` String,
`streamName` String,
`eventType` UInt64,
`eventPayload` String
)
ENGINE = MergeTree()
ORDER BY (sessionId, mediaSessionId, streamName)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.ConnectionEvent
(
`timestamp` UInt64,
`ip` IPv4,
`sessionId` String,
`eventType` UInt64,
`eventPayload` String
)
ENGINE = MergeTree()
ORDER BY (timestamp, sessionId)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.CDNEvent
(
`timestamp` UInt64,
`ip` IPv4,
`nodeId` String,
`eventType` UInt64,
`eventPayload` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
INSERT INTO wcs.CDNEventTypes VALUES (0, 'STATE'), (1, 'CDN_STATE'), (2, 'VERSION'), (3, 'ACL_REFRESH'), (4, 'ACL_UPDATE');
CREATE DICTIONARY wcs.DictionaryStreamEvents ( (nodeId, eventType)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.MediaSessionEvents
(
`timestamp` UInt64,
`id``ip` UInt16IPv4,
`type``mediaSessionId` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'StreamEventTypes'
))
LAYOUT(FLAT())
LIFETIME(300);
CREATE DICTIONARY wcs.DictionaryConnectionEvents (
`id` UInt16,
`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'ConnectionEventTypes'
))
LAYOUT(FLAT())
LIFETIME(300);
CREATE DICTIONARY wcs.DictionaryCDNEvents (
`id` UInt16,
`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'CDNEventTypes'
))
LAYOUT(FLAT())
LIFETIME(300);
,
`streamName` String,
`videoProfileId` UInt32,
`videoWidth` UInt32,
`videoHeight` UInt32,
`videoFrameRate` UInt32,
`videoKframes` UInt64,
`videoPframes` UInt64,
`videoBframes` UInt64,
`videoRate` UInt64,
`audioRate` UInt64,
`videoSyncTime` UInt64,
`audioSyncTime` UInt64,
`videoTimestamp` UInt64,
`audioTimestamp` UInt64,
`lastKeyFrameSyncTime` UInt64,
`sendNACK` UInt64,
`recvNACK` UInt64,
`videoFramesLost` UInt64,
`audioPacketsLost` UInt64,
`audioPlaybackSpeed` Float32,
`videoPlaybackSpeed` Float32
)
ENGINE = MergeTree()
ORDER BY (mediaSessionId, streamName)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.StreamEventHlsSegmenterEvents
(
`timestamp` UInt64,
`ip` IPv4,
`sessionId``streamId` String,
`mediaSessionId``variantName` String,
`streamName``segmentId` String,
`eventType``segmentStartPts` UInt64,
`eventPayload` String
)
ENGINE = MergeTree()
ORDER BY (sessionId, mediaSessionId, streamName)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.ConnectionEvent
(
`videoStartPts` UInt64,
`timestamp``audioStartPts` UInt64,
`ip``videoWidth` IPv4UInt32,
`sessionId``videoHeight` StringUInt32,
`eventType``videoFrameCount` UInt64UInt32,
`eventPayload``audioPacketCount` String
)
ENGINE = MergeTree()
ORDER BY (timestamp, sessionId)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.CDNEvent
(
UInt32,
`segmentDuration` UInt64,
`independent` Bool,
`timestamp``gap` UInt64Bool,
`ip``discontinuity` IPv4Bool,
`nodeId``segmentInterval` StringUInt64,
`eventType``partial` UInt64Bool,
`eventPayload``playbackSpeed` StringFloat32
)
ENGINE = MergeTree()
ORDER BY (nodeId, eventTypestreamId)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.MediaSessionEvents
(
`timestamp` UInt64,
`ip` IPv4,
`mediaSessionId` String,
`streamName` String,
`videoProfileId` UInt32,
`videoWidth` UInt32,HlsStreamEventSeverity
(
`videoHeight``id` UInt32UInt8,
`videoFrameRate` UInt32,
`videoKframes` UInt64,
`videoPframes` UInt64,
`videoBframes` UInt64,
`videoRate` UInt64,
`audioRate` UInt64,
`videoSyncTime` UInt64,`type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
INSERT INTO wcs.HlsStreamEventSeverity VALUES (0, 'INFO'), (1, 'WARNING'), (2, 'ERROR');
CREATE TABLE wcs.HlsStreamEventType
(
`audioSyncTime``id` UInt64UInt16,
`videoTimestamp` UInt64,
`audioTimestamp` UInt64,
`lastKeyFrameSyncTime` UInt64,
`sendNACK` UInt64,
`recvNACK` UInt64,
`videoFramesLost` UInt64,
`audioPacketsLost` UInt64
)
ENGINE = MergeTree()
ORDER BY (mediaSessionId, streamName)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.HlsSegmenterEvents
(
`timestamp` UInt64,
`ip` IPv4,
`streamId` String,
`variantName` String,
`segmentId` String,
`segmentStartPts` UInt64,
`videoStartPts` UInt64,
`audioStartPts` UInt64,
`videoWidth` UInt32,
`videoHeight` UInt32,
`videoFrameCount` UInt32,
`audioPacketCount` UInt32,
`segmentDuration` UInt64,
`independent` Bool,
`gap` Bool,
`discontinuity` Bool,
`segmentInterval` UInt64,
`partial` Bool,
`playbackSpeed` Float32`type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
INSERT INTO wcs.HlsStreamEventType
VALUES (0, 'PLAYBACK_SPEED'), (1, 'FPS_CHANGED'), (2, 'GAP'), (3, 'RESOLUTION_CHANGED'), (4, 'DISCONTINUITY'), (5, 'TASK_SKIPPED'), (6, 'NO_KYE_FRAME'), (7, 'NO_VIDEO'), (8, 'NO_AUDIO'), (9, 'SEGMENT_INTERVAL'), (10, 'OTHER');
CREATE DICTIONARY wcs.DictionaryHlsStreamEventSeverity
(
`id` UInt8,
`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'HlsStreamEventSeverity'
))
LAYOUT(FLAT())
LIFETIME(300);
CREATE DICTIONARY wcs.DictionaryHlsStreamEventType
(
`id` UInt16,
`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'HlsStreamEventType'
))
LAYOUT(FLAT())
LIFETIME(300);
CREATE TABLE wcs.HlsStreamEvents
(
`timestamp` UInt64,
`ip` IPv4,
`severity` UInt8,
`messageType` UInt16,
`streamId` String,
`variantName` String,
`segmentId` String,
`message` String
)
ENGINE = MergeTree()
ORDER BY (streamId)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.HlsStreamEventSeverityHlsClientEvents
(
`creationTime` UInt64,
`responseTime` UInt64,
`streamId` String,
`id``variantName` UInt8String,
`uri` String,
`type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
INSERT INTO wcs.HlsStreamEventSeverity VALUES (0, 'INFO'), (1, 'WARNING'), (2, 'ERROR');
CREATE TABLE wcs.HlsStreamEventType
(
`id` UInt16`localIp` IPv4,
`remoteIp` IPv4,
`remotePort` UInt32,
`userAgent` String,
`httpStatus` UInt32,
`type``clientId` StringUInt64
)
ENGINE = MergeTree()
ORDER BY id(creationTime)
SETTINGS index_granularity = 8192;
INSERTCREATE INTOTABLE wcs.HlsStreamEventType
VALUES (0, 'PLAYBACK_SPEED'), (1, 'FPS_CHANGED'), (2, 'GAP'), (3, 'RESOLUTION_CHANGED'), (4, 'DISCONTINUITY'), (5, 'TASK_SKIPPED'), (6, 'NO_KYE_FRAME'), (7, 'NO_VIDEO'), (8, 'NO_AUDIO'), (9, 'SEGMENT_INTERVAL'), (10, 'OTHER');
CREATE DICTIONARY wcs.DictionaryHlsStreamEventSeverity
(
`id` UInt8,
`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'HlsStreamEventSeverity'
))
LAYOUT(FLAT())
LIFETIME(300);
CREATE DICTIONARY wcs.DictionaryHlsStreamEventType
(
`id` UInt16,
`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'HlsStreamEventType'
))
LAYOUT(FLAT())
LIFETIME(300)MixerEvent
(
`timestamp` UInt64,
`mixerMediaSessionId` String,
`mixerStreamName` String,
`mediaSessionId` String,
`streamName` String,
`mixerAverageTickTimeInMs` Int64,
`audioMixerSync` Int64,
`videoMixerSync` Int64,
`nextAudioDataTime` Int64,
`nextVideoDataTime` Int64,
`audioBuffered` Int64,
`videoBuffered` Int64,
`audioDropsCounter` Int64,
`audioDropsSizeInBytes` Int64,
`videoDropsCounter` Int64,
`videoDropsSizeInBytes` Int64,
`videoFps` Int64,
`audioRate` DOUBLE,
`videoRate` DOUBLE,
`eventType` UInt32,
`eventPayload` String
)
ENGINE = MergeTree()
ORDER BY timestamp
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.HlsStreamEventsMixerEventTypes
(
`timestamp``id` UInt64UInt32,
`ip` IPv4,
`severity` UInt8,
`messageType` UInt16,
`streamId` String,
`variantName` String,
`segmentId` String,
`message` String
)
ENGINE = MergeTree()
ORDER BY (streamId)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.HlsClientEvents
`type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
INSERT INTO wcs.MixerEventTypes VALUES (0, 'nullEvent'), (1, 'dropBallastAudio'), (2, 'dropBallastVideo'), (3, 'audioNotBuffered'), (4, 'videoNotBuffered'), (5, 'audioBufferExhausted'), (6, 'videoBufferExhausted'), (7, 'alignStreamFailed'), (8, 'alignStreamDropAudio'), (9, 'alignStreamDropVideo'), (10, 'rateOutOfBoundsAudio'), (11, 'rateOutOfBoundsVideo');
CREATE DICTIONARY wcs.DictionaryMixerEvents (
`creationTime``id` UInt64UInt16,
`responseTime` UInt64,
`streamId` String,
`variantName` String,
`uri` String,
`localIp` IPv4,`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'MixerEventTypes'
))
LAYOUT(FLAT())
LIFETIME(300);
CREATE TABLE wcs.RtmpInBufferEvent
(
`remoteIp``timestamp` IPv4UInt64,
`remotePort``streamClockTime` UInt32UInt64,
`userAgent``mediaSessionId` String,
`httpStatus``streamName` UInt32String,
`clientId` UInt64
)
ENGINE = MergeTree()
ORDER BY (creationTime)
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.MixerEvent
(
`timestamp` UInt64`nextAudioDataTime` Int64,
`nextVideoDataTime` Int64,
`audioBuffered` Int64,
`mixerMediaSessionId``videoBuffered` StringInt64,
`mixerStreamName``maximumAllowedBuffer` StringInt64,
`mediaSessionId``bufferingCounter` StringInt64,
`streamName``lastAudioDataTime` StringInt64,
`mixerAverageTickTimeInMs``lastVideoDataTime` Int64,
`audioMixerSync` Int64,
`videoMixerSync` Int64,
`nextAudioDataTime` Int64,`bufferState` UInt32
)
ENGINE = MergeTree()
ORDER BY timestamp
SETTINGS index_granularity = 8192;
CREATE TABLE wcs.BufferStateTypes
(
`nextVideoDataTime``id` Int64UInt32,
`audioBuffered` Int64,
`videoBuffered` Int64,
`audioDropsCounter` Int64,
`audioDropsSizeInBytes` Int64,
`videoDropsCounter` Int64,`type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
INSERT INTO wcs.BufferStateTypes VALUES (0, 'BUFFERING'), (1, 'HOLD'), (2, 'TERMINATED'), (3, 'OVERFLOW'), (4, 'PASSTHROUGH');
CREATE DICTIONARY wcs.DictionaryBufferStateTypes (
`videoDropsSizeInBytes``id` Int64UInt16,
`videoFps``type` Int64,
String DEFAULT ''
)
PRIMARY `audioRate` DOUBLE,KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port `videoRate` DOUBLE,9000
user 'default'
`eventType` UInt32,password ''
db 'wcs'
`eventPayload` String
)
ENGINE = MergeTree()
ORDER BY timestamp
SETTINGS index_granularity = 8192table 'BufferStateTypes'
))
LAYOUT(FLAT())
LIFETIME(300);
CREATE TABLE wcs.MixerEventTypesAudioRecoveryEvent
(
`id``timestamp` UInt32UInt64,
`type``mediaSessionId` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
INSERT INTO wcs.MixerEventTypes VALUES (0, 'nullEvent'), (1, 'dropBallastAudio'), (2, 'dropBallastVideo'), (3, 'audioNotBuffered'), (4, 'videoNotBuffered'), (5, 'audioBufferExhausted'), (6, 'videoBufferExhausted'), (7, 'alignStreamFailed'), (8, 'alignStreamDropAudio'), (9, 'alignStreamDropVideo'), (10, 'rateOutOfBoundsAudio'), (11, 'rateOutOfBoundsVideo');
CREATE DICTIONARY wcs.DictionaryMixerEvents (
`id` UInt16,
`type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'wcs'
table 'MixerEventTypes'
))
LAYOUT(FLAT())
LIFETIME(300); |