Converting JSON-formatted Log Data with MaxCompute Built-in Functions and UDTF

Join us at the Alibaba Cloud ACtivate Online Conference on March 5–6 to challenge assumptions, exchange ideas, and explore what is possible through digital transformation.

Analysis of Business Scenarios

Due to the complexity of the business, data developers need to handle different types of data from different sources. They need to extract this data to the data platform and extract key business fields according to the designed data model, to form a two-dimensional table for subsequent statistical analysis and contextual computing in the big data platform or data warehouse.

This article introduces how to use MaxCompute to convert JSON-formatted log data with a specific case.

  1. Data Source: The application writes to the log file in real time under the specified ECS host directory.
  2. Data Formats: In log files, the format of each log is shown in the following figure (the data is simplified and masked in the example). Each log contains the device information, as well as the information for 1 or more sessions, and the number of sessions in each log is dynamic: 1 or more sessions. An example of the contents of a log is as follows:
Image for post
Image for post

Data Processing Requirements: These requirements are to collect log data, resolve and convert the log data, and perform statistical analysis on the converted log data in MaxCompute. The log data is in JSON format, so it contains information of multiple business fields. The business fields need to be extracted before subsequent business statistics can be carried out in MaxCompute (for example, PV/UV statistics by time period, statistics by device type, and statistics by associating device ID with membership information). Therefore, the key requirement of this article is how to resolve the key information of JSON-formatted data into a two-dimensional table containing the business fields.

Recommended Solution

In the solution presented in this article, the Alibaba Cloud’s Log Service + MaxCompute product portfolio is selected to meet the business requirements above. The log service only performs the functions of log collection and delivery, and does not perform data resolution and conversion.

Log Collection

Log data is obtained from the log service to logstore (for this part, see the Log Service Help Document)s

Image for post
Image for post

Using the delivery function of Log Service, the log is regularly delivered and archived to one raw log table of MaxCompute, in which all information of each log is written into the “content” field of the raw log table.

Image for post
Image for post

Use MaxCompute to perform field resolution and extraction of the raw data.

Use the built-in function get_json_object to extract data

The extraction results are as follows:

Image for post
Image for post

Summary of the Solution:

The processing logic above extracts the business fields of a log into row fields respectively, which is suitable for JSON records that have fixed information and can be mapped into table fields. For example, in the example above, after extracting the information of session1 and session2, they are treated as different column fields respectively. However, if the number of sessions contained in each log record is dynamic rather than fixed, this processing logic has difficulty meeting the requirements. For example, the next log contains 3 sessions. If the information of each session is to be extracted, the resolved SQL is required to add Session3_ID and Session3_EventName logic. What if the next log contains 100 sessions? This extraction method has difficulty in dealing with this situation.

Image for post
Image for post

In this case, the UDTF custom function can be used.

Develop MaxCompute UDTF Function to Process Logs

According to the characteristics of the data, 1 log contains multiple sessions, which has a 1:N relationship. When the log is converted into a two-dimensional table of the data warehouse, the session information needs to be resolved to the minimum granularity, and 1 row needs to be converted into N rows to extract all session information. The business objectives are as follows:

Image for post
Image for post

In MaxCompute, UDTF is needed to convert a row of records into multiple rows of records.

Taking JAVA UDTF as an example, each JSON record in the “content” field is resolved to obtain and return the business field to be extracted The UDTF processing logic here goes as far as JSON level 3, cyclically resolving the data with the smallest granularity and returning multiple records.

Note: For the details of writing, packaging and uploading, and creating functions for UDF, see the official documentation

After the program is written, you need to package UDTF, upload it and create a UDF function:

Package the compiled program, generate a jar package, and upload this resource in the MaxCompute client (odpscmd):

Then, create a function from the command line:

View the function after creation:

Image for post

Test and verification:

Query the table containing the raw log and query the “content” field with the created get_json_udtf:

Image for post
Image for post

The query results are as follows: UDFT function processes each JSON record and generates multiple records as expected:

Image for post
Image for post

And if the processing logic needs to be fixed, you can also use the “insert into” syntax to query the resolved results to a new table, and realize periodic data conversion through task scheduling.

Summary

Based on a big data analysis scenario of log analysis, and taking a common JSON log processing requirement as an example, this article introduces how to collect logs for MaxCompute through the log service, and how to use MaxCompute built-in functions and UDF to resolve and convert JSON-formatted log data, extract key business fields and generate log tables that can be used for subsequent analysis.

Reference:https://www.alibabacloud.com/blog/converting-json-formatted-log-data-with-maxcompute-built-in-functions-and-udtf_594533?spm=a2c41.12636363.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store