

I have a CSV file with the following header line:

"first_name","last_name","company_name","address","city","county","state","zip","phone1","phone2","email","web"

and here is the first line of data:
"James","Butt","Benton, John B Jr","6649 N Blue Gum St","New Orleans","Orleans","LA",70116,"504-621-8927","504-845-1427","jbutt@gmail.com","http://www.bentonjohnbjr.com"


I will start by understanding the application requirements, this will drive my data model!!!

The application requires "Partition Fault Tolerance", so I set the replication_factor to 3, spread across 
two DataCenters.

My create KeySpace command looks like this:

        CREATE KEYSPACE sales
         WITH REPLICATION = {'class' : 'NetworkTopologyStrategy',
                             'DataCenterWest' : 2, 'DataCenterEast' : 1};

  
I now need to build a Cassandra Table(s) to hold this data.

After talking with the User, my Data Model is:

        The application is going to query this data by State, so this will be my partition key,
        the application will also be asking for City with the State, so I add the City as a Clustering Column

        After talking with the User, I have determined the County field will not be use as part of a 
        query where clause, but will be used as part of the data selected, so it does not become a Clustering Column

        After talking to the User, I have determined the combination of First Name and Last Name are unique
        (within a City and State) so these are the last two parts of my Primary Key (and Clustering Columns)

        my final Table definition will be:

        CREATE TABLE customers (
         first_name text, 
         last_name text,
         company_name text,
         address text,
         city text,
         county text,
         state text,
         zip int,
         phone1 text,
         phone2 text,
         email text,
         web text,
         PRIMARY KEY ((state), city, last_name, first_name))
           WITH nodesync = {'enabled' : 'true'}  
        )


Now I need to load my CSV "starter" data

I open a cqlsh prompt and enter:
COPY sales.customers (first_name,last_name,company_name,address, city, county, state, zip, phone1, phone2, email, web) FROM '../../us-1000000.csv' WITH HEADER = TRUE; 

I query the cluster with:

select count(*) from customers where state = 'CO';

 count
-------
 22173

select count(*) from customers where state = 'CO' and city = 'Boulder'

 count
-------
  1570

select count(*) from customers where state = 'CO' and city = 'Boulder' and last_name like 'Smith'

InvalidRequest: Error from server: code=2200 [Invalid query] message="LIKE restriction is 
only supported on properly indexed columns. last_name LIKE 'Smith' is not valid."



select * from customers where state = 'CO' and city = 'Boulder' and last_name = 'Buemi';

 state | city    | last_name | first_name | address           | company_name            | county  | email            | phone1       | phone2       | web                                | zip
-------+---------+-----------+------------+-------------------+-------------------------+---------+------------------+--------------+--------------+------------------------------------+---------
    CO | Boulder |     Buemi |     Alease | 4 Webbs Chapel Rd | Porto Cayo At Hawks Cay | Boulder | alease@buemi.com | 303-301-4946 | 303-521-9860 | http://www.portocayoathawkscay.com |   80303


select count(*) from customers where state = 'CO' and city = 'Boulder' and last_name >= 'Adams' and last_name <= 'Zevon';

 count
-------
  1553


select count(*) from customers where state = 'CO' and city = 'Boulder' and first_name >= 'Adams' and first_name <= 'Zevon';

InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY column "first_name" cannot be restricted as preceding column "last_name" is not restricted"



