Get Insight on MySQL data using APEX 22.1
Oracle has announced support of MySQL connection in Oracle Database tool service. This announcement was followed by another exciting feature release of APEX 22.1, which can be used for building applications directly based on the MySQL Database Service (MDS) in Oracle Cloud Infrastructure(OCI). Additionally, this feature has enabled users to perform analysis and to create visualization of data directly from MDS. Technically, this functionality of APEX is achieved by leveraging ORDS “REST Enabled SQL Service”.
Today, we will discuss a use case where we will visualize and analyze the data in MDS using APEX. For this purpose, we have used sales data residing in MDS. We will leverage the APEX which comes along with Always Free Autonomous database (https://www.oracle.com/in/cloud/free/).
This use case can be completed in 5 simple steps:
Step#1: Create a MySQL connection in OCI Database tool service
We need a private endpoint and a password secret to create these connections. Here, a private endpoint would help us to access the data securely using private network.
Password secret will help to store and manage the user credentials safely in OCI vault and encrypt it with a master key.
Now we are ready to create the MySQL connection.
Test the connectivity using SQL worksheet.
Step#2: Create web credentials in APEX App Builder
Login to APEX console from Autonomous database.
Login to APEX.
Go to workspace utilities from App Builder and create Web Credentials.
We need to provide OCI authentication to create web credentials. Refer to https://docs.oracle.com/en/cloud/paas/management-cloud/logcs/create-credentials-oci-authentication.html for more details.
Step#3: Create a “Rest Enabled SQL Services” in APEX App Builder
Example of an endpoint URL:
ocid1.databasetoolsconnection.oc1.<OCI Region>.<OCID of Connection created Database tool service>
Test “Rest Enabled SQL Service”
Step#4: Create an APEX application
Add a page for the chart in a newly created application.
Step#5: Analyze the data with chart/dashboard created in APEX
Here, we will plot continent wise revenue data.
No of customers and continent wise revenue.
Day wise Revenue.
Conclusion
This is a good example of creating visualizations and analyzing data using the APEX platform. Currently, this platform supports data in csv, MySQL in OCI, Oracle databases, and more.
Going forward, I would like to see more MDS users to try this feature.
Let’s talk about it! Join the public Slack channel here.
