SQL

[gibberwocky]

Could we have the option to import CSV data and enable SQL to JOIN with the tables generated after importing a VCF? I'm aware that the cutevariant generated database can simply be opened with a DB browser to enable this functionality, but it would be nice to have the option to create/import tables and perform SQL queries. If this functionality is present can someone please point me in the right direction as I can't see it.

Cheers

D

[dridk]

Actually, you can import new VCF into an existing database by clicking on "import" .
I don't work with CSV, but we have a CSV parser to load variants from CSV files. But it has been disabled for now.

Do you have a CSV specification ? I can add this features if you wish.

[gibberwocky]

Thanks. I was thinking more of its utility for non-variant data.

As an example, if I have a dog VCF file which has been annotated using VEP to add ensembl gene id's, then it would be useful to be able to import a table that contains ensembl gene id's and their associated gene name so that an SQL join statement could be used to append gene name with a query. The result of the query could then be used to cross-check genes against harmonizome wordsets.

There are probably countless other examples where having the flexibility to create/import ad hoc tables and apply SQL filters could be useful.

[dridk]

Why not annotating your VCF with gene name before importing ?
I guess you are asking a feature to add extra annotation. For instance "annotate all variants with a CSV files ". This is a good idea .

We can imagine for instance :

• Click on annotate button
• Display a dialog to select annotation source (CSV ) , type , join column etc ..
• Add extra columns in the existing table .

[gibberwocky]

I've attempted to annotate the VCF with the gene name when running VEP, but without success. It could be that the GFF file passed to command-line VEP is not formatted sufficiently, or it could be that it simply isn't possible to add this annotation using VEP command-line with non-human genomes. It's not something I've had a lot of experience with, but my efforts over the last couple of days have been unsuccessful.

I don't know how complex the cutevariant software is, but I would have thought given that it is based on SQLite, providing the option to create a table should be straight-forward, which leaves either incorporating SQL into the VQL editor, or having a separate interface for it. If I import the cutevariant project db into DB Browser for SQLite, along with a 2 column table exported from biomart which contains gene name and transcript, then the SQL query to retrieve gene name is basic:

SELECT * from annotations INNER JOIN biomart_genes ON biomart_genes.TranscriptstableID=annotations.transcript

Presumably the result of which can be saved as a separate table and filtered using VQL with the GUI?

You may even be able to extend the VQL IMPORT clause to facilitate importing a multi-field table?

[dridk]

Did you try :

• bcftools annotate
• SnpSift annotate

I suggest to map genome coordinate to gene names instead of gene id .

About your idea for cutevariant, it's not easy to implement your proposal feature. Let me think how to do that. @SteampunkIslande what do you think ?

[gibberwocky]

True, I could use those, or vcfanno. That would likely be easier, and is exactly what I did for a couple of other annotations. To be honest I was hoping to solve it with VEP and got a little lost over-thinking it!

Cheers

D

[antonylebechec]

An "annotation plugin" is obviously a very smart idea!
In that way, "construct" an annotation table, or probably an extra annotation db (like GeneView plugin) should be a solution. By adding extra files, in many format (VCF, TSV, BED, commun used format such as Annovar, UCSC), your own annotation DB will be shared between projects, and could be use to directly annotate your project, before analyses/explorations.
So, basic VQL queries:
SELECT chr, pos, ref, alt, myNewAnnotation from variants WHERE myNewAnnotation=NM12345 (to select specific variants, wordsets can be used)
SELECT chr, pos, ref, alt, myNewAnnotation from variants WHERE myNewAnnotation NOT NULL(to select variants with this annotation, so within a list if the annotation is considered a flag)