Discover SQL Notebooks

Business stakeholders will want to know the top five airports–those with the most number of flight delays. We’ll write a query to find this information. To do this, we’ll use an SQL notebook.

SQL notebooks are interactive environments for performing queries on all SQL databases supported by Dataiku DSS. SQL notebooks support all kinds of SQL statements, from the simplest SQL queries to advanced DDL, and stored procedures.

Create a New SQL Notebook

Let’s create an SQL notebook with three queries. The purpose of the notebook will be to create a metric that finds the top five airports by volume of flight delays.

• From the top navigation bar, go to the Code menu, then choose Notebooks.

• At the top right, click + New Notebook, then choose Write your own.

• Select SQL and set the connection to the same SQL connection as the datasets in the Flow.

• Name the notebook compute_top_five_airports.

• Click Create.

Now we’ll add three new queries to the notebook.

Add A Query to Select All Rows from Flight Data Input

Let’s start with a basic query to ensure we have the right set up.

• Click + Query, and name it, Select ALL from Flight Data Input.

• In the left panel, click Tables to view the tables.

• Click the + next to the flight_data_input table to create the query.

• Run the query.

Note

You can always use ${projectKey} to tell Dataiku DSS to use your specific project key.

Add A Query to Count the Rows

• Click + Query, and name it, COUNT (*).

• Using the list of tables, create a query to compute the count of rows from flight_data_input.’

• Run the query.

Add A Query to Output the Top Five Airports

• Click + Query, and name it, Top 5 Origin.

• Using the list of tables, create a query to output the top five origin airports in descending order.

• Run the query.

Organize the View

Let’s change the view from full-screen to stacked-queries mode and organize our view so we can see all the queries at once. To do this:

• Click the Stacked queries mode icon.

• Collapse each query.

Using this view, we could check the status of and run each query.

For more information about SQL notebooks, visit Creating a SQL Notebook.