Advice in designing efficient full-text-search of JSON documents using Cloud . BigTable










1















We need help designing full-text-search with BigTable (of say structure JSON documents) and supporting AND boolean queries



For supporting OR we think our design is relatively fine,
Schema is as such:



Row keys format:



json.field_type_1:value1 : doc_ids_family “docid1”, “docid2" , “docid3”, ...
json.field_type_1:value2 : doc_ids_family “docid2", “docid4” , “docid5", ...
json.field_type_2:value1 : doc_ids_family “docid1”, “docid4" , “docid6”, ...
json.field_type_1:value2 : doc_ids_family “docid6", “docid7” , “docid8", ...



Where each row has a family of columns with in many cases thousands (or millions) of columns where the key is a document ID (blank byte)



Simple OR match is efficient O(1) and then we paginate



But finding the intersection of the columns (document IDs that have right value for 2 types is O(n) of documents essentially - needing to do a full columns range scan we might have loop through thousands of columns before with align them



We cannot rely on having a secondary index table, since the number of criteria (number of fields that the AND query may contain is user defined and we do not know ahead of time the number of fields / values — they are tokenized from JSON documents)



Thanks for any advice










share|improve this question



















  • 2





    Cloud Bigtable isn't necessarily the best tool for the job. Are you unable to index the data in something like Elasticsearch?

    – Gary Elliott
    Nov 18 '18 at 14:27















1















We need help designing full-text-search with BigTable (of say structure JSON documents) and supporting AND boolean queries



For supporting OR we think our design is relatively fine,
Schema is as such:



Row keys format:



json.field_type_1:value1 : doc_ids_family “docid1”, “docid2" , “docid3”, ...
json.field_type_1:value2 : doc_ids_family “docid2", “docid4” , “docid5", ...
json.field_type_2:value1 : doc_ids_family “docid1”, “docid4" , “docid6”, ...
json.field_type_1:value2 : doc_ids_family “docid6", “docid7” , “docid8", ...



Where each row has a family of columns with in many cases thousands (or millions) of columns where the key is a document ID (blank byte)



Simple OR match is efficient O(1) and then we paginate



But finding the intersection of the columns (document IDs that have right value for 2 types is O(n) of documents essentially - needing to do a full columns range scan we might have loop through thousands of columns before with align them



We cannot rely on having a secondary index table, since the number of criteria (number of fields that the AND query may contain is user defined and we do not know ahead of time the number of fields / values — they are tokenized from JSON documents)



Thanks for any advice










share|improve this question



















  • 2





    Cloud Bigtable isn't necessarily the best tool for the job. Are you unable to index the data in something like Elasticsearch?

    – Gary Elliott
    Nov 18 '18 at 14:27













1












1








1








We need help designing full-text-search with BigTable (of say structure JSON documents) and supporting AND boolean queries



For supporting OR we think our design is relatively fine,
Schema is as such:



Row keys format:



json.field_type_1:value1 : doc_ids_family “docid1”, “docid2" , “docid3”, ...
json.field_type_1:value2 : doc_ids_family “docid2", “docid4” , “docid5", ...
json.field_type_2:value1 : doc_ids_family “docid1”, “docid4" , “docid6”, ...
json.field_type_1:value2 : doc_ids_family “docid6", “docid7” , “docid8", ...



Where each row has a family of columns with in many cases thousands (or millions) of columns where the key is a document ID (blank byte)



Simple OR match is efficient O(1) and then we paginate



But finding the intersection of the columns (document IDs that have right value for 2 types is O(n) of documents essentially - needing to do a full columns range scan we might have loop through thousands of columns before with align them



We cannot rely on having a secondary index table, since the number of criteria (number of fields that the AND query may contain is user defined and we do not know ahead of time the number of fields / values — they are tokenized from JSON documents)



Thanks for any advice










share|improve this question
















We need help designing full-text-search with BigTable (of say structure JSON documents) and supporting AND boolean queries



For supporting OR we think our design is relatively fine,
Schema is as such:



Row keys format:



json.field_type_1:value1 : doc_ids_family “docid1”, “docid2" , “docid3”, ...
json.field_type_1:value2 : doc_ids_family “docid2", “docid4” , “docid5", ...
json.field_type_2:value1 : doc_ids_family “docid1”, “docid4" , “docid6”, ...
json.field_type_1:value2 : doc_ids_family “docid6", “docid7” , “docid8", ...



Where each row has a family of columns with in many cases thousands (or millions) of columns where the key is a document ID (blank byte)



Simple OR match is efficient O(1) and then we paginate



But finding the intersection of the columns (document IDs that have right value for 2 types is O(n) of documents essentially - needing to do a full columns range scan we might have loop through thousands of columns before with align them



We cannot rely on having a secondary index table, since the number of criteria (number of fields that the AND query may contain is user defined and we do not know ahead of time the number of fields / values — they are tokenized from JSON documents)



Thanks for any advice







google-cloud-platform google-cloud-bigtable






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 10 '18 at 22:21









Maxim

1,563211




1,563211










asked Nov 15 '18 at 4:33









flexoriumflexorium

61




61







  • 2





    Cloud Bigtable isn't necessarily the best tool for the job. Are you unable to index the data in something like Elasticsearch?

    – Gary Elliott
    Nov 18 '18 at 14:27












  • 2





    Cloud Bigtable isn't necessarily the best tool for the job. Are you unable to index the data in something like Elasticsearch?

    – Gary Elliott
    Nov 18 '18 at 14:27







2




2





Cloud Bigtable isn't necessarily the best tool for the job. Are you unable to index the data in something like Elasticsearch?

– Gary Elliott
Nov 18 '18 at 14:27





Cloud Bigtable isn't necessarily the best tool for the job. Are you unable to index the data in something like Elasticsearch?

– Gary Elliott
Nov 18 '18 at 14:27












1 Answer
1






active

oldest

votes


















0














Addding Gary's comment as an official answer:



Cloud Bigtable isn't necessarily the best tool for the job.



Are you unable to index the data in something like Elasticsearch? Alternatively the Algolia plus Google Cloud Firestore combination should work well for this specific case:



https://cloud.google.com/firestore/docs/solutions/search






share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53312489%2fadvice-in-designing-efficient-full-text-search-of-json-documents-using-cloud-b%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    Addding Gary's comment as an official answer:



    Cloud Bigtable isn't necessarily the best tool for the job.



    Are you unable to index the data in something like Elasticsearch? Alternatively the Algolia plus Google Cloud Firestore combination should work well for this specific case:



    https://cloud.google.com/firestore/docs/solutions/search






    share|improve this answer



























      0














      Addding Gary's comment as an official answer:



      Cloud Bigtable isn't necessarily the best tool for the job.



      Are you unable to index the data in something like Elasticsearch? Alternatively the Algolia plus Google Cloud Firestore combination should work well for this specific case:



      https://cloud.google.com/firestore/docs/solutions/search






      share|improve this answer

























        0












        0








        0







        Addding Gary's comment as an official answer:



        Cloud Bigtable isn't necessarily the best tool for the job.



        Are you unable to index the data in something like Elasticsearch? Alternatively the Algolia plus Google Cloud Firestore combination should work well for this specific case:



        https://cloud.google.com/firestore/docs/solutions/search






        share|improve this answer













        Addding Gary's comment as an official answer:



        Cloud Bigtable isn't necessarily the best tool for the job.



        Are you unable to index the data in something like Elasticsearch? Alternatively the Algolia plus Google Cloud Firestore combination should work well for this specific case:



        https://cloud.google.com/firestore/docs/solutions/search







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Mar 21 at 13:04









        Ramesh DharanRamesh Dharan

        45828




        45828





























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53312489%2fadvice-in-designing-efficient-full-text-search-of-json-documents-using-cloud-b%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Darth Vader #20

            How to how show current date and time by default on contact form 7 in WordPress without taking input from user in datetimepicker

            Ondo