Exporting selected CQL data to SQL

Use export options to select columns for export and limit the page size of the export. You can conditionally filter the data to select for export.

You can use export options to select certain columns for export and limit the page size of the export. You can also conditionally filter the data to select for export using the --cassandra-where-clause option clause. You enclose the CQL WHERE clause in double quotation marks.

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. Use the keyspace created in the previous example.
    cqlsh> USE tosql;
  2. Create a table in CQL, and then, insert some data.
    cqlsh:tosql> CREATE TABLE ruling_stewards (
             steward_name text,
             king text,
             reign_start int,
             event text,
             PRIMARY KEY (steward_name, king, reign_start)
           ); 
    
    cqlsh:tosql> INSERT INTO ruling_stewards (steward_name, king, reign_start, event) VALUES ('Hador', 'none', 2278, 'Last long-lived Duedian'); 
    
    cqlsh:tosql> INSERT INTO ruling_stewards (steward_name, king, reign_start, event) VALUES ('Denethor', 'Brego', 2435, 'Watchful Peace broken');
    
    cqlsh:tosql> INSERT INTO ruling_stewards (steward_name, king, reign_start, event) VALUES ('Boromir', 'Brego', 2477, 'Attacks continue');
    
    cqlsh:tosql> INSERT INTO ruling_stewards (steward_name, king, reign_start, event) VALUES ('Cirion', 'Brego', 2489, 'Defeat of Balchoth');
  3. Test a WHERE clause to use to filter data for export. Select only the data from King Brego's reign from 2450 up to, but not including, 2500.
    cqlsh:tosql> SELECT * FROM ruling_stewards WHERE king = 'Brego' AND reign_start >= 2450 AND reign_start < 2500 ALLOW FILTERING;
    
     steward_name | king  | reign_start | event
    --------------+-------+-------------+--------------------
          Boromir | Brego |        2477 |   Attacks continue
           Cirion | Brego |        2489 | Defeat of Balchoth
    
    (2 rows)
  4. Use the fromcql database from the previous example and create a MySQL table to accommodate the CQL ruling_stewards data.
    mysql> USE fromcql;
    mysql> create table sql_rulers (
             steward_name varchar(15) PRIMARY KEY,
             king varchar(15),
             reign_start INTEGER,
             event varchar (40)
           ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  5. Export the only the CQL data from King Brego's reign from 2450 up to, but not including, 2500. This example shows the export command entered on the command line.
    $ dse sqoop cql-export 
        --connect jdbc:mysql://127.0.0.1/fromcql 
        --username root --password root 
        --table sql_rulers 
        --cassandra-host 127.0.0.1 
        --cassandra-keyspace tosql 
        --cassandra-table ruling_stewards  
        --cassandra-select-columns steward_name,king,reign_start 
        --cassandra-where-clause "king='Brego' AND reign_start >=2450 AND reign_start < 2500"

    The MapReduce job runs and the end of the output indicates success exporting two records:

     . . .
    14/09/17 20:25:37 INFO mapreduce.ExportJobBase: Exported 2 records.
  6. Verify that the data was exported into the MySQL table.
    mysql> select * from sql_rulers;
    +--------------+-------+-------------+-------+
    | steward_name | king  | reign_start | event |
    +--------------+-------+-------------+-------+
    | Boromir      | Brego |        2477 | NULL  |
    | Cirion       | Brego |        2489 | NULL  |
    +--------------+-------+-------------+-------+
    2 rows in set (0.02 sec)