-
Notifications
You must be signed in to change notification settings - Fork 48
Using Hive to analyze Bitcoin Blockchain data
This is a Hive SQL application demonstrating some of the capabilities of the hadoopcryptoledger library. It enables transparent access to Bitcoin Blockchain data via Hive SQL. It can be linked with any other table in any other format in Hive, such as ORC or Parquet. It has successfully been tested with the Cloudera Quickstart VM 5.5 and HDP 2.5, but other Hadoop distributions should work equally well.
See here how to fetch Bitcoin blockchain data.
After it has been copied you are ready to use the example.
Instructions to deploy the Hive Serde for the Bitcoin Blockchain.
These are some examples. We assumed that you deployed the Hive Serde and created in the database 'blockchains' the table 'BitcoinBlockchain':
CREATE EXTERNAL TABLE BitcoinBlockchain ROW FORMAT SERDE 'org.zuinnote.hadoop.bitcoin.hive.serde.BitcoinBlockSerde' STORED AS INPUTFORMAT 'org.zuinnote.hadoop.bitcoin.format.mapred.BitcoinBlockFileInputFormat' OUTPUTFORMAT 'org.apache.hadoop.mapred.lib.NullOutputFormat' LOCATION '/user/bitcoin/input' TBLPROPERTIES("hadoopcryptoledger.bitcoinblockinputformat.filter.magic"="F9BEB4D9");
The following example counts the number of blocks:
select count(*) from BitcoinBlockchain;
The following example counts the number of transactions:
select count(*) from BitcoinBlockchain LATERAL VIEW explode(transactions) exploded_transactions;
The following example calculates the total output of all Bitcoin transactions:
select SUM(expout.value) FROM (select * from BitcoinBlockchain LATERAL VIEW explode(transactions) exploded_transactions as exptran) transaction_table LATERAL VIEW explode(exptran.listofoutputs) exploded_outputs as expout;
Find more examples here: https://github.com/ZuInnoTe/hadoopcryptoledger/tree/master/examples/hive-bitcoin
Find more information here: https://snippetessay.wordpress.com/2016/04/28/hive-bitcoin-analytics-on-blockchain-data-with-sql/
Understanding the structure of Bitcoin data:
Blocks: https://en.bitcoin.it/wiki/Block
Transactions: https://en.bitcoin.it/wiki/Transactions