Explorer 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 the connection in which you want to explore data and metadata.

Step 2. Assign a format to the connection if needed.

Step 3. Expand the metadata objects, such as database tables, files, columns, etc.

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

Step 5. Select a connection or metadata object and click the Develop SQL icon to write and execute SQL.

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

User Interface

explorer

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

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

divider

In Explorer, different information is displayed in each tab. The following tabs are available:

INFO

Information about a connection, table, columns, etc. When in the INFO tab click the View/Edit button to view or edit connection parameters.

view or edit connection

SQL

The SQL editor

DATA

Data in a grid format

RAW DATA

Raw data (the contents of the file, response from a web service, etc.)

explorer tabs

Connections and formats

Important: Explorer cannot work with connections that contain tokens, such as {params} in the URL. The tokens can be used, for example, to create dynamic URL. Instead, create a new connection, with the hard-coded URL pointing to the real resource.

There are two types of connections in Explorer:

  1. The ones which don't need a format - such as database connections and APIs.
  2. The ones that do require a format - such as file connections, web services, Redis, MongoDB, etc.

If the user tries to expand a connection that requires a format, Explorer will display a pop-up asking them to select a format. Once the format is selected, it will be associated with that connection. The user can change the 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 different types of data (for example, JSON and XML files), you will need to create as many identical connections as there are formats.

Connection Filters and Tags

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

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

explorer tags

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

Note: As is true elsewhere, if sticky filters are enabled, Explorer will remember the regular and tag filters you have entered and will keep them active.

Metadata Filters

When you are exploring metadata 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 part or all of the object's name. After this, only the metadata objects having the entered string in their names will be displayed.

metadata filter

Working with data grids

Important: Explorer can display up to 10000 rows for any given dataset.

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

grid status

When data is displayed in a grid format, the user can:

Resize the columns - grab the divider to the left or right.

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

sort grid

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

select all

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

partial select

Keyboard shortcuts

Keyboard shortcuts are available for most of the operations in Explorer. Click the Shortcuts icon on the toolbar to learn more.

shortcuts

Exploring metadata

To explore metadata associated with a connection, click the Connection icon. By clicking the link, you can drill down a level, which can be repeated.

Explorer can display the following metadata objects:

Database connections

Metadata example showing databases, schema objects, tables, views, and columns:

db metadata

File connections

Metadata example showing files, and columns:

file metadata

Web Services and API connections

Metadata example showing endpoint and columns:

web service metadata

Redis connections

Metadata example showing keys and columns:

redis metadata

Inbound Email connections

Metadata will show subjects, attachments, and columns.

MongoDB connections

Metadata will show documents and columns.

Exploring data

Viewing data in a Grid

To view data in a grid, select the object, such as a table, view, file, web service endpoint, Redis key, etc., and click the View data button on the toolbar. A grid with the chosen data will open up in a separate tab.

data grid

If a dataset is nested (for instance, most web services return nested datasets) Explorer will display a link with the label Dataset.

nested dataset in grid

By clicking the link you can drill down one level.

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 up one level.

nested dataset back button

Viewing data in a Form

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

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

form view button

The record will be displayed in a form.

form view

Exporting a dataset to a File

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

Step 1. Click the Export to File button.

export to file button

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

export to file

Viewing data in a Raw Format

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

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

Step 1. Select a connection, other than a database connection.

Step 2. Drill down to the particular file, endpoint, Redis key, or MongoDB document that you are interested in.

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

display-raw-data

Downloading Files

To download a file associated with a connection

Step 1. Select a connection, other than a database connection.

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

Step 3. Click the Download file button on the toolbar.

download file

Uploading files

The term "Local files" refers to files in a storage device attached to your computer, or on a network device available from your computer.

In Integrator, you can upload local files into any available file storage or cloud storage, including server storage.

Step 1. Open Explorer and select the connection you want to upload local files into. It can be any available file storage, cloud storage, Redis or POST/PUT HTTP endpoint connection. The connection must have a linked format.

Connection to upload local files

Step 2. Click the Upload Files (u) button.

Upload files

Step 3. Drag and drop one or more files into the drop zone, or click the Choose Files link and select files to upload. Integrator will start uploading the files immediately and will display upload progress. Note: each file will be uploaded separately.

Files being uploaded

Important: the maximum size of a single file which can be uploaded is 10 Mb. Please contact Etlworks if you need to upload bigger files.

Step 4. Close the drop zone and click the Refresh (r) button to see just the uploaded files.

Refresh files

Using SQL

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 by dragging the divider between panels up or down.

Databases

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

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

write SQL

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

Important: you can write queries using the SQL dialect for that specific database. Explorer just passes the queries to the database without any preprocessing.

execute the SQL

Executing DDL and DML SQL statements

In Explorer, when a 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 an SQL statement and click the Execute SQL (ctrl+F2) button.

Explorer will display the number of updated rows.

DML SQL

Important: when working with databases, Explorer automatically sets the 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, and Redis

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

Step 1. Select a connection, other than a database connection.

Step 2. Drill down to a particular file, endpoint or Redis key, by clicking on the database connection.

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

file SQL

Step 4. Write query, then click the Execute SQL (ctrl+F2) button on 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

For example:

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

Note: use a group by expression, as well as SUM(amount).

The following aggregation functions are supported:

The group by expression can include one or more comma-separated field name(s).

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

Writing queries for nested data objects.

What if you have a nested data object that is returned by the web service, such as the following?

{  
   "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 will extract all searchableFields and fields from the nested data object.

It is easy to accomplish using the following SQL query, which will be applied to the nested source data object (above):

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

The additional from clause is very important. When writing a query to be executed on a nested data object, you can specify the level from which the data will be extracted. Use . as a delimiter to drill down into the nested hierarchy of objects.

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

If you want to include some of the fields from the parent object, together with the fields from the nested object use the 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. The results for each statement will be displayed in a separate tab as Dataset1, Dataset2, Message, etc.

For 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 the selected SQL will be executed.

selected SQL

History

Explorer records the history of executed SQL statements, as well as who executed them, the result (success or error) and the time.

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

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

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

toggle history

There are 3 buttons available for each entry in SQL history:

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

Move to Editor - replaces the current text in the editor.

View - opens a pop-up window that displays the SQL.

history toolbar

Formatting SQL

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

format SQL

Using drag and drop

You may: