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.
Overview of ADWC Cloud Service
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.
Key Features and Benefits
This new ADWC offering comes with many new features to support a self-managing data warehouse environment. These include:
- Oracle 18c Database. Utilizing over 40 years of enterprise database development, gives you the most advanced, optimized and scalable database in the market
- High performance queries and concurrent workloads. Optimized query performance with preconfigured resource profiles for different types of users
- Online elastic scaling, allowing your data warehouse to automatically scale for compute and storage independently to suit your data warehouse workloads
- ADWC is fully managed with automatic provisioning, patching, upgrades, backups, data storage optimization, and query performance.
- Cloud-based data loading allows data to be loaded from a variety of data sources, including the Oracle Object Store, Amazon AWS, S3 and from your on-premises databases. Easily migrate from Amazon Redshift, Microsoft SQL Server, and other databases.
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:
- Oracle Text
- Oracle Spatial
- Oracle Graph
- Oracle OLAP
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.
Setting up the ADWC Cloud Service
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.
Creating Objects & Storing Data
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.
Oracle Machine Learning
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:
- Collaborative SQL notebook UI for data scientists
- Packaged with Oracle Autonomous Data Warehouse Cloud
- Easy access to shared notebooks, templates, permissions, scheduler, etc.
- Access to 30+ parallel, scalable in-database implementations of machine learning algorithms
- SQL and PL/SQL scripting language supported
- Enables and supports deployments of enterprise machine learning methodologies in ADWC
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:
- Oracle Autonomous Database for Transaction Processing
- Oracle Autonomous NoSQL Database for fast, massive-scale reads and writes (designed for Internet of Things)
- Oracle Autonomous Graph Database for network analysis.
Watch out for announcements about these; I’m sure there will lots about it at Oracle Open World later this year.
Conclusion
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.
Start the discussion at forums.toadworld.com