I have one question about Oracle NoSQL. It can store huge ammount of data and operate it using oracle.kv.lob package.
It can also translate data to a common relational Oracle Database 11gR2 using External Tables mechanism.
But combining these two mechanisms getting me a big trouble.
In ideal way I want to write:
SELECT PK, oracle_BLOB
WHERE PK in (<list of integer>);
BUT (now difficult part begins)
1)As documentation says, method toOracleLoaderFormat of Formatter interface returns String only. So you can not return unstructured data or especially 1GB of unstructured data in an ordinal way. The workaround is to use temporary files: unload NoSQL BLOB to a stream, write the stream to a temp file and then read this file using oracle BFILE locator. Yes, I encapsulated filewritting mechanism in toOracleLoaderFormat. But this method is slow, on kvlite database it takes 1:41 minutes to select 1,8 GB file and temp file is a really bottleneck.
2) As far as I encapsulated filewritting mechanism in toOracleLoaderFormat function, every time preprocessor invokes this method, a new 1 GB file is created.
And as long as NoSQL does full scan on its database using ETables, every SELECT results in ALL files extracted in temp directory.
As a crutch I used another file with list of PK that I needed. It is taken to be that this file will have been formed BEFORE main SELECT procedes.
So initially formatter function reads this file and then compares Key part of incoming KeyValueVersion variable with contents of this file. If the Key is in list then extracting proceeds.
For example, PK file contains 1000, 1001, 1003 and NoSQL DB contains recors with keys like
Preprocessor invokes Formatter with parameter kvv (key part) = exttab/1000/-/document/archive1000.zip, Formatter parses key, extracts 1000.
Then it reads PK file, finds record 1000 and puts huge 2GB archive to temp dir. When it doesn't find record 9000 it doesn't put a huge archive to temp dir.
Is there any idea how to perform seamlesly SELECT of BLOB (BFILE) from NoSQL to "SQL" database in a less complicated way and in acceptable time?
I use default getLOB(k, Consistency.NONE_REQUIRED, 5, TimeUnit.SECONDS);
The getLOB itself is ok, I'd like to know is there any method to avoid producing temp file.
In my method External Table only invokes Formatter to produce a temp file, it consists only of fields "PK" and "DOCNAME".
There is a view over it which uses Oracle function bfilename to get BFILE locator using DOCNAME and TEMP_DIR Oracle directory.
You can not use BFILE in external tables.
Charles, do you need my BLOB Formatter class in which I realized all workarounds with temp files and so on that I mentioned above? I can post it on forum but the code of it is not a small one (5 kb) and this forum as far as I see does not support tabulation, color highliting and other features which makes the code readable.
I could have send it to your email but I am newbie to this forum and I did not find it.
The code does not contain any trade secret, it is pure investigation.
I think that the fundamental problem is that External Tables rely on the Oracle Data Loader format which only supports BFILE. So what you are doing (generating files) appears to be the only way to get LOBs into an E/T from NoSQL Database. The Oracle Data Loader format just doesn't support any other way of reading them in. Unless I am missing something...
After setting all parameters (publishing, etc..) I tried SELECT and got error which in short sounds like Oracle can not see temp_dir but tries to look up ext_tab directory.
But even when I manually copy the file from temp_dir to ext_tab I got another very strange error:
ora-12801 error signaled in parallel query server P000
ora-29913 error in executing odciexttablefetch callout
ora-29400 data cartridge error
KUP-04095 preprocessor command
/home/oracle/kvhome/exttab/bin/nosql_stream encountered error
"java.lang.IllegalStateException: Error of parsing line 1 of
/home/oracle/kvhome/data/TEXT: content is not allowed in prolog.
/home/oracle/kvhome/data/ is ext_tab directory
/home/oracle/kvhome/exttab/bin/ is nosql_bin_dir directory
TEXT is the file I tried to get as blob
What am I doing wrong? May be preprocessor tries to parse TEXT file?