Introduction

In the previous blog (Hyperlink) we provisioned and connected to an Autonomous Transaction Processing instance. Autonomous Transaction Processing supports a complex mix of high-performance transactions, reporting, batch, IoT, and machine learning in a single database, allowing much simpler application development and deployment and enabling real-time analytics, personalization, and fraud detection.

In this blog you will install Oracle Client libraries, install Visual Studio, install Anaconda and run a few simple commands on Jupyter Notebook.

Step 1: Download the Oracle Instant Client

In order to connect and run applications from your PC to remote Oracle databases, such as Autonomous Transaction Processing, Oracle client libraries must be installed on your computer. Oracle Instant Client enables applications to connect to a local or remote Oracle Database for development and production deployment. The Instant Client libraries provide the necessary network connectivity, as well as basic and high-end data processing features, to make full use of any Oracle database. It underlies the Oracle APIs of popular languages and environments including Node.js, Python and PHP, as well as providing access for OCI, OCCI, JDBC, ODBC and Pro*C applications. Tools included in Instant Client, such as SQL*Plus and Oracle Data Pump, provide quick and convenient data access.

Let us start with Oracle Instant Client for Microsoft Windows (x64) 64-bit. You can find it here. If you happen to run another operating system, you can find the relevant Oracle Instant Client libraries here.

Accept License Agreement and select Basic Lite Package.

 

 

This will require you signing into OTN with your SSO account. If you do not have an account you need to create one.

 

 

Download the file and then proceed to the directory where the file was downloaded. Unzip the file into a directory. Open Command Prompt and navigate to the directory.

 

Add this directory to your path in Windows:

In Search, search for and then select: Advanced Systems Settings (Control Panel)
Click Environment Variables at the bottom of screen
In the System Variables double click Path
In the screen that opens up select NEW
Add full path to the instant client directory (C:\instantclient_18_5)

 

Step 2: Installing Microsoft Visual Studio Redistributable

Oracle Client libraries for Windows require the presence of the correct Visual Studio redistributable. Follow the link below to install:

https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads#bookmark-vs2013

 

Select the correct architecture

 

Double Click the downloaded file and proceed with the installation

 

This completes the installation of the pre-requisites

 

Step 3: Installing Anaconda/Python/Jupyter

Anaconda/Jupyter is a popular IDE. Anaconda/Jupyter is very sensitive to other installed versions and PATH’s associated with previous installations on your computer. If you have other versions of Python installed, remove them as any PATH’s and projects associated with them or this installation may not work.

Download the software from www.anaconda.com/download
Select the Python 3.7 version download highlighted below, make sure you select the one for your correct architecture (32 or 64-bit)

Go to the folder where the file was downloaded and Double Click it. This brings up the Anaconda installation page, go ahead and Click Next.

 

Click I agree on the next screen

 

In the next screen Select Just me and Click Next

 

Install in the following directory: C:\Anaconda3 You must create the directory if the directory does not exist create (the installer will not create it). Click Next

 

Make sure you Select Register Anaconda as my default Python 3.7. Leave Add Anaconda to your PATH environment variable non-selected. Click Install

 

The installation will take a few minutes. Once complete Click Next

 

You will get a prompt to install Microsoft VS Code. Skip this step.

 

Deselect both options in the next screen and Click Next.

 

You must add the new install directory into your PATH. Add C:\Anaconda3\ and C:\Anaconda3\scripts\ to your PATH:

 

In Windows 10:

In Search, search for and then select: Advanced System Settings (control panel)
Click Environment Variables at bottom of screen
In the System variables double click Path
In the screen that opens up select NEW
Add full path to the anaconda directory (C:\Anaconda3)
Add full path to the anaconda scripts directory (C:\Anaconda3\scripts\)

 

Hooray!!! Anaconda and Python is now installed.

 

Step 4: Using Anaconda/Jupyter/Python with Autonomous Transaction Processing

 

Before running any Python apps that access the database, the correct packages must be loaded into the Python environment. Open a Command Prompt Window and navigate to the directory where you installed Anaconda (C:\Anaconda3) and run the following commands in order. pip is a package management system used to install and manage software packages written in Python. We will use pip to install the packages:

 

pip install –upgrade pip

pip install keyring

pip install cx_oracle

pip install sql

pip install ipython-sql

pip install python-sql

 

To Start Anaconda/Jupyter, go to the Windows Start Icon, Click and Select Anaconda Navigator under Anaconda3. Once inside Anaconda, Select Jupyter

 

A new browser page will open up, running Jupyter, Select New and then Python 3 highlighted below:

 

A new Python Notebook will open up. Python is an interpreted language so we must load libraries to use every time an environment is started up. Libraries are loaded with the import command, we will use 3 libraries. Run the following commands as shown below. Copy the 3 lines below and Paste them directly in the box next to the In[]: prompt, then select Run. 

 

import cx_Oracle

import keyring

import os

 

Run a simple command to display your PATH. Run the following command (copy and paste into the box and select Run): print(os.environ["PATH"]

 

Now let us set the TNS_ADMIN variable. TNS_ADMIN is the location of the unzipped wallet files. Instructions on how to create a wallet can be found here (Hyperlink to previous blog post). Below we set and then check the variable (the first command sets it, the second displays it back). Run the following command (copy and paste into the box and select Run):

os.environ['TNS_ADMIN'] = 'c:\wallets'

print(os.environ["TNS_ADMIN"]

 

Let’s make some external calls to the Autonomous Transaction Processing. For that we need to load another library. Run the command below which will load the library needed to call external sql databases (ignore warning/error messages, make sure to include the %):

 

%load_ext sql

 

Next let us connect to the Autonomous Transaction Processing database using a user name, password and service. Use your admin account and password created when the ATP database was created. The format of the command is:

 

%sql oracle+cx_oracle://user:password@service

 

Once connected you will get the message ‘Connected: admin@None’

 

To run a query, once connected use the oracle+cx library calls followed by the SQL statement (notice no ; at the end of the statement). The SQL below is the same one we ran in previous labs, copy the statement below and paste it in the box and click  Run.

 

%sql oracle+cx_oracle://user:password@service

 

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_idAND customers.country_id = countries.country_id AND sales.time_id=times.time_idAND sales.channel_id=channels.channel_idAND times.calendar_month_desc IN ('2000-09', '2000-10')AND country_iso_code='US' GROUP BY channel_desc

 

Awesome. Now you are connected to Autonomous Transaction Processing using Anaconda.

Written by Philip Li & Sai Valluri

Read more: blogs.oracle.com

How to Get Started With Autonomous Data Warehouse

Our previous post Data Warehouse 101: Introduction outlined the benefits of the Autonomous Data Warehouse–it’s simple, fast, elastic, secure, and best of all it’s incredibly easy to spin up an environment and start a new project.  If you read through the last post, you already know how to sign up for a data warehouse trial account and download SQL Developer and Data Visualization Desktop, both of which come free with the Autonomous Data Warehouse. 

Sign up for a Free Data Warehouse Trial Today

This post will focus on the steps to get started using the Oracle Autonomous Data Warehouse. We will provision a new Autonomous Data Warehouse instance and connect to the database using Oracle SQL Developer.

How to Use Autonomous Data Warehouse with Oracle Cloud Infrastructure

STEP 1: Sign in to Oracle Cloud

Go to cloud.oracle.com. Click Sign In to sign in with your Oracle Cloud account.
Enter your Cloud Account Name and click My Services.

Enter your Oracle Cloud username and password, and click Sign In.

STEP 2: Create an Autonomous Data Warehouse Instance

Once you are logged in, you are taken to the cloud services dashboard where you can see all the services available to you. Click Create Instance.

Note: You may also access your Autonomous Data Warehouse service via the pull out menu on the top left of the page, or by using Customize Dashboard to add the service to your dashboard.

Click Create on the Autonomous Data Warehouse tile. If it does not appear in your Featured Services, click on All Services and find it there.

Select the root compartment, or another compartment of your choice where you will create your new Autonomous Data Warehouse instance. If you want to create a new Compartment or learn more, click here.
Note – Avoid the use of the ManagedCompartmentforPaaS compartment as this is an Oracle default used for Oracle Platform Services.

Click on Create Autonomous Data Warehouse button to start the instance creation process.

This will bring up the Create Autonomous Data Warehouse screen where you will specify the configurations of the instance. Select the root compartment, or another compartment of your choice.

Specify a memorable display name for the instance. Also specify your database's name, for this lab use ADWFINANCE.

Next, select the number of CPUs and storage size. Here, we use 4 CPUs and 1 TB of storage.

Then, specify an ADMIN password for the instance, and a confirmation of it. Make a note of this password.

For this lab, we will select Subscribe To A New Database License. If your organization owns Oracle Database licenses already, you may bring those license to your cloud service.
Make sure everything is filled out correctly, then proceed to click on Create Autonomous Data Warehouse.

Your instance will begin provisioning. Once the state goes from Provisioning to Available, click on your display name to see its details.

You now have created your first Autonomous Data Warehouse instance. Have a look at your instance's details here including its name, database version, CPU count and storage size.

Because Autonomous Data Warehouse only accepts secure connections to the database, you need to download a wallet file containing your credentials first. The wallet can be downloaded either from the instance's details page, or from the Autonomous Data Warehouse service console.

STEP 4: Download the Connection Wallet

In your database's instance details page, click DB Connection.

Under Download a Connection Wallet, click Download.

Specify a password of your choice for the wallet. You will need this password when connecting to the database via SQL Developer later, and is also used as the JKS keystore password for JDBC applications that use JKS for security. Click Download to download the wallet file to your client machine.
Note: If you are prevented from downloading your Connection Wallet, it may be due to your browser's pop-blocker. Please disable it or create an exception for Oracle Cloud domains.

Connecting to the database using SQL Developer

Start SQL Developer and create a connection for your database using the default administrator account 'ADMIN' by following these steps.

STEP 5: Connect to the database using SQL Developer

Click the New Connection icon in the Connections toolbox on the top left of the SQL Developer homepage.

Fill in the connection details as below:

Connection Name: admin_high
Username: admin
Password: The password you specified during provisioning your instance
Connection Type: Cloud Wallet
Configuration File: Enter the full path for the wallet file you downloaded before, or click the Browse button to point to the location of the file.
Service: There are 3 pre-configured database services for each database. Pick <databasename>_high for this lab. For
example, if you the database you created was named adwfinance, select adwfinance_high as the service.

Note : SQL Developer versions prior to 18.3 ask for a Keystore Password. Here, you would enter the password you specified when downloading the wallet from ADW.

Test your connection by clicking the Test button, if it succeeds save your connection information by clicking Save, then connect to your database by clicking the Connect button. An entry for the new connection appears under Connections.
If you are behind a VPN or Firewall and this Test fails, make sure you have SQL Developer 18.3 or higher. This version and above will allow you to select the "Use HTTP Proxy Host" option for a Cloud Wallet type connection. While creating your new ADW connection here, provide your proxy's Host and Port. If you are unsure where to find this, you may look at your computer's connection settings or contact your Network Administrator.

Watch a video demonstration of provisioning a new autonomous data warehouse and connect using SQL Developer:

NOTE: The display name for the Autonomous Data Warehouse is ADW Finance Mart and the Database name is ADWFINANCE. This is for representation only and you can choose your name.

In the next post, Data Warehouse 101: Setting up Object Store, we will start exploring a data set, how to load and analyze the data set.

Written by Sai Valluri and Philip Li

Read more: blogs.oracle.com