Getting started with Amazon Athena Tutorial - Serverless Interactive | The Ultimate Guide
Amazon Athena Tutorial ACTE

Getting started with Amazon Athena Tutorial – Serverless Interactive | The Ultimate Guide

Last updated on 18th Jan 2022, Blog, Tutorials

About author

Mithelesh (Senior Test Automation Engineer )

Mithelesh is the Senior Test Automation Engineer for AWS Athena, CSV, JSON, ORC, Apache Parquet, and Avro. He has skills with PostgreSQL RDS, DynamoDB, MongoDB, QLDB, Atlas AWS, and Elastic Beanstalk PaaS.

(5.0) | 19712 Ratings 1734
    • Introduction to Amazon Athena
    • Pre-requisites of Amazon Athena
    • Distinction Between Microsoft SQL Server And Amazon Athena
    • Utilization Of Amazon Athena
    • Features Of Athena
    • Partitioning
    • Inquiries
    • Security
    • Pricing
    • How accomplishes AWS Athena work
    • How accomplishes AWS Athena work Graph
    • Making Table In Athena
    • AWS Athena Pricing details
    • AWS glue
    • Advantages of AWS glue
    • Amazon QuickSight
    • Conclusion

    Subscribe For Free Demo


      Introduction to Amazon Athena:

    • On November 20, 2016, Amazon sent off Athena as one of its administrations. As depicted before, Amazon Athena is a serverless inquiry administration that makes the examination of information, utilizing standard SQL, put away in Amazon S3 less complex. With few ticks in the AWS executives Control center, clients can point Amazon Athena at their information put away in Amazon S3 and run inquiries utilizing standard SQL to get brings about seconds.

    • With Amazon Athena, there is no foundation to set up or make due, and the client pays just for the inquiries they run. Amazon Athena scales naturally, executing questions in equal, which gives quick outcomes, even with an enormous dataset and complex inquiries. Presently, that you what is Amazon Athena let me take you through the distinction it has contrasted with SQL Server.

    • Pre-requisites:

      In the event that you have not previously done as such, pursue a record in Setting Up. Utilizing a similar AWS Locale (for instance, US West (Oregon)) and record that you are utilizing for Athena, Make a container in Amazon S3 to hold your Athena question results.

      1 Make a Data set

      2 You first need to make a data set in Athena.

      3 To make an Athena information base

    • Open the Athena console at
    • Assuming this is your first ideal opportunity to visit the Athena console in your present AWS Locale, decide to Investigate the inquiry manager to open the question supervisor. In any case, Athena opens in the question editorial manager.
    • Pick View Settings to set up a question result area in Amazon S3.

    • 4 On the Settings tab, pick Make due.

      5 For Oversee settings, do one of the accompanyings:

      In the Area of question result box, enter the way to the container that you made in Amazon S3 for your inquiry results. Prefix the way with s3://.

      Pick Peruse S3, pick the Amazon S3 pail that you made for your present Locale, and afterward Pick.

      6 Pick Save.

      7 Pick Manager to change to the question proofreader.

      8 On the right of the route sheet, you can utilize the Athena question supervisor to enter and run inquiries and explanations.

      9 To make a data set named database, enter the accompanying Make Data set explanation.

      10 Pick Run or press Ctrl+ENTER.

      11 From the Information base rundown on the left, pick database to make it your present data set.

      Course Curriculum

      Learn Advanced AWS Database Certification Training Course to Build Your Skills

      Weekday / Weekend BatchesSee Batch Details

      Distinction Between Microsoft SQL Server And Amazon Athena:

      Utilization Of Amazon Athena:

      Assuming you are an Information Investigator and have an encounter of dissecting information put away on S3, you will connect with this, Information Examiners/Designers: Do you offer Stockpiling?

    • AWS: Yes.
    • Information Investigators/Engineers: Do you have devices for Examination?
    • AWS: Not certain.”
    • Amazon chipped away at this and thought of Amazon Athena. Presently, you have an instrument to play with your information. Athena assists you with examining unstructured, semi-organized, and organized information that is put away in Amazon S3. Involving Athena you can make dynamic questions for your dataset. Athena likewise works with AWS glue to give you a superior method for putting away the metadata in S3.
    • Utilizing AWS CloudFormation and Athena, you can utilize named questions. Named question permits you to name your inquiry and afterward call it utilizing the name.
    • This intuitive assistance from AWS can be utilized by Information Researchers, engineers to bring a slip top into the table as opposed to running the total question. It is additionally used to bring information from S3, load it to various information stores utilizing Athena JDBC driver, for log stockpiling/investigation and Information Warehousing occasions.
    • Since you realize Athena is an intriguing apparatus, how about we discover in this Amazon Athena instructional exercise how to get your hands on this astounding assistance from Amazon.

    • Features Of Athena:

      Out of the many administrations given by Amazon, Athena is one of the administrations. It has many highlights that make it reasonable for Information Investigation. How about we investigate the various highlights individually.

      Simple Execution: Athena doesn’t need establishment. It tends to be gotten to straightforwardly from the AWS Control center likewise straight by AWS CLI.

      Serverless: It is serverless, so the end client doesn’t have to stress over foundation, design, scaling, or disappointment. Athena deals with everything all alone.

      Pay per question: Athena charges you just for the inquiry you run, for example how much information is overseen per inquiry. You can save a great deal assuming you can pack them and configuration your dataset in a like manner.

      Quick: Athena is an exceptionally quick examination apparatus. It can perform complex inquiries quicker than expected by breaking the intricate questions into less difficult ones and running them parallelly, then, at that point, consolidate the outcomes to give the ideal result.

      Secure: With the assistance of IAM strategies and AWS Character, Athena gives you unlimited authority over the informational collection. As the information is put away in S3 pails, IAM strategies can assist you with overseeing control of clients.

      Exceptionally accessible: With the confirmation of AWS, Athena is profoundly accessible and the client can execute questions nonstop. As AWS is 99.999% accessible, Athena is as well.

      Reconciliation: The best element of Athena is that it tends to be incorporated with AWS glue. AWS glue will assist the client with making a superior bound together information vault. This assists you with making better forming of information, better tables, sees, and so on


    • By Partitioning your information, you can confine how much information is filtered by each question, in this manner further developing execution and decreasing expense.
    • Athena uses Hive for apportioning information.
    • You can segment your information by any key.

    • Inquiries:

    • You can inquiry about geospatial information.
    • You can inquiry various types of logs as your datasets.
    • Athena stores inquiry brings about S3.
    • Athena holds question history for 45 days.
    • Athena doesn’t uphold client characterized capacities, Supplement INTO explanations, and put away strategies.
    • Athena upholds both basic information types, for example, Number, Twofold, VARCHAR, and complex information types like Guides, Cluster, and STRUCT.
    • Athena upholds questioning information in Amazon S3 Requester Pays cans.

    • Security:

    • Control admittance to your information by utilizing IAM arrangements, access control records, and S3 container strategies.
    • Assuming the documents in the objective S3 pail are encoded, you can perform inquiries on the scrambled information itself.

    • Pricing:

    • You pay just for the inquiries that you run. You are charged in light of how much information is examined by each inquiry.
    • You are not charged for bombed inquiries.
    • You can get tremendous expense investment funds and execution gains by compacting, Partitioning, or changing your information over to a columnar arrangement, on the grounds that every one of those activities lessens how much information Athena needs to output to execute a question.

    • Course Curriculum

      Get JOB Oriented AWS Database Training for Beginners By MNC Experts

      • Instructor-led Sessions
      • Real-life Case Studies
      • Assignments
      Explore Curriculum

      How accomplishes AWS Athena work:

      Athena works straightforwardly with S3 information. It utilizes a conveyed SQL motor, Presto for running inquiries. It utilizes Apache Hive to make and modify tables and parcels. How about we examine the essentials to begin working with Athena:

    • Should have an AWS account
    • Empower your record to trade your expense and utilization information into an S3 container.
    • Plan cans for Athena to the interface.
    • AWS makes manifest records utilizing metadata each time it keeps in touch with the can. Make an organizer inside the innovation was-charging information pail known as Athena, which contains just the information.
    • To improve on the arrangement, we can utilize one district: the us-west-2 area.
    • The last advance is downloading the accreditations for the new IAM client. The qualifications will straightforwardly guide the data set accreditations to the interface.

    • How accomplishes AWS Athena work Graph:

      • create data set on the off chance that not exists costdb;
      • create outside table on the off chance that not exists cost (
      • InvoiceID string,
      • PayerAccountId string,
      • LinkedAccountId string,
      • RecordType string,
      • RecordId string,
      • ProductName string,
      • RateId string,
      • SubscriptionId string,
      • PricingPlanId string,
      • UsageType string,
      • Operation string,
      • AvailabilityZone string,
      • ReservedInstance string,
      • ItemDescription string,
      • UsageStartDate string,
      • UsageEndDate string,
      • UsageQuantity string,
      • Rate string,
      • Cost string,
      • ResourceId string
      • )
      • row design serde ‘org.apache.hadoop.hive.serde2.OpenCSVSerde’
      • with serdeproperties (
      • ‘separatorChar’ = ‘,’,
      • ‘quoteChar’ = ‘”‘,
      • ‘escapeChar’ = ”
      • )
      • stored as textfile
      • location ‘s3://innovation aws-charging information/athena’

      Making Table In Athena:

      In this tutorial, we are utilizing live assets, so you are just charged for the inquiries you run however not for the datasets you use, and to transfer your information records into Amazon S3, charges do have any significant bearing. To question S3 document information, you want to have an outside table related to the record structure. We can Make Outside TABLES in two ways:

    • Physically.
    • Utilizing the AWS glue crawler.
    • To physically make an Outside table, compose the assertion Make Outer TABLE after the right construction and indicate the right arrangement and exact area. A model is displayed beneath:

      Making an Outer table physically

    • The made Outer tables are put away in AWS glue Index. The glue Clawer parses the construction of the information record and produces metadata tables, characterized in glue Information Index.
    • The crawler utilizes an AWS IAM (Character and Access The board) job to allow admittance to the information put away and the Information List. You ought to have the authorization to pass the jobs to the crawler for getting to Amazon S3 ways that are slithered.
    • Go to AWS glue, pick “Add tables” and afterward select “Add tables utilizing a crawler” choice.

    • Add tables utilizing glue crawler

    • Give the crawler a name. Suppose for instance: a vehicles crawler
    • Enter crawler name
    • Pick the way in Amazon S3 where the record is saved.
    • Assuming you intend to question just one record, you can pick either an S3 document way or the S3 organizer way to inquiry every one of the documents in the envelope having a similar design.

    • Enter crawler name

    • Pick the way in Amazon S3 where the document is saved.
    • Assuming you intend to question just one record, you can pick either an S3 document way or the S3 envelope way to inquiry every one of the documents in the organizer having a similar design.
    • cars.json document is in the S3 area s3://rosyll-niranjana-xavier/data_input/json-records/cars.json. You can likewise pick s3://rosyll-niranjana-xavier/data_input/json-documents/as the way.
    • Make an IAM job that is having authorization to the S3 object that you mean to question or pick a current IAM job (which has an adequate number of honors to get to the S3 object).
    • Pick an information base that contains the outside tables and alternatively pick a prefix to be added to the outer table name.
    • Pick information base and prefix for outer tables
    • Click Finish to make the glue Crawler

    • Run the crawler

      The Outer table made it under the predetermined data set. Presently you can question the S3 object utilizing it.

      SELECT information from the outside table

      Since we put a document, the “SELECT *FROM json_files;” question returns a record that was in the document. We should now attempt to put another record having a similar construction in a similar S3 organizer and attempt to inquiry the Outside TABLE once more.

      petercars.json document transferred to S3

    • Assuming you Question a similar Outside table, you will see two columns returned rather than one.
    • At the point when a similar Outside TABLE is questioned, you will get two records. This is on the grounds that there are two records in the S3 envelope with the ideal construction. You can play out a few procedures on the information. For example, the accompanying inquiry will UNNEST the exhibit in the outcome set.

    • AWS Athena Pricing details:

      Things That Are Free

      Information base, table, DDL-related executions, and outline are for the most part free. For instance, there is no charge for any of the accompanying assertions:

    • Make Outside TABLE
    • MSCK Fix TABLE
    • Adjust TABLE

    • Additional Costs

      Athena peruses the information that is put away in S3. There are standard charges in S3 to store the information in view of how it’s put away. It stores question history and results in one more can know as an optional S3 container. Hence, there will likewise be standard S3 information charges for that new information put away in a similar pail.

      Cost Reduction Techniques

    • Till now, we ran over the estimating subtleties of Amazon Athena. Presently we should investigate a portion of the expense decrease strategies recorded beneath:
    • Eliminate recorded outcomes utilizing S3 lifecycle rules
    • Pack your feedback information in S3
    • Use Segments Viably
    • Store Your Information in a Columnar Organization
    • Amazon Athena is astonishing assistance. It assists you with organizing your information and questions to decrease your expenses up to a degree and you’ll be added with a possible new contender to your armory for serverless figuring.

    • AWS glue:

      AWS glue is an impeccably overseen ETL administration which makes it adaptable for clients who need to get ready and burden information for examination. You can assemble and execute an ETL in the Amazon executives Control center with a couple of snaps. You can direct AWS glue toward your AWS information and find your information and store related metadata like Blueprint and table definition in the AWS glue Information Inventory. Your information once listed is quickly accessible, queryable, and accessible for ETL.

      Advantages of AWS glue:

    • AWS glue is incorporated with a wide scope of AWS administrations, and that implies fewer problems for you while onboarding.
    • AWS glue is serverless. No framework is needed to arrange or make due.
    • Need to pay just for the assets used to run the positions.

    • Amazon QuickSight:

      Amazon QuickSight is a cloud-fueled, quick BI help, which makes it simple to convey bits of knowledge to everybody in the association. Being a completely overseen administration, QuickSight allows you to make intelligent dashboards effectively and distribute with ML bits of knowledge. Dashboards can be gotten from any gadget installed into your applications, sites, and entries. Utilizing Pay-per-Meeting evaluating, permits you to give everybody to acquire information required when just paying for what you use.

    • A portion of the significant advantages given by Amazon QuickSight are recorded as follows:
    • Pay just for what you use
    • Scale from 10 clients to 10,000
    • Implant self-administration information examination
    • Fabricate start to finish BI arrangements

    • AWS Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download


      As information has turned into a fundamental resource that an organization claims, acquiring bits of knowledge and separating more out of the information is more basic now than at any other time. With public cloud administrations, offering support-based examination administrations, for example, Amazon Athena, organizations can get more bits of knowledge with next to no costly inconveniences that emerge with home-constructed investigation apparatuses.

      Being serverless engineering and utilizing ANSI SQL, Athena makes information questions speedy to set up, simple to utilize, and quick to run. The compensation per-utilized model of Athena will make it reasonable to run an examination. Since Athena works with Amazon S3 and accompanies unequaled versatility, sturdiness, unwavering quality, and the force of item stockpiling, this is the ideal suite to run examination jobs.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free