<jupyter_start><jupyter_text>Test queries to access Project-2 sparkifydbThis file has queries to verify data in sparkifydb in Apache CassandraThere are two types of queries:1) Query1-3 to answer the exact questions asked in Project-22) Generic queries to access sparkifydb data more generally.<jupyter_code># Import Python packages 
import pandas as pd
import cassandra
import re
import os
import glob
import numpy as np
import json
import csv
# Connect to Apache Cassandra 
from cassandra.cluster import Cluster
try: 
    # Connect to local Apache Cassandra instance.
    cluster = Cluster(['127.0.0.1'])
    # Set session to execute queries.
    session = cluster.connect()

except Exception as e:
    print(e)

# Set KEYSPACE
try:
    session.set_keyspace('sparkifydb')
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Specific Project-2 queries<jupyter_code># QUERY-1: Give me the artist, song title and song's length in the music app history that was heard during \
## sessionId = 338, and itemInSession = 4
query = "SELECT artist, song, length \
                FROM song_in_session \
                WHERE   session_id = 338 AND \
                        item_in_session = 4"
try:
    songs = session.execute(query)
except Exception as e:
    print(e)

for row in songs:
    print (row.artist, row.song, row.length)
# QUERY-2: Give me only the following: name of artist, song (sorted by itemInSession) and 
# user (first and last name) for userid = 10, sessionid = 182
query = "SELECT artist, song, first_name, last_name \
                    FROM artist_in_session \
                    WHERE   user_id = 10 AND \
                    session_id = 182"
try:
    songs = session.execute(query)
except Exception as e:
    print(e)

for row in songs:
    print (row.artist, row.song, row.first_name, row.last_name)
# QUERY-3: Give me every user name (first and last) in my music app history who listened 
# to the song 'All Hands Against His Own'
query = "SELECT first_name, last_name \
                    FROM user_and_song \
                    WHERE song = 'All Hands Against His Own'"
try:
    songs = session.execute(query)
except Exception as e:
    print(e)

for row in songs:
    print (row.first_name, row.last_name)<jupyter_output><empty_output><jupyter_text>Generic queries<jupyter_code>query = "SELECT COUNT(*) FROM song_in_session"
try:
    songs = session.execute(query)
except Exception as e:
    print(e)

for row in songs:
    print (row)

print('---')
query = "SELECT * FROM song_in_session LIMIT 10"
try:
    songs = session.execute(query)
except Exception as e:
    print(e)

print ("session_id", "|", "item_in_session", "|", "artist", "|", "length", "|", "song")
for row in songs:
    print (row.session_id, "|", row.item_in_session, "|", row.artist, "|", row.length, "|", row.song)
query = "SELECT COUNT(*) FROM artist_in_session"
try:
    songs = session.execute(query)
except Exception as e:
    print(e)

for row in songs:
    print (row)

print('---')
query = "SELECT * FROM artist_in_session LIMIT 10"
try:
    songs = session.execute(query)
except Exception as e:
    print(e)

print ("user_id", "|", "session_id", "|", "item_in_session", "|", "artist", "|", "first_name", "|", "last_name")
for row in songs:
    print (row.user_id, "|", row.session_id, "|", row.item_in_session, "|", row.artist, "|", row.first_name, "|", row.last_name)
query = "SELECT COUNT(*) FROM user_and_song"
try:
    songs = session.execute(query)
except Exception as e:
    print(e)

for row in songs:
    print (row)

print('---')
query = "SELECT * FROM user_and_song LIMIT 10"
try:
    songs = session.execute(query)
except Exception as e:
    print(e)

print("song", "|", "first_name", "|", "last_name", "|", "user_id")
for row in songs:
    print (row.song, "|", row.first_name, "|", row.last_name, "|", row.user_id)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12008228336333247, 'fraction_numerical': 0.010285420416559526, 'mean_word_length': 3.486735870818916, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '293337', 'license': 'MIT', 'n_tokens_mistral': 1322, 'n_tokens_neox': 1269, 'n_words': 411, 'path': 'test.ipynb', 'repo_name': 'jukkakansanaho/udacity-dend-project-2', 'size': '3889'}