Notes on Injesting MySQL Data to NetApp Cloud Insights DWH

on

Coming from NetApp's on-premise OnCommand Insight (OCI) offering, where I have a lot of integrations that talk directly to the MySQL database on the Data Warehouse server, NetApp's new cloudy offering - Cloud Insights (CI) - introduces new challenges.

~~~

There is a new (only in customer preview at the moment) ODATA interface that allows using REST API to do things like MySQL selects. One idea being that you could do a limited ETL from the Cloud Insights DWH to an on-premise MySQL server if you so wish (a local repository of some of the CI data, which may be useful for designing report queries - there is a cloud based version of Cognos in CI.)

One option for populating the dwh_custom is to use the REST API for uploading CSVs:

/dwh-management/upload/csvs : Upload csv file to be processed by Data Warehouse custom script (secondary/post ETL)

Currently, you need to reach out to the NetApp SRE team to get the Data Warehouse custom script uploaded to CI.

~~~

I honestly think the challenges of moving from OCI to CI are well worth it. We live in an age were everything is going REST API. The old MySQL interfaces are old-skool. Upgrading integrations to use REST API, is an effort well worth doing.

Also, the benefits of scalability (being built on MicroServices - not the Monolithic architecture of OCI), single-pane-of glass (i.e. you don't need multiple OnCommand Insight servers if you're a large enterprise), CI/CD software delivery (i.e. you don't need to schedule a potentially scary OCI & DWH upgrade - CI is upgraded for you, and all the Acquisition Units automatically upgrade too) and many more new and cool features (that won't come to OCI, like Kubernetes, the Data Lake, Cloud Secure..)

Note: I was wondering what the use case was for the Data Lake. I was thinking it was a repository for the same kind of stuff you'd stick in dwh_custom. But no, the Data Lake holds 90 days worth of data. The Data Lake is useful for timeseries data, i.e. MySQL server telemetry, anything injest-able using Telegraf, etcetera.


~~~

Other notes...

1) Cognos: MySQL Editing and DataModules

Currently, there is no way you can plug in your MySQL Workbench, Toad MySQL, or whatever preferred MySQL client you have, into the Cloud Insights Data Warehouse, but, it is possible to do SQL Editing in Cognos (reminder to self to blog about this).

Also, the Data Modules in Cognos are very good - you can stitch tables from different schemas together, making life easy for consumers of the data - so they can get the data they want - using drag and drop functionality - into the reports they desire to create (reminder to self to blog about this).

2) ETL from Cloud Insights

The ODATA functionality (in customer preview currently - now August 2021), should allow a limited ETL (i.e. you won't want to pull down everything) from Cloud Insights (reminder to self to blog about this). This might be useful for:

2.1) Legacy Integrations:
So you can have an on-premise MySQL, with the data legacy integrations leveraging it. The legacy integrations can easily be repointed from one MySQL database to another.

2.2) Cognos Report Design using Preferred MySQL Client:
I spend a lot of time stitching data together to find answers to customer questions, or design the data MySQL query behind a requested Cognos Report. With an on-premise copy, you can easily use your favourite tools to design queries.

3) Random Notes
  • ODATA has a 10'000 rows / 10 minute timeout.
  • PUT for input, PATCH for updates, POST not used...
  • ODATA primarily for data extraction.
  • CI DWH ODATA Questions:
    • Create table?
    • Insert into table?
    • Update into table?
    • Delete from table?
    • Call procedures?
    • Drop/Truncate table?
    • SQL Permissions?

Comments

Post a Comment