使用C＃和.NET Core的ETL作业

目录

介绍

ETLBox组件

概述控制流任务

ADO.NET——旧方式

使用控制流任务的行计数

为什么不是Entitiy Framework

概述数据流

源组件

转换

缓冲

非阻塞和阻塞转换

目标组件

一个简单的数据流

示例

环境

开始编码

添加nlog.config

运行项目

一个简单的etl管道

再次运行

完整代码

---

ETLBox允许您使用C＃编写ETL作业，并且是SSIS的真正替代品。由于它基于.NET core，您甚至可以在任何平台上运行它。

介绍

此示例将概述ETLBox的基本概念。它向您展示了如何使用纯C＃代码编写自己的ETL作业。示例非常简单——它向您介绍了框架的基本概念。

示例代码将在.NET核心上运行，并符合.NET Standard 2.0。您还需要启动并运行SQL Server。最新版本的ETLBox可以通过nuget下载。

如果您正在寻找更多示例和详细信息，请参阅项目主页——https://etlbox.net。

ETLBox组件

ETLBox分为两个主要组件：控制流任务和数据流任务。控制流部分中的某些任务仅用于记录目的。本文将向您概述两者，然后向您展示如何编写简单的ETL作业。

概述控制流任务

控制流任务可以拆分为“ 常规 ”任务和“ 记录 ”任务。控制流任务驻留在ALE.ETLBox.ControlFlow命名空间中——记录任务驻留在ALE.ETLBox.Logging命名空间中。

控制流任务是一组全面的任务，用于管理、更改或查询数据库。只需一行代码，您就可以在数据库中创建表或触发SQL。如果您在使用ADO.NET之前曾经这样做过，那么您可能会发现有一些样板代码需要一遍又一遍地编写。控制流任务背后的想法是你不必一次又一次地编写相同的代码，例如，只是为了做一些微不足道的事情，比如打开连接和计算表中的行。这应该只用一行代码就可以了。

ADO.NET——旧方式

例如，在具有经典ADO.NET连接的表上建立连接和执行简单行计数的代码如下所示：

string connectionString = "Data Source=.; Database=Sample; Integrated Security=SSPI";
using (SqlConnection con = new SqlConnection(connectionString))
{
   SqlCommand cmd = new SqlCommand("select count(*) from dbo.tbl", con);
   con.Open();
   int numrows = (int)cmd.ExecuteScalar();   
}

使用控制流任务的行计数

现在让我们看一下如何使用控制流任务(Control Flow Tasks)库进行行计数。

首先，我们需要与控制流任务(Control Flow Tasks)建立连接。您只需设置一次数据库连接字符串，如下所示：

ControlFlow.CurrentDbConnection = 
new SqlConnectionManager(new ConnectionString
       ("Data Source=.; Database=Sample; Integrated Security=SSPI""));

如果在执行任务时没有传递其他连接，则连接将存储在静态属性中并由所有后续任务使用。

现在，您可以使用 RowCountTask来查询表中的行数，仅使用一行代码。

int count = RowCountTask.Count("dbo.tbl");

在内部，打开ADO.NET连接（使用默认的ADO.NET连接池），并且select count(*) from dbo.tbl语句在数据库上执行语句。结果将从RowCountTask返回。

为什么不是Entitiy Framework

ETLBox旨在用作ETL对象库。因此，用户通常处理大数据，某种数据仓库结构，并用于完全控制数据库。借助ADL.NET的基础功能——ETLBox使用它——您可以完全访问数据库，并且基本上可以执行您在服务器上使用SQL所做的任何事情。由于EF（实体框架）是一种高度复杂的ORM工具，它的缺点是您只能在EF允许您执行的数据库上执行操作。但由于EF没有包含SQL Server上的SQL和ADO.NET的所有可能性，因此实体框架通常不是创建ETL作业的好选择。对于其他ORM工具也是如此。

概述数据流

你有一些数据——存储在一些文件，一个表或其他地方。现在，您要定义一个获取此数据的管道，将其“动态”转换并将其写入目标（这可能又是数据库，文件或其他位置）。在抽象层面上，这可以看作是ETL过程（ETL = Extract，Transform，Load）。数据流组件将允许您定义自己的ETL作业。所有数据流任务都驻留在ALE.ETLBox.DataFlow命名空间中。

源组件

所有数据流管道至少需要一个或多个源。源基本上是可以从某个地方读取数据的所有内容（例如，CSV文件或数据库表），然后将这些数据发布到管道中。所有源都应该能够异步读取数据。这意味着，当组件从源读取数据时，它同时将已处理的数据发送到连接到源的组件。目前有两个内置数据源：CSVSource和DBSource。如果您需要其他源组件，可以扩展CustomSource.

一旦源开始读取数据，它将开始向其连接的组件发送数据。这些组件可以是转换或目标。

转换

转换始终至少有一个输入和一个输出。输入可以连接到其他转换或源，输出也可以连接到其他转换或目标。转换组件的目的是从其输入中获取数据并将转换后的数据发布到其输出。这是逐行完成的。只要输入中有任何数据，转换就会开始并将结果发布到输出。

缓冲

每次转型都会有一个输入。如果连接到输入发布数据的组件的处理速度比转换更快，那么缓冲区将保存此数据，直到转换可以继续进行下一项。这允许源尽可能快地读取，允许已读取的数据缓冲在内存中——因此转换将始终准备好处理一些数据。

非阻塞和阻塞转换

转换可以是阻塞也可以是非阻塞。

一旦在输入缓冲区中找到某些内容，非阻塞转换就会开始处理数据。在它发现数据的那一刻，它将开始转换它并将数据发送到已注册的输出组件。

阻塞转换将停止整个管道的数据处理——输入缓冲区将一直等到所有数据都到达输入。这意味着它将等到连接到转换的管道中的所有源都已从其源读取所有数据，并且所有转换在处理传入数据之前。当从连接的源读取所有数据并在管道下进行转换时，阻塞转换将开始转换。因此，在阻塞转换的转换中，您将可以访问内存中缓冲的所有数据。例如，排序组件是阻塞转换。它将等待所有数据到达转换块——然后它将对其进行排序并将已排序的数据发布到其输出。

目标组件

目标组件通常只有一个输入。它们定义数据的目标，例如数据库表或CSV文件。目前，有DBDestination和CSVDestination已经实现。如果您需要另一个目标组件，您可以扩展CustomDestination或在github中提出一个问题。

每个Destination都带有一个输入缓冲区。

虽然CSV目标的Destination将打开一个文件流，一旦数据到达就会将数据写入其中，而DB目标将逐批执行此操作——因此，它将等到输入缓冲区达到批处理大小（或数据是最后一批），然后使用批量插入将其插入数据库。

一个简单的数据流

让我们看一下像这样的简单数据流：

CSV文件（源）->行转换->DB目标

由于数据流任务基于与控制流任务相同的基础，因此您首先应该像控制流任务一样设置连接。

ControlFlow.CurrentDbConnection = 
new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;"));

现在我们需要创建一个源，在这个例子中，它可以包含订单数据。如下所示：

CSVSource sourceOrderData = new CSVSource("demodata.csv");

我们现在添加一个行转换。CSVSource的默认输出格式是string数组。在此示例中，我们将CSV string数组转换为Order对象。

RowTransformation<string[], Order> rowTrans = new RowTransformation<string[], Order>(
  row => new Order(row)
);    

现在我们需要创建一个目标。请注意，目标是使用Order对象键入的。

DBDestination<Order> dest = new DBDestination<Order>("dbo.OrderTable");

到目前为止，我们只创建了组件，但是我们没有定义数据流管道。我们现在就这样做：

sourceOrderData.LinkTo(rowTrans);
rowTrans.LinkTo(dest);

这将创建一个数据流管道CSVSource-> RowTransformation->DBDestination

现在我们将给源命令开始读取数据。

source.Execute();

此代码将作为异步任务执行。如果要等待数据流管道完成，请将此行添加到代码中：

dest.Wait();

当dest.Wait()返回时，所有的数据从源中读取和写入数据库表。

示例

现在让我们一起使用所有控制流任务和数据流组件。在此示例中，我们要创建数据库和表，并将输入文件中的一些数据加载到表中。此外，在加载数据时，我们将进行非常简单的转换。

环境

对于此演示，您可以使用Visual Studio for Mac和运行在docker镜像中的SQL Server for Linux。用于在Mac上管理SQL Server的用户界面将是Azure Data Studio。

首先，我们需要在ubuntu上启动运行SQL Server的docker镜像。在终端中运行以下命令行语句以启动容器。

docker run -d --name sql_server_demo -e 'ACCEPT_EULA=Y' 
-e 'SA_PASSWORD=reallyStrongPwd123' -p  1433:1433 microsoft/mssql-server-linux

使用该命令docker ps，我们可以看到容器已启动并正在运行。

现在创建一个新的dotnet核心控制台应用程序。使用GUI执行此操作或执行以下命令：

dotnet new console

将当前版本的ETLBox作为包添加到项目中。

dotnet add package ETLBox

现在，您将能够使用ETLBox附带的全套工具。

开始编码

现在进入static main方法。出于演示目的，将直接在此处添加以下代码。在现实生活中，您可能会创建一些对象。

首先，我们需要在static控制流对象中存储连接字符串。

ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString
           ("Data Source=.;Integrated Security=false;User=sa;password=reallyStrongPwd123"));

有了CreateDatabaseTask，我们将创建一个新的数据库。

CreateDatabaseTask.Create("demo");

此外，我们希望更改与刚刚创建的数据库的连接，并使用CreateTableTask在其中创建一个表。

ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString
("Data Source=.;Integrated Security=false;User=sa;password=reallyStrongPwd123;Initial Catalog=demo"));

            CreateTableTask.Create("dbo.table1", new List<TableColumn>()
            {
                new TableColumn("ID","int",allowNulls:false, isPrimaryKey:true, isIdentity:true),
                new TableColumn("Col1","nvarchar(100)",allowNulls:true),
                new TableColumn("Col2","smallint",allowNulls:true)
            });

添加nlog.config

在我们测试我们的演示项目之前，我们希望显示一些日志输出。ETLBox日志记录构建在nlog上。在etlbox网站上，您将找到如何使用nlog配置日志记录的示例。将以下行作为nlog.config添加到项目根目录。确保将其复制到输出目录中。

<?xml version="1.0" encoding="utf-8"?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"

      xsi:schemaLocation="NLog NLog.xsd"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <rules>
    <logger name="*" minlevel="Debug" writeTo="console" />
  </rules>
  <targets>
    <target name="console" xsi:type="Console" />     
  </targets>
</nlog>

运行项目

现在构建并运行项目。将弹出一个终端窗口并显示日志输出。当日志记录级别设置为debug时，您将看到针对数据库执行的所有SQL代码。检查数据库和表是否已创建。

一个简单的etl管道

接下来，我们要创建一个简单的etl管道。首先，我们创建一个名为input.csv的演示CSV文件。输入文件包含标题信息和一些值。此外，我们需要将其复制到输出目录中。

Col1,Col2
Value,1
Value2,2
Value3,3

现在，我们创建一个CSVSource指向新创建的输入文件。

CSVSource source = new CSVSource("input.csv");

在继续之前，我们需要一个可以保存数据的对象。我们称之为MyData。

public class MyData
{
    public string Col1 { get; set; }
    public string Col2 { get; set; }
}

现在我们添加一个行转换。行转换将从源接收一个string数组并将其转换为Mydata对象。

RowTransformation<string[], MyData> row = new RowTransformation<string[], MyData>
(
    input => new MyData() 
    { Col1 = input[0], Col2 = input[1] }
);

接下来，我们添加一个指向我们表的数据库目标。

DBDestination<MyData> dest = new DBDestination<MyData>("dbo.table1");

现在，我们需要链接我们的dataflow组件。

source.LinkTo(row);
row.LinkTo(dest);

链接组件后，我们希望源读取输入数据。目的地应该等到它收到所有数据。

source.Execute();
dest.Wait();

最后，我们检查数据是否已成功加载到表中并将其写入控制台输出。未完成这个我们将使用SQLTask。

SqlTask.ExecuteReader("Read all data from table1",
    "select Col1, Col2 from dbo.table1",
    col1 => Console.WriteLine(col1.ToString() + ","),
    col2 => Console.WriteLine(col2.ToString())
);

再次运行

让我们再次运行项目并查看输出。

您将看到数据已成功复制到数据库表中。

完整代码

这是整个示例代码。

Program.cs文件：

using System;
using System.Collections.Generic;
using ALE.ETLBox;
using ALE.ETLBox.ConnectionManager;
using ALE.ETLBox.ControlFlow;
using ALE.ETLBox.DataFlow;

namespace Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
            ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString
            ("Data Source=.;Integrated Security=false;User=sa;password=reallyStrongPwd123"));
            CreateDatabaseTask.Create("demo");
            ControlFlow.CurrentDbConnection = new SqlConnectionManager
            (new ConnectionString("Data Source=.;Integrated Security=false;
            User=sa;password=reallyStrongPwd123;Initial Catalog=demo"));

            CreateTableTask.Create("dbo.table1", new List<TableColumn>()
            {
                new TableColumn("ID","int",allowNulls:false, isPrimaryKey:true, isIdentity:true),
                new TableColumn("Col1","nvarchar(100)",allowNulls:true),
                new TableColumn("Col2","smallint",allowNulls:true)
            });

            CSVSource source = new CSVSource("input.csv");
            RowTransformation<string[], MyData> row = new RowTransformation<string[], MyData>(
            input => new MyData() { Col1 = input[0], Col2 = input[1] });
            DBDestination<MyData> dest = new DBDestination<MyData>("dbo.table1");

            source.LinkTo(row);
            row.LinkTo(dest);
            source.Execute();
            dest.Wait();

            SqlTask.ExecuteReader("Read all data from table1",
            "select Col1, Col2 from dbo.table1",
                col1 => Console.WriteLine(col1.ToString() + ","),
                col2 => Console.WriteLine(col2.ToString()));
        }

        public class MyData
        {
            public string Col1 { get; set; }
            public string Col2 { get; set; }
        }
    }
}

文件nlog.config （复制到输出目录）：

<?xml version="1.0" encoding="utf-8"?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"

      xsi:schemaLocation="NLog NLog.xsd"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <rules>
    <logger name="*" minlevel="Debug" writeTo="console" />
  </rules>
  <targets>
    <target name="console" xsi:type="Console" />     
  </targets>
</nlog>

文件input.csv（复制到输出目录）：

Col1,Col2
Value,1
Value2,2
Value3,3

 

原文地址：https://www.codeproject.com/Articles/1273231/ETL-Jobs-with-Csharp-NET-Core