[Python][CI-DWH][ODATA] Find Max Technical Key

on

I've been working on some tools to sync data from NetApp Cloud Insights DWH (Data Warehouse) to an on-premise MySQL Database - MySQL is the closest on-premise match for the Cloud Insights DWH and OnCommand Insight used MySQL for its Data Warehouse - and making good progress.

On thing I wanted to do was find the maximum technical key, a good example is:

SELECT MAX(tk) FROM dwh_capacity.date_dimension

The following python script can do just that.

Save the script as say FindLatestTk.py and run as:

python FindLatestTk.py tokenFile.txt https://YOURTENANT.cloudinsights.netapp.com dwh_capacity date_dimension tk

Image: Example running FindLatestTk.py


It does 3 main things:

  1. Create a custom MySQL query using ODATA (or MySQL view)
  2. Gets the output of running the custom query
  3. Deletes the custom MySQL query
I did wonder if there was an easier way to do this (find MAX) with ODATA but I didn't work for me. The other idea is commented out at the bottom of the script. And there are other ways to get the same result.

The Script

'''FINDING THE LATEST/MAXIMUM TK (OR OTHER KEY) IN A CI DWH TABLE
=================================================================
Example:
python FindLatestTk.py tokenFile.txt https://YOURTENANT.cloudinsights.netapp.com dwh_capacity date_dimension tk'''

import argparse
import requests
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
import random
import sys
print()

''' PARAMETERS '''
parser = argparse.ArgumentParser(description="Sync data from CI DWH to MySQL.",
  formatter_class=argparse.ArgumentDefaultsHelpFormatter)
parser.add_argument("tokenFile", help="API Token")
parser.add_argument("tenantUrl", help="https://TENANT.cloudinsights.netapp.com")
parser.add_argument("schema", help="Database")
parser.add_argument("table", help="Table")
parser.add_argument("tk", help="Technical key")

''' PARAMETERS (ARGUMENTS) processing '''
args = parser.parse_args()
config = vars(args)

''' READ THE TOKEN FILE '''
with open(config['tokenFile']) as t:
  lines = t.readlines()
token = lines[0]
headers = {'X-CloudInsights-ApiKey': '{key}'.format(key=token)}

''' CREATE THE TEMP SQL QUERY tquery '''
tquery = "TEMP_QUERY_" + str(random.randint(0,999))
query = "SELECT MAX(" + config['tk'] + ") FROM " + config['schema'] + "." + config['table']
print("tquery: " + tquery)
print("query: " + query,end = "\r\n\n")
api = '/rest/v1/dwh-management/odata/dwh_custom/custom_queries'
body = {
  "queryName": tquery,
  "querySql": query
}
p = requests.post(config['tenantUrl']+api, headers=headers, json=body, verify = False)
if p.status_code != 201: sys.exit("Failed to post custom query!")

''' ACQUIRE FROM CLOUD INSIGHTS '''
api = ('/rest/v1/dwh-management/odata/dwh_custom/' + tquery)
print("Collection api: " + api,end = "\r\n\n")
j = requests.get(config['tenantUrl']+api,headers=headers,verify=False).json()
print("Result for MAX(" + config['tk'] + "):")
print(j['value'][0][("MAX(" + config['tk'] + ")")],end = "\r\n\n")

''' DELETE THE TEMP SQL QUERY tquery '''
api = ("/rest/v1/dwh-management/odata/dwh_custom/custom_queries('" + tquery + "')")
d = requests.delete(config['tenantUrl']+api, headers=headers, verify = False)
if d.status_code != 200: print("Failed to delete custom query " + tquery,end = "\r\n\n")
else: print("Deleted " + tquery)

'''ATTEMPT TO FIND AN EASIER WAY
api = ('/rest/v1/dwh-management/' + config['schema'] + '/' + config['table'] + '?$apply=aggregate(' + config['tk'] + ' with max as Max)')
j = requests.get(config['tenantUrl']+api,headers=headers,verify=False).json()
- but I could not get it to work with CI.'''

Comments

Post a Comment