How we can do full text search and facets stuffs on Bigquery?

0
votes

In future we have millions of record in our web product. So we had used Bigquery for data storage and analysis. We have to built filters on our search page like flipkart, amazon provides 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 query in such way that it gives result and count of every category(term). Here category means different types filter on different columns as we see on e-commerce sites i.e. 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 solrdb which can be used with the Bigquery to provide above desired functionality.

Is Bigquery not suitable for above purpose? Are we need to stick around any RDBMS database (i.e. postgresql, mysql, etc) and search engine (i.e. solrdb, elastic search, etc) for this purpose?

Thanks

1
ruby-on-railsfull-text-searchgoogle-bigqueryfaceted-searchbigdata
BigQuery does not have indexes. What are facets stuffs? It sounds like you just want to apply a filter with a regular expression, e.g. using REGEXP_CONTAINS, but you haven't provided enough information about what you are trying to do. – Elliott Brossard
@ElliottBrossard I have updated my question. Please let me know your thought on it. – Lalit Kumar Maurya
Maybe you're looking for a visualization tool like Data Studio, Tableau, or Looker? There are a number of different options that integrate with BigQuery. I would suggest trying some out or reading about the features that they support. – Elliott Brossard
Thanks, but these are visualisation tools. I have to implement filter functionality under our web project. – Lalit Kumar Maurya
All of these let you "drill down" to analyze your data. I don't understand what you are trying to achieve still or why you are implementing your own filter functionality instead of using an existing tool (or integrating one into yours). – Elliott Brossard

1 Answers

0
votes

Is there any framework/plugin available like solrdb which can be used with the Bigquery to provide above desired functionality?

As far as I can tell, no, there's nothing like that.

BigQuery still can do this type of analyzes. You can simulate what a search engine database would do through some query in BQ and still it will process everything you need in just a few seconds. You can use Regexp operations, Split strings, use Javascript UDFs to break down words and so on.

I still think though that using a search engine database (as you mentioned, solrdb or elasticsearch) you'd be better off as you'd already have everything ready for you, such as the faceting feature. Nonetheless, BQ will give you results in seconds whereas these engines might return responses in milliseconds.

Google also offers a Search API in App Engine for this matter but you'd have to test it and see if it works well for you. It's biggest advantages I'd say is that it's easy to implement and you don't have to worry about managing an infrastructure. Not sure how it compares to solrdb or elasticsearch though.

Here at our company we have some systems that receives documents from BigQuery already in a JSON format and them a elasticsearch db indexes everything and handles the queries requests. So far it worked well for us.