Sharedbigrquery.ipynbOpen in CoCalc
Bigquery in cocalc

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
block_iddifficultyTargettimestamp
0000000000000000000000ab789f6d71d9642ae3f697975ccd00afcb98fe6bd2391203401 1522452348000
0000000000000000000000bb5b432a764ad6c7acf677dcd99161abfdf68e698e402691653 1513720654000
00000000000000000000011246f099d94f91628d71c9d75ad2f9a06e2beb7e92402804657 1489999494000
00000000000000000000013712632da34788a7b4ae2cd78f7982c7036610126b404031509 1438792320000
00000000000000000000021c043e439b5f4b632389b0062306bf2d4e0b657c7c391481763 1520517455000
000000000000000000000241d3121e74bbbc7a034666d6c6f4d46cac2dc2394a391481763 1520783660000
0000000000000000000002569beb610e7f806774fd0c650c415a78a087b9d979402713392 1508738156000
00000000000000000000026a36ce6423544d841d51f5b36ffccb63e4a4ce0b12402690497 1515091565000
000000000000000000000388a8c212badee4d31543b7afdca2d67e501853bb1a391129783 1523400616000
0000000000000000000003b4cc8365995e7567c7720b51e595ae54e9fe748622402717299 1507448823000

Converting the timestamp strings to ISO formatted datetimes

as_tibble(lowest_blocks) %>%
    mutate(timestamp = as.numeric(timestamp)) %>%
    mutate(timestamp = as_datetime(timestamp / 1000))
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

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 …