<jupyter_start><jupyter_text>Lesson 3 Demo 3: Focus on Clustering Columns  In this demo we are going to walk through the basics of creating a table with a good Primary Key and Clustering Columns in Apache Cassandra, inserting rows of data, and doing a simple SQL query to validate the information.  We will use a python wrapper/ python driver called cassandra to run the Apache Cassandra queries. This library should be preinstalled but in the future to install this library you can run this command in a notebook to install locally: ! pip install cassandra-driver More documentation can be found here:  https://datastax.github.io/python-driver/  Import Apache Cassandra python package<jupyter_code>import cassandra<jupyter_output><empty_output><jupyter_text>First let's create a connection to the database<jupyter_code>from cassandra.cluster import Cluster
try: 
    cluster = Cluster(['127.0.0.1']) #If you have a locally installed Apache Cassandra instance
    session = cluster.connect()
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Let's create a keyspace to do our work in<jupyter_code>try:
    session.execute("""
    CREATE KEYSPACE IF NOT EXISTS udacity 
    WITH REPLICATION = 
    { 'class' : 'SimpleStrategy', 'replication_factor' : 1 }"""
)

except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Connect to our Keyspace. Compare this to how we had to create a new session in PostgreSQL.<jupyter_code>try:
    session.set_keyspace('udacity')
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Let's imagine we would like to start creating a new Music Library of albums.  We want to ask 1 question of our data 1. Give me every album in my music library that was released by an Artist with Albumn Name in `ASC` Order and City In `ASC` Order`select * from music_library WHERE ARTIST_NAME="The Beatles"`  Here is our Collection of DataPlease refer to Table 4 in the video  How should we model this data? What should be our Primary Key and Partition Key? Since our data is looking for the `ARTIST_NAME` let's start with that. From there we will need to add other elements to make sure the Key is unique. We also need to add the `CITY` and `ALBUM_NAME` as Clustering Columns to sort the data. That should be enough to make the row key unique`Table Name: music_librarycolumn 1: Yearcolumn 2: Artist Namecolumn 3: Album NameColumn 4: CityPRIMARY KEY(artist name, album name, city)`<jupyter_code>query = "CREATE TABLE IF NOT EXISTS music_library "
query = query + "(year int, artist_name text, album_name text, city text, PRIMARY KEY (artist_name, album_name, city))"
try:
    session.execute(query)
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Let's insert our data into of table<jupyter_code>query = "INSERT INTO music_library (year, artist_name, album_name, city)"
query = query + " VALUES (%s, %s, %s, %s)"

try:
    session.execute(query, (1970, "The Beatles", "Let it Be", "Liverpool"))
except Exception as e:
    print(e)
    
try:
    session.execute(query, (1965, "The Beatles", "Rubber Soul", "Oxford"))
except Exception as e:
    print(e)
    
try:
    session.execute(query, (1964, "The Beatles", "Beatles For Sale", "London"))
except Exception as e:
    print(e)

try:
    session.execute(query, (1966, "The Monkees", "The Monkees", "Los Angeles"))
except Exception as e:
    print(e)

try:
    session.execute(query, (1970, "The Carpenters", "Close To You", "San Diego"))
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Let's Validate our Data Model -- Did it work?? If we look for Albums from The Beatles we should expect to see 3 rows.`select * from music_library WHERE ARTIST_NAME="The Beatles"`<jupyter_code>query = "select * from music_library WHERE ARTIST_NAME='The Beatles'"
try:
    rows = session.execute(query)
except Exception as e:
    print(e)
    
for row in rows:
    print (row.artist_name, row.album_name, row.city, row.year)<jupyter_output>The Beatles Beatles For Sale London 1964
The Beatles Let it Be Liverpool 1970
The Beatles Rubber Soul Oxford 1965<jupyter_text>Success it worked! We created a unique Primary key that evenly distributed our data, with clustering columns that sorted our data.   For the sake of the demo, I will drop the table.<jupyter_code>query = "drop table music_library"
try:
    rows = session.execute(query)
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>And Finally close the session and cluster connection<jupyter_code>session.shutdown()
cluster.shutdown()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08284789644012945, 'fraction_numerical': 0.010571736785329019, 'mean_word_length': 4.958868894601542, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 36, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '88494', 'license': 'MIT', 'n_tokens_mistral': 1455, 'n_tokens_neox': 1354, 'n_words': 570, 'path': 'Notebook-Exercises/Course-1-Lesson-3-NoSQL/L3-Demo-3-Clustering-Column.ipynb', 'repo_name': 'djanmagno/Udacity-Data-Engineer-Nanodegree', 'size': '4635'}