# Tabular SNPEff data for 1135 accessions by Joffrey Fitz ## Overview This dataset is a tabular representation of the SnpEff-annotated multi-sample VCF from the 1001 Genomes Project for *A. thaliana*.([1001genomes_snp-short-indel_only_ACGTN_v3.1.vcf.snpeff.gz](https://1001genomes.org/data/GMI-MPI/releases/current/1001genomes_snpeff_v3.1/1001genomes_snp-short-indel_only_ACGTN_v3.1.vcf.snpeff.gz)) The purpose of this dataset is to make variant-level and effect-level lookups fast and easy in SQL. The dataset uses horizontal partitioning by chromosome and separates variant classes into distinct tables/files for SNPs, insertions, and deletions. We provide a demo setup for SQLite, but the schema and loading approach can be adapted easily for MariaDB or PostgreSQL for larger multi-user deployments. If the main use case is range-based querying, we provide example `tabix` scripts as a lightweight alternative for fast positional lookups. ## Dataset contents For each chromosome 1 through 5, variants are partitioned by variant class into separate compressed TSV files (.gz): - SNPs - insertions - deletions Each variant class is represented by two related tables/files: - a main table containing the core variant record - an effects table containing the associated SnpEff consequence annotations Example file layout for chromosome 1: - `1001_snpeff_3.1_chr1_snps.tsv.gz` - `1001_snpeff_3.1_chr1_snps_effects.tsv.gz` - `1001_snpeff_3.1_chr1_ins.tsv.gz` - `1001_snpeff_3.1_chr1_ins_effects.tsv.gz` - `1001_snpeff_3.1_chr1_dels.tsv.gz` - `1001_snpeff_3.1_chr1_dels_effects.tsv.gz` --- ## Data Dictionary To assist with filtering and analysis, the following values are commonly found in the annotation columns: ### Impact Categories (`impact` and `effect_impact`) * **HIGH**: Variant assumed to have high (destructive) impact on the protein (e.g., stop gain, frameshift). * **MODERATE**: Non-disruptive variant that might change protein effectiveness (e.g., missense variant, in-frame deletion). * **LOW**: Unlikely to change protein behavior (e.g., synonymous variant, start/stop-retained). * **MODIFIER**: Usually non-coding variants where predictions are difficult (e.g., intergenic, upstream, downstream, or intron variants). ### Functional Class (`functional_class`) * **MISSENSE**: Amino acid change. * **NONSENSE**: Introduction of a stop codon. * **SILENT**: Synonymous change (no amino acid change). --- ## Column descriptions ### Main table columns | Column | Type | Meaning | | --------------- | ------------- | ----------------------------------------------------- | | `chr` | integer | Chromosome number, typically 1 to 5 | | `pos` | integer | 1-based genomic position | | `strain` | integer | Numeric identifier for the accession / strain | | `ref` | text | Reference allele | | `alt` | text | Alternate allele | | `quality` | real | Variant quality score from the source VCF | | `impact` | text nullable | High-level impact label, if present | | `type` | text | Variant type / effect category used in the export key | | `transcript_id` | text | Transcript identifier associated with the annotation | ### Effects table columns | Column | Type | Meaning | | -------------------- | ---------------- | ------------------------------------------------------------ | | `chr` | integer | Chromosome number | | `pos` | integer | 1-based genomic position | | `strain` | integer | Numeric identifier for the accession / strain | | `type` | text | Variant/effect type used for joining to the main table | | `effect_impact` | text nullable | SnpEff impact category | | `functional_class` | text nullable | Functional class such as synonymous or missense, where applicable | | `codon_change` | text nullable | Codon-level change annotation | | `amino_acid_change` | text nullable | Amino-acid change annotation | | `amino_acid_length` | integer nullable | Protein length, where reported | | `gene_name` | text nullable | Gene symbol or gene name | | `transcript_biotype` | text nullable | Transcript biotype | | `gene_coding` | text nullable | Coding/non-coding status annotation | | `transcript_id` | text | Transcript identifier | | `exon_rank` | integer nullable | Exon number / rank, where applicable | --- ## SQLite setup The schema uses composite Primary Keys and secondary indices to ensure data integrity and query performance. ### Create tables ```sql # 1. Initialize the database and create tables sqlite3 variants.db <<'SQL' CREATE TABLE chr1_snps ( chr INTEGER NOT NULL, pos INTEGER NOT NULL, strain INTEGER NOT NULL, ref TEXT NOT NULL, alt TEXT NOT NULL, quality REAL NOT NULL, impact TEXT, type TEXT NOT NULL, transcript_id TEXT NOT NULL, PRIMARY KEY (chr, pos, strain, type, transcript_id) ); CREATE TABLE chr1_snps_effects ( chr INTEGER NOT NULL, pos INTEGER NOT NULL, strain INTEGER NOT NULL, type TEXT NOT NULL, effect_impact TEXT, functional_class TEXT, codon_change TEXT, amino_acid_change TEXT, amino_acid_length INTEGER, gene_name TEXT, transcript_biotype TEXT, gene_coding TEXT, transcript_id TEXT NOT NULL, exon_rank INTEGER NOT NULL, PRIMARY KEY (chr, pos, strain, type, transcript_id, exon_rank) ); SQL ``` ### Loading TSV data into SQLite and indexing Since the TSV files are compressed, use `zcat` to pipe the data into the SQLite `.import` command via `/dev/stdin`. ```bash # 2. Import compressed data zcat 1001_snpeff_3.1_chr1_snps.tsv.gz | sqlite3 -separator $'\t' variants.db ".import /dev/stdin chr1_snps" zcat 1001_snpeff_3.1_chr1_snps_effects.tsv.gz | sqlite3 -separator $'\t' variants.db ".import /dev/stdin chr1_snps_effects" # 3. Create secondary indices for faster lookups sqlite3 variants.db <<'SQL' CREATE INDEX idx_chr1_snps_pos_strain ON chr1_snps (chr, pos, strain); CREATE INDEX idx_transcript_impact_coords ON chr1_snps (transcript_id, impact, chr, pos); CREATE INDEX idx_effects_join ON chr1_snps_effects (transcript_id); ANALYZE; SQL ``` Please repeat `Create tables` and `Loading TSV data into SQLite and indexing` for chromosomes 2 to 5. ### Example queries Select all variants found in AT4G40011.1: ```sql SELECT chr, pos, strain, ref, alt, type, impact FROM chr1_snps WHERE transcript_id = "AT1G30080.1" ORDER BY impact, chr, pos; ``` Select all variants for all strains found at position 10553167: ```sql select * from chr1_snps where pos=10553167 and chr=1; ``` Select all SNPs on chromosome 1 in the interval 44,000 to 45,000 with `impact = 'HIGH'`, together with their associated effect annotations: ```sql SELECT v.chr, v.pos, v.strain, v.ref, v.alt, v.type, v.impact, e.functional_class, e.codon_change, e.amino_acid_change, e.amino_acid_length FROM chr1_snps AS v LEFT JOIN chr1_snps_effects AS e ON v.chr = e.chr AND v.pos = e.pos AND v.strain = e.strain AND v.type = e.type AND v.transcript_id = e.transcript_id WHERE v.chr = 1 AND v.pos BETWEEN 44000 AND 45000 AND v.impact = 'HIGH' ORDER BY v.pos, v.strain; ``` And the same for gene id AT4G40011.1 and accession 9574 ```sql SELECT v.chr, v.pos, v.strain, v.ref, v.alt, v.type, v.impact, e.functional_class, e.codon_change, e.amino_acid_change, e.amino_acid_length FROM chr1_snps AS v LEFT JOIN chr1_snps_effects AS e ON v.transcript_id = e.transcript_id AND v.chr = e.chr AND v.pos = e.pos AND v.strain = e.strain WHERE v.transcript_id = "AT1G30080.1" AND v.impact = 'HIGH' AND v.strain = '9871' ORDER BY v.pos, v.strain; ``` ## Using tabix for range queries For coordinate-based lookups, the chromosome-partitioned TSV files can also be queried with `tabix`. This approach is useful when you want fast genomic interval retrieval without first loading the data into a relational database. The files must be sorted by chromosome and position and compressed with `bgzip`, after which a `tabix` index can be built using column 1 as the sequence name and column 2 as both the start and end coordinate. `tabix` is best suited to genomic range queries such as `chr:start-end`. It does not provide general secondary indexing for fields such as `transcript_id`, but its output can be piped into standard shell tools such as `awk` or `grep` for additional filtering. In the layout used here, `chr` is column 1, `pos` is column 2, and `strain` (the accession ID) is column 3. ### Preparing the index ```bash # bgzip and sort zcat 1001_snpeff_3.1_chr1_snps.tsv.gz | sort -k1,1 -k2,2n | bgzip > 1001_snpeff_3.1_chr1_sorted_snps.tsv.gz # Build the tabix index: seq col = 1, begin col = 2, end col = 2 tabix -s 1 -b 2 -e 2 1001_snpeff_3.1_chr1_sorted_snps.tsv.gz ``` ### Example queries ```bash # Query all variants in the range chr1:44,000-45,000 tabix 1001_snpeff_3.1_chr1_sorted_snps.tsv.gz 1:44000-45000 \ | awk -F'\t' '$3 == 9133' ``` ```bash # Query variants with impact = HIGH in the range chr1:44,000-45,000chr1:44,000-45,000 tabix 1001_snpeff_3.1_chr1_sorted_snps.tsv.gz 1:44000-45000 \ | awk -F'\t' '$7 == "HIGH"' ``` ```bash # Query variants with impact = HIGH in the range chr1:44,000-45,000 and keep only accession 6909 tabix 1001_snpeff_3.1_chr1_sorted_snps.tsv.gz 1:44000-45000 \ | awk -F'\t' '$3 == 9133 && $7 == "HIGH"' ``` ### References - **1001 Genomes Consortium.** (2016). 1,135 Genomes Reveal the Global Pattern of Polymorphism in *A. thaliana*. *Cell*, 166(2), 481-491. https://doi.org/10.1016/j.cell.2016.05.063 - **1001 Genomes Consortium.** (2017). *Polymorph 1001*. https://tools.1001genomes.org/polymorph/ (Accessed: March 2026). - **SnpEff:** Cingolani, P., et al. (2012). A program for annotating and predicting the effects of single nucleotide polymorphisms, SnpEff. *Fly*, 6(2), 80-92. - **SQLite:** Hipp, R. D. (2020). *SQLite* (Version 3.31.1). https://www.sqlite.org/index.html ### Technical Details & Provenance | Component | Specification | | -------------------- | ------------------------------------------------------ | | **Reference Genome** | *A. thaliana* TAIR10 | | **Annotation Tool** | SnpEff v4.0e (build 2014-09-13) | | **SnpEff Database** | `tair10` | | **Accessions** | 1,135 natural accessions from the 1001 Genomes Project |