Hi Thowell,

The "engine" you are seeing is regarding a small testing I did at my end , you can consider it as one of the dimension of your table similar to "station".

My testing values are just for reference and sample:~$aws timestream-write describe-table --database-name demotest1 --table-name demoptesttable1

*Table Name: demoptesttable1*
*Database Name: demotest1*
*Keys: device_id,engine*
*MemoryStoreRetentionPeriodInHours: 12,*
*MagneticStoreRetentionPeriodInDays: 3650*

Since I had already created a table with the same nomenclature as yours, therefore for another layer of testing , I mentioned different names for tables, database and keys.

Further, please let me quote from our public document officially written: [+]https://docs.aws.amazon.com/timestream/latest/developerguide/customer-defined-partition-keys-schema-validation.html#customer-defined-partition-keys-schema-validation-using

Specify custom composite partition keys: When creating the table, specify a custom partition key. This key determines the attribute that will be used to partition the table data. You can choose between dimension keys and measure keys for partitioning. A dimension key partitions data based on a dimension name, while a measure key partitions data based on the measure name.

Ingest data: When ingesting data into the Timestream for LiveAnalytics table, the schema validation process will check the records against the defined schema with custom composite partition keys. If the records do not adhere to the schema, Timestream for LiveAnalytics will return a validation error. 

However, When we create a table , its clearly mentioned on console in this manner that the list columns will have a heirarchy.

Composite partition key
A non-empty list of partition keys defining the attributes used to partition the table data. The order of the list determines the partition hierarchy. The name and type of each partition key as well as the partition key order cannot be changed after the table is created. However, the enforcement level of each partition key can be changed.

Composite partition key Info
A list of partition keys defining the attributes used to partition the table data. The order of the list determines the partition hierarchy.

I do completely understand that the documentation is not very clear and hence, I have reached out regarding this to our timestream internal team to take necessary actions regarding this.

If the workaround1 in previous response is not very helpful, then you can have a look at workaround2 as after strenuous discussion with internal team, there are only 2 workarounds available as of now.

Hope this provide you some more clarification about your outstanding queries.

Have a great one! cheers!

Hello Thowell,

Hope you are keeping well!

I studied your very detailed written and helpful case notes and observed that your requirement is :

add a table to the database: django-timeseries-datastore Name the table: composite-compressors with memory retention of 24hrs magnetic of 2 years (830 days)
a composite key over two DIMENSIONs : station, device_id

You have already tried with console, CLI and SDK and you have shared the respective codes and commands used and the error recieved as well.

Your command with AWS CLI is not working and with SDK you are facing the below mentioned error:

Error you are facing : botocore.errorfactory.ValidationException: An error occurred (ValidationException) when calling the CreateTable operation: You have specified too many partition keys to be used in your composite partition key. Only 1 partition keys can be specified.

I can see you have posted the query on our public forum re:post as well here : [+]https://repost.aws/questions/QUkJRdAnEiS8qEMmUFarjF2w/multiple-customer-defined-partition-keys-in-timestream

You wanted to know if this is possible to achieve this use case.

Findings and Explanation

To begin with, I went ahead and tested your use case with the AWS CLI version : "aws-cli/2.15.59 Python/3.11.8 Darwin/23.4.0 exe/x86_64" and below are my results with the command shared by yourself:

Testing 1

$ aws timestream-write create-table --database-name django-timeseries-datastore --table-name composite-compressors2 --retention-properties '{"MemoryStoreRetentionPeriodInHours": 24, "MagneticStoreRetentionPeriodInDays": 830}' --schema '{
  "CompositePartitionKey": [
    {
      "Type": "DIMENSION",
      "Name": "station",
      "EnforcementInRecord": "REQUIRED"
    },
    {
      "Type": "DIMENSION",
      "Name": "device_id",
      "EnforcementInRecord": "REQUIRED"
    }
  ]
}'

Error: An error occurred (ValidationException) when calling the CreateTable operation: You have specified too many partition keys to be used in your composite partition key. Only 1 partition keys can be specified.

Conclusion 1>Therefore, I can indeed reproduce the same error at my end using AWS CLI versions 2.12.x+ as well as you were facing with AWS SDK.

Testing 2

Another test I did with the command in my test environment with a same use case as above:

~$ aws timestream-write create-table --database-name test --table-name testtable1 --retention-properties '{"MemoryStoreRetentionPeriodInHours": 24, "MagneticStoreRetentionPeriodInDays": 830}' --schema '{"CompositePartitionKey":[{"Type":"DIMENSION","Name":"station","EnforcementInRecord":"REQUIRED"},{"Type":"DIMENSION","Name":"device_id","EnforcementInRecord":"REQUIRED"}]}' --region us-east-2

Same Exception: An error occurred (ValidationException) when calling the CreateTable operation: You have specified too many partition keys to be used in your composite partition key. Only 1 partition keys can be specified.

Conclusion 2

IMP>Therefore, I will first of all suggest you to utilize AWS CLI version 2.12.x+ as before upgrading the CLI , I was utilizing 2.11.x version and even I was getting the same error with the above mentioned command that it is not valid, adding it for your reference below:

usage: aws [options] <command> <subcommand> [<subcommand> ...] [parameters]
To see help text, you can run:

  aws help
  aws <command> help
  aws <command> <subcommand> help

Unknown options: --schema, {CompositePartitionKey=[{Type=DIMENSION,Name=station,EnforcementInRecord=REQUIRED},{Type=DIMENSION,Name=devide_id,EnforcementInRecord=REQUIRED}]}

IMP>When tested on 2.12.3 version, then also I can see that schema option is supported properly.

IMP>You can use the below command to find out AWS CLI version:

~$aws --version
aws-cli/2.12.3 Python/3.11.4 Darwin/22.5.0 exe/x86_64 prompt/off

IMP>Therefore, Kindly upgrade your CLI so that these options are supported from CLI end.

IMP>The way to upgrade your CLI version is provided here : [+]https://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.html

Testing 3

Additionally, I performed another testing I did where I am able to provide a list of comma separated values in the textbox on the console. Please check the attached image.

I am able to find the below information when trying to find the output of describe table where I added those values.

$ aws timestream-write describe-table --database-name demotest1 --table-name demoptesttable1
{
  "Table": {
    "Arn": "arn:aws:timestream:us-east-2:306316258086:database/demotest1/table/demoptesttable1",
    "TableName": "demoptesttable1",
    "DatabaseName": "demotest1",
    "TableStatus": "ACTIVE",
    "RetentionProperties": {
      "MemoryStoreRetentionPeriodInHours": 12,
      "MagneticStoreRetentionPeriodInDays": 3650
    },
    "CreationTime": "2024-05-29T16:28:27.926000+05:30",
    "LastUpdatedTime": "2024-05-29T16:28:27.926000+05:30",
    "MagneticStoreWriteProperties": {
      "EnableMagneticStoreWrites": true
    },
    "Schema": {
      "CompositePartitionKey": [
        {
          "Type": "DIMENSION",
          "Name": "device_id,engine",====================> see here
          "EnforcementInRecord": "REQUIRED"
        }
      ]
    }
  }
}

Conclusion 3

However , I observed that it uses the whole string “device_id,engine” as a dimension, instead of two strings “device_id” and “engine”, therefore this also will not be helpful regarding your use case.

Further, regarding your use case, I went ahead and performed thorough research on it and then checked with the internal team to understand if its even possible and got the below response:

Currently, there is unfortunately no provisional support available to add multiple partition keys to the same table unless both of the keys have a heirarchical relationship as we currently have the limitation of having only key specified in the partition-key list.

In other words , CompositePartitionKey, when launched with more than 1 key, is going to work for cases where there is a hierarchy or ordering relationship between the two attributes.

I can see internally that there is indeed some research being done in order to increase it, however, its still being considered as a feature request and hence, currently we can not provide the ETA on when this will be released. As you must already be aware, any new enhancement or feature release requires rigorous testing and compatibility checks before rolling it out globally. Once this feature is supported, the same information will be updated in our public documents. [1][2][3]

However , I do completely understand that the documentation about it can lead to confusion , therefore, I have provided the context and feedback about it to our concerned internal product team and they are looking into making the language of documentation about it more clear.

I would like to advise you to use the below mentioned way in order to achieve your use case if this looks suitable :

Workaround 1:

As I understand that your use case will be designed as multiple stations having multiple devices with M:N relationship.

Let's say station column value is stationA, under which there are three devices: device1, device2, device3. Can you try ingesting data into Timestream into another dimesion column called, say, "stationAndDeviceId" with values as stationAdevice1, stationAdevice2 , stationAdevice3 and add that "stationAndDeviceId" as your partition-key list.

This will essentially provide the similar use case as you would have done with having a composite key.

Workaround 2:

The other workaround/mitigation would be depending upon which column amongst station or deviceId is having higher cardinality and is always specified in the query, you can just make one of these columns as Partition-Key, and still greatly benefit from the partition pruning.

Having said that, going ahead, I will highly advice you to kindly take a look into the provided workarounds and analyse if these looks suitable.

Going ahead, I sincerely hope that the above information helps you to address your concerns and answer your queries.

Have a great one! cheers!

References:

[1] AWS Forums: https://forums.aws.amazon.com/forum.jspa?forumID=60 [2] RDS Release Notes - https://aws.amazon.com/releasenotes/Amazon-RDS [3] RDS What's New - https://aws.amazon.com/new/ [4]https://repost.aws/questions/QUkJRdAnEiS8qEMmUFarjF2w/multiple-customer-defined-partition-keys-in-timestream [5]https://repost.aws/questions/QUW4HiW9gMS1Wy63j95ufc-Q [6]https://docs.aws.amazon.com/timestream/latest/developerguide/customer-defined-partition-keys-checking-configuration.html [7]https://docs.aws.amazon.com/timestream/latest/developerguide/API_CreateTable.html [8]https://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.html [9]https://docs.aws.amazon.com/cli/latest/reference/timestream-write/create-table.html