Singer.io tap for extracting data from Oracle - extended for new Oracle versions
Project description
extended-tap-oracle
Singer tap that extracts data from a Oracle database and produces JSON-formatted data following the Singer spec.
How to use it
Run and configuration of this Singer Tap depends of the desired replication mode (INCREMENTAL or STREAMING)
Prerequisites : Create user on targeted PDB
Connect to CDB, then switch to targetted PDB
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=wms17;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
WMS17
Tap-Oracle user need to be created with the following rights on DB :
- CREATE_SESSION system privilege
grant CREATE SESSION to singer_user;
- SELECT right on V_$DATABASE
grant select on V_$DATABASE to singer_user;
You can also grant select on table to singer, table by table or via SELECT ANY TABLE privilege (up to you)
- SELECT ANY TABLE system privilege
grant SELECT ANY TABLE to singer_user;
Log based replication
Tap-Oracle Log-based replication requires some configuration changes in Oracle database:
-
Enable
ARCHIVELOG
mode -
Set retention period a reasonable and long enough period, ie. 1 day, 3 days, etc.
-
Enable Supplemental logging
Setting up Log-based replication on a self hosted Oracle Database:
To verify the current archiving mode, if the result is ARCHIVELOG
, archiving is enabled:
SQL> SELECT LOG_MODE FROM V$DATABASE
To enable ARCHIVELOG
mode (if not enabled yet):
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG
SQL> ALTER DATABASE OPEN
To set retention period, use RMAN:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
To enable supplemental logging:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
Install and Run
First, make sure Python 3 is installed on your system or follow these installation instructions for Mac or Ubuntu.
It's recommended to use a virtualenv:
python3 -m venv venv
pip install extended-tap-oracle
or
python3 -m venv venv
. venv/bin/activate
pip install --upgrade pip
pip install .
Configuration
Running the the tap requires a config.json
file.
Example with the minimal settings:
{
"host": "foo.com",
"port": 1521,
"user": "my_user",
"password": "password",
"sid": "ORCL",
"filter_schemas": "HR" # Lets get only the HR sample schema
}
You can run a discover run using the previous config.json
file to acquire all the tables definition
tap-oracle --config /tmp/config.json --discover >> /tmp/catalog.json
Then use the catalog.json to run a full export:
tap-oracle --config /tmp/config.json --catalog /tmp/catalog.json
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Hashes for extended-tap-oracle-1.0.7.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 83975e980fc1795fb6dc807665c8e5a2ad47febe61771062bb50f82fb01b5f28 |
|
MD5 | 52549b1d7ff3897366c6b029a17559b8 |
|
BLAKE2b-256 | 633a8be4f5c0c4fa902c442e3f32d75659f41dbf07bd911d0feae39b471e87fe |