Nfacct-clickhouse-import

From Initech Technical Wiki
Jump to: navigation, search

This script expects a json formatted (JSONEachRow in clickhouse terminology) input file comprising netflow / IPFIX records exported from pmacct at /tmp/nfacctd.json. It uses /usr/local/bin/nfacct_drop_patterns to pre-filter out some flows that we don't care about and then imports the remainder into clickhouse into the netflow.nfacct table.

It also imports the whole file without filters into a seperate table so that post-import DOS analysis can be done by a seperate script. (work in progress)

#!/bin/bash
cat /tmp/nfacctd.json | grep -v -f /usr/local/bin/nfacct_drop_patterns | clickhouse-client --input_format_skip_unknown_fields=1 --query="INSERT INTO netflow.nfacct FORMAT JSONEachRow"
clickhouse-client --query="truncate table netflow.nfacct_dos"
cat /tmp/nfacctd.json | clickhouse-client --input_format_skip_unknown_fields=1 --query="INSERT INTO netflow.nfacct_dos FORMAT JSONEachRow"