Skip to main content

Exploring Data

Once you have created a notebook, you can begin exploring data. This is typically accomplished by using SQL to query data from tables and display the results within a notebook paragraph. Scala and Python can also be used to query data, but using SQL, either through Magpie's scripting language, or directly in a SQL interpreter block provides formatted tabular output and the ability to do basic data visualization.

  • When using SQL through Magpie there is a bit more flexibility in how data can be used for subsequent operations and there is more control over the number of results returned.
  • When using SQL with the SQL interpreter, the syntax is somewhat simpler, however there is a strict limit of 1000 results returned.

Magpie SQL SELECT statement very closely follows the SQL syntax used by Spark SQL.

Setting the Schema​

The first step in querying data in Magpie is setting a schema to use. This is accomplished via the USEcommand as shown below.

USE SCHEMA schema_name;

Once the schema is set, it will be persistent across subsequent commands in the same paragraphs and other paragraphs within the notebook.

Listing and Describing Available Table Structures​

Once you have selected a schema, you can list the tables available in the schema by using the LIST command. This will show a list of tables in the active schema as shown here.

A notebook paragraph running "list tables;", with the results listing crime_incidents tables for 2011 through 2018

Alternately you can describe a schema using the DESCRIBE command.

Output of "describe schema washington_dc", showing the schema name, display name and description above a searchable list of its tables

You can get more detail about the columns available in the tables using the DESCRIBE command.

Output of "describe table building_permits", showing table attributes, a field list with data types, and the persistence mapping to a Parquet file on S3

The describe command returns the data types for each column as well as the persistence mapping associated with the table.

Using SQL with the Magpie Interpreter​

As noted above, SQL queries can be run either within a Magpie interpreter paragraph or in a SQL interpreter paragraph. When using the Magpie interpreter, you need to explicitly tell it that you are using SQL and that the results should be shown immediately.

Note: Magpie, like the underlying Spark platform, defers the execution of a data transformation or query until the results need to be used in a downstream process, for display, or to be saved to a file. This means that when using the Magpie DSL, execution often needs to be triggered by a show statement.

The screenshot below shows the typical syntax for querying data using the Magpie interpreter.

A "show 1000 from sql (...)" query returning a results grid of longitude, latitude, ward, permit subtype and fees paid, footed by "1000 of 24553 results returned"

Note the SHOW syntax at the beginning of the statement. This forces the interpreter to actually retrieve and display the data. The WITH COUNT clause at the end of the statement causes the total number of rows in the result to be displayed even if only the first 1000 rows are being shown.

The results of the last Magpie command executed are held in a special temporary table called name result. The result table can then be referenced in subsequent commands.

Two paragraphs: a sql(...) statement that is parsed but not run, then a "show" that queries the result table and returns fees summed by issue date

The ability to reuse the results of a query will come into play later, particularly when saving data to new tables and when you begin to build more complex data processing flows.

Using SQL with the SQL Interpreter​

When using the SQL interpreter, you can simply type in a selectstatement as shown here.

A paragraph using the %sql interpreter to run "select * from building_permits_2018", returning a results grid

As mentioned above, the number of rows returned is limited to 1000. This means that specifying a sort order is often useful to avoid returning a unpredictable, unsorted set of results.

Using the Tabular Data Display​

By default, the results are placed in a table display in the results area of the paragraph. By default, the columns are unformatted. By clicking on the down arrow button in each column, you can choose a data format to use. You can also sort on a column by clicking anywhere else on the header column.

A results grid with a column header dropdown open, offering text, numeric and date as the sort type for that column

Setting the data type is useful when higher precision numbers need to be displayed, and when there is a need to sort on numerical values. Sorting within the table on a numeric column without specifying the format will cause the numbers to be sorted as text leading to wrong results.

Visualizing Results​

Query results can also be displayed as charts. Simply select a chart type using the chart icons at the top of the results area of the paragraph.

A line chart of daily permit fees for June and July 2018, with the chart settings panel above it showing issue_date on the x-axis and total_daily_fees summed on the y-axis

Once you have selected a visualization, the columns to display can be adjusted by clicking on the settings button to expand the options, and dragging and dropping them into one of the boxes above the chart as shown above.

Using Magpie's Geospatial Visualizations​

If you have a result set that contains columns labeled latitude and longitude, Magpie will automatically populate a geospatial visualization. This can be accessed by clicking on the map pin icon at the top of the query results display.

A results grid with the longitude and latitude columns highlighted, and the map icon highlighted in the visualisation toolbar

This will open a new tab with a map view of the data in it.

A map of Washington, DC scattered with permit locations shaded by fee amount, a layer settings panel on the left and a tooltip showing one permit’s ward, subtype and fees paid

This view can then be customized to visualize the data in different ways using the controls on the left hand side of the screen.