DynamoDB中的表设计 [英] Table design in DynamoDB

问题描述

我是NoSQL DB和Serverless的初学者。我的应用程序有一个名为 Trips 的表。表的参数是 {id，路线，成本，销售，类型，日期，LR，资产} 和一堆其他不相关的文档编号，其中id是由uuid。

I am a beginner in NoSQL DB and Serverless. My app has a table called Trips. The parameters of the tables are {id, route, cost, selling, type, date, LR, asset } and a bunch of other irrelevant document numbers, where id is generated by uuid.

现在我想查询数据库中的给我

Now I want to query the database for giving me

1. 返回使用日期参数的日期范围内的所有行程。


2. 使用日期和资产参数返回给定时间段内资产的所有行程。


3. 使用日期路线参数返回给定时间内所有路线的行程。

2和3使用keyConditionExpression可以正常工作，但是对于1，我需要在扫描上使用filterExpression而不是查询，这会使它相对较慢，因为一旦查询完成就执行它。有没有更好的方式来形成架构？

2 and 3 work fine using keyConditionExpression but for 1 I need to use a filterExpression on a scan instead of a query which could make it relatively slower since it is executed once the query is complete. Is there a better way to form the schema?

在 Trips 表中，架构就是这样的

In Trips table the schema is as such

 tripTable:
  Type: "AWS::DynamoDB::Table"
  Properties:
    AttributeDefinitions:
      [
        { "AttributeName": "id", "AttributeType": "S" },
        { "AttributeName": "date", "AttributeType": "S" },
        { "AttributeName": "Asset", "AttributeType": "S" },
        { "AttributeName": "Route", "AttributeType": "S" },
      ]

    KeySchema:
      [
        { "AttributeName": "date", "KeyType": "HASH" },
        { "AttributeName": "id", "KeyType": "RANGE" },
      ]
    ProvisionedThroughput:
      ReadCapacityUnits: 5
      WriteCapacityUnits: 5
    StreamSpecification:
      StreamViewType: "NEW_AND_OLD_IMAGES"
    TableName: ${self:provider.environment.TRIPS}
    GlobalSecondaryIndexes:
      - IndexName: TripsVSAssets
        KeySchema:
          - AttributeName: asset
            KeyType: HASH
          - AttributeName: date
            KeyType: RANGE
        Projection:
          ProjectionType: ALL
        ProvisionedThroughput:
          ReadCapacityUnits: "5"
          WriteCapacityUnits: "5"
        GlobalSecondaryIndexes:
      - IndexName: RoutesVSAssets
        KeySchema:
          - AttributeName: route
            KeyType: HASH
          - AttributeName: date
            KeyType: RANGE
        Projection:
          ProjectionType: ALL
        ProvisionedThroughput:
          ReadCapacityUnits: "5"
          WriteCapacityUnits: "5"

推荐答案

我遇到了类似的问题最近并选择使用 year 作为分区键，并使用date作为排序键。这对我的数据量来说是正确的，我可以按日期查询，并且大多数情况下只运行一个查询即可。如果您有大量数据，也许 month 或什至 week 可能更合适（或完全其他）。

I had a similar problem recently and opted for using year as partition key and date as sort key. This was right for my amount of data and let me query by date and mostly just run a single query. If you have a lot of data, maybe month or even week would be more suitable (or something else entirely).

使用我的方法，我只需要检查我想查看的日期范围是否跨两年，在这种情况下（即很少），Lambda两个查询并合并结果。为了方便起见，我在下面包括了一些代码草稿（可能有更好的方法，但这对我有用！），我还建议您快速阅读以下内容：> https://aws.amazon.com/blogs/database/choosing-the-right-dynamodb-partition-key/ 。

With my approach I then just needed to check for whether the date range I want to look at cuts across two years and in those cases (i.e. very rarely) the Lambda makes two queries and combines the results. I've included some draft code below in case it's useful (there are probably better ways, but this worked for me!) and I also recommend this quick read: https://aws.amazon.com/blogs/database/choosing-the-right-dynamodb-partition-key/.

module.exports.getLatest = async event => {

  // some date and formatting code here not included

  var params1 = {
    ExpressionAttributeNames: { "#date": "date", "#year": "year" },
    ExpressionAttributeValues: {
      ':d': isoDate,
      ':y1': y1
     },
   KeyConditionExpression: '#year = :y1 AND #date > :d',
   TableName: process.env.HEADLINES_TABLE
  }

  if (y1 != y2) {
   // define var params2 (the same as params1 except it uses y2)
  }

  try {
    let result;

    // if the date range cuts across partitions (years), fire off two queries and wait for both

    if(y1 != y2) {
        let resultPromise1 = client.query(params1).promise();
        let resultPromise2 = client.query(params2).promise();
        const [result1, result2] = await Promise.all([resultPromise1,resultPromise2]);
        result = [...result1.Items, ...result2.Items];
    } else { 
        result = await client.query(params1).promise();
    }

    return {
      // stringify and return result.Items, statuscode 200 etc.
    }
  }
  // catch {} code here (irrelevant for the answer)
}

这篇关于DynamoDB中的表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！