{ "cells": [ { "cell_type": "markdown", "metadata": { "collapsed": false }, "source": [ "# Big-R-Query\n", "\n", "https://cran.r-project.org/web/packages/bigrquery/README.html\n", "\n", "The bigrquery packages provides an R interface to Google BigQuery. It makes it easy to retrieve metadata about your projects, datasets, tables and jobs, and provides a convenient wrapper for working with bigquery from R." ] }, { "cell_type": "markdown", "metadata": { "collapsed": false }, "source": [ "**Here, we look for the lowest block IDs in the [Bitcoin](https://bitcoin.org) blockchain and want to know when they were.**" ] }, { "cell_type": "markdown", "metadata": { "collapsed": false }, "source": [ "**Note for CoCalc:** oauth 2.0 setup via `httr` requires a local browser (I don't know how to use this text based). So, run this in your local R instance and then upload the `.httr-oauth` file into your projects home directory." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "── Attaching packages ─────────────────────────────────────── tidyverse 1.2.1 ──\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "✔ ggplot2 2.2.1 ✔ purrr 0.2.4\n", "✔ tibble 1.4.2 ✔ dplyr 0.7.4\n", "✔ tidyr 0.8.0 ✔ stringr 1.2.0\n", "✔ readr 1.1.1 ✔ forcats 0.2.0\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──\n", "✖ dplyr::filter() masks stats::filter()\n", "✖ dplyr::lag() masks stats::lag()\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\n", "Attaching package: ‘lubridate’\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "The following object is masked from ‘package:base’:\n", "\n", " date\n", "\n" ] } ], "source": [ "library(tidyverse)\n", "library(lubridate)\n", "library(bigrquery)\n", "library(tibble)\n", "\n", "project <- \"....\" # put your project ID here" ] }, { "cell_type": "markdown", "metadata": { "collapsed": false }, "source": [ "## Asking BigQuery" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "41.0 megabytes processed\n" ] }, { "data": { "text/html": [ "\n", "\n", "\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\n", "
block_iddifficultyTargettimestamp
0000000000000000000000ab789f6d71d9642ae3f697975ccd00afcb98fe6bd2391203401 1522452348000
0000000000000000000000bb5b432a764ad6c7acf677dcd99161abfdf68e698e402691653 1513720654000
00000000000000000000011246f099d94f91628d71c9d75ad2f9a06e2beb7e92402804657 1489999494000
00000000000000000000013712632da34788a7b4ae2cd78f7982c7036610126b404031509 1438792320000
00000000000000000000021c043e439b5f4b632389b0062306bf2d4e0b657c7c391481763 1520517455000
000000000000000000000241d3121e74bbbc7a034666d6c6f4d46cac2dc2394a391481763 1520783660000
0000000000000000000002569beb610e7f806774fd0c650c415a78a087b9d979402713392 1508738156000
00000000000000000000026a36ce6423544d841d51f5b36ffccb63e4a4ce0b12402690497 1515091565000
000000000000000000000388a8c212badee4d31543b7afdca2d67e501853bb1a391129783 1523400616000
0000000000000000000003b4cc8365995e7567c7720b51e595ae54e9fe748622402717299 1507448823000
\n" ] }, "execution_count": 2, "metadata": { }, "output_type": "execute_result" } ], "source": [ "sql <- \"SELECT\n", " block_id,\n", " difficultyTarget,\n", " STRING(timestamp) AS timestamp\n", "FROM\n", " [bigquery-public-data:bitcoin_blockchain.blocks]\n", "ORDER BY\n", " block_id ASC\n", "LIMIT\n", " 10\"\n", "\n", "# execute the query and store the result\n", "lowest_blocks <- query_exec(sql, project = project)\n", "lowest_blocks" ] }, { "cell_type": "markdown", "metadata": { "collapsed": false }, "source": [ "## Converting the timestamp strings to ISO formatted datetimes" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\t\n", "\n", "
block_iddifficultyTargettimestamp
0000000000000000000000ab789f6d71d9642ae3f697975ccd00afcb98fe6bd2391203401 2018-03-30 23:25:48
0000000000000000000000bb5b432a764ad6c7acf677dcd99161abfdf68e698e402691653 2017-12-19 21:57:34
00000000000000000000011246f099d94f91628d71c9d75ad2f9a06e2beb7e92402804657 2017-03-20 08:44:54
00000000000000000000013712632da34788a7b4ae2cd78f7982c7036610126b404031509 2015-08-05 16:32:00
00000000000000000000021c043e439b5f4b632389b0062306bf2d4e0b657c7c391481763 2018-03-08 13:57:35
000000000000000000000241d3121e74bbbc7a034666d6c6f4d46cac2dc2394a391481763 2018-03-11 15:54:20
0000000000000000000002569beb610e7f806774fd0c650c415a78a087b9d979402713392 2017-10-23 05:55:56
00000000000000000000026a36ce6423544d841d51f5b36ffccb63e4a4ce0b12402690497 2018-01-04 18:46:05
000000000000000000000388a8c212badee4d31543b7afdca2d67e501853bb1a391129783 2018-04-10 22:50:16
0000000000000000000003b4cc8365995e7567c7720b51e595ae54e9fe748622402717299 2017-10-08 07:47:03
\n" ] }, "execution_count": 3, "metadata": { }, "output_type": "execute_result" } ], "source": [ "as_tibble(lowest_blocks) %>%\n", " mutate(timestamp = as.numeric(timestamp)) %>%\n", " mutate(timestamp = as_datetime(timestamp / 1000))" ] }, { "cell_type": "markdown", "metadata": { "collapsed": false }, "source": [ "## Look at row #4\n", "\n", "this tells someone was really lucky to find such a low block ID back then in 2015!" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "00000000000000000000013712632da34788a7b4ae2cd78f7982c7036610126b" ] }, "execution_count": 4, "metadata": { }, "output_type": "execute_result" } ], "source": [ "row4blockid <- lowest_blocks[4, 'block_id']\n", "link <- sprintf(\"%s\", row4blockid, row4blockid)\n", "IRdisplay::display_html(link)" ] }, { "cell_type": "markdown", "metadata": { "collapsed": false }, "source": [ "## [Slush pool](https://slushpool.com/), of course.\n", "\n", "Always ahead of its time …" ] }, { "cell_type": "code", "execution_count": 0, "metadata": { "collapsed": false }, "outputs": [ ], "source": [ ] } ], "metadata": { "kernelspec": { "display_name": "R (R-Project)", "language": "r", "name": "ir" }, "language_info": { "codemirror_mode": "r", "file_extension": ".r", "mimetype": "text/x-r-source", "name": "R", "pygments_lexer": "r", "version": "3.4.3" } }, "nbformat": 4, "nbformat_minor": 0 }