Amazon Athena is a query service that makes it easy to analyze data in Amazon S3 using standard SQL.
Kloudio's AWS Athena integration allows users to easily create reports from Amazon S3 data and run them in Google Sheets or Excel.
The whole process can be divided into 3 sections
- Data preparation - done within the AWS platform
- Creating a connection in Kloudio
- Running your report
This has to be performed via AWS. Make sure you have logged into an account that has permissions to access S3 files and AWS-Athena. Once ready, please follow the below steps
- Setup source file: Copy your data file into an S3 bucket. We will call this bucket "Source Bucket". In our examples, we have copied our "movies.csv" file to a folder named "movies". (If you already have data prepared, you can move directly to step 2)
Below is a screenshot of our CSV file.
i. Make sure you keep one type of data (current and future) in one folder. As an example, if you have movie data, then you can keep current and future related data in a folder named "Movies". This will make sure Athena scans only relevant files, ensuring that the query performs fast (and is cost efficient).
ii. If your data has a header, remove it. Like in our Movies.csv file, our first row is the header as highlighted below. We recommend you to remove it
2. Create a data table in Athena: After Copying your files in S3, Go to Athena in Amazon AWS.
You will see the screen below:
For our example we will create a table named "movies" for our "Movies" folder.
Please follow the below steps:
Now Athena will create a table and your UI will resemble the below:
You can verify your table by running a simple SQL Query:
The data preparation is now finished - you can now move to Kloudio's platform.
Creating a connection on Kloudio portal
Follow the links in the order shown below, also demonstrated in the below screenshots:
Home -> Sidebar -> Connections -> New Connection -> AWS Athena -> Fill form
S3 Cache bucket is the location where Athena caches its queries. Make sure you don't have same cache and source path.
After clicking save, your connection will be created.
You can verify your connection by running an Adhoc report in Kloudio's Report Builder
You can save your report by clicking "save".