Today, I will discuss about Athena APIs which can be used in automation using shell scripting to fetch the Table data to perform validations like count, date, sum (aggregate functions), duplicate .I will explain the APIs using count validation in Athena. Get the Total records Details of the Athena tables using Athena APIs.STEP 1 : Use the below Command to get the query execution id for count Query of particular Table.API Used : start-query-executionaws athena start-query-execution –query-string “SELECT count(*) FROM $S3-Schema.$S3-Table” | grep [a-z] |awk -F “:” ‘{print $2}’|sed -e…
Category: AWS
Athena object properties
Today, I will discuss about the two things in single blog.1. How to generate DDL of Athena Object2. How to Check the properties of tables . GENERATE DDL of Athena ObjectLook at left Pane in the Athena console, select the database, Click on three vertical dots. See the below ScreenShot for the same. Click on “Generate Create Table DDL“ Table Properties of Athena Object Go to “Show Properties“ Once you click on Show properties. You will see the below Screen .If you can see , it will tell you when…
Automate the existence of S3 files through shell scripting
Today, I will discuss about “How to automate the existence of files in S3 bucket through shell script”. Here , i will share pseudo code for the same in the form of steps.* create one config file which we will have below details with pipe delimiter.S3 bucket Name|project_folder_name|Relative path of sub folderTechie-1|ABC|prj1/UC1/ #### Here techie-1 is the bucket Name, the immediate folder to this bucket is ABC and project Use-case folder is prj1/UC1/ ####* This[config] file will have list of Use-case folder names for which you need to test the…
features of Athena in AWS
Today, I will discuss about the Athena Database features in AWS. Below are the characteristics of the Athena .* Query Editor : This is the workspace where you can write your queries. * Saved Queries : This is the location where you can save your queries which are frequently used using “Save as” * History : It will have list of queries which are executed in the Query Editor section. * Athena logs : As mentioned in by previous blog, athena maintain logs in the S3 bucket as per the…
create table in Athena using CSV file
Today, I will discuss about “How to create table using csv file in Athena”.Please follow the below steps for the same.* Upload or transfer the csv file to required S3 location. * Create table using below syntax. create external table emp_details (EMPID int, EMPNAME string ) ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’ WITH SERDEPROPERTIES ( ‘serialization.format’ = ‘,’, ‘field.delim‘ = ‘,’ ) location ‘s3://techie-1/emp/’ TBLPROPERTIES ( “skip.header.line.count”=”1”) * Important to note here that if you have a file which has header , then you need to skip the header .For this, we…
create database in Athena
Today , I will discuss about “How to create Database in Athena.Follow the below steps to achieve the same.* Create IAM policy which gives access to S3 bucket for Athena Logs to the required user.* While creating that IAM policy, you need to put the below content in the JSON tab.* Here, in below JSON, you need to change the bucket Name. * If you see clearly , in JSON both Glue and Athena has mentioned , so if you don’t want to give access to Glue DB, then you…
CREATE SQL SERVER INSTANCE IN AWS
Today, i will discuss regarding the sql server instance creation in AWS. First of all, login to your AWS account. Go to services -> RDS. Below image will appear on your screen. Select SQL server as database Engine. Check the checkbox on “Only enable options for RDS Free Usage Tier” if you are using free Tier Account. Once you check the checkbox, it will automatically choose the option “Sql Server Express Edition”.If you are working on a project, choose the option as per the client requirements. See the below image…
HOW TO GENERATE ACCESS AND SECRET KEY IN AWS
Today, i will discuss about “how to generate Access and Secret key in AWS”. First, go to “My Account credentails”. See the below image for the same. Then Go to Access keys(Access key ID and secret access key). Then click on “Generate Access key”. Once you click on this button,”rootkey.csv” will get generated which will have Access key and Secret key.
HOW TO CREATE A S3 BUCKET IN AWS
Today i will discuss about “How to Create the S3 bucket in AWS”. Login to AWS account. Go to S3. Click on “create bucket”. See the below image for the same. Once you click on “create bucket“, it will ask for bucket name. Enter the bucket name(I created the bucket with name “s3-tech”) and click on Next. Then again click on Next.(As I am using free tier Account so few of functionalities are disabled). Then choose the value “Do not grant public read access to this bucket” for “Manage public permission”…