From 522de496b116e44bcddfa3c43de91f8bae20521c Mon Sep 17 00:00:00 2001 From: Anton Yuzhaninov Date: Wed, 19 Jun 2019 17:50:24 +0100 Subject: [PATCH] [Minor] Document ClickHouse table schema --- src/plugins/lua/clickhouse.lua | 92 +++++++++++++++++----------------- 1 file changed, 46 insertions(+), 46 deletions(-) diff --git a/src/plugins/lua/clickhouse.lua b/src/plugins/lua/clickhouse.lua index 463637171..ff46d80fe 100644 --- a/src/plugins/lua/clickhouse.lua +++ b/src/plugins/lua/clickhouse.lua @@ -90,54 +90,54 @@ local settings = { local clickhouse_schema = {[[ CREATE TABLE rspamd ( - Date Date, - TS DateTime, - From String, - MimeFrom String, - IP String, - Score Float32, - NRcpt UInt8, - Size UInt32, - IsWhitelist Enum8('blacklist' = 0, 'whitelist' = 1, 'unknown' = 2) DEFAULT 'unknown', - IsBayes Enum8('ham' = 0, 'spam' = 1, 'unknown' = 2) DEFAULT 'unknown', - IsFuzzy Enum8('whitelist' = 0, 'deny' = 1, 'unknown' = 2) DEFAULT 'unknown', - IsFann Enum8('ham' = 0, 'spam' = 1, 'unknown' = 2) DEFAULT 'unknown', - IsDkim Enum8('reject' = 0, 'allow' = 1, 'unknown' = 2, 'dnsfail' = 3, 'na' = 4) DEFAULT 'unknown', - IsDmarc Enum8('reject' = 0, 'allow' = 1, 'unknown' = 2, 'softfail' = 3, 'na' = 4, 'quarantine' = 5) DEFAULT 'unknown', - IsSpf Enum8('reject' = 0, 'allow' = 1, 'neutral' = 2, 'dnsfail' = 3, 'na' = 4, 'unknown' = 5) DEFAULT 'unknown', - NUrls Int32, - Action Enum8('reject' = 0, 'rewrite subject' = 1, 'add header' = 2, 'greylist' = 3, 'no action' = 4, 'soft reject' = 5, 'custom' = 6) DEFAULT 'no action', - CustomAction LowCardinality(String), - FromUser String, - MimeUser String, - RcptUser String, - RcptDomain String, - MimeRecipients Array(String), - MessageId String, - ListId String, - Subject String, - `Attachments.FileName` Array(String), - `Attachments.ContentType` Array(String), - `Attachments.Length` Array(UInt32), - `Attachments.Digest` Array(FixedString(16)), - `Urls.Tld` Array(String), - `Urls.Url` Array(String), - Emails Array(String), - ASN String, - Country LowCardinality(FixedString(2)), + Date Date COMMENT 'Date (used for partitioning)', + TS DateTime COMMENT 'Date and time of request start (UTC)', + From String COMMENT 'Domain part of the return address (RFC5321.MailFrom)', + MimeFrom String COMMENT 'Domain part of the address in From: header (RFC5322.From)', + IP String COMMENT 'SMTP client IP as provided by MTA or from Received: header', + Score Float32 COMMENT 'Message score', + NRcpt UInt8 COMMENT 'Number of envelope recipients (RFC5321.RcptTo)', + Size UInt32 COMMENT 'Message size in bytes', + IsWhitelist Enum8('blacklist' = 0, 'whitelist' = 1, 'unknown' = 2) DEFAULT 'unknown' COMMENT 'Based on symbols configured in `whitelist_symbols` module option', + IsBayes Enum8('ham' = 0, 'spam' = 1, 'unknown' = 2) DEFAULT 'unknown' COMMENT 'Based on symbols configured in `bayes_spam_symbols` and `bayes_ham_symbols` module options', + IsFuzzy Enum8('whitelist' = 0, 'deny' = 1, 'unknown' = 2) DEFAULT 'unknown' COMMENT 'Based on symbols configured in `fuzzy_symbols` module option', + IsFann Enum8('ham' = 0, 'spam' = 1, 'unknown' = 2) DEFAULT 'unknown' COMMENT 'Based on symbols configured in `ann_symbols_spam` and `ann_symbols_ham` module options', + IsDkim Enum8('reject' = 0, 'allow' = 1, 'unknown' = 2, 'dnsfail' = 3, 'na' = 4) DEFAULT 'unknown' COMMENT 'Based on symbols configured in dkim_* module options', + IsDmarc Enum8('reject' = 0, 'allow' = 1, 'unknown' = 2, 'softfail' = 3, 'na' = 4, 'quarantine' = 5) DEFAULT 'unknown' COMMENT 'Based on symbols configured in dmarc_* module options', + IsSpf Enum8('reject' = 0, 'allow' = 1, 'neutral' = 2, 'dnsfail' = 3, 'na' = 4, 'unknown' = 5) DEFAULT 'unknown' COMMENT 'Based on symbols configured in spf_* module options', + NUrls Int32 COMMENT 'Number of URLs and email extracted from the message', + Action Enum8('reject' = 0, 'rewrite subject' = 1, 'add header' = 2, 'greylist' = 3, 'no action' = 4, 'soft reject' = 5, 'custom' = 6) DEFAULT 'no action' COMMENT 'Action returned for the message; if action is not predefined actual action will be in `CustomAction` field', + CustomAction LowCardinality(String) COMMENT 'Action string for custom action', + FromUser String COMMENT 'Local part of the return address (RFC5321.MailFrom)', + MimeUser String COMMENT 'Local part of address in From: header (RFC5322.From)', + RcptUser String COMMENT 'Local part of the first envelope recipient (RFC5321.RcptTo)', + RcptDomain String COMMENT 'Domain part of the first envelope recipient (RFC5321.RcptTo)', + MimeRecipients Array(String) COMMENT 'List of recipients from headers (RFC5322.To/.CC/.BCC)', + MessageId String COMMENT 'Message-ID header', + ListId String COMMENT 'List-Id header', + Subject String COMMENT 'Subject header (or hash if `subject_privacy` module option enabled)', + `Attachments.FileName` Array(String) COMMENT 'Attachment name', + `Attachments.ContentType` Array(String) COMMENT 'Attachment Content-Type', + `Attachments.Length` Array(UInt32) COMMENT 'Attachment size in bytes', + `Attachments.Digest` Array(FixedString(16)) COMMENT 'First 16 characters of hash returned by mime_part:get_digest()', + `Urls.Tld` Array(String) COMMENT 'Effective second level domain part of the URL host', + `Urls.Url` Array(String) COMMENT 'Full URL if `full_urls` module option enabled, host part of URL otherwise', + Emails Array(String) COMMENT 'List of emails extracted from the message', + ASN String COMMENT 'BGP AS number for SMTP client IP (returned by asn.rspamd.com or asn6.rspamd.com)', + Country LowCardinality(FixedString(2)) COMMENT 'Country for SMTP client IP (returned by asn.rspamd.com or asn6.rspamd.com)', IPNet String, - `Symbols.Names` Array(LowCardinality(String)), - `Symbols.Scores` Array(Float32), - `Symbols.Options` Array(String), - ScanTimeReal UInt32, + `Symbols.Names` Array(LowCardinality(String)) COMMENT 'Symbol name', + `Symbols.Scores` Array(Float32) COMMENT 'Symbol score', + `Symbols.Options` Array(String) COMMENT 'Symbol options (comma separated list)', + ScanTimeReal UInt32 COMMENT 'Request time in milliseconds', ScanTimeVirtual UInt32, - AuthUser String, - SettingsId LowCardinality(String), - Digest FixedString(32), - SMTPFrom ALIAS if(From = '', '', concat(FromUser, '@', From)), - SMTPRcpt ALIAS if(RcptDomain = '', '', concat(RcptUser, '@', RcptDomain)), - MIMEFrom ALIAS if(MimeFrom = '', '', concat(MimeUser, '@', MimeFrom)), - MIMERcpt ALIAS MimeRecipients[1] + AuthUser String COMMENT 'Username for authenticated SMTP client', + SettingsId LowCardinality(String) COMMENT 'ID for settings profile', + Digest FixedString(32) COMMENT 'Deprecated, no longer stored', + SMTPFrom ALIAS if(From = '', '', concat(FromUser, '@', From)) COMMENT 'Return address (RFC5321.MailFrom)', + SMTPRcpt ALIAS if(RcptDomain = '', '', concat(RcptUser, '@', RcptDomain)) COMMENT 'First recipient (RFC5321.RcptTo)', + MIMEFrom ALIAS if(MimeFrom = '', '', concat(MimeUser, '@', MimeFrom)) COMMENT 'Address in From: header (RFC5322.From)', + MIMERcpt ALIAS MimeRecipients[1] COMMENT 'First recipients from headers (RFC5322.To/.CC/.BCC)' ) ENGINE = MergeTree() PARTITION BY toMonday(Date) ORDER BY TS -- 2.39.5