crypto_bitcoin (full)

gistfile1.txt

//// Advanced relational diagram for bigquery-public-data:crypto_bitcoin
//// Update on 2021-02-12
//// diagram by @rifkiamil
//// For the https://github.com/blockchain-etl/bitcoin-etl project

Project "bigquery-public-data:Crypto_Bitcoin" {
  database_type: 'BigQuery'
  Note: 'Advanced Bitcoin diagram layout (not using RECORD field type)'
}

Table "bigquery-public-data:crypto_bitcoin.blocks" as bcb {
  hash STRING [pk, not null,note: 'Hash of this block']
  size INTEGER [null, note: 'The size of block data in bytes'] 
  stripped_size INTEGER [null, note: 'The size of block data in bytes excluding witness data']
  weight INTEGER [null, note: 'Three times the base size plus the total size. https://github.com/bitcoin/bips/blob/master/bip-0141.mediawiki']
  number INTEGER [not null, note: 'The number of the bloc']
  version INTEGER [null, note: 'Protocol version specified in block header']
  merkle_root STRING [note: 'The root node of a Merkle tree, where leaves are transaction hashes']
  timestamp TIMESTAMP [not null,note: 'Block creation timestamp specified in block header']
  timestamp_month "PARTITIONED FIELD & DATE" [not null,note: 'Month of the block which contains this transaction and used for partitioned field']
  nonce STRING  [null, note: 'Difficulty solution specified in block header']
  bits STRING [null, note: 'Difficulty threshold specified in block header']
  coinbase_param STRING [null, note: 'Data specified in the coinbase transaction of this block']
  transaction_count INTEGER [null, note: 'Number of transactions included in this block']
  Note: 'Stores each Block from Bitcoin Blockcain'
}


Table "bigquery-public-data:crypto_bitcoin.transactions" as bct 
{
  hash STRING [not null, pk, note: 'The hash of this transaction']
  size INTEGER [null, note: 'The size of this transaction in bytes']
  virtual_size INTEGER [null, note: 'The virtual transaction size (differs from size for witness transactions)']
  version INTEGER [null, note: 'Protocol version specified in block which contained this transaction']
  lock_time INTEGER [null, note: 'Earliest time that miners can include the transaction in their hashing of the Merkle root to attach it in the latest block of the blockchain']
  block_hash STRING [not null, note: 'Hash of the block which contains this transaction']
  block_number INTEGER [not null, note: 'Number of the block which contains this transaction']
  block_timestamp TIMESTAMP [not null, note: 'Timestamp of the block which contains this transaction']
  block_timestamp_month "PARTITIONED FIELD & DATE" [not null, note: 'Month of the block which contains this transaction and used for partitioned field']
  input_count INTEGER [null, note: 'The number of inputs in the transaction']
  output_count INTEGER [null, note: 'The number of outputs in the transaction']
  input_value NUMERIC [null, note: 'Total value of inputs in the transaction']
  output_value NUMERIC [null, note: 'Total value of outputs in the transaction']
  is_coinbase BOOLEAN [null, note: 'true if this transaction is a coinbase transaction']
  fee NUMERIC [null, note: 'The fee paid by this transaction']
  inputs RECORD [not null, note: 'Transaction inputs']
  "inputs.index" INTEGER [not null, note: '0 indexed number of an input within a transaction']
  "inputs.spent_transaction_hash" STRING [null, note: 'The hash of the transaction which contains the output that this input spends']
  "inputs.spent_output_index" INTEGER [null, note: 'The index of the output this input spends']
  "inputs.script_asm" STRING [null, note: 'Symbolic representation of the bitcoins script language op-codes']
  "inputs.script_hex" STRING [null, note: 'Hexadecimal representation of the bitcoins script language op-codes']
  "inputs.sequence" INTEGER [null, note: 'A number intended to allow unconfirmed time-locked transactions to be updated before being finalized; not currently used except to disable locktime in a transaction']
  "inputs.required_signatures" INTEGER [null, note: 'The number of signatures required to authorize the spent output']
  "inputs.type" STRING [null, note: 'The address type of the spent output']
  "inputs.addresses" STRING [not null, note: 'Addresses which own the spent output']
  "inputs.value" STRING [null, note: 'The value in base currency attached to the spent output']
  outputs RECORD [not null, note: 'Transaction outputs']
  "outputs.index" INTEGER [not null, note: '0 indexed number of an output within a transaction used by a later transaction to refer to that specific outpu']
  "outputs.script_asm" STRING [null, note: 'Symbolic representation of the bitcoins script language op-codes']
  "outputs.script_hex" STRING [null, note: 'Hexadecimal representation of the bitcoins script language op-codes']
  "outputs.required_signatures" INTEGER [not null, note: 'The number of signatures required to authorize spending of this output']
  "outputs.type" STRING [null, note: 'The address type of the output']
  "outputs.addresses" STRING [not null, note: 'Addresses which own this output']
  "outputs.value" STRING [null, note: 'The value in base currency attached to this output']
}

Table "bigquery-public-data:crypto_bitcoin.inputs (view)" as bci
{
  transaction_hash STRING [not null, pk, note: 'The hash of this transaction']
  block_hash STRING [not null,note: 'Hash of this block']
  block_number INTEGER [not null,note: 'The number of the bloc']
  block_timestamp TIMESTAMP [not null,note: 'Block creation timestamp specified in block header']
  index INTEGER [not null, pk, note: '0-indexed number of an input within a transaction']
  spent_transaction_hash STRING [null, note: 'The hash of the transaction which contains the output that this input spends']
  spent_output_index INTEGER [null, note: 'The index of the output this input spends']
  script_asm STRING [null, note: 'Symbolic representation of the bitcoins script language op-codes']
  script_hex STRING [null,note: 'Hexadecimal representation of the bitcoins script language op-codes']
  sequence INTEGER [null,note: 'A number intended to allow unconfirmed time-locked transactions to be updated before being finalized; not currently used except to disable locktime in a transaction']
  required_signatures INTEGER [null,note: 'The number of signatures required to authorize the spent output']
  type STRING [null,note: 'The address type of the spent output']
  addresses STRING [null,note: 'Addresses which own the spent output']
  value NUMERIC [null,note: 'The value in base currency attached to the spent output']
  Note: 'This is a view based on the transaction table using field input which has the type of RECORD'
}

Table "bigquery-public-data:crypto_bitcoin.outputs (view)" as bco 
{
  transaction_hash STRING [not null, pk, note: 'The hash of this transaction']
  block_hash STRING [not null,note: 'Hash of this block']
  block_number INTEGER [not null,note: 'The number of the bloc']
  block_timestamp TIMESTAMP [not null,note: 'Block creation timestamp specified in block header']
  index INTEGER [not null, pk, note: '0-indexed number of an input within a transaction']
  script_asm STRING [null, note: 'Symbolic representation of the bitcoins script language op-codes']
  script_hex STRING [null,note: 'Hexadecimal representation of the bitcoins script language op-codes']
  required_signatures INTEGER [null, note: 'The number of signatures required to authorize spending of this output']
  type STRING [null, note: 'The address type of the output']
  addresses STRING [not null, note:'Addresses which own this output']
  value NUMERIC [null, note:'The value in base currency attached to this output']
  Note: 'This is a view based on the transaction table using field output which has the type of RECORD'
}


Ref: "bigquery-public-data:crypto_bitcoin.blocks"."hash" < "bigquery-public-data:crypto_bitcoin.transactions"."block_hash"

Ref: "bigquery-public-data:crypto_bitcoin.transactions"."hash" < "bigquery-public-data:crypto_bitcoin.inputs (view)"."transaction_hash"

Ref: "bigquery-public-data:crypto_bitcoin.transactions"."hash" < "bigquery-public-data:crypto_bitcoin.outputs (view)"."transaction_hash"