public static IEnumerable <DynamicTableEntity> ExecuteQuery(this CloudTable ct, TableQuery tq) { TableContinuationToken t = new TableContinuationToken(); #if DEBUG int iCounter = 0; #endif while (t != null) { var segment = ct.ExecuteQuerySegmented(tq, t); foreach (var result in segment.Results) { #if DEBUG iCounter++; #endif yield return(result); } t = segment.ContinuationToken; } #if DEBUG Debug.WriteLine("ExecuteQuery: (Count): {0}", iCounter); Debug.WriteLine("ExecuteQuery (Query): " + tq.FilterString); #endif }
/// <summary> /// Create a local file copy of the specified Azure Table. /// </summary> /// <param name="TableName">Name of Azure Table to backup.</param> /// <param name="OutFileDirectory">Local directory (path) with authority to create/write a file.</param> /// <param name="Compress">True to compress the file.</param> /// <param name="Validate">True to validate the written record count matches what was queried.</param> /// <param name="TimeoutSeconds">Set timeout for table client.</param> /// <param name="filters">A list of Filter objects to be applied to table values extracted.</param> /// <returns>A string containing the name of the file created.</returns> public string BackupTableToFile(string TableName, string OutFileDirectory, bool Compress = false, bool Validate = false, int TimeoutSeconds = 30, List <Filter> filters = default(List <Filter>)) { if (String.IsNullOrWhiteSpace(TableName)) { throw new ParameterSpecException("TableName is missing."); } if (String.IsNullOrWhiteSpace(OutFileDirectory)) { throw new ParameterSpecException("OutFileDirectory is missing."); } string OutFileName = String.Format(TableName + "_Backup_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt"); string OutFileNameCompressed = String.Format(TableName + "_Backup_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".7z"); int RecordCount = 0; if (!Filter.AreFiltersValid(filters)) { throw new ParameterSpecException(String.Format("One or more of the supplied filter criteria is invalid.")); } string OutFileCreated = ""; string OutFileNamePath = ""; string OutFileNamePathCompressed = ""; if (Path.GetFullPath(OutFileDirectory) != OutFileDirectory) { throw new ParameterSpecException(String.Format("Invalid output directory '{0}' specified.", OutFileDirectory)); } else { OutFileNamePath = Path.Combine(OutFileDirectory, OutFileName); OutFileNamePathCompressed = Path.Combine(OutFileDirectory, OutFileNameCompressed); } if (!CosmosTable.CloudStorageAccount.TryParse(new System.Net.NetworkCredential("", AzureTableConnectionSpec).Password, out CosmosTable.CloudStorageAccount StorageAccount)) { throw new ConnectionException("Can not connect to CloudStorage Account. Verify connection string."); } try { CosmosTable.CloudTableClient client = CosmosTable.CloudStorageAccountExtensions.CreateCloudTableClient(StorageAccount, new CosmosTable.TableClientConfiguration()); CosmosTable.CloudTable table = client.GetTableReference(TableName); table.ServiceClient.DefaultRequestOptions.ServerTimeout = new TimeSpan(0, 0, TimeoutSeconds); CosmosTable.TableContinuationToken token = null; var entities = new List <CosmosTable.DynamicTableEntity>(); var entitiesSerialized = new List <string>(); DynamicTableEntityJsonSerializer serializer = new DynamicTableEntityJsonSerializer(); TableSpec TableSpecStart = new TableSpec(TableName); using (StreamWriter OutFile = new StreamWriter(OutFileNamePath)) { OutFile.WriteLine(JsonConvert.SerializeObject(TableSpecStart)); CosmosTable.TableQuery <CosmosTable.DynamicTableEntity> tq; if (default(List <Filter>) == filters) { tq = new CosmosTable.TableQuery <CosmosTable.DynamicTableEntity>(); } else { tq = new CosmosTable.TableQuery <CosmosTable.DynamicTableEntity>().Where(Filter.BuildFilterSpec(filters)); } do { var queryResult = table.ExecuteQuerySegmented(tq, token); foreach (CosmosTable.DynamicTableEntity dte in queryResult.Results) { OutFile.WriteLine(serializer.Serialize(dte)); RecordCount++; } token = queryResult.ContinuationToken; } while (token != null); TableSpec TableSpecEnd = new TableSpec(TableName, RecordCount); OutFile.WriteLine(JsonConvert.SerializeObject(TableSpecEnd)); OutFile.Flush(); OutFile.Close(); } if (Validate) { int InRecords = 0; // Read file/validate footer using (StreamReader InFile = new StreamReader(OutFileNamePath)) { string HeaderRec = InFile.ReadLine(); string FooterRec = ""; string DetailRec = "x"; do { DetailRec = InFile.ReadLine(); if (DetailRec == null) { InRecords--; } else { InRecords++; FooterRec = DetailRec; } } while (DetailRec != null); InFile.Close(); TableSpec footer = JsonConvert.DeserializeObject <TableSpec>(FooterRec); if ((footer.RecordCount == InRecords) && (footer.TableName.Equals(TableName))) { //Do nothing, in count=out count } else { throw new AzureTableBackupException("Backup file validation failed."); } } } // https://stackoverflow.com/questions/11153542/how-to-compress-files if (Compress) { FileStream FileToCompress = File.OpenRead(OutFileNamePath); byte[] buffer = new byte[FileToCompress.Length]; FileToCompress.Read(buffer, 0, buffer.Length); FileStream CompressedFileTarget = File.Create(OutFileNamePathCompressed); using (GZipStream OutFileCompressed = new GZipStream(CompressedFileTarget, CompressionMode.Compress)) { OutFileCompressed.Write(buffer, 0, buffer.Length); } FileToCompress.Close(); CompressedFileTarget.Close(); if (File.Exists(OutFileNamePath)) { try { File.Delete(OutFileNamePath); } catch (Exception ex) { throw new AzureTableBackupException(String.Format("Error cleaning up files '{0}'.", OutFileNamePath), ex); } } OutFileCreated = OutFileNameCompressed; } else { OutFileCreated = OutFileName; } } catch (Exception ex) { throw new BackupFailedException(String.Format("Table '{0}' backup failed.", TableName), ex); } return(OutFileCreated); }
/// <summary> /// This method will delete rows for the table name specified corresponding to the filter criteria. If no filter criteria is specified, all rows will be deleted. /// </summary> /// <param name="TableNameToDelete">Name of table from entries will be deleted.</param> /// <param name="TimeoutSeconds">Set timeout for table client.</param> /// <param name="filters">A list of Filter objects to be applied to table rows to delete.</param> /// <returns>A string indicating the result of the operation.</returns> public string DeleteAzureTableRows(string TableNameToDelete, int TimeoutSeconds = 30, List <Filter> filters = default(List <Filter>)) { if (String.IsNullOrWhiteSpace(TableNameToDelete)) { throw new ParameterSpecException("TableNameToDelete rows from is missing."); } if (!Filter.AreFiltersValid(filters)) { throw new ParameterSpecException(String.Format("One or more of the supplied filter criteria is invalid.")); } try { if (!CosmosTable.CloudStorageAccount.TryParse(new System.Net.NetworkCredential("", AzureTableConnectionSpec).Password, out CosmosTable.CloudStorageAccount StorageAccountSource)) { throw new ConnectionException("Can not connect to CloudStorage Account for Source Table. Verify connection string."); } CosmosTable.CloudTableClient clientSource = CosmosTable.CloudStorageAccountExtensions.CreateCloudTableClient(StorageAccountSource, new CosmosTable.TableClientConfiguration()); CosmosTable.CloudTable TableSource = clientSource.GetTableReference(TableNameToDelete); TableSource.ServiceClient.DefaultRequestOptions.ServerTimeout = new TimeSpan(0, 0, TimeoutSeconds); //CosmosTable.CloudTable TableDest = TableSource; bool BatchWritten = true; string PartitionKey = String.Empty; CosmosTable.TableBatchOperation Batch = new CosmosTable.TableBatchOperation(); int BatchSize = 100; int BatchCount = 0; long TotalRecordCountIn = 0; long TotalRecordCountOut = 0; CosmosTable.TableContinuationToken token = null; do { CosmosTable.TableQuery <CosmosTable.DynamicTableEntity> tq; if (default(List <Filter>) == filters) { tq = new CosmosTable.TableQuery <CosmosTable.DynamicTableEntity>(); } else { tq = new CosmosTable.TableQuery <CosmosTable.DynamicTableEntity>().Where(Filter.BuildFilterSpec(filters)); } var queryResult = TableSource.ExecuteQuerySegmented(tq, token); foreach (CosmosTable.DynamicTableEntity dte in queryResult.Results) { TotalRecordCountIn++; if (String.Empty.Equals(PartitionKey)) { PartitionKey = dte.PartitionKey; } if (dte.PartitionKey == PartitionKey) { Batch.Delete(dte); //Batch.InsertOrReplace(dte); BatchCount++; TotalRecordCountOut++; BatchWritten = false; } else { try { TableSource.ExecuteBatch(Batch); Batch = new CosmosTable.TableBatchOperation(); PartitionKey = dte.PartitionKey; Batch.Delete(dte); //Batch.InsertOrReplace(dte); BatchCount = 1; TotalRecordCountOut++; BatchWritten = false; } catch (Exception ex) { throw new DeleteFailedException(String.Format("Table '{0}' row delete failed.", TableNameToDelete), ex); } } if (BatchCount >= BatchSize) { try { TableSource.ExecuteBatch(Batch); PartitionKey = String.Empty; Batch = new CosmosTable.TableBatchOperation(); BatchWritten = true; BatchCount = 0; } catch (Exception ex) { throw new DeleteFailedException(String.Format("Table '{0}' row deletion failed.", TableNameToDelete), ex); } } } token = queryResult.ContinuationToken; } while (token != null); if (!BatchWritten) { try { TableSource.ExecuteBatch(Batch); PartitionKey = String.Empty; } catch (Exception ex) { throw new DeleteFailedException(String.Format("Table '{0}' row deletion failed.", TableNameToDelete), ex); } } return(String.Format("Table '{0}' total rows deleted {1}.", TableNameToDelete, TotalRecordCountIn)); } catch (ConnectionException cex) { throw cex; } catch (Exception ex) { throw new DeleteFailedException(String.Format("Table '{0}' row deletion failed.", TableNameToDelete), ex); } finally { } }
/// <summary> /// Backup table directly to Blob. /// /// </summary> /// <param name="TableName">Name of Azure Table to backup.</param> /// <param name="BlobRoot">Name to use as blob root folder.</param> /// <param name="Compress">True to compress the file.</param> /// <param name="RetentionDays">Process will age files in blob created more than x days ago.</param> /// <param name="TimeoutSeconds">Set timeout for table client.</param> /// <param name="filters">A list of Filter objects to be applied to table values extracted.</param> /// <returns>A string containing the name of the file created.</returns> public string BackupTableToBlobDirect(string TableName, string BlobRoot, bool Compress = false, int RetentionDays = 30, int TimeoutSeconds = 30, List <Filter> filters = default(List <Filter>)) { string OutFileName = ""; int RecordCount = 0; int BackupsAged = 0; if (String.IsNullOrWhiteSpace(TableName)) { throw new ParameterSpecException("TableName is missing."); } if (String.IsNullOrWhiteSpace(BlobRoot)) { throw new ParameterSpecException("BlobRoot is missing."); } try { if (Compress) { OutFileName = String.Format(TableName + "_Backup_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt.7z"); } else { OutFileName = String.Format(TableName + "_Backup_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt"); } if (!CosmosTable.CloudStorageAccount.TryParse(new System.Net.NetworkCredential("", AzureTableConnectionSpec).Password, out CosmosTable.CloudStorageAccount StorageAccount)) { throw new ConnectionException("Can not connect to CloudStorage Account. Verify connection string."); } if (!AZStorage.CloudStorageAccount.TryParse(new System.Net.NetworkCredential("", AzureBlobConnectionSpec).Password, out AZStorage.CloudStorageAccount StorageAccountAZ)) { throw new ConnectionException("Can not connect to CloudStorage Account. Verify connection string."); } AZBlob.CloudBlobClient ClientBlob = AZBlob.BlobAccountExtensions.CreateCloudBlobClient(StorageAccountAZ); var container = ClientBlob.GetContainerReference(BlobRoot); container.CreateIfNotExists(); AZBlob.CloudBlobDirectory directory = container.GetDirectoryReference(BlobRoot.ToLower() + "-table-" + TableName.ToLower()); AZBlob.CloudBlockBlob BlobBlock = directory.GetBlockBlobReference(OutFileName); // start upload from stream, iterate through table, possible inline compress try { CosmosTable.CloudTableClient client = CosmosTable.CloudStorageAccountExtensions.CreateCloudTableClient(StorageAccount, new CosmosTable.TableClientConfiguration()); CosmosTable.CloudTable table = client.GetTableReference(TableName); table.ServiceClient.DefaultRequestOptions.ServerTimeout = new TimeSpan(0, 0, TimeoutSeconds); CosmosTable.TableContinuationToken token = null; var entities = new List <CosmosTable.DynamicTableEntity>(); var entitiesSerialized = new List <string>(); DynamicTableEntityJsonSerializer serializer = new DynamicTableEntityJsonSerializer(); TableSpec TableSpecStart = new TableSpec(TableName); var NewLineAsBytes = Encoding.UTF8.GetBytes("\n"); var tempTableSpecStart = Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(TableSpecStart)); AZBlob.CloudBlobStream bs2 = BlobBlock.OpenWrite(); Stream bs = BlobBlock.OpenWrite(); if (Compress) { bs = new GZipStream(bs2, CompressionMode.Compress); } else { bs = bs2; } bs.Write(tempTableSpecStart, 0, tempTableSpecStart.Length); bs.Flush(); bs.Write(NewLineAsBytes, 0, NewLineAsBytes.Length); bs.Flush(); CosmosTable.TableQuery <CosmosTable.DynamicTableEntity> tq; if (default(List <Filter>) == filters) { tq = new CosmosTable.TableQuery <CosmosTable.DynamicTableEntity>(); } else { tq = new CosmosTable.TableQuery <CosmosTable.DynamicTableEntity>().Where(Filter.BuildFilterSpec(filters)); } do { var queryResult = table.ExecuteQuerySegmented(tq, token); foreach (CosmosTable.DynamicTableEntity dte in queryResult.Results) { var tempDTE = Encoding.UTF8.GetBytes(serializer.Serialize(dte)); bs.Write(tempDTE, 0, tempDTE.Length); bs.Flush(); bs.Write(NewLineAsBytes, 0, NewLineAsBytes.Length); bs.Flush(); RecordCount++; } token = queryResult.ContinuationToken; } while (token != null); TableSpec TableSpecEnd = new TableSpec(TableName, RecordCount); var tempTableSpecEnd = Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(TableSpecEnd)); bs.Write(tempTableSpecEnd, 0, tempTableSpecEnd.Length); bs.Flush(); bs.Write(NewLineAsBytes, 0, NewLineAsBytes.Length); bs.Flush(); bs.Close(); } catch (Exception ex) { throw new BackupFailedException(String.Format("Table '{0}' backup failed.", TableName), ex); } DateTimeOffset OffsetTimeNow = System.DateTimeOffset.Now; DateTimeOffset OffsetTimeRetain = System.DateTimeOffset.Now.AddDays(-1 * RetentionDays); //Cleanup old versions var BlobList = directory.ListBlobs().OfType <AZBlob.CloudBlockBlob>().ToList();; foreach (var blob in BlobList) { if (blob.Properties.Created < OffsetTimeRetain) { try { blob.Delete(); BackupsAged++; } catch (Exception ex) { throw new AgingException(String.Format("Error aging file '{0}'.", blob.Name), ex); } } } return(String.Format("Table '{0}' backed up as '{2}' under blob '{3}\\{4}'; {1} files aged.", TableName, BackupsAged, OutFileName, BlobRoot, directory.ToString())); } catch (ConnectionException cex) { throw cex; } catch (Exception ex) { throw new BackupFailedException(String.Format("Table '{0}' backup failed.", TableName), ex); } finally { } } // BackupTableToBlobDirect