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.

• Secure servers
  In addition to defining a connection string, to connect secure RavenDB servers you must export the server certificate from the source server and install it as a client certificate on the destination server.

• 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.

Creating a Connection String for Raven ETL

{
    //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 FactoryName
        FactoryName = "MySql.Data.MySqlClient",

        // define database - may also need to define authentication and encryption parameters
        // by default, encrypted databases are sent over encrypted channels
        ConnectionString = "host=127.0.0.1;user=root;database=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);