Shantanu's Blog
Database ConsultantOctober 28, 2020
Using Lambda Functions as UDF's in Redshift
Let's assume I have a list of client_codes saved in a redshift table and I need to find the details from an API.
# select client_code from some_table limit 10;client_code |
--------------+
1001 |
2002 |
9009 |
1009 |
1898 |
5465 |
3244 |
5576 |
4389 |
8756 |
(10 rows)
I need to get the client addresses from a website. For e.g. the first client code is 1001 and address should come from
http://some_site.com/Details?dest=1001
This can not be done at SQL query level. You need to loop through an array using Python, PHP, Java etc. You can also write your scripts in AWS Lambda and use them as UDF (User Defined Functions) in Redshift. For e.g.
# select client_code, client_details(client_code) as c_address from some_table limit 10;
client_code | c_address
--------------+---------------------------------------------
1001 | 21,Tamilnadu,
2002 | 14,Madhya Pradesh & Chattisgarh,
9009 | 7,Gujarat,
1009 | 23,Uttar Pradesh (W) & Uttarakhand
1898 | 11,Karnataka
5465 | 3,Bihar & Jharkhand
3244 | 11,Karnataka
5576 | 6,Delhi
4389 | 13,Kolkata
8756 | 11,Karnataka
(10 rows)
The code of "client_details" Lambda function will look something like this...
import json
import requests
myurl = 'http://some_site.com/Details?dest='
def lambda_handler(event, context):
ret = dict()
res = list()
for argument in event['arguments']:
try:
number = str(argument[0])
page = requests.get(myurl+number[-10:])
res.append((page.content).decode('utf-8'))
ret['success'] = True
except Exception as e:
res.append(None)
ret['success'] = False
ret['error_msg'] = str(e)
ret['results'] = res
return json.dumps(ret)
Notes:
1) We are using "requests" module in this code. Since it is not available in AWS Lambda environment, I have added it using this layer...
# Layer: arn:aws:lambda:us-east-1:770693421928:layer:Klayers-python38-requests:9
2) You will also need to increase the timeout of Lambda upto 15 minutes. The API may take more than 3 seconds (default) to respond.
3) You will also have to update the IAM role associated with your Redshift cluster. (Actions - Manage Role) You can add the policy called "AWSLambdaFullAccess" or grant access to a single function as explained in the documentation.
The lambda function needs to be "linked" to Redshift using the "create function" statement like this...
CREATE OR REPLACE EXTERNAL FUNCTION client_details (number varchar )
RETURNS varchar STABLE
LAMBDA 'client_details'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyUnload';
You need to change the IAM role name and the 12 digit account ID mentioned above in the IAM Role.
You can now use your lambda function in your redshift query for e.g.
# select client_code, client_details(client_code) as c_address from some_table limit 10;
You can read more...
# https://aws.amazon.com/blogs/big-data/accessing-external-components-using-amazon-redshift-lambda-udfs/
Labels: aws, aws_lambda, redshift
April 13, 2020
Redshift Spectrum
Athena and redshift both are great database. But there are times when we need a bridge to connect them. For e.g. when we need to join a redshift table with Athena. Redshift spectrum can be used in such cases.
1) Create an IAM role called "RedshiftCopyUnload" using the cloudformation template shown in this example:
https://stackoverflow.com/questions/58816446/template-to-create-iam-role-for-spectrum-s3-access
2) Create database using the Role created in the first step:
create external schema spectrum_schema from data catalog
database 'spectrum_db'
iam_role 'arn:aws:iam::XXX:role/RedshiftCopyUnload'
create external database if not exists;
3) Create external table:
create external table spectrum_schema.testme (number bigint)
row format delimited fields terminated by '|'
stored as textfile
location 's3://texport/c_pincode_data/';
4) Create internal table native to redshift using select query on external table:
create table mypincode as select * from spectrum_schema.testme limit 10;
1) Create an IAM role called "RedshiftCopyUnload" using the cloudformation template shown in this example:
https://stackoverflow.com/questions/58816446/template-to-create-iam-role-for-spectrum-s3-access
2) Create database using the Role created in the first step:
create external schema spectrum_schema from data catalog
database 'spectrum_db'
iam_role 'arn:aws:iam::XXX:role/RedshiftCopyUnload'
create external database if not exists;
3) Create external table:
create external table spectrum_schema.testme (number bigint)
row format delimited fields terminated by '|'
stored as textfile
location 's3://texport/c_pincode_data/';
4) Create internal table native to redshift using select query on external table:
create table mypincode as select * from spectrum_schema.testme limit 10;
March 27, 2020
Redshift log of user queries to S3
Here is an interesting blog post about analyzing redshift queries.
https://thedataguy.in/reconstruct-redshift-stl-querytext-using-aws-athena/
The author has suggested to use tables like STL_QUERYTEXT that saves only 2 to 5 days of data.
If you need to save all the queries and keep them for years, follow these steps:
a) Create a new parameter group and name it something like "with_logs".
b) Set "enable_user_activity_logging" to "true" in that parameter group.
c) Use the newly created parameter group while creating redshift cluster.
Once the logs are generated, you can download them and study the queries executed by the users.
1) Download log files for any given day:
aws s3 sync s3://logredshift/mycompanylogs/AWSLogs/1234567890/redshift/us-east-1/2020/03/27/ .
2) Extract:
gunzip *
3) Remove windows line breaks:
dos2unix *
4) Remove linux line breaks:
cat *useractivitylog* | tr '\n' ' ' | sed "s/\('[0-9]\{4\}\)/\r\n\1/g" > mylog.txt
5) Select query text:
cat mylog.txt | awk -F 'LOG:' '{print $2}' | sort -u > to_study.txt
6) Study the queries:
cat to_study.txt | sed '0~1 a\\' | more
This includes the system generated queries as well. Therefore this log may be difficult to analyze.
_____
1) Download and install latest version of pgbadger utility from:
https://github.com/darold/pgbadger/releases
2) create a new directory
mkdir /tmp/todel/
cd /tmp/todel/
3) Download the logs for a month. For e.g. March 2020
aws s3 sync s3://alogredshift/AWSLogs/1234567890/redshift/us-east-1/2020/03/ .
4) Analyze
pgbadger --format redshift `find /tmp/todel/ -name "*tylog*"` --dbname vadb --outfile /tmp/myq1.txt"
Or use docker:
docker run -i --rm -v $(pwd):/workdir -v /tmp/:/tmp/ shantanuo/pgbadger --format redshift find /tmp/todel/ -name "*tylog*" --dbname vdb --exclude-query 'FROM pg_' --outfile /tmp/myq123xx2.txt
Or use --dump-all-queries to get all queries in non-normalized form.
https://thedataguy.in/reconstruct-redshift-stl-querytext-using-aws-athena/
The author has suggested to use tables like STL_QUERYTEXT that saves only 2 to 5 days of data.
If you need to save all the queries and keep them for years, follow these steps:
a) Create a new parameter group and name it something like "with_logs".
b) Set "enable_user_activity_logging" to "true" in that parameter group.
c) Use the newly created parameter group while creating redshift cluster.
Once the logs are generated, you can download them and study the queries executed by the users.
1) Download log files for any given day:
aws s3 sync s3://logredshift/mycompanylogs/AWSLogs/1234567890/redshift/us-east-1/2020/03/27/ .
2) Extract:
gunzip *
3) Remove windows line breaks:
dos2unix *
4) Remove linux line breaks:
cat *useractivitylog* | tr '\n' ' ' | sed "s/\('[0-9]\{4\}\)/\r\n\1/g" > mylog.txt
5) Select query text:
cat mylog.txt | awk -F 'LOG:' '{print $2}' | sort -u > to_study.txt
6) Study the queries:
cat to_study.txt | sed '0~1 a\\' | more
This includes the system generated queries as well. Therefore this log may be difficult to analyze.
_____
1) Download and install latest version of pgbadger utility from:
https://github.com/darold/pgbadger/releases
2) create a new directory
mkdir /tmp/todel/
cd /tmp/todel/
3) Download the logs for a month. For e.g. March 2020
aws s3 sync s3://alogredshift/AWSLogs/1234567890/redshift/us-east-1/2020/03/ .
4) Analyze
pgbadger --format redshift `find /tmp/todel/ -name "*tylog*"` --dbname vadb --outfile /tmp/myq1.txt"
Or use docker:
docker run -i --rm -v $(pwd):/workdir -v /tmp/:/tmp/ shantanuo/pgbadger --format redshift find /tmp/todel/ -name "*tylog*" --dbname vdb --exclude-query 'FROM pg_' --outfile /tmp/myq123xx2.txt
Or use --dump-all-queries to get all queries in non-normalized form.
Labels: redshift
March 04, 2020
Redshift UDF to remove salutations
There are times when I need to remove the salutations like mr or mrs. from the name column in redshift. I can write a user defined function that will do the needful.
# select f_extract_name2('mr shantanu oak');
f_extract_name2
-----------------
SHANTANU OAK
(1 row)
The function is written in python and source code will look like this...
CREATE OR REPLACE FUNCTION f_extract_name2 (myname varchar(1000) ) RETURNS varchar(1000) IMMUTABLE as $$
try:
remove_list=['MR', 'MR.', 'MRS.', 'MRS', 'MISS', 'MASTER', 'MISS.', 'MASTER.' ]
new_list=list()
for i in myname.upper().split():
if i not in remove_list:
new_list.append(i)
if len(new_list) == 2:
return (" ".join(new_list))
except:
pass
$$ LANGUAGE plpythonu
# select f_extract_name2('mr shantanu oak');
f_extract_name2
-----------------
SHANTANU OAK
(1 row)
The function is written in python and source code will look like this...
CREATE OR REPLACE FUNCTION f_extract_name2 (myname varchar(1000) ) RETURNS varchar(1000) IMMUTABLE as $$
try:
remove_list=['MR', 'MR.', 'MRS.', 'MRS', 'MISS', 'MASTER', 'MISS.', 'MASTER.' ]
new_list=list()
for i in myname.upper().split():
if i not in remove_list:
new_list.append(i)
if len(new_list) == 2:
return (" ".join(new_list))
except:
pass
$$ LANGUAGE plpythonu
February 19, 2020
connect to redshift and import data into pandas
There are 2 ways to connect to redshift server and get the data into pandas dataframe. Use the module "sqlalchemy" or "psycopg2". As you can see, sqlalchemy is using psycopg2 module internally.
from sqlalchemy import create_engine
pg_engine = create_engine(
"postgresql+psycopg2://%s:%s@%s:%i/%s" % (myuser, mypasswd, myserver, int(myport), mydbname)
)
my_query = "select * from some_table limit 100”
df = pd.read_sql(my_query, con=pg_engine)
since "create_engine" class can also be used to connect to mysql database, it is recommended for the sake of consistency.
_____
#!pip install psycopg2-binary
import psycopg2
pconn = psycopg2.connect("host=myserver port=myport dbname=mydbname user=myuser password=mypasswd")
my_query = "select * from some_table limit 100”
cur = pconn.cursor()
cur.execute(my_query)
mydict = cur.fetchall()
import pandas as pd
df = pd.DataFrame(mydict)
from sqlalchemy import create_engine
pg_engine = create_engine(
"postgresql+psycopg2://%s:%s@%s:%i/%s" % (myuser, mypasswd, myserver, int(myport), mydbname)
)
my_query = "select * from some_table limit 100”
df = pd.read_sql(my_query, con=pg_engine)
since "create_engine" class can also be used to connect to mysql database, it is recommended for the sake of consistency.
_____
#!pip install psycopg2-binary
import psycopg2
pconn = psycopg2.connect("host=myserver port=myport dbname=mydbname user=myuser password=mypasswd")
my_query = "select * from some_table limit 100”
cur = pconn.cursor()
cur.execute(my_query)
mydict = cur.fetchall()
import pandas as pd
df = pd.DataFrame(mydict)
Labels: aws, pandas, python, redshift
Import csv data and convert it to parquet in athena
# Let's assume we have a large file that we need to import in athena, here are the commands to be used.
# gunzip -c panindia_pincode.csv.gz | head
"1","110016","DELHI","DELHI","","","",""
"2","110027","DELHI","DELHI","","","",""
"3","110062","DELHI","DELHI","","","",""
# create a table in athena
CREATE EXTERNAL TABLE pandindia_pincode (
serial_number string,
pincode_number string,
client_city string,
client_state string,
dummy1 string,
dummy2 string,
dummy3 string,
dummy4 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'serialization.format' = ',',
'field.delim' = ',',
"quoteChar" = "\""
)
LOCATION 's3://datameetgeo/pincode/'
TBLPROPERTIES ('has_encrypted_data'='false');
## create parquet file format table
CREATE TABLE default.pandindia_pincode_parq
with (format='PARQUET', external_location='s3://datameetgeo/parquetpincode/'
) AS
SELECT * FROM default.pandindia_pincode
# gunzip -c panindia_pincode.csv.gz | head
"1","110016","DELHI","DELHI","","","",""
"2","110027","DELHI","DELHI","","","",""
"3","110062","DELHI","DELHI","","","",""
# create a table in athena
CREATE EXTERNAL TABLE pandindia_pincode (
serial_number string,
pincode_number string,
client_city string,
client_state string,
dummy1 string,
dummy2 string,
dummy3 string,
dummy4 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'serialization.format' = ',',
'field.delim' = ',',
"quoteChar" = "\""
)
LOCATION 's3://datameetgeo/pincode/'
TBLPROPERTIES ('has_encrypted_data'='false');
## create parquet file format table
CREATE TABLE default.pandindia_pincode_parq
with (format='PARQUET', external_location='s3://datameetgeo/parquetpincode/'
) AS
SELECT * FROM default.pandindia_pincode
February 16, 2020
Using Python in Redshift
Here is a python function that can be
installed in Redshift. It will normalize the text by removing junk characters
and non-essential strings.
CREATE OR REPLACE FUNCTION
f_file_split (mystr varchar(1000) ) RETURNS varchar(1000) IMMUTABLE as $$
try:
import
itertools
mylist=list()
if
mystr:
for i in mystr[:100].split("_"):
for x in i.split("-"):
for y in x.split("/"):
mylist.append(y.split("."))
news = '
'.join(itertools.chain(*mylist))
newlist=list()
stopwords = ['sanstha', 'vikas', 'society', 'seva', 'json']
for i in
news.split():
if len(i) < 4 or i in stopwords or i.isdigit() or i.startswith('bnk')
or not i.isalpha() :
pass
else:
newlist.append(i.lower().replace('vkss',
'').replace('vks',''))
return '
'.join(set(newlist))
except:
pass
$$ LANGUAGE plpythonu
I can add a new column in the table
and populate that column with transformed values.
alter table final_details add column
branch_name_fuzzy varchar(500);
update final_details set
branch_name_fuzzy = f_file_split(filename);
February 06, 2020
Manage redshift cluster using boto
Save or restore from last snapshot and delete the running redshift cluster are the two important activities those are possible using this boto code.
import boto
import datetime
conn = boto.connect_redshift(aws_access_key_id='XXX', aws_secret_access_key='XXX')
mymonth = datetime.datetime.now().strftime("%b").lower()
myday = datetime.datetime.now().strftime("%d")
myvar = mymonth+myday+'-v-mar5-dreport-new'
# take snapshot and delete cluster
mydict=conn.describe_clusters()
myidentifier=mydict['DescribeClustersResponse']['DescribeClustersResult']['Clusters'][0]['ClusterIdentifier']
conn.delete_cluster(myidentifier, skip_final_cluster_snapshot=False, final_cluster_snapshot_identifier=myvar)
# Restore from the last snapshot
response = conn.describe_cluster_snapshots()
snapshots = response['DescribeClusterSnapshotsResponse']['DescribeClusterSnapshotsResult']['Snapshots']
snapshots.sort(key=lambda d: d['SnapshotCreateTime'])
mysnapidentifier = snapshots[-1]['SnapshotIdentifier']
conn.restore_from_cluster_snapshot('v-mar5-dreport-new', mysnapidentifier, availability_zone='us-east-1a')
import boto
import datetime
conn = boto.connect_redshift(aws_access_key_id='XXX', aws_secret_access_key='XXX')
mymonth = datetime.datetime.now().strftime("%b").lower()
myday = datetime.datetime.now().strftime("%d")
myvar = mymonth+myday+'-v-mar5-dreport-new'
# take snapshot and delete cluster
mydict=conn.describe_clusters()
myidentifier=mydict['DescribeClustersResponse']['DescribeClustersResult']['Clusters'][0]['ClusterIdentifier']
conn.delete_cluster(myidentifier, skip_final_cluster_snapshot=False, final_cluster_snapshot_identifier=myvar)
# Restore from the last snapshot
response = conn.describe_cluster_snapshots()
snapshots = response['DescribeClusterSnapshotsResponse']['DescribeClusterSnapshotsResult']['Snapshots']
snapshots.sort(key=lambda d: d['SnapshotCreateTime'])
mysnapidentifier = snapshots[-1]['SnapshotIdentifier']
conn.restore_from_cluster_snapshot('v-mar5-dreport-new', mysnapidentifier, availability_zone='us-east-1a')
Labels: aws, linux tips, redshift, shell script
October 02, 2019
Redshift VPC with subnet issue
I was trying to launch an Amazon Redshift cluster in a specific VPC, but I could not select any VPC in the drop-down list. I checked that a VPC exist (though it's not default one!)
In order to resolve this, follow these 3 steps to create a cluster subnet group.
1) click "Security" from redshift console.
2) On the Subnet Groups tab, click Create Cluster Subnet Group.
3) Specify a Name, Description, VPC ID and Click add "all the subnets" link.
In order to resolve this, follow these 3 steps to create a cluster subnet group.
1) click "Security" from redshift console.
2) On the Subnet Groups tab, click Create Cluster Subnet Group.
3) Specify a Name, Description, VPC ID and Click add "all the subnets" link.
October 01, 2019
Load parquet data into redshift table
Here is the copy command to load data from parquet file format to existing table in redshift.
copy favoritemovies from 'S3://some_bucket/parquet_file/'
iam_role 'arn:aws:iam::0123456789012:role/RedshiftCopyUnload'
The IAM role needs to be created using the steps mentioned below:
To create an IAM role to allow Amazon Redshift to access AWS services
1) Open the IAM Console.
2) In the navigation pane, choose Roles.
3) Choose Create role.
4) Choose AWS service, and then choose Redshift.
5) Under Select your use case, choose Redshift - Customizable and then choose Next: Permissions.
6) The Attach permissions policy page appears. choose
AmazonS3ReadOnlyAccess, AWSGlueConsoleFullAccess and AmazonAthenaFullAccess.
7) Choose Next: Tags.
8) Choose Next: Review.
9) For Role name, type a name for your role, for example RedshiftCopyUnload. Choose Create role.
Now you need to attach the role to the current cluster.
1) Sign in to the AWS Management Console and open the Amazon Redshift console
2) In the navigation pane, choose Clusters.
3) In the list, choose the cluster that you want to manage IAM role associations for. Choose Manage IAM Roles.
4) Select your IAM role from the Available roles list to associate or remove current one.
5) choose Apply Changes to update the IAM roles that are associated with the cluster.
1) https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html
2) https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html#copy-unload-iam-role-associating-with-clusters
copy favoritemovies from 'S3://some_bucket/parquet_file/'
iam_role 'arn:aws:iam::0123456789012:role/RedshiftCopyUnload'
The IAM role needs to be created using the steps mentioned below:
To create an IAM role to allow Amazon Redshift to access AWS services
1) Open the IAM Console.
2) In the navigation pane, choose Roles.
3) Choose Create role.
4) Choose AWS service, and then choose Redshift.
5) Under Select your use case, choose Redshift - Customizable and then choose Next: Permissions.
6) The Attach permissions policy page appears. choose
AmazonS3ReadOnlyAccess, AWSGlueConsoleFullAccess and AmazonAthenaFullAccess.
7) Choose Next: Tags.
8) Choose Next: Review.
9) For Role name, type a name for your role, for example RedshiftCopyUnload. Choose Create role.
Now you need to attach the role to the current cluster.
1) Sign in to the AWS Management Console and open the Amazon Redshift console
2) In the navigation pane, choose Clusters.
3) In the list, choose the cluster that you want to manage IAM role associations for. Choose Manage IAM Roles.
4) Select your IAM role from the Available roles list to associate or remove current one.
5) choose Apply Changes to update the IAM roles that are associated with the cluster.
1) https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html
2) https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html#copy-unload-iam-role-associating-with-clusters
November 10, 2017
access redshift through python pandas
Postgresql (or redshift) can be accessed trough python pandas just like mysql. Python needs psycopg2 module pre-installed for this code to work.
from sqlalchemy import create_engine
pg_engine = create_engine('postgresql+psycopg2://root:XXX@xxx.xxx.ap-south-1.redshift.amazonaws.com:5439/dbname')
Once the engine is created, I can use the object to read data from a table and create dataframe like this...
my_query= 'select id, full_name from customer '
df=pd.read_sql( my_query , con=pg_engine)
from sqlalchemy import create_engine
pg_engine = create_engine('postgresql+psycopg2://root:XXX@xxx.xxx.ap-south-1.redshift.amazonaws.com:5439/dbname')
Once the engine is created, I can use the object to read data from a table and create dataframe like this...
my_query= 'select id, full_name from customer '
df=pd.read_sql( my_query , con=pg_engine)
Labels: aws, pandas, python, redshift
April 29, 2016
Finding the most relevant values for a given list
Let's assume we have a list of "good" numbers and we need to find the relevance of a number for e.g. 103 to this list. In this case the weight of 103 in the context of the given list is 3.94 We can calculate the weight of all numbers, sort and take top 4 or 5
Here is how to generate test data for this exercise...
mylist=[100, 101, 102, 104, 105, 106, 107, 220, 221, 289, 290, 542, 544 ]
import pandas as pd
columnA=pd.DataFrame(mylist)
secondlist=[103, 299, 999, 108, 543]
import pandas as pd
row1=pd.DataFrame(secondlist)
for i in row1[0]:
columnA[i] = 1/ abs(columnA[0] - i)
# dataframe looks like this excel sheet
columnA
mypd=columnA.sum()
mypd.sort_values(inplace=True,ascending=False)
mypd
0 2831.000000
103 3.948958
108 2.551252
543 2.030021
299 0.280611
999 0.017591
dtype: float64
Labels: pandas, python, redshift, usability
March 26, 2016
Write your own slack command
You can write lambda funciton that will handle a Slack slash command and echoes the details back to the user.
Follow these steps to configure the slash command in Slack:
1. Navigate to https://.slack.com/services/new
2. Search for and select "Slash Commands".
3. Enter a name for your command and click "Add Slash Command Integration".
4. Copy the token string from the integration settings and use it in the next section.
5. After you complete this blueprint, enter the provided API endpoint URL in the URL field.
_____
Lambda Function
Create a new function using a blueprint called "slack-echo-command-python". The only change is to comment the encryption line and declare the variable...
#kms = boto3.client('kms')
#expected_token = kms.decrypt(CiphertextBlob = b64decode(ENCRYPTED_EXPECTED_TOKEN))['Plaintext']
expected_token = 'A9sU70Lz4isPdTet5tvGD0PB'
You will get this token when you registered a new keyword at slack.
_____
API Gateway
Create an AIP - LambdaMicroservice
Actions - Create Resource - getme
Actions - Create Method - post
Integration type - Lambda Function
Lambda Function: Select function name getme
Integration Request - Body Mapping Templates - Add mapping template - application/x-www-form-urlencoded
Mapping template - {"body":$input.json("$")}
Deploy API - Stage name - prod
_____
Connect API to Lambda Function and slack slash command:
Add the method name to invoke url. If the invoke URL looks like this...
https://sevbnlvu69.execute-api.us-east-1.amazonaws.com/prod
Then the actual URL to be added in "API endpoints" tab of function - getme will be:
https://sevbnlvu69.execute-api.us-east-1.amazonaws.com/prod/getme
Follow these steps to configure the slash command in Slack:
1. Navigate to https://
2. Search for and select "Slash Commands".
3. Enter a name for your command and click "Add Slash Command Integration".
4. Copy the token string from the integration settings and use it in the next section.
5. After you complete this blueprint, enter the provided API endpoint URL in the URL field.
_____
Lambda Function
Create a new function using a blueprint called "slack-echo-command-python". The only change is to comment the encryption line and declare the variable...
#kms = boto3.client('kms')
#expected_token = kms.decrypt(CiphertextBlob = b64decode(ENCRYPTED_EXPECTED_TOKEN))['Plaintext']
expected_token = 'A9sU70Lz4isPdTet5tvGD0PB'
You will get this token when you registered a new keyword at slack.
_____
API Gateway
Create an AIP - LambdaMicroservice
Actions - Create Resource - getme
Actions - Create Method - post
Integration type - Lambda Function
Lambda Function: Select function name getme
Integration Request - Body Mapping Templates - Add mapping template - application/x-www-form-urlencoded
Mapping template - {"body":$input.json("$")}
Deploy API - Stage name - prod
_____
Connect API to Lambda Function and slack slash command:
Add the method name to invoke url. If the invoke URL looks like this...
https://sevbnlvu69.execute-api.us-east-1.amazonaws.com/prod
Then the actual URL to be added in "API endpoints" tab of function - getme will be:
https://sevbnlvu69.execute-api.us-east-1.amazonaws.com/prod/getme
Labels: aws, aws_lambda, boto, linux tips, pandas, python, redshift, shell script, usability
March 25, 2016
Machine Learning
In order to let the machine do predictions for us, we need to first train the computers with some data. We will download the test data from here...
https://s3.amazonaws.com/aml-sample-data/banking.csv
This data looks something like this once opened in excel.
The software will learn the pattern from the above data. It will read the data like this...
Someone who is 28 years old and from Management job is likely to say yes to a bank loan. The married older people may likely say "no" (say_yes: 0).
Now when a new customer walks in, we can ask the machine first if he is going to buy the banking product or not.
This customer is most likely going to say "No" (Predicted label: 0) as per what machine has learned from other customers so far.
http://docs.aws.amazon.com/machine-learning/latest/dg/step-5-use-the-ml-model-to-create-batch-predictions.html
Machine learning can solve very complex problems those are very difficult to answer using standard SQL queries.
https://s3.amazonaws.com/aml-sample-data/banking.csv
This data looks something like this once opened in excel.
age job marital education default housing loan contact month day_of_week duration campaign pdays previous poutcome emp_var_rate cons_price_idx cons_conf_idx euribor3m nr_employed say_yes
44 blue-collar married basic.4y unknown yes no cellular aug thu 210 1 999 0 nonexistent 1.4 93.444 -36.1 4.963 5228.1 0
53 technician married unknown no no no cellular nov fri 138 1 999 0 nonexistent -0.1 93.2 -42 4.021 5195.8 0
28 management single university.degree no yes no cellular jun thu 339 3 6 2 success -1.7 94.055 -39.8 0.729 4991.6 1
The software will learn the pattern from the above data. It will read the data like this...
Someone who is 28 years old and from Management job is likely to say yes to a bank loan. The married older people may likely say "no" (say_yes: 0).
Now when a new customer walks in, we can ask the machine first if he is going to buy the banking product or not.
32,services,divorced,basic.9y,no,unknown,yes,cellular,dec,mon,110,1,11,0,nonexistent,-1.8,94.465,-36.1,0.883,5228.1
This customer is most likely going to say "No" (Predicted label: 0) as per what machine has learned from other customers so far.
http://docs.aws.amazon.com/machine-learning/latest/dg/step-5-use-the-ml-model-to-create-batch-predictions.html
Machine learning can solve very complex problems those are very difficult to answer using standard SQL queries.
Labels: aws, aws_lambda, boto, pandas, python, redshift, shell script, usability
March 09, 2016
Log of redshift copy command
We can get the log of copy commands those were executed successfully (aborted=0) using the table STL_query Write a function to extract data from query text column and get the results of which table was populated with which data.
drop FUNCTION f_tname(mystr VARCHAR(65000));
CREATE FUNCTION f_tname(mystr VARCHAR(65000))
RETURNS varchar(65000)
IMMUTABLE AS $$
return mystr.split()[1]+","+mystr.split()[3].split('/')[-2:-1][0]
$$ LANGUAGE plpythonu;
create table importlog as
select querytxt, starttime, endtime from STL_query where database = 'vdb' and querytxt like 'copy%' and aborted = 0
select split_part(f_tname(querytxt), ',', 1), split_part(f_tname(querytxt), ',', 1) from importlog
drop FUNCTION f_tname(mystr VARCHAR(65000));
CREATE FUNCTION f_tname(mystr VARCHAR(65000))
RETURNS varchar(65000)
IMMUTABLE AS $$
return mystr.split()[1]+","+mystr.split()[3].split('/')[-2:-1][0]
$$ LANGUAGE plpythonu;
create table importlog as
select querytxt, starttime, endtime from STL_query where database = 'vdb' and querytxt like 'copy%' and aborted = 0
select split_part(f_tname(querytxt), ',', 1), split_part(f_tname(querytxt), ',', 1) from importlog
Labels: aws, python, redshift, usability
March 03, 2016
Installing adminer and ipython on a new server
Here are 3 steps to start an EC2 instance based on Amazon Linux
import easyboto
dev=easyboto.connect('XXX', 'XXX')
dev.placement='us-east-1a'
dev.key='dec15a'
dev.startEc2('ami-da4d7cb0', 't2.medium')
This will return an IP address of the newly created server and you can connect using the standard ssh command that will look something like this...
ssh -i dec15a.pem ec2-user@52.20.10.47
You should have dec15a.pem or dec15a.ppk (for putty) file in order to connect.
_____
Once you are connected to newly created server, install docker...
## install and start docker deamon
yum install docker
etc/init.d/docker start
_____
## python with notebook can be installed using these 3 commands:
docker run -it -p 7778:7778 --hostname conda shantanuo/miniconda_ipython /bin/bash
cd /home/
ipython notebook --ip=* --port=7778
## adminer can be installed using these 2 commands:
docker run -it -p 80:80 --hostname adminer shantanuo/adminer
sudo service apache2 start
import easyboto
dev=easyboto.connect('XXX', 'XXX')
dev.placement='us-east-1a'
dev.key='dec15a'
dev.startEc2('ami-da4d7cb0', 't2.medium')
This will return an IP address of the newly created server and you can connect using the standard ssh command that will look something like this...
ssh -i dec15a.pem ec2-user@52.20.10.47
You should have dec15a.pem or dec15a.ppk (for putty) file in order to connect.
_____
Once you are connected to newly created server, install docker...
## install and start docker deamon
yum install docker
etc/init.d/docker start
_____
## python with notebook can be installed using these 3 commands:
docker run -it -p 7778:7778 --hostname conda shantanuo/miniconda_ipython /bin/bash
cd /home/
ipython notebook --ip=* --port=7778
## adminer can be installed using these 2 commands:
docker run -it -p 80:80 --hostname adminer shantanuo/adminer
sudo service apache2 start
Labels: aws, boto, docker, pandas, redshift, shell script, usability
January 20, 2016
Lambda function to open port
I can create an API that will take the current IP of requesting client and add that IP address to a security group to open up certain ports.
Integration request:
content-type: application/json
Template:
{
"sourceip": "$context.identity.sourceIp"
}
The lambda function will look something like this...
def lambda_handler(event, context):
from boto3.session import Session
session = Session(aws_access_key_id='XXX', aws_secret_access_key='YYY')
ec2 = session.resource('ec2')
security_group = ec2.SecurityGroup('sg-f682c78f')
myip=event['sourceip']+"/32"
security_group.authorize_ingress(IpProtocol="tcp",CidrIp=myip,FromPort=80,ToPort=80)
return (event['sourceip'])
Integration request:
content-type: application/json
Template:
{
"sourceip": "$context.identity.sourceIp"
}
The lambda function will look something like this...
def lambda_handler(event, context):
from boto3.session import Session
session = Session(aws_access_key_id='XXX', aws_secret_access_key='YYY')
ec2 = session.resource('ec2')
security_group = ec2.SecurityGroup('sg-f682c78f')
myip=event['sourceip']+"/32"
security_group.authorize_ingress(IpProtocol="tcp",CidrIp=myip,FromPort=80,ToPort=80)
return (event['sourceip'])
Labels: aws, aws_lambda, boto, redshift, usability
September 12, 2015
Redshift support of User Defined functions
The following example creates a function that compares two numbers and returns the larger value. Note that the indentation of the code between the double dollar signs ($$) is a Python requirement.
create function f_greater(a float, b float)
returns float
stable
as $$
if a > b:
return a
return b
$$ language plpythonu;
The following query calls the new f_greater function to query the SALES table and return either COMMISSION or 20 percent of PRICEPAID, whichever is greater:
select f_greater(commission, pricepaid*0.20) from sales;
# Z-score calculation
create function f_z_test_by_pval (alpha float, x_bar float, test_val float, sigma float, n float)
RETURNS varchar
STABLE
AS $$
import scipy.stats as st
import math as math
z = (x_bar - test_val) / (sigma / math.sqrt(n))
p = st.norm.cdf(z)
if p <= alpha:
return 'Statistically significant'
else:
return 'May have occurred by random chance'
$$LANGUAGE plpythonu;
# string function
create function
f_return_focused_specialty(full_physician_specialty varchar)
RETURNS varchar
STABLE
AS $$
parts = full_physician_specialty.split("/")
return parts[len(parts) - 1].strip()
$$LANGUAGE plpythonu
create function f_greater(a float, b float)
returns float
stable
as $$
if a > b:
return a
return b
$$ language plpythonu;
The following query calls the new f_greater function to query the SALES table and return either COMMISSION or 20 percent of PRICEPAID, whichever is greater:
select f_greater(commission, pricepaid*0.20) from sales;
# Z-score calculation
create function f_z_test_by_pval (alpha float, x_bar float, test_val float, sigma float, n float)
RETURNS varchar
STABLE
AS $$
import scipy.stats as st
import math as math
z = (x_bar - test_val) / (sigma / math.sqrt(n))
p = st.norm.cdf(z)
if p <= alpha:
return 'Statistically significant'
else:
return 'May have occurred by random chance'
$$LANGUAGE plpythonu;
# string function
create function
f_return_focused_specialty(full_physician_specialty varchar)
RETURNS varchar
STABLE
AS $$
parts = full_physician_specialty.split("/")
return parts[len(parts) - 1].strip()
$$LANGUAGE plpythonu
Labels: python, redshift, usability
Archives
June 2001 July 2001 January 2003 May 2003 September 2003 October 2003 December 2003 January 2004 February 2004 March 2004 April 2004 May 2004 June 2004 July 2004 August 2004 September 2004 October 2004 November 2004 December 2004 January 2005 February 2005 March 2005 April 2005 May 2005 June 2005 July 2005 August 2005 September 2005 October 2005 November 2005 December 2005 January 2006 February 2006 March 2006 April 2006 May 2006 June 2006 July 2006 August 2006 September 2006 October 2006 November 2006 December 2006 January 2007 February 2007 March 2007 April 2007 June 2007 July 2007 August 2007 September 2007 October 2007 November 2007 December 2007 January 2008 February 2008 March 2008 April 2008 July 2008 August 2008 September 2008 October 2008 November 2008 December 2008 January 2009 February 2009 March 2009 April 2009 May 2009 June 2009 July 2009 August 2009 September 2009 October 2009 November 2009 December 2009 January 2010 February 2010 March 2010 April 2010 May 2010 June 2010 July 2010 August 2010 September 2010 October 2010 November 2010 December 2010 January 2011 February 2011 March 2011 April 2011 May 2011 June 2011 July 2011 August 2011 September 2011 October 2011 November 2011 December 2011 January 2012 February 2012 March 2012 April 2012 May 2012 June 2012 July 2012 August 2012 October 2012 November 2012 December 2012 January 2013 February 2013 March 2013 April 2013 May 2013 June 2013 July 2013 September 2013 October 2013 January 2014 March 2014 April 2014 May 2014 July 2014 August 2014 September 2014 October 2014 November 2014 December 2014 January 2015 February 2015 March 2015 April 2015 May 2015 June 2015 July 2015 August 2015 September 2015 January 2016 February 2016 March 2016 April 2016 May 2016 June 2016 July 2016 August 2016 September 2016 October 2016 November 2016 December 2016 January 2017 February 2017 April 2017 May 2017 June 2017 July 2017 August 2017 September 2017 October 2017 November 2017 December 2017 February 2018 March 2018 April 2018 May 2018 June 2018 July 2018 August 2018 September 2018 October 2018 November 2018 December 2018 January 2019 February 2019 March 2019 April 2019 May 2019 July 2019 August 2019 September 2019 October 2019 November 2019 December 2019 January 2020 February 2020 March 2020 April 2020 May 2020 July 2020 August 2020 September 2020 October 2020 December 2020 January 2021 April 2021 May 2021 July 2021 September 2021 March 2022 October 2022 November 2022 March 2023 April 2023 July 2023 September 2023 October 2023 November 2023 April 2024 May 2024 June 2024 August 2024 September 2024 October 2024 November 2024 December 2024 January 2025 February 2025 April 2025 June 2025 July 2025 August 2025 November 2025 March 2026 July 2026
