aws vpc flow log analysis with postgres

import.sql

COPY vpc FROM '/SOME/PATH/concat' WITH (FORMAT CSV, DELIMITER ' ', NULL '-');

process.sh

#!/usr/bin/env bash
set -euo pipefail
IFS=$'\n\t'

aws s3 cp --recursive s3://bucket/flow .

for FILE in */*.gz; do
	echo "$FILE"
	gzip -d "$FILE"
done

if -e concat; then
	echo 'concat file already exists'
	exit 1
fi

for FILE in */*; do
	echo "$FILE"
	cat "$FILE" >> concat
	rm -rf "$FILE"
done

schema.sql

CREATE TABLE "public"."vpc" (
    "time" timestamp NOT NULL,
    "version" integer NOT NULL,
    "account" text NOT NULL,
    "interface" text NOT NULL,
    "srcaddr" inet,
    "dstaddr" inet,
    "srcport" integer,
    "dstport" integer,
    "protocol" text,
    "packets" bigint,
    "bytes" bigint,
    "start" text,
    "end" text,
    "action" text,
    "log-status" text
) WITH (oids = false);