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.
CQLCOL:SQLCOL
The following example shows how to map a list of multiple SQL rows.
cqlsh> CREATE TABLE cql_table (cqlid int PRIMARY KEY, mylist listtext);
cqlsh> INSERT INTO cql_table (cqlid, mylist) VALUES (1, ['value1','value2','value3']);
mysql> CREATE TABLE sql_table(sqlid INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, id INTEGER, value VARCHAR(20));
--cassandra-column-mapping cqlid:id,mylist:value
+-------+----+--------+
| 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.
CQLCOL:[SQLCOL1,SQLCOL2,SQLCOL3]
CQLCOL:[KEY1:SQLCOL1,SQLCOL2,KEY3:SQLCOL3]
The following example shows how to map a CQL map to an SQL table.
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'});
mysql> CREATE TABLE sql_table(sqlid INTEGER PRIMARY KEY, col1 VARCHAR(20), col2 VARCHAR(20), col3 VARCHAR(20));
--cassandra-column-mapping cqlid:sqlid,mymap:[key1:col1,col2,key3:col3]
+-------+--------+--------+--------+
| sqlid | col1 | col2 | col3 |
+-------+--------+--------+--------+
| 1 | value1 | value2 | value3 |
+-------+--------+--------+--------+