How to Use S3 Select to Query Data in Amazon S3

Use S3 Select to run SQL queries directly on data stored in Amazon S3 objects without downloading the entire file. You specify the bucket, object key, input/output formats, and an SQL expression to filter or transform the data.

Syntax

The basic syntax for using S3 Select involves specifying the bucket name, object key, input serialization format, output serialization format, and the SQL expression to run on the object data.

• Bucket: The S3 bucket where your file is stored.
• Key: The path or name of the file inside the bucket.
• Expression: The SQL query to filter or select data.
• InputSerialization: Describes the format of the input file (e.g., CSV, JSON).
• OutputSerialization: Describes the format of the output data.

javascript

s3.selectObjectContent({
  Bucket: 'example-bucket',
  Key: 'data.csv',
  ExpressionType: 'SQL',
  Expression: 'SELECT * FROM S3Object WHERE _1 > 100',
  InputSerialization: { CSV: { FileHeaderInfo: 'USE' } },
  OutputSerialization: { CSV: {} }
})

Example

This example shows how to use AWS SDK for JavaScript to query a CSV file stored in S3 using S3 Select. It selects rows where the first column value is greater than 100.

javascript

import { S3Client, SelectObjectContentCommand } from "@aws-sdk/client-s3";

const client = new S3Client({ region: "us-east-1" });

async function run() {
  const params = {
    Bucket: "example-bucket",
    Key: "data.csv",
    ExpressionType: "SQL",
    Expression: "SELECT * FROM S3Object WHERE _1 > 100",
    InputSerialization: { CSV: { FileHeaderInfo: "USE" } },
    OutputSerialization: { CSV: {} }
  };

  const command = new SelectObjectContentCommand(params);
  const response = await client.send(command);

  for await (const event of response.Payload) {
    if (event.Records) {
      const chunk = new TextDecoder("utf-8").decode(event.Records.Payload);
      console.log(chunk);
    }
  }
}

run().catch(console.error);

Output

101,John,Sales
150,Mary,Marketing

Common Pitfalls

Common mistakes when using S3 Select include:

• Not matching the InputSerialization to the actual file format, causing errors.
• Using incorrect SQL syntax or referencing columns incorrectly (e.g., using _1 for the first column in CSV).
• Forgetting to handle the streaming response properly, which can cause incomplete data reads.
• Trying to use S3 Select on unsupported file types or compressed files without proper configuration.

javascript

/* Wrong: InputSerialization set to JSON for a CSV file */
s3.selectObjectContent({
  Bucket: 'example-bucket',
  Key: 'data.csv',
  ExpressionType: 'SQL',
  Expression: 'SELECT * FROM S3Object',
  InputSerialization: { JSON: {} }, // Incorrect for CSV
  OutputSerialization: { CSV: {} }
});

/* Right: InputSerialization matches CSV format */
s3.selectObjectContent({
  Bucket: 'example-bucket',
  Key: 'data.csv',
  ExpressionType: 'SQL',
  Expression: 'SELECT * FROM S3Object',
  InputSerialization: { CSV: { FileHeaderInfo: 'USE' } },
  OutputSerialization: { CSV: {} }
});

Quick Reference

Remember these tips when using S3 Select:

• Match InputSerialization to your file type (CSV, JSON, Parquet).
• Use SQL expressions to filter or transform data.
• Handle the streaming response to read data chunks.
• S3 Select works best for large files where you want to reduce data transfer.

Key Takeaways

S3 Select lets you query data inside S3 objects using SQL without downloading the whole file.

Always set the correct input and output serialization formats matching your file type.

Use SQL expressions carefully, referencing columns properly (e.g., _1 for first CSV column).

Handle the streaming response to process data chunks correctly.

S3 Select reduces data transfer and speeds up data retrieval for large files.