Leveraging the new Hue’s SQL Scratchpad Web Component and REST API into your own project.
The Hue SQL Editor project has been evolving for more than 10 years and allows you to query any Database or Data Warehouse. Segmenting the overall project into fully decoupled components is one reason it could rapidly evolve and still be alive after so long.
The already popular SQL Parser component is now joined by the SQL Scratchpad component.
The SQL Scratchpad is a lightweight repackaging of the mature Hue SQL Editor
One major added benefit is that now the Editor is easy to share and integrate, hence making a strong case for avoiding to re-invent the wheel and re-create various duplicated SQL Editors instead of focusing on making a single one better for the end users.
Here is a live demo of how easy it is to add the component:
Adding the component in 3 lines and watching the interaction with the public API of demo.gethue.com
How it works
The SQL Editor is a module published to a registry called NPM. The component can then be integrated in any Web page. It then communicates via a REST API with the Hue server which interacts with the Databases we want to query.
Query Editor Component
Here is how easy it is to integrate it into a Web page. Copy paste the HTML code below into an index.html file and open it with FireFox:
<!DOCTYPE html>
<html>
<head>
<title>SQL Scratchpad</title>
<script type="text/javascript" src="https://unpkg.com/gethue/lib/components/SqlScratchpadWebComponent.js"></script>
</head>
<body>
<div style="position: absolute; height: 100%; width: 100%">
<sql-scratchpad api-url="https://demo.gethue.com" username="demo" password="demo" dialect="hive" />
</div>
</body>
</html>
And “that’s it”!
Directly opening the local HTML page with Firefox
The autocomplete is powered by the local parser and will show-up. It obviously can only help with the SQL syntax on its own. In order to get the dynamic content like the list of tables, columns… and execute SQL queries, the component needs to point to a Query API.
For the next steps and integrating deeper the component, have a look to the NPM Hue registry.
Query Editor API
To be truly alive, the Editor component needs to talk to the service that can authenticate the user and execute the queries.
This is possible via the new public REST API which is much simpler to use than before and follows nowadays standards. It leverages the exact same authentication as the regular login page and only requires to provide a JWT token afterwards.
For example, simply ask for an access token:
curl -X POST https://demo.gethue.com/api/token/auth -d 'username=demo&password=demo'
Then provide the token value in each following calls.
curl -X POST https://demo.gethue.com/api/editor/execute/hive --data 'statement=SHOW TABLES' -H "Authorization: Bearer <token value>"
The endpoints for executing SQL queries have been greatly simplified and now only ask for the essential like which SQL dialect and query statement or the id of the query previously sent for execution:
curl -X POST https://demo.gethue.com/api/editor/check_status --data 'operationId=63ce87ba-ca0f-4653-8aeb-e9f5c1781b78'
This first release lets you edit and execute SQL content. Popular functionalities of the full editor are gradually being integrated, e.g.
- Query formatting and sharing
- Result downloading
- Open Scratchpad in a popup (e.g. for editing embedded SQL in PySpark)…
An option to SSO and look-up a local JWT token in order to not require the credentials is coming soon.
The SQL Scratchpad component and its API are rapidly evolving. Now is a great time to give it a try and send feedback or contribute!
Onwards!
Romain from the Hue Team