DataWorks:Data Studio: For developers

Go to Data Studio

Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.

Procedure

1. Step 1: Create a workflow.

   Data development is organized around workflows. You must create a workflow before you can begin developing code.

2. Step 2: Create tables.

   DataWorks allows you to create tables visually, which are then displayed in a directory structure. Before you begin data development, you must create a table in the compute engine to store data cleansing results.

3. Step 3: Create a node.

   DataWorks encapsulates tasks from different compute engines into different node types. You can select the appropriate node type based on your business needs.

4. Step 4: Edit the node.

   On the node editing page, write your business logic using the syntax of the node's database engine.

5. Step 5: Configure schedule settings for the node.

   Define the scheduling properties for the node to run it periodically.

6. Step 6: Debug the code.

   DataWorks provides three methods to debug your code and verify its logic: Quick run for code snippets, Run, and Advanced run.

7. Step 7: Save and submit the node.

   After you debug the node, you must save and submit it.

8. Step 8: Run smoke testing.

   To ensure that production tasks run efficiently and to avoid wasting compute resources, run smoke testing on your task in the development environment to verify its correctness before deployment.

9. Step 9: Deploy the task.

   DataWorks only runs scheduled tasks that are deployed to the production environment. After the smoke testing is successful, you must deploy the task to the production scheduling system.

Step 1: Create a workflow

DataWorks organizes data development around workflows. Workflows use container-like panels for development nodes, grouping related tools, optimizations, and management actions around a central object. This approach simplifies development and management. You can group related business tasks into a single workflow to meet your requirements.

1. Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.

2. Create a workflow.

   You can create a workflow in one of the following two ways:

   • Method 1: Hover over the icon and click Create Workflow.

   • Method 2: In the left-side navigation tree of Data Studio, right-click Workflow and select Create Workflow.

3. Enter a name and description for the workflow and click Create.

   This tutorial creates a workflow named Create your first scheduled task. In a real-world scenario, you should plan your workflows based on your business requirements.

   Note

   For more information about workflows, see Create and manage workflows.

Step 2: Create tables

Data Studio nodes cleanse and process your source data. Therefore, you must first create tables in the compute engine to store the data cleansing results and define the table structure.

1. Create the tables.

   1. In the workflow you created in Step 1, expand the sub-directory, right-click MaxCompute > Table, and select Create Table.

   2. Configure the table name, engine instance, and other information.

   In this tutorial, the following two tables are created.

   Table name	Description
   bank_data	Stores raw business data.
   result_table	Stores the cleansed data results.

   Note

   • For the table creation statements, see Table creation statements.

   • For more information about visually creating tables for various engines, such as creating MaxCompute or EMR tables, see Create a table.

2. Configure the table structure.

   Go to the table editing page, switch to DDL mode, and use DDL statements to configure the table structure. After the table structure is generated, enter a Display Name for the table in the General section. Then, submit it to both the development environment and the production environment. After you submit the table, you can view it in the data source project of the corresponding environment. To view the bound data source information for each environment, see Bind MaxCompute compute resources.

   Note

   • Table operations such as creation and updates take effect in the compute engine only after you submit them to the corresponding environment.

   • You can also configure the table structure visually based on your business needs and on-screen instructions. For more information about visually creating tables, see Create and use MaxCompute tables.

   The following statement is a reference for generating the bank_data table structure.

   CREATE TABLE IF NOT EXISTS bank_data
   (
    age             BIGINT COMMENT 'Age',
    job             STRING COMMENT 'Job type',
    marital         STRING COMMENT 'Marital status',
    education       STRING COMMENT 'Education level',
    default         STRING COMMENT 'Whether the user has a credit card',
    housing         STRING COMMENT 'Housing loan',
    loan            STRING COMMENT 'Personal loan',
    contact         STRING COMMENT 'Contact method',
    month           STRING COMMENT 'Month of the year',
    day_of_week     STRING COMMENT 'Day of the week',
    duration        STRING COMMENT 'Last contact duration, in seconds',
    campaign        BIGINT COMMENT 'Number of contacts performed during this campaign',
    pdays           DOUBLE COMMENT 'Days since the last contact from a previous campaign',
    previous        DOUBLE COMMENT 'Number of contacts performed before this campaign',
    poutcome        STRING COMMENT 'Outcome of the previous marketing campaign',
    emp_var_rate    DOUBLE COMMENT 'Employment variation rate',
    cons_price_idx  DOUBLE COMMENT 'Consumer price index',
    cons_conf_idx   DOUBLE COMMENT 'Consumer confidence index',
    euribor3m       DOUBLE COMMENT 'Euribor 3-month rate',
    nr_employed     DOUBLE COMMENT 'Number of employees',
    y               BIGINT COMMENT 'Whether the client has subscribed to a term deposit'
   );

   The following statement is a reference for generating the result_table table structure.

   CREATE TABLE IF NOT EXISTS result_table
   (
   education STRING COMMENT 'Education level',
   num BIGINT COMMENT 'Number of people'
   )
   PARTITIONED BY
   (
   day STRING,
   hour STRING
   );

3. Upload data.

   Store your raw business data in a DataWorks table. In this example, the local file banking.txt is uploaded to the bank_data table in DataWorks to simulate a real-world data write scenario. In the data import wizard, first select the target table bank_data, confirm that the table schema includes fields such as age, job, marital, education, default, and housing, and then set the partition parameters. Then, upload the local file banking.txt, set the file format to CSV, the delimiter to a comma, the character set to GBK, and the import start row to 1, and select the 'First row is header' checkbox. Finally, select Match by Name as the field mapping method, confirm that the mapping between the source and target fields is correct, and then click Import Data to complete the operation. For more information about uploading data, see Upload local data to the bank_data table.

Step 3: Create a node

1. Create a node.

   You can create a node in one of the following two ways:

   • Method 1: Create from the navigation tree.

     1. In the navigation tree, find the workflow you created in Step 1 under Workflow.

     2. Right-click the desired engine and select an appropriate node under Create Node.

   • Method 2: Create from the workflow panel.

     1. In the navigation tree, find the workflow you created in Step 1 under Workflow.

     2. Double-click the workflow to open its panel.

     3. In the left-side navigation pane of the panel, click the desired node or drag it onto the canvas.

2. Configure the node's engine instance, name, and other settings.

   This tutorial creates an ODPS SQL node named result_table, which is the same as the name of the result table created in Step 2.

   Note

   When you use DataWorks nodes for data development, you cleanse data with a development node and store the results in a result table. Name the node after the result table it populates to make it easier to locate the table data produced by the node.

   In the Common Nodes section of the Data Studio panel, drag the ODPS SQL node onto the canvas on the right to create it.

Step 4: Edit the node

Find the node you created in Step 3 in the workflow navigation tree or on the workflow panel. Double-click the node to open the editor. Based on the node type, write your business logic using the syntax of the corresponding database.

--@exclude_output=xc_DPE_E2.result_table
--@exclude_input=bank_data
--odps sql
--***********************************************************--
--author:xxx
--create time:2022-08-11 14:33:23
--***********************************************************--
INSERT OVERWRITE TABLE result_table partition (day='${day}', hour='${hour}')
SELECT education
    , COUNT(marital) AS num
FROM bank_data
WHERE  day='${day}' and hour='${hour}'
GROUP BY education;

INSERT OVERWRITE TABLE result_table partition (day='${day}', hour='${hour}')
SELECT education
, COUNT(marital) AS num
FROM bank_data
GROUP BY education;

Step 5: Configure schedule settings

Step 6: Debug the code

Step 7: Save and submit the node

1. Click the icon on the toolbar to save the node configuration.

2. Click the icon on the toolbar to submit the node to the development environment.

Step 8: Run smoke testing

Step 9: Deploy the task

In a standard mode workspace, any changes you submit in Data Studio, such as creating, updating, or deleting nodes, resources, and functions, are sent to the task deployment page to await deployment. To apply these changes, you must navigate to Task Deployment > Create Deploy Task and create a deployment package to release them to the production environment. The changes become active only after a successful deployment. For more information, see Deploy tasks.