如何将Bitcoin比特币区块链数据导入关系数据库

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 在接触了比特币和区块链后,我一直有一个想法,就是把所有比特币的区块链数据放入到关系数据库(比如SQL Server)中,然后当成一个数据仓库,做做比特币交易数据的各种分析。想法已经很久了,但是一直没有实施。

在接触了比特币和区块链后,我一直有一个想法,就是把所有比特币的区块链数据放入到关系数据库(比如SQL Server)中,然后当成一个数据仓库,做做比特币交易数据的各种分析。想法已经很久了,但是一直没有实施。最近正好有点时间,于是写了一个比特币区块链的导出导入程序。

之前我的一篇博客:在区块链上表白——使用C#将一句话放入比特币的区块链上  介绍了怎么发起一笔比特币的交易,今天我们仍然是使用C#+NBitcoin,读取比特币钱包Bitcoin Core下载到本地的全量区块链数据,并将这些数据写入数据库。如果有和我一样想法的朋友,可以参考下面是我的操作过程:

一、准备

我们要解析的是存储在本地硬盘上的Bitcoin Core钱包的全量比特币数据,那么首先就是要下载并安装好Bitcoin Core,下载地址:https://bitcoin.org/en/download 然后就等着这个软件同步区块链数据吧。目前比特币的区块链数据大概130G,所以可能需要好几天,甚至一个星期才能将所有区块链数据同步到本地。当然如果你很早就安装了这个软件,那么就太好了,毕竟要等好几天甚至一个星期,真的很痛苦。

二、建立比特币区块链数据模型

要进行区块链数据的分析,那么必须得对区块链的数据模型了解才行。我大概研究了一下,可以总结出4个实体:区块、交易、输入、输出。而其中的关系是,一个区块对应多个交易,一个交易对应多个输入和多个输出。除了Coinbase的输入外,一笔输入对应另一笔交易中的输出。于是我们可以得出这样的数据模型:

image

需要特别说明几点的是:

1.TxId是自增的int,我没有用TxHash做Transaction的PK,那是因为TxHash根本就不唯一啊!有好几个不同区块里面的第一笔交易,也就是Coinbase交易是相同的。这其实应该是异常数据,因为相同的TxHash将导致只能花费一次,所以这个矿工杯具了。

2.对于一笔Coinbase 的Transaction,其输入的PreOutTxId是0000000000000000000000000000000000000000000000000000000000000000,而其PreOutIndex是-1,这是一条不存在的TxOutput,所以我并没有建立TXInput和TxOutput的外键关联。

3.对于Block,PreId就是上一个Block的ID,而创世区块的PreId是0000000000000000000000000000000000000000000000000000000000000000,也是一个不存在的BlockId,所以我没有建立Block的自引用外键。

4.有很多字段其实并不是区块链数据结构中的,这些字段是我添加为了接下来方便分析用的。在导入的时候并没有值,需要经过一定的SQL运算才能得到。比如Trans里面的TotalInAmount,TransFee等。

我用的是PowerDesigner,建模完成后,生成SQL语句,即可。这是我的建表SQL:

create table Block ( 
   Height               int                  not null, 
   BlkId                char(64)             not null, 
   TxCount              int                  not null, 
   Size                 int                  not null, 
   PreId                char(64)             not null, 
   Timestamp            datetime             not null, 
   Nonce                bigint               not null, 
   Difficulty           double precision     not null, 
   Bits                 char(64)             not null, 
   Version              int                  not null, 
   TxMerkleRoot         char(64)             not null, 
   constraint PK_BLOCK primary key nonclustered (BlkId) 
) 
go

/*==============================================================*/ 
/* Index: Block_Height                                          */ 
/*==============================================================*/ 
create unique clustered index Block_Height on Block ( 
Height ASC 
) 
go

/*==============================================================*/ 
/* Table: Trans                                                 */ 
/*==============================================================*/ 
create table Trans ( 
   TxId                 int                  not null, 
   BlkId                char(64)             not null, 
   TxHash               char(64)             not null, 
   Version              int                  not null, 
   InputCount           int                  not null, 
   OutputCount          int                  not null, 
   TotalOutAmount       bigint               not null, 
   TotalInAmount        bigint               not null, 
   TransFee             bigint               not null, 
   IsCoinbase           bit                  not null, 
   IsHeightLock         bit                  not null, 
   IsTimeLock           bit                  not null, 
   LockTimeValue        int                  not null, 
   Size                 int                  not null, 
   TransTime            datetime             not null, 
   constraint PK_TRANS primary key (TxId) 
) 
go

/*==============================================================*/ 
/* Index: Relationship_1_FK                                     */ 
/*==============================================================*/ 
create index Relationship_1_FK on Trans ( 
BlkId ASC 
) 
go

/*==============================================================*/ 
/* Index: Trans_Hash                                            */ 
/*==============================================================*/ 
create index Trans_Hash on Trans ( 
TxHash ASC 
) 
go

/*==============================================================*/ 
/* Table: TxInput                                               */ 
/*==============================================================*/ 
create table TxInput ( 
   TxId                 int                  not null, 
   Idx                  int                  not null, 
   Amount               bigint               not null, 
   PrevOutTxId          char(64)             not null, 
   PrevOutIndex         int                  not null, 
   PaymentScriptLen     int                  not null, 
   PaymentScript        varchar(8000)        not null, 
   Address              char(58)             null, 
   constraint PK_TXINPUT primary key (TxId, Idx) 
) 
go

/*==============================================================*/ 
/* Index: Relationship_2_FK                                     */ 
/*==============================================================*/ 
create index Relationship_2_FK on TxInput ( 
TxId ASC 
) 
go

/*==============================================================*/ 
/* Table: TxOutput                                              */ 
/*==============================================================*/ 
create table TxOutput ( 
   TxId                 int                  not null, 
   Idx                  int                  not null, 
   Amount               bigint               not null, 
   ScriptPubKeyLen      int                  not null, 
   ScriptPubKey         varchar(8000)        not null, 
   Address              char(58)             null, 
   IsUnspendable        bit                  not null, 
   IsPayToScriptHash    bit                  not null, 
   IsValid              bit                  not null, 
   IsSpent              bit                  not null, 
   constraint PK_TXOUTPUT primary key (TxId, Idx) 
) 
go

/*==============================================================*/ 
/* Index: Relationship_3_FK                                     */ 
/*==============================================================*/ 
create index Relationship_3_FK on TxOutput ( 
TxId ASC 
) 
go

alter table Trans 
   add constraint FK_TRANS_RELATIONS_BLOCK foreign key (BlkId) 
      references Block (BlkId) 
go

alter table TxInput 
   add constraint FK_TXINPUT_RELATIONS_TRANS foreign key (TxId) 
      references Trans (TxId) 
go

alter table TxOutput 
   add constraint FK_TXOUTPUT_RELATIONS_TRANS foreign key (TxId) 
      references Trans (TxId) 
go
View Code

三、导出区块链数据为CSV

数据模型有了,接下来我们就是建立对应的表,然后写程序将比特币的Block写入到数据库中。我本来用的是EntityFramework来实现插入数据库的操作。但是后来发现实在太慢,插入一个Block甚至要等10多20秒,这要等到何年何月才能插入完啊!我试了各种方案,比如写原生的SQL,用事务,用LINQToSQL等,性能都很不理想。最后终于找到了一个好办法,那就是直接导出为文本文件(比如CSV格式),然后用SQL Server的Bulk Insert命令来实现批量导入,这是我已知的最快的写入数据库的方法。

解析Bitcoin Core下载下来的所有比特币区块链数据用的还是NBitcoin这个开源库。只需要用到其中的BlockStore 类,即可轻松实现区块链数据的解析。

以下是我将区块链数据解析为我们的Block对象的代码:

private static void LoadBlock2DB(string localPath, int start) 
{ 
    var store = new BlockStore(localPath, Network.Main); 
    int i = -1; 
    BlockToCsvHelper helper = new BlockToCsvHelper(height);

    foreach (var block in store.Enumerate(false)) 
    { 
        i++; 
        if (i < start) 
        { 
            continue; 
        }

        try 
         { 
            log.Debug("Start load Block " + i + ": " + block.Item.Header + " from file:" +  block.BlockPosition.ToString()); 
            var blk = LoadBlock(block, i);//将NBitcoin的Block转换为我们建模的Block对象 
            helper.WriteBitcoin2Csv(blk);//将我们的Block对象转换为CSV保存 
        } 
        catch (Exception ex) 
         { 
            log.Error("保存Block到数据库时异常,请手动载入,i=" + i, ex); 
        }

    } 
    Console.WriteLine("--------End-----------"); 
    Console.ReadLine(); 
}


private static Block LoadBlock(StoredBlock block, int i) 
{ 
    var blk = new Block() 
    { 
         BlkId = block.Item.Header.ToString(), 
        Difficulty = block.Item.Header.Bits.Difficulty, 
        Bits = block.Item.Header.Bits.ToString(), 
        Height = i, 
        Nonce = block.Item.Header.Nonce, 
        PreId = block.Item.Header.HashPrevBlock.ToString(), 
        TxMerkleRoot = block.Item.GetMerkleRoot().ToString(), 
        Size = block.Item.GetSerializedSize(), 
        Version = block.Item.Header.Version, 
        Timestamp = block.Item.Header.BlockTime.UtcDateTime, 
        TxCount = block.Item.Transactions.Count 
    }; 
    log.Debug("Transaction Count=" + block.Item.Transactions.Count); 
    foreach (var transaction in block.Item.Transactions) 
    { 
        var tx = new Trans() 
         { 
            BlkId = blk.BlkId, 
            TxHash = transaction.GetHash().ToString(), 
            Version = (int)transaction.Version, 
            InputCount = transaction.Inputs.Count, 
             OutputCount = transaction.Outputs.Count, 
            TotalOutAmount = transaction.TotalOut.Satoshi, 
            TransTime = blk.Timestamp, 
            IsCoinbase = transaction.IsCoinBase, 
            IsHeightLock = transaction.LockTime.IsHeightLock, 
            IsTimeLock = transaction.LockTime.IsTimeLock, 
             LockTimeValue = (int)transaction.LockTime.Value, 
             Size = transaction.GetSerializedSize() 
        }; 
        blk.Trans.Add(tx); 
        for (var idx = 0; idx < transaction.Inputs.Count; idx++) 
        { 
            var input = transaction.Inputs[idx]; 
            var txInput = new TxInput() 
             { 
                PaymentScript = input.ScriptSig.ToString(), 
                PaymentScriptLen = input.ScriptSig.Length, 
                 PrevOutTxId = input.PrevOut.Hash.ToString(), 
                 PrevOutIndex = (int)input.PrevOut.N, 
                Trans = tx, 
                Idx = idx 
            };

            if (!tx.IsCoinbase) 
            { 
                var addr = input.ScriptSig.GetSignerAddress(Network.Main); 
                if (addr != null) 
                { 
                    txInput.Address = addr.ToString(); 
                } 
            } 
             if (txInput.PaymentScript.Length > 8000) 
            { 
                 log.Error("Transaction Input PaymentScript异常,将被截断,TxHash: " + tx.TxHash); 
                txInput.PaymentScript = txInput.PaymentScript.Substring(0, 7999); 
            } 
            tx.TxInput.Add(txInput); 
        } 
        for (var idx = 0; idx < transaction.Outputs.Count; idx++) 
        { 
            var output = transaction.Outputs[idx]; 
            var txOutput = new TxOutput() 
            { 
                Amount = output.Value.Satoshi, 
                ScriptPubKey = output.ScriptPubKey.ToString(), 
                ScriptPubKeyLen = output.ScriptPubKey.Length, 
                Trans = tx, 
                IsUnspendable = output.ScriptPubKey.IsUnspendable, 
                IsPayToScriptHash = output.ScriptPubKey.IsPayToScriptHash, 
                IsValid = output.ScriptPubKey.IsValid, 
                Idx = idx

            }; 
            if (txOutput.ScriptPubKey.Length > 8000) 
            { 
                log.Error("Transaction Output ScriptPubKey异常,将被截断,TxHash: " + tx.TxHash); 
                txOutput.ScriptPubKey = txOutput.ScriptPubKey.Substring(0, 7999); 
            } 
            if (!output.ScriptPubKey.IsUnspendable) 
            { 
                if (output.ScriptPubKey.IsPayToScriptHash) 
                { 
                    txOutput.Address = output.ScriptPubKey.GetScriptAddress(Network.Main).ToString(); 
                } 
                else 
                { 
                     var addr = output.ScriptPubKey.GetDestinationAddress(Network.Main); 
                    if (addr == null) 
                     { 
                        var keys = output.ScriptPubKey.GetDestinationPublicKeys(); 
                        if (keys.Length == 0) 
                        { 
                             //异常 
                            log.Warn("Transaction Output异常,TxHash: " + tx.TxHash); 
                        } 
                         else 
                        { 
                             addr = keys[0].GetAddress(Network.Main); 
                         } 
                    } 
                    if (addr != null) 
                    { 
                        txOutput.Address = addr.ToString(); 
                    } 
                 } 
            } 
            tx.TxOutput.Add(txOutput); 
         } 
    } 
    return blk; 
}
View Code

至于WriteBitcoin2Csv方法,就是以一定的格式,把Block、Trans、TxInput、TxOutput这4个对象分别写入4个文本文件中即可。

四、将CSV导入SQL Server

在完成了CSV文件的导出后,接下来就是怎么将CSV文件导入到SQL Server中。这个很简单,只需要执行BULK INSERT命令。比如这是我在测试的时候用到的SQL语句:

bulk insert [Block] from 'F:\temp\blk205867.csv'; 
bulk insert Trans from 'F:\temp\trans205867.csv'; 
bulk insert TxInput from 'F:\temp\input205867.csv'; 
bulk insert TxOutput from 'F:\temp\output205867.csv';

当然在实际的情况中,我并不是这么做的。我是每1000个Block就生成4个csv文件,然后使用C#连接到数据库,执行bulk insert命令。执行完成后再把这生成的4个csv文件删除,然后再循环继续导出下一批1000个Block。因为比特币的区块链数据实在太大了,如果我不分批,那么我的PC机硬盘就不够用了,而且在导入SQL Server的时候我也怀疑能不能导入那么大批量的数据。

最后,附上一张我正在导入中的进程图,已经导了一天了,还没有完成,估计还得再花一、两天时间吧。

image

所有区块链数据都进入数据库以后,就要发挥一下我的想象力,看能够分析出什么有意思的结果了。

【本文章出自 博客园深蓝居,转载请注明作者出处,如果您觉得博主的文章对您有很大帮助,欢迎点击右侧打赏按钮对博主进行打赏。】
打个招聘广告,博主正在主导开发一个跨链区块链项目:PalletOne,一直在招Go程序员,待遇丰厚,坐标北京酒仙桥,希望有识之士加入!
目录
相关文章
|
7月前
|
供应链 安全 物联网
区块链去中心化交易所源码|去中心化交易系统开发
随着区块链技术的发展,应用的扩展,区块链软件开√发也随之应用到物联网、供应链管理等领域,其中包含区块链交Y所系统,区块链去中心化交Y所,依托于区块链技术,具有去中心化、匿名性、信息不可纂改等特点
|
11月前
|
算法 区块链 数据库
区块链101:什么是分布式分类帐?
区块链101:什么是分布式分类帐?
|
11月前
|
存储 安全 区块链
区块链101:区块链和数据库的区别是什么?
区块链101:区块链和数据库的区别是什么?
|
存储 区块链
分布式账本
分布式账本
98 0
|
存储 供应链 算法
区块链从入门到放弃系列教程-涵盖密码学,超级账本,以太坊,Libra,比特币等持续更新
区块链从入门到放弃系列教程-涵盖密码学,超级账本,以太坊,Libra,比特币等持续更新
区块链从入门到放弃系列教程-涵盖密码学,超级账本,以太坊,Libra,比特币等持续更新
|
存储 供应链 算法
区块链从入门到放弃系列教程-涵盖密码学,超级账本,以太坊,Libra,比特币等持续更新
区块链从入门到放弃系列教程-涵盖密码学,超级账本,以太坊,Libra,比特币等持续更新
区块链从入门到放弃系列教程-涵盖密码学,超级账本,以太坊,Libra,比特币等持续更新
|
SQL 存储 数据挖掘
区块链数据探索:Bitcoin公链数据ETL
Bitcoin 公链可以理解为是一个公共的数据库,里面存储的是Bitcoin发布至今的所有转账记录,并且任何人只要接入到其网络中都可以获取,并不需要任何交易、挖矿、持币等相关操作。 本文主要主题的是将原始的Bitcoin公链数据进行清洗规整,写入到阿里云SLS,然后做一些有趣的数据处理,比如实现简洁的区块链浏览器、数据分析、交易链路追踪等。
849 0
区块链数据探索:Bitcoin公链数据ETL
|
存储 区块链
区块链实战(一)实现简单的区块与区块链交易
区块链实战(一)实现简单的区块与区块链交易
123 0
区块链实战(一)实现简单的区块与区块链交易
|
SQL API 区块链
Google 新区块链服务! BigQuery 导入以太坊分析工具一览智慧合约、交易量现况
Google 考量到在系统架构,以太坊类似于比特币,主要用于记录不可变的交易。两者本质上都是 OLTP 数据库,但几乎不提供 OLAP(分析)功能。但是,以太坊数据明显有系统分析管理的需求性。
1934 0