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 list<text>); 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:
--cql-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 cql-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 map<text,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:
--cql-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 | +-------+--------+--------+--------+