[CI][Python] Custom SQL Queries in the NetApp Cloud Insights Data Warehouse

Introduction

In the NetApp OnCommand Insight (OCI) Data Warehouse world, it was fairly standard to integrate with the MySQL OCI-DWH by running queries from an external client (like Splunk for example.) In the NetApp Cloud Insights (CI) world, over the REST API ODATA interface, there is another way of doing it; create custom SQL queries in CI, and the integration simply calls that custom query (one API call). So how do we do this?

0) Setup

Notes: 1) We're just running things directly in the Python Client, no creating scripts here. 2) Update the highlighted bits as per your CI tenant.

• import requests
• import urllib3
• urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
• apiKey = "YOUR_API_KEY"
• yourTenant = "aaXXXX.bYY-cc-Z"
• baseUrl = "https://" + yourTenant + ".cloudinsights.netapp.com/rest/v1/"
• headers = {'X-CloudInsights-ApiKey': '{key}'.format(key=apiKey)}

1) Seeing What Custom SQL Queries Are Already in the CI DWH (there may be none)

To see the Custom Queries that are currently in the CI DWH, run:

• api = "dwh-management/odata/dwh_custom/custom_queries"
• url = baseUrl + api
• jsondata = requests.get(url, headers=headers, verify = False).json()
• print(jsondata)

The json format isn't super human readable, but we can make it more readable with the following.

To print out the odata.context (the full API URL):

• print(jsondata['@odata.context'])

To print out the list of queries:

• i = 0
• for query in jsondata['value']:
•   print(str(i) + ":" + query['queryName'])
•   i += 1

And if you want to see the actual SQL query (see the Appendix below if you want to print the sql to a text file):

• print(jsondata['value'][i]['querySql'])

2) How to We Upload a Custom Query to CI?

The method changes to POST, and the BODY needs two fields - queryName and querySql, like so:

{
  "queryName": "test_query_1",
  "querySql": "SELECT * FROM dwh_inventory.storage"
}

To create our very simple Custom Query in the CI DWH, run:

• api = "dwh-management/odata/dwh_custom/custom_queries"
• url = baseUrl + api
• body = {
•   "queryName": "test_query_1",
•   "querySql": "SELECT * FROM dwh_inventory.storage"
• }
• postdata = requests.post(url, headers=headers, json=body, verify = False)

Response [201] = success!

3) How to We Run a Custom Query in CI?

To execute the custom query, it is simply (replace test_query_1 with the name of the query):

• api = "dwh-management/odata/dwh_custom/test_query_1"
• url = baseUrl + api
• jsondata = requests.get(url, headers=headers, verify = False).json()

And similar to the above you'll find the table output in 'value':

• i = 0
• for query in jsondata['value']:
•   print(str(i) + ":" + str(query))
•   i += 1

You will need to pass the data as per requirements.

4) How do We Delete a Custom Query in CI?

To execute the custom query, it is simply (replace test_query_1 with the name of the query):

• api = "dwh-management/odata/dwh_custom/custom_queries('test_query_1')"
• url = baseUrl + api
• delQuery = requests.delete(url, headers=headers, verify = False)

Response [200] = success!

PS Just a reminder that if you're using Python on your Windows Machine, in a corporate environment, initiated from a DOS prompt, then you'll probably want to set the proxy in DOS first:

set https_proxy=https://USERNAME:PASSWORD@proxy.server.fqdn:PORT

APPENDIX: Random Useful Stuff

How to redirect python print output to a file, then redirect output back to the CLI:

orig_stdout = sys.stdout

sys.stdout = open('file.txt','w')

print(...)

sys.stdout.close()

sys.stdout = orig_stdout

How to turn a formatted SQL Query into a String in Python (or How to String-ize an SQL Query with Python):

One of the challenges with using Custom SQL Queries in CI is that your beautifully formatted SQL query needs to be turned into a string. And we can do this from the Python shell as per the below:

Note: Remember where you have \ it should be \\ in Python.

workingDir = "{WORKING DIRECTORY}"

file = "{FILE NAME}"

strFile = ""

with open(workingDir + "\\" + file) as f:

  lines = f.readlines()

len(lines) # Check this is what you expect!

for l in lines:

  strFile += l.strip() + " "

file2 = "{FILE NAME 2}"

with open(workingDir + "\\" + file2, 'w') as f:

  f.write(strFile)

Note 1: You would still need to convert " to ' (because above we use " to encapsulate the SQL query.)

Note 2: I did have issues with a really big query, but exactly the same string runs fine in OCI-DWH SQL - Cloud Insights problem? I didn't check if it would run in CI Cognos (this is something I need to try ... could we be missing an annotation!?)