Memory usage with miller & join

[trantor]

Hello everyone.

I am processing a multi-GB CSV file, attempting to join with a different CSV file of just a few lines... and quickly I've got to abort mlr since the RSS of the process skyrockets to multi-GB levels as well, quite larger than the original CSV as well.

Much the same things happens when one tries, for instance, a grep -f with dozens of regex patterns (not fixed strings), so I am not exactly surprised. However, since the problem of joining (essentialy to filter and associate down to a relatively small amount of records) on sizeable CSV files is relatively common, I would be curious about options on alternative approaches to the problem.

[johnkerl]

Hi @trantor -- you might check out mlr join -s -- if the inputs are already sorted on their join key(s).

Another idea is to maybe swap the role of the 'left' and 'right' file. Without -s, the 'left' file will be loaded into memory but the 'right' file will be streamed.

[johnkerl]

@trantor there is no meaningful difference in what you wrote. :)

I can reproduce using your data above; 👀 ...

[johnkerl]

@trantor please add --no-implicit-csv-header on your final join

$ cat file1.csv
path,foo
backup-mail3/items/%2BPInrYaKIv0OSIMSwqYk59DeWiODMx%2CQKcOLCRCRGyA%3D,1
backup-mail3/items/CfKi2QeCymoegAPaBS8Pe%2C8fJ0sRwymik%2CHLFzKXiOI%3D,2
backup-mail3/items/cvRdeMKgDe8X0QJWPaBjn%2B8j7xcddQO8INfFRmpGZvU%3D,3
backup-mail3/items/vdtyY8v4Gdx2t2EgK7dka3zuDxq3QifH7ml433jlpMc%3D,4

$ cat file2.csv
"/opt/zimbra/backup/zextras/accounts/a0dde7c9-5c48-43e0-b52b-11b0007be7d2/items/02/302","%2BPInrYaKIv0OSIMSwqYk59DeWiODMx%2CQKcOLCRCRGyA%3D"
"/opt/zimbra/backup/zextras/accounts/a0dde7c9-5c48-43e0-b52b-11b0007be7d2/items/02/602","CfKi2QeCymoegAPaBS8Pe%2C8fJ0sRwymik%2CHLFzKXiOI%3D"
"/opt/zimbra/backup/zextras/accounts/a0dde7c9-5c48-43e0-b52b-11b0007be7d2/items/02/702","cvRdeMKgDe8X0QJWPaBjn%2B8j7xcddQO8INfFRmpGZvU%3D"
"/opt/zimbra/backup/zextras/accounts/a0dde7c9-5c48-43e0-b52b-11b0007be7d2/items/02/902","vdtyY8v4Gdx2t2EgK7dka3zuDxq3QifH7ml433jlpMc%3D"

mlr --from file2.csv --implicit-csv-header --csv \
    rename 1,metadata_path,2,digest then \
    filter '$metadata_path !=~ "\\.del$"' then \
    cat -n -g digest then \
    filter '$n == 1' then \
    cut -x -f n then \
    put '$digest = "backup-mail3/items/".$digest' then \
    sort -f digest then \
    join -f file1.csv -i csv -l path -r digest -j path

(nothing)

vs

mlr --from file2.csv --implicit-csv-header --csv \
    rename 1,metadata_path,2,digest then \
    filter '$metadata_path !=~ "\\.del$"' then \
    cat -n -g digest then \
    filter '$n == 1' then \
    cut -x -f n then \
    put '$digest = "backup-mail3/items/".$digest' then \
    sort -f digest then \
    join -f file1.csv --no-implicit-csv-header -i csv -l path -r digest -j path

path,foo,metadata_path
backup-mail3/items/%2BPInrYaKIv0OSIMSwqYk59DeWiODMx%2CQKcOLCRCRGyA%3D,1,/opt/zimbra/backup/zextras/accounts/a0dde7c9-5c48-43e0-b52b-11b0007be7d2/items/02/302
backup-mail3/items/CfKi2QeCymoegAPaBS8Pe%2C8fJ0sRwymik%2CHLFzKXiOI%3D,2,/opt/zimbra/backup/zextras/accounts/a0dde7c9-5c48-43e0-b52b-11b0007be7d2/items/02/602
backup-mail3/items/cvRdeMKgDe8X0QJWPaBjn%2B8j7xcddQO8INfFRmpGZvU%3D,3,/opt/zimbra/backup/zextras/accounts/a0dde7c9-5c48-43e0-b52b-11b0007be7d2/items/02/702
backup-mail3/items/vdtyY8v4Gdx2t2EgK7dka3zuDxq3QifH7ml433jlpMc%3D,4,/opt/zimbra/backup/zextras/accounts/a0dde7c9-5c48-43e0-b52b-11b0007be7d2/items/02/902

since it appears your file1.csv has a header, where file2.csv does not except you are adding one during processing.

[aborruso]

@johnkerl a note: I do not find --no-implicit-csv-header in the documentation https://miller.readthedocs.io/en/latest/reference.html

After applying rename verb it would be like having canceled --implicit-csv-header option, mlr should inherit it. Am I saying something meaningful and useful?

[trantor]

@aborruso I can't find it either...
Anyway @johnkerl thanks for spotting the problem. I guess that since that option is not mentioned in the list for the options related to the left file in join I assumed it would not be inherited.
Can you document those other options as well? Thanks :D

[johnkerl]

mlr should inherit it

@aborruso mlr join is inherting --implicit-csv-header from mlr but mlr join is being given data which has a header. So mlr join needs to be told --no-implicit-csv-header.

Can you document those other options as well? Thanks :D

Thanks @trantor , will do!