[Python][CI-DWH] Batching up CI DWH MySQL Deletes and Inserts

In this post, I cover an example of how to batch up NetApp Cloud Insights Data Warehouse MySQL Deletes and Inserts, to an imaginary table called CC_MAPPING.

Python Connect to NetApp CI Basics

import requests
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
apiKey = "YOUR-KEY"
yourTenant = "https://YOUR-TENANT.cloudinsights.netapp.com"
headers = {'X-CloudInsights-ApiKey': '{key}'.format(key=apiKey)}

 

Encapsulate the Deletes

We use api1 and api2 to encapsulate the deletes.

api1 = "/rest/v1/dwh-management/odata/dwh_custom/CC_MAPPING('"
api2 = "')"

 

THE DELETES

This is a one-liner, there is just one example here.

ID = "12345678"; d = requests.delete(yourTenant + api1 + ID + api2, headers=headers, verify = False); print("DELETE API worked!") if d.status_code == 200 else print("DELETE API failed!")

... AND REPEAT!

 

The API for INSERTS

api = '/rest/v1/dwh-management/odata/dwh_custom/CC_MAPPING

 

THE INSERTS

This is a one-liner, there is just one example here.

body = {'id': '12345678', 'costCode': '23456789'}; p = requests.post(yourTenant+api, headers=headers, json=body, verify = False); print("INSERT API worked!") if p.status_code == 201 else print("INSERT API failed!")

... AND REPEAT!

 

If You Want To Check Things

To get one result:

api = "/rest/v1/dwh-management/odata/dwh_custom/CC_MAPPING('12345678')"

j = requests.get(yourTenant + api,headers=headers,verify=False).json()

print(j)

 

To get all the results:

api = '/rest/v1/dwh-management/odata/dwh_custom/CC_MAPPING'

collect = True
skip = 0
batch = 5000
results = []

while collect:
  apiExtra = ('?&$top=' + str(batch) + '&$skip=' + str(skip))
  print(apiExtra)
  j = requests.get(yourTenant + api + apiExtra,headers=headers,verify=False).json()
  if len(j['value']) == 0:
    print("Reached the end!")
    collect = False
  else:
    results += j['value'] 
  skip += batch
 
print("Results count = " + str(len(results)),end = "\r\n\n")
 
with open('psids.txt', 'w') as f:
  for r in results:
    f.write(str(r))
    f.write('\n')