Using Workbooks in Workbench

Explorer Panel

On the left side of the screen, you will see a tabbed panel, which we call the Explorer. The Explorer has three main views: Data, Workbooks, & Files.

The Data Explorer UI displays all of the data you have in Kinetica, like tables, views, graphs, and data sources. The data objects are sorted by type, allowing you to minimize or expand each section, and filter through the object names using a text input.

The Workbook Explorer UI provides a means to organize a SQL-based set of commands or workflows for development or presentation purposes.

The File Explorer UI allows browsing of the Kinetica FileSystem (KiFS), where data files uploaded to Kinetica reside.

Data Explorer

When a data object is created using the UI-based wizard, it will appear in the Data Explorer automatically. When creating an object using a SQL Block, the Data Explorer will refresh automatically after the statement has been executed. Any queries executed in a worksheet that do not return rows will trigger a refresh of the Data Explorer to ensure you have the latest information about the system. The Data Explorer will show newly created objects, but if you do not see one listed, use the Refresh button at the top right of the Data Explorer to retrieve the latest information manually.

../images/wb.explorer_data.png

Data Explorer Tab

Creating and Modifying Data Objects

Create new data objects, like tables, data sources, or graphs, by clicking the plus + button at the top of the Data Explorer, or next to any of the data object type headings. You will be walked through options in a wizard. To modify an existing object, click the object and select Configure.

../images/wb.explorer_data_popup.png

Data Explorer Table Actions

../images/wb.create_table.png

Create Table Wizard

Previewing Objects

You can preview an object’s data by clicking and selecting Data Preview. For tabular data, you will be shown a table view of the data.

../images/wb.data_preview_grid.png

Data Grid Preview

You can also preview geospatial tabular data on a map by selecting WMS Preview. Abstract graphs, like social graphs cannot be previewed in the Workbench. Previewing a geospatial graph will display a map of the graph.

../images/wb.data_preview_wms.png

WMS Data Preview

Deleting Objects

To delete an object, click the object and select Delete.

Workbook Explorer

The Workbook Explorer contains two lists of workbooks:

  • My Workbooks - a list of workbooks you've created, and the Quick Start Guide, which comes with each Kinetica deployment
  • Shared With Me - a list of workbooks others have created and made available to you
../images/wb.explorer_workbooks.png

Workbook Explorer Tab

Creating a New Workbook

To create a new workbook, select the Workbook tab in the Explorer, then click the plus + button. Alternatively, you can create a new workbook using the large button on the Explore landing page.

After giving it a name, the new workbook will be displayed in the right-hand pane with a single empty SQL Block to start.

../images/wb.workbook_new.png

New Workbook

Example Workbook

Kinetica ships with a workbook that showcases an end-to-end analytics example through SQL. In this workbook, you will ingest data from S3, perform several analytic queries, and finally visualize the output on a map. To open the example workbook, click the Workbooks tab in the Explorer and select Quick Start Guide.

Exporting/Importing Workbooks

You can export and import workbooks to share your SQL. Right-click on a workbook in the Workbook Explorer and select the Export menu option. Choose Workbook JSON to initiate the download in JSON format.

../images/wb.workbook_export.png

Export Workbook

To import a workbook, select the plus + button at the top right of the Workbook Explorer. This will open a dialog window that allows you to drag and drop a previously exported workbook.

../images/wb.workbook_import.png

Import Workbook Menu

File Explorer

The File Explorer can be used to manage directories & files that reside in Kinetica File System (KiFS). This feature facilitates uploading local data files to import their data into Kinetica.

../images/wb.explorer_files.png

File Explorer Tab

Uploading a File

To upload a data file, select the Files tab in the Explorer to reach the File Explorer. Then, proceed either one of two ways:

  • Click the plus + button at the top of the File Explorer panel and then click Upload New File
  • Right-click the directory to upload the file into, and then click Upload New File

At this point a modal will open up, prompting the selection of a data file to upload from the local file system. Choose a file and then click Open. Then enter (or confirm) the name of the directory to upload the file to and, optionally, choose a different name for the uploaded file in KiFS. Click Upload to complete the file upload, and Close to close the modal.

../images/wb.explorer_files_upload.png

Upload File Dialog

Importing a File

To import a file from KiFS, select the Files tab in the Explorer to reach the File Explorer. Then, right-click the name of the file to upload and click Import.

The Kinetica Filesystem Import wizard will be displayed with the name of the file to import already populated in the File field.

../images/wb.explorer_files_import_setup.png

Import File

Click Import at any step of the wizard to import the file with the default options, along with any that have been configured in previous steps. See Import Data Wizard for the step-by-step walkthrough of the wizard.

SQL Workbooks and Worksheets

Workbooks can be accessed via the Explore page, using the top navigation.

This page will be used in the future to import workbooks from Kinetica’s Workbook Repository.

../images/wb.explore.png

Explore Landing Page

Workbooks

A workbook is a container for worksheets.

A worksheet may contain one or more SQL Blocks, Text Blocks, and Map Blocks. These allow you to organize SQL statements and comments for application development or presentation of a workflow.

Any type of Block can be moved up and down in the sequence or removed using the buttons in the upper right corner of the Block. You can also add another Block directly below an existing one by using the plus + button in the upper right menu.

../images/wb.workbook.png

SQL Workbook

SQL Blocks

A SQL Block should contain a single SQL statement, which can be independently evaluated.

Each SQL Block has a play button on the left side that allows you to run the SQL contained in the block. Alternatively, you can run all of the SQL Blocks in a worksheet in sequence by clicking the Run All button at the top of the screen.

If you want to create a visualization for a SQL Block, you will first need to create and run a SELECT statement. See Visualizations for directions on how to create visualizations in your workbook.

../images/wb.workbook_sql_block.png

SQL Block with Output

Type-ahead

SQL Blocks feature a type-ahead capability that will suggest SQL syntax and database object names. When you begin typing, keywords will appear and you will be able to select one of the items using the arrow keys. Pressing Enter while one of these keywords is highlighted will add it to your SQL Block.

For database objects, simply type a schema name and a dot (.); you should see all of the tables and views in that schema appear in a dropdown list.

Running Individual Blocks

To run a SQL Block, click the play button next to it. When the job has finished, you will see an output notifying you of the success or failure. If you run a SELECT statement, you will see the result data presented in a data grid format, along with other visualization options. The data grid and visualizations are directly tied to the corresponding SELECT statement in the SQL Block.

Running All Blocks

You may also run the entire workbook from start to finish, in sequence, by clicking the Run All button in the upper right of the worksheet. As each SQL Block completes, the next will run until an error or the end of the workbook is reached.

Canceling Jobs

If you want to stop a long-running query or a long sequence of SQL Blocks, you can easily cancel the running jobs associated with each query. Use the Queries button at the top of the screen. This will open a modal with a list of all of the running jobs in the system, any of which can be stopped by checking the box next to the job and pressing the Cancel Selected button.

../images/wb.jobs.png

Running Jobs Listing

Visual Explain Plan

The explain plan visualizer is a graphical complement to using the EXPLAIN keyword in a given query, providing visual detail to the SQL query's execution plan, execution time, and native API translation(s).

In a given SQL Block, use the hierarchy icon to select Explain Plan. This will not execute the query, but allow the database to walk through the native actions necessary to execute the query and show the plan.

Use the hierarchy icon to select Run and Analyze to execute the query and display the execution time for each step in the plan.

../images/wb.workbook_sql_explain.png

Visual Explain Plan

Text Blocks

Text Blocks help you annotate the SQL code in a worksheet. They serve as comments for SQL Blocks, or as a way to help you tell a story with your worksheet. A Text Block is not attached to a SQL Block, so it can be moved up and down independently. There are several text formatting options available, including bold, italic, and underline.

Map Blocks

A Map Block starts out as an empty map, but can be configured to display geospatial data from a table.

Click on Configure to pick a source table and longitude & latitude columns. Then click Update to display the data on the map.

../images/wb.workbook_vis_map.png

Map Block

Visualizations

Once you have run a SQL Block that contains a SELECT statement, you have the option to visualize the result data. By default, the data will be displayed as a grid in the Data tab below the SQL Block. If you select the Visualization tab, you can visualize the data using one of the following modes:

../images/wb.workbook_vis.png

Visualization Selector

Choose a visualization style, click Configure to set up the graph, and then click Update to display the graph.


Bar Chart

The Bar Chart Visualization allows you to view volumes of data by group.

../images/wb.workbook_vis_bar.png

Bar Chart Visualization

Line Chart

The Line Chart Visualization shows you trends across a dimension.

../images/wb.workbook_vis_line.png

Line Chart Visualization

Pie Chart

The Pie Chart Visualization shows you the numeric proportion of values across several groups.

../images/wb.workbook_vis_pie.png

Pie Chart Visualization

Importing Data

Whether you’re importing directly via SQL, or using the import wizard, loading data into Kinetica is easy. Several data sources are supported when ingesting data through either mechanism. See Data Sources for more information on data sources or CREATE DATA SOURCE for managing them in SQL.

Import Data Wizard

Importing data via the wizard can be done in several easy steps.

  1. Click Import on the top menu to reach the Importing Your Data page.
  2. Choose the location of your data:
    • File Upload - Import data from a file on your local file system
    • Kinetica Filesystem - Import data from a file that has already been uploaded to KiFS
    • Azure Blob Storage, Amazon S3, Kafka Stream, HDFS - Import data from one of these external providers.
  3. If ingesting from a external provider like S3, you can either select an existing data source corresponding to that provider or create a new one.
  4. Enter the path to the file, and choose the file format.
  5. Next, select the schema in which your new table should be created. Then give your new table a name. By default, records that fail to be parsed or loaded correctly will be skipped and the import process will proceed to the subsequent records. To stop the import if an error occurs on a record, check the appropriate box. Optionally, define the structure of the target table if that table does not yet exist. If the table doesn't exist and the structure is not defined here, it will be intuited by analyzing the source file headers & data.
  6. Once the ingest has been configured, the corresponding SQL statement for loading the data will be displayed, which you can copy into a worksheet for use later. Click Import to begin the import immediately, and wait for the job to complete. You can see the status of jobs in the History tab, and you can see error messages to help you troubleshoot the import there as well.

For a more detailed look at the configuration options available through the Import Wizard, see Import.

../images/wb.import.png

Import Landing Page

../images/wb.importing_data_summary.png

Import Data Summary, with Corresponding SQL Import Statement

Import via SQL

You can use the SQL generated by the Import Data Wizard in your SQL Worksheets, or you can write your own ingest statements. When importing from a remote location, like S3, you will need to create a data source first. See CREATE DATA SOURCE for the syntax used to create one.

For example, to load taxi data from S3:

Create an S3 Data Source 
1
2
3
4
5
6

CREATE OR REPLACE DATA SOURCE quickstart
LOCATION = 'S3'
WITH OPTIONS (
    BUCKET NAME = 'quickstartpublic',
    REGION = 'us-west-1'
)

Once you’ve created a data source, you can load data using the LOAD INTO statement.

Load Taxi Data from S3 
1
2
3
4
5
6

LOAD DATA INTO taxi_data_historical
FROM FILE PATHS 'taxi_data.parquet'
FORMAT PARQUET
WITH OPTIONS (
    DATA SOURCE = 'quickstart'
)