Issue when ingesting VCFs with beacon_init

[jimmyhli]

Description

Hello, I am trying to ingest some VCF files from my pipeline as well as 1kgenome (for testing), but am getting the following errors

[2021-06-02 16:19:30,503][beacon][26314 MainProcess][INFO    ] (L:299) db_load | insert_variants: Received 1000 variants for insertion to hostseq_bcgsc
[2021-06-02 16:19:30,504][beacon][26314 MainProcess][INFO    ] (L:303) db_load | insert_variants: Insert variants into the database
[2021-06-02 16:19:30,504][beacon][26314 MainProcess][ERROR   ] (L:383) db_load | insert_variants: AN ERROR OCCURRED WHILE ATTEMPTING TO INSERT VARIANTS -> list index out of range

I tried to load it with the beacon_init, how can I debug what error occurred? What exactly is the list index out of range here?

DoD (Definition of Done)

When I can successfully load VCFs

Testing

Below is how I run mine

I made up some data for the metadata.json

wget ftp://ftp.1000genomes.ebi.ac.uk/vol1/ftp/release/20130502//ALL.chr1.phase3_shapeit2_mvncall_integrated_v5b.20130502.genotypes.vcf.gz

beacon_init  /projects/1kgenome/ALL.chr1.phase3_shapeit2_mvncall_integrated_v5b.20130502.genotypes.vcf.gz  metadata.json

Thanks a lot!!

[blankdots]

We have some test data we are loading in https://github.com/CSCfi/beacon-python/tree/master/data
will look into it in the next weeks.

[jimmyhli]

This is what I got when I try to load the test data, maybe I'm doing something wrong?

Also, I think our postgres server is only 9.4; is that too old?

PS. Yeah, the docs say 9.6+; sorry about that

Best

(beacon-python) [jimli@localhost beacon-python]$ beacon_init /projects/1kgenome/ALL.chrMT.phase3_callmom-v0_4.20130502.genotypes.vcf.gz example_metadata.json 
[2021-06-03 09:26:11,399][beacon][24803 MainProcess][INFO    ] (L:61) db_load | __init__: Start database routines
[2021-06-03 09:26:11,399][beacon][24803 MainProcess][INFO    ] (L:180) db_load | connection: Establish a connection to database
[2021-06-03 09:26:11,482][beacon][24803 MainProcess][INFO    ] (L:189) db_load | connection: Database connection has been established
[2021-06-03 09:26:11,484][beacon][24803 MainProcess][INFO    ] (L:195) db_load | check_tables: Request tables from database
[2021-06-03 09:26:11,493][beacon][24803 MainProcess][INFO    ] (L:203) db_load | check_tables: Tables received -> check that correct tables exist
[2021-06-03 09:26:11,493][beacon][24803 MainProcess][INFO    ] (L:208) db_load | check_tables: beacon_dataset_table exists
[2021-06-03 09:26:11,493][beacon][24803 MainProcess][INFO    ] (L:208) db_load | check_tables: beacon_dataset_counts_table exists
[2021-06-03 09:26:11,493][beacon][24803 MainProcess][INFO    ] (L:208) db_load | check_tables: beacon_data_table exists
[2021-06-03 09:26:11,493][beacon][24803 MainProcess][INFO    ] (L:208) db_load | check_tables: beacon_mate_table exists
[2021-06-03 09:26:11,493][beacon][24803 MainProcess][INFO    ] (L:228) db_load | load_metadata: Calculate number of samples from /projects/candig/1kgenome/ALL.chrMT.phase3_callmom-v0_4.20130502.genotypes.vcf.gz
[2021-06-03 09:26:11,494][beacon][24803 MainProcess][INFO    ] (L:230) db_load | load_metadata: Parse metadata from example_metadata.json
[2021-06-03 09:26:11,495][beacon][24803 MainProcess][INFO    ] (L:234) db_load | load_metadata: <_io.TextIOWrapper name='example_metadata.json' mode='r' encoding='UTF-8'>
[2021-06-03 09:26:11,496][beacon][24803 MainProcess][INFO    ] (L:236) db_load | load_metadata: {'name': '1000 genomoe', 'datasetId': 'urn:hg:1000genome', 'description': 'Data from the 1000 Genomes Project. The 1000 Genomes Project ran between 2008 and 2015, creating the largest public catalogue of human variation and genotype data. As the project ended, the Data Coordination Centre at EMBL-EBI has received continued funding from the Wellcome Trust to maintain and expand the resource.', 'assemblyId': 'GRCh38', 'createDateTime': '2013-05-02 12:00:00', 'updateDateTime': '2013-05-02 12:00:00', 'version': 'v0.4', 'externalUrl': 'ftp://ftp.1000genomes.ebi.ac.uk/vol1/ftp/release/20130502/', 'accessType': 'PUBLIC', 'callCount': 3892, 'variantCount': 4242}
[2021-06-03 09:26:11,496][beacon][24803 MainProcess][INFO    ] (L:237) db_load | load_metadata: Metadata has been parsed
[2021-06-03 09:26:11,496][beacon][24803 MainProcess][INFO    ] (L:239) db_load | load_metadata: Attempting to insert metadata to database
[2021-06-03 09:26:11,499][beacon][24803 MainProcess][ERROR   ] (L:270) db_load | load_metadata: AN ERROR OCCURRED WHILE ATTEMPTING TO INSERT METADATA -> syntax error at or near "ON"
[2021-06-03 09:26:11,499][beacon][24803 MainProcess][INFO    ] (L:287) db_load | load_datafile: Read data from /projects/candig/1kgenome/ALL.chrMT.phase3_callmom-v0_4.20130502.genotypes.vcf.gz
[2021-06-03 09:26:11,500][beacon][24803 MainProcess][INFO    ] (L:289) db_load | load_datafile: Generate database queue(s)
[2021-06-03 09:26:11,614][beacon][24803 MainProcess][INFO    ] (L:299) db_load | insert_variants: Received 1000 variants for insertion to urn:hg:1000genome
[2021-06-03 09:26:11,615][beacon][24803 MainProcess][INFO    ] (L:303) db_load | insert_variants: Insert variants into the database
[2021-06-03 09:26:11,616][beacon][24803 MainProcess][ERROR   ] (L:383) db_load | insert_variants: AN ERROR OCCURRED WHILE ATTEMPTING TO INSERT VARIANTS -> syntax error at or near "ON"
[2021-06-03 09:26:11,726][beacon][24803 MainProcess][INFO    ] (L:299) db_load | insert_variants: Received 1000 variants for insertion to urn:hg:1000genome
[2021-06-03 09:26:11,726][beacon][24803 MainProcess][INFO    ] (L:303) db_load | insert_variants: Insert variants into the database
[2021-06-03 09:26:11,728][beacon][24803 MainProcess][ERROR   ] (L:383) db_load | insert_variants: AN ERROR OCCURRED WHILE ATTEMPTING TO INSERT VARIANTS -> syntax error at or near "ON"
[2021-06-03 09:26:11,841][beacon][24803 MainProcess][INFO    ] (L:299) db_load | insert_variants: Received 1000 variants for insertion to urn:hg:1000genome
[2021-06-03 09:26:11,842][beacon][24803 MainProcess][INFO    ] (L:303) db_load | insert_variants: Insert variants into the database
[2021-06-03 09:26:11,843][beacon][24803 MainProcess][ERROR   ] (L:383) db_load | insert_variants: AN ERROR OCCURRED WHILE ATTEMPTING TO INSERT VARIANTS -> syntax error at or near "ON"
[2021-06-03 09:26:11,941][beacon][24803 MainProcess][INFO    ] (L:299) db_load | insert_variants: Received 892 variants for insertion to urn:hg:1000genome
[2021-06-03 09:26:11,941][beacon][24803 MainProcess][INFO    ] (L:303) db_load | insert_variants: Insert variants into the database
[2021-06-03 09:26:11,943][beacon][24803 MainProcess][ERROR   ] (L:383) db_load | insert_variants: AN ERROR OCCURRED WHILE ATTEMPTING TO INSERT VARIANTS -> syntax error at or near "ON"
[2021-06-03 09:26:11,943][beacon][24803 MainProcess][INFO    ] (L:293) db_load | load_datafile: /projects/candig/1kgenome/ALL.chrMT.phase3_callmom-v0_4.20130502.genotypes.vcf.gz has been processed
[2021-06-03 09:26:11,944][beacon][24803 MainProcess][INFO    ] (L:388) db_load | close: Mark the database connection to be closed
[2021-06-03 09:26:11,946][beacon][24803 MainProcess][INFO    ] (L:390) db_load | close: The database connection has been closed

[blankdots]

so it works, can we close?

[jimmyhli]

I will try it out with the new version of postgres and let you know

When you get a chance, could you try out the ftp://ftp.1000genomes.ebi.ac.uk/vol1/ftp/release/20130502//ALL.chr1.phase3_shapeit2_mvncall_integrated_v5b.20130502.genotypes.vcf.gz? Thanks a lot

[jimmyhli]

Hi there,

So I am able to successfully ingest the test data.

However, I am still unable to ingest the 1kgenome data file, or the file from our own pipeline.

Do you mind trying ingestion with this one? The error is still list index out of range.

wget ftp://ftp.1000genomes.ebi.ac.uk/vol1/ftp/release/20130502//ALL.chr1.phase3_shapeit2_mvncall_integrated_v5b.20130502.genotypes.vcf.gz

beacon_init  /projects/1kgenome/ALL.chr1.phase3_shapeit2_mvncall_integrated_v5b.20130502.genotypes.vcf.gz  metadata.json

Best

[jimmyhli]

I printed out the unpack values, looks like some aren't successfully unpacked, which caused the issue, but some are. (this is with the chr1 data whose link is posted above)

[2021-06-03 21:25:34,154][beacon][14661 MainProcess][INFO    ] (L:308) db_load | insert_variants: ([0.000399360986193642], [2], ['SNP'], ['A'], 5008, [])
[2021-06-03 21:25:34,155][beacon][14661 MainProcess][INFO    ] (L:308) db_load | insert_variants: ([0.000599041988607496], [3], ['SNP'], ['A'], 5008, [])
[2021-06-03 21:25:34,155][beacon][14661 MainProcess][INFO    ] (L:308) db_load | insert_variants: ([], [], ['SNP'], ['C'], 5008, [])
[2021-06-03 21:25:34,156][beacon][14661 MainProcess][ERROR   ] (L:385) db_load | insert_variants: AN ERROR OCCURRED WHILE ATTEMPTING TO INSERT VARIANTS -> list index out of range

[blankdots]

yes this seems an issue, might be that https://github.com/brentp/cyvcf2 changed some of their unpacking in the meantime

[blankdots]

this should be fixed, closing