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:
- Extract selected data from your source database when changes are made or new data is added.
- Apply a transform script on the data.
- 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:
You can add ETL task by using the AddEtlOperation method.
AddEtlOperation
public AddEtlOperation(EtlConfiguration<T> configuration)
Parameters | ||
---|---|---|
configuration | EtlConfiguration<T> |
ETL configuration where T is connection string type |
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);