Pricing Details

You are charged for the number of bytes scanned by Amazon Athena, rounded up to the nearest megabyte, with a 10MB minimum per query. There are no charges for Data Definition Language (DDL) statements like CREATE/ALTER/DROP TABLE, statements for managing partitions, or failed queries. Cancelled queries are charged based on the amount of data scanned.

Compressing your data allows Athena to scan less data. Converting your data to columnar formats allows Athena to selectively read only required columns to process the data. Athena supports Apache ORC and Apache Parquet. Partitioning your data also allows Athena to restrict the amount of data scanned. This leads to cost savings and improved performance. You can see the amount of data scanned per query on the Athena console. link

So it becomes more important to compress your data and convert it to the recommended file formats Apache Parquet or Apache ORC.

DON’T WORRY!!! RAthena is here to help!

RAthena’s help

For a lot of users, Apache Parquet or Apache ORC are file formats that aren’t well known and as a result alto systems don’t have the software to create these formats. RAthena offers some assists by firstly enabling apache parquet format to be uploaded through dbWriteTable, using the R package arrow to create the parquet format.

If uploading Apache Parquet is not possible or if the file format Apache ORC is preferred then RAthena offers another solution. RAthena can utilise the power of AWS Athena to convert file formats for you. What this allows you to do is:

• Upload Data in an easier file format for example delimited format
• Convert Data into Parquet or ORC using AWS Athena to save cost
• Finally insert into final table with ETL processes

library(DBI)
library(RAthena)

con <- dbConnect(athena())

# create a temporary database to upload data into
res <- dbExecute(con, "CREATE IF NOT EXISTS DATABASE temp")
dbClearResult(res)

iris2 <- iris
iris2$time_stamp <- format(Sys.Date(), "%Y%m%d")

dbWriteTable(con, "temp.iris_delim", iris2)

# convert to parquet
dbConvertTable(con,
               obj = "temp.iris_delim",
               name = "iris_parquet",
               file.type = "parquet")

# convert to orc
dbConvertTable(con,
               obj = "temp.iris_delim",
               name = "iris_orc",
               file.type = "orc")

# convert to parquet with partition time_stamp
dbConvertTable(con,
               obj = "temp.iris_delim",
               name = "iris_parquet_partition",
               partition = "time_stamp",
               file.type = "parquet")

dbConvertTable(con,
              obj = SQL("select 
                          Sepal_Length,
                          Sepal_Width,
                          date_format(current_date, '%Y%m%d') as time_stamp 
                        from temp.iris_delim"),
              name = "iris_orc_partition",
              partition = "time_stamp",
              file.type = "orc")

res <- 
  dbExecute(con, "insert into iris_orc_partition
                  select 
                    Sepal_Length,
                    Sepal_Width, 
                    date_format(date_add('date', 1, current_date) , '%Y%m%d') time_stamp 
                  from temp.iris_delim")
dbClearResult(res)

library(dplyr)

iris_tbl <- tbl(con, dbplyr::in_schema("temp", "iris_delim"))

r_date <- format(Sys.Date(), "%Y%m%d")

iris_tbl %>% 
  select(petal_length,
         petal_width) %>% 
  mutate(time_stamp = r_date) %>%
  compute("iris_dplyr_parquet", partition = "time_stamp", file_type = "parquet")

Reading Material

• CTAS insert into ETL
• Considerations and Limitations for CTAS Queries
• Examples of CTAS Queries