Ongoing Tasks: ETL Basics

• Share relevant data
  Send data in a well-defined format to match specific requirements, ensuring only relevant data is transmitted
  (e.g., sending data to an existing reporting solution).

• Protect your data - Share partial data
  Limit access to sensitive data. Details that should remain private can be filtered out as you can share partial data.

• Reduce system calls
  Distribute data across related services within your system architecture, allowing each service to access its own copy of the data without cross-service calls (e.g., sharing a product catalog among multiple stores).

• Transform the data

  • Modify content sent as needed with JavaScript code.
  • Multiple documents can be sent from a single source document.
  • Data can be transformed to match the target destination's model.

• Aggregate your data
  Data sent from multiple locations can be aggregated in a central server
  (e.g., aggregating sales data from point of sales systems for centralized calculations).

• The following ETL tasks can be defined:

  • RavenDB ETL - send data to another RavenDB database
  • SQL ETL - send data to an SQL database
  • OLAP ETL - send data to an OLAP destination
  • Elasticsearch ETL - send data to an Elasticsearch destination
  • Kafka ETL - send data to a Kafka message broker
  • RabbitMQ ETL - send data to an RabbitMQ exchange
  • Azure Queue Storage ETL - send data to an Azure Queue Storage message queue

• All ETL tasks can be defined from the Client API or from the Studio.

• The destination address and access options are set using a pre-defined connection string, simplifying deployment across different environments. For example, with RavenDB ETL, multiple URLs can be configured in the connection string since the target database can reside on multiple nodes within the Database Group of the destination cluster. If one of the destination nodes is unavailable, RavenDB automatically executes the ETL process against another node specified in the connection string. Learn more in the Connection Strings article.

ETL's three stages are:

• Extract - Extract the documents from the database
• Transform - Transform & filter the documents data according to the supplied script (optional)
• Load - Load (write) the transformed data into the target destination

Extract

The ETL process starts with retrieving the documents from the database.
You can choose which documents will be processed by the next two stages (Transform and Load).

The possible options are:

• Documents from a single collection
• Documents from multiple collections
• All documents

Transform

• This stage transforms and filters the extracted documents according to a provided script.
  Any transformation can be done so that only relevant data is shared.
  The script is written in JavaScript and its input is a document.

• A task can be provided with multiple transformation scripts.
  Different scripts run in separate processes, allowing multiple scripts to run in parallel.

• You can do any transformation and send only data you are interested in sharing.
  The following is an example of RavenDB ETL script processing documents from the "Employees" collection:

  var managerName = null;
  
  if (this.ReportsTo !== null)
  {
      var manager = load(this.ReportsTo);
      managerName = manager.FirstName + " " + manager.LastName;
  }
  
  // Load the object to a target destination by the name of "EmployeesWithManager"
  loadToEmployeesWithManager({
      Name: this.FirstName + " " + this.LastName,
      Title: this.Title ,
      BornOn: new Date(this.Birthday).getFullYear(),
      Manager: managerName
  });

Load

• Loading the results to the target destination is the last stage.

• In contrast to Replication, ETL is a push-only process that writes data to the destination whenever documents from the relevant collections are changed. Existing entries on the target will always be overwritten.

• Updates are implemented by executing consecutive DELETEs and INSERTs.
  When a document is modified, the delete command is sent before the new data is inserted and both are processed under the same transaction on the destination side. This applies to all ETL types with two exceptions:

  • In RavenDB ETL, when documents are loaded to the same collection there is no need to send DELETE because the document on the other side has the same identifier and will just update it.
  • in SQL ETL you can configure to use inserts only, which is a viable option for append-only systems.

ETL errors and warnings are logged to files and displayed in the notification center panel.
You will be notified if any of the following events happen:

• Connection error to the target
• JS script is invalid
• Transformation error
• Load error
• Slow SQL was detected

Fallback Mode:
If the ETL cannot proceed the load stage (e.g. it can't connect to the destination) then it enters the fallback mode.
The fallback mode means suspending the process and retrying it periodically.
The fallback time starts from 5 seconds and it's doubled on every consecutive error according to the time passed since the last error, but it never crosses ETL.MaxFallbackTimeInSec configuration (default: 900 sec).

Once the process is in the fallback mode, then the Reconnect state is shown in the Studio.