Ship logs to MaxCompute (legacy)

Limitations

• Only an Alibaba Cloud account can create data shipping jobs. RAM users cannot create data shipping jobs.

• Do not ship data from different Logstores to the same MaxCompute table. In rare cases, this may overwrite partition data in the MaxCompute table.

• Data with a log time (corresponding to the reserved field time) older than 14 days is not supported for shipping and is automatically discarded during the process.

• When you ship logs from Simple Log Service to MaxCompute, the DECIMAL, DATETIME, DATE, and TIMESTAMP data types are not supported. For more information, see 2.0 data type versions.

• This feature is supported only in some regions. In other regions, use DataWorks to synchronize data. For more information, see Ship data from Simple Log Service by using data views.

  SLS project region	MaxCompute project region
  China (Qingdao)	China (Shanghai)
  China (Beijing)	China (Beijing) and China (Shanghai)
  China (Zhangjiakou)	China (Shanghai)
  China (Hohhot)	China (Shanghai)
  China (Hangzhou)	China (Shanghai)
  China (Shanghai)	China (Shanghai)
  China (Shenzhen)	China (Shenzhen) and China (Shanghai)
  China (Hong Kong)	China (Shanghai)

Step 1: Create a data shipping job

1. Log on to the Simple Log Service console.

2. In the Projects section, click the one you want.

   

3. On the Log Storage > Logstore tab, find the target Logstore, click the > icon, and then choose Data Processing > Export > MaxCompute.

4. Click Enable.

5. In the Shipping Note dialog box, click Ship.

6. On the Data Shipping to MaxCompute page, configure the parameters for the data shipping job and click OK.

   The following table describes the key parameters.

   Parameter	Description
   Select Region	The supported MaxCompute regions vary based on the region of your Simple Log Service project. For more information, see Limitations.
   Shipping Job Name	The name of the data shipping job.
   MaxCompute Project Name	The name of the MaxCompute project.
   Table Name	The name of the MaxCompute table.
   MaxCompute Common Column	In the left text box, enter the log field name that you want to map to a column in the MaxCompute table. The name of the corresponding MaxCompute table column is displayed on the right. For more information, see Data model mapping. Important Simple Log Service ships logs to MaxCompute by mapping fields to columns based on their order. Modifying the names of MaxCompute columns does not affect data shipping. If you change the schema of the MaxCompute table, you must reconfigure the mappings. The log field name in the left text box cannot contain double quotation marks (""), single quotation marks (''), or spaces. If your logs contain duplicate field names (for example, two fields named request_time), Log Service may display one as request_time_0 in the console. The underlying stored name remains request_time. Use the original field name request_time when configuring the shipping rule. With duplicate field names, the system randomly ships only one value. Avoid duplicate field names for predictable results.
   MaxCompute Partition Key Column	In the left text box, enter the log field name that you want to map to a partition column in the MaxCompute table. The name of the corresponding MaxCompute partition column is displayed on the right. For more information, see Data model mapping. Note You can configure a maximum of three partition columns. Select custom fields for partition columns with caution. Make sure that each data shipping job generates fewer than 512 partitions. Otherwise, the data shipping job fails to write data to the MaxCompute table, and the system cannot ship the entire batch of data.
   Partition Format	The format of the time partition. For configuration examples and parameter details, see Examples and Java SimpleDateFormat. Note The time partition format takes effect only when the Partition Field is set to __partition_time__. Do not use a date format that is accurate to the second. Otherwise, the number of partitions in a single table may exceed the upper limit (60,000). The number of data partitions in a single data shipping job cannot exceed 512.
   Shipping Interval	The duration of a data shipping job. The default value is 1800. Unit: seconds. When the duration of a data shipping job reaches the specified value, the system automatically creates a new data shipping job.

   After you enable data shipping, log data typically arrives in MaxCompute within one hour of being written to the Logstore. After the data is shipped, you can view the data in MaxCompute. For more information, see How do I check the completeness of data that is shipped from Simple Log Service to MaxCompute?.

Step 2: View data in MaxCompute

| log_source | log_time | log_topic | time | ip | thread | log_extract_others | log_partition_time | status |
+------------+------------+-----------+-----------+-----------+-----------+------------------+--------------------+-----------+
| 10.10.*.* | 1453899013 | | 27/Jan/2016:20:50:13 +0800 | 10.10.*.* | 414579208 | {"url":"POST /PutData?Category=YunOsAccountOpLog&AccessKeyId=****************&Date=Fri%2C%2028%20Jun%202013%2006%3A53%3A30%20GMT&Topic=raw&Signature=******************************** HTTP/1.1","user-agent":"aliyun-sdk-java"} | 2016_01_27_20_50 | 200 |
+------------+------------+-----------+-----------+-----------+-----------+------------------+--------------------+-----------+

Grant data shipping permissions

If you delete and then recreate a table in DataWorks, the default authorization becomes invalid. You must manually re-grant the shipping permissions to Simple Log Service.

1. Log on to the DataWorks console.

2. In the upper-left corner of the page, select a region.

3. In the left-side navigation pane, click Workspaces.

4. On the Workspaces page, find the target workspace, hover over Shortcuts, and then click Data Development.

5. Create a workflow.

   1. On the Data Development page, choose Create > Create Workflow.

   2. In the Create Workflow dialog box, configure Service Name and click Create.

6. Create a node.

   1. On the Data Development page, choose Create > Create Node > ODPS SQL.

   2. In the Create Node dialog box, configure Name and Path, and then click Submit.

      Set Path to the workflow that you created in step 5.

7. In the editor of the created node, run the following commands to grant the required permissions.

   Command	Description
   ADD USER aliyun$shennong_open@aliyun.com;	Adds a user to the MaxCompute project. shennong_open@aliyun.com is the Simple Log Service account (a fixed value that cannot be modified).
   GRANT Read, List ON PROJECT {ODPS_PROJECT_NAME} TO USER aliyun$shennong_open@aliyun.com;	Grants the user the read and list permissions on the MaxCompute project. In this parameter, {ODPS_PROJECT_NAME} is the MaxCompute project name and must be replaced with an actual value.
   GRANT Describe, Alter, Update ON TABLE {ODPS_TABLE_NAME} TO USER aliyun$shennong_open@aliyun.com;	Grants the user the Describe, Alter, and Update permissions. Where {ODPS_TABLE_NAME} is the name of the MaxCompute table. Replace the placeholder with an actual value.
   SHOW GRANTS FOR aliyun$shennong_open@aliyun.com;	Verifies whether the permissions are granted. If the command returns output similar to the following, the permissions are granted. A projects/{ODPS_PROJECT_NAME}: List | Read A projects/{{ODPS_PROJECT_NAME}/tables/{ODPS_TABLE_NAME}: Describe | Alter | Update

Related operations

References

• __partition_time__

  Using the log time as a partition field to filter data is a common practice in MaxCompute.

  • Format

    The partition_time is calculated based on the value of the time field in Simple Log Service and is rounded down based on the partition time format. To avoid exceeding the maximum number of partitions allowed for a single MaxCompute table, the value of the date partition column is aligned based on the shipping interval.

    For example, if the log time in Simple Log Service is 27/Jan/2016 20:50:13 +0800, Simple Log Service calculates the value of the reserved field time as the UNIX timestamp 1453899013. The following table shows the values of the time partition column for different configurations.

    Shipping interval	Time partition format	__partition_time__
    1800	yyyy_MM_dd_HH_mm_00	2016_01_27_20_30_00
    1800	yyyy-MM-dd HH:mm	2016-01-27 20:30
    1800	yyyyMMdd	20160127
    3600	yyyyMMddHHmm	201601272000
    3600	yyyy_MM_dd_HH	2016_01_27_20

  • Usage

    You can use partition_time to filter data and avoid a full table scan. For example, to query log data for January 26, 2016, the query statement is as follows.

    select * from {ODPS_TABLE_NAME} where log_partition_time >= "2015_01_26" and log_partition_time < "2016_01_27";

• __extract_others__

  The __extract_others__ field contains a JSON string. For example, to obtain the user-agent value, run the following query statement:

  select get_json_object(sls_extract_others, "$.user-agent") from {ODPS_TABLE_NAME} limit 10;

  Note

  • get_json_object is a standard UDF provided by MaxCompute. Contact the MaxCompute team to obtain the permissions to use this standard UDF. For more information, see Standard UDFs provided by MaxCompute.

  • This example is for reference only. Follow the recommendations from MaxCompute.

• Data model mapping

  When you ship logs from Simple Log Service to MaxCompute, data model mapping is involved. The following section describes related limits and examples.

  • A MaxCompute table must contain at least one data column and one partition column.

  • The recommended reserved fields for Simple Log Service are partition_time, source, and topic.

  • The maximum number of partitions in a MaxCompute table is 60,000. If the number of partitions exceeds this limit, you can no longer write data to the table.

  • Data shipping jobs are run in batches. Configure partition columns and their types with caution to ensure that a single data shipping job processes fewer than 512 data partitions. Otherwise, the system cannot write the entire batch of data to MaxCompute.

  • The system reserved field extract_others has a former name, extract_others, and they can be used interchangeably.

  • Do not set MaxCompute partition column values to MaxCompute reserved words or keywords. For more information, see Reserved words and keywords.

  • MaxCompute partition column values cannot be empty. Therefore, the field mapped to a partition column must be a reserved field or a log field whose value can be converted from string to the partition column type by using the cast operator. The shipping process discards logs that have empty partition column values.

  • In Simple Log Service, a log field can be mapped to only one column (a data column or a partition column) in a MaxCompute table. Simple Log Service does not support field redundancy. If you use the same field name a second time, the shipped value is null. If a null value occurs in a partition column, the system cannot ship the data.

  The following table provides an example of the mappings between the data columns and partition columns of a MaxCompute table and the fields of Simple Log Service. For more information about the reserved fields of Simple Log Service, see Reserved fields.

  Column category	MaxCompute column name	Type (MaxCompute)	SLS field name	Type (SLS)	Description
  Data column	log_source	string	__source__	Reserved field	The source of the log.
  	log_time	bigint	__time__	Reserved field	The UNIX timestamp of the log, which indicates the number of seconds that have elapsed since 00:00:00 on January 1, 1970. This field corresponds to the Time domain in the data model.
  	log_topic	string	__topic__	Reserved field	The topic of the log.
  	time	string	time	Log content field	This field is parsed from the log and corresponds to a key-value pair in the data model. In many cases, the value of __time__ for data collected by Logtail is the same as the value of time.
  	ip	string	ip	Log content field	This field is parsed from the log.
  	thread	string	thread	Log content field	This field is parsed from the log.
  	log_extract_others	string	__extract_others__	Reserved field	The system serializes other unmapped log fields into a single-level JSON string based on key-value pairs. Nested JSON is not supported.
  Partition column	log_partition_time	string	__partition_time__	Reserved field	The system calculates and aligns this field based on the log's __time__ field. The partition granularity is configurable.
  	status	string	status	Log content field	This field's values must be enumerable to ensure the number of partitions does not exceed the upper limit.