CODEWITHSUNDEEP
javaamazon-web-servicesamazon-dynamodb
Nicholas Muir
Nicholas Muir

Reputation: 3134

DynamoDB get single column list of range keys or global secondary

I have a DynamoDB table that contains videos info.

Currently "videoID"is the primary (hash) key and "Category" is the range (sort) key.

I want to get a list of all of the "Categories" (Range keys) so I can allow the user to select from one of the available video categories.

https://www.quora.com/What-are-some-good-ways-to-extract-one-single-column-from-a-DynamoDB-table

I was reading that if you modified change the attribute "Category" to a global secondary index you can return the items for that GSI. But I have not been able to find how to do that.

http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSIJavaDocumentAPI.html

So I guess that gives me three questions:

Is there a way to do to find the items in Category by querying just the range key?

If change Category to a GSI can I fiind the items that way?

or

Is the only way of doing it scanning the whole table?

Thanks in advance for your help

Upvotes: 0

Views: 2466

Answers (2)

notionquest
notionquest

Reputation: 39226

Here is the sample code to create Videos table with GSI.

Create "Videos" table with GSI:-

@Autowired
    private AmazonDynamoDBClient dynamoDBClient;

    public Boolean createTableWithGlobalSecondaryIndex(String tableName) {

        CreateTableRequest createTableRequest = null;

        DynamoDB dynamoDB = new DynamoDB(dynamoDBClient);

        try {

            ArrayList<AttributeDefinition> attributeDefinitions = new ArrayList<AttributeDefinition>();
            attributeDefinitions.add(new AttributeDefinition().withAttributeName("videoid").withAttributeType("S"));
            attributeDefinitions.add(new AttributeDefinition().withAttributeName("category").withAttributeType("S"));

            ArrayList<KeySchemaElement> keySchema = new ArrayList<KeySchemaElement>();
            keySchema.add(new KeySchemaElement().withAttributeName("videoid").withKeyType(KeyType.HASH));
            keySchema.add(new KeySchemaElement().withAttributeName("category").withKeyType(KeyType.RANGE));

            // Initial provisioned throughput settings for the indexes
            ProvisionedThroughput ptIndex = new ProvisionedThroughput().withReadCapacityUnits(150L)
                    .withWriteCapacityUnits(150L);

            GlobalSecondaryIndex videoCategoryGsi = new GlobalSecondaryIndex().withIndexName("VideoCategoryGsi")
                    .withProvisionedThroughput(ptIndex)
                    .withKeySchema(new KeySchemaElement().withAttributeName("category").withKeyType(KeyType.HASH),
                            new KeySchemaElement().withAttributeName("videoid").withKeyType(KeyType.RANGE))
                    .withProjection(new Projection().withProjectionType(ProjectionType.ALL));

            createTableRequest = new CreateTableRequest().withTableName(tableName).withKeySchema(keySchema)
                    .withAttributeDefinitions(attributeDefinitions)
                    .withProvisionedThroughput(
                            new ProvisionedThroughput().withReadCapacityUnits(100L).withWriteCapacityUnits(100L))
                    .withGlobalSecondaryIndexes(videoCategoryGsi);

            Table table = dynamoDB.createTable(createTableRequest);

            table.waitForActive();

        } catch (ResourceInUseException re) {

            if (re.getErrorMessage().equalsIgnoreCase("Cannot create preexisting table")) {
                LOGGER.info("Table already exists =============>" + tableName);
            } else if (re.getErrorMessage().contains("Table already exists")) {
                LOGGER.info("Table already exists =============>" + tableName);
                LOGGER.info("Message =============>" + re.getErrorCode() + ";" + re.getErrorMessage());
            } else {

                throw new RuntimeException("DynamoDB table cannot be created ...", re);
            }

        } catch (Exception db) {

            throw new RuntimeException("DynamoDB table cannot be created ...", db);

        }

        return true;

    }

Query GSI by category:-

Here is the input is just category and it is querying using GSI. In other words, it is not scanning the entire table as well.

public List<String> findVideosByCategoryUsingGlobalSecondaryIndex(String category) {

        List<String> videoAsJson = new ArrayList<>();

        DynamoDB dynamoDB = new DynamoDB(dynamoDBClient);

        Table table = dynamoDB.getTable("Videos");

        Index index = table.getIndex("VideoCategoryGsi");

        ItemCollection<QueryOutcome> items = null;

        QuerySpec querySpec = new QuerySpec();

        querySpec.withKeyConditionExpression("category = :val1")
                .withValueMap(new ValueMap()
                        .withString(":val1", category));
        items = index.query(querySpec);

        Iterator<Item> pageIterator = items.iterator();


        while (pageIterator.hasNext()) {
            String videoJson = pageIterator.next().toJSON();
            System.out.println("Video json ==================>" + videoJson);
            videoAsJson.add(videoJson);
        }

        return videoAsJson;

    }

Upvotes: 0

Harshal Bulsara
Harshal Bulsara

Reputation: 8264

Is the only way of doing it scanning the whole table?

-NO, you can implement GSI to avoid it 

Is there a way to do to find the items in Category by querying just the range key?

- Yes, If you don't want to scan entire table then you need to create GSI which will have Category as Hash. This GSI will act as a table in itself and you can query on it by passing category values.

If change Category to a GSI can I find the items that way?

-Yes, you can query on GSI with category values

I was reading that if you modified change the attribute "Category" to a global secondary index you can return the items for that GSI. But I have not been able to find how to do that.

-You need to create GSI when you create table, example is given in the link that you have specified once that is done you can query that GSI

References:http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html

Upvotes: 1

Related Questions