How to Call AWS API from Snowflake

My Snowflake free account expires tomorrow. So I thought I should get the external integration happening today.

Following the length instructions from Snowflake turns out not to be enough. I got stuck at the input and output parameter formats between SF and the Lambda function. I finally got it working and here they are:

The Lambda function in Python:

import json

print('Loading function')

dict={2000:"Sydney", 2218:"Allawah", 3000 : "Melbourne"}

def lambda_handler(event, context):

    print(f"event={event}")

    # normal channel the event looks like: {'body': '{ "data": [ [ 0, 43, "page" ], [ 1, 42, "life, the universe, and everything" ] ] }'}

    # from SF, event looks like: {'data': [[0, 2000]]}

    if "body" in event:

        postcode=event["body"]["data"][0][1]

    else:

        postcode=event["data"][0][1]

    city="unknown"

    print(f"postcode={postcode}")

    if postcode in dict:

        city=dict[postcode]

    output=[0, city]

   

    array_of_rows_to_return = [ ]

    array_of_rows_to_return.append(output)

    json_compatible_string_to_return = json.dumps({'data' : array_of_rows_to_return})

   

    return {'data': array_of_rows_to_return}

The Snowflake side:

create or replace external function postcode_to_city(postcode int)

    returns array

    api_integration = demonstration_external_api_integration_01

    as 'https://....execute-api.ap-southeast-2.amazonaws.com/dev';

select postcode_to_city(2000) as city;

The result:

Dell Alienware Laptop Freezes

 The hand-me-down laptop from my son is randomly freezing for several seconds. The Dell community recons that the culprit is the Dell Support Assist. So to solve this problem: stop the SupportAssistAgent service in Task Manager, or better still, remove the SupportAssist altogether. However, after doing the above I found that the problem still persists. So I stopped a few more services:

• DDVDataCollector - Dell Data Vault Collector
• DDVRulesProcessor - Dell Data Vault Processor
• DDVCollectorSvcApi - Dell Data Vault Service API

That seems to have solved my problem. Basically stop everything from Dell!

This problem really reflects badly on Dell - such basic problem yet happens on so many  machines. Also, the Alienware laptop has very flimsy keys and buttons on its keyboard and touchpad. Five of the keys are broken for far. Such disappointment. 

COBOL DataSet EBIDIC to ASCII Converter

 For some reason the characters (alpha, numeric) are prepended with either 'C' or 'B' in the mainframe file. Special characters (e.g. '@') are just single byte without the above extra characters.

The following script handles the characters well. It does not handle numbers (integers, floating points, signed, unsigned, etc.) because without a copybook, it is impossible to convert it correctly. I leave that as the next challenge.

import codecs

import sys

# Usage instruction: python3 converter.py FILENAME

filename = sys.argv[1]

# EBCDIC format in those files are not standard

# General method

# Read in 2 bytes

# Look at first byte

# C means capital or number

# B means lowercase

# Is a number if 2nd byte is >= 0xb0 (b0 is '0') for whatever reason

# Lookup dict for special characters

# {EBCDIC_code : ascii_char}

hexLookup = {

    0x40 : ' ',

    0x4b : '.',

    0x6b : ',',

    0x7c : '@',

    0x61 : '/',

    0x80 : '{',

    0x90 : '}',

}

# Number of characters on each line

LINEWIDTH = 999

with codecs.open(filename, 'rb') as input, open('converted.txt', 'w', encoding='utf-8') as output:

    lineLength = LINEWIDTH

    while True:

        lineLength -= 1

        ebcdicChar = input.read(1) # Read 1 bytes at a time

        unicodeChar = codecs.decode(ebcdicChar, "cp500")

        if not ebcdicChar: # Finish when no more chars

            break

       

        #actualChar = ' '

        actualChar=unicodeChar

        if unicodeChar == 'C':

            actualChar = input.read(1)

            # If meant to be a number

            if 0xb0 <= actualChar[0] <= 0xb9:

                actualChar = str(actualChar[0] - 0xb0)

            # For special characters

            #elif actualChar[0] in hexLookup:

            #    actualChar = hexLookup[actualChar[0]]

            else:

                actualChar = codecs.decode(actualChar, 'cp500').upper()

        elif unicodeChar == 'B':

            actualChar = codecs.decode(input.read(1), 'cp500')

        elif ebcdicChar[0] in hexLookup:

            actualChar = hexLookup[ebcdicChar[0]]

           

        unicodeContent = codecs.encode(actualChar, 'utf-8')

       

        output.write(unicodeContent.decode('utf-8'))

       

        if lineLength == 0:

            output.write('\n')

            lineLength = LINEWIDTH

A few days later, I realised why I got the strange file - the person who created the file used Notepad++ copy and paste (ctrl c, ctrl v). This basically turns the binary data into utf-16 (by the looks of it). By the way Notepad++ does support binary copy and paste. What a waste of time!

Chat GPT-3.5 vs. New Bing

 A few days ago, I had a very specific query based on the following situation that I was facing: currently my system is using DataTools API to validate addresses. A new requirement has popped up to be able to tell whether a given address is residential or business. 

Being an animal of habits, the first action was of course to search on Google. Of course it points me to datatools.com web site among other references. However, it was not explicitly clear from all those sources whether DataTools APIs can return whether an address is business or not - it can distinguish email and phone numbers, but not clear about street addresses.

Not satisfied with what Google dished out, I tried the new Bing next. To my delight, it immediately gave me the straight answer:

I then asked it about Google Address Validation API's capability and some samples of Australian addresses. It gave me the information exactly as I wanted.

A few  days later, I tried the same on Chat GPT-3.5:

Clearly, it did not understand the context of DataTools. So I explained:

So 3 question in, all I got was drivel. I thought it may be my way of questioning - so I copied the exact question I used in Bing:

Again, the answer is not clear at all - certainly no clearer than what I can read from DataTools.com itself. I then drilled down further, all I got was ambiguity and the alternative products/solutions it returned were all obscure not well know companies, Google was certainly not in it. When I explicitly asked it about Google's Address Validation API, it misunderstood my question again... so the whole experience with Chat GPT is quite frustrating.

TSP Art

 Last week I went to Kinokuniya and saw a book about computer art. In it, there were the usual fractal, computer image processing works that I have seen eons ago. However, there was a particular style produced by Travelling Sales Person algorithm that mesmerized me. I liked it so much that I decided to make some TSP pictures myself. Here is one that I like:

Here are some useful articles and tools:

• Making TSP Art  
• TSP Art from Egg-Bot
• TSP Art from Oscar Luis Vera Pérez
• Open Processing Community: Stippling, Voronois and TSPs