Installing and Configuring cx_Oracle on CentOS 6

cx_Oracle module of python is used to connect to Oracle from Python. This works only with python 2.7. Here are the steps that can be used to install python2.7 on CentOS 6 and cx_Oracle module.

Installing python 2.7 as alternate python installation on CentOS 6

wget "https://www.python.org/ftp/python/2.7.13/Python-2.7.13.tgz"
tar -zxvf Python-2.7.13.tgz 
cd Python-2.7.13
./configure 
make
make altinstall 
which python2.7 # To verify installation path

Installing Oracle Instant Client

Download Oracle Instant Client from this link. We need instant client and instant client SDK. To install Oracle Instant client just unzip both the zip files to /opt or any other direcory

Create symbolic link libclntsh.so so it points to correct version.

cd /opt/instantclient_11_2/
ln -s libclntsh.so.11.1 libclntsh.so

Set environment variables

export ORACLE_HOME=/opt/instantclient_11_2
export LD_LIBRARY_PATH=/opt/instantclient_11_2

Installing pip for python 2.7

pip is a package management system used to install python libraries. Here are the steps to install.

wget https://bootstrap.pypa.io/get-pip.py
python2.7 get-pip.py

Installing cx_Oracle module using pip

pip install cx_Oracle

Sample program to test cx_Oracle

import cx_Oracle

connection = cx_Oracle.connect ("username/password@hostname/service")

cursor = connection.cursor ()
cursor.execute ("SELECT 1 a, 'AA' b, sysdate c FROM dual union select 2 a, 'BB' b, sysdate + 1 c from dual")
result = cursor.fetchall ()

for row in result:
    print row[0], row[1], row[2]

cursor.close ()
connection.close ()

Analyze AWS billing detailed line items file, and getting bill details using script

We can configure itemized bill of AWS to be uploaded S3. It can be done from "Billing Management Console" of AWS -> "Preferences", and turning on option "Receive Billing Reports". Now we can do analysis of "Detailed billing report with resources and tags", and get bill amount in INR, and know which resources are getting billed more etc.

Here is the script that provides analysis of itemized bill that's already uploaded to S3.

# This script uses command line utility q - Text as Data
# Download Page: https://harelba.github.io/q/

# This script expects aws - CLI already configured on the system

# Hard coded values first
account_id=xxxxxxxxx #AWS account ID
tax=15  # Service tax + Swacch Bharath + Krishi Kalyan
bucket_name="xxxxxx" #Name of S3 bucket where billing reports are uploaded automatically
month=`date +"%Y-%m"` #Current month

#month=2017-02 #Needed if bill of another month is needed

# Get currency conversion rate from Google
usd=`wget -q -O - "http://www.google.com/finance/converter?a=1&from=USD&to=INR" \
      | grep "currency_converter_result" \
      | sed 's/<[^>]*>//g' \
      | cut -f4 -d ' '`

echo "Conversion Rate: $usd, Tax=$tax% "
echo " "

aws_url="s3://$bucket_name/$account_id-aws-billing-detailed-line-items-with-resources-and-tags-AISPL-$month.csv.zip"

rm -f bill_line_items*

aws s3 cp $aws_url bill_line_items.csv.zip --quiet
unzip -p bill_line_items.csv.zip > bill_line_items.csv

cat bill_line_items.csv | q -T -b -O -H -d "," -w all \
"select \
    \"user:Name\" as Resource_Name, \
    UsageType,\
    strftime('%Y-%m-%d %H:%M', datetime(UsageStartDate,'+5.5 hours')) as Usage_Start_Time, \
    round(Cost*$usd*(1+$tax/100.0),2) as \"Cost+Tax(INR)\"  \
from -  \
where cost<>0 and Rate <> '' \
order by UsageEndDate, Resource_Name"

echo " "

cat bill_line_items.csv | q -T -b -H -d "," -w all \
"select 'Total cost in INR including tax: ' || \
 sum( round(Cost*$usd*(1+$tax/100.0),2)) \
 from - where cost<>0 and Rate <> '' "

Sample output

Conversion Rate: 66.2050, Tax=15% 
 
Resource_Name   UsageType                   Usage_Start_Time    Cost+Tax(INR)
node1       APS3-BoxUsage:t2.large      2017-03-03 16:30    9.06
Hadoop      APS3-BoxUsage:t2.large      2017-03-06 17:30    9.06
Hadoop      APS3-BoxUsage:t2.large      2017-03-06 18:30    9.06
.....
Cloudera1   APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
gw          APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.09
hadoop      APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.23
node1       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
node2       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
node3       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
node4       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
photos_os   APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.09
 
Total cost in INR including tax: 297.81

Working with AWS EC2 instances from aws command line tool

Command to list instances using JMESPath query

aws ec2 describe-instances --query "Reservations[*].Instances[*].[InstanceId, Tags[?Key=='Name'].Value|[0], State.Name, PrivateIpAddress, InstanceType]" --output=table

-------------------------------------------------------------------------------
|                              DescribeInstances                              |
+----------------------+------------+----------+-----------------+------------+
|  i-xxxxxxxxxxxxxxxxx |  node0     |  running |  172.31.11.20   |  t2.micro  |
|  i-yyyyyyyyyyyyyyyyy |  node1     |  stopped |  172.31.11.21   |  t2.large  |
+----------------------+------------+----------+-----------------+------------+

Command to start instances

aws ec2 start-instances --instance-ids i-xxxxxxxxxxxxxxxxx i-yyyyyyyyyyyyyyyyy

Command to stop instances

aws ec2 stop-instances --instance-ids i-xxxxxxxxxxxxxxxxx i-yyyyyyyyyyyyyyyyy