1. Create Databases – To begin,  create both an Athena database and a table. Remember you need to create a table that will match CSV formats and files in the S3 billing bucket.
Few unseen errors that can be viewed are as follows 

• Users can parse the CSV file by using the open CSV serde plugin.
• The plugin only supports gzip files and not zip files.  You will need to convert the compression format to gzip or any of the supporting formats.
• The plugin claims to support skip. Line. header. Count to skip header rows, it seems to be broken though. Then, you will have to rewrite the CSV files manually without the header.
• You can run the Data Definition Language using either the AWS web console or via the product for creating databases.

2. Partitioning Data –  Users generally store their data in time series format and require to query specific data considering period time i.e day, months, and year. Without partitioning data, Athena will scan all the data without executing queries.  By partitioning the data you can restrict Athena, thus reducing time, and effort, and lowering cost. This will help to improve performance also.

3. Convert data into columnar facts –   Customers can use open sources columnar formats such as Apache ORC and Apache Parquet. It will save cost as well as improve the performance.

4. Compare the Performance – The user can compare the performance of the same query between Parquet files and text files.

AWS Athena charges the users by the amount of data that is scanned per query.  By converting the data to columnar formats, Partitioning, and compressing it, you will save cost and also get better performance.

AWS Athena Benefits

1. Serverless: As AWS Athena is serverless there is no need for infrastructure to use the tool. You can quickly query the data without the need of setting up servers or data warehouses. It allows the user to tap all the data easily.  All you need is to set up a schema and begin querying by using the inbuilt query editor.

2. Cost Effective: For using AWS Athena users have to only pay for the queries they run. They only have to shell money for the amount of data that is scanned per query.  Also, there are no additional charges for storage as the user has to perform queries directly in S3.

3. Widely Accessible: It is accessible on a larger scale for everyone from developer’s engineers to business analysts to other data specialists, as it is merely a query tool that uses normal Structure Query Language. The queries are simple & easy hence can be used by anyone.

4. Flexibility: A versatile and open architecture of AWS Athena guarantees that the user is not limited or tied up with a single provider, tool, or technology.  For instance, the user can work with the Nth number of file formats that are open source without changing the schema swap amongst query engines.

5. Secure: Amazon provides its users with services that are secured. Security is its priority. The effectiveness of which is regularly tested and verified by third-party auditors which is a part of AWS compliance programs. 

6. Integration:  AWS Athena can integrate with a variety of tools such as AWS Glue, Key Management Service, and Amazon Quicksight. For example, If a user integrates Athena with Glue then the user can access the Glue catalogue which will help him to create a metadata repository across various services.

Join our AWS Technical Training today and enhance your skills to new heights!

Subscribe to our YouTube channel to get new updates..!

Amazon Athena Use case

Let’s take an example. As shown in the diagram given above. It depicts a data pipeline whereby data is retrieved and then put in S3 buckets taken from many sources. As the image indicates, unprocessed data depicts that they are not transferred yet. So, the user now can connect the data to S3 using AWS Athena and start analyzing them.

There is no requirement of setting up a database or using external tools for querying the raw data, hence a very straight approach. Once you complete your research and get the desired results. You can use EMR as seen in the diagram to do data transformations and complex analytics  and return to S3 after cleaning and processing the data that is raw

The user can then use Athena for querying the processed data to analyse it further. Quick sight can also connect directly with AWS Athena and create images of the data which is stored on S3. You can also migrate the data to Amazon RedShift which is a MPP data warehouse for data analysis, then the user should use QuickSight to view the data from the Redshift.

Optimization Techniques for AWS Athena

AWS Athena has proved to be decently priced. But its billing process is not simple.  It will let you know the funds used but would be difficult to see how and when.

Following are the optimization techniques that you can use to optimise AWS Athena

Partitioning the data in S3: Partitioning the data will help to divide it into parts and keep the related data in one place based on column values such as date, region, and country. It acts as virtual columns. First, you need to define the table and then reduce the data that needs to be scanned for the query. You can restrict the query i.e., the amount of data that should be scanned based on the partition. Therefore, Athena will only scan the necessary data and charge accordingly.

Use Data Compression Techniques:  AWS Athena supports many compression formats for both readings and writing the data.  It can successfully read the data from a table that uses Parquet file format when it is compressed with snappy and others with GZIP. The same condition applies to text files, ORC, and JSON storage formats. Various compression formats that Athena supports are GZIP, LZO, SNAPPY, DEFLATE, and others.

Optimise JOIN conditions in queries: Selecting the right join order is important for better query performance.  In case you are joining two tables, mention the larger table and smaller table on the left and right side of the join respectively.  It distributes the table to worker notes which is on the right and then the left side of the table is streamed to do the join.  If the right-side table is smaller then less memory is utilised and also the query runs faster.

Top 50+ frequently asked AWS interview questions !

Amazon Athena Pricing

AWS Athena is very cost-effective. The charges are calculated on the quantity of data that is scanned during query execution.  It costs around $5 per Terabyte of data. An additional advantage of using Athena is it allows users to compress data and have Columnar formats, and also routinely delete the old results sets.

Additional charges: Since Amazon  Athena reads your data that is stored in Amazon S3 there will be nominal charges for the storage of the data depending on the nature of its storage. History & results are also stored in the S3 bucket therefore, there will be charges which are very nominal for the data stored in that bucket too. 

For Instance – Let us consider a table with equal size on S3 with three columns with a size of 3 TB in total as an uncompressed file. Since the text formats are impossible to be divided, running queries to extract the data from each column of the table would require AWS Athena to scan the whole file. 

Thus the query would cost approximately  $15. Price of 3 TB ( Terabyte) of data scanned i.e. 3*5= 15 $

Pros and Cons AWS Athena

Pros:

• The query of the data is possible for 24 X 7 without running servers.
• It is a cost-effective method as compared to traditional databases.
• Glue and Athena use the same data catalog. Therefore, data analysis becomes easy.
• No need for any configuration.
• Ad hoc to make check the data
• It integrates easily with QuickSight. 

Cons:

• Data optimization is not possible. It can only optimise the queries but not the underlying data.
• All the users of AWS Athena across the globe share the same sources while running the queries.
• It does not support data manipulation operations. Because AWS Athena provides merely a query service. It does not have a DTL interface to insert, delete, and update the data.
• It requires data partitioning.
• It does not have an indexing feature. 

Conclusion

AWS Athena is thus very cost-effective when compared with its competitors in the market. As it charges only when the user utilises it. Athena helps the user with the ability to use the standard SQL statements on data that is stored in S3 buckets. As seen, there are many benefits of using Amazon Athena along with a few limitations. All you need is to understand the requirements of your organisation and make a sound decision.