Real-Time Bitcoin Price Streaming using Oracle Cloud Services

Oracle Cloud

In this article we will demonstrate how to provision Oracle Cloud services to stream Bitcoin prices from a crypto currency API. An instance of the Oracle Autonomous Cloud Database (ADW) as our cloud data warehouse, an Oracle Compute Instance (Oracle Linux operating system), and Oracle Block Storage to attach to our compute instance are needed to execute the python scripts without interruption. These services are all provided under Oracle’s free tier leveraging both IaaS (Infrastructure as a Service) and PaaS (Platform as a Service). The ADW instance is limited to 20GB and 1 OCPU and the compute instances are about 1/8 an OCPU which should be enough compute power to run the simple Python scripts. We will also run Python scripts and VNC (Virtual Network Computing) client and server to provide an interactive GUI (Graphical User Interface) for our compute instance. We will also configure cloud security rules to allow communication with internal services and 3rd Party APIs. The Oracle Instant Client is necessary to enable communication between our compute instance and ADW. Then we will show how to execute Oracle SQL statements in Python and create a scheduler to request data from the API and push to ADW. Now that we have some data in ADW, we can use the Oracle Analytics Cloud (OAC) to visualize the data we have collected. The goal is to showcase how to leverage some cloud services to collect real-time data. Note: I am running a macOS High Sierra version 10.13.6 operating system.

Steps:

  1. Install Python

Download Python3:

If you have a mac, you likely already have python installed. You can check whether you have python by simply typing in a terminal window:

python -V

This should return something that starts with a 3. If “command not found” you need to install python. You can find those instructions here. If you plan to work on some data science projects, I would recommend downloading anaconda distribution.

Test by again entering the same command above into your terminal and seeing the version of python above 3. You can also enter:

python
print("hello world")

The execution of these lines should return return hello world.

Create Oracle Free Tier Account:

Before provisioning instances, you may want to see all the free cloud services available by going the Oracle website. Most of what we provision will be under the free tier, and I will note when you might have to pay out of pocket for a specific service.

First create an Oracle Cloud account by simply clicking the “start for free” button in the previous link I posted. When you start a free trial you must enter a credit card, but as long as you use free cloud services you will not be charged. I will note when a specific service is not under the free tier.

Provision Compute Instance:

To create a new compute instance simply click the hamburger menu button on the top left of your screen, and then click “compute”. There are also sometimes shortcut links on the homepage where you can click “Create a VM instance”.

Here is a link to help you setup a compute instance. For now (though it is generally bad practice in a production environment), you can setup all your infrastructure services in the root compartment. The other tricky parts involve enabling a public IP address and entering the SSH key. Please be sure to select the “Assign public IP address”. This allows you to more easily connect to virtual machine using your laptop (we will get into more detail on how to do this later). If you are using a mac or linux machine you want to look for the id_rsa.pub. This file is located in your .ssh folder (a period in front of the .ssh means that the folder is hidden; hit cmd + shift + . to show all hidden folders. If you use the finder application, this folder is usually located under your username. If you don’t see this folder and need help, click here.

You can usually open the id_rsa.pub file using a text editor. You can then copy and paste that entire string to the compute instance.

Provision Oracle Block Volume/Storage:

Here is a link that provides pretty good documentation on how to setup Block Volume. The basic idea is that once you provision a block volume instance you can attach it to your compute instance. This gives your virtual machine some disk space to install applications and store data. Don’t worry about backup policy since this tutorial is just for learning. If you need more or less space, you can always select the block volume and hit the “resize” button.

If you select the compute instances, there should be a button called “Attach Block Volume” that allows you to attach the block volume you just created to a compute instance. Note: you will also see a boot volume attached which contains the operating system information to setup and configure the virtual machine.

Provision Autonomous Data Warehouse (ADW) instance:

Similar to provisioning the compute instance, simply click on the hamburger menu on the top-left part of the screen and select “Autonomous Data Warehouse” (ADW). As part of the free tier, you can provision a free instance of ADW with 20GB which is more than enough for this project. The default field values are sufficient to launch an instance. But please remember the password you use to create the instance (if you are using a mac, I use the “notes” application to document my progress). The password allows us to connect to the data warehouse. Note: Your username is “Admin”. Note: it should take a few minutes for most of these services to provision; once the instance turns green you know it has successfully provisioned.

Autonomous Data Warehouse after being correctly provisioned

Edit security:

To allow our infrastructure services like compute and ADW to communicate with the internet, we need to make some changes to our security rules. First, we want to add an ingress rules that allows traffic to flow to our virtual cloud network (which includes our compute instance, block storage, and other infrastructure services that we haven’t provisioned). Below are the ingress rules to allow information to flow into our cloud network:

Here are the egress rules that enable our cloud services to send information out to others:

Notice that SSH Remote Login Protocol is enable which limits communication with our services to those with the correct unique machine keys, id_rsa file.

Here is some documentation detailing how to control traffic exiting, egress rules, and entering, ingress rules, your cloud network.

VNC Viewer and Server setup:

VNC allows your local machine to not only connect to the virtual machine (compute instance we setup in the Oracle Cloud) but also have a GUI (Graphic User Interface) to interact with the machine.

After you have logged into your compute instance (Oracle Linux), you can setup the VNC server:

Install Server on your computer instance’s Oracle Linux operating system:

sudo yum install tigervnc-server

Set password:

Copy template file to the right spot:

sudo cp/lib/sysemd/system/vncserver@.service /etc/system/system/vncserver@\:1.service

Edit the config file and replace all <USER> with your Oracle Linux VM’s username (username is usually “opc”):

sudo vim/etc/system/system/vncserver@/:1.service

Reload, start, and enable the VNC Server:

sudo systemct1 daemon-reload
sudo systemct1 start vncserver@\:1.service
sudo systemct1 enable vncserver@\:1.service

Configure firewall to let vnc traffic flow through a particular port, in this case port 5091:

sudo firewall -cmd - zone=public - add-service=vnc-server
sudo firewall -cmd - zone=public - add-service=vnc-server -permanent
sudo firewall -cmd - zone=public - add-port=5091/tcp
sudo firewall -cmd - zone=public - add-port=5901/tcp -permanent

Install Desktop GUI:

sudo yum groupinstall "server with gui"
sudo systemct1 set-default graphical.target
sudo systemct1 restart vncserver@\:1.service

Setup the VNC Viewer on YOUR machine. The following commands enable your machine to access the server (note: the ‘<>’ is a placeholder for information noted within the <>; example: <name> means for you to enter your name like ‘Ary Sharifian’:

Install VNC Viewer from this link.

ssh -I <private_key_path> -L 5901:localhost:5901 opc@<vm_public_ip_address> -N&

Open VNC Viewer, click on “File”, then click on “New Connection”

In the VNC Server Parameter, enter localhost:<vnc_server_port> and give the connection a name.

On the next page, enter the password that was previously set to access the desktop environment.

To disconnect, run the command below. The process ID can be found as output from the command in step 2:

Verify the disconnection by checking the machine’s list of ssh connections:

ps aux | grep ssh

Note: you may also want to check whether python is installed on this instance, since we will be executing python scripts on this machine. We are using a compute instance instead of our local machine to run these python scripts because the scripts on our local machine will stop once our compute is closed but scripts on a compute instance will run uninterrupted.

Crypto Currency API Setup:

Here we are going to setup a crypto currency account to pull bitcoin pricing data. I used a free crypto currency api from cryptocompare, link here.

Click, “Get your free API key”, then “Get your free key”. I logged in using Google, but you can create a new account or login using Facebook. Note: you are limited to 100,000 calls per month which should be more than enough for this case, but if you want to make sure just count the number of times your python scripts will execute a request.

After you created your account, you should see your API key hidden. That key will allow you to access the server which holds crypto currency info like the real-time price for bitcoin, etc.

There is a button on top right part of the screen which says “</> use key”, click on that. This page shows you how to use the key to make API calls.

The part before the “?” in the GET call represents the base url to make the request. The part after the “?” represents the query parameters identifying what you would like the server to return. For example:

https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=USD,JPY,EUR

Note: this query is requesting the price of Bitcoin in USD, JPY, and EUR currency.

Test: Crypto Currency API call

Okay, go to your compute instance terminal and enter:

python

Attempt to print “hello world” to the console:

print("hello world")

Note: sometimes copying and pasting these commands into your terminal might cause an error because of how the characters are formatted. If you have an error here, just try typing the python code manually instead of copying and pasting it into terminal.

If you get “hello world” after executing the print statement, we are good. Otherwise, you might have an issue with the version (make sure its python 3) or you didn’t install python correctly.

Enter:

Import the following modules for the api:

import json
import requests

Enter your crypto currency info from the api account you created earlier. Headers represents the parameters needed to validate your api request, like a key to a lock:

api_token = <enter token here within single quotes,’’>

headers = {'Content-Type': 'application/json', 'Authorization': 'Bearer {0}'.format(api_token)}

Example of api_token:

api_token = ‘k;jlkrj23;klr2j3k2;l.3/2,4m432’

Create a function that will make the API request. Here, I hard coded the api_url to get Bitcoin prices in USD, JPY, and EUR, but you can always ask the user which cryptocurrency data they want and in what type of currency and simply concatenate the string with user input:

def get_crypto_info(): 

api_url = 'https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=USD,JPY,EUR'
#if successful request
if response.status_code == 200:
return json.loads(response.content.decode('utf-8'))
else:
return None
response = requests.get(api_url, headers=headers)

Finally, we can test the API request through these scripts. After executing these scripts you should get the current price of Bitcoin in USD. You can double check this result by googling the “Bitcoin price” and seeing if it matches your output:

crypto_data = get_crypto_info()
print(crypto_data.get('USD'))

If everything executed as planned, your crypto currency API is working.

Great job!

Instant Client setup:

The instant client uses the ADW instance wallet to connect your machine to ADW instance. This allows you to run Python scripts that connect to and pull/push information to the ADW instance.

Follow the instructions below to upload your ADW credentials to the Oracle Linux Virtual Machines (compute instance) we created earlier:

Remember the ADW instant client credential we downloaded earlier? We need this credential in the compute instance. Log into your compute instance we provisioned earlier. re-download the credential by logging into your Oracle Cloud account and select “Autonomous Data Warehouse” and then select “DB Connection”, then select “Instant Wallet”.

Next, we are going to download the Oracle Instant Client packages assuming you have Oracle Linux version 6 or 7 as your operating system (note: I usually download one version below the latest release because they are usually more stable):

Download the basic package (RPM).

Download the SQL * Plus Package (RPM).

Download the SDK Package (RPM).

Note: in the current iteration of these packages, the version is 18.3.0.0.0.0, but things can obviously change since the release of the article.

Next, you want to install these packages in your virtual machine. While you are in the compute instance run the following commands in terminal:

sudo rpm -ivh oracle-instantclient18.3-basic-18.3.0.0.0-3x86_64.rpm sudo rpm -ivh oracle-instantclient18.3-devel-18.3.0.0.0-3.x86_64.rpm sudo rpm -ivh oracle-instantclient18.3-sqlplus-18.3.0.0.0-3.x86_64.rpm sudo su echo "/usr/lib/oracle/18.3/client64/lib" > /etc/ld.so.conf.d/oracle_client.conf/sbin/ldconfig

Note: These instructions will install the instant client at: /usr/lib/oracle/18.3/client64/

Next, we will configure some files to enable a connection to ADW.

First we will create a “/admin/” folder under “usr/lib/oracle/18.3/client64/” (note: make sure you execute the mkdir command while in the client64 folder; the mkdir command is the equivalent of opening the folder and adding a new folder through the GUI).

Next, unzip the ADW instant wallet that we downloaded earlier into the admin folder we just created. Here, you will move the zip file to the admin folder and then unzip the folder. This will create a mess of files inside the admin folder but that is okay.

Then find the sqlnet.ora file in the mess of files you just unzipped and open the file. Look for “Directory=” and set the variable equal to “/usr/lib/oracle/18.3/client64/network/admin”.

Next, set your bash profile, a file that defines all your paths/variables to execute commands:

This allows you to edit your profile:

vi ~/.bash_profile

Add the following (meaning edit your bash profile and add the strings below):

export TNS_ADMIN = /usr/lib/oracle/18.3/client64/network/admin
export PATH = /usr/lib/oracle/18.3/client64/bin:$PATH
export LD_LIBRARY_PATH= /usr/lib/oracle/oracle/18.3/client64/lib
export ORACLE_HOME = /usr/lib/oracle/18.3/client64/

Note: the above is based on the default Oracle Linux compute instance that I provisioned; it is possible that your compute instance could have files located somewhere different.

Test: making sure we can connect to ADW via Instant Client

This part is important to store our real-time bitcoin prices to a warehouse.

In terminal enter the following:

python
print("hello world")

Okay, if those were successful, lets give it a shot:

import cx_Oracle
import os
connection = cx_Oracle.connect('admin', '<password>','<connection string>')

Note: connection string is usually the name of the database underscore high/medium/low.Example: db123_high.

Note: the high/medium/low defines the level of resources given to your query. High means you get full parallel processing capability while low means your queries are executed sequentially. Think of it like you want to print a bunch of articles via printer and high lets your print articles with a bunch of different prints while low means you get 1 printer and they print according to the order you hit print. More information about that here.

#for executing sql to ADW
cursor = connection.cursor()
#testing connection
rs = cursor.execute("select 'Hello for ADB' from dual")
rs.fetchall()

If after executing the last line of code, there is a response that outputs the following: [(‘Hello for ADB’,)]

Then you have successfully installed the instant client and made a connection to ADW, great job!

If you want make the same connection but through a different path, check out this source.

Okay, now we have almost all our ingredients to do some real-time bitcoin streaming.

Python: create table, execute SQL, create scheduler

Since we have all of the ingredients to create a connection to ADW and pull data from the API, we have provided code below that shows how to simply create a table, use paramstyles with Oracle SQL, and create a scheduler to execute scripts. With respect to paramstyles, every database references python variables in SQL differently.

Let’s enter the following into our terminal on the compute instance we provisioned earlier (I recommend entering the code line by line, unless there is a function in which case you should enter the entire block — some of this code is repeated in earlier sections above):

Note: I have obfuscated or left blank vital credentials; please enter your information to these fields.

OAC Data Visualization:

We can visualize the Bitcoin data we have captured by connecting our ADW instance to OAC. OAC is similar to Tableau or QlikView which are both visualization and reporting tools. We can also use Python packages like Matplotlib, Seaborn, and even Pandas to visualize the data we captured, but I want to highlight how to best leverage an Oracle Cloud PaaS service that can simplify data exploration.

Here are some instructions to provision an OAC instance. After you have provisioned OAC, you can use these instructions, starting at part 2, to connect OAC to ADW. Once there is a connection established, you can pull data. This blog post details the entire process with some great visuals. Here is a blog post that covers using machine learning in OAC. There are also some built-in advanced analytics that include clusters and trend lines that can be leveraged.

Note: you may want to gather some additional data about bitcoin prices to create a robust machine learning model. In other words, only having bitcoin price data likely won’t create a useful model.

Thanks

Thank you to Paul Chyz, Ji Zhang, and Carl Lejerskar for their contribution to this article.

Originally published at https://medium.com on February 3, 2020.

Oracle DevOps Engineer. The views expressed here are my own and do not necessarily reflect the views of Oracle. https://www.linkedin.com/in/arshya-sharifian/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store