Overview

This API is used to extract content from the specified object (in CSV or JSON format) using Structured Query Language (SQL) statements. To send this request, you need to specify the content delimiter and use an appropriate SQL function. COS Select will return matched extraction results in a format you specified for saving.

For more information on COS Select, see COS SELECT Overview. For more information on SQL expressions in COS Select, see SELECT Command in the Developer Guide.

Note：

The Select Object Content API currently only supports virtual-hosted requests, but not path-style requests.

Permission restrictions

To use COS Select, you must have the permission to cos:GetObject.

• If you are using a root account, you have the permission by default.
• If you are using a sub-account, contact your root account to get the permission to this operation. For more information on permission settings, see Granting Sub-accounts Access to COS.

Object formats

COS Select supports extracting data from objects in the following formats:

• CSV: an object is stored in CSV format with its data records separated with a specific delimiter.
• JSON: an object is stored in JSON format, which can be either a JSON file or a JSON list.
• Parquet: an object is stored in Parquet format, which can contain nested structures.

Note：

• To use COS Select, the object must be UTF-8 encoded.
• COS Select supports extracting data from CSV- and JSON-formatted objects compressed using GZIP or BZIP2, and Parquet-formatted objects compressed using GZIP or Snappy.
• COS Select supports extracting data from objects encrypted with SSE-COS.

Request

Sample request

POST /<ObjectKey>?select&select-type=2 HTTP/1.1
Host: <BucketName-APPID>.cos.<Region>.myqcloud.com
Date: date
Authorization: Auth String

Request body

Note：

• Authorization: Auth String (see Request Signature for details).
• The request parameters select and select-type=2 are required, where the former represents the initiation of a select request, and the latter represents the version information of the API.

Request headers

This API only uses common request headers. For more information, see Common Request Headers.

Request body

The following sample shows how to initiate a COS Select request to extract all the content from a CSV-formatted object and save the result as a CSV-formatted object.

<?xml version="1.0" encoding="UTF-8"?>
<SelectRequest>
    <Expression>Select * from COSObject</Expression>
    <ExpressionType>SQL</ExpressionType>
    <InputSerialization>
        <CompressionType>GZIP</CompressionType>
        <CSV>
            <FileHeaderInfo>IGNORE</FileHeaderInfo>
            <RecordDelimiter>\n</RecordDelimiter>
            <FieldDelimiter>,</FieldDelimiter>
            <QuoteCharacter>"</QuoteCharacter>
            <QuoteEscapeCharacter>"</QuoteEscapeCharacter>
            <Comments>#</Comments>
            <AllowQuotedRecordDelimiter>FALSE</AllowQuotedRecordDelimiter>
        </CSV>
    </InputSerialization>
    <OutputSerialization>
        <CSV>
            <QuoteFields>ASNEEDED</QuoteFields>
            <RecordDelimiter>\n</RecordDelimiter>
            <FieldDelimiter>,</FieldDelimiter>
            <QuoteCharacter>"</QuoteCharacter>
            <QuoteEscapeCharacter>"</QuoteEscapeCharacter>
        </CSV>
    </OutputSerialization>
    <RequestProgress>
        <Enabled>FALSE</Enabled>
    </RequestProgress>
</SelectRequest> 

The following sample shows how to initiate a COS Select request to extract all the content from a JSON-formatted object and save the result as a JSON-formatted object.

<?xml version="1.0" encoding="UTF-8"?>
<SelectRequest>
    <Expression>Select * from COSObject</Expression>
    <ExpressionType>SQL</ExpressionType>
    <InputSerialization>
        <CompressionType>GZIP</CompressionType>
        <JSON>
            <Type>DOCUMENT</Type>
        </JSON>
    </InputSerialization>
    <OutputSerialization>
        <JSON>
            <RecordDelimiter>\n</RecordDelimiter>
        </JSON>                                  
    </OutputSerialization>
    <RequestProgress>
        <Enabled>FALSE</Enabled>
    </RequestProgress>                                  
</SelectRequest> 

The following sample shows how to initiate a COS Select request to extract all the content from a Parquet-formatted object and save the result as a JSON-formatted object.

<?xml version="1.0" encoding="UTF-8"?>
<SelectRequest>
    <Expression>Select * from COSObject</Expression>
    <ExpressionType>SQL</ExpressionType>
    <InputSerialization>
        <CompressionType>GZIP</CompressionType>
        <Parquet>
        </Parquet>
    </InputSerialization>
    <OutputSerialization>
        <JSON>
            <RecordDelimiter>\n</RecordDelimiter>
        </JSON>                                  
    </OutputSerialization>
    <RequestProgress>
        <Enabled>FALSE</Enabled>
    </RequestProgress>                                  
</SelectRequest> 

Note：

• InputSerialization is a required element that specifies the format for the object to be extracted. It can be set to CSV, JSON or Parquet.
• OutputSerialization specifies the format in which extraction results are to be saved. It can be set only to CSV or JSON.
• The two formats above do not need to be the same. For example, you may extract data from an object in JSON format and save the extraction result in CSV format.

The following table shows all elements in a request body:

InputSerialization container element

CSV container element (InputSerialization sub-element)

JSON container element (InputSerialization sub-element)

OutputSerialization container element

CSV container element (OutputSerialization sub-element)

JSON container element (OutputSerialization sub-element)

RequestProgress container element

Response

A successful extraction operation will return a 200 OK status code.

Response headers

This API only returns common response headers. For more information, see Common Response Headers.

Response body

As the size of the response body is unpredictable, COS presents the response body in serialized form, i.e., dividing the response body into multiple parts to be returned. The following shows an overview of the returned response body:

<Message 1>
<Message 2>
<Message 3>
......
<Message n>

Pre-response (Prelude) and Response Result (Data)

COS splits the extraction result into multiple parts, each of which is a message. Each message consists the pre-response (prelude) and response result (data).

• The prelude consists of two parts:
  • Total length of the message.
  • Total length of all headers.
• The data consists of two parts:
  • Header.
  • Payload.

Both the prelude and data end with a 4-byte CRC code encoded in Big Endian. COS Select uses CRC32 to calculate the CRC code. For more information on CRC32, see the RFC documentation. In addition to data, COS Select additionally spends a total of 16 bytes in transferring the prelude and code.

Note：

The integer values in all the messages are transferred in network byte order (i.e., encoded in Big Endian).

The figure below shows what a message and a header consist of. One message may contain multiple headers.

As shown above, each message consists of a prelude, a prelude CRC code (composed of two pieces of information that record the number of bytes), header(s), a payload, and a message CRC code. As can be seen from the above figure, the length of the entire response body is calculated as follows:

Total length of a response body = Length of the prelude + length of the prelude CRC code + length of the payload + length of the header(s) + length of the message CRC code

As the prelude, the prelude CRC code and the message CRC code always have a combined length of 16 bytes, the total length of the response body can also be quickly calculated as follows:

Total length of a response body = Length of the payload + length of the header(s) + 16

The following describes the components of the response body in detail:

A message may include multiple headers, each of which consists of the following elements:

COS Select supports the following response types:

These response types are as detailed below.

Records message

• Header format
  A records message contains three types of headers: ":message-type", ":event-type", and ":content-type", as shown below:
  
• Body format
  The body of a records message may contain a single record, a partial record, or multiple records, depending on the number of extraction results.

Continuation Message

• Header format
  A continuation message contains two types of headers: ":message-type" and ":event-type", as shown below:
  
• Body format
  A continuation message contains no body content.

Progress Message

• Header format
  A progress message contains three types of headers: ":message-type", ":event-type", and ":content-type", as shown below:
  
• Body format
  The body of a progress message is XML text which contains the current query progress, mainly including:
  • BytesScanned: if the file is compressed, this value represents the size of the file in bytes before it is decompressed; otherwise, this value represents the size of the file in bytes.
  • BytesProcessed: if the file is compressed, this value represents the size of the file in bytes after it is decompressed; otherwise, this value represents the size of the file in bytes.
  • BytesReturned: size of the extraction result currently returned by COS Select in bytes.

Below is a sample:

<?xml version="1.0" encoding="UTF-8"?>
<Progress>
     <BytesScanned>512</BytesScanned>
     <BytesProcessed>1024</BytesProcessed>
     <BytesReturned>1024</BytesReturned>
</Progress>

Stats Message

• Header format
  A stats message contains three types of headers: ":message-type", ":event-type", and ":content-type", as shown below:
  
• Body format
  The body of a stats message is XML text which contains the statistics of the current query, mainly including:
  • BytesScanned: if the file is compressed, this value represents the size of the file in bytes before it is decompressed; otherwise, this value represents the size of the file in bytes.
  • BytesProcessed: if the file is compressed, this value represents the size of the file in bytes after it is decompressed; otherwise, this value represents the size of the file in bytes.
  • BytesReturned: size of the extraction result returned by COS Select in the query in bytes.

See below as an example:

<?xml version="1.0" encoding="UTF-8"?>
<Stats>
     <BytesScanned>512</BytesScanned>
     <BytesProcessed>1024</BytesProcessed>
     <BytesReturned>1024</BytesReturned>
</Stats>

End Message

• Header format
  An end message contains two types of headers: ":message-type" and ":event-type", as shown below:
  
• Body format
  An end message contains no body content.

Request Level Error Message

• Header format
  A request level error message contains three types of headers: ":error-code", ":error-message", and ":message-type", as shown below:
  

For more information on the error code in a request level error message, see Special Error Codes.

• Body format
  A request level error message contains no body content.

Special error codes

For common errors related to this request, see Error Codes. The following describes special error codes:

Examples

Sample 1. Extracting data from an object in CSV format

The following sample shows the process of calling this API to extract all the data from a CSV-formatted object and outputting the extraction result in CSV format. The object to be extracted is named exampleobject.csv and stored in the bucket examplebucket-1250000000 in Beijing (ap-beijing).

POST /exampleobject.csv?select&select-type=2 HTTP/1.1
Host: examplebucket-1250000000.cos.ap-beijing.myqcloud.com
Date: Tue, 12 Jan 2019 11:49:52 GMT
Authorization: authorization string
Content-Length: content length

<?xml version="1.0" encoding="UTF-8"?>
<SelectRequest>
    <Expression>Select * from COSObject</Expression>
    <ExpressionType>SQL</ExpressionType>
    <InputSerialization>
        <CompressionType>None</CompressionType>
        <CSV>
            <FileHeaderInfo>IGNORE</FileHeaderInfo>
            <RecordDelimiter>\n</RecordDelimiter>
            <FieldDelimiter>,</FieldDelimiter>
            <QuoteCharacter>"</QuoteCharacter>
            <QuoteEscapeCharacter>"</QuoteEscapeCharacter>
            <Comments>#</Comments>
        </CSV>
    </InputSerialization>
    <OutputSerialization>
        <CSV>
            <QuoteFields>ASNEEDED</QuoteFields>
            <RecordDelimiter>\n</RecordDelimiter>
            <FieldDelimiter>,</FieldDelimiter>
            <QuoteCharacter>"</QuoteCharacter>
            <QuoteEscapeCharacter>"</QuoteEscapeCharacter>
        </CSV>                               
    </OutputSerialization>
</SelectRequest> 

To run different extraction commands, you can modify the SQL command in the Expression element. For more information on commands, see SELECT Command. Some common extraction scenarios are described below.

• Suppose that you filter data in an object by column index. You can use s._n to filter out the data in the n column, with the minimum value of n being 1. The following command will filter out the records in column 3 with a value greater than 100 from the object and return columns 1 and 2 of those records:

  SELECT s._1, s._2 FROM COSObject s WHERE s._3 > 100

• If your CSV-formatted object has a column header and you want to filter data in the object by the column header (by setting FileHeaderInfo to Use), you can use s.name to do so. The following command will filter out records with a header named Id or FirstName:

  SELECT s.Id, s.FirstName FROM COSObject s

• You can also specify a function in the SQL expression. The following command will count the number of records in the first column that are smaller than 1:

  SELECT count(*) FROM COSObject s WHERE s._1 < 1

The following is a sample response:

HTTP/1.1 200 OK
x-cos-id-2: cos_id_demo
x-cos-request-id: cos_request_id_demo
Date: Tue, 12 Jan 2019 11:50:29 GMT

A series of messages

Sample 2. Extracting data from an object in JSON format

The following sample shows the process of calling this API to extract all the content from a JSON object and outputting the extraction result in CSV format. The object to be extracted is named exampleobject.json and stored in the bucket examplebucket-1250000000 in Beijing (ap-beijing).

POST /exampleobject.json?select&select-type=2 HTTP/1.1
Host: examplebucket-1250000000.cos.ap-beijing.myqcloud.com
Date: Tue, 12 Jan 2019 11:52:29 GMT
Authorization: authorization string
Content-Length: content length

<?xml version="1.0" encoding="UTF-8"?>
<SelectRequest>
    <Expression>Select * from COSObject</Expression>
    <ExpressionType>SQL</ExpressionType>
    <InputSerialization>
        <CompressionType>NONE</CompressionType>
        <JSON>
            <Type>DOCUMENT</Type>
        </JSON>
    </InputSerialization>
    <OutputSerialization>
        <CSV>
            <QuoteFields>ASNEEDED</QuoteFields>
            <RecordDelimiter>\n</RecordDelimiter>
            <FieldDelimiter>,</FieldDelimiter>
            <QuoteCharacter>"</QuoteCharacter>
            <QuoteEscapeCharacter>"</QuoteEscapeCharacter>
        </CSV>                               
    </OutputSerialization>
</SelectRequest> 

Similarly, you can also perform different extraction commands on JSON objects by modifying the SQL command in the Expression element. For more information on commands, see SELECT Command. Some common extraction scenarios are described below.

• You can extract data using the JSON attribute name. The following command will filter out records whose city value is Seattle from the object and return the country and city information in those records:

  SELECT s.country, s.city from COSObject s where s.city = 'Seattle'

• You can also specify a function in the SQL expression. The following command will count the total number of records in the JSON object:

  SELECT count(*) FROM COSObject s

Notes:

Unlike the GET Object API, SELECT Object Content cannot:

• Return a part of an object. You cannot use parameters such as Range to specify only a certain part of an object to return.
• Extract data from the ARCHIVE or DEEP ARCHIVE storage class before the data is restored.