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. 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. In the fast-demo OBS bucket, create folders user_data, product_data, comment_data, and action_data, and upload files user_data.csv, product_data.csv, comment_data.csv, and action_data.csv to the corresponding folders.
      

      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.

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. To enable the execution of table creation statements in the default queue, contact the customer service or technical support of the DLI service.

• 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.

After enabling DLI, you need to create a DLI connection in Management Center, create a database through the DataArts Factory module, and run an SQL statement to create an OBS foreign table. The procedure is as follows:

1. Log in to the DataArts Studio console by following the instructions in Accessing the DataArts Studio Instance Console.
2. On the DataArts Studio console, locate a workspace and click Management Center.
3. On the displayed Manage Data Connections page, click Create Data Connection.
   Figure 1 Creating a data connection
   

4. Create a DLI data connection. Select DLI for Data Connection Type and set Name to dli.

   Click Test to test the connection. If the test is successful, click OK.

   Figure 2 Creating a data connection
   

5. Go to the DataArts Factory page.
   Figure 3 DataArts Factory page
   

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

7. Create a DLI SQL script used to create data tables by entering DLI SQL statements in the editor.
   Figure 5 Creating a script
   

8. In the SQL editor, 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 these files is from CSV files in specified OBS paths. 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 6 Creating data tables
   
   The key parameters are as follows:

   • Data Connection: DLI data connection created in Step 4
   • Database: database created in Step 6
   • Resource Queue: The default resource queue default can be used.
     

     • 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. To enable the execution of table creation statements in the default queue, contact the customer service or technical support of the DLI service.

     • 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.

9. After the script is executed successfully, run the following script to check whether the data tables are created successfully.

   SHOW TABLES;

   

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