Have any question?: +917021479126 | info@webmagicinformatica.com

WebMagic Informatica | AWS Training In MumbaiWebMagic Informatica | AWS Training In Mumbai
    • Home
    • Courses
      • AWS Courses
      • Azure Courses
    • About Us
    • Contact Us
    • Jobs
    • Blog
      • Register Login

        Login with your site account

        Lost your password?

        Not a member yet? Register now

    • Home
    • Courses
      • AWS Courses
      • Azure Courses
    • About Us
    • Contact Us
    • Jobs
    • Blog

    Getting Started With Amazon Athena

    • Home
    • Blog
    • AWS Tutorial
    • Getting Started With Amazon Athena Tutorial

    Getting Started With Amazon Athena Tutorial

    • Date May 6, 2018

    This tutorial walks you through using Amazon Athena to query data. You’ll create a table based on sample data stored in Amazon Simple Storage Service, query the table, and check the results of the query.

    The tutorial is using live resources, so you are charged for the queries that you run. You aren’t charged for the sample datasets that you use, but if you upload your own data files to Amazon S3, charges do apply.

    Step 1: Create a Database

    1. Open the Athena console.
    2. If this is your first time visiting the Athena console, you’ll go to a Getting Started page. Choose Get Started to open the Query Editor. If it isn’t your first time, the Athena Query Editor opens.
    3. In the Athena Query Editor, you see a query pane with an example query. Start typing your query anywhere in the query pane.
    4. To create a database named mydatabase, enter the following CREATE DATABASE statement, and then choose Run Query:
      CREATE DATABASE mydatabase
    5. Confirm that the catalog display refreshes and mydatabase appears in the DATABASE list in the Catalog dashboard on the left side.

    Step 2: Create a Table

    Now that you have a database, you’re ready to create a table that’s based on the sample data file. You define columns that map to the data, specify how the data is delimited, and provide the location in Amazon S3 for the file.

    To create a table

    1. Make sure that mydatabase is selected for DATABASE and then choose New Query.
    2. In the query pane, enter the following CREATE TABLE statement, and then choose Run Query:

      Note

      You can query data in regions other than the region where you run Athena. Standard inter-region data transfer rates for Amazon S3 apply in addition to standard Athena charges. To reduce data transfer charges, replace myregionin s3://athena-examples-myregion/path/to/data/ with the region identifier where you run Athena, for example, s3://athena-examples-us-east-1/path/to/data/.

      CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
        `Date` DATE,
        Time STRING,
        Location STRING,
        Bytes INT,
        RequestIP STRING,
        Method STRING,
        Host STRING,
        Uri STRING,
        Status INT,
        Referrer STRING,
        os STRING,
        Browser STRING,
        BrowserVersion STRING
        ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
        WITH SERDEPROPERTIES (
        "input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$"
        ) LOCATION 's3://athena-examples-myregion/cloudfront/plaintext/';

      The table cloudfront_logs is created and appears in the Catalog dashboard for your database.

    Step 3: Query Data

    Now that you have the cloudfront_logs table created in Athena based on the data in Amazon S3, you can run queries on the table and see the results in Athena.

    To run a query

    1. Choose New Query, enter the following statement anywhere in the query pane, and then choose Run Query:
      SELECT os, COUNT(*) count
      FROM cloudfront_logs
      WHERE date BETWEEN date '2014-07-05' AND date '2014-08-05'
      GROUP BY os;

      Results are returned that look like the following:

    Tag:Amazon Athena

    • Share:
    Admin bar avatar
    admin

    Previous post

    What is Amazon Athena?
    May 6, 2018

    Next post

    What is AWS Batch?
    May 8, 2018

    You may also like

    Snowball Edge
    EC2 Compute Instances for AWS Snowball Edge
    21 July, 2018
    alb
    Simplify Login with Application Load Balancer Built-in Authentication
    2 June, 2018
    Amazon Linux 2 SSL
    Extending Amazon Linux 2 with EPEL and Let’s Encrypt
    16 May, 2018

    Leave A Reply Cancel reply

    Your email address will not be published. Required fields are marked *

    Latest Courses

    AWS Certified Solutions Architect – Associate

    AWS Certified Solutions Architect – Associate

    ₹12,000.00
    Microsoft Certified  Azure Administrator – Associate

    Microsoft Certified Azure Administrator – Associate

    ₹12,000.00
    DevOps – Continuous Integration and Deployment

    DevOps – Continuous Integration and Deployment

    ₹13,000.00
    Ansible: Infrastructure Automation

    Ansible: Infrastructure Automation

    ₹12,000.00
    AWS Certified SysOps Administrator

    AWS Certified SysOps Administrator

    ₹15,000.00
    AWS Certified Developer Associate

    AWS Certified Developer Associate

    ₹12,000.00

    Center Address

    Thane
    Address: 303, Sameer Arcade, Agyari Lane, Near Chintamani Jewellers, Jambhali Naka, Talaopali, Thane West, Thane – 400601
    Mail: info@webmagicinformatica.com
    Mobile No.: +91 7021 479 126

    Chembur
    Address
    : 201 2nd Floor, Bhairav Kripa Building, Above Mahalaxmi Jewellers, Opp.Sawan, Bazar, Near Chembur Flyover, Chembur Station Road East, Mumbai
    Mail: info@webmagicinformatica.com
    Mobile No.: +91 7021 479 126