Articles in this section

See more

Extract data from source, transform, and load in destination

  • Updated
Follow

When to use this flow type

This is the most common flow type. Almost all available connections can be used as a source, as well as a destination.  

Basically, Integrator extracts data from the source connection, transforms it by applying one or multiple transformations, and then automatically loads it in the destination. 

The following variations of this flow are available:

This transformation modifies the source data before loading it in the destination. Use copy, move, rename, delete, zip, and unzip files if all you need is to copy, move, rename or delete the files.

The source is a relational database.

When the source is a relational database, Integrator extracts data from the database table, view, or synonym or executes a user-defined SQL query.

The user has two options:

  1. Use the From field to select or enter a database object name, such as table, view or synonym. In this case, Integrator will automatically execute the SQL query select * from object. Note that the fully qualified wilcard names are supported. Example: public.*. In this case, the system will extract the data from all database objects which match then wildcard name public.* and load into the destination.
  2. Enter the actual SELECT SQL statement in the Source Query field. 

source_query.png

Creating flow

To create a flow go to Flows window, click the + button and type in database to. Select one of the available flows types and add one or multiple source-to-destination transformations.

Available transformations

Available parameters

The destination is a relational database.

When loading data into a database, Integrator either automatically generates and executes the SQL DML statement (INSERT, UPDATE, DELETE or MERGE) or executes user-created SQL statements.

When the destination is a relational database, the user has two options:

  1. Use the To field to select or enter the database object name (table, view, synonym). If the source (FROM) is configured with a wildcard, you can use * as a part of the destination name (TO). The system will substitute the * with the source name at run time.
  2. Or enter the actual INSERT, UPDATE, or DELETE SQL statement in the Destination query field.

destination_query.png

The destination query can be more than just one SQL statement. Use ; to separate SQL statements.

In the example below, 2 queries will be executed. Fields within the { and } brackets will be replaced with the actual field values given by the source.

Source query:

SELECT locust_staging.id,
       locust_update_id.hotlineeventid,
       (SELECT TOP 1 speciesid
        FROM   species
        WHERE  sp.options = species.DESCRIPTION)                                                                               AS species,
       recorded_time                                                                                                           AS calldatetime,
       CASE
         WHEN date_completed IS NULL
         THEN 2
         ELSE 1
       END                                                                                                                     AS eventstatusid,
       (SELECT TOP 1 officerid
        FROM   officers
        WHERE  ( ISNULL(officers.givenname,'') + ISNULL(officers.surname,'') ) 
IN (SELECT ISNULL(NAME,'') + ISNULL(surname,'')
FROM   staging_users
WHERE  current_user_id = staging_users.id)) AS officertakingcallid,
       GETDATE()                                                                                                               AS modifieddate,
       'ddc2'                                                                                                                  AS modifiedby
FROM   locust_staging
       INNER JOIN locust_update_id
       ON locust_staging.id = locust_update_id.id
       INNER JOIN (SELECT options,(ROW_NUMBER() OVER 
(ORDER BY (SELECT 100))) - 1 AS enum FROM staging_species) sp
       ON locust_staging.species = sp.enum

Destination query:

update hotlineevent
set calldatetime = {calldatetime}, 
    officertakingcallid = {officertakingcallid},
    modifieddate = {modifieddate},
    modifiedby = {modifiedby}
where hotlineeventid = {hotlineeventid};             

update locust_staging
set HotlineEventId =  {hotlineeventid}
where id = {id};

Creating flow

To create a flow go to Flows window, click the + button and type in to database. Select one of the available flows types and add one or multiple source-to-destination transformations.

Available transformations

Available parameters

The source is a file, inbound email connection, or API.

When the source is a file, inbound email connection, or API endpoint, it can be a flat or structured and semi-structured file, a response from the web service, or a social website.

Define the source by selecting a source (FROM) connection and a source (FROM) format. Then you select or enter a source (FROM) object name:

  • If the source connection is file storage (server or cloud), the source object name is an actual file name or a wildcard file name.
  • If the source connection is a web service, the source object name is an any appropriate name. For example, patient.json.
  • If the source connection is an oubound email connection, the source object name is either the name of the attached file or any appropriate name.
  • If the source connection is in a key-value storage, such as Redis, the name is a "key" (in a key-value pair).
  • If the source connection is in a NoSQL database, such as MongoDB, the name is a document id.

source_is_a_data_object.png

Using SQL to transform data

Similar to when the source is a relational database, the user has an option to enter an SQL query, which will be executed after the data has been read from the source. For example, if the source is a CSV file, the user can write a query which aggregates the data into a file and creates a summary view. This view will be used to drive the destination data.

Read more about SQL transformation.

source-sql.png

Working with wildcard file names

If your source data object is a file, it is possible to configure a source (FROM) so that it will read the files by the wildcard name, for example, order_*.csv.

Read how to work with wildcard file names and how to process all files by the wildcard.

Creating flow

To create a flow go to Flows window, click the + button.

  • If the source is a file, inbound email connection, Redis or MongoDB type in file to.
  • If the source is web service type in web service to.
  • If the source is Google Analytics type in well-known api to.

Select one of the available flows types and add one or multiple source-to-destination transformations.

Available transformations

Available parameters

The destination is a file, outbound email connection, or API.

When the destination is a file, outbound email connection, or API endpoint, it can be a flat or structured file and semi-structured file, a payload for the API endpoint or a social website.

You define a destination by selecting a destination (TO) connection and a destination (TO) format. Then you select or enter a destination (TO) object name:

  • If the destination connection is in file storage (server or cloud), the destination object name is an actual file name. Read how Etlworks Integrator calculates the destination file name.
  • If the destination connection is a web service, the destination object name is any appropriate name. For example, patient.json.
  • If the destination connection is an outbound email connection, the destination object name is either the name of the attached file or any appropriate name.
  • If the destination connection is a key-value storage, such as Redis, the name is any appropriate name.
  • If the destination connection is a NoSQL database, such as MongoDB, the name is document id.

When the destination connection is in file storage - the new file will be created each time the transformation is executed. If the file already exists, it will be overwritten.

destination_is_a_data_object.png

Creating flow

To create a flow go to Flows window, click the + button.

  • if the destination is a file, outbound email connection, Redis or MongoDB type in to file.
  • if the destination is web service type in to web service.

Select one of the available flows types and add one or multiple source-to-destination transformations.

Available transformations

Available parameters

 

The source is a well-known API.

Creating flow

To create a flow go to Flows window, click the + button, select Well-known API to and select the flow which extracts data from the well-known API.

Add one or multiple source-to-destination transformations.

Available transformations

Available parameters

The destination is a well-known API.

Creating flow

To create a flow go to Flows window, click the + button, select to Well-known API and select the flow which loads data into the well-known API.

Add one or multiple source-to-destination transformations.

Available transformations

Available parameters

Related articles

Comments

0 comments

Please sign in to leave a comment.