Comment on page
HiveSQL for Microsoft Excel
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:
SELECT
YEAR(Blocks.timestamp) AS [Year],
MONTH(Blocks.timestamp) AS [Month],
COUNT(*) AS [Transactions]
FROM
Transactions
INNER JOIN Blocks ON Transactions.block_num = Blocks.block_num
GROUP BY
YEAR(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
OK
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.
Last modified 2yr ago