示例#1
0
        /// <summary>Legacy bulk copy, tmp table switching version</summary>
        public async Task UpdateTable(SyncTableCfg tableCfg, ILogger log, CancellationToken cancel, bool fullLoad = false, int limit = 0,
                                      SyncMode mode = SyncMode.Blob)
        {
            var sw = Stopwatch.StartNew();


            await Dest.Init(Store.ContainerUrl);

            var sourceSql = tableCfg.Sql ?? $"select * from {Source.DefaultSchema}.tableCfg.Name";

            var destTable  = new TableId(Dest.DefaultSchema, tableCfg.Name);
            var destSchema = await Dest.Schema(destTable);

            var destExists = destSchema != null;

            var syncType = fullLoad || destSchema == null ? SyncType.Full : tableCfg.SyncType;

            if (syncType != SyncType.Full &&
                destSchema?.Columns.Any(c => c.ColumnName.Equals(tableCfg.TsCol, StringComparison.InvariantCultureIgnoreCase)) == false)
            {
                syncType = SyncType.Full;
            }
            if (syncType.IsIncremental() && tableCfg.TsCol.NullOrEmpty())
            {
                throw new InvalidOperationException("table configured for incremental, but no ts column was found");
            }
            var maxTs = syncType.IsIncremental()
        ? await Dest.Conn.ExecuteScalar <object>(nameof(UpdateTable), $"select max({Dest.Sql(tableCfg.TsCol)}) from {Dest.Sql(destTable)}")
        : null;

            // start reading and get schema. if we are blowwing, do this to get the schema without loading any rows
            using var reader = await Source.Read(sourceSql, tableCfg, maxTs, mode == SyncMode.Blob? 0 : limit);

            var querySchema = reader.Schema();

            destSchema ??= querySchema; // if there is no final destination schema, then it should match the source
            // apply overrides to dest schema
            destSchema = new TableSchema(destSchema.Columns.Select(c => {
                var cfg = tableCfg.Cols[c.ColumnName];
                return(new ColumnSchema(c.ColumnName, c.DataType)
                {
                    ProviderTypeExpression = cfg?.SqlType,
                    Key = cfg?.Id,
                    AllowDBNull = cfg?.Null
                });
            }));

            // create table if not exists
            if (!destExists)
            {
                await CreateDestTable(destTable, tableCfg, destSchema, log);
            }

            // prepare tmp table if required
            var tmpTable  = destTable.WithTable($"{destTable.Table}_tmp");
            var loadTable = destExists ? tmpTable : destTable;

            if (loadTable == tmpTable)
            {
                await CreateTmpTable(tmpTable, querySchema);
            }

            // copy data
            var newRows  = 0L;
            var newBytes = 0.Bytes();
            var loadId   = DateTime.UtcNow.FileSafeTimestamp();

            if (mode == SyncMode.Blob)
            {
                newBytes += await LoadBLobData(tableCfg, log, loadId, sourceSql, maxTs, loadTable);
            }
            else
            {
                newRows = await Dest.BulkCopy(reader, loadTable, log, cancel);

                log.Debug("Sync {Table} - loaded {Rows} into {LoadTable} ({SyncType})", tableCfg.Name, newRows, loadTable, syncType);
            }

            // if we loaded in to temp table, work out best way to switch this in without downtime
            if (loadTable == tmpTable)
            {
                if (newRows == 0 && newBytes == 0.Bytes())
                {
                    await Dest.DropTable(tmpTable);                         // no new rows, nothing to do
                }
                else if (syncType.IsIncremental() || tableCfg.ManualSchema) // incremental load, or manual schema. Move the rows into the desitntion table
                {
                    var cols     = destSchema.Columns;
                    var mergeRes = await Dest.Merge(destTable, tmpTable, tableCfg.IdCols, cols);

                    log.Debug("Sync {Table} - merged {Records} from {TempTable}", tableCfg.Name, mergeRes, tmpTable);
                    await Dest.DropTable(tmpTable);
                }
                else
                {
                    // there may be moments where the table dissapears.I removed the transaction to get past this error: BeginExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.
                    //using (var trans = await Dest.Conn.Conn.BeginTransactionAsync(IsolationLevel.ReadUncommitted, cancel)) {
                    await Dest.DropTable(destTable);

                    await Dest.RenameTable(tmpTable, destTable);

                    /*await trans.CommitAsync();
                     * }*/
                    log.Debug("Sync {Table} - switch out temp table {TempTable}", tableCfg.Name, tmpTable);
                }
            }

            log.Information("Sync {Table} - completed loading {Size} in {Duration}",
                            tableCfg.Name, newBytes > 0.Bytes() ? newBytes.Humanize("#,#") : newRows.ToString("#,#"), sw.Elapsed.HumanizeShort());
        }
示例#2
0
        public async Task UpdateTable(SyncTableCfg tableCfg, ILogger log, bool fullLoad = false, int limit = 0)
        {
            var sw          = Stopwatch.StartNew();
            var sourceTable = new TableId(Source.DefaultSchema, tableCfg.Name);
            var destTable   = new TableId(Dest.DefaultSchema, tableCfg.Name);
            var destSchema  = await Dest.Schema(destTable);

            var destExists = destSchema != null;

            var syncType = fullLoad || destSchema == null ? SyncType.Full : tableCfg.SyncType;

            if (syncType != SyncType.Full &&
                destSchema?.Columns.Any(c => c.ColumnName.Equals(tableCfg.TsCol, StringComparison.InvariantCultureIgnoreCase)) == false)
            {
                syncType = SyncType.Full;
            }
            if (syncType.IsIncremental() && tableCfg.TsCol.NullOrEmpty())
            {
                throw new InvalidOperationException("table configured for incremental, but no ts column was found");
            }
            var maxTs = syncType.IsIncremental()
        ? await Dest.Connection.ExecuteScalar <object>(nameof(UpdateTable), $"select max({Dest.Sql(tableCfg.TsCol)}) from {Dest.Sql(destTable)}")
        : null;

            // start reading and get schema
            using var reader = await Source.Read(sourceTable, tableCfg, maxTs, limit);

            var querySchema = reader.Schema();

            destSchema ??= querySchema; // if there is no final destination schema, then it should match the source
            // apply overrides to dest schema
            destSchema = new TableSchema(destSchema.Columns.Select(c => {
                var cfg = tableCfg.Cols[c.ColumnName];
                return(new ColumnSchema(c.ColumnName, c.DataType)
                {
                    ProviderTypeExpression = cfg?.TypeOverride,
                    Key = cfg?.Id,
                    AllowDBNull = cfg?.Null
                });
            }));

            // create table if not exists
            if (!destExists)
            {
                await CreateDestTable(destTable, tableCfg, destSchema, log);
            }

            // prepare tmp table if required
            var tmpTable  = destTable.WithTable($"{destTable.Table}_tmp");
            var loadTable = destExists ? tmpTable : destTable;

            if (loadTable == tmpTable)
            {
                await CreateTmpTable(tmpTable, querySchema);
            }

            // copy data
            var newRows = await Dest.BulkCopy(reader, loadTable, log);

            log.Debug("{Table} - loaded {Rows} into {LoadTable} ({SyncType})", tableCfg.Name, newRows, loadTable, syncType);

            // if we loaded in to temp table, work out best way to switch this in without downtime
            if (loadTable == tmpTable)
            {
                if (newRows == 0)
                {
                    await Dest.DropTable(tmpTable);                         // no new rows, nothing to do
                }
                else if (syncType.IsIncremental() || tableCfg.ManualSchema) // incremental load, or manual schema. Move the rows into the desitntion table
                {
                    var cols     = destSchema.Columns;
                    var mergeRes = await Dest.Merge(destTable, tmpTable, tableCfg.IdCol, cols);

                    log.Debug("{Table} - merged {Records} from {TempTable}", tableCfg.Name, mergeRes, tmpTable);
                    await Dest.DropTable(tmpTable);
                }
                else
                {
                    using (var trans = Dest.Connection.Conn.BeginTransaction()) {
                        await Dest.DropTable(destTable, trans);

                        await Dest.RenameTable(tmpTable, destTable, trans);

                        await trans.CommitAsync();

                        log.Debug("{Table} - switch out temp table {TempTable}", tableCfg.Name, tmpTable);
                    }
                }
            }

            log.Information("{Table} - completed loading {Rows} in {Duration}", tableCfg.Name, newRows, sw.Elapsed.HumanizeShort());
        }