Document-DataWorks(DataWorks)-阿里云帮助中心

This topic describes how to use DataWorks Data Integration to migrate JSON data from OSS to MaxCompute and then parse the data using the GET_JSON_OBJECT string function in MaxCompute.

Prerequisites

MaxCompute is activated.
A DataWorks workflow has been created. This example uses a workspace in basic mode. For more information, see Create a workflow.

A JSON file has been renamed with the.txt extension and uploaded to OSS. In this example, the OSS bucket is in the China (Shanghai) region. The following is the sample file content.

{
    "store": {
        "book": [
             {
                "category": "reference",
                "author": "Nigel Rees",
                "title": "Sayings of the Century",
                "price": 8.95
             },
             {
                "category": "fiction",
                "author": "Evelyn Waugh",
                "title": "Sword of Honour",
                "price": 12.99
             },
             {
                 "category": "fiction",
                 "author": "J. R. R. Tolkien",
                 "title": "The Lord of the Rings",
                 "isbn": "0-395-19395-8",
                 "price": 22.99
             }
          ],
          "bicycle": {
              "color": "red",
              "price": 19.95
          }
    },
    "expensive": 10
}

Migrate JSON from OSS to MaxCompute

Add an OSS data source. For more information, see Configure an OSS data source.
Create a table in DataWorks to store the migrated JSON data.
1. Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Development and O&M > Data Development. On the page that appears, select the desired workspace from the drop-down list and click Go to Data Development.
2. Click Data Source to go to the Data Source page, then click New data source to add a MaxCompute project.
3. Click Add Data Source and Bind to Data Development to complete the binding.
4. On the Data Development page, move the pointer over the icon and choose Create Table > Table.
5. In the Create Table dialog box, select a Path, enter a Name, and click Create .
  
  Note
  If multiple engine instances are bound, you must select a MaxCompute engine instance.
6. On the table editing page, click DDL Statement.
7. In the DDL mode dialog box, enter the following statement and click Generate Table Schema.
```
create table mqdata (mq_data string);
```
8. In the Confirm dialog box, click Confirm.
9. After the table schema is generated, enter a Display Name for the table in the General section. Then, click Commit to Development Environment and Commit to Production Environment.
  
  Note
  If you use a workspace in basic mode, click only Commit to Production Environment.

Create an offline synchronization node.

Go to the data analytics page. Right-click the specified workflow and choose Create Node > Data Integration > Offline synchronization.
In Create Node dialog box, enter Name, and click Confirm.
In the top navigation bar, choose icon.
In script mode, click icon.
In import Template dialog box SOURCE type, data source, target type and data source, and click confirm.

Modify the JSON code and click the button.

Sample code:

{
    "type": "job",
    "steps": [
        {
            "stepType": "oss",
            "parameter": {
                "fieldDelimiterOrigin": "^",
                "nullFormat": "",
                "compress": "",
                "datasource": "OSS_userlog",
                "column": [
                    {
                        "name": 0,
                        "type": "string",
                        "index": 0
                    }
                ],
                "skipHeader": "false",
                "encoding": "UTF-8",
                "fieldDelimiter": "^",
                "fileFormat": "binary",
                "object": [
                    "applog.txt"
                ]
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "odps",
            "parameter": {
                "partition": "",
                "isCompress": false,
                "truncate": true,
                "datasource": "odps_first",
                "column": [
                    "mqdata"
                ],
                "emptyAsNull": false,
                "table": "mqdata"
            },
            "name": "Writer",
            "category": "writer"
        }
    ],
    "version": "2.0",
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    },
    "setting": {
        "errorLimit": {
            "record": ""
        },
        "speed": {
            "concurrent": 2,
            "throttle": false
        }
    }
}

Verify the results

Create an ODPS SQL node.

Right-click the workflow and choose new > MaxCompute > ODPS SQL.
In create a function dialog box, enter function name, click submit.

In the ODPS SQL node editor, enter the following statements.

-- Query data from the mqdata table.
SELECT * from mqdata;
-- Extract the 'expensive' value from the JSON data.
SELECT GET_JSON_OBJECT(mqdata.MQdata,'$.expensive') FROM mqdata;

Click icon to run the code.
You can operation Log view the results.