Search DynamoDB in C # for time series data in a range using GSI

3 minute read

It is a method to search the time series data stored in Amazon DynamoDB in C # by specifying the range using GSI (Global Secondary Index).

[Preparation 1] Data

So that you can save the data of the following structure

[DynamoDBTable("SampleTable")]
public class Sample
{
    /// <summary>"Sample Table" table hash key</summary>
    [DynamoDBHashKey]
    public string Id { get; set; }

    /// <summary>Range key for the "Sample Table" table</summary>
    [DynamoDBRangeKey]
    public string DateAndTime { get; set; }

    /// <summary>「Date-Time-index "GSI hash key</summary>
    [DynamoDBGlobalSecondaryIndexHashKey]
    public string Date { get; set; }

    /// <summary>「Date-Time-index ”GSI range key</summary>
    [DynamoDBGlobalSecondaryIndexRangeKey]
    public string Time { get; set; }

    [DynamoDBProperty]
    public string Message { get; set; }

    public override string ToString()
    {
        return $"{Id} / {DateAndTime} / {Message}";
    }
}

From the AWS Console, prepare a SampleTable table with ʻId and DateAndTime` as keys.
table.png

Next, create 10 data using the SaveAsync method as shown below.

//Prepare an object to access DynamoDB
using var client = new AmazonDynamoDBClient(RegionEndpoint.APNortheast1);
using var context = new DynamoDBContext(client);

//Create 10 data
for (var i = 0; i < 10; i++)
{
    var now = DateTime.Now;

    await context.SaveAsync(new Sample
    {
        Id = $"{i % 3 + 1:D3}",
        DateAndTime = $"{now:yyyy-MM-dd HH:mm:ss.fff}",
        Date = $"{now:yyyy-MM-dd}",
        Time = $"{now:HH:mm:ss}",
        Message = $"message- {i + 1}"
    });

    //I want to shift the save time, so wait 500 msec
    await Task.Delay(500);
}

Get all the data using the ScanAsync method as shown below.

//Get all data
var listAll = await context.ScanAsync<Sample>(null).GetRemainingAsync();

Console.WriteLine("--- listAll");
listAll.OrderBy(n => n.DateAndTime).ToList().ForEach(Console.WriteLine);

Then, you can get the data as below.

--- listAll
001 / 2020-08-11 11:40:40.236 /message- 1
002 / 2020-08-11 11:40:41.224 /message- 2
003 / 2020-08-11 11:40:41.756 /message- 3
001 / 2020-08-11 11:40:42.293 /message- 4
002 / 2020-08-11 11:40:42.824 /message- 5
003 / 2020-08-11 11:40:43.355 /message- 6
001 / 2020-08-11 11:40:43.877 /message- 7
002 / 2020-08-11 11:40:44.412 /message- 8
003 / 2020-08-11 11:40:44.936 /message- 9
001 / 2020-08-11 11:40:45.458 /message- 10

[Preparation 2] GSI

From the AWS Console, you can confirm that the data is saved as shown below.
aws.png

At the end of the preparation, prepare a Date-Time-index index (GSI) with Date and Time as keys from the AWS Console so that you can search by specifying the date and time.
gsi.png

Now you are ready to go.

[Scenario 1] I want to get the data of Id = 001

Get the data using the QueryAsync () method with 001 as the hash key as shown below.

var list01 = await context.QueryAsync<Sample>("001").GetRemainingAsync();

Console.WriteLine("--- list01");
list01.OrderBy(n => n.DateAndTime).ToList().ForEach(Console.WriteLine);

Then, you can get the data as below.

--- list01
001 / 2020-08-11 11:40:40.236 /message- 1
001 / 2020-08-11 11:40:42.293 /message- 4
001 / 2020-08-11 11:40:43.877 /message- 7
001 / 2020-08-11 11:40:45.458 /message- 10

Only the data with Id of 001 can be obtained.

[Scenario 2] I want to acquire data from 11:40:42 to 11:40:43 on 2020-08-11.

Specify the GSI name Date-Time-index as shown below, specify 2020-08-11 for the hash key, and QueryOperator.Between and 11:40:42-for the range key. Get the data using the QueryAsync () method with 11:40:43 .

var list02 = await context.QueryAsync<Sample>(
    "2020-08-11",
    QueryOperator.Between,
    new List<object>
    {
        "11:40:42",
        "11:40:43"
    },
    new DynamoDBOperationConfig
    {
        IndexName = "Date-Time-index" //Specify the GSI to use
    }).GetRemainingAsync();

Console.WriteLine("--- list02");
list02.OrderBy(n => n.DateAndTime).ToList().ForEach(Console.WriteLine);

Then, you can get the data as below.

--- list02
001 / 2020-08-11 11:40:42.293 /message- 4
002 / 2020-08-11 11:40:42.824 /message- 5
003 / 2020-08-11 11:40:43.355 /message- 6
001 / 2020-08-11 11:40:43.877 /message- 7

Only the data of 11: 40: 42 --11: 40: 43 can be obtained across the hash key ʻId of the SampleTable` table.

at the end

I was able to search by specifying the date and time using GSI.
However, when it comes to cost considerations and NoSQL-like design,

Please let me know if there is a better way m (_ _) m

You May Also Enjoy