private async Task CopyDataAsync(bool sampleData = false, SqlPipelines pipelines = null) { _logger.LogInformation("Copying data started..."); _primaryKeyNames = new Dictionary <string, List <string> >(); var tables = (await _sqlConnection.QueryAsync(@" select SCHEMA_NAME(t.schema_id) AS TABLE_SCHEMA, t.name AS TABLE_NAME from sys.tables t where t.temporal_type_desc IN ('SYSTEM_VERSIONED_TEMPORAL_TABLE', 'NON_TEMPORAL_TABLE') ")).ToList(); // *** refresh to workaround NCBC-2784 Dispose(); await ConnectAsync(); await CreateBucketAsync(); // **** foreach (var table in tables) { await CopyDataFromTableToCollectionAsync(table.TABLE_SCHEMA, table.TABLE_NAME, sampleData, pipelines); } _logger.LogInformation("Copying data complete."); }
/// <summary> /// Start one or more Migration processes /// </summary> /// <param name="validateNames">Check to make sure the table names are short enough or are mapped properly</param> /// <param name="createBucket">Create a Couchbase bucket to match the SQL catalog</param> /// <param name="createCollections">Create Couchbase collections to match the SQL tables</param> /// <param name="createIndexes">Create Couchbase indexes to match the SQL indexes</param> /// <param name="copyData">Create Couchbase documents to match the SQL rows</param> /// <param name="sampleForDemo">Only create a sample set of Couchbase documents/indexes - only suitable for demos!</param> /// <param name="createUsers">Create Couchbase users to match the SQL users</param> /// <param name="pipelines">Pipelines mapped to tables to perform filters/transforms</param> /// <returns></returns> public async Task MigrateAsync( bool validateNames = false, bool createBucket = false, bool createCollections = false, bool createIndexes = false, bool copyData = false, bool sampleForDemo = false, bool createUsers = false, SqlPipelines pipelines = null) { if (validateNames) { await ValidateNamesAsync(); } var shouldContinue = createBucket || createCollections || createIndexes || copyData || createUsers; if (!shouldContinue) { return; } if (!_isValid) { throw new Exception("Validation on SQL Server names has not been performed. Cannot continue with migration."); } if (createBucket) { await CreateBucketAsync(); } else { await ConnectBucketAsync(); } if (createCollections) { await CreateCollectionsAsync(); } if (createIndexes) { await CreateIndexesAsync(sampleForDemo); } if (copyData) { await CopyDataAsync(sampleForDemo, pipelines); } if (createUsers) { await CreateUsersAsync(); } }
private async Task CopyDataFromTableToCollectionAsync(string tableSchema, string tableName, bool sampleData = false, SqlPipelines pipelines = null) { var collectionName = GetCollectionName(tableSchema, tableName); _logger.LogInformation($"Copying data from `{tableSchema}.{tableName}` table to {collectionName} collection..."); var counter = 0; ICouchbaseCollection collection; if (_config.UseSchemaForScope) { var scopeName = GetScopeName(tableSchema); var scope = await _bucket.ScopeAsync(scopeName); collection = await scope.CollectionAsync(collectionName); } else { var scope = await _bucket.DefaultScopeAsync(); collection = await scope.CollectionAsync(collectionName); } // lookup and use custom pipeline defined for this table SqlPipelineBase pipeline = new SqlPipelineDefault(tableSchema, tableName); if (sampleData) { pipeline = new SqlPipelineDefaultSample(tableSchema, tableName); } var customPipeline = pipelines?.Get(tableSchema, tableName); if (customPipeline != null) { pipeline = customPipeline; } // buffered false because this could be a very large amount of data // see: https://dapper-tutorial.net/buffered using (var outerConnection = new SqlConnection(_config.SourceSqlConnectionString)) { _logger.LogInformation($"Opening non-buffered connection to `{tableSchema}.{tableName}`"); var rows = outerConnection.Query(pipeline.Query, buffered: false); _logger.LogInformation("Writing row(s)..."); foreach (var row in rows) { if (!pipeline.IsIncluded(row)) { continue; } var transformedRow = pipeline.Transform(row); string documentKey = null; try { documentKey = await GetDocumentKeyFromPrimaryKeyValuesAsync(transformedRow, tableSchema, tableName); await collection.UpsertAsync(documentKey, transformedRow); } catch (Exception ex) { _logger.LogError($"Error writing."); _logger.LogError($"Row data: {transformedRow}"); _logger.LogError($"Document key: {documentKey}"); _logger.LogError($"Exception message: {ex.Message}"); _logger.LogError($"Exception stack trace: {ex.StackTrace}"); } counter++; if ((counter % 1000) == 0) { _logger.LogInformation(counter + "..."); } } } _logger.LogInformation($"Closing non-buffered connection to `{tableSchema}.{tableName}`"); _logger.LogInformation("Done"); }