erwin Data Transformation 9.2.6 - User Guide

NoSQL DB Query

NoSQL DB Query

When the field “Type” is set to “NoSQL DB Query”, then the “Query” field is highlighted. In this field, the user has to write the query to be run by the MongoDB Database and MongoDB item is selected in the “DB Type” dropdown list. The query must be written using SQL syntax; examples of supported SQL functions are:

•select object.key1, object2.key3, object1.key4 from my_collection where object.key2 = 34 AND object2.key4 > 5

•select * from my_table where date(column,'YYY-MM-DD') >= '2016-12-12'

•select * from my_table where date(column,'natural') >= '5000 days ago'

•select * from my_table where regexMatch(column,'^[ae"gaf]+$') = true

•select distinct column1 from my_table where value IS NULL

•select * from my_table where value LIKE 'start%'

•select column1 from my_table where value IN ("theValue1","theValue2","theValue3")

•select column1 from my_table where value NOT IN ("theValue1","theValue2","theValue3")

•select column1 from my_table where column = true

•select borough, cuisine, count(*) from my_collection WHERE borough LIKE 'Queens%' GROUP BY borough, cuisine ORDER BY count(*) DESC;

•delete from my_table where value IN ("theValue1","theValue2","theValue3")

In the Connection Parameters, user is asked for: Connection String, Database name and Password. For local connection, Connection string is usually mongodb://127.0.0.1:27017, while for cloud connections, Connection String can be automatically retrieved from the Connect button inside MongoDB web console. Connection String samples are:

mongodb://myusername:<PASSWORD>@cluster0-shard-00-00-pncrc.mongodb.net:27017,cluster0-shard-00-01-pncrc.mongodb.net:27017,cluster0-shard-00-02-pncrc.mongodb.net:27017/admin?replicaSet=Cluster0-shard-0&ssl=true

and

mongodb+srv://myusername:<PASSWORD>@cluster0-pncrc.mongodb.net/test

The user can replace <PASSWORD> wildcard with the real one or can keep <PASSWORD> wildcard in the Connection string and provide the encrypted password in the “Password” field.

DB Metadata

DB Metadata

Setting the “Type” field to “DB Metadata” allows metadata to be retrieved from an SQL databases.

If the “Connection Parameters” are valid, clicking the  button will result in options being displayed for filtering the results by catalog name, schema, table name, and/or table type, as appropriate to the database type and requested metadata type.

If the a connection cannot be established, clicking the  button will result in a “Connection Refused” message being displayed.

The first 50 results can be previewed by pressing the  button.

Web Service Adapter Configuration

Web Service Adapter Configuration

Going through this configuration window, the user can set up an adapter to execute a connection with a known Web Service.

The first group of fields in the window relate to the Web Services Adapter List, which lists all the available adapters. For each adapter selected in the list, the Web Services Adapter Parameters fill the second group of fields, which describe the Web Service connection parameters and the third group of fields show the expected input parameters for the execution of the adapter.

When adding a new one, it’s possible to choose between:

•SOAP Web Service

•REST/others specific adapters, from supported third party systems

Add or Modify SOAP Web Service Adapter

Add or Modify SOAP Web Service Adapter

To create a new adapter just press the , and select “SOAP Web Service” in the following popup:

To modify an existing adapter selected from the list, press the . An editing window pops up.

The same pop up window applies in both cases. The fields will be empty if the user is creating a new adapter. The same fields will contain data, if the user is going to modify an already existing adapter.

The first group of fields concern the WDSL Parameters while the second group allows the user to specify login parameters, if required.

The first field contains the URL of the WSDL file that should apply for the Web Service. The side button  of the first field allows navigating into the file system and selecting the WSDL file from a local path.

Should the Web Service require a login (username and password) to the WSDL server, a flag in the checkbox  will enable the related fields allowing entry of the credentials.

The  button steps into the next window that shows all the available operations of that Web Service.

The first field of the window is a drop-down list that contains a list of the operations available for the selected Web Service.

After selecting the desired operation it is required to enter a name and a description for the adapter in the appropriate fields;the user must browse the file system for the adapter folder (used to save intermediate and result file for adapter execution).

The next button  steps into the next window that allows the user to choose the values for the Web Service parameters.

If the Web Services has no parameters the user can leave the fields empty in the following wizard window. 

The first field shows the list of the Web Service parameters, as read from the WSDL:

•Type of parameter (Type),

•Attribute Name (Class Name),

•Attribute Value (Value),

•Capability to accept list of values, as defined in WSDL (isList)

For each of the shown parameters, a value can be entered into the multiline text field, while the parameter is selected in the list above.

The  button sets the entered data.

If a parameter is actually a list of values (column “isList” is true), then the user is required to optionally enter different values, separated by a new line (one per row). In this case, DT will call the Web Service only once, passing to it the multi-line string, containing the different values.

If a parameter natively does not allow a list of values (column “isList” is false), but the user needs to pass more than one value to the Web Service call, selecting the option, the user is allowed to enter a list of values for the selected parameter, even if “isList” is false, with the new line as a separator.

DT will then perform all the implied calls (as many times as the number of desired values in the list) to the Web Service at the scheduled time, by scheduling just one job.

Pressing the  button will save all the changes made in the window.

One common application may be extracting information from MS SharePoint Lists, for on premise deployments. In this case, the user could configure a web service adapter to read GetListItems operation from the Lists web service, specifying as a parameter the name of the list to get data from.

After configuring the adapter, users can use it in any workflow as a source, applying for source normalization the provided parser GetListItemResponseParser (which may be customized in terms of extracted properties, if needed), so that the list content can be used in an end to end operation.