Warning
This is not a recommended or supported process. It was done as a proof of concept only, and has significant limitations. As Power Query attempts to pull the entire result set of the query onto the client, this process does not scale to "big data" amounts of data.
Prerequisites
This POC was done using the Imply Quickstart. You can find instructions on setting up the Quickstart here: https://docs.imply.io/on-prem/quickstart
It was completed using Microsoft Excel 2019 on Windows. Power Query used to be called "Get and Transform" in Excel 2016 and was not available in previous versions and at the time of writing, was only available for Windows.
Alright... let's get into it.
From Excel go to Data-->Get Data-->From Other Sources -->Blank Query
With Power Query open enter the following expression, replacing the IP address with your Broker IP or hostname and the query value with your SQL query.
= Csv.Document(Web.Contents("http://192.168.56.1:8888/druid/v2/sql/", [Headers=[#"Content-Type"="application/json"], Content=Text.ToBinary("{""query"": ""select * from wikipedia limit 10000"",""resultFormat"":""csv"",""header"":""true""}")]))
Pressing the Check Mark will execute the query and display a preview
You will notice that the headers are not in the header row, so the next step is to click Use First Row as Headers
After that is complete you can give your query a name, and click Close & Load
And there you have it, you have extracted data from Apache Druid into Excel.
Comments
0 comments
Please sign in to leave a comment.