<jupyter_start><jupyter_text>Populate the staging tablesThe log files are partitioned by year and month.<jupyter_code>log_data = 's3a://udacity-dend/log-data/*/*/*.json'
staging_events = spark.read.json(log_data)
staging_events.cache()
print('Events records: {}'.format(staging_events.count()))
staging_events.printSchema()<jupyter_output><empty_output><jupyter_text>The songs files are partitioned by the first three letters of each song's track ID.<jupyter_code>song_data = 's3a://udacity-dend/song_data/*/*/*/*.json'

# define schema for the song data
song_schema = StructType([
    StructField('song_id', StringType()),
    StructField('artist_id', StringType()),
    StructField('artist_name', StringType()),
    StructField('artist_location', StringType()),
    StructField('artist_latitude', DoubleType()),
    StructField('artist_longitude', DoubleType()),
    StructField('duration', DoubleType()),
    StructField('num_songs', IntegerType()),
    StructField('title', StringType()),
    StructField('year', IntegerType())
])

staging_songs = spark.read.json(song_data, schema = song_schema)
staging_songs.cache()
print('Songs records: {}'.format(staging_songs.count()))
staging_songs.show(5)<jupyter_output><empty_output><jupyter_text>Investigate the staging tablesHow many fact records can we potentially have in table `songplays`?<jupyter_code>songplays = staging_events.where('''
    page = 'NextSong'
    AND ts is NOT NULL
    AND userId is NOT NULL
    AND level  is NOT NULL
''').dropDuplicates(['ts', 'userID'])
songplays.cache()
songplays.count()<jupyter_output><empty_output><jupyter_text>Are there `songplays` for which no song can be matched from `staging_songs` by the artist name and song title?<jupyter_code>songplays.join(staging_songs,
               (songplays.song == staging_songs.title) &
               (songplays.artist == staging_songs.artist_name),
               how = 'left_anti' # https://stackoverflow.com/a/54554055
              ).count()<jupyter_output><empty_output><jupyter_text>How many potential records in `time` dimension? The number of actual records will not exceed the number of facts in `songplays`.<jupyter_code>songplays.dropDuplicates(['ts']).count()<jupyter_output><empty_output><jupyter_text>So are there multiple `NextSong` events at the same time?<jupyter_code>songplays.groupBy(songplays.ts) \
         .agg(count(songplays.userId).alias('users')) \
         .where(col('users') > 1) \
         .show()<jupyter_output><empty_output><jupyter_text>How many users are there in `users` dimension?<jupyter_code>songplays.dropDuplicates(['userId', 'firstName', 'lastName', 'gender']).count()<jupyter_output><empty_output><jupyter_text>For how many users at least one song can be matched from `staging_songs` by the artist name and song title??<jupyter_code>songplays.dropDuplicates(['userId', 'firstName', 'lastName', 'gender']) \
         .join(staging_songs,
               (songplays.song == staging_songs.title) &
               (songplays.artist == staging_songs.artist_name),
               how = 'left_semi' # https://stackoverflow.com/a/54554055
              ) \
         .select(['userId', 'firstName', 'lastName', 'gender']).show()<jupyter_output><empty_output><jupyter_text>How many songs are there in `songs` dimension?<jupyter_code>staging_songs.dropDuplicates(['song_id']).where("song_id is NOT NULL").count()<jupyter_output><empty_output><jupyter_text>How many of these songs will be mentioned in `songplays`?<jupyter_code>staging_songs.join(songplays,
                   (songplays.song == staging_songs.title) &
                   (songplays.artist == staging_songs.artist_name),
                   how = 'left_semi' # https://stackoverflow.com/a/54554055
                  ) \
             .dropDuplicates(['song_id']) \
             .count()<jupyter_output><empty_output><jupyter_text>How many artists are there in `artists` dimension?<jupyter_code>staging_songs.where("artist_id is NOT NULL").dropDuplicates(['artist_id', 'artist_name']).count()<jupyter_output><empty_output><jupyter_text>Are there artists with different names under the same id?<jupyter_code>artist_multinames = staging_songs \
    .dropDuplicates(['artist_id', 'artist_name']) \
    .groupBy('artist_id') \
    .agg(count('artist_name').alias('names')) \
    .where(col('names') > 1) \
    .sort(col('names').desc())
print('Artists with different names under the same id: {}'.format(artist_multinames.count()))
artist_multinames.show(10)<jupyter_output><empty_output><jupyter_text>What do artists with multi names look like?<jupyter_code>staging_songs.dropDuplicates(['artist_id', 'artist_name']) \
    .join(artist_multinames.select('artist_id', 'names'), on = 'artist_id', how = 'left_semi') \
    .select('artist_id', 'artist_name') \
    .sort('artist_id') \
    .show(truncate = False)<jupyter_output><empty_output><jupyter_text>How many artists will be mentioned in `songplays`?<jupyter_code>staging_songs.join(songplays,
                   (songplays.song == staging_songs.title) &
                   (songplays.artist == staging_songs.artist_name),
                   how = 'left_semi' # https://stackoverflow.com/a/54554055
                  ) \
             .dropDuplicates(['artist_id']) \
             .count()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13897280966767372, 'fraction_numerical': 0.00736404833836858, 'mean_word_length': 4.494813278008299, 'pattern_counts': {'":': 0, '<': 57, '<?xml version=': 0, '>': 59, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, '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': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '275771', 'license': 'MIT', 'n_tokens_mistral': 1837, 'n_tokens_neox': 1731, 'n_words': 311, 'path': 'sparkify.ipynb', 'repo_name': 'adzugaiev/Udacity-Data-Engineering-4', 'size': '5296'}