Troubleshooting
Problem
Summary
In this KB article, we would look at how to unload and load the original write time and TTL for data, and its limitations.
Applies to
- All DSE versions and DSBULK versions
Sometimes customer requires to keep the original write time and TTL for the data they need to load into a new table or cluster, in this case, we would need to extract the original write time and TTL for these data and load them with the data into the new table or cluster.
Procedures
Case #1 When there are normal data type and no collections data type
- Create a test table
CREATE KEYSPACE IF NOT EXISTS test_ks WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', 'Cassandra' : 1};
CREATE TABLE IF NOT EXISTS test_ks.test_tb1 ( uid int, name text, value int, PRIMARY KEY (uid) ) WITH compaction = {'class' : 'SizeTieredCompactionStrategy'} AND comment = 'Test Table1';
- Input Data
INSERT INTO test_ks.test_tb1 ( uid, name, value ) VALUES ( 1, 'a', 100 ) USING TTL 86400 ; INSERT INTO test_ks.test_tb1 ( uid, name, value ) VALUES ( 2, 'b', 200 ) USING TTL 86400 ; INSERT INTO test_ks.test_tb1 ( uid, name, value ) VALUES ( 3, 'c', 300 ) USING TTL 86400 ;
- Unload with Original Write Time and TTL Data
dsbulk unload -url /home/automaton/dsbulk/test_ks.test_tb1 -query "SELECT uid, name, value, WRITETIME(name) AS writetime_name, TTL(name) AS ttl_name, WRITETIME(value) AS writetime_value, TTL(value) AS ttl_value FROM test_ks.test_tb1;"
- Create a destination table to load the data into
CREATE TABLE IF NOT EXISTS test_ks.test_tb2 ( uid int, name text, value int, PRIMARY KEY (uid) ) WITH compaction = {'class' : 'SizeTieredCompactionStrategy'} AND comment = 'Test Table2';
- Load data back with Original Write Time and TTL Data, here we need to use batch, this is because we can only insert 1 write time and 1 TTL per insert statement, so we would need to do this for each of the none primary key columns that write time and TTL needs to be preserved, in this case, the columns are `name` and `value`
dsbulk load -url /home/automaton/dsbulk/test_ks.test_tb1 -query "BEGIN BATCH INSERT INTO test_ks.test_tb2(uid, name) VALUES (:uid, :name) USING TIMESTAMP :writetime_name AND TTL :ttl_name; INSERT INTO test_ks.test_tb2(uid, value) VALUES (:uid, :value) USING TIMESTAMP :writetime_value AND TTL :ttl_value; APPLY BATCH;" --batch.mode DISABLED
Please note that when unloading data using the options -timestamp or -ttl to automatically preserve cell timestamp and TTL, the operation fails if the table being unloaded contains collections. Unsupported types are excluded from an automatic timestamp and TTL unload, and a warning is logged explaining that some timestamps and TTLs may be lost.
When loading data, it's possible to use the writetime and ttl functions to map a field’s value to the timestamp or TTL of one or more columns in the table. For example, the following mapping would use field3’s value as the writetime of columns col1 and col2, and field4’s value as the TTL of those columns: field1 = col1, field2 = col2, field3 = writetime(col1,col2), field4 = ttl(col1,col2)
The special tokens \timestamp and \ttl are deprecated (but still honored). If used, a warning message is logged. When you can, replace any \timestamp and \ttl tokens with writetime () and ttl(), respectively.
Cause #2 When there are collection data type
The query on `write time` and `TTL` would not work for collections prior to DSE v6.7.0, you would receive error like below:
select id, writetime(team) from test_ks.test1; InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot use selection function writeTime on collections"and in DSE v6.7.0 and later versions, we will get the correct output:
select id, writetime(team) from test_ks.test1; id | writetime(team) ----+-------------------------------------------------------- 1 | [1580428056181257, 1580428056181257, 1580428056181257] (1 rows)
However, although we are able to export the `write time` and `TTL` out, we are not able to import or insert these data into the collection data type, this is because for each column, we can only insert 1 `write time` and `TTL`, but for the collection data type there are multiple items in each column, so in this case, customer won't be able to load the original `write time` and `TTL` for the collection data types.
Last Reviewed Date: November 30th 2023
Document Location
Worldwide
Historical Number
ka06R000000HdOqQAK
Was this topic helpful?
Document Information
Modified date:
30 January 2026
UID
ibm17258781