MySQL HeatWave is the MySQL DBaaS provided by Oracle in OCI and some other clouds. Compared to the vanilla MySQL, one of the key features of the service is that it allows you to run analytics queries (aka OLAP) very quickly using the HeatWave cluster. You can also run such queries on files using LakeHouse.
When we talk about analytics, we also think about data visualization solutions. In OCI, you can use Oracle Analytics Cloud.
In this article, we explore the procedure to establish a connection between Oracle Analytics Cloud (OAC) and a MySQL HeatWave DB instance within Oracle Cloud Infrastructure (OCI).
Deploying OAC
We consider that you already have a DB System up and running in OCI. The first step is then to deploy an Analytics Cloud instance.
The Analytics Cloud service is located in Analytics & AI section:
We need to create a new instance:
We provide the usual details, after filling in a name and a description, I use the defaults:
The instance is then created:
Prepare the DB Instance
Of course, you need, if you want, but still recommended, to create a dedicated user for OAC to connect to your MySQL HeatWave DB instance but there is something else very important to perform before being able to connect OAC to MySQL HeatWave: prepare a DNS name.
If like me, you don’t have any FQDN for your DB instance, you will need to follow the steps in this section,
As you can see the FQDN’s value is –.
Therefore, we need to create a new private DNS Zone we will use to connect OAC to MySQL HeatWave:
I called my private zone lefred.hw and it’s important to use the same VCN as the DB Instance.
When the zone is created, we need to create a new record for the MySQL HeatWave DB instance resolving to its private IP (10.0.1.35 on my system):
We can then publish the changes:
And we are done with this part.
OAC Private Access Channel
By now, our OAC instance should be active. We still need to create a Private Access Channel to access our MySQL HeatWave DB System:
It’s important to use again the same VCN as the DB System and the private subnet (where the DB System is). It’s mandatory to use the VNC’s domain name as DNS zone and then add the new DNS zone we created in the previous section:
As soon as the OAC is active again, we can click on the URL to access the Oracle Analytics Cloud interface:
Connection & Dataset
Once we reach OAC’s home page, we can create a new connection:
We browse the connection types and we choose HeatWave:
Then we enter the credentials but pay attention that you cannot use the DB System’s IP:
But if the login and the password are correct, with the DNS name we created, the connection will be successful:
And we can now access the data stored in our MySQL HeatWave instance.
We can finally create a dataset and a workbook:
Conclusion
In the context of Oracle Cloud Infrastructure, employing a robust tool such as Oracle Analytics Cloud alongside MySQL HeatWave necessitates the precise configuration of a private access channel. It is essential to utilize a DNS-resolvable name for your database instance, as the IP address will not work.
Enjoy Analytics and data visualization of all your data stored in MySQL HeatWave!
