DynamoDB 查询二级索引,如何定义索引 [英] DynamoDB queries on secondary index, how to define the indexes
问题描述
我一直在这个问题上转来转去,只是不清楚该怎么做.
I've been going around and around this and it's just not clear what to do.
我有一个简单的表,我想在其中对几列进行查询.据我了解,这意味着为要查询的每一列创建一个二级索引.我已经定义了表——使用无服务器框架 serverless.yml
——并且收到了各种奇怪的错误消息.
I have a simple table where I want to make queries against several columns. As I understand it, that means creating a secondary index for each column there is to query against. I've defined the table -- using the Serverless framework serverless.yml
-- and am getting a variety of strange error messages.
当前serverless.yml
定义为:
resources:
Resources:
MessagesDynamoDBTable:
Type: 'AWS::DynamoDB::Table'
Properties:
AttributeDefinitions:
- AttributeName: messageId
AttributeType: S
- AttributeName: room
AttributeType: S
- AttributeName: userId
AttributeType: S
KeySchema:
- AttributeName: messageId
KeyType: HASH
- AttributeName: userId
KeyType: RANGE
LocalSecondaryIndexes:
- IndexName: roomIndex
KeySchema:
- AttributeName: room
KeyType: HASH
Projection:
ProjectionType: "KEYS_ONLY"
- IndexName: userId
KeySchema:
- AttributeName: userId
KeyType: HASH
Projection:
ProjectionType: "KEYS_ONLY"
ProvisionedThroughput:
ReadCapacityUnits: 1
WriteCapacityUnits: 1
TableName: ${self:custom.tableName}
它类似于 Slack 服务 - 因此我想查询来自房间、用户等的条目.
It's meant to be something like a Slack service - and therefore I want to query for entries from a room, by a user, and so forth.
根据我能找到的文档,这个定义是有意义的.一个应该为索引中使用的列声明属性,我已经这样做了.KeySchema - 我真的只需要 messageId 字段,但一条错误消息表明它需要一个 RANGE 索引,所以我将 userId 字段添加到 KeySchema 只是为了关闭它.根据我找到的文档,二级索引字段看起来很正确.
This definition makes sense going by the documentation I've been able to find. One is supposed to declare Attributes for the columns used in indexes, and I've done so. The KeySchema - I really only need the messageId field, but an error message indicated it needed a RANGE index, so I added the userId field to the KeySchema just to shut that up. The secondary index fields look right based on the documentation I've been able to find.
有了这个定义,我在尝试使用 serverless deploy
With this definition I'm getting this error when trying to deploy using serverless deploy
An error occurred: MessagesDynamoDBTable - Property AttributeDefinitions is inconsistent
with the KeySchema of the table and the secondary indexes.
我尝试了几种变体,也遇到了其他奇怪的错误.以下是一些,但我不记得对定义的相应更改.
I have tried several variations and gotten other strange errors as well. What follows is a few, but I don't remember what the corresponding changes were to the definition.
An error occurred: MessagesDynamoDBTable - One or more parameter values were invalid:
Index KeySchema does not have a range key for index: userId (Service: AmazonDynamoDBv2; Status Code: 400;
Error Code: ValidationException; Request ID: 1KFA2IMASC12HRLLDPG753CU63VV4KQNSO5AEMVJF66Q9ASUAAJG).
An error occurred: MessagesDynamoDBTable - 1 validation error detected: Value '[com.amazonaws.dynamodb.v20120810.KeySchemaElement@aa4cdc91,
com.amazonaws.dynamodb.v20120810.KeySchemaElement@d2cd6f64, com.amazonaws.dynamodb.v20120810.KeySchemaElement@4d7c1f9,
com.amazonaws.dynamodb.v20120810.KeySchemaElement@d2cd6f64]' at 'keySchema' failed to satisfy constraint: Member must have length less
than or equal to 2 (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException; Request ID: BOVVBQ1F35VA18CCF3L5MSKS1FVV4KQNSO5AEMVJF66Q9ASUAAJG).
An error occurred: MessagesDynamoDBTable - Property AttributeDefinitions is inconsistent with the KeySchema
of the table and the secondary indexes.
An error occurred: MessagesDynamoDBTable - One or more parameter values were invalid: Index KeySchema does not have a range key for index:
userIdIndex (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException; Request ID: KFS63VSPKDUC60DV6U2V47UP27VV4KQNSO5AEMVJF66Q9ASUAAJG).
An error occurred: MessagesDynamoDBTable - One or more parameter values were invalid: Table KeySchema does not have a range key,
which is required when specifying a LocalSecondaryIndex (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException; Request ID: 86Q2JSPM6Q9UPNIEOVHALLIIQJVV4KQNSO5AEMVJF66Q9ASUAAJG).
推荐答案
它不起作用的原因是本地二级索引中的键必须与表具有相同的分区键.因此,在您的情况下,您的本地二级索引必须将 messageId
作为其 HASH
键,并将 room
和 userId
作为 RANGE
键在它们各自的索引上.并且由于您的表已经由 (messageId, userId)
键入,因此您不需要 userId
本地二级索引.
The reason it isn't working is that the keys in a Local Secondary Index must have the same partition key as the table. So in your case, your Local Secondary Indexes must have messageId
as its HASH
key and room
and userId
as RANGE
keys on their respective indexes. And since your table is already keyed by (messageId, userId)
then you don't need the userId
Local Secondary Index.
此设置在技术上可行:
MessagesDynamoDBTable:
Type: AWS::DynamoDB::Table
Properties:
AttributeDefinitions:
- AttributeName: messageId
AttributeType: S
- AttributeName: room
AttributeType: S
- AttributeName: userId
AttributeType: S
KeySchema:
- AttributeName: messageId
KeyType: HASH
- AttributeName: userId
KeyType: RANGE
LocalSecondaryIndexes:
- IndexName: roomIndex
KeySchema:
- AttributeName: messageId
KeyType: HASH
- AttributeName: room
KeyType: RANGE
Projection:
ProjectionType: KEYS_ONLY
ProvisionedThroughput:
ReadCapacityUnits: 1
WriteCapacityUnits: 1
TableName: ${self:custom.tableName}
但是,如果您想要按房间和用户进行查询,那么您可能希望采用不同的表格设计.您尝试执行的操作最终会要求您始终使用 messageId
作为查询的一部分来查询表,因为它是分区键.因此,您将无法仅通过 room
和 userId
进行查询.您可能想要的是 全球二级索引.在这种情况下,这将起作用:
However if what you want to do is query by rooms and users, then you probably want to go with a different table design. What you are trying to do would end up requiring you to always query the table using the messageId
as part of the query since it is the partition key. So you wouldn't be able to query by just room
and userId
. What you probably want are Global Secondary Indexes. In that case, this would work:
MessagesDynamoDBTable:
Type: AWS::DynamoDB::Table
Properties:
AttributeDefinitions:
- AttributeName: messageId
AttributeType: S
- AttributeName: room
AttributeType: S
- AttributeName: userId
AttributeType: S
KeySchema:
- AttributeName: messageId
KeyType: HASH
GlobalSecondaryIndexes:
- IndexName: roomIndex
KeySchema:
- AttributeName: room
KeyType: HASH
Projection:
ProjectionType: KEYS_ONLY
ProvisionedThroughput:
ReadCapacityUnits: 1
WriteCapacityUnits: 1
- IndexName: userIndex
KeySchema:
- AttributeName: userId
KeyType: HASH
Projection:
ProjectionType: KEYS_ONLY
ProvisionedThroughput:
ReadCapacityUnits: 1
WriteCapacityUnits: 1
ProvisionedThroughput:
ReadCapacityUnits: 1
WriteCapacityUnits: 1
TableName: ${self:custom.tableName}
请注意,使您的 ProjectionType: KEYS_ONLY
意味着当您查询 roomIndex
或 userIndex
时,您将得到的只是 messageIds
- 然后您必须使用 messageIds
重新查询表以获取其他属性.您可能想要使用不同的 ProjectionType
,具体取决于您的使用模式.
Note that making your ProjectionType: KEYS_ONLY
means when you query roomIndex
or userIndex
what you would get back is just messageIds
- you would then have to requery the table with the messageIds
to get other attributes. You might want to use a different ProjectionType
depending on what your usage pattern is.
这篇关于DynamoDB 查询二级索引,如何定义索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!