Skip to content

Instantly share code, notes, and snippets.

View gingerwizard's full-sized avatar

Dale McDiarmid gingerwizard

48 followers · 2 following
View GitHub Profile
@gingerwizard
gingerwizard / aws_glue_redshift_to_clickhouse.py
Created March 15, 2023 18:03
import logging
import sys
import time
import clickhouse_connect
from boto3 import Session
logger = logging.getLogger()
logger.setLevel(logging.INFO)
handler = logging.StreamHandler(sys.stdout)
@gingerwizard
gingerwizard / grafana_repo_analysis.md
Created February 9, 2023 16:51
@gingerwizard
gingerwizard / payments.tsv
Created February 8, 2023 12:32
425.3 confirmed 2023-01-25 13:43:39 2023-01-25 13:46:29
417.57 confirmed 2023-01-25 13:44:57 2023-01-25 13:47:44
395.46 confirmed 2023-01-25 13:48:38 2023-01-25 13:51:16
393.11 confirmed 2023-01-25 13:49:01 2023-01-25 13:51:38
386.77 confirmed 2023-01-25 13:50:05 2023-01-25 13:52:39
384.24 confirmed 2023-01-25 13:50:30 2023-01-25 13:53:03
380.27 confirmed 2023-01-25 13:51:10 2023-01-25 13:53:42
374.69 confirmed 2023-01-25 13:52:06 2023-01-25 13:54:35
371.28 confirmed 2023-01-25 13:52:40 2023-01-25 13:55:08
362.02 confirmed 2023-01-25 13:54:12 2023-01-25 13:56:36
@gingerwizard
gingerwizard / article_reads.tsv
Created February 8, 2023 12:31
2023-01-18 23:44:17 10 [16,18,7,21,23,22,11,19,9,8] [82,96,294,253,292,66,44,256,222,86] 424
2023-01-20 22:53:00 10 [21,8] [30,176] 271
2023-01-21 03:05:19 10 [24,11,23,9] [178,177,172,105] 536
2023-01-22 09:38:58 10 [21,9,4,14,22,17,7] [72,172,175,121,268,230,96] 690
2023-01-22 22:34:21 10 [5,13] [61,228] 997
2023-01-23 19:46:03 10 [3] [149] 355
2023-01-24 08:00:16 10 [4,10,21,9,6,8,2,19,5,20] [256,30,163,205,266,65,257,165,151,70] 138
2023-01-24 23:47:09 10 [10,4,13,12] [291,113,157,113] 590
2023-01-25 06:09:50 10 [9,1,2,18,3,7,10] [70,237,101,240,73,215,173] 848
2023-01-25 06:23:21 10 [4,20,6,18,9,10,7] [266,132,103,212,75,299,231] 462
@gingerwizard
gingerwizard / contracts.md
Created February 6, 2023 12:33 
CREATE TABLE `bigquery-public-data.crypto_ethereum.contracts`
(
  address STRING NOT NULL OPTIONS(description="Address of the contract"),
  bytecode STRING OPTIONS(description="Bytecode of the contract"),
  function_sighashes ARRAY<STRING> OPTIONS(description="4-byte function signature hashes"),
  is_erc20 BOOL OPTIONS(description="Whether this contract is an ERC20 contract"),
  is_erc721 BOOL OPTIONS(description="Whether this contract is an ERC721 contract"),
  block_timestamp TIMESTAMP NOT NULL OPTIONS(description="Timestamp of the block where this contract was created"),
  block_number INT64 NOT NULL OPTIONS(description="Block number where this contract was created"),
@gingerwizard
gingerwizard / anitjoin-needed.md
Created February 3, 2023 15:40 
SELECT
    sum(sum_tx_ether),
    sum(avg_tx_gas_cost)
FROM
(
    SELECT
        SUM(value / POWER(10, 18)) AS sum_tx_ether,
        AVG(gas_price * (receipt_gas_used / POWER(10, 18))) AS avg_tx_gas_cost,
        toStartOfDay(block_timestamp) AS tx_date
@gingerwizard
gingerwizard / ethereum_schemas.md
Last active February 6, 2023 14:19

Blocks

CREATE TABLE ethereum.blocks
(
    `number` UInt32 CODEC(Delta(4), ZSTD(1)),
    `hash` String,
    `parent_hash` String,
    `nonce` String,
    `sha3_uncles` String,
@gingerwizard
gingerwizard / traces.md
Last active February 6, 2023 13:22 
CREATE TABLE `clickhouse-cloud.crypto_ethereum.traces`
(
  transaction_hash STRING OPTIONS(description="Transaction hash where this trace was in"),
  transaction_index INT64 OPTIONS(description="Integer of the transactions index position in the block"),
  from_address STRING OPTIONS(description="Address of the sender, null when trace_type is genesis or reward"),
  to_address STRING OPTIONS(description="Address of the receiver if trace_type is call, address of new contract or null if trace_type is create, beneficiary address if trace_type is suicide, miner address if trace_type is reward, shareholder address if trace_type is genesis, WithdrawDAO address if trace_type is daofork"),
  value NUMERIC OPTIONS(description="Value transferred in Wei"),
  input STRING OPTIONS(description="The data sent along with the message call"),
  output STRING OPTIONS(description="The output of the message call, bytecode of contract when trace_type is create"),
@gingerwizard
gingerwizard / transactions.md
Last active February 6, 2023 14:14 
CREATE TABLE `clickhouse-cloud.crypto_ethereum.transactions`
(
  `hash` STRING NOT NULL OPTIONS(description="Hash of the transaction"),
  nonce INT64 NOT NULL OPTIONS(description="The number of transactions made by the sender prior to this one"),
  transaction_index INT64 NOT NULL OPTIONS(description="Integer of the transactions index position in the block"),
  from_address STRING NOT NULL OPTIONS(description="Address of the sender"),
  to_address STRING OPTIONS(description="Address of the receiver. null when its a contract creation transaction"),
  value NUMERIC OPTIONS(description="Value transferred in Wei"),
  gas INT64 OPTIONS(description="Gas provided by the sender"),
@gingerwizard
gingerwizard / blocks.md
Last active February 6, 2023 12:31 
CREATE TABLE `clickhouse-cloud.crypto_ethereum.blocks`
(
 timestamp TIMESTAMP NOT NULL OPTIONS(description="The timestamp for when the block was collated"),
 number INT64 NOT NULL OPTIONS(description="The block number"),
 `hash` STRING NOT NULL OPTIONS(description="Hash of the block"),
 parent_hash STRING OPTIONS(description="Hash of the parent block"),
 nonce STRING NOT NULL OPTIONS(description="Hash of the generated proof-of-work"),
 sha3_uncles STRING OPTIONS(description="SHA3 of the uncles data in the block"),
 logs_bloom STRING OPTIONS(description="The bloom filter for the logs of the block"),