Overview

Explorer is an Integrator add-on that supports exploring and visualizing any data regardless of the format and location, enabling you to provide users with the information that they need, in the context they want, when they need it the most.

How to start

In Integrator click the compass icon on the left navigation bar.

open explorer

Note: if you want to open Explorer in a separate tab right click on the compass icon.

Step 1. Select connection to explore data and meta data.

Step 2. Assign format to the connection if needed.

Step 3. Expand meta data objects, such as database tables, files, columns, etc.

Step 4. Select table, view, file or API endpoint and click the view data icon to view data in a grid format.

Step 5. Select connection or meta data object and click the develop SQL icon to write and execute SQL.

Step 6. Select file or API endpoint and click the raw data icon to see data in a raw (original) format.

User Interface

explorer

  1. Filters and Tags
  2. Toolbar
  3. Connections and meta data
  4. Data in a grid format
  5. Data in a raw (original) format
  6. SQL editor
  7. Information about connection or meta data object

Note: you can change the size of the panels 3 and 4 by dragging the divider between panels to the left or to the right.

divider

In the Explorer different information displayed in the different tabs. The following tabs are available:

INFO - information about connection, table, columns, etc.

SQL - SQL editor

DATA - data in a grid format

RAW DATA - raw data (content of the file, response from the web service, etc.)

explorer tabs

Connections and formats

Important: Explorer can not work with the connections that contain tokens, such as {params} in the URL. If you have a connection with the token, for example to customize URL, create a new one, with a hard coded URL pointed to the real resource.

There are two types of the connections in the Explorer

  1. The once, which don't need a format - database connections and APIs.
  2. The onces that require a format - file connections, web services, Redis.

If user will try to expand a connection that requires a format, Explorer will display a pop-up asking to select it. Once format is selected it will be associated with a connection. User can change a format by clicking on the format icon and selecting a new format.

format-selector

Important: only one format can be associated with a particular connection. If you use the same connection for the different types of data (for example JSON and XML files) you will need to create as many identical connections as the formats.

Connection Filters and Tags

By default Explorer displays all available connections. Just like everywhere else user can filter out connections by name. Just enter a part of the name in the Filter box. After this only the connections, having the entered string in their names, will be displayed.

Similarly, user can filter connections by tags. Click the small Tags filter icon on the far right of the filter box and select or enter tag names(s). Once tag filtering is applied the Tags filter icon will change color on the blue and only the connections, having the selected tag(s), will be displayed.

explorer tags

Important: actual tag names are not currently displayed in the Explorer.

Note: just like everywhere else if sticky filters are enabled Explorer will remember regular and tag filters you have entered and will keep them active.

Meta data Filters

When you are exploring meta data associated with a connection, such as tables, files, columns, etc. you can apply filters to these objects as well.

Click the small filter icon (if available) next to the group of objects and enter the part of the object name. After this only the meta data objects, having the entered string in their names, will be displayed.

meta data filter

Working with data grids

Important: Explorer can display up to 10000 (10K) rows for any given dataset.

The Maximum number of rows that can be displayed, and the actual number of rows currently displayed are shown in the status bar in the bottom of the data grid.

grid status

When data displayed in a grid format user can:

Resize columns - grab the column, drag it to the left or to the right.

Sort by any column - click the column header to sort by the selected column or change sort direction.

sort grid

Select all cells in the grid - select grid and press Ctrl+A.

select all

Copy selected cells - select cells and press Ctrl+C.

partial select

Keyboard shortcuts

The keyboard shortcuts are available for the most of the operations in the Explorer. Click the shortcuts icon in the toolbar to learn more.

shortcuts

Exploring meta data (tables, files, columns, etc.)

To explore meta data, associated with a connection, click the connection object. Drill down to the lower levels.

Explorer can display the following meta data objects:

Database connections - databases, schema objects, tables, views, columns

db metadata

File connections - files, columns

file metadata

Web Services and API connections - endpoint, columns

web service metadata

Redis connections - keys, columns

redis metadata

Inbound Email connections - files or subjects, columns

Visualizing data

To view data in a grid format, select object, such as table, view, file, web service endpoint, Redis key, etc., and click the view data button in a toolbar. The grid with the data will be opened in a separate tab.

data grid

If dataset is nested (most web services return nested datasets) Explorer displays a link with a label Dataset.

nested dataset in grid

By clicking the link you can drill one level down.

If you are one level below the root Explorer displays a Back button and a row from the previous level. Click the Back button to navigate one level up.

nested dataset back button

Form View

In addition to the grid you can view data in a from - one record at the time.

To view data in a form, while in a grid view double click in a row or click the Open Selected Row in a Form View button.

form view button

Export to File

While in a grid view it is possible to export data set to the any of supported file formats (CSV, JSON, etc.).

Step 1. Click the Export to File button.

export to file button

Step 2. Select available format from the list and click the Export button.

export to file

Writing and executing SQL queries

In Explorer you can write and execute SQL queries for any type of connection, including databases, files, web services, Redis, etc.

Note: you can change the size of the SQL editor panel but dragging the divider between panels up and down.

Databases

Step 1. Select database connection, or any node below it.

Step 2. Click the Develop SQL button in the toolbar.

write sql

Step 3. Write query, click the Execute SQL (ctrl+F2) button in the editor's toolbar.

Important: you can write queries using SQL dialect for the specific database. Explorer just passes queries to the databases without any pre-processing.

execute sql

Executing DDL and DML SQL statements

In Explorer, when database connection is selected, it is possible to execute DDL and DML SQL statements. For example:

update flow 
set name = 'test'
where flowid= 123

Just enter the statement and click the Execute SQL (ctrl+F2) button.

Explorer will display number of updated rows.

dml sql

Important: when working with databases Explorer automatically sets auto commit mode to true, regardless of the connection's settings. Therefore, the result of executing DML statements, such as update, insert, delete, etc. becomes visible to other users immediately.

Files, web services, Redis

One of the unique features of the Explorer is an ability to execute SQL queries on a dataset of any type, including files, web services, etc.

Step 1. Select connection, other than database connection.

Step 2. Drill down to the particular file, endpoint or Redis key.

Step 3. Click the Develop SQL button in the toolbar.

file sql

Step 4. Write query, click the Execute SQL (ctrl+F2) button in the editor's toolbar.

run file sql

Writing SQL queries for files, web services or Redis key-values

Writing SQL queries to be executed on a dataset of any type is not much different from writing queries to be executed on a relational database:

Using aggregation functions

Example:

select "Order Number" OrderNum, "Order Date" as OrderDate, sum(amount) OrderAmount
group by "Order Number"

Note using of group by expression, together with SUM(amount).

The following aggregation functions are supported:

The group by expression can include one or multiple (coma separated) field name(s).

Note: expressions are not supported as parameters for the aggregation functions.

Writing queries for nested data objects.

Lets assume that there is a nested data object, returned by the web service.

{  
   "requestId":"185d6#14b51985ff0",
   "success":true,
   "result":[  
      {  
         "id":"433455454",
         "name":"all fields", 
         "searchableFields":[  
            {  
               "name":"field1",
               "displayName":"Just field 1",
               "dataType":"string",
               "length":36,
               "updateable":false
            },
            {  
               "name":"field2",
               "displayName":"Just field 2",
               "dataType":"string",
               "length":50,
               "updateable":false
            },
         ],
         "fields":[  
            {  
               "name":"leadId",
               "displayName":"Lead Id",
               "dataType":"integer",
               "updateable":false
            },
            {  
               "name":"role",
               "displayName":"Role",
               "dataType":"string",
               "length":50,
               "updateable":false
            },
            {  
               "name":"isPrimary",
               "displayName":"Is Primary",
               "dataType":"boolean",
               "updateable":true
            },
            {  
               "name":"externalCreatedDate",
               "displayName":"External Created Date",
               "dataType":"datetime",
               "updateable":true
            }
         ]
      }
   ]
}

The goal is to write a query, which extracts all searchableFields and fields from the nested data object.

It can be easily done using the following SQL query, applied to the nested source data object:

select name, displayName, 'searchable' as fieldType
from result.searchableFields
union 
select name, displayName, 'regular' as fieldType
from result.fields

The key here is added from clause. Basically, when writing a query to be executed on a nested data object, you can specify a level from which data will be extracted. Use . as a delimiter to drill down into the nested hierarchy of objects.

When querying data from the nested data object, you can use union and intersect, granted that the list of fields is the same for all queries combined by union or intersect.

If you want to include some of the fields from the parent object, together with the fields from the nested object use owner. qualifier:

select result.name as apiName, searchableFields.name, searchableFields.displayName, 'searchable' as fieldType
from result.searchableFields
union  
select result.name as apiName, fields.name, fields.displayName, 'regular' as fieldType
from result.fields

Executing multiple SQL statements

You can write and execute multiple ; separated SQL statements. Result for each statement will be displayed in a separate tab as Dataset1, Dataset2, Message, etc.

Example:

update flow 
set name = 'test'
where flowid= 123;

select * from flow;

select * from format;

multiple SQL statements

Executing selected SQL statements

If something is selected in the SQL editor only selected SQL will be executed.

selected SQL

History

Explorer records the history of the executed SQL statements, as well as who executed them, what was the result (success or error) and when did it happen.

History is recorded separately for each connection but shared between users.

User can copy SQL from the History sidebar to the editor's window.

To toggle History sidebar click the Hide/Show Execution History button in the SQL editor toolbar.

toggle history

There are 3 button available for each entry in the SQL history:

Copy to Editor - inserts text into the current position in the editor.

Move to Editor - replaces current text in the editor.

View - opens a popup window that displays SQL.

history toolbar

Formatting SQL

Explorer can format (prettify) SQL in the editor. To format SQL click the Format code button in the editor's toolbar.

format sql

Using drag and drop

Accessing data in the raw format

When working with files, web services, APIs and Redis, Explorer can display data in the raw format, such as JSON, HL7. etc.

Important: displaying data in the raw format is not available for the database connections.

Step 1. Select connection, other than database connection.

Step 2. Drill down to the particular file, endpoint or Redis key.

Step 3. Click the Raw data (f) button in the toolbar.

display-raw-data

Step 4. Choose between downloading file or displaying content of the file in the Explorer.

raw data