May 1, 2018 6:46:35 AM by Brendan Tierney
On March 27th 2018, Oracle announced the availability of the Oracle Autonomous Data Warehouse Cloud Service (ADWC). The announcement was done via a live webcast with Larry Ellison leading the webcast, along with a live demo and some customer interviews. In this article I will give an overview of the main features of ADWC, how to set it up, some example of what you can do, and an overview of a new machine-learning product that is only available on the ADWCS.
The Oracle Autonomous Data Warehouse Cloud aims to deliver all of the analytical capabilities, security features, and high availability of the Oracle Database without any of the complexities of managing, configuration, tuning, and administration. It is a totally managed solution with additional machine-learning capabilities to allow the database to adapt and manage the data and queries, as the workloads and data volumes change and evolve over time in the data warehouse.
The ADWC is a combination of Oracle 18c Database, Exadata hardware, advanced administration and tuning using machine learning, all hosted in the Oracle Cloud.
This new ADWC offering comes with many new features to support a self-managing data warehouse environment. These include:
There are also some limitations. These are mainly some of the typical database options that haven’t been enabled in the database. There seems to be various reasons for these features’ not being available. One should reason is that this is the first release of ADWC and some of these feature may become available in future releases and updates of ADWC. Some of the features that are not currently available include:
Some of these are widely used and are also used by other features of the database. Be careful that you fully check all the database features you typically use to ensure that they are available in the version of the ADWC you will be using.
The setting up of the ADWC service is a very straightforward process. As with all cloud services, it is simple and allows you to avoid the slight pain of having to order, setup, and configure new hardware, before having to install and configure the Oracle Database for your data warehouse environment.
To install Oracle’s ADWC you need to go to cloud.oracle.com and select the ADWC from the PaaS section of the website.
You will need some cloud credits. You can easily claim $300 worth of free credits; or, if you attend one of the Oracle Code events or other similar Oracle events, you might be lucky enough to get $500 of free credits. This will be more than enough to try out ADWC for a few days and if you carefully manage your account you will get much longer.
The steps you need to follow are very similar to creating a new cloud Oracle Database instance. Enter such details as the name of the database, the contact email, number of CPUs, what storage you need, and the admin passwords.
Remember that the CPU count and storage can be adjusted later if you need to scale up or down.
After a few minutes the ADWC instance should be up and running. The next step is to start the service using the Service Console; after that you can then connect to the database using various tools. Before you can connect to your ADWC, you will need to download a connection zip file wallet. Then in SQL Developer you can set up a new connection that has a type of Cloud PDB and then enter the details of this wallet file.
There are a number of ways of loading data into your ADWC, and this can be done in the same way as any other database, be it located on-premises or in the Oracle Cloud. But with the ADWS you can also load data from the Oracle Object Store, Amazon AWS S3, and from your on-premises databases. Easily migrate from Amazon Redshift, Microsoft SQL Server, and other databases. I won’t go into the details of loading data from the Oracle Object Store, as there is a good tutorial available here.
When it comes to creating your own object in the ADWS, such as creating tables, all you need to do is give a basic table definition without having to define any additional indexes, or partitions, storage settings, etc. These features are not enabled for us as a user of the database, but these will be created, adjusted and tuning automatically by the ADWS using the built-in machine learning capability of the ADWC.
The following is an example of a create table statement that can be run in ADWC.
CREATE TABLE customers (
cust_id NUMBER NOT NULL,
cust_first_name VARCHAR2(20) NOT NULL,
cust_last_name VARCHAR2(40) NOT NULL,
cust_gender CHAR(1) NOT NULL,
cust_year_of_birth NUMBER(4) NOT NULL,
cust_marital_status VARCHAR2(20) ,
cust_street_address VARCHAR2(40) NOT NULL,
cust_postal_code VARCHAR2(10) NOT NULL,
cust_city VARCHAR2(30) NOT NULL,
cust_city_id NUMBER NOT NULL,
cust_state_province VARCHAR2(40) NOT NULL,
cust_state_province_id NUMBER NOT NULL,
country_id NUMBER NOT NULL,
cust_main_phone_number VARCHAR2(25) NOT NULL,
cust_income_level VARCHAR2(30) ,
cust_credit_limit NUMBER );
As this table has data added and queries run again this table and data, ADWC will monitor this usage and using the machine learning to work out the best way of structuring this data, as well as the optimal way of running the queries on this data. We need to trust that this will be done correctly in the background. This might be a bit difficult for some to accept, but over time, people will. Similarly, as the ADWC works out the optimal storage for the data and the optimal way of executing the queries, we will have to accept that in the beginning our queries might take longer than expected to run. This is because all machine learning and intelligent systems need some time to learn their tasks. In this case, they need to learn how this data will be used and from this work out the optimal way to store the data and run the queries.
Machine learning has been available in Oracle Database for some time now. With ADWC, this machine learning is included and forms part of the machine-learning adaptive and tuning feature of the ADWC.
The core machine-learning features in Oracle Database are also available. These are commonly referred to as Oracle Data Mining, which is part of the Oracle Advanced Analytics option. This is included as part of the ADWC offering.
But there are some restrictions to using this. I mentioned earlier that some features are not enabled or available in the current version of ADWC. Some of these features are used by Oracle Data Mining; in particular, Oracle Text.
There are two main ways of accessing and using the in-database machine learning algorithms. The first is to use your favourite developer tool to write your machine learning SQL code. Unfortunately, you will not be able to use the Oracle Data Mining GUI tool, in SQL Developer, but this may become possible with future releases of ADWC.
The second option is to use the new machine-learning tool on ADWC called Oracle Machine Learning (OML). Oracle Machine Learning is based on Apache Zeppelin and gives us a new machine-learning tool for accessing the in-database machine-learning algorithms and in-database statistical functions. OML SQL notebooks provide easy access to Oracle's parallelized, scalable in-database implementations of a library of Oracle Advanced Analytics' machine-learning algorithms (classification, regression, anomaly detection, clustering, associations, attribute importance, feature extraction, time series, etc.), SQL, PL/SQL and Oracle statistical and analytical SQL functions. Oracle Machine Learning SQL notebooks and Oracle Advanced Analytics' library of machine learning SQL functions combined with PL/SQL allow companies to automate their discovery of new insights, generate predictions, and add "AI" to data visualization dashboards and enterprise applications. The key features of Oracle Machine Learning include:
More Autonomous Database Cloud Services
The Oracle Autonomous Data Warehouse (Cloud ADWC) is the first of many Oracle Autonomous Database Cloud services that will be released over the next few months. Other planned cloud services include:
Watch out for announcements about these; I’m sure there will lots about it at Oracle Open World later this year.
The Autonomous Data Warehouse Cloud service is the first of the autonomous database services that Oracle will be delivering this year and beyond. The best way to try out ADWC is to get your hands on some free cloud credits. But you will need to carefully manage these credits to ensure that they last long enough for you to give ADWC a proper try. There are so many new features with ADWC and part of the beauty of it is that most of the benefits are hidden, you just don’t see anything happening. It is all autonomous and self-managing.
Written by Brendan Tierney
Brendan Tierney, Oracle ACE Director, is an independent consultant and lectures on Data Mining and Advanced Databases in the Dublin Institute of Technology in Ireland. He has 22+ years of extensive experience working in the areas of Data Mining, Data Warehousing, Data Architecture and Database Design. Brendan has worked on projects in Ireland, UK, Belgium and USA. Brendan is the editor of the UKOUG Oracle Scene magazine and deputy chair of the OUG Ireland BI SIG. Brendan is a regular speaker at conferences across Europe and the USA and has written technical articles for OTN, Oracle Scene, IOUG SELECT Journal and ODTUG Technical Journal. Brendan has published the following books with Oracle Press book Predictive Analytics using Oracle Data Miner Oracle R Enterprise SQL & PL/SQL from the Experts These books are available on Amazon.