DataWorks:Simple tutorial for house buying group analysis

Step 1: Create a MaxCompute table

Before you upload the test data, you need to use the data catalog feature of DataWorks to create a table named bank_data in the MaxCompute project to store the test data to be uploaded.

1. Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane of the DataWorks console, choose Data Development and O&M > Data Development. On the page that appears, select the desired workspace from the Select Workspace drop-down list and click Go to Data Studio.

2. In the left-side navigation pane of the Data Studio page, click the icon to go to the Data Catalog pane.

3. (Optional) If your MaxCompute project is not added to DataWorks as a data catalog, click the icon next to MaxCompute to go to the DataWorks Data Sources tab. On the DataWorks Data Sources tab, add the MaxCompute project that is already added as a computing resource or data source to the workspace as a MaxCompute data catalog.

4. In the DATA CATALOG pane, click MaxCompute. In the MaxCompute section, create a MaxCompute table in the Table folder under the MaxCompute project that is added as a data catalog.

   Note

   • If the schema feature is enabled for the MaxCompute project, you must open the schema in the MaxCompute project before you can create a MaxCompute table in the Table folder under the MaxCompute project.

   • In this tutorial, a workspace in standard mode is used, and debugging needs to be performed only in the development environment. Therefore, you need to create the bank_data table in the MaxCompute project only in the development environment. If you use a workspace in basic mode, you need to create the bank_data table in the MaxCompute project only in the production environment.

5. Click the icon next to Table to go to the table creation tab.

   In the DDL section of the table creation tab, enter the following SQL code. Then, the system automatically generates all information for the table.

   CREATE TABLE IF NOT EXISTS bank_data
   (
    age             BIGINT COMMENT 'The age',
    job             STRING COMMENT 'The job type',
    marital         STRING COMMENT 'The marital status',
    education       STRING COMMENT 'The education level',
    default         STRING COMMENT 'Whether a credit card is possessed',
    housing         STRING COMMENT 'The mortgage',
    loan            STRING COMMENT 'The loan',
    contact         STRING COMMENT 'The contact information',
    month           STRING COMMENT 'The month',
    day_of_week     STRING COMMENT 'The day of the week',
    duration        STRING COMMENT 'The duration',
    campaign        BIGINT COMMENT 'The number of contacts during the campaign',
    pdays           DOUBLE COMMENT 'The time elapsed since the last contact',
    previous        DOUBLE COMMENT 'The number of contacts with the customer',
    poutcome        STRING COMMENT 'The result of the previous marketing campaign',
    emp_var_rate    DOUBLE COMMENT 'The employment change rate',
    cons_price_idx  DOUBLE COMMENT 'The consumer price index',
    cons_conf_idx   DOUBLE COMMENT 'The consumer confidence index',
    euribor3m       DOUBLE COMMENT 'The euro deposit rate',
    nr_employed     DOUBLE COMMENT 'The number of employees',
    y               BIGINT COMMENT 'Whether a time deposit is possessed'
   );

6. In the top toolbar of the configuration tab, click Deploy to create the bank_data table in the MaxCompute project in the development environment.

7. After the bank_data table is created, click the table name in the MaxCompute section to view the detailed information of the table.

Step 2: Upload data to the bank_data table

Download the banking.csv file to your on-premises machine and then upload data in the file to the bank_data table created in the MaxCompute project. For more information, see Limits.

1. In the upper-left corner of the DataWorks console, click the icon and choose All Products > Data Integration > Upload and Download.

2. In the Recent Upload Records section of the Upload and Download page, click Upload Data. On the Upload Data page, configure the parameters by referring to the following table.

   Section		Description
   Data Source		Select Local File.
   Specify Data to Be Uploaded	Select File	Upload the banking.csv file that is downloaded to your on-premises machine.
   Configure Destination Table	Compute Engine	Select MaxCompute.
   	MaxComputeProject Name	Select the MaxCompute project to which the bank_data table belongs.
   	Select Destination Table	Select bank_data.
   Preview Data of Uploaded File		Click Mapping by Order to complete mapping between the data in the file to the fields in the bank_data table.

   Note

   • You can upload files whose names are suffixed with .csv, .xls, .xlsx, and .json from your on-premises machine.

   • If you upload a file whose name is suffixed with .xls or .xlsx, the first sheet of the file is uploaded by default.

   • If you upload a file whose name is suffixed with .csv, the size of the file cannot exceed 5 GB. For other types of files, the file size cannot exceed 100 MB.

3. Click Upload Data to upload data in the CSV file to the bank_data table.

4. Check whether the data is written to the bank_data table.

   After the data is uploaded, you can use the SQL query feature to check whether the data is written to the bank_data table.

   1. Click the icon in the upper-left corner of the DataWorks console and choose All Products > Data Analysis > SQL Query.

   2. In the SQL Query pane, click the icon next to My Files and select Create File. In the Create File dialog box, configure the File Name parameter based on your business requirements and click OK.

   3. On the configuration tab of the file, enter the following SQL statement:

      SELECT * FROM bank_data limit 10；

   4. In the upper-right corner of the configuration tab of the SQL query file, select the workspace to which the bank_data table belongs and the desired MaxCompute data source, and click OK.

      Note

      In this tutorial, a workspace in standard mode is used, and the bank_data table is created only in the development environment. Therefore, you must select a MaxCompute data source in the development environment. If you use a workspace in basic mode, you can select a MaxCompute data source in the production environment.

   5. In the top toolbar of the configuration tab, click the Run icon. In the Estimate Costs dialog box, click Run. After the SQL statement is executed, you can view the first ten data records in the bank_data table that are displayed in the lower part of the configuration tab. This indicates that the data in the file is successfully uploaded from your on-premises machine to the bank_data table.

      

Step 3: Process data

In this step, you need to use MaxCompute SQL nodes to filter data that is uploaded to the bank_data table to obtain the data about the distribution of education levels among single people who have mortgages, and write the processed data to the result_table table.

Step 4: Debug and run the workflow

After the dw_basic_case workflow is configured, you can debug and run the workflow on the Data Studio page to check whether the configurations of the workflow are correct. To debug and run the workflow, you must go to the configuration tab of the workflow again.

1. After you complete the development of the inner nodes of the workflow, switch to the configuration tab of the workflow and click the Save icon in the top toolbar of the configuration tab to save the workflow.

2. After the workflow is saved, click the Run icon in the top toolbar of the configuration tab to debug and run the workflow.

3. After the running of the workflow is complete, view the result. The following figure shows the expected result.

   

Step 5: Query and display data

After the data is uploaded to the MaxCompute computing resource and processed in Data Studio, you can query the data and view the data analysis result.

1. Click the icon in the upper-left corner of the DataWorks console and choose All Products > Data Analysis > SQL Query.

2. In the SQL Query pane, click the icon next to My Files and select Create File. In the Create File dialog box, configure the File Name parameter based on your business requirements and click OK.

3. On the configuration tab of the file, enter the following SQL statement:

   SELECT * FROM result_table;

4. In the upper-right corner of the configuration tab of the SQL query file, select the workspace to which the result_table table belongs and the desired MaxCompute data source, and click OK.

   Note

   In this tutorial, a workspace in standard mode is used, and the result_table table is created only in the development environment and is not deployed to the production environment. Therefore, you must select a MaxCompute data source in the development environment. If you use a workspace in basic mode, you can select a MaxCompute data source in the production environment.

5. In the top toolbar of the configuration tab, click the Run icon. In the Estimate Costs dialog box, click Run.

6. On the tab that displays the execution result, click the icon to view the visualization result. You can click the icon in the upper-right corner of the tab to customize the graph pattern.

7. In the upper-right corner of the tab that displays the execution result, click the Save icon to save the chart as a card. In the left-side navigation pane of the SQL Query page, click the (Cards) icon to view the saved card.