Oracle Adapter

Oracle Adapter

Overview

Develop another adapter for Cloudberry to support Oracle as a backend database.

Note

Since OracleDB does not support if exists syntax, writing PL/SQL will be required. The PL/SQL requires checking the table space, so the Oracle user must create an user named BERRY to use the OracleAdapter.

Summary

Major differences from MySQL adapter

Changes	Explanation
Convert if exists to pl/sql	Oracle 's syntax does not support keyword if exists, creating berry.meta, dropping table, and creating view's if exists should be replaced to pl/sql. (Raw Data / Meta Data)
limit	In Oracle fetch first n rows is equivalent to limit(Raw Data/Meta Data)
replace into	replace in to is used when creating a view in the database. merge into can be used in Oracle instead (Raw Data / Meta Data)
group by	In MySQL the select clause executes first, but in Oracle where clause executes first. Changes in the function of parsing group by. (Raw Data)
Extracting time units	In MySQL there are functions to extract a certain time field, and the usage in Oracle is different. (Raw Data)
Full text search	Different syntax (Raw Data)
Adding quotations	Currently, names such as berry.meta, stats.creatTime are all using quotations to correspond to AsterixDB(Same in MySQL) (Meta Data / Raw Data)
Geo DataType	In AsterixDB the built-in data type Point could be queried by directly using select t.coordinate, but Oracle's SDO_GEOMETRY type requires different syntax

Functions overrode

• parseTimeRelation
• parseTextRelation
• parseDrop
• parseCreate
• parseSelect
• parseGroupByFunc
• parseGroupBy
• parseUpssertMeta
• fieldType2SQLType
• postGeneralQuery in OracleConn

Quick Guide for using this Oracle Adapter on MAC OS X

• Since Oracle DB does not support latest MAC OS X, we install Oracle 12c on a Linux machine, and run Cloudberry and Twittermap on a MAC machine.

1 Deploy Oracle DB

(NOTE: This is the only step done on a Linux machine.)

1.1 Install Oracle DB

Following link is a sample guidance for installing Oracle DB 12c on CentOS 7: https://www.tecmint.com/install-oracle-database-12c-on-centos-7/ If you are using other versioned Linux/Windows systems, please find relevant resources online.

1.2 Create user berry in Oracle

create user berry identified by orcl;
grant connect, resource, dba to berry;

2 Ingest Tweets into Oracle DB

2.1 Install python 3.7 and cx_Oracle

brew install python
pip3 install cx_Oracle --upgrade --user

(reference: https://cx-oracle.readthedocs.io/en/latest/installation.html#installing-cx-oracle-on-macos)

2.2 Ingest Tweets into Oracle DB

cd cloudberry/examples/twittermap
# If you have different username/password or hostname or SID or your oracle configuration, you'll need to modify the link [con = cx_Oracle.connect('berry/orcl@localhost:1521/orcl')] inside those two scripts: ingestOracledata.py, ingestpopulationOracle.py
./script/OracleTwittermapIngestion.sh

3 Configure Cloudberry and Run

Modify cloudberry/neo/conf/applicaiton.conf:

#asterixdb.lang = SQLPP # comment this line
asterixdb.lang = oracle # uncomment this line
oracledb.url = "jdbc:oracle:thin:berry/orcl@localhost:1521:orcl" # uncomment this line with url of your oracle

Done!

Now you can start Cloudberry and Twittermap as before.