public void TestSimpleDataflow()
{
CreateTableTask.Create("test.Staging", new List <TableColumn>()
{
keyCol, col1, col2, col3
});
DataFlowTask.Execute("Test dataflow task", "DataFlow/InputData.csv", "test.Staging", 3, RowTransformation, BatchTransformation);
Assert.AreEqual(4, SqlTask.ExecuteScalar <int>("Check staging table", "select count(*) from test.Staging"));
}
public void TestDataflowDbToDb()
{
using (TestDb)
{
string destSchema = "test";
string destTable = "Staging3";
string destObject = $"[{destSchema}].[{destTable}]";
new DropAndCreateTableTask(TestDb.SqlConnection).Execute(destSchema, destTable, new List <TableColumn>()
{
Ziel_F0, Ziel_F1, Ziel_F2, Ziel_F3
});
SqlSource <Datensatz_DbToDb> DBSource = new SqlSource <Datensatz_DbToDb>(TestDb.getNewSqlConnection()
, "SELECT 0 as F1"
+ " UNION ALL SELECT 4 as F1"
+ " UNION ALL SELECT -3 as F1"
+ " UNION ALL SELECT -2 as F1"
);
DBSource.DataMappingMethod = ReaderAdapter_DbToDb.Read;
SqlDestination <Datensatz_DbToDb> destination = new SqlDestination <Datensatz_DbToDb>();
destination.ObjectName = destObject;
destination.FieldCount = 4;
destination.ObjectMappingMethod = WriterAdapter_DbToDb.Fill;
destination.SqlConnection = TestDb.SqlConnection;
Graph g = new Graph();
g.GetVertex(0, DBSource);
g.GetVertex(1, new RowTransformation <Datensatz_DbToDb>(RowTransformationDB));
g.GetVertex(2, new RowTransformation <Datensatz_DbToDb>(RowTransformationDB2));
g.GetVertex(3, destination);
g.AddEdge(0, 1); // connect 0 to 1
g.AddEdge(1, 2); // connect 1 to 2
g.AddEdge(2, 3); // connect 2 to 3
DataFlowTask <Datensatz_DbToDb> .Execute("Test dataflow task", 10000, 1, g);
//TestHelper.VisualizeGraph(g);
Assert.AreEqual(4, new ExecuteSQLTask(TestDb.SqlConnection).ExecuteScalar(string.Format("select count(*) from {0}", destObject)));
}
}
public void TestDataflow_Http_to_List()
{
Graph g_html = new Graph();
HttpSource <Datensatz_Http> httpSource = new HttpSource <Datensatz_Http>("https://www.dreckstool.de/hitlist");
httpSource.DataMappingMethod = (token) =>
{
var Datensatz_Http = new Datensatz_Http();
Datensatz_Http.token = token;
return(Datensatz_Http);
};
ListDestination <Datensatz_Http> Listdestination_html = new ListDestination <Datensatz_Http>();
g_html.GetVertex(0, httpSource);
g_html.GetVertex(1, Listdestination_html);
g_html.AddEdge(0, 1);
DataFlowTask <Datensatz_Http> .Execute("http df task", 10000, 1, g_html);
}
public void TestDataflow_MySql_to_List()
{
string ServerName = "";
if (string.IsNullOrEmpty(ServerName))
{
return;
}
MySqlConnectionManager MyTestDb = new MySqlConnectionManager(ServerName, "TestDB", "", "");
using (MyTestDb)
{
MySqlSource <Datensatz_MySql_to_List> MySqlDBSource = new MySqlSource <Datensatz_MySql_to_List>(MyTestDb.getNewMySqlConnection()
, "SELECT Tag, Monat FROM test"
);
MySqlDBSource.DataMappingMethod = ReaderAdapter_MySql_to_List.Read;
ListDestination <Datensatz> destination = new ListDestination <Datensatz>();
Graph g = new Graph();
g.GetVertex(0, MySqlDBSource);
g.GetVertex(1, new RowTransformation <Datensatz_MySql_to_List>(RowTransformationDB));
g.GetVertex(2, new RowTransformation <Datensatz_MySql_to_List>(RowTransformationDB2));
g.GetVertex(3, destination);
g.AddEdge(0, 1); // connect 0 to 1
g.AddEdge(1, 2); // connect 1 to 2
g.AddEdge(2, 3); // connect 2 to 3
DataFlowTask <Datensatz> .Execute("Test dataflow task", 10000, 1, g);
//TestHelper.VisualizeGraph(g);
//Assert.AreEqual(4, new ExecuteSQLTask(MyTestDb.MySqlConnection).ExecuteScalar(string.Format("select count(*) from {0}", destObject)));
}
}
static void Main(string[] args)
{
var x = t1();
Graph g_html = new Graph();
HttpSource <Datensatz_Http> httpSource = new HttpSource <Datensatz_Http>("https://www.boerse-frankfurt.de/index/dax/kurshistorie/tickdaten");
httpSource.DataMappingMethod = (token) =>
{
var Datensatz_Http = new Datensatz_Http();
Datensatz_Http.token = token;
return(Datensatz_Http);
};
ListDestination <Datensatz_Http> Listdestination_html = new ListDestination <Datensatz_Http>();
g_html.GetVertex(0, httpSource);
g_html.GetVertex(1, Listdestination_html);
g_html.AddEdge(0, 1);
DataFlowTask <Datensatz_Http> .Execute("http df task", 10000, 1, g_html);
}
public void TestDataflow_Massendaten()
{
using (TestDb)
{
int SkalaGrenze = 10000;
for (int i = SkalaGrenze * -1; i < SkalaGrenze; i = i + 50)
{
MetrischeSkala.Add(new IntervalPointMetric(i, i));
}
int Anzahl_je_Faktor = 10000;
int Anzahl_Faktoren = 10;
string TempObjectNameName = "test.tmp";
new DropTableTask(TestDb.getNewSqlConnection()).Execute(TempObjectNameName);
string QuellSchemaName = "test";
string QuellTabelle = "source";
string QuellObjekt = $"[{QuellSchemaName}].[{QuellTabelle}]";
new DropAndCreateTableTask(TestDb.getNewSqlConnection()).Execute(QuellSchemaName, QuellTabelle, new List <TableColumn>()
{
new TableColumn("Key", SqlDbType.Int, false, true, true),
new TableColumn("F1", SqlDbType.Int, true),
new TableColumn("F2", SqlDbType.Int, true),
new TableColumn("F3", SqlDbType.Int, true),
new TableColumn("F4", SqlDbType.Int, true),
new TableColumn("F5", SqlDbType.Int, true),
new TableColumn("F6", SqlDbType.Int, true),
new TableColumn("F7", SqlDbType.Int, true),
new TableColumn("F8", SqlDbType.Int, true),
new TableColumn("F9", SqlDbType.Int, true),
new TableColumn("F10", SqlDbType.Int, true),
});
string sql_generate_Massendaten = @"
select top 0 F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 into " + TempObjectNameName + @" from " + QuellObjekt + @" -- tmp-Tabelle erstellen
declare @grenze as int = " + SkalaGrenze + @"
declare @i as int = 0
while (@i < " + Anzahl_je_Faktor + @")
begin
insert into test.tmp
select @i % @grenze, @i % @grenze + 1, @i % @grenze + 2, (@i % @grenze) * -1, (@i % @grenze) * -1 -1, @i % @grenze, @i % @grenze -1, @i % @grenze +2, @i% @grenze+3, @i % @grenze+4
set @i = @i + 1
end
declare @j as int = 0
while (@j < " + Anzahl_Faktoren + @")
begin
insert into " + QuellObjekt + @"
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 from test.tmp
set @j = @j + 1
end
"
;
Debug.WriteLine("Generiere Massendaten ... ");
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(sql_generate_Massendaten);
string ZielSchemaName = "test";
string ZielTabelle = "destination";
string ZielObjekt = $"[{ZielSchemaName}].[{ZielTabelle}]";
new DropAndCreateTableTask(TestDb.getNewSqlConnection()).Execute(ZielSchemaName, ZielTabelle, new List <TableColumn>()
{
new TableColumn("Key", SqlDbType.Int, false, true, true),
new TableColumn("F1", SqlDbType.Int, true), new TableColumn("F1_calc", SqlDbType.Int, true),
new TableColumn("F2", SqlDbType.Int, true), new TableColumn("F2_calc", SqlDbType.Int, true),
new TableColumn("F3", SqlDbType.Int, true), new TableColumn("F3_calc", SqlDbType.Int, true),
new TableColumn("F4", SqlDbType.Int, true), new TableColumn("F4_calc", SqlDbType.Int, true),
new TableColumn("F5", SqlDbType.Int, true), new TableColumn("F5_calc", SqlDbType.Int, true),
new TableColumn("F6", SqlDbType.Int, true), new TableColumn("F6_calc", SqlDbType.Int, true),
new TableColumn("F7", SqlDbType.Int, true), new TableColumn("F7_calc", SqlDbType.Int, true),
new TableColumn("F8", SqlDbType.Int, true), new TableColumn("F8_calc", SqlDbType.Int, true),
new TableColumn("F9", SqlDbType.Int, true), new TableColumn("F9_calc", SqlDbType.Int, true),
new TableColumn("F10", SqlDbType.Int, true), new TableColumn("F10_calc", SqlDbType.Int, true),
});
System.Data.SqlClient.SqlConnectionStringBuilder builder_CurrentDbConnection
= new System.Data.SqlClient.SqlConnectionStringBuilder(TestDb.getNewSqlConnection().ConnectionString);
string Current_InitialCatalog = builder_CurrentDbConnection.InitialCatalog;
string Current_DataSource = builder_CurrentDbConnection.DataSource;
SqlSource <Datensatz> DBSource = new SqlSource <Datensatz>(TestDb.getNewSqlConnection()
, string.Format("select [Key],F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 from {0}", QuellObjekt)
);
DBSource.DataMappingMethod = ReaderAdapter.Read;
SqlDestination <Datensatz> Ziel_Schreibe = new SqlDestination <Datensatz>();
Ziel_Schreibe.ObjectName = ZielObjekt;
Ziel_Schreibe.FieldCount = FieldCount;
Ziel_Schreibe.ObjectMappingMethod = WriterAdapter.Fill;
Ziel_Schreibe.SqlConnection = TestDb.SqlConnection;
Graph g = new Graph();
g.GetVertex(0, DBSource);
g.GetVertex(1, new RowTransformation <Datensatz>(RowTransformationDB));
g.GetVertex(2, Ziel_Schreibe);
g.AddEdge(0, 1); // connect 0 to 1
g.AddEdge(1, 2); // connect 1 to 2
//TestHelper.VisualizeGraph(g);
int MaxDegreeOfParallelism = 1;
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(string.Format("truncate table {0}", ZielObjekt));
Debug.WriteLine("Start Laufzeittest MaxDegreeOfParallelism {0} ... ", MaxDegreeOfParallelism);
Stopwatch s = Stopwatch.StartNew();
DBSource.SqlConnection = TestDb.getNewSqlConnection();
DataFlowTask <Datensatz> .Execute("Test dataflow task", 10000, MaxDegreeOfParallelism, g);
Debug.WriteLine("Laufzeit in ms: {0}", s.ElapsedMilliseconds);
MaxDegreeOfParallelism = 5;
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(string.Format("truncate table {0}", ZielObjekt));
Debug.WriteLine("Start Laufzeittest MaxDegreeOfParallelism {0} ... ", MaxDegreeOfParallelism);
s = Stopwatch.StartNew();
DBSource.SqlConnection = TestDb.getNewSqlConnection();
DataFlowTask <Datensatz> .Execute("Test dataflow task", 10000, MaxDegreeOfParallelism, g);
Debug.WriteLine("Laufzeit in ms: {0}", s.ElapsedMilliseconds);
MaxDegreeOfParallelism = 10;
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(string.Format("truncate table {0}", ZielObjekt));
Debug.WriteLine("Start Laufzeittest MaxDegreeOfParallelism {0} ... ", MaxDegreeOfParallelism);
s = Stopwatch.StartNew();
DBSource.SqlConnection = TestDb.getNewSqlConnection();
DataFlowTask <Datensatz> .Execute("Test dataflow task", 10000, MaxDegreeOfParallelism, g);
Debug.WriteLine("Laufzeit in ms: {0}", s.ElapsedMilliseconds);
Assert.AreEqual(Anzahl_je_Faktor * Anzahl_Faktoren, new ExecuteSQLTask(TestDb.SqlConnection).ExecuteScalar(string.Format("select count(*) from {0}", QuellObjekt)));
}
}
public void TestSampleDataflow()
{
using (TestDb)
{
TableColumn keyCol = new TableColumn("Key", SqlDbType.Int, false, true, true);
TableColumn col1 = new TableColumn("Col1", SqlDbType.NVarChar, 100, false);
TableColumn col2 = new TableColumn("Col2", SqlDbType.NVarChar, 50, true);
TableColumn col3 = new TableColumn("Col3", SqlDbType.Int, false);
string destSchema = "test";
string destTable1 = "Staging1";
string destObject1 = $"[{destSchema}].[{destTable1}]";
new DropAndCreateTableTask(TestDb.getNewSqlConnection()).Execute(destSchema, destTable1, new List <TableColumn>()
{
keyCol, col1, col2, col3
});
SqlDestination <string[]> destination1 = new SqlDestination <string[]>();
destination1.ObjectName = destObject1;
destination1.FieldCount = 4;
destination1.ObjectMappingMethod = WriterAdapter_SampleDataflow.Fill;
destination1.SqlConnection = TestDb.getNewSqlConnection();
string destTable2 = "Staging2";
string destObject2 = $"[{destSchema}].[{destTable2}]";
new DropAndCreateTableTask(TestDb.getNewSqlConnection()).Execute(destSchema, destTable2, new List <TableColumn>()
{
keyCol, col1, col2, col3
});
SqlDestination <string[]> destination2 = new SqlDestination <string[]>();
destination2.ObjectName = destObject2;
destination2.FieldCount = 4;
destination2.ObjectMappingMethod = WriterAdapter_SampleDataflow.Fill;
destination2.SqlConnection = TestDb.getNewSqlConnection();
CSVSource <string[]> CSVSource =
new CSVSource <string[]>("DataFlow/InputData.csv");
Graph g = new Graph();
g.GetVertex(0, CSVSource);
g.GetVertex(1, new RowTransformation <string[]>(RowTransformation1));
g.GetVertex(11, new RowTransformation <string[]>(RowTransformation2));
g.GetVertex(10, new BroadCast <string[]>(CloneTransformation1));
g.GetVertex(12, new RowTransformationMany <string[]>(RowTransformationMany));
g.GetVertex(20, new RowTransformation <string[]>(RowTransformation3));
g.GetVertex(100, destination1);
g.GetVertex(110, destination2);
Edge e1 = g.AddEdge(0, 1); // connect 0 to 1
Edge e2 = g.AddEdge(1, 10); // connect 1 to 10
Edge e3 = g.AddEdge(10, 20);
Edge e4 = g.AddEdge(20, 100);
Edge e5 = g.AddEdge(10, 11);
Edge e6 = g.AddEdge(11, 12);
Edge e7 = g.AddEdge(12, 110);
DataFlowTask <string[]> .Execute("Test dataflow task", 1000, 1, g);
e2.cost = counter_RowTransformation1;
e6.cost = counter_RowTransformation2;
e4.cost = counter_RowTransformation3;
e7.cost = counter_RowTransformationMany;
//TestHelper.VisualizeGraph(g);
Assert.AreEqual(4, new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteScalar(string.Format("select count(*) from {0}", destObject1)));
Assert.AreEqual(8, new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteScalar(string.Format("select count(*) from {0}", destObject2)));
}
}
