# Export variants to a tsv file **URL:** \ **Category:** Hail Query & hailctl\ **Created:** [June 16, 2022, 11:16am UTC](https://discuss.hail.is/t/export-variants-to-a-tsv-file/2711 "2022-06-16T11:16:09Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![shuang](https://avatars.discourse-cdn.com/v4/letter/s/f08c70/32.png) [@shuang](https://discuss.hail.is/u/shuang)\ **Post date:** [June 16, 2022, 11:16am UTC](https://discuss.hail.is/t/export-variants-to-a-tsv-file/2711/1 "2022-06-16T11:16:09Z")
Hi, I want to export all variants from a MT to a txt file. (1:100:A:C is best, but I can accept 1:100 [“A”,“C”]) I tried this but failed: ```auto mt = hl.read_matrix_table(mt_path) # select row fields: locus and alleles mt2 = mt.select_rows(mt.locus, mt.alleles) # by select rows to trans mt to a table rows_table = mt2.rows() mt2.export('gs://path/mt2.tsv') ``` `hail.expr.expressions.base_expression.ExpressionException: 'MatrixTable.select_rows': cannot overwrite key field 'locus' with annotate, select or drop; use key_by to modify keys.` I also tried to export info field or filters field `mt.info.export('gs://path/info.tsv')`, by that I also will get locus and alleles columns but I find out the sum of variants given by exported info tsv or filters tsv is not as the same as the `hl.summarize_variants(mt)` func gave. by` mt.info.export('info.tsv')`, I count the tsv, I find 461410 (w/ header line) by `hl.summarize_variants(mt)`, I should have: Number of variants: 463841. They use the same MT input, should give the same variants. I feel so confused and I only have biallelic variants. Any help? thanks a lot. ---
**Author:** ![tpoterba](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/tpoterba/32/61_2.png) [@tpoterba](https://discuss.hail.is/u/tpoterba)\ **Post date:** [June 16, 2022, 1:18pm UTC](https://discuss.hail.is/t/export-variants-to-a-tsv-file/2711/2 "2022-06-16T13:18:03Z")
The select\_rows is throwing an error here because key fields are implicitly included in any selection, so you’re selecting them twice with this. The error message could definitely be improved. You can get the chr:pos:ref:alt with `hl.variant_str`. Let’s try: ```auto ht = mt.rows() ht = ht.key_by() # drop key ht = ht.select(variant=hl.variant_str(ht.locus, ht.alleles)) ht.export(...) ``` ---
**Author:** ![shuang](https://avatars.discourse-cdn.com/v4/letter/s/f08c70/32.png) [@shuang](https://discuss.hail.is/u/shuang)\ **Post date:** [June 16, 2022, 2:10pm UTC](https://discuss.hail.is/t/export-variants-to-a-tsv-file/2711/3 "2022-06-16T14:10:07Z")
Hi @tpoterba thanks a lot for your answer! it works great. I get the variant list as chr:pos:ref:alt. but I find this wired dismatch: code I ran: ```auto hl.summarize_variants(mt) ht = mt.rows() ht = ht.key_by() # drop key ht = ht.select(variant=hl.variant_str(ht.locus, ht.alleles)) ht.export('gs://path/out.tsv') ``` from `hl.summarize_variants(mt)` I get: ```auto Number of variants: 463841 ============================== Alleles per variant ------------------- 2 alleles: 463841 variants ============================== ``` but from the exported tsv I count it with wc -l, I only get: #460889 w/header line (I only have biallelic SNVs in my MT as showed in the `hl.summarize_variants(mt)` report) Why there are some variants disappeared from the exported tsv? ---
**Author:** ![danking](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/danking/32/43_2.png) [@danking](https://discuss.hail.is/u/danking)\ **Post date:** [June 16, 2022, 3:58pm UTC](https://discuss.hail.is/t/export-variants-to-a-tsv-file/2711/4 "2022-06-16T15:58:37Z")
@shuang , Are you using Spark Speculation? Spark Speculation interacts badly with HDFS to sometimes lose files. You can disable it by specifying `spark.speculation` as `false` in your Spark Conf. If you’re using `hl.init`, try this: ```auto import hail as hl hl.init(spark_conf={'spark.speculation': 'false'}) ``` EDIT: fix syntax error ---
**Author:** ![shuang](https://avatars.discourse-cdn.com/v4/letter/s/f08c70/32.png) [@shuang](https://discuss.hail.is/u/shuang)\ **Post date:** [June 17, 2022, 11:22am UTC](https://discuss.hail.is/t/export-variants-to-a-tsv-file/2711/6 "2022-06-17T11:22:04Z")
Hi @danking, thanks for your reply. I tried it (made slight change in code `hl.init(spark_conf={'spark.speculation': 'false'}, default_reference='GRCh38')` unless it report type error) and I get more variants in tsv but still lost some variants. code I ran: ```auto import hail as hl hl.init(spark_conf={'spark.speculation': 'false'}, default_reference='GRCh38') mt_path = 'gs://path/autosomal.mt' mt = hl.read_matrix_table(mt_path) hl.summarize_variants(mt) ht = mt.rows() ht = ht.key_by() # drop key ht = ht.select(variant=hl.variant_str(ht.locus, ht.alleles)) ht.export('gs://path/autosomal.tsv') ``` I get 463795 variants, but according `hl.summarize_variants(mt)` I should have 463841 I ran with GCP dataproc and I use this to generate hail cluster: ```auto hailctl dataproc start hail \ --master-machine-type n1-highmem-64 \ --master-boot-disk-size 2000 \ --num-workers 2 \ --num-preemptible-workers 10 \ --preemptible-worker-boot-disk-size 300 \ --worker-machine-type n1-highmem-64 \ --worker-boot-disk-size 300 \ --num-worker-local-ssds 1 \ --region europe-west1 \ --zone europe-west1-b \ --project myproject \ --max-idle pt5m \ --scopes cloud-platform ``` ---
**Author:** ![danking](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/danking/32/43_2.png) [@danking](https://discuss.hail.is/u/danking)\ **Post date:** [June 17, 2022, 2:13pm UTC](https://discuss.hail.is/t/export-variants-to-a-tsv-file/2711/7 "2022-06-17T14:13:23Z")
ok, I think you’re hitting a bug for which we’re releasing a fix next week. I think you’re encountering preemptible machine failure interacting badly with GCS. ---
**Author:** ![shuang](https://avatars.discourse-cdn.com/v4/letter/s/f08c70/32.png) [@shuang](https://discuss.hail.is/u/shuang)\ **Post date:** [June 18, 2022, 6:35pm UTC](https://discuss.hail.is/t/export-variants-to-a-tsv-file/2711/8 "2022-06-18T18:35:57Z")
Hi @danking thanks a lot for your reply. Btw, according to your reply, I tried with only 2 normal nodes, w/o secondary nodes, I get almost all vars but still lost 4 vars in the exported tsv, compared with its MT. I will waiting for the new release. Thanks a lot for your help!