1. Using Oracle Autonomous Data Warehouse
  2. Using Autonomous Data Warehouse
  3. Getting Started with Autonomous Data Warehouse

1 Getting Started with Autonomous Data Warehouse

Provides an overview of the service and describes how to get started with Autonomous Data Warehouse.

Topics

About Autonomous Data Warehouse

Autonomous Data Warehouse provides an easy-to-use, fully autonomous data warehouse that scales elastically, delivers fast query performance and requires no database administration. It is designed to support all standard SQL and business intelligence (BI) tools, and provides all of the performance of the market-leading Oracle Database in an environment that is tuned and optimized for data warehouse workloads.

As a service Autonomous Data Warehouse does not require database administration. With Autonomous Data Warehouse you do not need to configure or manage any hardware, or install any software. Autonomous Data Warehouse handles creating the data warehouse, backing up the database, patching and upgrading the database, and growing or shrinking the database.

Additionally, Autonomous Data Warehouse does not require any tuning. Autonomous Data Warehouse is designed as a "load and go" service: you start the service, define tables, load data, and then run queries. When you use Autonomous Data Warehouse, no tuning is necessary. You do not need to consider any details about parallelism, partitioning, indexing, or compression. The service automatically configures the database for high-performance queries.

Autonomous Data Warehouse is built upon Oracle Database, so that the applications and tools that support Oracle Database also support Autonomous Data Warehouse. These tools and applications connect to Autonomous Data Warehouse using standard SQL*Net connections. The tools and applications can either be in your data center or in a public cloud. Oracle Analytics Cloud and other Oracle Cloud services provide support for Autonomous Data Warehouse connections.

Autonomous Data Warehouse is a completely elastic service. When you get started with Autonomous Data Warehouse, simply specify the number of OCPUs and the storage capacity in TB's for the data warehouse. At any time, you can scale, increase or decrease, either the OCPUs or the storage capacity. When you make resource changes for your Autonomous Data Warehouse, the data warehouse resources automatically shrink or grow, without requiring any downtime or service interruptions.

Autonomous Data Warehouse includes a cloud-based service console for managing the service (for tasks such as stopping, starting, or scaling the service), and monitoring the service (for tasks such as viewing the recent levels of activity on the data warehouse).

Autonomous Data Warehouse also includes the following:

  • Oracle Application Express (APEX): a low-code development platform that enables you to build scalable, secure enterprise apps with world-class features.
  • Oracle REST Data Services (ORDS): a Java Enterprise Edition based data service that makes it easy to develop modern REST interfaces for relational data and JSON Document Store.
  • Oracle SQL Developer Web: a browser-based interface of Oracle SQL Developer.
  • Oracle Machine Learning: a cloud-based notebook application which provides simple querying, data-visualization, and collaboration capabilities. The notebook is designed to be used alongside other business intelligence applications.

You can use Autonomous Data Warehouse with Oracle Data Visualization Desktop to easily create visualizations and projects that reveal trends in your company’s data and help you answer questions and discover important insights about your business.

The following figure shows the Autonomous Data Warehouse architecture.

Description of autonomous-data-warehouse-architecture.eps follows
Description of the illustration autonomous-data-warehouse-architecture.eps

Before You Begin with Autonomous Data Warehouse

Before you begin using Oracle Autonomous Data Warehouse, you should be familiar with Oracle Cloud.

See Getting Started with Oracle Cloud.

Before you create an Autonomous Data Warehouse instance:

  • On Oracle Cloud, sign up for a free trial subscription or purchase a subscription. You cannot create an Autonomous Data Warehouse deployment until you do so.

  • (Optional) if you want to leverage an object store for data loading you need your object store credentials to use with Oracle Autonomous Data Warehouse, including a username and a password. For details on the required credentials, depending on the object store you want to use, see the following:

    • Oracle Cloud Infrastructure Object Storage, the username is your Oracle Cloud Infrastructure user name. The password is your auth token. See Working with Auth Tokens.

    • Oracle Cloud Infrastructure Object Storage Classic, the username is your Oracle Cloud Infrastructure Classic user name and the password is your Oracle Cloud Infrastructure Classic password.

    • Amazon S3, the username is your AWS access key ID and the password is your AWS secret access key. See AWS Identity and Access Management.

    • Azure Blob Storage, the username is your Azure storage account name and the password is an Azure storage account access key. See About Azure storage accounts.

  • (Optional) Create a bucket for cloud storage manual backups:

    If you want to manually back up Autonomous Data Warehouse to cloud storage you must associate an instance with a cloud storage backup location. For information on defining your Oracle Cloud Infrastructure Object Storage and creating the bucket for manual backups, see Configure Manual Backups on Autonomous Data Warehouse.

After you create an Autonomous Data Warehouse instance:

Key Features of Autonomous Data Warehouse

This section describes key features of Autonomous Data Warehouse, an affordable, feature-rich service in the cloud.

Key Features

  • Managed: Oracle simplifies end-to-end management of the data warehouse:

    • Provisioning new databases

    • Growing or shrinking storage and compute resources

    • Patching and upgrades

    • Backup and recovery

  • Fully Tuned: “Load and go”:

    • Define tables, load data, run queries

    • Provides good performance out of the box

    • Run your queries using any business analytics tool or cloud service

    • Built-in SQL worksheet and notebook also included

  • Fully elastic scaling: Scale compute and storage independently to fit your data warehouse workload with no downtime:

    • Size the Autonomous Data Warehouse to the exact compute and storage required

    • Scale the Autonomous Data Warehouse on demand: Independently scale compute or storage

    • Shut off idle compute to save money

  • Auto scaling: Enable auto scaling to allow Autonomous Data Warehouse to use more CPU and IO resources automatically when the workload requires it:

    • Specify the number of OCPUs for Autonomous Data Warehouse workloads.

    • Enable auto scaling to allow the database to use up to three times more CPU and IO resources depending on workload requirements.

    • Enable auto scaling when you provision an Autonomous Data Warehouse instance or using Scale Up/Down on the Oracle Cloud Infrastructure Console.

  • Autonomous Data Warehouse supports:

    • Existing applications, running in the cloud or on-premises

    • Connectivity via SQL*Net, JDBC, ODBC

    • Third-party data-integration tools

    • Oracle cloud services: Oracle Analytics Cloud, Oracle GoldenGate Cloud Service, Oracle Integration Cloud Service, and others

  • High-performance queries and concurrent workloads: Optimized query performance with preconfigured resource profiles for different types of users.

  • Oracle SQL: Autonomous Data Warehouse is compatible with existing applications that support Oracle Database.

  • Built-in web-based data analysis tool: Web-based notebook tool for designing and sharing SQL based data-driven, interactive documents.

  • Database migration utility: Easily migrate from Amazon AWS Redshift, SQL Server, and other databases.

Simple Cloud-based Data Loading

Autonomous Data Warehouse provides:

  • Fast, scalable data-loading from Oracle Object Store, Azure Blob Storage, AWS S3, or on-premises data sources.

SQL Developer Autonomous Data Warehouse Support

Using Autonomous Data Warehouse with SQL Developer you can do the following:

  • Connect to Autonomous Data Warehouse

  • Create tables in Autonomous Data Warehouse

  • Load data into Autonomous Data Warehouse

  • Copy tables to Autonomous Data Warehouse

  • Transfer a schema to Autonomous Data Warehouse

Business Intelligence Tools Support

Autonomous Data Warehouse is compatible with a number of business intelligence and data visualization tools from Oracle and from trusted third parties.

  • Oracle Analytics Cloud

  • Oracle Data Visualization Desktop

  • Third-party Business Intelligence tools

Typical Workflow for Using Autonomous Data Warehouse

Describes the steps to start using Autonomous Data Warehouse.

Task Description More Information

Create and log in to your cloud account

Provide your information and sign up for an Oracle Cloud Service.

Getting Started with Oracle Cloud

Provision Autonomous Data Warehouse

Create an Autonomous Data Warehouse instance.

Quickstart Tutorial: Getting Started and Provisioning an Autonomous Database

Start the data warehouse

Start and verify that an Autonomous Data Warehouse instance is up and running.

Start Autonomous Data Warehouse

Create database users and obtain security credentials

Create accounts for your users and assign them appropriate privileges. Assign the necessary Autonomous Data Warehouse roles.

Create Users with Autonomous Data Warehouse

Connect to your data warehouse using SQL Developer

Download SQL Developer or another database client tool. Obtain security credentials and connect to your Autonomous Data Warehouse.

Connect to Autonomous Data Warehouse Using Oracle Database Tools

Connect to your data warehouse using a database client

Download a database client. Obtain security credentials and connect to your Autonomous Data Warehouse instance.

Connecting to Autonomous Data Warehouse

Scale the data warehouse

Use the Oracle Cloud Infrastructure Console to scale an instance to add or remove CPU or storage resources.

Starting, Stopping, and Scaling Autonomous Data Warehouse

Load data into the data warehouse

Use the available tools to load data into the data warehouse.

Loading Data with Autonomous Data Warehouse

Migrate data from Amazon Redshift

Migrate data from an existing Amazon Redshift data warehouse.

Migrating Amazon Redshift to Autonomous Data Warehouse

Monitor the data warehouse

Check on the health and performance of the data warehouse.

Managing and Monitoring Performance of Autonomous Data Warehouse

Manage the data warehouse

Check on the day-to-day operation of your service, monitor performance, and review important notifications.

All aspects of the database lifecycle are simplified by the service, including patching and upgrading the database. You can obtain service information including the service and database version from the service console.

Backing Up and Restoring Autonomous Data Warehouse
Clone the data warehouse

Use the Oracle Cloud Infrastructure console to clone a database. You can choose to clone either the full database or only the database metadata.

Cloning a Database with Autonomous Data Warehouse

Use Oracle Machine Learning with Autonomous Data Warehouse

Oracle Machine Learning provides a notebook style application designed for advanced SQL users and provides interactive data analysis that lets you develop, document, share, and automate reports based on sophisticated analytics and data models.

Key features of Oracle Machine Learning:

  • Allows collaboration among data scientists, developers, business users

  • Leverages the scalability and performance of Oracle Platform and its Cloud Services

To use Oracle Machine Learning with Autonomous Data Warehouse you need to add or create Oracle Machine Learning users and access Oracle Machine Learning:

  • OML User Management lets the Admin (user with administrative privileges) create and modify Oracle Machine Learning user accounts.

  • OML Application – application users access Oracle Machine Learning to create, view, and share notebooks for data analytics, data visualization, and other Oracle Machine Learning tasks.

To access Oracle Machine Learning User Management from Autonomous Data Warehouse Service console:
  1. From the Autonomous Data Warehouse instance details page, click Service Console.
  2. Click Administration.
  3. Click Manage Oracle ML Users.

    This shows the Oracle Machine Learning User Administration page.

    After you create user accounts, the users can access Oracle Machine Learning. See Create and Update User Accounts for Oracle Machine Learning for more detailed information on creating users.

  4. You access the Oracle Machine Learning application by clicking the Oracle Machine Learning Home icon on the User Administration page or from the account details mailed to a new user.

Build Reports and Dashboards with Analytics in Autonomous Data Warehouse

Working with Oracle Analytics Cloud

You can use Oracle Analytics Cloud with Autonomous Data Warehouse. Use Oracle Analytics Cloud to select interactive visualizations and automatically create advanced calculations to reveal the insights in your data.

For more information, see Using Oracle Analytics Cloud with Autonomous Data Warehouse.

Working with Oracle Data Visualization Desktop

You can use Oracle Data Visualization Desktop with Autonomous Data Warehouse. Just connect to Autonomous Data Warehouse, select the elements that you’re interested in, and let Data Visualization Desktop find the best way to visualize it. Choose from a variety of visualizations to look at data in a specific way.

For more information see Working with Data Visualization Desktop in Autonomous Data Warehouse.

Use Sample Data Sets in Autonomous Data Warehouse

For users who want to start using the service without creating their own tables, Autonomous Data Warehouse provides the read-only Sales History and Star Schema Benchmark data sets.

These data sets are provided as Oracle Database schemas SH and SSB respectively. Any user can query these data sets without any manual configuration.

Note:

Both SH and SSB are provided as schema-only users, so you cannot unlock or drop those users or set a password. The storage of the sample data sets does not count towards your database storage.

Sales History (SH) Schema

The SH schema provides a small data set you can use to run the sample queries in the Oracle Database Data Warehousing Guide. Note that you need to prefix the table names with the schema name SH in your queries. For example, the following query shows you how the SQL function RANK() works: 

SELECT channel_desc, TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$,
   RANK() OVER (ORDER BY SUM(amount_sold)) AS default_rank,
   RANK() OVER (ORDER BY SUM(amount_sold) DESC NULLS LAST) AS custom_rank
FROM sh.sales, sh.products, sh.customers, sh.times, sh.channels, sh.countries
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND customers.country_id = countries.country_id AND sales.time_id=times.time_id
  AND sales.channel_id=channels.channel_id
  AND times.calendar_month_desc IN ('2000-09', '2000-10')
  AND country_iso_code='US'
GROUP BY channel_desc;

For more information on the SH schema see Sample Schemas and Schema Diagrams.

Star Schema Benchmark (SSB) Schema

The SSB schema provides a well-known large sample data set. The SSB schema in the Autonomous Data Warehouse contains 1 TB of data. You can use this schema to test the performance of your service. You can run the sample queries on this schema with different database services, HIGH, MEDIUM, LOW and with different number of OCPUs to test the performance of different Autonomous Data Warehouse configurations.

The SSB schema contains the tables: lineorder, customer, supplier, part, and dwdate. See Sample Star Schema Benchmark (SSB) Queries and Analytic Views for a list of sample queries you can use against the SSB schema. Note that you need to prefix the table names with the schema name SSB in your queries.

For more information on database services, see Predefined Database Service Names for Autonomous Data Warehouse.

Security and Authentication in Autonomous Data Warehouse

Autonomous Data Warehouse stores all data in encrypted format in the Oracle Database. Only authenticated users and applications can access the data when they connect to the database.

All connections to Autonomous Data Warehouse use certificate-based authentication and Secure Sockets Layer (SSL). This ensures that there is no unauthorized access to Autonomous Data Warehouse and that communications between the client and server are fully encrypted and cannot be intercepted or altered.

Certificate based authentication uses an encrypted key stored in a wallet on both the client (where the application is running) and the server (where your database service on the Autonomous Data Warehouse is running). The key on the client must match the key on the server to make a connection. A wallet contains a collection of files, including the key and other information needed to connect to your database service in the Autonomous Data Warehouse. For more information on connections to Autonomous Data Warehouse see About Connecting to an Autonomous Data Warehouse Instance.

You do not need to do any manual configuration to encrypt your data and the connections to your database. These are implemented by Autonomous Data Warehouse.

Autonomous Data Warehouse uses strong password complexity rules for all users based on Oracle Cloud security standards. For more information on the password complexity rules see Create Users with Autonomous Data Warehouse.

You can further restrict connections by specifying a network Access Control List (ACL). By specifying a network ACL a specific Autonomous Data Warehouse database only accepts connections from addresses on the ACL and rejects all other client connections. See Set Access Control List with Autonomous Data Warehouse for more information.

Quickstart Tutorials

Provides links to the Autonomous Data Warehouse quickstart tutorials.

Quickstart Tutorial: Getting Started and Provisioning an Autonomous Database

This tutorial shows you how to log in to the Autonomous Data Warehouse console, create an instance, and create a user.

Tutorial icon Quickstart Tutorial – Getting Started and Provisioning an Autonomous Database

Quickstart Tutorial: Connecting SQL Developer to Autonomous Data Warehouse

This tutorial shows you how to connect SQL Developer to a database in Autonomous Data Warehouse.

Tutorial icon Quickstart Tutorial – Connecting SQL Developer and Creating Tables

Quickstart Tutorial: Loading Your Data

This tutorial shows you how to load data from an Oracle Object Store into a database in Autonomous Data Warehouse.

Tutorial icon Quickstart Tutorial – Loading Your Data

Quickstart Tutorial: Running a Query on Sample Data

This tutorial shows you how to use SQL Developer to query tables in an Autonomous Data Warehouse database.

Tutorial icon Quickstart Tutorial – Running a Query on Sample Data

Quickstart Tutorials for Oracle Machine Learning with Autonomous Data Warehouse

These tutorials show you how to use Oracle Machine Learning with Autonomous Data Warehouse.

Tutorial icon Quickstart Tutorial –Creating Projects and Workspaces in Oracle Machine Learning

Tutorial icon Quickstart Tutorial – Creating and Running Notebooks in Oracle Machine Learning

Tutorial icon Quickstart Tutorial – Collaborating in Oracle Machine Learning

Tutorial icon Quickstart Tutorial – Creating a SQL Script in Oracle Machine Learning

Tutorial icon Quickstart Tutorial – Running SQL Statements in Oracle Machine Learning