Saturday, 8 January 2022

Uploading a CSV to Cloud Insights for Purposes of DWH_Custom Enrichment

If you want to create a NetApp Cloud Insights dwh_custom table, insert data to this table, etcetera ... the method to do this is via the API:

POST /dwh-management/upload/csvs

After uploading the CSV to Cloud Insights, you will need a Post ETL SQL script to process this CSV. Currently, to get the Post ETL SQL script to Cloud Insights, you need to reach out to the NetApp Cloud Insights SRE team.

In the post I'll show two examples of uploading the CSV to NetApp Cloud Insights:

  1. Via the swagger-ui
  2. Using a Linux Server

1) Via the Swager UI

With the correct permissions, to get to the NetApp Cloud Insights Swagger UI:

Login to either:

  • https://cloud.netapp.com > Select Fabric View > Click on Cloud Insights
  • Or go in via your tenant link: https://{tenant}.cloudinsights.netapp.com/

Admin > API Access > API Documentation > Data Warehouse

We're interested in the Data Warehouse API Documentation. And the API we're interested in is:

POST /dwh-management/upload/csvs

The next couple of screenshots show uploading a CSV with a HTML 200 (Successful Operation) result:

(Click to enlarge the images)



2) Using a Linux Server

This is probably a more useful example, as you'll likely be programmatically uploading CSVs to Cloud Insights. Because it is a little interesting, I've included an error here and how to troubleshoot it. My example is from a Linux VM inside an Enterprise Organization (any identifiers redacted/removed/changed), and to get out to Cloud Insights, necessary proxy access had to be configured in the first place. Also, you have already obtained your API key.

curl: (6) Could not resolve host: YOURTENANT.c01-eu-1.cloudinsights.netapp.com; Name or service not known
[user@aserver ~]$
[user@aserver ~]$ grep hosts /etc/nsswitch.conf
#hosts:     db files nisplus nis dns
hosts:      files dns
[user@aserver ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
128.129.130.131 aserver aserver.localdomain
[user@aserver ~]$
[user@aserver ~]$ sudo vi /etc/hosts
[sudo] password for aserver:
[user@aserver ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
128.129.130.131 aserver aserver.localdomain
3.126.150.64 YOURTENANT YOURTENANT.c01-eu-1.cloudinsights.netapp.com
[user@aserver ~]$
[user@aserver ~]$ touch AWSPriceList-Windows.csv
[user@aserver ~]$ vi AWSPriceList-Windows.csv
[user@aserver ~]$ cat AWSPriceList-Windows.csv
ConfigType,vCPU,Memory,HourlyCost,OS,Region
t3.nano,2,0.5,0.0098,Windows,US East (N. Virginia)
t3.micro,2,1,0.0196,Windows,US East (N. Virginia)
t3.small,2,2,0.0392,Windows,US East (N. Virginia)
t3.medium,2,4,0.06,Windows,US East (N. Virginia)
t3.large,2,8,0.1108,Windows,US East (N. Virginia)
t3.xlarge,4,16,0.24,Windows,US East (N. Virginia)
t3.2xlarge,8,32,0.48,Windows,US East (N. Virginia)
t3a.nano,2,0.5,0.0093,Windows,US East (N. Virginia)
t3a.micro,2,1,0.0186,Windows,US East (N. Virginia)
t3a.small,2,2,0.0372,Windows,US East (N. Virginia)
t3a.medium,2,4,0.056,Windows,US East (N. Virginia)
t3a.large,2,8,0.1028,Windows,US East (N. Virginia)
t3a.xlarge,4,16,0.224,Windows,US East (N. Virginia)
t3a.2xlarge,8,32,0.448,Windows,US East (N. Virginia)
t2.nano,1,0.5,0.0081,Windows,US East (N. Virginia)
t2.micro,1,1,0.0162,Windows,US East (N. Virginia)
t2.small,1,2,0.032,Windows,US East (N. Virginia)
t2.medium,2,4,0.0644,Windows,US East (N. Virginia)
t2.large,2,8,0.1208,Windows,US East (N. Virginia)
t2.xlarge,4,16,0.2266,Windows,US East (N. Virginia)
t2.2xlarge,8,32,0.4332,Windows,US East (N. Virginia)
[user@aserver ~]$
[user@aserver ~]$ pwd
/home/user
[user@aserver ~]$
[user@aserver ~]$ export https_proxy='aproxy.company.com:80' proxy_auth_scheme=''
[user@aserver ~]$
[user@aserver ~]$ curl -i -X POST -H "X-CloudInsights-ApiKey:eyJra...very.long.string...DV2GQ " -H "Content-Type:multipart/form-data" -F "customFile=@/home/user/AWSPriceList-Windows.csv" "https://YOURTENANT.c01-eu-1.cloudinsights.netapp.com/rest/v1/dwh-management/upload/csvs"
HTTP/1.1 200 Connection established

HTTP/1.1 100 Continue

HTTP/1.1 200
Date: Tue, 04 Jan 2022 20:03:56 GMT
Content-Length: 0
Connection: keep-alive
Server: nginx
X-Content-Type-Options: nosniff
X-XSS-Protection: 1; mode=block
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Pragma: no-cache
Expires: 0
Strict-Transport-Security: max-age=31536000 ; includeSubDomains
X-Frame-Options: DENY

To explain the above.
  • We get a could not resolve host error.
  • To troubleshoot, first check hosts is using files.
  • Then add the Cloud Insights IP to /etc/hosts
  • Then we create a sample CSV to upload.
  • Then we export the proxy configuration.
  • Then we use CURL to POST our CSV to Cloud Insights.
  • And we get HTTP 200 successful!
APPENDIX: POST ETL SQL File

This might have MySQL commands like:

DROP TABLE ...
CREATE TABLE ...

LOAD DATA LOCAL INFILE 'AWSPriceList-Linux.csv' INTO
TABLE dwh_custom.AWSPriceLinux FIELDS TERMINATED by ',' ENCLOSED BY '"' LINES terminated by '\n'
IGNORE 1 LINES;

UPDATE: You can do REST ODATA API inserts to dwh_custom!

A colleague informed me in the week (say 13th January 2021), that testing of POST-ing data to dwh_custom in CI, over the REST ODATA API, does work, which is very good news. An example:

curl -X POST -k https://YOURTENANT.cloudinsights.netapp.com/rest/v1/dwh-management/odata/dwh_custom/aCustomTable -H "Content-Type:application/json" -H "X-CloudInsights-ApiKey:${key}" -d '{"someTk":23,"clientname":"aserver.acme.com","versionLabel":"7.6.1.2","osDescription":"Linux 3.10.0-1160.11.1.el7.x86_64 "}'

Note: ${key} comes from a .sh file.