Exporting CQL data to SQL

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

  1. 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;
  2. 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) ;
    Observe the range limitation of MySQL timestamps.
  3. 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;
  4. 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.
  5. 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)