6. SQL Editor

6.1 What is the SQL Editor?

The SQL Editor is a databricks UI where one can author queries, browse available data, and create visualizations. One can also share saved queries with other team members from the SQL Editor UI.

When you run a SQL query for NS purposes, you will be using the ns-ii-tech-dev-sql SQL warehouse by default. This warehouse will start automatically your run any SQL query.

6.2 Practical

Querying available data

The good thing with the SQL Editor is that it is integrated with the catalog. Therefore, whatever is in the catalog, it can be queried from the SQL editor!

For example, below is an SQL query file called si_community. It contains the following SQL query:

-- Test SI community
SELECT * FROM `ns-ii-tech-dev-catalog`.bronze.si_community_sublayer_survey_2024_06_19__06_08_54;

This query is simple, it only retrieves the data for the SI community survey subtable. Click the blue button Run selected to view the results of your query. They will appear as a table at the bottom.

To save a query, using for example this si_community tab, you click on the Save button. Follow the prompts. You may need to create an extra folder in your workspace name to save the sql files in a special place. For example, there is a special folder for sql queries called ns_queries under this author’s workspace folder.

One can also schedula a SQL query to run at pre-defined intervals. Click on Schedule>Add schedule and set your schedule. Thereafter click Create.

One can also share the SQL tab you were using. Simply click Share and a new UI will come up. Use this UI to specify people or groups to share to and their permission.

Complex queries

One can also run more complex queries, so long as they are SQL supported in the SQL tab.

Creating new tables or views

With the SQL Editor connected to the catalog, it can not only read and modify data, but also create new data.

For example, the following query will create a new view called xprize_acoustic_data_time within the bronze_views folder. The query format a date and time column into a specific format. This will be a new view since such data was not available before.

It is recommended to perform a sanity check to see if your view or table creation was successful.

Creating visualizations

If you run a query successfully, the results will appear as a table. What if you want to create a chart out of the resultant table?

Click on the + next to the Raw Results output and select Visualization.

A new UI will popup. Select your visualization type such as Bar, Pie chart or any other. Fill in the dialog boxes and perform any necessary customizations for your data to come out clean, aesthetic and appealing.

One can decide to download (there is an arrow button at the top right) to your local PC. Clicking on Save will display the visualization on the results panel like below.

You can click the dropdown next to your visualization results table from where you choose which format to save in, such as csv or adding to a dashboard!