How can I download only certain columns of a compressed (bgz) file using wget?

Question

We need to download a 182 GB compressed file (uncompressed as a TSV). However, we only need the first five columns of the file, which equates to approximately 1 GB.

Is there some fancy shell magic that can be done to download a subset of a file?

Downloading the whole file only to delete 99% of it really kills our server's storage.

What is being downloaded: gnomad.genomes.v3.1.2.sites.chr1.vcf.bgz from gnomad.broadinstitute.org.

Any alternatives solutions are welcome. I'm interested in solutions that work for non-compressed files as well.

The gist is, how do I avoid downloading a massive file when I only need a subset of that file?

Answer 1

You can download the file, filter it, and then write the result to your local disk

curl https://storage.googleapis.com/gcp-public-data--gnomad/release/3.1.2/vcf/genomes/gnomad.genomes.v3.1.2.sites.chr1.vcf.bgz |
    bgzip -d |
    cut -f1-5

This still requires you to download the full file but only the filtered amount gets written to disk.

On Debian the bgzip command is provided by the tabix package. But if that's not installed you can also use gzip's zcat to read bgzip-compressed files (curl … | zcat | cut -f1-5).

---

I've had some queries about the amount of data storage this pipeline will require. Here is a real run. Notice that on this system I've only got 2GB of available storage in total; nowhere near the 182GB required to download and save the file even compressed:

# How much disk space available in my current directory?
df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/root       7.9G  5.6G  2.0G  75% /

# Download and filter the file, saving only the result
curl https://storage.googleapis.com/gcp-public-data--gnomad/release/3.1.2/vcf/genomes/gnomad.genomes.v3.1.2.sites.chr1.vcf.bgz |
bgzip -d |
cut -f1-5 > bigfile

# What did we get, and how much disk space remains?
ls -lh bigfile
-rw-r--r-- 1 roaima roaima 1.7G Feb  7 05:09 bigfile

df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/root       7.9G  7.2G  347M  96% /

Interestingly, I note that the file is not strictly TSV (tab-separated values) format. Of its 59,160,934 lines, 942 do not contain tab separated data.

file bigfile
bigfile: Variant Call Format (VCF) version 4.2, ASCII text, with very long lines

Answer 2

Simply no, there isn't.

I have looked at the data and as you said the file is quite big, so I didn't quite download it.

Second thing you say file is TSV:

... we only need the first 5 columns of the binary TSV formatted file ...

If I interpret your abbreviation correctly, you mean TSV as in Tab Separated Value file, in which case it is regular text file and not a binary file. Of course from certain point of view even text files are binary files - but in this case we are talking about text file.

However the file is indeed compressed:

: curl -s -L https://storage.googleapis.com/gcp-public-data--gnomad/release/3.1.2/vcf/genomes/gnomad.genomes.v3.1.2.sites.chr1.vcf.bgz | file -
/dev/stdin: Blocked GNU Zip Format (BGZF; gzip compatible), block length 4462

As somebody already answered, this means, you can use streaming technique to unpack file on the fly, by sending it over pipe to decompresor. bgzcat doesn't seem to exist in my distro, but zcat, which is quite universal, is in my distro and knows how to read this compressed stream:

: curl -s -L https://storage.googleapis.com/gcp-public-data--gnomad/release/3.1.2/vcf/genomes/gnomad.genomes.v3.1.2.sites.chr1.vcf.bgz | zcat | head -n 1              
##fileformat=VCFv4.2

Investigating further, beginning of the file there are comments:

curl -L https://storage.googleapis.com/gcp-public-data--gnomad/release/3.1.2/vcf/genomes/gnomad.genomes.v3.1.2.sites.chr1.vcf.bgz | zcat | head -n 80
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0##fileformat=VCFv4.2
##hailversion=0.2.77-684f32d73643
##FILTER=<ID=AC0,Description="Allele count is zero after filtering out low-confidence genotypes (GQ < 20; DP < 10; and AB < 0.2 for het calls)">
##FILTER=<ID=AS_VQSR,Description="Failed VQSR filtering thresholds of -2.7739 for SNPs and -1.0606 for indels">
##FILTER=<ID=InbreedingCoeff,Description="InbreedingCoeff < -0.3">
##FILTER=<ID=PASS,Description="Passed all variant filters">
##INFO=<ID=AC,Number=A,Type=Integer,Description="Alternate allele count">
##INFO=<ID=AN,Number=1,Type=Integer,Description="Total number of alleles">
##INFO=<ID=AF,Number=A,Type=Float,Description="Alternate allele frequency">
##INFO=<ID=popmax,Number=A,Type=String,Description="Population with maximum allele frequency">
##INFO=<ID=faf95_popmax,Number=A,Type=Float,Description="Filtering allele frequency (using Poisson 95% CI) for the population with the maximum allele frequency">
##INFO=<ID=AC_non_v2_XX,Number=A,Type=Integer,Description="Alternate allele count for XX samples in non_v2 subset">
##INFO=<ID=AN_non_v2_XX,Number=1,Type=Integer,Description="Total number of alleles in XX samples in non_v2 subset">
##INFO=<ID=AF_non_v2_XX,Number=A,Type=Float,Description="Alternate allele frequency in XX samples in non_v2 subset">
##INFO=<ID=nhomalt_non_v2_XX,Number=A,Type=Integer,Description="Count of homozygous individuals in XX samples in non_v2 subset">
##INFO=<ID=AC_non_cancer_fin_XX,Number=A,Type=Integer,Description="Alternate allele count for XX samples of Finnish ancestry in non_cancer subset">
##INFO=<ID=AN_non_cancer_fin_XX,Number=1,Type=Integer,Description="Total number of alleles in XX samples of Finnish ancestry in non_cancer subset">
##INFO=<ID=AF_non_cancer_fin_XX,Number=A,Type=Float,Description="Alternate allele frequency in XX samples of Finnish ancestry in non_cancer subset">

Finally @943 line probably headers start:

: curl -s -L https://storage.googleapis.com/gcp-public-data--gnomad/release/3.1.2/vcf/genomes/gnomad.genomes.v3.1.2.sites.chr1.vcf.bgz | zcat | tail -n+943 | head -n 1 
#CHROM  POS ID  REF ALT QUAL    FILTER  INFO

I am too lazy to determine which is actually column delimiter in the first data line, but it seems "\t" is indeed column separator:

: curl -s -L https://storage.googleapis.com/gcp-public-data--gnomad/release/3.1.2/vcf/genomes/gnomad.genomes.v3.1.2.sites.chr1.vcf.bgz | zcat | tail -n+944 | head -n 1 
chr1    10031   .   T   C   .   AC0;AS_VQSR AC=0;AN=56642;AF=0.00000;AC_non_v2_XX=0;AN_non_v2_XX=23674;AF_non_v2_XX=0.00000;nhomalt_non_v2_XX=0;AC_non_cancer_fin_XX=0;AN_non_cancer_fin_XX=1060;AF_non_cancer_fin_XX=0.00000;nhomalt_non_cancer_fin_XX=0;AC_non_neuro_nfe=0;AN_non_neuro_nfe=24462;AF_non_neuro_nfe=0.00000;nhomalt_non_neuro_nfe=0;AC_non_neuro_afr_XY=0;AN_non_neuro_afr_XY=5226;AF_non_neuro_afr_XY=0.00000;nhomalt_non_neuro_afr_XY=0;AC_non_neuro_nfe_XY=0;AN_non_neuro_nfe_XY=9974;AF_non_neuro_nfe_XY=0.00000;nhomalt_non_neuro_nfe_XY=0;AC_controls_and_biobanks_eas_XY=0;AN_controls_and_biobanks_eas_XY=392;AF_controls_and_biobanks_eas_XY=0.00000;nhomalt_controls_and_biobanks_eas_XY=0;AC_non_neuro_sas_XX=0;AN_non_neuro_sas_XX=260;AF_non_neuro_sas_XX=0.00000;nhomalt_non_neuro_sas_XX=0;AC_non_v2=0;AN_non_v2=44696;AF_non_v2=0.00000;nhomalt_non_v2=0;AC_non_topmed_nfe_XX=0;AN_non_topmed_nfe_XX=2800;AF_non_topmed_nfe_XX=0.00000;nhomalt_non_topmed_nfe_XX=0;AC_non_v2_mid=0;AN_non_v2_mid=192;AF_non_v2_mid=0.00000;nhomalt_non_v2_mid=0;AC_non_topmed_sas=0;AN_non_topmed_sas=1114;AF_non_topmed_sas=0.00000;nhomalt_non_topmed_sas=0;AC_non_cancer_eas_XX=0;AN_non_cancer_eas_XX=746;AF_non_cancer_eas_XX=0.00000;nhomalt_non_cancer_eas_XX=0;AC_amr_XY=0;AN_amr_XY=3650;AF_amr_XY=0.00000;nhomalt_amr_XY=0;AC_non_v2_nfe_XX=0;AN_non_v2_nfe_XX=12970;AF_non_v2_nfe_XX=0.00000;nhomalt_non_v2_nfe_XX=0;AC_controls_and_biobanks_XY=0;AN_controls_and_biobanks_XY=6960;AF_controls_and_biobanks_XY=0.00000;nhomalt_controls_and_biobanks_XY=0;AC_non_neuro_asj_XY=0;AN_non_neuro_asj_XY=722;AF_non_neuro_asj_XY=0.00000;nhomalt_non_neuro_asj_XY=0;AC_oth=0;AN_oth=782;AF_oth=0.00000;nhomalt_oth=0;AC_non_topmed_mid_XY=0;AN_non_topmed_mid_XY=82;AF_non_topmed_mid_XY=0.00000;nhomalt_non_topmed_mid_XY=0;AC_non_cancer_asj_XX=0;AN_non_cancer_asj_XX=770;AF_non_cancer_asj_XX=0.00000;nhomalt_non_cancer_asj_XX=0;AC_sas_XY=0;AN_sas_XY=860;AF_sas_XY=0.00000;nhomalt_sas_XY=0;AC_non_neuro_fin=0...

So there is your data.

Now the issues:

• because file is TSV, it is basically just more general from of CSV format and unfortunately this means, it is line oriented format, not column oriented, and that means:
  • incoming data unit is line
  • line incoming is split into columns and not vice versa
  • i.e. to get "just" first 5 columns you still need to visit all the lines in the file
  • because lines have unexpected/unpredictable lengths, structure of the data is irregular so you cannot compute required byte ranges in any meaningful way
• because file is bgzip compressed, it is impossible to use byte ranges anyway
  • due to errors in size calculation when using in on the fly gzip compressions in HTTP servers, byte ranges on such files are often broken
  • we don't know whether file is pre-compressed (and put into download dir) or not (and is on the fly compressed by the httpd/nginx)
  • given the size file, it is probably pre-compressed, so theoretically byte ranges could work, but you would still need index to map respective uncompressed lines into respective BGZIP block which is probably task above your level (given you are asking this question - it would not be profitable even on my level given time effort ratio (I would need weeks/months))
  • even if you got the indexing working you still need to download/parse the whole file and even then the moment file is changed upstream the index breaks
• easiest for you is to hope that google download servers are setup in such a way that they won't kill your connection even if it lingers for hours/days.

Given last point you could do what @user10489 is saying:

1. start streaming file from google servers with curl and pipe it to zcat
2. attach end of the zcat output with pipe to input of some quickly hacked together script: perl, python, php, lua
3. in the script parse each incoming line for first five columns delimited with tabs, and store those columns into separate data file locally (TSV, or I would suggest sqlite3) - you also have to ignore comments at the beginning of dataset mentioned above.

The final usage would look something like this:

: curl -s -L https://storage.googleapis.com/gcp-public-data--gnomad/release/3.1.2/vcf/genomes/gnomad.genomes.v3.1.2.sites.chr1.vcf.bgz | zcat | myscript my-outfile.tsv

You get the picture.

This is doable in couple in minutes/hours, depending on your ability, but now you will have following problems:

• you need to setup your pipeline in a way so it can run unattended for hours (tmux, screen)
• even if you implement line counting and store the last processed line in a sentinel file, if ever you script crashes in the middle of the processing, you will need to restart the download from beginning, and either wait until you reach the stored line, or, in this case, it is simply easier to reprocess everything again on restart
• you don't know that "only first 5 columns" extracted will be substantially smaller than 128GB compressed - you might run out of space during processing anyway
• finally we don't know whether google servers don't have some "download connection taking too long" protection, so they might sever their link to curl head of pipeline prematurely - and that means it will be effectively for you still impossible to stream the whole file down.

So in your case I would go to project/research lead or management and request resources: drive, perhaps vm/node, download whole file as intended and preprocess it locally. Analyze resulting pre-processed 5 column file length/size, and upload to servers only that.

You won't depend on google server not severing your pipeline, and you can rerun and tune your pipeline as many times as you want. You won't have to worry about "will 5 columns "only" fit into storage?" question either.

Hope it helped.

Answer 3

• If the file wasn't compressed, it is possible in the http protocol to download ranges of bytes. However, this probably isn't useful without an index, even if it is possible.
• If the file is compressed with stream compression, you have to download the whole thing to decompress any of it. A lot of stream compressors actually do sequential compressed blocks, but again, without an index of those blocks, it's basically not feasible, and if you're trying to get single column rather than a range of large chunks, it's not possible.
• If it's not the network bandwidth but the disk space you are having problems with, there's lots of options. Download the thing to memory, stream uncompress it in memory, send the uncompressed data to a process that extracts the column you need and write it out. This can all be done in a single pipeline with wget -O - | bgzcat | ... and stream based tools that can take input on stdin and write what you need to stdout or a file.

Answer 4

As others have noted, it is technically impossible to download only certain columns of a file using wget.

You would need to filter the data before you download it, which means the data should be filtered server-side. A simple compressed file on an HTTPS server does not provide this capability.

Other alternatives:

• It might be worth investigating if you can access the data in different ways. Perhaps the Tab Separated Value (TSV) file is actually the output of a database dump, and you can query the database directly using an Application Programming Interface (API). Perhaps there exists a way to tailor the data based on a Representational state transfer (REST) API or otherwise. You may wish to confirm with the data provider if such an interface is publicly available. It would seem unexpected that such a huge TSV file is the way in which they store the data internally.

• You may be able to bring your code to the data rather than the data to the code. Apparently, the data are stored in multiple clouds. If you have a tenancy in either of them, you can access the data much more quickly, perhaps filtering it down to be small enough for you to store before downloading it to your local system.