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;
I need to get the client addresses from a website. For e.g. the first client code is 1001 and address should come from
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
The code of “client_details” Lambda function will look something like this…
myurl = ‘http://some_site.com/Details?dest=’
def lambda_handler(event, context):
ret = dict()
res = list()
for argument in event[‘arguments’]:
number = str(argument)
page = requests.get(myurl+number[-10:])
ret[‘success’] = True
except Exception as e:
ret[‘success’] = False
ret[‘error_msg’] = str(e)
ret[‘results’] = res
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.
CREATE OR REPLACE EXTERNAL FUNCTION client_details (number varchar )
RETURNS varchar STABLE
You need to change the IAM role name and the 12 digit account ID mentioned above in the IAM Role.
Powered by WPeMatico