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

Prerequisites

Benefits

  • Lower storage cost

    You can store infrequently updated data in OSS buckets. This reduces your storage cost. If you store the data in an ApsaraDB RDS for MySQL instance, you must pay for the instance.

  • Lower traffic cost

    You can store data in OSS buckets and read the data over the internal network.

Procedure

  1. Log on to the Log Service console.
  2. Click the project in the Projects section.
  3. Choose Log Management > Logstores. On the Logstores tab, click the management icon of the Logstore.
  4. Enter the following query statement in the search box and click Search & Analytics.
    Create an external store named user_meta1 and map the data in the store to objects of the specified OSS bucket. If the execution result is true, an external store is created and data is mapped.
    * | 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
    Configure the name of the external store and schema of the store in the CREATE clause and specify the information about the OSS bucket and objects in the WITH clause.
    Parameter Description
    External store name The name of the external store, for example user_meta1.
    Schema The column names, data type of the columns, and other properties of the external store, for example, (userid bigint, nick varchar, gender varchar, province varchar, gender varchar,age bigint).
    endpoint The endpoint of the OSS bucket. For more information, see Regions and endpoints.
    accessid The AccessKey ID of your account.
    accesskey The AccessKey secret of your account.
    bucket The OSS bucket that stores the CSV objects.
    objects The path of the CSV objects.
    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. Run the following statement to check whether the external store is created.
    If the returned result is the content that you have defined, the external store is created.
    select * from user_meta1
    Verify the settings
  6. Use the JOIN syntax to join data in Log Service and OSS.

    For example, you can run the following statement to join data in Log Service and OSS by using the id log field and the userid field in OSS objects. test_accesslog is the name of the Logstore. user_meta1 is the name of the external store. Replace the parameter values based on your business requirements.

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

For more information about how to associate Log Service with OSS, see Perform an association analysis on Log Service logs and OSS tables.