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.
noctua is here to 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.
noctua 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
noctua offers another solution.
noctua can utilise the power of AWS Athena to convert file formats for you. What this allows you to do is:
Uploading Data in delimited format is the easiest method.
library(DBI) library(noctua) <- dbConnect(athena()) con # create a temporary database to upload data into <- dbExecute(con, "CREATE IF NOT EXISTS DATABASE temp") res dbClearResult(res) <- iris iris2 $time_stamp <- format(Sys.Date(), "%Y%m%d") iris2 dbWriteTable(con, "temp.iris_delim", iris2)
However delimited file format isn’t the most cost effective when it comes to using AWS Athena. To overcome this we can convert this by using AWS Athena.
Converting table to a non-partitioned Parquet or ORC format.
# 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")
NOTE: By default
dbConvertTable compresses Parquet/ ORC format using
noctua goes a step further by allowing tables to be converted with partitions.
# convert to parquet with partition time_stamp dbConvertTable(con, obj = "temp.iris_delim", name = "iris_parquet_partition", partition = "time_stamp", file.type = "parquet")
noctua even allows SQL queries to be converted into desired file format:
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")
As we have created partitioned data, we can easily insert into:
<- 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)
What this all means is that you can create ETL processes by uploading data in basic file format (delimited), and then converting / inserting into the prefer file format.
The good news doesn’t stop there,
noctua integrates with
dplyr to allow converting to be done through
library(dplyr) <- tbl(con, dbplyr::in_schema("temp", "iris_delim")) iris_tbl <- format(Sys.Date(), "%Y%m%d") r_date %>% iris_tbl select(petal_length, %>% petal_width) mutate(time_stamp = r_date) %>% compute("iris_dplyr_parquet", partition = "time_stamp", file_type = "parquet")