medvedev1088

98.3 MiB /start_block=00000000/end_block=00099999/blocks_00000000_00099999.csv
97.7 MiB /start_block=00100000/end_block=00199999/blocks_00100000_00199999.csv
97.4 MiB /start_block=00200000/end_block=00299999/blocks_00200000_00299999.csv
98.2 MiB /start_block=00300000/end_block=00399999/blocks_00300000_00399999.csv
98.5 MiB /start_block=00400000/end_block=00499999/blocks_00400000_00499999.csv
98.6 MiB /start_block=00500000/end_block=00599999/blocks_00500000_00599999.csv
98.6 MiB /start_block=00600000/end_block=00699999/blocks_00600000_00699999.csv
98.7 MiB /start_block=00700000/end_block=00799999/blocks_00700000_00799999.csv
98.7 MiB /start_block=00800000/end_block=00899999/blocks_00800000_00899999.csv
98.9 MiB /start_block=00900000/end_block=00999999/blocks_00900000_00999999.csv

medvedev1088

  7.9 MiB /start_block=00000000/end_block=00099999/transactions_00000000_00099999.csv
 28.7 MiB /start_block=00100000/end_block=00199999/transactions_00100000_00199999.csv
 41.8 MiB /start_block=00200000/end_block=00299999/transactions_00200000_00299999.csv
 46.0 MiB /start_block=00300000/end_block=00399999/transactions_00300000_00399999.csv
 45.8 MiB /start_block=00400000/end_block=00499999/transactions_00400000_00499999.csv
 56.3 MiB /start_block=00500000/end_block=00599999/transactions_00500000_00599999.csv
 71.2 MiB /start_block=00600000/end_block=00699999/transactions_00600000_00699999.csv
127.3 MiB /start_block=00700000/end_block=00799999/transactions_00700000_00799999.csv
113.6 MiB /start_block=00800000/end_block=00899999/transactions_00800000_00899999.csv
135.7 MiB /start_block=00900000/end_block=00999999/transactions_00900000_00999999.csv

medvedev1088

 18.4 KiB /start_block=00400000/end_block=00499999/token_transfers_00400000_00499999.csv
 36.6 KiB /start_block=00500000/end_block=00599999/token_transfers_00500000_00599999.csv
 18.5 KiB /start_block=00600000/end_block=00699999/token_transfers_00600000_00699999.csv
  3.6 KiB /start_block=00700000/end_block=00799999/token_transfers_00700000_00799999.csv
 13.2 KiB /start_block=00800000/end_block=00899999/token_transfers_00800000_00899999.csv
431.9 KiB /start_block=00900000/end_block=00999999/token_transfers_00900000_00999999.csv
183.9 KiB /start_block=01000000/end_block=01099999/token_transfers_01000000_01099999.csv
  1.0 MiB /start_block=01100000/end_block=01199999/token_transfers_01100000_01199999.csv
 10.8 MiB /start_block=01200000/end_block=01299999/token_transfers_01200000_01299999.csv
  4.9 MiB /start_block=01300000/end_block=01399999/token_transfers_01300000_01399999.csv

medvedev1088

CREATE EXTERNAL TABLE IF NOT EXISTS blocks (
    number BIGINT,
    hash STRING,
    parent_hash STRING,
    nonce STRING,
    sha3_uncles STRING,
    logs_bloom STRING,
    transactions_root STRING,
    state_root STRING,
    receipts_root STRING,

medvedev1088

{
    "objects": [
        {
            "id": "Default",
            "name": "Default",
            "failureAndRerunMode": "CASCADE",
            "resourceRole": "DataPipelineDefaultResourceRole",
            "role": "DataPipelineDefaultRole",
            "pipelineLogUri": "s3://<your_bucket>/",
            "scheduleType": "ondemand",

medvedev1088

# The below partitioning tries to make each partition of equal size.
# The first million blocks are in a single partition.
# The next 3 million blocks are in 100k partitions.
# The next 1 million blocks are in 10k partitions.
# Note that there is a limit in Data Pipeline on the number of objects, which can be
# increased in the Support Center
# https://docs.aws.amazon.com/datapipeline/latest/DeveloperGuide/dp-limits.html
EXPORT_PARTITIONS = [(0, 999999)] + \
                    [(start, end) for start, end in split_to_batches(1000000, 1999999, 100000)] + \
                    [(start, end) for start, end in split_to_batches(2000000, 2999999, 100000)] + \

medvedev1088

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

## @params: [JOB_NAME]
args = getResolvedOptions(sys.argv, ['JOB_NAME'])

medvedev1088

#standardSQL
-- MIT License
-- Copyright (c) 2018 Evgeny Medvedev, evge.medvedev@gmail.com
with double_entry_book as (
    -- debits
    select to_address as address, value as value
    from `bigquery-public-data.crypto_ethereum.traces`
    where to_address is not null
    and status = 1
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)

medvedev1088

WITH traces_with_status AS (
    -- Find all nested traces of failed traces
    WITH nested_failed_traces AS (
        SELECT distinct child.transaction_hash, child.trace_address
        FROM `bigquery-public-data.ethereum_blockchain.traces` parent
        JOIN `bigquery-public-data.ethereum_blockchain.traces` child
        ON (parent.trace_address IS NULL OR starts_with(child.trace_address, concat(parent.trace_address, ',')))
        AND child.transaction_hash = parent.transaction_hash
        where parent.trace_type IN ('call', 'create')
        AND parent.error IS NOT NULL

medvedev1088