DynamoDB - Local Secondary Indexes

user-pcr user-pcr
Mar 16, 2022 - 17:52
0 9
DynamoDB - Local Secondary Indexes

Some applications only query with the primary key, but in some situations an alternate sort key is used. Allow your application to choose by creating one or more local secondary indexes.

Complex data access requirements such as merging millions of items make it necessary to perform more efficient queries/scans. Local secondary indexes provide an alternate sort key for the partition key value. They also contain copies of all or some of the table's attributes. They organize the data by the table's partition key, but use a different sort key.

Using a local secondary index eliminates the need to scan the entire table and allows you to perform a simple and fast query using a sort key.

All local secondary indexes must satisfy certain conditions −

  • Identical partition key and partition key of the source table.
  • The sort key of only one scalar attribute.
  • A projection of the table's original sort key, acting as a non-key attribute.

All local secondary indexes automatically contain the partition and sort keys from the parent tables. In queries, this means efficient collection of projected attributes as well as searching for non-projected attributes.

The storage limit for a local secondary index remains 10 GB per partition key value, which includes all table entries and index entries that share a partition key value.

Attribute projection

Some operations require redundant read/retrieval due to complexity. These operations can consume significant bandwidth. Projection allows you to avoid costly fetching and perform complex queries by isolating these attributes. Remember that projections are made up of attributes copied to a secondary index.

When you create a secondary index, you specify the projected attributes. Recall the three options provided by DynamoDB: KEYS_ONLY, INCLUDE, and ALL .

When choosing certain attributes in a projection, consider the associated cost trade-offs −

  • If you project only a small set of required attributes, you will significantly reduce storage costs.

  • If you design for frequently used non-key attributes, you offset the cost of scanning with the cost of storage.

  • If you project most or all of the non-key attributes, this maximizes flexibility and reduces throughput (no lookups); However, the cost of storage increases.

  • If you design KEYS_ONLY for frequent writes/updates and infrequent queries, this minimizes the size but supports query preparation.

If you project only a small set of required attributes, you will significantly reduce storage costs.

If you design for frequently used non-key attributes, you offset the cost of scanning with the cost of storage.

If you project most or all of the non-key attributes, this maximizes flexibility and reduces throughput (no lookups); However, the cost of storage increases.

If you design KEYS_ONLY for frequent writes/updates and infrequent queries, this minimizes the size but supports query preparation.

Create a local secondary index

Use the LocalSecondaryIndex parameter on CreateTable to create one or more local secondary indexes. You must specify one non-key attribute for the sort key. When you create a table, you create local secondary indexes. When you delete, you delete those indexes.

Tables with a local secondary index must meet the 10 GB size limit per partition key value, but can store any number of items.

Local Secondary Query and Scan Index

A query operation on local secondary indexes returns all elements with a matching partition key value when multiple elements in an index share sort key values. Matching elements are not returned in a particular order. Queries for local secondary indexes use either possible or strong consistency, and strongly consistent reads provide the latest values.

The scan operation returns all local secondary index data. When scanning, you must specify the table and index name and allow the use of a filter expression to discard data.

Subject Writing

When you create a local secondary index, you specify the sort key attribute and its data type. When you write an element, its type must match the data type of the keymap if the element defines an index key attribute.

DynamoDB does not impose any one-to-one relationship requirements on table items and local secondary index items. Tables with multiple local secondary indexes have higher write costs than tables with fewer.

Throughput Issues in Local Secondary Indexes

The power consumption of a query read depends on the nature of the data access. Queries use either possible or strong consistency, with strongly consistent reads using one unit compared to half the units of eventually consistent reads.

Result limits include a maximum size of 1 MB. The result sizes are obtained from the sum of the corresponding index element size, rounded up to the nearest 4 KB, and the corresponding table element size, also rounded to the nearest 4 KB.

The write power consumption remains within the allocated units. Calculate the total cost secured by finding the sum of the units consumed in the table entry and the units consumed in the index update.

You can also consider the key factors that affect the cost, some of which can be −

  • When you write an element that defines an indexed attribute, or you update an element to define an undefined indexed attribute, one write operation occurs.

  • When a table update changes the value of an indexed key attribute, there are two entries to delete and then one to add an element.

  • When a write causes an indexed attribute to be removed, one write occurs to remove the projection of the old element.

  • If the element does not exist in the index before or after the update, no entry is made.

When you write an element that defines an indexed attribute, or you update an element to define an undefined indexed attribute, one write operation occurs.

When a table update changes the value of an indexed key attribute, there are two entries to delete and then one to add an element.

When a write causes an indexed attribute to be removed, one write occurs to remove the projection of the old element.

If the element does not exist in the index before or after the update, no entry is made.

Storing a local secondary index

When writing a table element, DynamoDB automatically copies the correct set of attributes to the required local secondary indexes. This charges your account. The space used is the sum of the byte size of the table's primary key, the byte size of the index key attribute, any byte of the current size of the projected attribute, and an overhead of 100 bytes for each index element.

The storage score is obtained by estimating the average index element size and multiplying by the table element count.

Using Java to work with local secondary indexes

Create a local secondary index by first creating an instance of the DynamoDB class. Then create an instance of the CreateTableRequest class with the required request information. Finally, use the createTable method.

example

DynamoDB dynamoDB = new DynamoDB ( new AmazonDynamoDBClient ( new ProfileCredentialsProvider ())); String tableName = "Tools" ; CreateTableRequest createTableRequest = new CreateTableRequest (). withTableName ( tableName );    
    
   
  
   
   
//Provisioned Throughput 
createTableRequest . setProvisionedThroughput ( new ProvisionedThroughput () . withReadCapacityUnits (( long ) 5 ) . withWriteCapacityUnits (( long ) 5 ));
    
   
    
   
//Attributes ArrayList < AttributeDefinition > attributeDefinitions = new ArrayList < AttributeDefinition >(); 
   attributeDefinitions . add ( new AttributeDefinition () . withAttributeName ( "Make" ) . withAttributeType ( "S" ));
 
     
   
   
   
attributeDefinitions . add ( new AttributeDefinition () . withAttributeName ( "Model" ) . withAttributeType ( "S" )); 
   
   
   
attributeDefinitions . add ( new AttributeDefinition () . withAttributeName ( "Line" ) . withAttributeType ( "S" )); 
   
   
   
createTableRequest . setAttributeDefinitions ( attributeDefinitions );

//Key Schema ArrayList < KeySchemaElement > tableKeySchema = new ArrayList < KeySchemaElement >();
  
   
   
tableKeySchema . add ( new KeySchemaElement () . withAttributeName ( "Make" ) . withKeyType ( KeyType . HASH )); //partition key 
   
                       
   
tableKeySchema . add ( new KeySchemaElement () . withAttributeName ( "Model" ) . withKeyType ( KeyType . RANGE )); //Sort key 
   
                      
   
createTableRequest . setKeySchema ( tableKeySchema ); ArrayList < KeySchemaElement > indexKeySchema = new ArrayList < KeySchemaElement >();
  
   
   
indexKeySchema . add ( new KeySchemaElement () . withAttributeName ( "Make" ) . withKeyType ( KeyType . HASH )); //partition key 
   
                      
   
indexKeySchema . add ( new KeySchemaElement () . withAttributeName ( "Line" ) . withKeyType ( KeyType . RANGE )); //Sort key 
   
                      
   
Projection projection = new Projection () . withProjectionType ( ProjectionType . INCLUDE );  
   

ArrayList < String > nonKeyAttributes = new ArrayList < String >();  
nonKeyAttributes . add ( "Type" );  
nonKeyAttributes . add ( "Year" );  
projection . setNonKeyAttributes ( nonKeyAttributes );    

LocalSecondaryIndex localSecondaryIndex = new LocalSecondaryIndex () . withIndexName ( "ModelIndex" ) . withKeySchema ( indexKeySchema ) . withProjection ( projection );   
   
   
     

ArrayList < LocalSecondaryIndex > localSecondaryIndexes = new ArrayList < LocalSecondaryIndex >();  
    

localSecondaryIndexes . add ( localSecondaryIndex );  
createTableRequest . setLocalSecondaryIndexes ( localSecondaryIndexes ); Table table = dynamoDB . createTable ( createTableRequest ); System . out . println ( table.getDescription ( ) );

Get information about a local secondary index using the description method. Just create an instance of the DynamoDB class, instantiate the Table class, and pass the table to the description method.

example

DynamoDB dynamoDB = new DynamoDB ( new AmazonDynamoDBClient ( new ProfileCredentialsProvider ()));    
    
   
String tableName = "Tools" ; Table table = dynamoDB . getTable ( tableName ); tableDescription tableDescription = table . describe (); 



List < LocalSecondaryIndexDescription > localSecondaryIndexes =  
   tableDescription . getLocalSecondaryIndexes ();
   
Iterator < LocalSecondaryIndexDescription > lsiIter =  
   localSecondaryIndexes . iterator ();
   
while ( lsiIter . hasNext ()) { LocalSecondaryIndexDescription lsiDescription = lsiIter . next (); System . out . println ( "Index info " + lsiDescription . getIndexName () + ":" ); Iterator < KeySchemaElement > kseIter = lsiDescription . getKeySchema (). iterator ();    
    
       
    
   
   while ( kseIter . hasNext ()) { KeySchemaElement kse = kseIter . next (); System . out . printf ( "\t%s: %s\n" , kse . getAttributeName (), kse . getKeyType ()); }   
       
       
   
   
   Projection projection = lsiDescription . getProjection (); System . out . println ( "\tProjection type: " + projection . getProjectionType ()); 
     
   
   if ( projection . getProjectionType (). toString (). equals ( "INCLUDE" )) { System . out . println ( "\t\tNon-key projected attributes: " +  
         projection . getNonKeyAttributes ()); } }

Execute the query following the same steps as querying the table. Just create an instance of the DynamoDB class, an instance of the Table class, an instance of the Index class, a query object, and use the query method.

example

DynamoDB dynamoDB = new DynamoDB ( new AmazonDynamoDBClient ( new ProfileCredentialsProvider ()));    
    
   
String tableName = "Tools" ; Table table = dynamoDB . getTable ( tableName ); Index index = table . getIndex ( "LineIndex" ); QuerySpec spec = new QuerySpec () . withKeyConditionExpression ( "Make = :v_make and Line = :v_line" ) . withValueMap ( new ValueMap () . withString ( ":v_make" ,   
 
  
   
    
     
    "Depault" ) . withString ( ":v_line" , "SuperSawz" )); 
    
      
ItemCollection < QueryOutcome > items = index . query ( spec ); Iterator < Item > itemsIter = items . iterator ();


while ( itemsIter . hasNext ()) { Item item = itemsIter . next (); System . out . println ( item.toJSONPretty ( ) ); }

You can also view the following example.

Note. The following example can use a previously created data source. Before attempting to execute, acquire the supporting libraries and create the necessary data sources (tables with the required characteristics or other referenced sources).

The following example also uses the Eclipse IDE, an AWS credential file, and the AWS Toolkit in an Eclipse AWS Java project.