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.

Writing a SQL query

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"

Use your HiveSQL account login and password (see Register your HiveSQL account)

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.

Finalizing the presentation

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.