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

Prerequisites

Benefits

If you associate 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. Log on to the Log Service console.
  2. In the Projects section, click the name of the project that you want to view.
  3. Choose Log Storage > Logstores. On the Logstores tab, click the Logstore that you want to view.
  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 named user_meta1 and map the table to the OSS object user.csv. If result in the output is true, the SQL statement is successfully executed, and an external store is created.
    * | create table user_meta1 ( userid bigint, nick varchar, gender varchar, province varchar, gender varchar,age bigint) with ( endpoint='oss-cn-hangzhou-internal.aliyuncs.com',accessid='**************************',accesskey ='****************************',bucket='testoss*********',objects=ARRAY['user.csv'],type='oss')
    External store
    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
    External store name The name of the external store, which is the same as the name of the virtual external table. In this example, enter user_meta1.
    Table schema The properties of the virtual external table, including the column names and data types. In this example, enter (userid bigint, nick varchar, gender varchar, province varchar, gender varchar,age bigint).
    endpoint The internal endpoint of OSS. For more information, see Regions and endpoints.
    accessid The AccessKey ID of your account. For more information, see AccessKey pair.
    accesskey The AccessKey secret of your account. For more information, see AccessKey pair.
    bucket The OSS bucket in which the CSV object is stored.
    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.
    type The type of the external store. Set the value to oss.
  5. 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.
    * | select * from user_meta1
    Check result
  6. Perform a JOIN query on 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. 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 requirements.

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

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