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

Step 1: Make Preparations

Preparations

If you use DataArts Studio for the first time, register a , buy a DataArts Studio instance, and create a workspace by following the instructions provided in Preparations. Then you can go to the created workspace and start using DataArts Studio.

Preparing Data Sources

This practice uses the 100,000 scores given by 1,000 users to 1,700 movies. The scores are available at https://grouplens.org/datasets/movielens/100k/. Obtain the .zip package from the link and extract the u.item and u.data files from it. They contain the movie information and rating information, respectively.

To facilitate demonstration, this practice provides some data used to simulate the original data. To integrate the source data into the cloud, you need to store the sample data in CSV files and upload them to an OBS bucket.

  1. Create CSV files (UTF-8 without BOM), name the files with the corresponding data table names, copy the sample data to different CSV files, and save the files.

    To generate a CSV file in Windows, you can perform the following steps:
    1. Use a text editor (for example, Notepad) to create a .txt document and copy the sample data to the document. Then check the total number of rows and check whether the data of rows is correctly separated. (If the sample data is copied from a PDF document, the data in a single row will be wrapped if the data is too long. In this case, you must manually adjust the data to ensure that it is in a single row.)
    2. Choose File > Save as. In the displayed dialog box, set Save as type to All files (*.*), enter the file name with the .csv suffix for File name, and select the UTF-8 encoding format (without BOM) to save the file in CSV format.

  2. Upload the CSV file to OBS.

    1. Log in to the management console and choose Storage > Object Storage Service to access the OBS console.
    2. Click Create Bucket and set parameters as prompted to create an OBS bucket named fast-demo.

      To ensure network connectivity, select the same region for OBS bucket as that for the DataArts Studio instance. If an enterprise project is required, select the enterprise project that is the same as that of the DataArts Studio instance.

      For details about how to create a bucket on the OBS console, see Creating a Bucket in Object Storage Service Console Operation Guide.

    3. Upload data to OBS bucket fast-demo.

      For details about how to upload a file on the OBS console, see Uploading a File in Object Storage Service Console Operation Guide.

This practice involves movie data (movies.csv) and rating data (ratings.csv). Descriptions of the data are as follows:
  • movies.csv: 
    movieId,movieTitle,videoReleaseDate,IMDbURL,unknown,Action,Adventure,Animation,Childrens,Comedy,Crime,Documentary,Drama,Fantasy,FilmNoir,Horror,Musical,Mystery,Romance,SciFi,Thriller,War,Western
1,Toy Story (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Toy%20Story%20(1995),0,0,0,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0
2,GoldenEye (1995),1-Jan-95,http://us.imdb.com/M/title-exact?GoldenEye%20(1995),0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0
3,Four Rooms (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Four%20Rooms%20(1995),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0
4,Get Shorty (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Get%20Shorty%20(1995),0,1,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0
5,Copycat (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Copycat%20(1995),0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,1,0,0
6,Shanghai Triad (Yao a yao yao dao waipo qiao) (1995),1-Jan-95,http://us.imdb.com/Title?Yao+a+yao+yao+dao+waipo+qiao+(1995),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0
7,Twelve Monkeys (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Twelve%20Monkeys%20(1995),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,1,0,0,0
8,Babe (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Babe%20(1995),0,0,0,0,1,1,0,0,1,0,0,0,0,0,0,0,0,0,0
9,Dead Man Walking (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Dead%20Man%20Walking%20(1995),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0
10,Richard III (1995),22-Jan-96,http://us.imdb.com/M/title-exact?Richard%20III%20(1995),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,1,0
11,Seven (Se7en) (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Se7en%20(1995),0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,1,0,0
12,"Usual Suspects, The (1995)",14-Aug-95,"http://us.imdb.com/M/title-exact?Usual%20Suspects,%20The%20(1995)",0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,1,0,0
13,Mighty Aphrodite (1995),30-Oct-95,http://us.imdb.com/M/title-exact?Mighty%20Aphrodite%20(1995),0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0
14,"Postino, Il (1994)",1-Jan-94,"http://us.imdb.com/M/title-exact?Postino,%20Il%20(1994)",0,0,0,0,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0
15,Mr. Holland's Opus (1995),29-Jan-96,http://us.imdb.com/M/title-exact?Mr.%20Holland's%20Opus%20(1995),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0
16,French Twist (Gazon maudit) (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Gazon%20maudit%20(1995),0,0,0,0,0,1,0,0,0,0,0,0,0,0,1,0,0,0,0
17,From Dusk Till Dawn (1996),5-Feb-96,http://us.imdb.com/M/title-exact?From%20Dusk%20Till%20Dawn%20(1996),0,1,0,0,0,1,1,0,0,0,0,1,0,0,0,0,1,0,0
18,"White Balloon, The (1995)",1-Jan-95,http://us.imdb.com/M/title-exact?Badkonake%20Sefid%20(1995),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0
19,Antonia's Line (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Antonia%20(1995),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0
20,Angels and Insects (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Angels%20and%20Insects%20(1995),0,0,0,0,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0
21,Muppet Treasure Island (1996),16-Feb-96,http://us.imdb.com/M/title-exact?Muppet%20Treasure%20Island%20(1996),0,1,1,0,0,1,0,0,0,0,0,0,1,0,0,0,1,0,0
22,Braveheart (1995),16-Feb-96,http://us.imdb.com/M/title-exact?Braveheart%20(1995),0,1,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,1,0
23,Taxi Driver (1976),16-Feb-96,http://us.imdb.com/M/title-exact?Taxi%20Driver%20(1976),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,0
24,Rumble in the Bronx (1995),23-Feb-96,http://us.imdb.com/M/title-exact?Hong%20Faan%20Kui%20(1995),0,1,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0
25,"Birdcage, The (1996)",8-Mar-96,"http://us.imdb.com/M/title-exact?Birdcage,%20The%20(1996)",0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0
26,"Brothers McMullen, The (1995)",1-Jan-95,"http://us.imdb.com/M/title-exact?Brothers%20McMullen,%20The%20(1995)",0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0
27,Bad Boys (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Bad%20Boys%20(1995),0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
28,Apollo 13 (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Apollo%2013%20(1995),0,1,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,0
29,Batman Forever (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Batman%20Forever%20(1995),0,1,1,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0
30,Belle de jour (1967),1-Jan-67,http://us.imdb.com/M/title-exact?Belle%20de%20jour%20(1967),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0
31,Crimson Tide (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Crimson%20Tide%20(1995),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,1,0
32,Crumb (1994),1-Jan-94,http://us.imdb.com/M/title-exact?Crumb%20(1994),0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0
33,Desperado (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Desperado%20(1995),0,1,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0
34,"Doom Generation, The (1995)",1-Jan-95,"http://us.imdb.com/M/title-exact?Doom%20Generation,%20The%20(1995)",0,0,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0
35,Free Willy 2: The Adventure Home (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Free%20Willy%202:%20The%20Adventure%20Home%20(1995),0,0,1,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0
36,Mad Love (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Mad%20Love%20(1995),0,0,0,0,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0
37,Nadja (1994),1-Jan-94,http://us.imdb.com/M/title-exact?Nadja%20(1994),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0
38,"Net, The (1995)",1-Jan-95,"http://us.imdb.com/M/title-exact?Net,%20The%20(1995)",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,0
39,Strange Days (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Strange%20Days%20(1995),0,1,0,0,0,0,1,0,0,0,0,0,0,0,0,1,0,0,0
40,"To Wong Foo, Thanks for Everything! Julie Newmar (1995)",1-Jan-95,"http://us.imdb.com/M/title-exact?To%20Wong%20Foo,%20Thanks%20for%20Everything!%20Julie%20Newmar%20(1995)",0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0
41,Billy Madison (1995),1-Jan-95,http://us.imdb.com/M/title-exact?Billy%20Madison%20(1995),0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0
42,Clerks (1994),1-Jan-94,http://us.imdb.com/M/title-exact?Clerks%20(1994),0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0
43,Disclosure (1994),1-Jan-94,http://us.imdb.com/M/title-exact?Disclosure%20(1994),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,0
44,Dolores Claiborne (1994),1-Jan-94,http://us.imdb.com/M/title-exact?Dolores%20Claiborne%20(1994),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,0
45,Eat Drink Man Woman (1994),1-Jan-94,http://us.imdb.com/M/title-exact?Yinshi%20Nan%20Nu%20(1994),0,0,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0
46,Exotica (1994),1-Jan-94,http://us.imdb.com/M/title-exact?Exotica%20(1994),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0
47,Ed Wood (1994),1-Jan-94,http://us.imdb.com/M/title-exact?Ed%20Wood%20(1994),0,0,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0
48,Hoop Dreams (1994),1-Jan-94,http://us.imdb.com/M/title-exact?Hoop%20Dreams%20(1994),0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0
49,I.Q. (1994),1-Jan-94,http://us.imdb.com/M/title-exact?I.Q.%20(1994),0,0,0,0,0,1,0,0,0,0,0,0,0,0,1,0,0,0,0
50,Star Wars (1977),1-Jan-77,http://us.imdb.com/M/title-exact?Star%20Wars%20(1977),0,1,1,0,0,0,0,0,0,0,0,0,0,0,1,1,0,1,0

    The following table describes the data.

    Table 1 Movie data description

    Field

    Type

    Description

    movieId

    INT

    Movie ID

    movieTitle

    VARCHAR

    Movie name

    videoReleaseDate

    VARCHAR

    Release date

    IMDbURL

    VARCHAR

    IMDb link

    unknown

    INT

    Whether the movie type is unknown. If yes, the value is 1; otherwise, the value is 0.

    Action

    INT

    Whether the movie type is action. If yes, the value is 1; otherwise, the value is 0.

    Adventure

    INT

    Whether the movie type is adventure. If yes, the value is 1; otherwise, the value is 0.

    Animation

    INT

    Whether the movie type is animation. If yes, the value is 1; otherwise, the value is 0.

    Childrens

    INT

    Whether the movie type is children. If yes, the value is 1; otherwise, the value is 0.

    Comedy

    INT

    Whether the movie type is comedy. If yes, the value is 1; otherwise, the value is 0.

    Crime

    INT

    Whether the movie type is crime. If yes, the value is 1; otherwise, the value is 0.

    Documentary

    INT

    Whether the movie type is documentary. If yes, the value is 1; otherwise, the value is 0.

    Drama

    INT

    Whether the movie type is drama. If yes, the value is 1; otherwise, the value is 0.

    Fantasy

    INT

    Whether the movie type is fantasy. If yes, the value is 1; otherwise, the value is 0.

    FilmNoir

    INT

    Whether the movie type is noir. If yes, the value is 1; otherwise, the value is 0.

    Horror

    INT

    Whether the movie type is horror. If yes, the value is 1; otherwise, the value is 0.

    Musical

    INT

    Whether the movie type is musical. If yes, the value is 1; otherwise, the value is 0.

    Mystery

    INT

    Whether the movie type is mystery. If yes, the value is 1; otherwise, the value is 0.

    Romance

    INT

    Whether the movie type is romance. If yes, the value is 1; otherwise, the value is 0.

    SciFi

    INT

    Whether the movie type is science fiction. If yes, the value is 1; otherwise, the value is 0.

    Thriller

    INT

    Whether the movie type is thriller. If yes, the value is 1; otherwise, the value is 0.

    War

    INT

    Whether the movie type is war. If yes, the value is 1; otherwise, the value is 0.

    Western

    INT

    Whether the movie type is western. If yes, the value is 1; otherwise, the value is 0.
  • ratings.csv: 
    userId,movieId,rating,timestamp
210,40,3,891035994
224,29,3,888104457
308,1,4,887736532
7,32,4,891350932
10,16,4,877888877
99,4,5,886519097
115,20,3,881171009
138,26,5,879024232
243,15,3,879987440
293,5,3,888906576
162,25,4,877635573
135,23,4,879857765
62,21,3,879373460
59,23,5,888205300
43,14,2,883955745
19,4,4,885412840
5,2,3,875636053
72,48,4,880036718
224,26,3,888104153
299,14,4,877877775
151,10,5,879524921
6,14,5,883599249
250,7,4,878089716
268,2,2,875744173
292,11,5,881104093
181,3,2,878963441
145,15,2,875270655
1,33,4,878542699
276,2,4,874792436
18,26,4,880129731
87,40,3,879876917
272,12,5,879455254
296,20,5,884196921
5,17,4,875636198
128,15,4,879968827
287,1,5,875334088
65,47,2,879216672
1,20,4,887431883
290,50,5,880473582
45,25,4,881014015
109,8,3,880572642
157,25,3,886890787
301,33,4,882078228
62,12,4,879373613
276,40,3,874791871
269,22,1,891448072
10,7,4,877892210
244,17,2,880607205
222,26,3,878183043
185,23,4,883524249
207,13,3,875506839
8,22,5,879362183
222,49,3,878183512
200,11,5,884129542
90,25,5,891384789
15,25,3,879456204
234,10,3,891227851
295,39,4,879518279
217,2,3,889069782
189,20,5,893264466
42,44,3,881108548
268,21,3,875742822
262,28,3,879792220
90,22,4,891384357
270,25,5,876954456
194,23,4,879522819
161,48,1,891170745
58,9,4,884304328
79,50,4,891271545
221,48,5,875245462
223,11,3,891550649
292,9,4,881104148
16,8,5,877722736
17,13,3,885272654
148,1,4,877019411
280,1,4,891700426
110,38,3,886988574
90,12,5,891383241
239,9,5,889180446
311,9,4,884963365
151,13,3,879542688
2,50,5,888552084
8,50,5,879362124
286,44,3,877532173
85,25,2,879452769
274,50,5,878944679
217,27,1,889070011
181,14,1,878962392
297,25,4,874954497
1,47,4,875072125
6,23,4,883601365
222,22,5,878183285
314,28,5,877888346
291,15,5,874833668
94,24,4,885873423
83,43,4,880308690
43,40,3,883956468
44,15,4,878341343
158,24,4,880134261
151,12,5,879524368
66,1,3,883601324
5,1,4,875635748
207,25,4,876079113
109,1,4,880563619
227,50,4,879035347
181,1,3,878962392
213,13,4,878955139
121,14,5,891390014
117,15,5,880125887
85,13,3,879452866
313,22,3,891014870
43,5,4,875981421
11,38,3,891905936
72,28,4,880036824
115,8,5,881171982
95,1,5,879197329
145,22,5,875273021
66,7,3,883601355
267,17,4,878971773
25,25,5,885853415
103,24,4,880415847
87,9,4,879877931
49,47,5,888068715
135,39,3,879857931
269,13,4,891446662
99,50,5,885679998
306,14,5,876503995
291,7,5,874834481
312,28,4,891698300
184,36,3,889910195
305,11,1,886323237
198,7,4,884205317
104,7,3,888465972
293,39,3,888906804
256,25,5,882150552
92,15,3,875640189
1,17,3,875073198
214,42,5,892668130
82,14,4,876311280
305,50,5,886321799
223,8,2,891550684
91,28,4,891439243
315,13,4,879821158
269,9,4,891446246
217,7,4,889069741
49,7,4,888067307
87,2,4,879876074
268,1,3,875742341
262,47,2,879794599
84,12,5,883452874
264,33,3,886122644
224,20,1,888104487
200,24,2,884127370
92,24,3,875640448
276,38,3,874792574
286,34,5,877534701
49,38,1,888068289
311,5,3,884365853
269,47,4,891448386
194,4,4,879521397
57,28,4,883698324
108,50,4,879879739
207,4,4,876198457
181,16,1,878962996
94,9,5,885872684
234,20,4,891227979
68,7,3,876974096
13,14,4,884538727
98,47,4,880498898
53,24,3,879442538
239,10,5,889180338
63,20,3,875748004
276,43,1,874791383
272,48,4,879455143
116,7,2,876453915
26,25,3,891373727
62,24,4,879372633
295,47,5,879518166
63,50,4,875747292
49,17,2,888068651
310,24,4,879436242
7,44,5,891351728
326,22,4,879874989
213,12,5,878955409
222,29,3,878184571
249,11,5,879640868
217,22,5,889069741
189,1,5,893264174
234,50,4,892079237
296,48,5,884197091
81,3,4,876592546
151,15,4,879524879
59,12,5,888204260
246,8,3,884921245
276,34,2,877934264
97,50,5,884239471
244,7,4,880602558
298,8,5,884182748
7,28,5,891352341
41,28,4,890687353

    The following table describes the data.

    Table 2 Rating data description

    Field

    Type

    Description

    userId

    INT

    User ID

    movieId

    INT

    Movie ID

    rating

    INT

    Rating. The total score is 5 points.

    timestamp

    VARCHAR

    Timestamp

Preparing a Data Lake

This practice uses DWS as the data lake.

For details about how to create a DWS cluster, see Creating a Cluster. The DWS cluster must meet the following requirements so that it can communicate with the DataArts Studio instance:
  • If the CDM cluster in the DataArts Studio instance and the DWS cluster are in different regions, a public network or a dedicated connection is required.
  • If the CDM cluster in the DataArts Studio instance and the DWS cluster are in the same region, VPC, subnet, and security group, they can communicate with each other by default. If they are in the same VPC but in different subnets or security groups, you must configure routing rules and security group rules. For details about how to configure routing rules, see Configuring Routes. For details about how to configure security group rules, see Configuring Security Group Rules.
  • The DWS cluster and the DataArts Studio workspace belong to the same enterprise project. If they do not, you can modify the enterprise project of the workspace.

After creating a DWS cluster, you need to create a DWS connection in Management Center, create a database and schema through the DataArts Factory module, and run an SQL statement to create a DWS table. The procedure is as follows:

  1. On the DataArts Studio console, locate a workspace and click Management Center.

    Figure 1 Management Center

  2. On the displayed Manage Data Connections page, click Create Data Connection.

    Figure 2 Creating a data connection

  3. On the displayed page, configure the following parameters and click OK. Figure 3 lists the parameters.

    • Data Connection Type: Select DWS.
    • Name: Enter dws_link.
    • Manual: Disable it, and you do not need to manually specify IP Address and Port.
    • Cluster Name: Select the DWS cluster that you have created.
    • Username: Enter the database username that you specified when creating the DWS cluster. The default username is dbadmin.
    • Password: Enter the password that you specified when creating the DWS cluster for accessing the database.
    • KMS Key: Select a KMS key used to encrypt sensitive data. If no KMS key is available, click Access KMS to go to the KMS console and create one.
    • Agent: Select a CDM cluster as the connection agent. The CDM cluster must be able to communicate with the DWS cluster. In this example, you can select the DataArts Migration cluster that is automatically created when the DataArts Studio instance is created.
    Figure 3 DWS connection parameters

  4. Go to the DataArts Factory page.

    Figure 4 DataArts Factory page

  5. Create a DWS database and a database schema.

    1. Right-click the DWS connection and select Create Database to create a database named demo for storing data tables.
      Figure 5 Creating a database
    2. Expand the DWS connection directory to the database schema level, right-click schemas, and select Create Schema to create a schema named dgc for storing data tables.
      Figure 6 Creating a database schema

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

    Figure 7 Creating a script

  7. In the SQL editor, enter the following SQL statements and click Execute to create data tables. Among them, movies_item and ratings_item are original data tables, to which data will be migrated from OBS through CDM. top_rating_movie and top_active_movie are result tables which store analysis results. 

    SET SEARCH_PATH TO dgc;
CREATE TABLE IF NOT EXISTS movies_item(
    movieId INT,
    movieTitle VARCHAR,
    videoReleaseDate VARCHAR,
    IMDbURL VARCHAR,
    unknown INT,
    Action INT,
    Adventure INT,
    Animation INT,
    Childrens INT,
    Comedy INT,
    Crime INT,
    Documentary INT,
    Drama INT,
    Fantasy INT,
    FilmNoir INT,
    Horror INT,
    Musical INT,
    Mystery INT,
    Romance INT,
    SciFi INT,
    Thriller INT,
    War INT,
    Western INT
);

CREATE TABLE IF NOT EXISTS ratings_item(  
  userId INT,
  movieId INT,
  rating INT,
  timestamp VARCHAR
);

CREATE TABLE IF NOT EXISTS top_rating_movie(
  movieTitle VARCHAR,
  avg_rating float,
  rating_user_number int
); 

CREATE TABLE IF NOT EXISTS top_active_movie(
  movieTitle VARCHAR,
  avg_rating float,
  rating_user_number int
);
    Figure 8 Creating data tables
    The key parameters are as follows:
    • Data Connection: DWS data connection created in Step 3
    • Database: database created in step 5

  8. After the script is executed successfully, run the following script to check whether the data tables are created successfully. After confirming that the data tables are created successfully, you can close the script as it is no longer needed. 

    SELECT * FROM pg_tables;

Preparing Authentication Data

If you want to migrate OBS data using CDM, you need AK/SK authentication. Therefore, you must create an AK/SK pair.
  • Access Key ID (AK): indicates the ID of the access key, which is a unique identifier associated with a secret access key and is used in conjunction with a secret access key to sign requests cryptographically.
  • Secret Access Key (SK): indicates the key used with its associated AK to cryptographically sign requests and identify request senders to prevent requests from being modified.
To obtain an access key, perform the following steps:
  1. Log in to the management console, move the cursor to the username in the upper right corner, and select My Credentials from the drop-down list.
  2. On the My Credentials page, choose Access Keys, and click Create Access Key. See Figure 9.
    Figure 9 Clicking Create Access Key
  3. Click OK and save the access key file as prompted. The access key file will be saved to your browser's configured download location. Open the credentials.csv file to view Access Key Id and Secret Access Key.
    • Only two access keys can be added for each user.
    • To ensure access key security, the access key is automatically downloaded only when it is generated for the first time and cannot be obtained from the management console later. Keep them properly.