bigrquery.ipynb

Kernel: R (R-Project)

Big-R-Query

https://cran.r-project.org/web/packages/bigrquery/README.html

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.

Here, we look for the lowest block IDs in the Bitcoin blockchain and want to know when they were.

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.

library(tidyverse)
library(lubridate)
library(bigrquery)
library(tibble)

project <- "...." # put your project ID here

── Attaching packages ─────────────────────────────────────── tidyverse 1.2.1 ──
✔ ggplot2 2.2.1     ✔ purrr   0.2.4
✔ tibble  1.4.2     ✔ dplyr   0.7.4
✔ tidyr   0.8.0     ✔ stringr 1.2.0
✔ readr   1.1.1     ✔ forcats 0.2.0
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()

Attaching package: ‘lubridate’

The following object is masked from ‘package:base’:

    date

Asking BigQuery

sql <- "SELECT
  block_id,
  difficultyTarget,
  STRING(timestamp) AS timestamp
FROM
  [bigquery-public-data:bitcoin_blockchain.blocks]
ORDER BY
  block_id ASC
LIMIT
  10"

# execute the query and store the result
lowest_blocks <- query_exec(sql, project = project)
lowest_blocks

41.0 megabytes processed

Converting the timestamp strings to ISO formatted datetimes

as_tibble(lowest_blocks) %>%
    mutate(timestamp = as.numeric(timestamp)) %>%
    mutate(timestamp = as_datetime(timestamp / 1000))

Look at row #4

this tells someone was really lucky to find such a low block ID back then in 2015!

row4blockid <- lowest_blocks[4, 'block_id']
link <- sprintf("<a href='https://blockchair.com/bitcoin/block/%s'>%s</a>", row4blockid, row4blockid)
IRdisplay::display_html(link)

Slush pool, of course.

Always ahead of its time …