Loading 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:

Create a dataset containing only the partition key columns and the static columns.
Load static data with DataStax Bulk Loader using the -query parameter and the format:
```
-query "INSERT INTO t1 (pk1, pk2, ..., static1, static2, ...)"
```
Create another dataset containing the entire primary key (partition keys and clustering columns), and regular columns, without any static data.
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)"

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

Loading tables that contain static and non-static columns

Outline of the steps:

Example

Was this helpful?

Give Feedback