Extensible Source Connections

This topic describes how to configure and use Extensible Source Connections. This connection type enables any data source type to be browsed and ingested into your data warehouse, as long as your sources have Windows scriptable APIs or command-line tools available or both. Examples of source types made available by this feature are REST/SOAP Services, JDBC, Cloud-Based Storage, JSON/XML/Avro, and many more.

Prerequisites

Before you can create an Extensible Source Connection you must first have setup:

1. A matching set of UI Configurations for Connection Properties and Load Table Properties.
2. Browse Script that utilizes these configurations.
3. Windows Connection to run the Browse Script against.

Additionally, you would need a Load Template that works with your Extensible Source Connection and its configured field metadata to load data into your data warehouse from that source. Please see UI Configurations section for more information on accessing the configured field metadata through Templates and Scripts.

For detailed information on the prerequisites involved please review the following sections:

• UI Configurations
• Browse Scripts

RED provides examples for each of the prerequisites above that all form a working example of an SQL ODBC connection utilizing these features. Please follow the instructions here to create each of the examples to build the set.

Creating Extensible Source Connections

After you set the prerequisites described above, add a new Extensible Source Connection by following these steps:

1. To add a new Extensible Source Connection, right-click Connection located on the left side panel, and select New Object.
2. After adding an object name for the new connection, the Properties dialog displays. Under the Connection Type, all the Connection UI Configurations loaded into your repository are displayed in the drop-down list together with the existing connection types.
3. Selecting a Connection UI Configuration name from the Connection Type list will enable three new required connection properties: Connection Browse Script, Script Connection, and Load Table UI Configuration.

• Connection Browse Script: The Windows-based Host Script that will be executed by the 'Browse' action for this connection.
• Script Connection: The Windows connection for the Browse script to execute against.
• Load Table UI Configuration: The Load Table UI configuration is applied to Load tables sourced from this connection.

The fields presented in the Connection Properties screen below depend on the connection, see below the available options when selecting a UI Configuration connection:

The Configure Source Connection Properties window has the following fields:

General

Other

The Text Box Fields, List View Fields, and Numeric Fields displayed in the screenshot are field configurations taken from the examples found in Field Configuration JSON.

The Column UI Configuration selection is only available on Target Enabled Connections and not Extensible Source Connections. For more information, review Connection Target Settings.

Loading from Extensible Source Connections

To load Load Tables which were created from an Extensible Source Connection you need to develop a load template that has logic in it to work with that connection type.

WhereScape provided Enablement Packs containing Extensible Source Connections will also have the relevant load templates to match. This section briefly explains how to determine the Connection Type from load templates so that you can generate the correct routine to load the table.

The Name of your Connection Properties UI Configuration is used as the Connection Type, so it is important to name your UI Configurations meaningfully.

Here is a pebble template macro to output the Connection Type as a string:

{% macro GetConnectionType(con = table.loadInfo.sourceConnection) %}
{%- fetch con -%}
{%- if con.connectionPropertiesConfig is defined -%}
{{ con.connectionPropertiesConfig }}
{%- else -%}
{{ con.connectionType.name }}
{%- endif -%}
{% endmacro %}

To use this macro to set a variable in PowerShell syntax:

$sourceConnectionType=’{{ GetConnectionType() }}’

To use this macro in a pebble template conditional block:

{% if GetConnectionType() | trim == "ODBC" %} # add your ODBC specific code here {% endif %} 

Note that there are shortcuts to these items in the table.loadinfo node but the macro provided above is generic and suitable for any routine generation context. The equivalent pebble template shortcuts available to Load Table routine generation are:

{{ table.loadInfo.sourceConnectionType.name }}
{{ table.loadInfo.sourceConnectionPropertiesConfig }}

Create an Extensible Source Connection set example

This section steps through creating the example Extensible Source Connection set which provides the prerequisites mentioned earlier. The example set was designed to browse an SQL Server ODBC DSN but can be easily adapted to other ODBC based DSN’s also.

Follow these steps to create the example SQL Server ODBC Extensible Source Connection prerequisites:

1. Create a new Connection Properties UI configuration from Maintain UI Configuration by selecting Tools>UI Configurations>Maintain UI Configurations
   
   
2. In the UI Configurations Maintenance dialog, click New.
   
3. Under the Edit UI Configuration, add a Name, Description, select a Configuration Type from the drop-down list, and click OK.
   
   
   
4. Empty the default script to enable the examples menu in the editor. Select Tools>Create Example Configuration>Connection Configuration
   
   
5. Repeat step 1 and step 2 to create the matching Load Table UI and Column UI Configurations.
6. Create a Connection Browse Script by following these steps:
   1. Right-click Host Script and select New Object.
      
   2. In the Add a New Metadata Object window, add an Object Name for the script, and click Add.
      
   3. The Host Script properties window opens, edit the options you need, and click OK.
      
   4. Open your Host Script in the script editor and select Tools>Create Example Browse Script
   5. After the Example Browse Script is created, click Save.