Improve query performance, cut costs with Amazon Athena

Format data in S3

Amazon Athena uses standard SQL, and developers often use big data SQL back ends to track usage analytics, as they can handle and manipulate large volumes of data to form useful reports. For example, an application could log every action a user takes in a given session and then create a log file in Amazon S3. A typical analytics log might track user ID, action name, action value, browser, location, IP address, start time and end time.

The application could store a log in S3 from a single session as a CSV or plain text format, but Athena works more efficiently with data stored in Parquet format. Apache provides a C++ library for Parquet, as well as several adapters for other languages, like Node.js.

The node-parquet module is simple to use and shows how to compress data into Parquet format for S3 and Athena. This code writes out data to a Parquet format then uploads that data to S3:

const parquet = require('node-parquet');

var schema = {

  user_id: {type: 'int32'},

  action_name: {type: 'byte_array'},

  action_value: {type: 'byte_array', optional: true},

  browser: {type: 'byte_array'},

  location: {type: 'byte_array'},

  ip_address: {type: 'byte_array'},

  start_time: {type: 'int32'},

  end_time: {type: 'int32'},

};

var data = [

  [ 1234, 'Login', null, 'Chrome', 'Ohio', '10.0.0.1', 1496941960, 1496942080],

  [ 1234, 'Search', 'Chris Moyer', 'Chrome', 'Ohio', '10.0.0.1', 1496941960, 1496942080],

  [ 1234, 'View', 'Book: Building Applications in the Cloud', 'Chrome', 'Ohio', '10.0.0.1', 1496941960, 1496942080],

  [ 1234, 'Add to Cart', 'Building Applications in the Cloud', 'Chrome', 'Ohio', '10.0.0.1', 1496941960, 1496942080],

  [ 1234, 'Checkout', '1 Item', 'Chrome', 'Ohio', '10.0.0.1', 1496951960, 1496952080],

];

var writer = new parquet.ParquetWriter(`${session_id}.parquet`, schema);

writer.write(data);

writer.close();

writer.on('end', () => {

  s3.upload({

    Bucket: 'my-parquet-data',

    Key: '${session_id}.parquet',

    Body: fs.createReadStream(`${session_id}.parquet`),

  }).promise().catch(e => {

    // Retry logic if there's an issue, or alert

    // if nothing can be retried.

  });

});

Parquet files have a strict format, so you need to define the schema before uploading to S3. This process also ensures that files will conform to the same schema and enables Athena to more easily and correctly process data. Developers can also use GZIP compression to further improve query performance.

Query data with Athena

After uploading data to S3, create a virtual table in Athena to query the data. This instructs Athena on how to parse S3 data into an SQL-compatible format. Use regular expressions or specify that the input data is in Parquet format. Specify Parquet as the data format, and the data will automatically move into the proper table schema based on how it's stored. Be sure to specify which columns to load into Athena; choose the same format and column names created when saving the data to S3.

Partitions, which are similar to indexes, can improve query performance. For example, you might choose to partition on user ID and action name, which would quickly group all activities from a particular user or action type.

After completing the Athena configuration, any SQL queries running either through the AWS Management Console or Athena API will search S3 directly. There's no caching or synchronization process; the data automatically updates, and the IT team only pays when performing a search.

Amazon Athena is helpful for developers that don't want to maintain a running SQL server, but it's only efficient for infrequent queries. Applications running constant queries against data should load data into a full database, like Amazon Aurora, or a data warehouse, like Amazon Redshift.