Twitter Live Feed with Oracle Database as a Service and Business Intelligence Cloud Service

In this post I am going to show how you can very quickly get a Twitter live feed streaming into your Oracle Database as a Service (DBAAS) instance.  Although this example will work on any machine with an Oracle client and for any Oracle Database instance putting it on DBAAS makes it extremely easy to utilize our streaming Twitter data inside of other Oracle Cloud applications such as Business Intelligence Cloud Service (BICS).


  1. Thank you to geniuses at Tweepy for making it so I don’t have to deal with oauth or really anything related to the Twitter API.  I don’t know who you are but I love you! ♥
  2. Twitter so I never have to wonder what Kim or Kanye are thinking.
  3. My homey Przemyslaw Piotrowski (I don’t know him either but his examples sustain my laziness in coding) for writing a super helpful series called The Mastering Oracle+Python Series which I fully intend to finish someday.
  4. Who ever this dude is ( whose example made this easy.

The great thing about DBAAS is I actually have access to the underlying server so I can take advantage of the operating system, and languages like python, as well as the database.  Some might be offended by such interloping but I’m not one of them and as I said above there isn’t any requirement to do this on the database server I’m just taking advantage of a machine that has the cycles to spare.

I will assume that you already have a DBAAS instance up and running and are familiar with how to administer it and are also familiar with using tools like MobaXterm to connect to it.

At the time of this example our Database as a Service is running Oracle Linux 6.7 and of the database with all the bells and whistles.  I needed to install 5 things to make this work:

1. plliblzma to make it so I can get the latest EPEL repository without having to deal with “expletive” Error: xz compression not available message


yum install pyliblzma-0.5.3-3.el6.x86_64.rpm

2. Latest EPEL repository for this version of linux


yum install epel-release-6-8.noarch.rpm

3. Then I can install python-pip with ease

yum install python-pip

4. And then we can fetch Tweepy with equal ease

pip install tweepy

5. And last but not least Oracle’s python library for interacting with the database

pip install cx_Oracle

Now that we have all the chunks make sure you can import the stuff you need with python and not get any errors.

[oracle@testdrive-01 ~]$ python
Python 2.6.6 (r266:84292, Jul 23 2015, 05:13:40)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-16)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import tweepy
>>> import cx_Oracle

If you have issue with cx_Oracle you most likely just need to make sure the Oracle environment variables are set via bashrc or however you like to set them.

I created a table in my database to store the JSON document that is the API response


In order to use the Twitter API you will have to register your app with them here at which point they will give you the keys to make oauth work.

Then all we need to do is write (by which I mean copy paste and modify) a little python code and we are ready to rock:

#import libraries
from tweepy import Stream
from tweepy import OAuthHandler
from tweepy.streaming import StreamListener
import cx_Oracle
import datetime
import json

#connection string for database

#get me a connection
conn =cx_Oracle.connect(conn_str)

#turn on autocommit

#object for executing sql

#clob variable

#twitter api application keys
#consumer key, consumer secret, access token, access secret.
asecret='The Password'

#listen to the stream
class listener(StreamListener):

#get some
    def on_data(self, data):

            #barf response insto json object
            all_data = json.loads(data)

            #parse out tweet text, screenname and tweet id
            tweet = all_data["text"]
            if (all_data["user"]["screen_name"]) is not None:
                username = all_data["user"]["screen_name"]
                username = 'No User'
            tid = all_data["id"]

            #set clob variable to json doc
                #create sql string with bind for clob var

                sql_str="INSERT INTO EAT_MY_TWEET (ID,USERNAME,TWEET_JSON) Values("+str(tid)+",q'["+username.encode('utf-8').strip()+"]',:EATIT)" 

                #insert into database 

            except Exception: 

            #watch tweets go by in console 

            #in case you want to print response 
        except Exception: 
def on_error(self, status): 
     print status 
     print sql_str 

#log in to twitter api 
auth = OAuthHandler(ckey, csecret) 
auth.set_access_token(atoken, asecret)
#fire it up 
twitterStream = Stream(auth, listener()) 

#what to search for (not case sensitive) 
#comma separated for words use 
# for hashtag 
#phrases are words separated by spaces (like this comment) 
twitterS.filter(track=["ProveFilterWorks,Oracle,Vlamis,OBIEE,BICS,DVCS,Data Visualization Desktop"])

I named my file so to execute I just fire it up with nohup –

nohup python -u /home/oracle/>/home/oracle/stream.out 2>/home/oracle/stream.err &

and if I tail my stream.out file with tail -f stream.out I can watch the tweets go by as they are inserted into the database

now that I am inserting the json document that the twitter api sends back to me into the database

I can use Oracle Database 12c support for json to expose the document in my table as a view using the following SQL

  SELECT cast(TO_TIMESTAMP_TZ(REPLACE(upper(b.created_on),'+0000','-00:00'),'DY MON DD HH24:MI:SS TZH:TZM YYYY')  at Time zone 'CST' as date) CST_DATE,
cast(TO_TIMESTAMP_TZ(REPLACE(upper(b.created_on),'+0000','-00:00'),'DY MON DD HH24:MI:SS TZH:TZM YYYY') as date) UTC_DATE,
cast(to_char(cast(TO_TIMESTAMP_TZ(REPLACE(upper(b.created_on),'+0000','-00:00'),'DY MON DD HH24:MI:SS TZH:TZM YYYY') as date),'HH24') as number) UTC_HOUR,
cast(to_char(cast(TO_TIMESTAMP_TZ(REPLACE(upper(b.created_on),'+0000','-00:00'),'DY MON DD HH24:MI:SS TZH:TZM YYYY') as date),'MI') as number) UTC_MINUTE,
'$' columns(
    id varchar(50) path '$.id',
    created_on varchar2(100) path '$.created_at',
    screen_name varchar2(200) path '$."user".screen_name',
    location varchar2(250) path '$."user"."location"',
    followers_cnt number path '$."user".followers_count',
    friends_cnt  number path '$."user".friends_count',
    listed_cnt  number path '$."user".listed_count',
    favourites_cnt  number path '$."user".favourites_count',
    statuses_cnt  number path '$."user".statuses_count',
    retweet_cnt number path '$.retweet_count',
    favourite_cnt number path '$.favorite_count',
     url varchar2(250) path '$."user"."url"',
    profile_image_url  varchar2(500) path '$."user".profile_image_url',
    banner_image_url varchar2(500) path '$."user".profile_banner_url',
     hashtags varchar2(500) format json with wrapper path '$.entities.hashtags[*].text',
      tweet varchar2(250) path '$.text'
    -- nested path '$.entities.hashtags[*]' columns (ind_hashtag varchar2(30) path '$.text'    )
) b

Then I can analyze the real time twitter data I’m interested in using a simple sql statement


and while being able to hit real time twitter data with SQL is cool the real goal is being able to surface that data inside of our BI tools. Here we combine Answers and Visual Analyzer on a dashboard to show latest tweets and aggregate information over time utilizing Oracle Business Intelligence Cloud Service (BICS)

Tim and Arthur to Present at Northwest Oracle Users Group Conference October 10

On October 10, Tim Vlamis and Arthur Dayton will be presenting at the Northwest Oracle Users Group Conference in Portland, OR.

The conference will take place at the Portland Marriott Downtown Waterfront from 8:00AM until 5:00PM.

Tim and Arthur will be presenting "Designing an Analytics Strategy for the 21st Century" from 9:30AM to 10:30AM.

For more information and to view the abstract for this presentation, please visit our presentations page.

Part 2 - Database

This is the second in a series of posts on Maps in OBIEE.  Part 1 - Map Basics is here.

In the database geometry objects are stored in a special column type called SDO_GEOMETRY that is specifically for geometric data.  The database stores geometry objects, such as States, as an ordered set of points, which are then connected by line segments by Map Viewer to render the shape.

The type, and its associated metadata views and functions, are provided by the MDSYS schema which is included with your database installation.  Although the SDO_GEOMETRY type is associated with the MDSYS schema it can be used by any database user to create geometry columns in any schema.  The only requirement is that there must be a spatial index on the table that contains the geometry type and there must be an entry in the USER_SDO_GEOM_METADATA view that tells the database that a particular table and column contain geometric data in order for functions and rendering operations to work correctly.

The geometry type is analogous to an XML type in that it is stored as a column in a table but also has its own internal structure that can be addressed by various database functions provided specifically for it.

X/Y Grid

Using an example from the documentation, if we look at the polygon named cola_b rendered on an xy grid we can see that its definition should include the points (5,1, 8,1, 8,6, 5,7, 5,1).

If we look at the COLA_MARKETS table in the database and look at the row that contains cola_b we can see that  the SHAPE column (which is an SDO_GEOMETRY type) does indeed contain those points.  Checking to see we have an entry in the USER_SDO_GEOM_METADATA view and that we have a spatial index on the table we can create a map view of our COLA_MARKETS table right inside of SQL Developer.

COLA_MARKETS table in the database

Map based objects such as a US State are merely a more complex example of a polygon that have a reference to a projection system (SRID) for rendering the earth’s 3 dimensional surface on a 2 dimensional plane.  If we look at a stored geometry for Colorado we can see that the only difference between it and the simple polygon we created for cola_b above is a reference to an SRID code and the number of points (longitude and latitude in this case) needed to render it.

That covers the basics of what you need to know about geometry and the database for now.  The SDO_GEOMETRY column type is used to store geometry definitions in the database and the USER_SDO_GEOM_METADATA view gives the database the information it needs to create spatial indexes.

I’ll show you in another post that lines, points, circles, etc. can also be associated with and rendered on maps and it’s pretty easy to create and use these objects to add even more insight to your map visualizations.  Additionally, we'll talk about several important and useful database procedures & functions that are provided for validating (and fixing if needed) your geometric data which is especially helpful when we import freely available shapefiles into our database to create our own maps for use inside of OBIEE.