Step 1: Prepare Data

Preparations Before Using DataArts Studio

If you are new to DataArts Studio, register a Huawei account, buy a DataArts Studio instance, create workspaces, and make other preparations. For details, see Buying and Configuring a DataArts Studio Instance. Then you can go to the created workspace and start using DataArts Studio.

Preparing Data Sources

This practice analyzes the data features of the users and products of an e-commerce store. (The data is from BI reports.)

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. Copy and paste the sample data (user data user_data.csv, product data product_data.csv, comment data comment_data.csv, and behavior data action_data.csv) to different CSV files and save them in UTF-8 without BOM format. The name of a CSV file is the name of the corresponding data table. For example, the user data table is user_data.csv.
   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 file and copy the sample data to the file.
      

      • Check the number of rows and line-break accuracy.
      • If the sample data is replicated 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 containing the sample data to OBS.
   1. Log in to the OBS console.
   2. Click Create Bucket and set parameters as prompted to create an OBS bucket. (In this example, the OBS bucket name is 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. Click the bucket name fast-demo to go to the Objects page.
   4. On the Objects page, click Create Folder to create the user_data, product_data, comment_data, and action_data folders.
      Figure 1 Creating folders
      
   5. Upload the user_data.csv, product_data.csv, comment_data.csv, and action_data.csv files saved in Step 1 to the corresponding folders. (For example, upload user_data.csv to the user_data folder.)
      

      When associating a CSV table with DLI to create an OBS foreign table, you cannot specify the file name and can only specify the file path. Therefore, you need to place CSV tables in different file paths and ensure that each file path contains only the required CSV table.

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

Sample Data

This practice involves the following sample data: user data (user_data.csv), product data (product_data.csv), comment data (comment_data.csv), and action data (action_data.csv).

Preparing a Data Lake

This practice uses DLI as the data foundation. To ensure network connectivity between DataArts Studio and DLI, ensure that you select the same region and enterprise project as those of the DataArts Studio instance when creating a DLI queue.

• The version of the default Spark component of the default DLI queue is not up-to-date, and an error may be reported indicating that a table creation statement cannot be executed. In this case, you are advised to create a queue to run your tasks. If you want to execute table creation statements in the default queue, contact the DLI customer service or technical support.

• The default queue default of DLI is only used for trial. It may be occupied by multiple users at a time. Therefore, it is possible that you fail to obtain the resource for related operations. If the execution takes a long time or fails, you are advised to try again during off-peak hours or use a self-built queue to run the job.

Configure a DLI job bucket, create a DLI connection in Management Center, create a database using the DataArts Factory module, and then run a SQL statement to create an OBS foreign table.

The procedure is as follows:

1. Configure a DLI job bucket. For details, see Configuring a DLI Job Bucket.
2. Log in to the DataArts Studio console.

   For details, see Accessing the DataArts Studio Instance Console.

3. On the DataArts Studio console, locate a workspace and click Management Center.
4. On the displayed Manage Data Connections page, click Create Data Connection.
   Figure 2 Creating a data connection
   

5. Create a DLI data connection. Select DLI for Data Connection Type, set Name to dli, and retain the default values for other parameters.
   Click Test to test the connection. If the test is successful, click Save.

   Figure 3 Creating a data connection
   

6. Go to the DataArts Factory page.
   Figure 4 DataArts Factory page
   

7. Right-click the DLI connection to create a database named BI for storing data tables. For how to create a database, see Figure 5.
   Figure 5 Creating a database
   

8. Create a DLI SQL script by referring to Figure 6. With the script, you can create data tables using DLI SQL statements.
   Figure 6 Creating a script
   

9. In the displayed SQL editor, set key parameters.

   Table 5 Key parameters

   Parameter	Description
   Connection	DLI data connection created in Step 5
   Database	Database created in Step 7
   Queue	The default resource queue default can be used.

   Figure 7 Key parameters
   
   Enter the following SQL statements and click Execute to create data tables: Among them, user, product, comment, and action are OBS foreign tables that store raw data. The data in them is from the CSV files in Preparing Data Sources. top_like_product and top_bad_comment_product are DLI tables that store analysis results.

   create table user(
     user_id int,
     age int,
     gender int,
     rank int,
     register_time string
   ) USING csv OPTIONS (path "obs://fast-demo/user_data");
   create table product(
     product_id int,
     a1 int,
     a2 int,
     a3 int,
     category int,
     brand int
   ) USING csv OPTIONS (path "obs://fast-demo/product_data");
   create table comment(
     deadline string,
     product_id int,
     comment_num int,
     has_bad_comment int,
     bad_comment_rate float
   ) USING csv OPTIONS (path "obs://fast-demo/comment_data");
   create table action(
     user_id int,
     product_id int,
     time string,
     model_id string,
     type string
   ) USING csv OPTIONS (path "obs://fast-demo/action_data");
   create table top_like_product(brand int, like_count int);
   create table top_bad_comment_product(product_id int, comment_num int, bad_comment_rate float);

   Figure 8 Creating data tables
   

10. After the script is successfully executed, create a DLI SQL script by referring to Step 8 and run the following script to check whether the data tables have been successfully created:

    SHOW TABLES;

    

    After confirming that the data tables have been created, you can close the script as it is no longer needed.