Community Articles
Find and share helpful community-sourced technical articles
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.
Labels (1)
Super Guru

Ingesting Blockchain data from btc.com and blockchain.com(.info).

Like everything in Apache NiFi, this is trivial easy to ingest all these different feeds, process them, route them and store them for SQL access.

78592-blocksfortodayflow.png

API:

blockchain.com

https://blockchain.info/latestblock

https://api.blockchain.info/charts/transactions-per-second?timespan=5weeks&rollingAverage=8hours&for...

https://blockchain.info/blocks/BTC.com?format=json

https://api.blockchain.info/pools?timespan=5days

https://api.blockchain.info/stats

https://blockchain.info/ticker

https://blockchain.info/tobtc?currency=USD&value=10000

Blocks For Today

https://chain.api.btc.com/v3/block/date/${now():format('yyyyMMdd')}

78600-blocksfortodayrestcall.png

78601-blocksfortodayschema.png

btc.com

https://chain.api.btc.com/v3/block/latest

https://chain.api.btc.com/v3/tx/unconfirmed

78595-blockchainstats.png

78596-convertbtctousd.png

78597-splitourbitcoinjsonrecords.png

78598-onlyshowdatawithtransactions.png

78599-bitcoinmergeavro.png

Blockchain Stats Call

{
  "timestamp": 1.528924637E12,
  "market_price_usd": 6284.846,
  "hash_rate": 3.4875735626794174E10,
  "total_fees_btc": 3517243180,
  "n_btc_mined": 177500000000,
  "n_tx": 208694,
  "n_blocks_mined": 142,
  "minutes_between_blocks": 9.5603,
  "totalbc": 1709147500000000,
  "n_blocks_total": 527318,
  "estimated_transaction_volume_usd": 9.726263100037038E8,
  "blocks_size": 130612249,
  "miners_revenue_usd": 1.13766549673085E7,
  "nextretarget": 528191,
  "difficulty": 4940704885521,
  "estimated_btc_sent": 15475738148615,
  "miners_revenue_btc": 1810,
  "total_btc_sent": 154234767570645,
  "trade_volume_btc": 100767.92920045,
  "trade_volume_usd": 6.333109167637314E8
}

Deeper Use Case

Today's Blocks

Apache Avro Schema in JSON Format Stored in Hortonworks Schema Registry

{
 "type": "record",
 "name": "blocksfortoday",
 "fields": [
  {
   "name": "height",
   "type": "int",
   "doc": "Type inferred from '527784'"
  },
  {
   "name": "version",
   "type": "int",
   "doc": "Type inferred from '536870912'"
  },
  {
   "name": "mrkl_root",
   "type": "string",
   "doc": "Type inferred from '\"c8f658ad595854f4c8c510b672447e838a2746e8724bfb26d0d127e5a4421385\"'"
  },
  {
   "name": "timestamp",
   "type": "int",
   "doc": "Type inferred from '1529180826'"
  },
  {
   "name": "bits",
   "type": "int",
   "doc": "Type inferred from '389609537'"
  },
  {
   "name": "nonce",
   "type": "int",
   "doc": "Type inferred from '236046944'"
  },
  {
   "name": "hash",
   "type": "string",
   "doc": "Type inferred from '\"00000000000000000017ca1d74bdee575dd48d4b3513eea2f7e06b313883d73d\"'"
  },
  {
   "name": "prev_block_hash",
   "type": "string",
   "doc": "Type inferred from '\"00000000000000000000acf6259fffe63d36623c324f756faaf995a9e2896b87\"'"
  },
  {
   "name": "next_block_hash",
   "type": "string",
   "doc": "Type inferred from '\"0000000000000000000000000000000000000000000000000000000000000000\"'"
  },
  {
   "name": "size",
   "type": "int",
   "doc": "Type inferred from '189026'"
  },
  {
   "name": "pool_difficulty",
   "type": "long",
   "doc": "Type inferred from '11831716619811'"
  },
  {
   "name": "difficulty",
   "type": "double",
   "doc": "Type inferred from '4.940704885521827E12'"
  },
  {
   "name": "tx_count",
   "type": "int",
   "doc": "Type inferred from '486'"
  },
  {
   "name": "reward_block",
   "type": "int",
   "doc": "Type inferred from '1250000000'"
  },
  {
   "name": "reward_fees",
   "type": "int",
   "doc": "Type inferred from '15691427'"
  },
  {
   "name": "created_at",
   "type": "int",
   "doc": "Type inferred from '1529180835'"
  },
  {
   "name": "confirmations",
   "type": "int",
   "doc": "Type inferred from '1'"
  },
  {
   "name": "is_orphan",
   "type": "boolean",
   "doc": "Type inferred from 'false'"
  },
  {
   "name": "curr_max_timestamp",
   "type": "int",
   "doc": "Type inferred from '1529180826'"
  },
  {
   "name": "is_sw_block",
   "type": "boolean",
   "doc": "Type inferred from 'true'"
  },
  {
   "name": "stripped_size",
   "type": "int",
   "doc": "Type inferred from '153817'"
  },
  {
   "name": "weight",
   "type": "int",
   "doc": "Type inferred from '650477'"
  },
  {
   "name": "extras",
   "type": {
    "type": "record",
    "name": "extras",
    "fields": [
     {
      "name": "pool_name",
      "type": "string",
      "doc": "Type inferred from '\"BTC.com\"'"
     },
     {
      "name": "pool_link",
      "type": "string",
      "doc": "Type inferred from '\"https://pool.btc.com\"'"
     }
    ]
   },
   "doc": "Type inferred from '{\"pool_name\":\"BTC.com\",\"pool_link\":\"https://pool.btc.com\"}'"
  }
 ]
}

QueryRecord Query

SELECT * FROM FLOWFILE WHERE CAST(tx_count AS INT) > 0

Create an Apache Hive Table

CREATE EXTERNAL TABLE IF NOT EXISTS blocksfortoday1 (height INT, version INT, mrkl_root STRING, timestamp INT, bits INT, nonce INT, hash STRING, prev_block_hash STRING, next_block_hash STRING, size INT, pool_difficulty BIGINT, difficulty DOUBLE, tx_count INT, reward_block INT, reward_fees INT, created_at INT, confirmations INT, is_orphan BOOLEAN, curr_max_timestamp INT, is_sw_block BOOLEAN, stripped_size INT, weight INT, extras STRUCT<pool_name:STRING, pool_link:STRING>)
STORED AS ORC
LOCATION '/blocksfortoday1'

Example Apache Hive Query Run in Apache Zeppelin

select * from blocksfortoday1 where  CAST(tx_count as INT) > 500 order by created_at desc

78593-blocksfortodayzep.png


References:

1,137 Views
Comments
New Contributor

Great post. Another solution may be to make use of Google Blockchain public dataset and Nifi:
http://datamater.io/2018/07/19/fetching-bitcoin-transactions-with-apache-nifi/

Don't have an account?
Coming from Hortonworks? Activate your account here
Version history
Revision #:
2 of 2
Last update:
‎08-17-2019 07:05 AM
Updated by:
 
Contributors
Top Kudoed Authors