Retrieving Chat Bot conversation data in Azure

Question

A brief background first: The Bot framework stores the conversation data within a storage either Azure tables/Cosmos DB (Azure tables in my case). For each conversation there is an entry made into the Azure table with the timestamp, userid, conversation messages and other details.

I am trying to retrieve the conversation details from Azure table storage using custom code in C# as shown below.

Microsoft.WindowsAzure.Storage.CloudStorageAccount storageAccount = Microsoft.WindowsAzure.Storage.CloudStorageAccount.Parse(connectionString);
Microsoft.WindowsAzure.Storage.Table.CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference("botdata");
TableQuery<DynamicTableEntity> projectionQuery = new TableQuery<DynamicTableEntity>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "directline:user"));
var dataRow = table.ExecuteQuery(projectionQuery).Where(q => q.RowKey == "souvik").FirstOrDefault();
var conversation = Encoding.UTF8.GetString(dataRow.Properties["Data"].BinaryValue);
Console.WriteLine(conversation);

I am getting some gibberish text, something like this- \u001f�\b\0\0\0\0\0\u0004\0��\u0005\0C���\u0002\0\0\0 when I retrieve the data stored in Binary format in the Azure table storage. The data looks something like as shown below in the Azure storage explorer-

I have tried and tested for each conversation and I have seen that each time I type and send a data to the bot and get a response, new records are getting added to the Azure table with correct timestamp. Is it the correct way of retrieving the data or am I doing something wrong? Let me know if I can add more details.

Update-1:

I have referred this- How to retrieve Saved Conversation Data in Azure (Tablelogger) but did not get much help my issue.

Update-2

Based on Fei's answer below, I just noticed when I use the PartitionKey as directline:user or directline:conversation I get the deserialized values as {} but if I use directline:private I get somthing like this-

{"ResumptionContext":{"locale":"en-US","isTrustedServiceUrl":true}, "DialogState":"H4sIAAAAAAAEAO1a328iVRRmYGYKbTfbdZtNzLq7GM26DWS2FGi7MVUptUrcVi1VYxrSvcCFjh1mmrl3quyjb74YEx+N/hW+uzFxE/8KX9fsH6HnzAxQ7A8GhHpZoemBDs O953znO+d+99KQFAqF/oIHPuNjNgzm8y29YlvMqnFt3YJfRzeq1E7GP6U20y1zLa2llrQlLZOM5x2DOzZdM6nDbWIk4x85ZUOvfECbu9YhhTtT5Vp6NbtMqunlDE1nFZznF+nM8bWCyaltEoNpm 3oZpvKeHqX29s6+f0MnhlVnJz7nXdkl7DAZH1kMpVIEglAYJ5VDtWaTBmXKl0TnLCLLT8LFJuO0oeUtw6AVDjMx7T1qUluvaA91xs+P5lT0hU0cW8DwRzo4CYhOESAlhv6YIMaJIrdsWjAhJ2aF rjd3m0e06Ni2VSecJj4sfwGp2KE1alN4Gxl+ObPgbwzJEpPRKGBk/HPCkmFUoLqvA4ZM2Wf6YxrdP/aGBaR/DdpfhMVur4TUiUZjqs8i5I8iXwpxsUPLeEfkjs/ST0ydN7sGfN/CWV16T4GRFN /FWBTMFDodkvxL8pOxz4YbZwzNNJgrYRlj7gUOMlTeIJzE3DsQ0NkcY7RRNprbEKcUkqLqDNz0NPBqiPBskgrksikgSphsdXak6kFy6YXzXAHzW1DgPoPFWWzcYoibEutLGwlcLHKD2Icyai JZvpyV9odwH1x4tCQaask9v5t4oSXjDVaxbEMvd0bPaIv403vw8soKyVayy6kH6QxdXH3gpcTtYVfRzGFzxkbmdrRraF7CS1dbPfw6cnFuAmpvUGWbMi6blFZlOTBYO/CZ/yNYiYD4bAOcbcb OIxefw/60n0+j9Jg6JoZD9/dD0bAvRhSk+tNxp3T32HnLNCkubFphizJG6jQHu4lj0Bv/9KJ95xC7/An2B8ZVVPYLhOtAhXIDWf4nFMpz/zBHwqZ+rf3Ws5NvXW/JqJdDfeyWth3DcMtCPNHR 0VDz/axbopJxlK3Y3Zdw0FMqJ3adcrVB+YFVjcg9t3s/nUOUi3BK7B445iHg7HhiddSLzH1/AhCH/sGKtgVbLmoXzJp13hb2JppX0NxCCt3oZ50QlUKC9LPB2fb7ENgmCAgDE/M2mjto4ti2b 7ba9quTJndxK+isB1jWojcuXJNV251K/qaHr55HuRr3vhi5lG3ja1jEtwYgMZJVxsOurpOvWN4gjLmvinrdJOjWdPvV0rQ3JG755/wz8Zxdh6BNziR8hCLRK74jeNNeSX0d50GeuzlX745BztU 3wMmvI604DmxKqrpZ19AP5lrwIOjBqa/K+o+4VIojbPcCn9D6X8gNMNM5zWSQBnSGYwVAEfzqyhhMeQrePJ4nGYbLUTc1C+o9SMQz5eJE9JsJwZrtaL+LGYxCYwLRcPk50pYSiO54gj4t3W4piY WJ1h2azOuIDpRsL5B+PalPfu5PnwgSQVvKJFBHo5hGOaMmu3WD9mKlTb0P8fw4kRjDbeFBkxtQfSxCjr5XJ+pjoj4uQX0I0piCCxUFt54CbYkHP876tkcQ+QOim4mHlnXkXRH2sDSFZglNGsxsBs zax2YOYur4lQrql+kYhoJr8njKisHpcLfbryJpHBlU83DUdiyLe4FnBs1SFs0ymhVUPKmW3l8Vq546khkZJVqNoO6dzkE3rdAqNbncR9LCLfyldEtsvglmxseWNc2KpznXBIxbfQuc+k66QJTET8 RxztrbXrm8F1B5nH7Fe65zOfyvIVI26KnTnAX1bXDrj//erZFi72/Qs62CfWdsm2OntrEOAtYO3BnC+pFWWmWzDmbe93wHKlE/plU3wWr+Xy896kZwz9R33UxcxL6zvBwdDQNvRBbUTdxkhMVxXRSKesU28zeNVmRMBTMAAA=="}

I have a feeling that the DialogState data in the JSON above needs to be decrypted?

For every message I type and the response I receive, there are three records inserted into the Azure table each with a different PartitionKey which are- directline:private, directline:user and directline:conversation.

Answer 1

Binary data in Azure Table Storage is stored as Base64 encoded string. What you would need to do is convert this string to bytes first and then get the string from those bytes.

Something like:

var conversation = Encoding.UTF8.GetString(Convert.FromBase64String(dataRow.Properties["Data"].BinaryValue));

Answer 2

If you’d like to use WindowsAzure.Storage client library to retrieve entities from table storage and extract data from Data property, you can refer to the following code.

CloudStorageAccount storageAccount = CloudStorageAccount.Parse("DefaultEndpointsProtocol=https;AccountName={your_account_name};AccountKey={your_account_key};EndpointSuffix=core.windows.net");

CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

CloudTable table = tableClient.GetTableReference("botdata");

TableQuery<MessageEntity> query = new TableQuery<MessageEntity>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "emulator:user"));

foreach (MessageEntity entity in table.ExecuteQuery(query))
{
    string mydata = "";
    using (var msi = new MemoryStream(entity.Data))
    using (var mso = new MemoryStream())
    {
        using (var gs = new GZipStream(msi, CompressionMode.Decompress))
        {
            gs.CopyTo(mso);
        }
        mydata = Encoding.UTF8.GetString(mso.ToArray());
    }

    object data = JsonConvert.DeserializeObject(mydata);

    //.....
}

MessageEntity:

public class MessageEntity : TableEntity
{
    public MessageEntity(string pk, string rk)
    {
        this.PartitionKey = pk;
        this.RowKey = rk;
    }

    public MessageEntity() { }

    public string BotId { get; set; }
    public string ChannelId { get; set; }
    public string ConversationId { get; set; }
    public byte[] Data { get; set; }
    public string UserId { get; set; }
}

Test result:

Note:

• In source code, you can find the Data property defined as byte[] in the BotDataEntity class.
• In Jason Sowers's reply, he has shared the information about serialize and deserialize of the Data property.

Answer 3

I am assuming you are using the botbuilder-azure package

don't do this in a dialog because you can just access the same data in the context object. it just happens to be where I wrote the code

It's actually as simple as this:

private async Task MessageReceivedAsync(IDialogContext context, IAwaitable<object> result)
{
    var activity = await result as Activity;

    IBotDataStore<BotData> table = new TableBotDataStore(ConfigurationManager.ConnectionStrings["StorageConnectionString"].ConnectionString);

//use the type of state data you need
    var userData = await table.LoadAsync(Address.FromActivity(activity), BotStoreType.BotUserData, CancellationToken.None );
    var privateConvoData = await table.LoadAsync(Address.FromActivity(activity), BotStoreType.BotPrivateConversationData, CancellationToken.None );
    var convoData = await table.LoadAsync(Address.FromActivity(activity), BotStoreType.BotConversationData, CancellationToken.None);

//in this case I am just replying with the data, but do what you need with it here
    var reply = activity.CreateReply(userData.Data.ToString());
    var reply2 = activity.CreateReply(privateConvoData.Data.ToString());
    var reply3 = activity.CreateReply(convoData.Data.ToString());

    await context.PostAsync(reply);
    await context.PostAsync(reply2);
    await context.PostAsync(reply3);

    context.Wait(MessageReceivedAsync);
}

If you notice in the source for botbuilder-azure there is a serialize and deserialize method. Where this happens:

private byte[] Serialize(object data)
{
    using (var cmpStream = new MemoryStream())
    using (var stream = new GZipStream(cmpStream, CompressionMode.Compress))
    using (var streamWriter = new StreamWriter(stream))
    {
        var serializedJSon = JsonConvert.SerializeObject(data, serializationSettings);
        streamWriter.Write(serializedJSon);
        streamWriter.Close();
        stream.Close();
        return cmpStream.ToArray();
    }
}

So the data you need is compressed into the "jibberish" you were seeing. When accessing the data via the LoadAsync method it is also being decompressed like here:

private object Deserialize(byte[] bytes)
{
    using (var stream = new MemoryStream(bytes))
    using (var gz = new GZipStream(stream, CompressionMode.Decompress))
    using (var streamReader = new StreamReader(gz))
    {
        return JsonConvert.DeserializeObject(streamReader.ReadToEnd());
    }
}

deserialize is called inside the LoadAsync method in the return statement return new BotData(entity.ETag, entity.GetData()); It its the GetData() method like below:

internal ObjectT GetData<ObjectT>()
{
    return ((JObject)Deserialize(this.Data)).ToObject<ObjectT>();
}