Loading tables that contain static and non-static columns

Steps to load tables that contain static and non-static columns.

This topic describes the steps required to load database tables that contain static and non-static columns.

Outline of the steps

  1. Create a dataset containing only the partition key columns and the static columns.
  2. Load static data with DataStax Bulk Loader using the -query parameter and the format:
    -query "INSERT INTO t1 (pk1, pk2, ..., static1, static2, ...)"
  3. Create another dataset containing the entire primary key (partition keys and clustering columns), and regular columns, without any static data.
  4. Load non-static data with DataStax Bulk Loader using the -query parameter and the format:
    -query "INSERT INTO t1 (pk1, pk2, .. cc1, cc2 ..., nonstatic1, nonstatic2, ...)"

Example

In CQLSH, create a keyspace and a table:
CREATE KEYSPACE demo WITH replication = 
       {'class': 'SimpleStrategy', 'replication_factor': '1'};
CREATE TABLE demo.mytable (
       pk1 text, pk2 text, 
       cc1 text, cc2 text, 
       static1 text static, static2 text static, 
       nonstatic1 text, nonstatic2 text,
       PRIMARY KEY ((pk1,pk2), cc1, cc2));

Create a file named static-data.csv to contain the static data:
pk1,pk2,static1,static2
a,b,y,z
Use DataStax Bulk Loader to load the static data from the CSV file:
dsbulk load -k demo -url static-data.csv \
  -query "INSERT INTO demo.mytable (pk1,pk2,static1,static2) \ 
  VALUES (:pk1,:pk2,:static1,:static2)"
Tip: By default, DataStax Bulk Loader considers the first row in the file as a CSV header.
Create a second file named non-static-data.csv to contain the non-static data:
pk1,pk2,cc1,cc2,nonstatic1,nonstatic2
a,b,c,d,e,f
Use DataStax Bulk Loader to load the non-static data from the second CSV file:
dsbulk load -k demo -url non-static-data.csv \
  -query "INSERT INTO demo.mytable (pk1,pk2,cc1,cc2,nonstatic1,nonstatic2)  \
          VALUES (:pk1,:pk2,:cc1,:cc2,:nonstatic1,:nonstatic2)"
With the data loading operations complete, you can now query the table and retrieve all the data:
SELECT * from demo.mytable;
 pk1 | pk2 | cc1 | cc2 | static1 | static2 | nonstatic1 | nonstatic2
-----+-----+-----+-----+---------+---------+------------+------------
   a |   b |   c |   d |       y |       z |          e |          f