4

In the future, we will have millions ofs record in our web product. So we had used BigQuery for data storage and analysis. We have to build filters on our search page like Flipkart and Amazon provide product filters.

Basically, we need different filters on left side of our search page. With help of these filters, we can see our desired results. Every filters have their count, i.e., record counts in that category/term. So we have to build queries in such way that they give result and count of every category(term). Here, "category" means different types filter on different columns as we see on e-commerce sites, e.g., Flipkart, Amazon etc.

Faceted search (also called faceted navigation, guided navigation, or parametric search) breaks up search results into multiple categories, typically showing counts for each, and allows the user to "drill down" or further restrict their search results based on those facets.

Is there any framework/plugin available like Solr which can be used with BigQuery to provide above desired functionality?

Is BigQuery not suitable for above purpose? Do we need to stick with any RDBMS database (e.g., PostgreSQL, MySQL, etc.) and search engine (e.g., Solr, Elasticsearch, etc.) for this purpose?

Improve this question

2 Answers 2

Reset to default
5

Is BigQuery not suitable for above purpose? Do we need to stick with any RDBMS database (e.g., PostgreSQL, MySQL, etc.) and search engine (e.g., Solr, Elasticsearch, etc.) for this purpose?

Correct, BigQuery does not provide search or faceting capabilities. BigQuery is a data warehouse, so it's good for running analytics over large amounts of data, but it does not provide indexing, search, or facets.

You may be able to use Google Cloud Datastore (document database) for this, since it provides secondary indexes, or you may want to do as you suggested, and use a combination of a SQL database such as MySQL or PostgreSQL together with a search product such as Elasticsearch or Solr.

Improve this answer
0
1

BigQuery support a collection of RegEx and String query functions, making it suitable for text search queries across STRING fields. However, there is a 64k per row (and field) limit for each BigQuery record, so it may not possible to support a totally unstructured, unlimited size, document text search case.

https://developers.google.com/bigquery/docs/query-reference#stringfunctions

https://developers.google.com/bigquery/docs/query-reference#regularexpressionfunctions

For a full text search capabilities I would suggest looking at the Search API:

https://cloud.google.com/appengine/docs/standard/python/search/

If you want to choose the option of using a RDBMS database that supports full text search, you will have to connect it to BiqQuery with some external tool like Alooma.

Improve this answer
4
  • Thanks, I have ended with Mongodb (mongoid) + Elasticsearch (searchkick) in my Rails application. It is working great at my end.
    – Lalit Kumar Maurya
    Commented Aug 18, 2017 at 6:17
  • Hi, your answer is almost an exact copy of stackoverflow.com/a/11265567/2420536 When a question is duplicated please rather refer the asker to the original question than to duplicate the answer from the related question.
    – Nameless One
    Commented Oct 16, 2020 at 7:17
  • The search via documentation and index is no longer supported.
    – Ameya
    Commented Aug 9, 2021 at 9:29
  • The litecene library for Java implements a user-friendly boolean query syntax with a transpiler to a BigQuery SQL predicate: github.com/sigpwned/litecene. It makes use of regular expressions and/or the new BigQuery SEARCH function, depending on the query and availability of a search index. Disclaimer: I am the library's author.
    – sigpwned
    Commented May 16, 2022 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.