[CI-DWH][Python] Get Row Counts of All Tables in dwh_custom

I needed to find some good tables in NetApp Cloud Insights dwh_custom, to test how long it takes to delete and add rows. Adding and deleting rows is going to be fine for smallish tables, but when you might daily have 100'000 rows or so, I'm not sure using the REST API ODATA interface to do all those adds is going to work in a timely manner. In which case the other option is the CSV upload route (the CSV will be processed by the POST ETL script, which still exists in CI, you just design it and then ask the NetApp SRE team to validate it and put it in the right place.)

Here's a little script that does the titular task. You will need to add your proxy details (I'm testing behind a proxy, which certainly slows things down) - if you don't have a proxy, just remove that bit of code. You'll also need to add your tenant (https://...cloudinsights.netapp.com) and your API token.

I did notice an error reading a table -

{'error': {'code': None, 'message': 'Exception while preparing Entity Collection'}}

- and the code handles this. Also, it handles where you have empty tables. I did notice that sometimes the api works and sometimes it does not, maybe retrying on 1 or 2 failures might be a good addition to the script.

The Script

import requests
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
 
proxies = {'https': 'http://USERNAME:PASSWORD@PROXYFQDN:PORT'}
yourTenant = "YOURTENANT"
apiKey = "APIKEY"
headers = {'X-CloudInsights-ApiKey': '{key}'.format(key=apiKey)}

''' Firstly create a list of the custom queries '''
api = "/rest/v1/dwh-management/odata/dwh_custom/custom_queries"
j = requests.get(yourTenant + api,headers=headers,proxies=proxies,verify=False).json()
customQueries = []
for row in j['value']: customQueries += [row['queryName']]

''' Then we get the tables in dwh_custom '''
api = "/rest/v1/dwh-management/odata/dwh_custom"
j = requests.get(yourTenant + api,headers=headers,proxies=proxies,verify=False).json()

''' And check their lengths '''
for row in j['value']:
  if row['name'] in customQueries: pass
  else:
    api = "/rest/v1/dwh-management/odata/dwh_custom/" + row['name'] + "?&$count=true&$top=1"
    try:
      j = requests.get(yourTenant + api,headers=headers,proxies=proxies,verify=False).json()
      if len(j) == 1: print(row['name'] + " : " + j['error'])
      else: print(row['name'] + " : " + str(j['@odata.count']))
    except:
      print(row['name'] + " : Exception reading the table (possible empty table)!")

''' The above handles the error -
{'error': {'code': None, 'message': 'Exception while preparing Entity Collection'}}
- and other errors (like empty table.) '''



Comments