Simple Log Service:Ship logs to MaxCompute (legacy)

Limitations

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

• Do not ship data from different Logstores to the same MaxCompute table. In rare cases, this can cause partition data in the table to be overwritten.

• Logs older than 14 days cannot be shipped. The age of a log is determined by its__time__ reserved field. These logs are automatically discarded during the shipping 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 Data types (Version 2.0).

• The following table lists the supported regions. If your Simple Log Service project is in a region not listed below, use DataWorks for data synchronization. For more information, see Ship data from Simple Log Service by using Data Integration.

  Simple Log Service project region	MaxCompute project region
  China (Qingdao)	China (Shanghai)
  China (Beijing)	China (Beijing), China (Shanghai)
  China (Zhangjiakou)	China (Shanghai)
  China (Hohhot)	China (Shanghai)
  China (Hangzhou)	China (Shanghai)
  China (Shanghai)	China (Shanghai)
  China (Shenzhen)	China (Shenzhen), 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 > Logstores tab, click the > icon next to the Logstore that you want to manage, and then choose Data Transformation > Export > MaxCompute (Formerly ODPS).

4. Click Enable Shipping.

5. In the Shipping notes dialog box, click Ship Directly.

6. On the Ship data to MaxCompute page, configure the shipping rule and click OK.

   The following table describes the key parameters.

   Parameter	Description
   Region	The regions where you can create MaxCompute projects vary based on the region of your Simple Log Service project. For more information, see Limitations.
   Job name	The name of the data shipping job.
   MaxCompute project	The name of the MaxCompute project.
   MaxCompute table	The name of the MaxCompute table.
   Column mapping	In the left text box, enter the name of a log field to map to a MaxCompute table column. In the right text box, enter the name of the corresponding MaxCompute table column. For more information, see Data model mapping. Important Simple Log Service ships logs to MaxCompute by mapping log fields to MaxCompute table columns in order. Modifying column names in the MaxCompute table does not affect data shipping. If you change the schema of the MaxCompute table, you must reconfigure the mappings between log fields and table columns. The log field name in the left text box cannot contain double quotation marks (""), single quotation marks (''), or spaces. If a log contains two fields that have the same name, such as request_time, Log Service displays one of the fields as request_time_0. The two fields are still stored as request_time in Log Service. When you configure a shipping rule, you can use only the original field name request_time. If a log contains fields that have the same name, Log Service randomly ships the value of one of the fields. We recommend that you do not include fields that have the same name in your logs.
   Partition key mapping	In the left text box, enter the name of a log field to map to a MaxCompute partition key column. In the right text box, enter the name of the corresponding partition key column in the MaxCompute table. For more information, see Data model mapping. Note You can configure up to three partition key columns. Be cautious when using custom fields for partition keys. Each data shipping job must generate fewer than 512 partitions. Otherwise, the job fails to write data to the MaxCompute table, and the entire batch of data cannot be shipped.
   Time 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 with second-level precision, as this can easily cause the number of partitions in a table to exceed the 60,000 limit. The number of data partitions in a single data shipping job must be less than 512.
   Import interval	The interval, in seconds, for creating new data shipping jobs. The default value is 1800. When this interval elapses, a new data shipping job is automatically created.

   After enabling data shipping, logs are typically imported into MaxCompute within one hour of being written to the Logstore. After the import is successful, you can view the log data in MaxCompute. For more information, see How do I check the data integrity after I ship logs 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 permissions to Simple Log Service

If you delete and then re-create a table in DataWorks, the authorization for Simple Log Service is revoked. You must manually grant the required permissions again.

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, hover over the Shortcuts section for the target workspace, 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 the Workflow Name parameter 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 the Name and Path parameters, and then click Commit.

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

7. In the node editor, 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 fixed account for Simple Log Service and cannot be changed.
   GRANT Read, List ON PROJECT {ODPS_PROJECT_NAME} TO USER aliyun$shennong_open@aliyun.com;	Grants the user the permissions to read and list resources in the MaxCompute project. {ODPS_PROJECT_NAME} is the name of the MaxCompute project. Replace this placeholder with the actual project name.
   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. {ODPS_TABLE_NAME} is the name of the MaxCompute table. Replace this placeholder with the actual table name.
   SHOW GRANTS FOR aliyun$shennong_open@aliyun.com;	Verifies whether the authorization is successful. If the command returns the following output, the authorization is successful. A projects/{ODPS_PROJECT_NAME}: List | Read A projects/{ODPS_PROJECT_NAME}/tables/{ODPS_TABLE_NAME}: Describe | Alter | Update

Related operations

References

• __partition_time__

  A common method for filtering data in MaxCompute is to use the log time as a partition field, which allows you to filter data by time.

  • Format

    The value of the__partition_time__ field is calculated based on the value of the__time__ field in Simple Log Service and is rounded down based on the partition time format. The value of a date partition key column is aligned based on the shipping interval to prevent the number of partitions in a single table from exceeding the MaxCompute limit.

    For example, if the log time in Simple Log Service is27/Jan/2016 20:50:13 +0800, Simple Log Service calculates the value of the__time__ reserved field as the UNIX timestamp1453899013. The following table describes the values of the time partition key column for different configurations.

    Shipping interval	Partition time 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 the__partition_time__ field to filter data, which helps avoid a full-table scan. For example, to query log data for January 26, 2016, you can use the following query statement.

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

• __extract_others__

  __extract_others__ is a JSON string. For example, to get the user-agent content from this field, you can use 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 request permissions to use this standard UDF. For more information, see MaxCompute-provided standard UDFs.

  • The example is for reference only. The official MaxCompute documentation takes precedence.

• Data model mapping

  When you ship logs from Simple Log Service to MaxCompute, a data model mapping between the two services is involved. The following section provides notes and examples.

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

  • We recommend that you use the following reserved fields of Simple Log Service:__partition_time__,__source__, and__topic__.

  • The maximum number of partitions in a MaxCompute table is 60,000. If this limit is exceeded, no more data can be written to the table.

  • Data shipping jobs are run in batches. Use caution when you set partition key columns and their types. Make sure that the number of data partitions processed in a single data shipping job is less than 512. Otherwise, the entire batch of data cannot be written to MaxCompute.

  • The system reserved field__extract_others__ has a legacy name_extract_others_ that is also supported.

  • The value of a MaxCompute partition key column cannot be a reserved word or keyword of MaxCompute. For more information, see Reserved words and keywords.

  • The value of a MaxCompute partition key column cannot be empty. Therefore, the fields that are mapped to partition key columns must be reserved fields or log fields. You must be able to use the CAST operator to convert the value of a string-type field to the data type of the corresponding partition key column. Logs that would result in an empty partition key value are discarded during shipping.

  • In Simple Log Service, a log field can be mapped to only one MaxCompute table column (a data column or a partition key column). If you map the same log field to a second column, its value in that second column will be null. If the null value appears in a partition key column, the data cannot be shipped.

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

  MaxCompute column type	MaxCompute column	MaxCompute type	SLS field	SLS field type	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 is the number of seconds that have elapsed since 00:00:00 UTC on January 1, 1970. This field corresponds to the Time field in the data model.
  	log_topic	string	__topic__	Reserved field	The topic of the log.
  	time	string	time	Log content field	For data collected by Logtail, the value of the content field time is often identical to the value of the reserved field __time__.
  	ip	string	ip	Log content field	This field is parsed from the log content.
  	thread	string	thread	Log content field	This field is parsed from the log content.
  	log_extract_others	string	__extract_others__	Reserved field	Other log fields that are not mapped in the configuration are serialized into a JSON string of key-value pairs. This JSON string has a single-level structure and does not support nested JSON objects within its fields.
  Partition key column	log_partition_time	string	__partition_time__	Reserved field	This field is calculated by aligning the value of the__time__ field of the log. The partition granularity is configurable.
  	status	string	status	Log content field	This field is parsed from the log content. The value of this field supports enumeration to ensure that the number of partitions does not exceed the upper limit.