Using mat view as alert for new dns

alert.sql

---Create dns table

CREATE TABLE new_dns(domain_name String, fetch_date DateTime) PARTITION BY toYYYYMM(fetch_date) ORDER BY domain_name

---Create interesting search table

create table interesting_search (pattern String) order by pattern;


----Create mat view for alert
CREATE MATERIALIZED VIEW clickhousealert_real
ENGINE = URL('https://hooks.slack.com/services/xxxxxxx/xxxxxx/xxxxxxxxxx', 'JSONEachRow')
AS
SELECT
    if(
        length(domain_names) = 0,
        '',
        concat(
            'Domains:\n',
            arrayStringConcat(
                arrayMap((d, f) -> concat('- ', d, ' (', formatDateTime(f, '%Y-%m-%d'), ')'), domain_names, fetch_dates),
                '\n'
            )
        )
    ) AS text,
    'dns-alert' AS username,
    ':warning:' AS icon_emoji
FROM (
    SELECT
        groupArray(domain_name) AS domain_names,
        groupArray(fetch_date) AS fetch_dates
    FROM new_dns
    WHERE arrayExists(
        p -> domain_name LIKE p,
        (SELECT groupArray(pattern) FROM interesting_search)
    )
);





CREATE MATERIALIZED VIEW clickhousealert_real
ENGINE = URL('https://yourslack_web_hook', 'JSONEachRow')
AS
SELECT
    if(
        length(domain_names) = 0,
        '',
        concat(
            'Domains:\n',
            arrayStringConcat(
                arrayMap((d, f) -> concat('- ', d, ' (', formatDateTime(f, '%Y-%m-%d'), ')'), domain_names, fetch_dates),
                '\n'
            )
        )
    ) AS text,
    'dns-alert' AS username,
    ':warning:' AS icon_emoji
FROM (
    SELECT
        groupArray(domain_name) AS domain_names,
        groupArray(fetch_date) AS fetch_dates
    FROM new_dns
    WHERE arrayExists(
        p -> domain_name LIKE p,
        (SELECT groupArray(pattern) FROM interesting_search)
    )
);

---- Insert interesting patterns

INSERT INTO interesting_search (pattern) VALUES ('%clickhouse%'),('%coinbase%'),('%altinity%');

---- INSERT data to our dns table

INSERT INTO new_dns(domain_name) SETTINGS materialized_views_ignore_errors=1 SELECT * FROM url('https://raw.githubusercontent.com/romainmarcoux/malicious-domains/refs/heads/main/full-domains-ab.txt', 'CSV', 'domain_name String') WHERE domain_name NOT IN (SELECT domain_name FROM new_dns WHERE fetch_date>today()-INTERVAL 14 day); select count(*) from new_dns;