01_extract_blocks.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extracting Blockdata\n",
    "This script only make sense if you have a running Bitcoin Fullnode. In order to have access to the API, you need to somehow specify three specific parameters:\n",
    "* The User with which to connect to the Fullnode\n",
    "* The Password for that user\n",
    "* The name or IP of the host where the fullnode is running\n",
    "* the Port (ussually 8332)\n",
    "\n",
    "By default they will be loaded from environment-variables but you can also hardcode them if you want."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "rpc_user = os.getenv('BTC_RPC_USER','bitcoin')\n",
    "# Or hardcode by uncommenting here:\n",
    "# rpc_user=\"bitcoin\"\n",
    "rpc_password = os.getenv('BTC_RPC_PASSWORD')\n",
    "# Or hardcode by uncommenting here:\n",
    "# rpc_password=\"mySecretPassword\"\n",
    "rpc_host = os.getenv('BTC_RPC_HOST','localhost')\n",
    "# Or hardcode by uncommenting here:\n",
    "# rpc_host=\"mybtc-host.local\"\n",
    "rpc_port = os.getenv('BTC_RPC_PORT','8332')\n",
    "# Or hardcode by uncommenting here:\n",
    "# rpc_port=\"1234\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the connectivity-parameter above are not correct, you'll get an error below. There are more possibilities for issues. Especially if BTC_RPC_HOST is NOT localhost (or 127.0.0.1).\n",
    "Some resources to read for troubleshooting are:\n",
    "* https://bitcoin.stackexchange.com/questions/88947/cant-reach-remote-bitcoin-rpc-server\n",
    "* https://bitcoin.stackexchange.com/questions/91274/bitcoin-json-rpc-not-working-on-remote-ip\n",
    "\n",
    "Some specific issues:\n",
    "* _\"Name or service not known\"_ -> doublecheck rpc_host. Don't add any http(s) or anything like that\n",
    "* _\"ModuleNotFoundError: No module named 'cryptoadvance'\"_ --> Check to properly activate the environment\n",
    "* _\"Failed to establish a new connection: [Errno 111] Connection refused'\"_ --> are you sure your btc-fullnode is running on rpc_host? If it's a remote-server, maybe there is a firewall in between?\n",
    "* _\"Server responded with error code 401:\"_\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'chain': 'main',\n",
       " 'blocks': 630128,\n",
       " 'headers': 630128,\n",
       " 'bestblockhash': '0000000000000000000ebe91af6cc7f180b0df8a9b48793f723c57f1d218934f',\n",
       " 'difficulty': 16104807485529.38,\n",
       " 'mediantime': 1589298250,\n",
       " 'verificationprogress': 0.9999987399113666,\n",
       " 'initialblockdownload': False,\n",
       " 'chainwork': '00000000000000000000000000000000000000000f6956303ef81a92ec6f8bce',\n",
       " 'size_on_disk': 314522173081,\n",
       " 'pruned': False,\n",
       " 'softforks': [{'id': 'bip34', 'version': 2, 'reject': {'status': True}},\n",
       "  {'id': 'bip66', 'version': 3, 'reject': {'status': True}},\n",
       "  {'id': 'bip65', 'version': 4, 'reject': {'status': True}}],\n",
       " 'bip9_softforks': {'csv': {'status': 'active',\n",
       "   'startTime': 1462060800,\n",
       "   'timeout': 1493596800,\n",
       "   'since': 419328},\n",
       "  'segwit': {'status': 'active',\n",
       "   'startTime': 1479168000,\n",
       "   'timeout': 1510704000,\n",
       "   'since': 481824}},\n",
       " 'warnings': ''}"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from cryptoadvance.specter.rpc import BitcoinRPC, RpcError\n",
    "import numpy as np\n",
    "cli = BitcoinRPC(rpc_user, rpc_password, rpc_host, rpc_port)\n",
    "cli.getblockchaininfo()\n",
    "# To investigate the Bitcoin API, here are some great resources:\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The response is encoded in [json](https://en.wikipedia.org/wiki/JSON) (as all the results of bitcoin-rpc-calls) we're looking for is something like:\n",
    "```\n",
    "{'chain': 'main',\n",
    " 'blocks': 629212,\n",
    "...\n",
    "```\n",
    "We can beautify the output a bit like this in order to make it more readable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"chain\": \"main\",\n",
      "  \"blocks\": 630128,\n",
      "  \"headers\": 630128,\n",
      "  \"bestblockhash\": \"0000000000000000000ebe91af6cc7f180b0df8a9b48793f723c57f1d218934f\",\n",
      "  \"difficulty\": 16104807485529.38,\n",
      "  \"mediantime\": 1589298250,\n",
      "  \"verificationprogress\": 0.9999987051023334,\n",
      "  \"initialblockdownload\": false,\n",
      "  \"chainwork\": \"00000000000000000000000000000000000000000f6956303ef81a92ec6f8bce\",\n",
      "  \"size_on_disk\": 314522173081,\n",
      "  \"pruned\": false,\n",
      "  \"softforks\": [\n",
      "    {\n",
      "      \"id\": \"bip34\",\n",
      "      \"version\": 2,\n",
      "      \"reject\": {\n",
      "        \"status\": true\n",
      "      }\n",
      "    },\n",
      "    {\n",
      "      \"id\": \"bip66\",\n",
      "      \"version\": 3,\n",
      "      \"reject\": {\n",
      "        \"status\": true\n",
      "      }\n",
      "    },\n",
      "    {\n",
      "      \"id\": \"bip65\",\n",
      "      \"version\": 4,\n",
      "      \"reject\": {\n",
      "        \"status\": true\n",
      "      }\n",
      "    }\n",
      "  ],\n",
      "  \"bip9_softforks\": {\n",
      "    \"csv\": {\n",
      "      \"status\": \"active\",\n",
      "      \"startTime\": 1462060800,\n",
      "      \"timeout\": 1493596800,\n",
      "      \"since\": 419328\n",
      "    },\n",
      "    \"segwit\": {\n",
      "      \"status\": \"active\",\n",
      "      \"startTime\": 1479168000,\n",
      "      \"timeout\": 1510704000,\n",
      "      \"since\": 481824\n",
      "    }\n",
      "  },\n",
      "  \"warnings\": \"\"\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "print(json.dumps(cli.getblockchaininfo(), indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is the result of the [getblockchaininfo-call](https://bitcoin.org/en/developer-reference#getblockchaininfo). In order to get more knowledge about the Bitcoin-API, especially these three resources might be helpful:\n",
    "* https://bitcoin.org/en/developer-reference#bitcoin-core-apis -  The reference-documentation. This is the truth by definition\n",
    "* https://chainquery.com/bitcoin-cli - This is awesome because you can play in a sandbox what all these calls do\n",
    "* https://github.com/ChristopherA/Learning-Bitcoin-from-the-Command-Line - This is more tutorial style\n",
    "\n",
    "Let's look into the result of the [getblock-call](https://bitcoin.org/en/developer-reference#getblock). It needs a hash as param and we're using the [getblockhash-call](https://bitcoin.org/en/developer-reference#getblockhash) to obtain the hash via the desired blockheight. Here is an example what gets returned from the 2nd block:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The whole json-result looks like this:\n",
      "{\n",
      "  \"hash\": \"000000006a625f06636b8bb6ac7b960a8d03705d1ace08b1a19da3fdcc99ddbd\",\n",
      "  \"confirmations\": 630127,\n",
      "  \"strippedsize\": 215,\n",
      "  \"size\": 215,\n",
      "  \"weight\": 860,\n",
      "  \"height\": 2,\n",
      "  \"version\": 1,\n",
      "  \"versionHex\": \"00000001\",\n",
      "  \"merkleroot\": \"9b0fc92260312ce44e74ef369f5c66bbb85848f2eddd5a7a1cde251e54ccfdd5\",\n",
      "  \"tx\": [\n",
      "    \"9b0fc92260312ce44e74ef369f5c66bbb85848f2eddd5a7a1cde251e54ccfdd5\"\n",
      "  ],\n",
      "  \"time\": 1231469744,\n",
      "  \"mediantime\": 1231469665,\n",
      "  \"nonce\": 1639830024,\n",
      "  \"bits\": \"1d00ffff\",\n",
      "  \"difficulty\": 1,\n",
      "  \"chainwork\": \"0000000000000000000000000000000000000000000000000000000300030003\",\n",
      "  \"nTx\": 1,\n",
      "  \"previousblockhash\": \"00000000839a8e6886ab5951d76f411475428afc90947ee320161bbf18eb6048\",\n",
      "  \"nextblockhash\": \"0000000082b5015589a3fdf2d4baff403e6f0be035a5d9742c1cae6295464449\"\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "print(\"The whole json-result looks like this:\")\n",
    "print(json.dumps(cli.getblock(cli.getblockhash(2)), indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So finally, we want to know the flow, the number of generated bitcoins per month. In order to know that we need to know the subsidy of each block and the date. So what we want is a flat-list in order to generate a csv which looks like this:\n",
    "```\n",
    "subsidy,date\n",
    "50,1231469744\n",
    "... one line per block ...\n",
    "```\n",
    "However, the first problem is that there is no subsidy in the data. So we need to calculate that ourself for each block. We know that it's halving every 210000 blocks. With a bit of googling (developers are lazy and you ALWAYS can find the solution to your problem somewhere on the internet) we found:\n",
    "https://gist.github.com/nelruk/d00d785f84e47288b0ab734d4aab5f49\n",
    "So this is how our function looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "the blocksubsidy of the 5th      block is: 50\n",
      "the blocksubsidy of the 210000th block is: 25.0\n",
      "the blocksubsidy of the 420000th block is: 12.5\n",
      "the blocksubsidy of the 630000th block is: 6.25\n"
     ]
    }
   ],
   "source": [
    "# subsidy is not part of the json. So we need to calclate it ourself\n",
    "def blocksubsidy(height):\n",
    "    ' see https://gist.github.com/nelruk/d00d785f84e47288b0ab734d4aab5f49 '\n",
    "    ' passes in a blockheight and returns the subsidy. E.g. blocksubsidy(5) = 50 '\n",
    "    period = int(height / 210000)\n",
    "    subsidy=50\n",
    "    for i in range(0,period):\n",
    "        subsidy = subsidy /2\n",
    "    # easier:\n",
    "    # subsidy / (2 ^ period)\n",
    "    return subsidy\n",
    "\n",
    "print(\"the blocksubsidy of the 5th      block is: {}\".format(blocksubsidy(5)))\n",
    "print(\"the blocksubsidy of the 210000th block is: {}\".format(blocksubsidy(210000)))\n",
    "print(\"the blocksubsidy of the 420000th block is: {}\".format(blocksubsidy(420000)))\n",
    "print(\"the blocksubsidy of the 630000th block is: {}\".format(blocksubsidy(630000)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Back to the csv:\n",
    "```\n",
    "subsidy,date\n",
    "50,1231469744\n",
    "... one line per block ...\n",
    "```\n",
    "The date might looks weird. I took it from the time-field above. It's a [Unix-timestamp](https://en.wikipedia.org/wiki/Unix_time) and we'll later convert it to a human-readable date. But we'll collect more then the minimum. Maybe it will be helpfull to later have ... the difficulty or ... you never know!\n",
    "\n",
    "So the one thing which is troubling is the \"tx\":[] field. It's a nested list with tx-IDs and we can't integrate that to our csv because csv-files are \"flat\", not \"nested\".\n",
    "We can explicitely get the keys (which later will get the header to our csv-file) and also the values (which will get the rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A List of the keys (later the headers):\n",
      "dict_keys(['hash', 'confirmations', 'strippedsize', 'size', 'weight', 'height', 'version', 'versionHex', 'merkleroot', 'tx', 'time', 'mediantime', 'nonce', 'bits', 'difficulty', 'chainwork', 'nTx', 'previousblockhash', 'nextblockhash'])\n",
      "----------------------------------------------------\n",
      "A List of the values (later the rows):\n",
      "['000000006a625f06636b8bb6ac7b960a8d03705d1ace08b1a19da3fdcc99ddbd', 630127, 215, 215, 860, 2, 1, '00000001', '9b0fc92260312ce44e74ef369f5c66bbb85848f2eddd5a7a1cde251e54ccfdd5', ['9b0fc92260312ce44e74ef369f5c66bbb85848f2eddd5a7a1cde251e54ccfdd5'], 1231469744, 1231469665, 1639830024, '1d00ffff', 1, '0000000000000000000000000000000000000000000000000000000300030003', 1, '00000000839a8e6886ab5951d76f411475428afc90947ee320161bbf18eb6048', '0000000082b5015589a3fdf2d4baff403e6f0be035a5d9742c1cae6295464449']\n"
     ]
    }
   ],
   "source": [
    "print(\"A List of the keys (later the headers):\")\n",
    "print(cli.getblock(cli.getblockhash(2)).keys())\n",
    "print(\"----------------------------------------------------\")\n",
    "print(\"A List of the values (later the rows):\")\n",
    "print(list(cli.getblock(cli.getblockhash(2)).values()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The thing which doesn't work though is the \"tx-column\" as this value is a list of tx-hashes. We're not interested in that, so let's remove it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['00000000839a8e6886ab5951d76f411475428afc90947ee320161bbf18eb6048',\n",
       "       '630128', '215', '215', '860', '1', '1', '00000001',\n",
       "       '0e3e2357e806b6cdb1f70b54c3a3a17b6714ee1f0e68bebb44a74b1efd512098',\n",
       "       '1231469665', '1231469665', '2573394689', '1d00ffff', '1',\n",
       "       '0000000000000000000000000000000000000000000000000000000200020002',\n",
       "       '1',\n",
       "       '000000000019d6689c085ae165831e934ff763ae46a2a6c172b3f1b60a8ce26f',\n",
       "       '000000006a625f06636b8bb6ac7b960a8d03705d1ace08b1a19da3fdcc99ddbd'],\n",
       "      dtype='<U64')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myblock = cli.getblock(cli.getblockhash(1))\n",
    "del myblock['tx']\n",
    "np.array(list(myblock.values()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That looks more like a row in a csv-file. Let's make two functions out of that. this term:\n",
    "```\n",
    "blk[\"subsidy\"] = blocksubsidy(height)\n",
    "```\n",
    "will call the subsidy-function and simply add the missing piece to the json-data. Then the tx-part will get removed. The block_as_list-function will return a simple array with the values we want in the csv."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleansed_block(height):\n",
    "    ' missing documentation '\n",
    "    blk = cli.getblock(cli.getblockhash(height))\n",
    "    blk[\"subsidy\"] = blocksubsidy(height)\n",
    "    del blk['tx']\n",
    "    return blk\n",
    "        \n",
    "def block_as_list(blk):\n",
    "        # when loading specify like this:\n",
    "        # df.columns(['height','subsidy','hash','strippedsize','size','weight','version','versionHex','merkleroot','time','mediantime','nonce','bits','difficulty','chainwork','nTx','previousblockhash','nextblockhash])\n",
    "        return [blk['height'],blk['subsidy'],blk['hash'],blk['strippedsize'],blk['size'],blk['weight'],blk['version'],blk['versionHex'],blk['merkleroot'],blk['time'],blk['mediantime'],blk['nonce'],blk['bits'],blk['difficulty'],blk['chainwork'],blk['nTx'],blk['previousblockhash'],blk['nextblockhash']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're almost done. As writing out all the blocks is taking so much time (10-12h) we're writing it in chunks. Specifying the beginning and end of each file is done in _first_block_ and _last_block_.\n",
    "\n",
    "In order to track progress, we're printing out a timestamp and the  blockheight every 144 blocks (roughly a day).\n",
    "The \"%\" is the rest of a division. So 144%144 == 0!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-05-06 04:33:46 629136\n",
      "2020-05-07 02:08:23 629280\n",
      "2020-05-08 02:02:18 629424\n",
      "2020-05-09 03:21:50 629568\n",
      "2020-05-10 03:32:51 629712\n",
      "2020-05-11 00:59:59 629856\n",
      "2020-05-11 21:23:43 630000\n",
      "created the csv in 123.0787 seconds\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "import time\n",
    "from datetime import datetime\n",
    "number_of_blocks = cli.getblockchaininfo()['blocks']\n",
    "first_block = 629122\n",
    "last_block =  number_of_blocks\n",
    "tic = time.perf_counter()\n",
    "with open(f\"data/blocks_{last_block}.csv\", 'w', newline='') as csvfile:\n",
    "    writer = csv.writer(csvfile, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL)\n",
    "    for i in range(first_block, last_block):\n",
    "        block = cleansed_block(i)\n",
    "        writer.writerow(block_as_list(block))\n",
    "        if i % 144 == 0 :\n",
    "            print(f\"{datetime.fromtimestamp(block['time'])} {block['height']}\")\n",
    "toc = time.perf_counter()\n",
    "print(f\"created the csv in {toc - tic:0.4f} seconds\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's a bit annoying to manually adjust first_block and last_block and run that script here so often but at least it keeps the code simple. Feel free to improve it!"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}