Exporting data from CQL collections (deprecated)

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

Note: Hadoop is deprecated for use with DataStax Enterprise. DSE Hadoop and BYOH (Bring Your Own Hadoop) are deprecated. Sqoop is also deprecated and will be removed when Hadoop is removed.

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 |
+-------+--------+--------+--------+