Operations: How to Add ETL

This API article demonstrates how to create various ETL tasks and the connection strings required.

• You can add an ETL task by using the AddEtlOperation method.

• ETL tasks are ongoing tasks that:

  1. Extract selected data from your source database when changes are made or new data is added.
  2. Apply a transform script on the data.
  3. Load the transformed data to a destination that you designate.

• To learn more about ETL (Extract, Transfer, Load) ongoing tasks, see this article on ETL Basics.

• To learn how to manage ETL tasks using the Studio see: RavenDB ETL Task.

In this page:

• AddEtlOperation

• Add Raven ETL

  • Creating a Connection String for Raven ETL
  • Code Sample to Add Raven ETL

• Add Sql ETL

  • Creating a Connection String for Sql ETL
  • Code Sample to Add Sql ETL

You can add ETL task by using the AddEtlOperation method.

AddEtlOperation

public AddEtlOperation(EtlConfiguration<T> configuration)

Add Raven ETL

Raven ETL tasks enable ongoing Extract, Transform, Load functionality from a RavenDB source database to a RavenDB destination.

Creating a Connection String for Raven ETL

• Secure clusters
  In addition to defining a connection string, to connect secure RavenDB clusters you must export the server certificate from the source cluster and install it into the destination cluster.

• After passing the certificate, you can either create an ETL with a connection string and transformation script via the studio or with the following API.

{
    //define connection string
    var ravenConnectionString = new RavenConnectionString()
    {
        //name connection string
        Name = "raven-connection-string-name",

        //define appropriate node
        //Be sure that the node definition in the connection string has the "s" in https
        TopologyDiscoveryUrls = new[] { "https://127.0.0.1:8080" },

        //define database to connect with on the node
        Database = "Northwind",
    };
    //create the connection string
    var resultRavenString = store.Maintenance.Send(
        new PutConnectionStringOperation<RavenConnectionString>(ravenConnectionString));
}

Code Sample to Add Raven ETL

AddEtlOperation<RavenConnectionString> operation = new AddEtlOperation<RavenConnectionString>(
    new RavenEtlConfiguration
    {
        ConnectionStringName = "raven-connection-string-name",
        Name = "Employees ETL",
        Transforms =
        {
            new Transformation
            {
                Name = "Script #1",
                Collections =
                {
                    "Employees"
                },
                Script = @"loadToEmployees ({
                        Name: this.FirstName + ' ' + this.LastName,
                        Title: this.Title
                });"
            }
        }
    });

AddEtlOperationResult result = store.Maintenance.Send(operation);

Add Sql ETL

SQL ETL tasks enable ongoing Extract, Transform, Load functionality from RavenDB to SQL servers.

Creating a Connection String for Sql ETL

{
    {
        // define new connection string
        PutConnectionStringOperation<SqlConnectionString> operation
        = new PutConnectionStringOperation<SqlConnectionString>(
            new SqlConnectionString
            {
                // name connection string
                Name = "local_mysql",

                // define data source and database name
                // may also need to define authentication and encryption parameters
                ConnectionString = @"Data Source=localhost\sqlexpress;Integrated Security=SSPI;
                        Connection Timeout=3;Initial Catalog=SqlReplication-Northwind;"
            });

        // create connection string
        PutConnectionStringResult connectionStringResult = store.Maintenance.Send(operation);
    }

Code Sample to Add Sql ETL

AddEtlOperation<SqlConnectionString> operation = new AddEtlOperation<SqlConnectionString>(
    new SqlEtlConfiguration
    {
        ConnectionStringName = "sql-connection-string-name",
        FactoryName = "System.Data.SqlClient",
        Name = "Orders to SQL",
        SqlTables = {
            new SqlEtlTable {TableName = "Orders", DocumentIdColumn = "Id", InsertOnlyMode = false},
            new SqlEtlTable {TableName = "OrderLines", DocumentIdColumn = "OrderId", InsertOnlyMode = false},
        },
        Transforms =
        {
            new Transformation
            {
                Name = "Script #1",
                Collections =
                {
                    "Orders"
                },
                Script = @"var orderData = {
                                Id: id(this),
                                OrderLinesCount: this.Lines.length,
                                TotalCost: 0
                            };

                            for (var i = 0; i < this.Lines.length; i++) {
                                var line = this.Lines[i];
                                orderData.TotalCost += line.PricePerUnit;
                                
                                // Load to SQL table 'OrderLines'
                                loadToOrderLines({
                                    OrderId: id(this),
                                    Qty: line.Quantity,
                                    Product: line.Product,
                                    Cost: line.PricePerUnit
                                });
                            }
                            orderData.TotalCost = Math.round(orderData.TotalCost  * 100) / 100;

                            // Load to SQL table 'Orders'
                            loadToOrders(orderData)"
            }
        }
    });

AddEtlOperationResult result = store.Maintenance.Send(operation);