# Read DBNSFP file **URL:** \ **Category:** Hail Query & hailctl\ **Created:** [November 29, 2019, 4:23pm UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192 "2019-11-29T16:23:32Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![thedam](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/thedam/32/342_2.png) [@thedam](https://discuss.hail.is/u/thedam)\ **Post date:** [November 29, 2019, 4:23pm UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/1 "2019-11-29T16:23:32Z")
Hey, I’m totally new to Hail, but I hope it does what I need. I’ve downloaded DBNSFP and just for test I’ve executed: `table = hl.import_table('/DATABASES/dbNSFP40/dbNSFP4.0a_variant.chr9', missing=".", delimiter='\t', impute=True)` then: `table.show()` ``` at is.hail.utils.richUtils.RichIterable.foreachBetween(RichIterable.scala:12) at is.hail.expr.ir.Pretty$.is$hail$expr$ir$Pretty$$pretty$1(Pretty.scala:408) at is.hail.expr.ir.Pretty$$anonfun$is$hail$expr$ir$Pretty$$pretty$1$6.apply(Pretty.scala:408) at is.hail.expr.ir.Pretty$$anonfun$is$hail$expr$ir$Pretty$$pretty$1$6.apply(Pretty.scala:408) at is.hail.utils.richUtils.RichIterator$.foreachBetween$extension(RichIterator.scala:35) at is.hail.utils.richUtils.RichIterable.foreachBetween(RichIterable.scala:12) at is.hail.expr.ir.Pretty$.is$hail$expr$ir$Pretty$$pretty$1(Pretty.scala:408) at is.hail.expr.ir.Pretty$$anonfun$is$hail$expr$ir$Pretty$$pretty$1$6.apply(Pretty.scala:408) at is.hail.expr.ir.Pretty$$anonfun$is$hail$expr$ir$Pretty$$pretty$1$6.apply(Pretty.scala:408) at is.hail.utils.richUtils.RichIterator$.foreachBetween$extension(RichIterator.scala:35) Hail version: 0.2.28-61941242c15d Error summary: StackOverflowError: null ``` what is wrong? ``` In [62]: table.count() [Stage 42:=====================================================>(207 + 3) / 210]Out[62]: 3388601 ``` ps. table.count() took like 10 sek, it’s too slow ☹ ---
**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:** [November 29, 2019, 10:14pm UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/2 "2019-11-29T22:14:08Z")
Ah, sorry about this. A few questions: 1. is this the full pipeline that fails? import/show? 2. count() is not instant because Hail executes the import to do the count. In order to process datasets too large to fit in memory, Hail streams through data, meaning that it lazily executes pipelines, possibly importing data from disk multiple times (if you count and show, for instance). How large is the DBNSFP data, and what is its schema? ---
**Author:** ![thedam](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/thedam/32/342_2.png) [@thedam](https://discuss.hail.is/u/thedam)\ **Post date:** [November 30, 2019, 4:19pm UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/3 "2019-11-30T16:19:36Z")
Hi, 1. yes, it’s all what you need to provoke this error 2. [dbNSFP](https://sites.google.com/site/jpopgen/dbNSFP) is just a big .tsv file. For example a relatively small chromosome 9 file has 3388602 rows x 376 cols. you can check it easily by yourself: bash: > ``` > wget ftp://dbnsfp:dbnsfp@dbnsfp.softgenetics.com/dbNSFP4.0a.zip > unzip dbNSFP4.0a.zip > gunzip dbNSFP4.0a_variant.chr9.gz > > ``` then in python: > ``` > import hail as hl > hl.init() > table = hl.import_table('dbNSFP4.0a_variant.chr9', missing=".", delimiter='\t', impute=True) > table.show() > > ``` ps. My server has 125GB RAM ---
**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:** [November 30, 2019, 6:02pm UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/4 "2019-11-30T18:02:14Z")
OK, will try to replicate! I don’t think this is related to the file size though ---
**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:** [November 30, 2019, 6:59pm UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/5 "2019-11-30T18:59:20Z")
This is a large download – would you mind attaching the first 10 lines of the file (as gz is fine) here? I think I should be able to replicate with that. ---
**Author:** ![thedam](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/thedam/32/342_2.png) [@thedam](https://discuss.hail.is/u/thedam)\ **Post date:** [December 1, 2019, 9:56am UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/6 "2019-12-01T09:56:03Z")
yeah, main zip has 25GB 🙂 here is the head: [chr9\_head.txt](https://discuss.hail.is/uploads/short-url/jYCf2l4Bgr5QGvAJryWh58M6MsY.txt) (132.5 KB) ---
**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:** [December 2, 2019, 12:21am UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/7 "2019-12-02T00:21:05Z")
Thanks. I can totally replicate this – I think probably show() will fail for any table of this width. We’ll fix that, sorry! ---
**Author:** ![thedam](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/thedam/32/342_2.png) [@thedam](https://discuss.hail.is/u/thedam)\ **Post date:** [December 2, 2019, 10:15am UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/8 "2019-12-02T10:15:32Z")
hmm as I understood Gnomad has much more columns, so it is strange that this error was not discovered before 🙂 ps. a question a bit out of topic: currently I use Sqlite3 database: I have one table with ~100 mln rows and 6 columns and the other table with ~11mln rows and ~400 columns They contains all annotations I need. The indexed key column is a coord column “`chr_start_ref_alt`” (`1_123133_A_G`). Querying this tables takes no time, however I’m worried about future performance when the tables will grow. I hoped to store such data in Hail, but what I see for now (and I’m veery new in Hail) that querying hail is slower:( for example this takes 0.001s in sqlite3: `"select * from anns_table where coord = "X_107869475_G_A"` # table has 11 mln rows and this take ~0.9s: `mt.entry.take(1)` ## this “mt” is created from VCF with 120k variatns… Seems like hail is slower ☹ ---
**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:** [December 2, 2019, 12:34pm UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/9 "2019-12-02T12:34:39Z")
Hail definitely will have higher latency for single-row queries than sqlite. If you’re building an application that executes single row queries in response to user input, for example, then Hail isn’t the right choice of backend. Hail is designed for efficient batch processing, and is probably a good choice for doing ETL before dumping data into a low-latency system like sqlite or elastic search. What was the full Hail pipeline before the `entry.take(1)`? Was it just `read_matrix_table` from disk? I’d like to see this query take 10-100 ms, but there’s not an obvious immediate path to 1ms. ---
**Author:** ![thedam](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/thedam/32/342_2.png) [@thedam](https://discuss.hail.is/u/thedam)\ **Post date:** [December 2, 2019, 1:44pm UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/10 "2019-12-02T13:44:47Z")
well, I don’t make any “advanced tests”, just “feeling” the data. SQLite3 dabase: ``` sqlite> select count(*) from patients_regions ; count(*) 108642855 Run Time: real 1.096 user 0.183413 sys 0.910287 ``` so my sqlite3 table has 108642855 rows. now take some data from some patient from the bottom of the table: ``` sqlite> select * from patients_regions where patient_id = 1087 limit 1; 108504348|1087|1|201|44|288|25|19|1|0.113|0.003 **Run Time: real 0.000 user 0.000218 sys 0.000256** ``` **it took ~0.000 sec.** now some Hail experiments: I load vcf with 116656 variants: ``` hl.import_vcf(some_final.vcf').write('final.mt', overwrite=True) mt = hl.read_matrix_table('final.mt') ``` then I do: ``` start = time.time() mt.row_key.show(1) end = time.time() print(end - start) ``` I get: ``` +---------------+------------+ | locus | alleles | +---------------+------------+ | locus | array | +---------------+------------+ | 1:13116 | ["T","G"] | +---------------+------------+ showing top 1 row 0.5431049156188965 ``` some other times: 0.24679160118103027 0.2239077091217041 so it’s slower in hail to take some first record from 100k records, than for Sqlite3 to take some last record from 100M records. (I actually don’t know yet how to filter n-th record from hail matrix table, but I suspect, that taking n-th record from 100M hail table will be slower than taking it from Sqlite3) 🙂 (yes, I’m building an app that display single row queries in response to the user input, so your clarifications were very helpful, thanks!) ---
**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:** [December 2, 2019, 3:14pm UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/11 "2019-12-02T15:14:07Z")
You’re comparing apples and oranges [1], but even if we fixed the experiment to properly compare sqlite3 and Hail, sqlite3 will outperform Hail for this operation. sqlite3 is designed for excellent single machine performance. Hail is designed to (not so excellently) use more than one machine. This design has a cost. * * * [1] Your schema appears to use a single row for each variant-sample pair whereas Hail uses a single row for all the samples in one variant. When Hail shows one row, Hail is showing the equivalent of many rows of your SQL table. ---
**Author:** ![thedam](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/thedam/32/342_2.png) [@thedam](https://discuss.hail.is/u/thedam)\ **Post date:** [December 2, 2019, 3:54pm UTC](https://discuss.hail.is/t/read-dbnsfp-file/1192/12 "2019-12-02T15:54:09Z")
Yeah, I know I compare wrong things, but I see that simple Hail operation already takes some time. I just want to create some robust data source that I could quickly query and eventually perform some simple operations. Gnomad Genomes v2.1.1 has 261942336 single variants, for every variant I can generate ~400 annotations, so having Sql table of size 261942336x400 would be problematic.