CODEWITHSUNDEEP
amazon-dynamodb
naz
naz

Reputation: 2072

Ordering the results by the range key in DynamoDB does not work

I have a table definition like the following:

    ATTRIBUTE_NAME, ATTRIBUTE_TYPE = 'AttributeName', 'AttributeType'
    my_table = dynamodb.create_table(
    TableName='my_table',
    KeySchema=[
        {
            ATTRIBUTE_NAME: 'order_id',
            'KeyType': 'HASH'
        },
        {
            ATTRIBUTE_NAME: 'time',
            'KeyType': 'RANGE'
        }
    ],
    AttributeDefinitions=[
        {
            ATTRIBUTE_NAME: 'order_id',
            ATTRIBUTE_TYPE: 'S'
        },
        {
            ATTRIBUTE_NAME: 'time',
            ATTRIBUTE_TYPE: 'S'
        },
        {
            ATTRIBUTE_NAME: 'market_product',
            ATTRIBUTE_TYPE: 'S'
        }
    ],
    GlobalSecondaryIndexes=[
        {
            'IndexName': 'market_product_index',
            'KeySchema': [
                {
                    'AttributeName': 'market_product',
                    'KeyType': 'HASH'
                },
            ],
            'Projection': {
                'ProjectionType': 'KEYS_ONLY'
            },
            'ProvisionedThroughput': {
                'ReadCapacityUnits': 5,
                'WriteCapacityUnits': 5
            }
        }
    ],
    ProvisionedThroughput={
        'ReadCapacityUnits': 5,
        'WriteCapacityUnits': 5
    }
    )

Here I create dummy data for this table:

from uuid import uuid4 as uuid

my_table_dummy = [
    {
        'order_id': str(uuid()),
        'time': '2019-02-13 15:07:55.575960',
        'market_product': 'bitmex:BTC-USD',
        'side': 'buy',
        'size': '10.2',
        'weighted_price': '21.3'
    },
    {
        'order_id': str(uuid()),
        'time': '2019-02-13 15:06:55.575960',
        'market_product': 'bitmex:BTC-USD',
        'side': 'buy',
        'size': '10.2',
        'weighted_price': '21.3'
    },
    {
        'order_id': str(uuid()),
        'time': '2019-02-12 15:06:55.575960',
        'market_product': 'bitmex:BTC-USD',
        'side': 'buy',
        'size': '10.2',
        'weighted_price': '21.3'
    },
    {
        'order_id': str(uuid()),
        'time': '2019-02-12 15:06:55.575961',
        'market_product': 'bitmex:BTC-USD',
        'side': 'buy',
        'size': '10.2',
        'weighted_price': '21.3'
    },
    {
        'order_id': str(uuid()),
        'time': '2019-02-11 15:06:55.575960',
        'market_product': 'bitmex:BTC-USD',
        'side': 'buy',
        'size': '10.2',
        'weighted_price': '21.3'
    }
]

for dummy_sample in my_table_dummy:
    my_table.put_item(Item=dummy_sample)

I read that when one queries the above and uses ScanForwardIndex flag, then, the results are sorted by the range key, which in this case is time. However, I am not getting the intended behaviour of obtaining the results of the query in descending/ascending order by time attribute:

response = my_table.query(
    IndexName='market_product_index',
    KeyConditionExpression=Key('market_product').eq('bitmex:BTC-USD'),
    ScanIndexForward=True
)

and response looks like this, i.e. not ordered by time at all:

{'Items': [{'market_product': 'bitmex:BTC-USD',
   'order_id': '0d9fd701-5a7e-4348-bb01-631388c2c246',
   'time': '2019-02-12 15:06:55.575960'},
  {'market_product': 'bitmex:BTC-USD',
   'order_id': '8cc1f2a2-0bc5-4169-aca5-cf37abbb5bc4',
   'time': '2019-02-11 15:06:55.575960'},
  {'market_product': 'bitmex:BTC-USD',
   'order_id': 'd23cfa2c-9ae6-403b-ae57-1e1a3796e116',
   'time': '2019-02-13 15:06:55.575960'},
  {'market_product': 'bitmex:BTC-USD',
   'order_id': '29095ee3-588f-4fb8-98a0-ce34adf028ea',
   'time': '2019-02-12 15:06:55.575961'},
  {'market_product': 'bitmex:BTC-USD',
   'order_id': '6cacd8fa-a2d0-4f2d-8041-a30fa5252c3b',
   'time': '2019-02-13 15:07:55.575960'}],
 'Count': 5,
 'ScannedCount': 5,
 'ResponseMetadata': {'RequestId': 'bbc8bc0e-218a-4669-ba52-4ac07cc7bb60',
  'HTTPStatusCode': 200,
  'HTTPHeaders': {'content-type': 'application/x-amz-json-1.0',
   'x-amz-crc32': '365619475',
   'x-amzn-requestid': 'bbc8bc0e-218a-4669-ba52-4ac07cc7bb60',
   'content-length': '738',
   'server': 'Jetty(8.1.12.v20130726)'},
  'RetryAttempts': 0}}

Upvotes: 1

Views: 51

Answers (1)

Matthew Pope
Matthew Pope

Reputation: 7679

They are returned in no particular order because your index doesn’t have a sort key. Indexes do not automatically inherit any structure from the base table.

By choosing keys only, you are telling DynamoDB that the primary key of the table should be projected to the GSI, but DynamoDB will not assume that you want your GSI to be sorted by the same attribute.

You can fix this by deleting and recreating your GSI with time stamp as the GSI sort key.

Upvotes: 1

Related Questions