在接觸了比特幣和區塊鏈後，我一直有一個想法，就是把所有比特幣的區塊鏈資料放入到關聯式資料庫（比如SQL Server）中，然後當成一個資料倉儲，做做比特幣交易資料的各種分析。想法已經很久了，但是一直沒有實施。最近正好有點時間，於是寫了一個比特幣區塊鏈的匯出匯入程式。
之前我的一篇部落格：在區塊鏈上表白——使用C#將一句話放入比特幣的區塊鏈上 介紹了怎麼發起一筆比特幣的交易，今天我們仍然是使用C#+NBitcoin，讀取比特幣錢包Bitcoin Core下載到本地的全量區塊鏈資料，並將這些資料寫入資料庫。如果有和我一樣想法的朋友，可以參考下面是我的操作過程：
一、準備
我們要解析的是儲存在本地硬碟上的Bitcoin Core錢包的全量比特幣資料，那麼首先就是要下載並安裝好Bitcoin Core，下載地址：https://bitcoin.org/en/download 然後就等著這個軟體同步區塊鏈資料吧。目前比特幣的區塊鏈資料大概130G，所以可能需要好幾天，甚至一個星期才能將所有區塊鏈資料同步到本地。當然如果你很早就安裝了這個軟體，那麼就太好了，畢竟要等好幾天甚至一個星期，真的很痛苦。
二、建立比特幣區塊鏈資料模型
要進行區塊鏈資料的分析，那麼必須得對區塊鏈的資料模型瞭解才行。我大概研究了一下，可以總結出4個實體：區塊、交易、輸入、輸出。而其中的關係是，一個區塊對應多個交易，一個交易對應多個輸入和多個輸出。除了Coinbase的輸入外，一筆輸入對應另一筆交易中的輸出。於是我們可以得出這樣的資料模型：
需要特別說明幾點的是：
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
三、匯出區塊鏈資料為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; }
至於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的時候我也懷疑能不能匯入那麼大批量的資料。
最後，附上一張我正在匯入中的程式圖，已經導了一天了，還沒有完成，估計還得再花一、兩天時間吧。
所有區塊鏈資料都進入資料庫以後，就要發揮一下我的想象力，看能夠分析出什麼有意思的結果了。