如何在AWS Timestream插入数据时添加唯一ID？

Hey there! Let's get your unique ID set up for your Timestream transactions table, following AWS's recommended approach to keep queries efficient. As the docs mention, storing the unique ID as a measure value (instead of a dimension) will cut down on query latency significantly—here's exactly how to modify your code:

Key Changes Explained #

• Generate a Unique ID: Use uniqid() (simple and widely supported) or uuid_create() (for standard UUIDs, requires the PHP uuid extension) to create a unique identifier per record.
• Switch to Multi-Measure Mode: Since we're adding a second measure (the unique ID alongside your transactions_cnt), we'll set MeasureValueType to MULTI and define all measure fields in a MeasureValues array.
• Clean Up Common Attributes: The single-measure MeasureName and MeasureValue fields are no longer needed in multi-measure mode, so we'll remove them from CommonAttributes.

Modified Full Code #

<?php
$date = new DateTime();
$time = strval($date->getTimestamp() * 1000);
$dimensions = [];
$i = 0;

while ($i < 1) {
    // Generate unique transaction ID - use uniqid() for simplicity, or uuid_create() for standard UUIDs
    $transaction_id = uniqid('txn_', true);
    
    $expected_delivery = '2022-04-30 18:00:00';
    $created_date = date("Y-m-d H:i:s");
    $completed_date = 'n/a';
    $completed = '0';
    $created_by = 'xxx xxxx';
    $sender_user_id = '1';
    $receiver_user_id = '2';
    $sender_company_id = '10';
    $receiver_company_id = '11';
    $remark = 'TEST REMARK TEST HERE';
    $receiver_address = 'xxxxxxxxxxxx';

    $dimensions[] = [
        'Dimensions' => [
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'expected_delivery',
                'Value' => $expected_delivery,
            ],
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'created_date',
                'Value' => $created_date,
            ],
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'completed_date',
                'Value' => $completed_date,
            ],
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'completed',
                'Value' => $completed,
            ],
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'created_by',
                'Value' => $created_by,
            ],
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'sender_user_id',
                'Value' => $sender_user_id,
            ],
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'receiver_user_id',
                'Value' => $receiver_user_id,
            ],
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'sender_company_id',
                'Value' => $sender_company_id,
            ],
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'receiver_company_id',
                'Value' => $receiver_company_id,
            ],
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'remark',
                'Value' => $remark,
            ],
            [
                'DimensionValueType' => 'VARCHAR',
                'Name' => 'receiver_address',
                'Value' => $receiver_address,
            ],
        ],
        // Multi-measure setup: include both unique ID and transaction count
        'MeasureValues' => [
            [
                'Name' => 'transaction_id',
                'Value' => $transaction_id,
                'Type' => 'VARCHAR'
            ],
            [
                'Name' => 'transactions_cnt',
                'Value' => '1', // Adjust this to your actual count value (e.g., number of transactions in this record)
                'Type' => 'INTEGER'
            ]
        ],
        'MeasureValueType' => 'MULTI'
    ];
    $i++;
}

$query = [
    'CommonAttributes' => [
        'Time' => $time,
        'TimeUnit' => 'MILLISECONDS',
        'Version' => 1,
    ],
    'DatabaseName' => 'packaging_app',
    'Records' => $dimensions,
    'TableName' => 'transactions',
];
?>

Quick Notes #

• I updated transactions_cnt to use an INTEGER type with a value of 1—this makes more sense for a count metric, but feel free to adjust the value to match your actual business logic.
• If you want to use standard UUIDs instead of uniqid(), install the PHP uuid extension (pecl install uuid) and replace the ID line with:

  $transaction_id = uuid_create(UUID_TYPE_RANDOM);
  

• With multi-measure mode, your unique ID will be stored in the same row as your other time-series data, making queries faster and more efficient just like AWS recommends.

内容的提问来源于stack exchange，提问作者showtime