Using Sqoop, you can export data of different data types from CQL to MySQL.
Using Sqoop, you can export data of different data types from CQL to MySQL. You can
import SQL into CQL collections, and exporting CQL collections to multiple rows in
SQL. This example creates a CQL table of columns of different data types, inserts
values into the table, and exports the data to SQL.
Procedure
-
Create a keyspace using the default datacenter name Analytics, and use the
keyspace.
cqlsh> CREATE KEYSPACE tosql WITH REPLICATION =
{'class':'NetworkTopologyStrategy', 'Analytics':1};
cqlsh> USE tosql;
-
Create a table in CQL, and then, insert some data.
cqlsh:tosql> CREATE TABLE cql_table (
id int PRIMARY KEY,
a timestamp,
b float,
c boolean,
d blob,
e inet,
f uuid);
cqlsh:tosql> INSERT INTO cql_table ( id, a, b, c, d, e, f ) VALUES ( 123, '1974-07-17 22:18:32', 3.14159265, true, 0x1afb, '127.0.0.1', 69d5c4fd-a7b7-4269-9cb5-c6f7d5fc076e );
cqlsh:tosql> INSERT INTO cql_table ( id, b ) VALUES ( 789, 11.001001000) ;
-
Create a database and table in MySQL that corresponds to the CQL table. Use
compatible data types, which are listed in Export data types.
mysql> CREATE DATABASE fromcql;
mysql> USE fromcql;
mysql> CREATE TABLE sql_table (
id INTEGER PRIMARY KEY,
a TIMESTAMP,
b VARCHAR(25),
c BOOLEAN,
d BLOB,
e VARCHAR(15),
f VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
Export the CQL data to MySQL. This example shows the export command entered on
the command line instead of using an options file.
$ dse sqoop cql-export --connect jdbc:mysql://127.0.0.1/fromcql --username root --password root --table sql_table --cassandra-host 127.0.0.1 --cassandra-keyspace tosql --cassandra-table cql_table
Alternatively, you can adapt the export.options file to your environment in
the manner shown earlier for modifying the import.options file, and then use
this
command:
$ bin/dse sqoop --options-file path_to_export.options
The MapReduce job runs and the end of the output looks like this:
. . .
14/05/29 08:08:33 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 52.2312 seconds (0 bytes/sec)
14/05/29 08:08:33 INFO mapreduce.ExportJobBase: Exported 2 records.
-
Check that the data was exported into the MySQL table.
mysql> SELECT * FROM sql_table;
+---+-------------+----------+----+----+----------+------------+
|id |a | b | c |d | e |f |
+---+-------------+----------+----+----+----------+------------+
|123|1974-07-17...| 3.1415927| 1|? |/127.0.0.1|69d5c4fd... |
|789|2014-05-29...| 11.001001|NULL|NULL|NULL |NULL |
+---+-------------+----------+----+----+----------+------------+
3 rows in set (0.00 sec)