7 Replies Latest reply on May 9, 2013 9:43 AM by 1007762

    NoSQL + SQL LOBs

      Hello, everyone!

      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
      FROM NoSQL_ExternalTable
      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?

      Edited by: 1004734 on May 8, 2013 3:26 PM
        • 1. Re: NoSQL + SQL LOBs
          Charles Lamb
          I just wanted to point out that kvlite is not tuned in the least and you should not base any kind of performance numbers on it.

          What are the Consistency parameters that you are using to read the LOBs into External Tables?

          • 2. Re: NoSQL + SQL LOBs
            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.

            If you want to know more details, please ask me.
            • 3. Re: NoSQL + SQL LOBs
              Charles Lamb
              Arsenikum wrote:
              I use default getLOB(k, Consistency.NONE_REQUIRED, 5, TimeUnit.SECONDS);
              Since you are using Consistency.NONE_REQUIRED, performance will be improved if you have a replication factor > 1. kvlite does not support rf > 1.

              As I said, I will think about the temp file issue.

              • 4. Re: NoSQL + SQL LOBs
                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.

                Edited by: Arsenikum on 08.05.2013 12:33
                • 5. Re: NoSQL + SQL LOBs
                  Charles Lamb
                  Sure, send it over. charles.lamb @ obvious-domain.com
                  • 6. Re: NoSQL + SQL LOBs
                    Charles Lamb
                    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...

                    • 7. Re: NoSQL + SQL LOBs
                      Charles, sorry, I tried to send the file from my personal mail (mail.ru domain), but your mail is unroutable.

                      By the way I tried to create eTable:

                      CREATE TABLE nosql_data_blob_test (PK integer,
                      docfilename varchar2(120),
                      docdata blob)
                      organization external
                      (type oracle_loader
                      default directory ext_tab
                      access parameters
                      (records delimited by newline
                      preprocessor nosql_bin_dir:'nosql_stream'
                      fields terminated by '|'
                      PK CHAR(30),
                      docfilename char(60)
                      COLUMN TRANSFORMS (docdata FROM LOBFILE (docfilename) FROM (temp_dir) BLOB)
                      location (ext_tab:'nosql_blob.dat'))

                      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?

                      I go holydays up to 12 may, see you that day.

                      Edited by: Arsenikum on 09.05.2013 2:43