All Products
Search
Document Center

Simple Log Service:Associate a Logstore with an OSS external table to perform query and analysis

Last Updated:Feb 18, 2024

In some scenarios, you may need to use external tables to query and analyze logs. This topic describes how to associate a Logstore with an Object Storage Service (OSS) external table to perform query and analysis.

Prerequisites

Note

You can associate only one OSS object whose maximum size can be 10 MB. The object cannot be compressed. You cannot specify regular expressions for the related parameters.

Background information

Company A is an electronic payment company. Company A wants to analyze the impact of user ages, geographic locations, and genders on payment preferences. Company A has collected payment behavior logs by using Simple Log Service and stored user property information in an OSS bucket. The payment behavior logs contain bills and payment methods. The user property information includes the geographic location, age, and gender information about a user. The query and analysis engine of Simple Log Service allows you to associate Logstores with external stores to perform query and analysis. External stores include MySQL databases and OSS buckets. To analyze the metrics that are related to user properties, you can use the SQL JOIN syntax to associate the payment behavior logs with the user property information.

If you associate Simple Log Service with OSS buckets to perform query and analysis, you can receive 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. Create a CSV file and upload it to the OSS bucket.

    1. Create a file named user.csv.

      userid,nick,gender,province,age
      1,User A,male,Shanghai,18
      2,User B,female,Zhejiang,19
      3,User C,male,Guangdong,18
    2. Upload the user.csv file to the OSS bucket. For more information, see Upload objects.

  2. Log on to the Simple Log Service console.

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

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

  5. On the page that appears, enter a query statement in the search box and select a query time range.

    Execute the following SQL statement to create a virtual external table named user_meta1 and map the table to the OSS object user.csv. If result in the output is true, the SQL statement is executed, and an external store is created.

    * | 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='testoss',objects=ARRAY['user.csv'],type='oss')

    外部存储

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

    Parameter

    Description

    Example

    External store name

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

    user_meta1

    Table schema

    The properties of the virtual external table, including the column names and data types. Example: (userid bigint, nick varchar, gender varchar, province varchar, age bigint).

    (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.aliyuncs.com

    accessid

    The AccessKey ID that is used to access the OSS bucket. For more information, see AccessKey pair.

    LT****7V

    accesskey

    The AccessKey secret that is used to access the OSS bucket. For more information, see AccessKey pair.

    Y4****ZR

    bucket

    The OSS bucket in which the CSV object is stored.

    testoss

    objects

    The path of the CSV object.

    Note

    The value of the objects parameter is an array. The array can contain only one element. An element represents an OSS object.

    user.csv

    type

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

    oss

  6. Check whether the external store is created.

    Execute the following statement. If the table content that you define is returned, the external store is created. user_meta1 is the name of the external store. Configure this parameter based on your business scenario.

    * | select * from user_meta1

    验证结果

  7. Perform a JOIN query on Simple Log Service and OSS.

    Execute the following statement to perform a JOIN query. A Logstore is associated with an OSS object based on the ID field in the Logstore and the userid field in the OSS object. test_accesslog is the name of the Logstore. l is the alias of the Logstore. user_meta1 is the name of the external store that you define. You can configure the parameters based on your business scenario.

    * | select * from test_accesslog l join user_meta1 u on l.userid = u.userid

    Examples:

    • Analyze the access requests from users of different genders.

      * | select u.gender, count(1) from test_accesslog l join user_meta1 u on l.userid = u.userid group by u.gender

      性别访问

    • Analyze the access requests from users of different ages.

      * | select u.age, count(1) from test_accesslog l join user_meta1 u on l.userid = u.userid group by u.age

      年龄访问

    • Analyze the access trends of users of different ages in different time ranges.

      * | select date_trunc('minute',__time__) as minute, count(1) ,u.age from test_accesslog l join user_meta1 u on l.userid = u.userid group by u.age,minute

      时间访问