AWK - Processing a large CSV (13 Billion Rows) for duplicate data based on multiple columns (composite key) results in Out of Memory error

user_15742743 0
Viewed 187

I have a CSV with 13 billion rows in it, 719GB in size. There are some duplicated rows in the CSV. The CSV has three columns, sample data below:

tag,time,sensor_value
"CHLR_3_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-08-31 10:27:13.736248","59.76000213623047"

Each instance of the composite key tag and time should be unique. In words, a tag can have one value at a given time.

I have tried the following:

awk -F, '!seen[$1,$2]++' data.csv > data_UNIQUE.csv

Kernel eventually kills the above process due to Out Of Memory error. My system specifications are as follows:

Intel(R) Core(TM) i9-10900K CPU @ 3.70GHz
128GB RAM
2TB NVME

How can I successfully process this CSV with awk?

EDIT: The desired output CSV would have no duplicated data, and per discussion in the comments, it would make sense to sort before passing to awk so we only look at adjacent rows.

Desired output:

tag,time,sensor_value
"CHLR_1_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-08-31 10:27:13.736248","59.76000213623047"
csv awk
2 Answers

Sort based on the multi-column key. Then a single pass through the sorted file can eliminate duplicates by looking only at adjacent records. There is no need to store the entire file in memory.

EDIT: To compare adjacent records and ignore duplicates (WARNING: UNTESTED CODE):

($1 $2) == prev { next }
{ prev = ($1 $2); print $0 }

The first line compares the concatenation of the first two fields to the previous record and skips to the next record if the key fields of the current record and previous record match. The second line is executed only if the current record differs from the previous record; it saves the key in the first two fields, then prints the record.

user_4488100

Using any versions of the mandatory Unix tools awk, sort, and cut, this will sort the output by the 2 key values:

$ cat tst.sh
#!/usr/bin/env bash

awk '
    BEGIN { FS=OFS="," }
    { print (NR>1), NR, $0 }
' "${@:--}" |
sort -t, -k1,1n -k3,4 -k2,2n |
cut -d, -f3- |
awk '
    BEGIN { FS=OFS="," }
    { key = $1 FS $2 }
    key != prev {
        print
        prev = key
    }
'


$ ./tst.sh file
tag,time,sensor_value
"CHLR_1_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-08-31 10:27:13.736248","59.76000213623047"

This will preserve the input order for the output:

$ cat tst.sh
#!/usr/bin/env bash

awk '
    BEGIN { FS=OFS="," }
    { print (NR>1), NR, $0 }
' "${@:--}" |
sort -t, -k3,4 |
awk '
    BEGIN { FS=OFS="," }
    { key = $1 FS $3 FS $4 }
    key != prev {
        print
        prev = key
    }
' |
sort -t, -k1,1n -k2,2n |
cut -d, -f3-


$ ./tst.sh file
tag,time,sensor_value
"CHLR_3_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_3_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_1_SP","2020-08-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-03-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-01-31 10:27:13.736248","59.76000213623047"
"CHLR_2_SP","2020-08-31 10:27:13.736248","59.76000213623047"

We decorate the input using awk (printing NR>1) to identify the header line (0) separately from the rest (1) instead of using head -n 1 test.csv && tail -n +2 test.csv | sort... because the latter requires the input file to be opened twice and so won't work if the input is coming from a pipe.

We also decorate with NR so that given 2 duplicate keys the value printed will be the first one that occurred in the input (or we could revers the sort on that field so the last value gets printed if that's preferable). We could instead use GNU sort for -s but then the solution becomes unnecessarily GNU-only.

user_17450010
Related