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

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的时候我也怀疑能不能导入那么大批量的数据。

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

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

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

标签：数据库的操作   download   csv   path   rate   地址   script   没有   应该