<jupyter_start><jupyter_text>Dashboard for analytic queries against Sparkify Date LakeHere is examples of possible analytic queries. You can create your own queries just using the Spark SQL syntax. Tables structure is described in the README.<jupyter_code>import configparser
import os
from pyspark.sql import SparkSession<jupyter_output><empty_output><jupyter_text>First we should setup credentials to S3 and then we can answer on some analytical questions. > `S3` section of the configuration file `dl.cfg` should be filled with user credentials with Full Access rights to S3.<jupyter_code>config = configparser.ConfigParser()
config.read('dl.cfg')

os.environ['AWS_ACCESS_KEY_ID'] = config.get('S3', 'AWS_ACCESS_KEY_ID')
os.environ['AWS_SECRET_ACCESS_KEY'] = config.get('S3', 'AWS_SECRET_ACCESS_KEY')
spark = SparkSession \
    .builder \
    .appName('Sparkify Analytics') \
    .getOrCreate()
spark.read.parquet('tables/songs/songs.parquet').createOrReplaceTempView('songs')
spark.read.parquet('tables/artists/artists.parquet').createOrReplaceTempView('artists')
spark.read.parquet('tables/users/users.parquet').createOrReplaceTempView('users')
spark.read.parquet('tables/time/time.parquet').createOrReplaceTempView('time')
spark.read.parquet('tables/songplays/songplays.parquet').createOrReplaceTempView('songplays')<jupyter_output><empty_output><jupyter_text>Find top 10 most popular songsCompany want to publish top charts of songs. Find top 10 songs that users listened to most often. Print `song` (name of the song), `artist` and `play_count` (how many times users listened the song)._(*): The output could contains a single row because of lack of real data. This is just an example of the posible query_<jupyter_code>spark.sql("""
    SELECT s.title as song
        , a.name as artist
        , COUNT(*) as play_count
    FROM songplays sp
    INNER JOIN songs s ON s.song_id = sp.song_id
    LEFT JOIN artists a ON a.artist_id = sp.artist_id
    GROUP BY s.title, a.name
    ORDER BY play_count DESC
    LIMIT 10
""").show()<jupyter_output>+--------------+------+----------+
|          song|artist|play_count|
+--------------+------+----------+
|<NAME>| Elena|         1|
+--------------+------+----------+<jupyter_text>Weekly statisticsBuild a report for each year, month and week to show how many songs were played and how many unique users uses Sparkify service. Report should contain following fields: `year`, `month`, `week`, `song_count` (how many songs were played), `user_count` (unique users which used the service at least once this month)._(*): The output could contains a single row because of lack of real data. This is just an example of the posible query_<jupyter_code>spark.sql("""
    SELECT t.year
        , t.month
        , t.week
        , COUNT(*) as song_count
        , COUNT(DISTINCT sp.user_id) as user_count
    FROM songplays sp
    INNER JOIN time t ON t.start_time = sp.start_time
    GROUP BY t.year, t.month, t.week
    ORDER BY t.year ASC, t.month, t.week ASC
""").show()<jupyter_output>+----+-----+----+----------+----------+
|year|month|week|song_count|user_count|
+----+-----+----+----------+----------+
|2018|   11|  47|         1|         1|
+----+-----+----+----------+----------+
{'fraction_non_alphanumeric': 0.15970287836583102, 'fraction_numerical': 0.006809037449705973, 'mean_word_length': 3.3440860215053765, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 17, 'https://': 0, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '649397', 'license': 'MIT', 'n_tokens_mistral': 1027, 'n_tokens_neox': 965, 'n_words': 308, 'path': '04-data-lake/dashboard.ipynb', 'repo_name': 'Ceridan/data-engineering-projects', 'size': '3239'}