> For the complete documentation index, see [llms.txt](https://amartyushov.gitbook.io/tech/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://amartyushov.gitbook.io/tech/platforms/aws/athena.md).

# Athena

## Apache Hive 

* data warehouse software facilitates **reading, writing**, and managing **large datasets** residing **in distributed storage using SQL**
* built on top of Hadoop
* initially developed by Facebook
* uses HiveQL (SQL-like queries) to query
* converts HiveQL to MapReduce jobs
* used by Athena as a metastore or catalog
* Athena provides an abstraction over Hive

<figure><img src="https://415484505-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LxtoAXZwwOc4XGto8vb%2Fuploads%2Fzvp6i7n948FiD22BcR6h%2FScreenshot%202022-12-16%20at%2015.03.28.png?alt=media&amp;token=536bb7ef-7d11-4a0e-9c2c-28ffb1d1e223" alt=""><figcaption></figcaption></figure>

## Athena databases and tables

* managed with Hive DDL statements
  \*

  ```
  <figure><img src="/files/Fn6roTbkyLwHtRoX5MDG" alt=""><figcaption></figcaption></figure>
  ```
* stored in a catalog or metastore
* SerDes libs grant data format support
  * Serializer / Deserializer libs. E.g. for json data => json SerDes lib, for csv => csv SerDes
* Schema is projected on to your data files
* Schame changes are ACID-compliant
* Tables are always external
* Table partitioning is supported

## [Data formats](https://docs.aws.amazon.com/athena/latest/ug/supported-serdes.html)

* json
* csv
* tsv
* CloudTrail logs
* Apache Web logs
* Parquet
* ORC
* Avro

### [Compression](https://docs.aws.amazon.com/athena/latest/ug/compression-formats.html)

* Snappy
* Zlib
* GZIP
* LZO
* BZip2

#### [Compression considerations](https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/):

* Splittable formats (e.g. BZip2)
  * Files can be scanned by multiple readers allowing you to take advantage of parallel processing
* Optimum file size (advice >120Mb)
  * Strike a balance between having too many small data files and having too few large data files
* Compression ratio
  * algorithms with high compression ratios make files smaller but also increase decompress CPU consumption

If you compress => you reduce money consumed by Athena (as there is less data and you pay for queried data. You pay for data size in compressed state)

## Presto SQL Engine

* Open-source distributed SQL engine
* ANSI-Compliant or standard SQL
* Reads info about schema from remote Hive metastore (Athena catalog)
  * Presto needs to know where the data is and what of data it is
* :warning:Does not use MapReduce
* Size of dataset does not matter

## Columnar Format considerations

* Analytic query workloads
  * Queries that concern large numbers of rows, but only a few columns will benefit from this kind of format
* Compression algorithm
  * Parquet and ORC compress by default. However, the algorithms they use may be experimented with.

## Other solutions

* Redshift
  \*

  ```
  <figure><img src="/files/KQAWc6eGMCvSnwEt8H5a" alt=""><figcaption></figcaption></figure>
  ```
* Elastic MapReduce
  \*

  ```
  <figure><img src="/files/mSeksTPbDcwWYpdolmny" alt=""><figcaption></figcaption></figure>
  ```