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.
- 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
- Create a DWS SQL script used to create data tables by entering DWS SQL statements in the editor.
Figure 2 Creating a script
- 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)
- 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.
- 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
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.
- 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
- Create a DWS SQL script used to create data tables by entering DWS SQL statements in the editor.
Figure 6 Creating a script
- 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)
- 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.
- 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
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.
- 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
- Create a batch job named topmovie.
Figure 10 Creating a job
Figure 11 Configuring the job
- 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.
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.
- After configuring the job, click to test it.
- 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.
- 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.
- 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.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.