jamiew/helium-etl-queries

A collection of SQL views used to enrich data produced by a Helium blockchain-etl

helium-etl-queries

A collection of SQL queries used to enrich data produced by a Helium blockchain-etl

This repo is specifically trying to create and manage views that better surface the embedded JSON fields in a more managable form, e.g. turning transactions[type='state_channel_close'] => data_credits table, with properly typed columns

A live instance with these views is avaialble on the beta DeWi ETL instance, which runs Metabase:

https://etl.dewi.org

If you're interested in using it, please contact @jamiedubs/@wishplorer on Discord. There is auto signup available if using gmail.com email address with google signin.

Documentation available on: https://dbdocs.io/wishplorer/HeliumDewiETL

Setup

• setup a blockchain-etl instance – this might take a while
• load all the views from this repository:

dbname="etl"
for i in views/*; do psql $dbname < $i; done

Or load them one at a time etc.

TODO

• More views: challenges, consensus_groups, payments, split out reward-types, etc
• Get fancy and use dbt

Contributing

• Pull requests welcome
• Ask questions in #hips or #blockchain-dev on Discord

Contributors

• @~zav~
• @jamiedubs (@jamiew)
• @wishplorer
• @GregKarabinos

License

MIT

helium-etl-queries's Issues

Add indexes to transctions_payment

How is the Helium API doing efficient payment txn lookups? Our current setup is extremely slow

Specifically like to index payer and payee

Might require turning this into a materialized view and adding to the cronjobs, but I assume blockchain-http has some secrets

Merge this repo with `dewi-alliance/dewi-etl`

https://github.com/dewi-alliance/dewi-etl started as issue tracker for the server, but maybe merge together? Or use submodules/subtrees

It's a package deal at this point really

Same with https://github.com/jamiew/helium-etl-maintenance and some of @danksu's helper apps like go-etl-shenanigans

Switch to using dbt and dbt-metabase

Use dbt pipelines to handle transormations and view creation more reliably and sanely.

Then use dbt-metabase to sync the dbt data model w/ Metabase (!)

Will make it a lot easier to test, easier to automate, and make it easier for others to contribute new tables and views

Add witness_address index to challenge_receipts_parsed

Weird that transmitter has indexes on both address and name, but witness is only on name

OR make both of these only use address... shave some space off a large index

Name not unique so shouldn't really be an index at all

challenge_receipts_parsed does not work from scratch

Currently it expects a value for MAX(block) from challenge_receipts_parsed, so it only loads new data. But if there is no data at all, it loads nothing. Should add a conditional or similar