Help Center/ DataArts Studio/ Getting Started/ Beginners: Data Integration and Development Based on Movie Scores/ Step 3: Develop Data
Updated on 2023-12-11 GMT+08:00
View PDF

Step 3: Develop Data

This step describes how to use the movie information and rating data to analyze 10 top-rated movies and 10 most frequently scored movies. Jobs are periodically executed and the results are exported to tables every day for data analysis.

Creating DWS SQL Script top_rating_movie for Storing 10 Top-rated Movies

The method of finding out the 10 top-rated movies is as follows: Calculate the total score of each movie and the number of the users who participate in scoring the movies, filter out the movies that are scored by less than three users, and then return the movie names, average scores, and participant quantity.

  1. Log in to the DataArts Studio console. Locate an instance and click Access. On the displayed page, locate a workspace and click DataArts Factory.

    Figure 1 DataArts Factory

  2. Create a DWS SQL script used to create data tables by entering DWS SQL statements in the editor.

    Figure 2 Creating a script

  3. In the SQL editor, enter the following SQL statements and click Execute to calculate the 10 top-rated movies from the movies_item and ratings_item tables and save the result to the top_rating_movie table. 

    SET
    SEARCH_PATH TO dgc;
insert
    overwrite into top_rating_movie
select
    a.movieTitle,
    b.ratings / b.rating_user_number as avg_rating,
    b.rating_user_number
from
    movies_item a,
    (
        select
            movieId,
            sum(rating) ratings,
            count(1) as rating_user_number
        from
            ratings_item
        group by
            movieId
    ) b
where
    rating_user_number > 3
    and a.movieId = b.movieId
order by
    avg_rating desc
limit
    10
    Figure 3 Script (top_rating_movie)

    The key parameters are as follows:
    • Data Connection: DWS data connection created in Step 3
    • Database: database created in step 5

  4. After debugging the script, click Save and Submit to submit the script and name it top_rating_movie. This script will be referenced later in Developing and Scheduling a Job.
  5. After the script is saved and executed successfully, you can use the following SQL statement to view data in the top_rating_movie table. You can also download or dump the table data by referring to Figure 4. 

    SET SEARCH_PATH TO dgc;
SELECT * FROM top_rating_movie
    Figure 4 Viewing the data in the top_rating_movie table

Creating DWS SQL Script top_active_movie for Storing 10 Most Frequently Scored Movies

The method of finding out the 10 most frequently scored movies is as follows: Calculate the 10 most frequently scored movies whose average scores are higher than 3.5.

  1. Log in to the DataArts Studio console. Locate an instance and click Access. On the displayed page, locate a workspace and click DataArts Factory.

    Figure 5 DataArts Factory

  2. Create a DWS SQL script used to create data tables by entering DWS SQL statements in the editor.

    Figure 6 Creating a script

  3. In the SQL editor, enter the following SQL statements and click Execute to calculate the 10 most frequently scored movies from the movies_item and ratings_item tables and save the result to the top_active_movie table. 

    SET
    SEARCH_PATH TO dgc;
insert
    overwrite into top_active_movie
select
    *
from
    (
        select
            a.movieTitle,
            b.ratingSum / b.rating_user_number as avg_rating,
            b.rating_user_number
        from
            movies_item a,
            (
                select
                    movieId,
                    sum(rating) ratingSum,
                    count(1) as rating_user_number
                from
                    ratings_item
                group by
                    movieId
            ) b
        where
            a.movieId = b.movieId
    ) t
where
    t.avg_rating > 3.5
order by
    rating_user_number desc
limit
    10
    Figure 7 Script (top_active_movie)
    The key parameters are as follows:
    • Data Connection: DWS data connection created in Step 3
    • Database: database created in step 5

  4. After debugging the script, click Save and Submit to submit the script and name it top_active_movie. This script will be referenced later in Developing and Scheduling a Job.
  5. After the script is saved and executed successfully, you can use the following SQL statement to view data in the top_active_movie table. You can also download or dump the table data by referring to Figure 8. 

    SET SEARCH_PATH TO dgc;
SELECT * FROM top_active_movie
    Figure 8 Viewing the data in the top_active_movie table

Developing and Scheduling a Job

Assume that the movie and rating tables in the OBS bucket are changing in real time. To update top 10 movies every day, use the job orchestration and scheduling functions of DataArts Factory.

  1. Log in to the DataArts Studio console. Locate an instance and click Access. On the displayed page, locate a workspace and click DataArts Factory.

    Figure 9 DataArts Factory

  2. Create a batch job named topmovie.

    Figure 10 Creating a job
    Figure 11 Configuring the job

  3. Open the created job, drag two CDM Job nodes, three Dummy nodes, and two DWS SQL nodes to the canvas, select and drag , and orchestrate the job shown in Figure 12.

    Figure 12 Connecting nodes and configuring node properties

    Key nodes:

    • Begin (Dummy node): serves only as a start identifier.
    • movies_obs2dws (CDM Job node): In Node Properties, select the CDM cluster in Step 2: Integrate Data and associate it with the CDM job movies_obs2dws.
    • ratings_obs2dws (CDM Job node): In Node Properties, select the CDM cluster in Step 2: Integrate Data and associate it with the CDM job ratings_obs2dws.
    • Waiting (Dummy node): No operation is performed. It is an identifier of the execution completion of the previous node.
    • top_rating_movie (DWS SQL node): In Node Properties, associate this node with the DWS SQL script top_rating_movie you have created in Creating DWS SQL Script top_rating_movie.
    • top_active_movie (DWS SQL node): In Node Properties, associate this node with the DWS SQL script top_active_movie you have created in Creating DWS SQL Script top_active_movie.
    • Finish (Dummy node): serves only as an end identifier.

  4. After configuring the job, click to test it.
  5. If the job runs properly, click Scheduling Setup in the right pane and configure the scheduling policy for the job.

    Figure 13 Configuring scheduling

    Notes:

    • Scheduling Properties: The job is executed at 01:00 every day from Feb 09 to Feb 28, 2022.
    • Dependency Properties: You can configure a dependency job for this job. You do not need to configure it in this practice.
    • Cross-Cycle Dependency: Select Independent on the previous schedule cycle.

  6. Click Save and Submit and Execute. Then the job will be automatically executed every day so the 10 highest scored and most frequently scored movies are automatically saved to the top_active_movie and top_rating_movie tables, respectively.
  7. If you want to check the job execution result, choose Monitoring > Monitor Instance in the left navigation pane.

    Figure 14 Viewing the job execution status

You can also configure notifications to be sent through SMS messages, emails, or console when a job encounters exceptions or fails.

Now you have learned the data integration and development process based on movie scores. In addition, you can analyze the ratings and browsing of different types of movies to provide valuable information for marketing decision-making, advertising, and user behavior prediction.