Beispiel #1
0
 public void SaveTableAsCsv(FetchTables TableToFetch, string CsvFolder, string csvSeparator)
 {
     foreach (Shard shard in TableToFetch.Shards)
     {
         SaveShardAsCsv(shard, CsvFolder, csvSeparator);
     }
 }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        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);
                }
            }
        }
Beispiel #4
0
        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);
        }