This step-by-step tutorial will show how to use Microsoft Excel to retrieve data from the HiveSQL database and present your result in a simple but nice pivot chart.
Let’s say for example you want to see how many transactions have been issued each month on the blockchain. The SQL query to get such information will be:
SELECTYEAR(Blocks.timestamp) AS [Year],MONTH(Blocks.timestamp) AS [Month],COUNT(*) AS [Transactions]FROMTransactionsINNER JOIN Blocks ON Transactions.block_num = Blocks.block_numGROUP BYYEAR(Blocks.timestamp),MONTH(Blocks.timestamp)
Retrieving data with Excel
Start Excel, go to the Data tab and select SQL Server as your data source
The Data Connection Wizard will be displayed
Enter the connection information in the wizard dialog box as shown and click "Next"
Select any table (it doesn’t matter at this stage) and click '"Finish" to close the wizard
Click on "Properties"
Select the "Definition" tab and check the "Save Password" checkbox to avoid Excel asking you for credentials each time you connect to the database.
You can safely ignore the warning if your computer is secure
Open the "Command type" dropdown control and select "SQL"
Paste your SQL query and click
Select the type of data presentation you want in Excel.
Now that you got all your data, make a nice chart.
Organize your data fields in the pivot properties
You now have created your report using the blockchain data.