All Products
Search
Document Center

Simple Log Service:Associate Simple Log Service with an OSS bucket

Last Updated:Nov 14, 2023

This topic describes how to create an external store to associate Simple Log Service with an Object Storage Service (OSS) bucket.

Prerequisites

Benefits

The external storage feature that is used to associate Simple Log Service with OSS buckets provides the following benefits:

  • Cost-effectiveness: If you store infrequently updated data in OSS buckets, the data can be read over an internal network. In this case, you need only to pay for the storage service, and you are not charged for Internet traffic.

  • Reduced O&M workload: You can perform lightweight association analysis without the need to store all data in one storage system.

  • High efficiency: You can use SQL statements to analyze data and view the analysis results within seconds. You can also create charts based on analysis results that are commonly queried. Then, you can click the charts to view the analysis results.

Procedure

  1. Log on to the Simple Log Service console.

  2. In the Projects section, click the project that you want to manage.

  3. On the Log Storage > Logstores tab, click the Logstore that you want to manage.

  4. On the page that appears, enter a query statement in the search box and click Search & Analyze.

    Execute the following SQL statement to create a virtual external table and map the table to specific OSS objects:

    * | create table <External storage> (<Schema>) with (endpoint='oss-cn-${your_region_name}-internal.aliyuncs.com',accessid='<your accessid>',accesskey ='<your accesskey>',bucket='<your bucket name>',objects=ARRAY['*.csv'],type='oss')

    Define the name and table schema of the external store in the SQL statement, and define the information required to access OSS objects in the WITH clause. The following table describes the parameters.

    Parameter

    Description

    Example

    External storage

    The name of the external store. The name is the same as the name of the virtual external table.

    user_meta1

    Schema

    The properties of the virtual external table, including the column names and data types.

    (userid bigint, nick varchar, gender varchar, province varchar, age bigint)

    endpoint

    The internal endpoint of OSS. For more information, see Regions and endpoints.

    oss-cn-hangzhou-internal.aliyuncs.com

    accessid

    The AccessKey ID and AccessKey secret of your Alibaba Cloud account. We recommend that you use the AccessKey pair of a RAM user that is granted the write permissions on the Simple Log Service project. For information about how to grant a RAM user the write permissions on a project, see Use custom policies to grant permissions to a RAM user. For information about how to obtain an AccessKey pair, see AccessKey pair.

    LT****7V

    accesskey

    Y4****ZR

    bucket

    The OSS bucket in which the CSV object is stored.

    examplebucket

    objects

    The path of the CSV object.

    Note

    The value of the objects parameter is an array. The array can contain multiple elements. Each element represents an OSS object.

    user.csv

    type

    The type of the external store. Set the value to oss.

    oss

    Sample SQL statement:

    * | create table user_meta1 ( userid bigint, nick varchar, gender varchar, province varchar, age bigint) with ( endpoint='oss-cn-hangzhou.aliyuncs.com',accessid='LTAI5t8y9c113M7V****',accesskey='Y45H7bqvvgapWZR****',bucket='examplebucket',objects=ARRAY['user.csv'],type='oss')

    If result in the execution result is true, the SQL statement is successfully executed.

    外部存储
  5. Check whether the external store is created.

    Execute the following statement. In this example, the External storage parameter is set to the name of the external store that you defined in Step 4. If the table content that you defined is returned, the external store is created.

    * | select * from <External storage>
  6. Perform a JOIN query on Simple Log Service and OSS.

    Execute the following statement to perform a JOIN query. A Logstore is associated with OSS objects based on the ID field in the Logstore and the userid field in the OSS objects.

    Note

    The collected logs must contain the userid field.

    * | select * from <logstore> l join <External storage> u on l.userid = u.userid

    Parameter

    Description

    Example

    Logstore

    The name of the Logstore to which the logs belong.

    test_logstore

    External storage

    The name of the external store that you defined in Step 4.

    user_meta1

    联合查询

For more information about the best practices for associating Simple Log Service with OSS buckets, see Associate a Logstore with an OSS external table to perform query and analysis.