Very simple question . How can I migrate few tables from Oracle to couchbase without using a third party tool like Talend ?
I was looking for couple of threads where somebody suggested to use GoldenGateAdapter whereas in other place suggested to use a undocumented open source java library to move the data. We don’t have GoldenGate and other undocumented library not working and throwing class exceptions and that could be because of the CB version.
Do we have any clear documentation or code or library how can we move the data ?
thanks
Eldorado,
Take a look at this blog. https://blog.couchbase.com/how-to-use-n1ql-to-migrate-oracle-data-into-couchbase/
-binh
Thank you @binh.le . Very comprehensive and that is what we are looking for .
Few questions to you : This REST API process vs cbimport (assuming that export was done in csv from Oracle) or oracle2couchbase (utility written by Manuel Hurtado, Solutions Engineer, Couchbase ) : https://github.com/mahurtado/oracle2couchbase
among all three process what do you suggest for TB’s of data move from oracle to CB … Its 700 Billions records.
thanks
There are some limitations to the N1Q L Curl and Oracle REST api approach, and that is the limit of 64MB with the N1QL Curl. There are ways to get around this by doing this incrementally with Oracle OFFSET and FETCH NEXT. However, I would not recommend this approach with large volume of data, as I had stated in the blog.
What the approach in the blog highlights is the ability to use N1QL to transform the data, as you migrate into Couchbase.
-binh
@manuel.hurtado - Dear Manuel ,
Need your help on your well written github auto data migration process outlined here :
As this is very old source code, do you mind me asking if you get chances to make it more customized , for example I am looking for passing queries across the parameter rather using : -DoraTables=F_CUSTOMER_ORDER
I have massive table to move and possibly I want to join that table with bunch of other dimensions and move over from Oracle to CB bucket . So for me query will give more flexibility .
Can you help here how can I leverage your java package to get the data ported ?
Thanks again and sorry not intention to bother you and I think this does the job except less flexibility .
Looking forward for your help,
@binh.le - Hi Binh , Need your help -
When I am executing UPSERT with CURL for single document pull from Oracle to my CB Bucket using the Query tool on Web Console I am hitting below problem : Here my oracle exist on diff host while CB bucket exist in completely diff host . Is that a problem you see here as I have checked port 8080 open for access from my CB host ?
[
{
“code”: 5010,
“msg”: “Error evaluating ExpressionScan. - cause: Invalid JSON endpoint http://myoracledbnode:8080/ords/oracleschema/_/sql”
}
]
I would try running the curl statement from the command line on the CB query node. Does it work there?
Unfortunately Not . I have tried to execute that same as you said inside the POD node using below : and then UPSERT same issue .
cbq -e http://localhost:8093/ -s “select * from test” -u Administrator -p password
or
cbq -e http://localhost:8091/ -s “select * from test” -u Administrator -p password
I am pretty sure its not problem reaching to ORDS as simple CURL works from other env and even from my local :
So not sure what I am missing
The request is supposed to be sent to your Oracle server, not the Couchbase server. So you need to:
- Add the Oracle server IP to the request. http://<your oracle server ip here>:8080
- Also follow the instructions to :
- Enable Oracle schema for Rest Services
https://blog.couchbase.com/how-to-use-n1ql-to-migrate-oracle-data-into-couchbase/
Sorry I should be giving full snippets : , I understand that part …
cbq -e http://localhost:8091 -u=Administrator -p=password
cbq> UPSERT INTO test (key ndoc.doc_key,value ndoc)
SELECT ndoc.doc_key, ndoc
FROM CURL(“http://oraclehost:8080/ords/oracleschema/_/sql”,
{ “request”:“POST”,“header”:“Content-Type: application/sql”
, “data”:“SELECT ‘source’ doc_type, ‘source_id:’ || ds.source_id doc_key, ds.*
FROM source_dim ds”
, “user”:‘oracleschema:oracle’}
) r
UNNEST r.items[0].resultSet.items as ndoc ;
When execute in in Web console Query It throws :
[
{
“code”: 5010,
“msg”: “Error evaluating ExpressionScan. - cause: Invalid JSON endpoint http://oraclehost:8080/ords/oracleschema/_/sql”
}
]
Service from Oracle schema already enabled and I can send CURL request with same ORDS URL to get response in JSON if I do it outside of CB …say in my local CURL . The access for that bucket is enabled in role for UPSERT .
Also I have noticed why the confusion happened … CB forum not liking my word under open angle < and close angle > where I was trying to write oracleschema or oraclehost.
Not sure why CB is not being able to reach Oracle DB endpoints …
@binh.le - Sorry again I have lately edited and corrected and remove all open angle ‘<’ and close angles bracket ‘>’ so this is where I stand on current issue… your help is appreciated
Post as Pre-ormated text. Highlet the text and click the 6th button (lessthan+slash+gratethan)
understand ..learning <thanks>
Can you run the SELECT… from the Couchbase Workbench?
SELECT ndoc.doc_key, ndoc
FROM CURL("http://oraclehost:8080/ords/oracleschema/_/sql",
{ "request":"POST","header":"Content-Type: application/sql"
, "data":"SELECT 'source' doc_type, 'source_id:' || ds.source_id doc_key, ds.*
FROM source_dim ds"
, "user":"oracleschema:oracle"}
) r
UNNEST r.items[0].resultSet.items as ndoc ;I am not familiar with that but I will try if you please refer the latest document for 6.0 which I should use . I will do the external setup before running this.
thanks
Couchbase Web Console is the main graphical user interface for the Couchbase,. https://docs.couchbase.com/server/6.0/manage/management-tools.html
You can access it from your browser. http://<couchbase_server_ip>:8091, then choose Query to bring up the Query Workbench.
Hi Binh
I am doing the same UPSERT from QUERY workbench at web console itself . Same error and same issue.
Was not sure before it called as Query Workbench …My bad !
Not sure if this issue is related to the Oracle access from CB Cluster installed in Kubernetes platform .
However I can ran all other queries from this Bucket w/o issue.
thanks
@binh.le - Also I have tried the same in CB cluster installed with different datacenter and I got some different message with UPSERT .
“msg”: “Error evaluating ExpressionScan. - cause: URL end point isn’t whitelisted http://oraclehost:8080/ords/oracleschema/_/sql. Please make sure to whitelist the URL on the UI.”
Not I got more confused how to make it work.