public void SaveTableAsCsv(FetchTables TableToFetch, string CsvFolder, string csvSeparator) { foreach (Shard shard in TableToFetch.Shards) { SaveShardAsCsv(shard, CsvFolder, csvSeparator); } }
bool CheckIfTempTableExists(FetchTables ft) { string Sql = "select case when OBJECT_ID('" + ft.TargetSchema + "." + ft.SourceSchema + "_" + ft.SourceTable + "_tmp', 'U') is not null then 'true' else 'false' end"; bool r = bool.Parse(TargetDataAccess.GetSingleValue(Sql).ToString()); return(r); }
void BulkInsert(FetchTables ft, bool ParallelExecution, int MaxThreads, bool OracleSpool) { // If table is not incrementally loaded, all data is loaded to a temp table which is then switched // If incremental load, data is loaded into table // create target schema if not exists TargetDataAccess.ExecSqlNonQuery("IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '" + ft.TargetSchema + "') BEGIN EXEC('CREATE SCHEMA " + ft.TargetSchema + "') END"); // 1. Create temp table if (!ft.Incremental) { TargetDataAccess.ExecSqlNonQuery(ft.DropTempTableSql); TargetDataAccess.ExecSqlNonQuery(ft.CreateTempTableSql); } else { // if incremental, check if target table exists otherwise create it if (!CheckIfTableExists(ft)) { TargetDataAccess.ExecSqlNonQuery(ft.CreateTableSql); } } // 2. Bulk insert data to temp table Parallel.ForEach(ft.Shards, new ParallelOptions { MaxDegreeOfParallelism = MaxThreads / 10 }, (shard) => //foreach(Shard shard in ft.Shards) { Console.WriteLine($"Bulk inserting {shard.Name} on thread {Thread.CurrentThread.ManagedThreadId}"); if (BulkInsert(shard, ft.Incremental, OracleSpool)) { ft.NumShardsInsertedSuccessfully++; } } ); if (!ft.Incremental) { // if all shards where inserted successfully if (ft.AllShardsBulkInsertedSuccessfully) { // 3. Drop existsing table TargetDataAccess.ExecSqlNonQuery(ft.DropTableSql); // 4. Rename temp table TargetDataAccess.ExecSqlNonQuery(ft.SwitchTableSql); } } }
public override List <FetchTables> LoadTablesFromConfig(int MaxRowLimit) { List <FetchTables> fetchTables = new List <FetchTables>(); string[] lines = File.ReadAllLines("Config.txt"); List <string> linesWithoutComments = new List <string>(); foreach (string line in lines) { if (line.Length > 2 && line.Substring(0, 2) != "//" && line.Substring(0, 2) != "--") { linesWithoutComments.Add(line); } } for (int i = 0; i < linesWithoutComments.Count; i++) { string line = linesWithoutComments[i]; if (line.Split(':')[0] == "fetchtable") { string sourcechema = "", sourcetable = "", shardmethod = "", shardcolumn = "", incrementalcolumn = "", incrementalcolumntype = "", where = "", targetschema = ""; bool loadtotarget = false, sharding = false, incremental = false; int counter = 0; for (int j = 1; j <= 10; j++) { if (i + j < linesWithoutComments.Count) { if (linesWithoutComments[i + j].Split(':')[0].Trim() == "sourceschema") { sourcechema = linesWithoutComments[i + j].Split(':')[1].Trim(); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "sourcetable") { sourcetable = linesWithoutComments[i + j].Split(':')[1].Trim(); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "targetschema") { targetschema = linesWithoutComments[i + j].Split(':')[1].Trim(); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "loadtotarget") { loadtotarget = bool.Parse(linesWithoutComments[i + j].Split(':')[1].Trim()); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "sharding") { sharding = bool.Parse(linesWithoutComments[i + j].Split(':')[1].Trim()); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "shardmethod") { shardmethod = linesWithoutComments[i + j].Split(':')[1].Trim(); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "shardcolumn") { shardcolumn = linesWithoutComments[i + j].Split(':')[1].Trim(); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "incremental") { incremental = bool.Parse(linesWithoutComments[i + j].Split(':')[1].Trim()); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "incrementalcolumn") { incrementalcolumn = linesWithoutComments[i + j].Split(':')[1].Trim(); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "incrementalcolumntype") { incrementalcolumntype = linesWithoutComments[i + j].Split(':')[1].Trim(); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "where") { where = linesWithoutComments[i + j].Split(':')[1].Trim(); counter++; } if (linesWithoutComments[i + j].Split(':')[0].Trim() == "fetchtable") { break; } } } i = i + counter; // if no targetschema is set set dlt as default if (targetschema == "") { targetschema = "dlt"; } FetchTables ft = new FetchTables(sourcechema, sourcetable, targetschema, loadtotarget, "SqlServer"); ft.Sharding = sharding; ft.ShardMethod = shardmethod; ft.ShardColumn = shardcolumn; ft.Incremental = incremental; ft.IncrementalColumn = incrementalcolumn; ft.IncrementalColumnType = incrementalcolumntype; ft.Where = where; ft.LimitRowsForTest = MaxRowLimit; fetchTables.Add(ft); } } // Populate each fetchtable with table creation sql´s by fetching metadata from data source foreach (FetchTables fetchTable in fetchTables) { fetchTable.CreateTableSql = GetCreateTableSql(fetchTable.TargetSchema, fetchTable.SourceSchema, fetchTable.SourceTable, false); fetchTable.CreateTempTableSql = GetCreateTableSql(fetchTable.TargetSchema, fetchTable.SourceSchema, fetchTable.SourceTable, true); fetchTable.DropTableSql = "IF OBJECT_ID('" + fetchTable.TargetSchema + "." + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + "', 'U') IS NOT NULL DROP TABLE " + fetchTable.TargetSchema + "." + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + ";"; fetchTable.DropTempTableSql = "IF OBJECT_ID('" + fetchTable.TargetSchema + "." + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + "_tmp', 'U') IS NOT NULL DROP TABLE " + fetchTable.TargetSchema + "." + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + "_tmp;"; fetchTable.SwitchTableSql = "EXEC sp_rename '" + fetchTable.TargetSchema + "." + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + "_tmp', '" + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + "';"; } return(fetchTables); }