Exporting data from CQL collections

The cql-export tool supports the export of data from list, set, and map collection types.

The cql-export tool supports the export of data from list, set, and map collection types.

Exporting a set or list 

You can export a CQL list and set to multiple SQL rows. You map each element in the list to an SQL row, and then use the cql-export command to export the data. In the SQL database, multiple rows store the collection.

The cql-export tool supports exporting list and set data as multiple SQL rows using the following mapping:
CQLCOL:SQLCOL

The following example shows how to map a list of multiple SQL rows.

Suppose you have created and populated a CQL table using the following commands:
cqlsh> CREATE TABLE cql_table (cqlid int PRIMARY KEY, mylist listtext);
cqlsh> INSERT INTO cql_table (cqlid, mylist) VALUES (1, ['value1','value2','value3']);
Using MySQL, you create the following table that corresponds to the CQL table:
mysql> CREATE TABLE sql_table(sqlid INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, id INTEGER, value VARCHAR(20));
The following map along with other options exports the data into MySQL:
--cassandra-column-mapping cqlid:id,mylist:value
Querying MySQL to select the table produces the following output:
+-------+----+--------+
| sqlid | id | value  |
+-------+----+--------+
| 1     | 1  | value1 |
| 2     | 1  | value2 |
| 3     | 1  | value3 |
+-------+----+--------+

Sqoop does not export sqlid from CQL. MySQL auto-generates the sqlid to give the table a unique id.

Exporting a map 

You can export a CQL map collection to a single SQL row. You map each map key to SQL column names. You can only map one collection per Sqoop statement.

You use the following cassandra-column-mapping parameter to export CQL map entries to SQL columns where the key maps to a SQL column name. Where the map key is the same as the SQL column name, you can omit the key from the mapping:
CQLCOL:[SQLCOL1,SQLCOL2,SQLCOL3]
Like the importing mechanism, the mapping mechanism for exporting supports a mix of key name mapping.
CQLCOL:[KEY1:SQLCOL1,SQLCOL2,KEY3:SQLCOL3]

The following example shows how to map a CQL map to an SQL table.

Create and populate a CQL table using the following commands:
cqlsh> CREATE TABLE cql_table (cqlid int PRIMARY KEY, mymap maptext,text); 
cqlsh> INSERT INTO cql_table (cql, mymap) values (1, {'key1':'value1','col2':'value2','key3':'value3'});
Using MySQL, create the following table that corresponds to the CQL table:
mysql> CREATE TABLE sql_table(sqlid INTEGER PRIMARY KEY, col1 VARCHAR(20), col2 VARCHAR(20), col3 VARCHAR(20));
The following map along with other options exports the data to MySQL:
--cassandra-column-mapping cqlid:sqlid,mymap:[key1:col1,col2,key3:col3]
Querying MySQL to select the table produces the following output:
+-------+--------+--------+--------+
| sqlid | col1   | col2   | col3   |
+-------+--------+--------+--------+
| 1     | value1 | value2 | value3 |
+-------+--------+--------+--------+