[ad_1]
In October 2022, BigQuery launched the search indexes and SEARCH operate that allow utilizing Google Commonplace SQL to effectively pinpoint particular information components in unstructured textual content and semi-structured information. In a earlier weblog submit, we demonstrated the efficiency positive aspects achievable by using search indexes on the SEARCH operate.
Immediately, BigQuery expands the optimization capabilities to a brand new set of SQL operators and capabilities, together with the equal operator (=), IN operator, LIKE operator, and STARTS_WITH operate when used to match string literals with listed information. Which means that you probably have a search index on a desk, and a question that compares a string literal to a price within the desk, BigQuery can now use the index to search out the rows that match the question extra rapidly and effectively.
For extra details about which current capabilities/operators are eligible for search index optimization, seek advice from the Search listed information documentation.
On this weblog submit we cowl the journey from creating an index and effectively retrieving, through just a few illustrative examples, and share some measured efficiency achieve numbers.
Take Benefit of Search Index Optimizations with Present SQLs
Earlier than this launch, the one technique to benefit from a BigQuery search index was to make use of the SEARCH operate. The SEARCH operate is highly effective. Along with column-specific, it helps cross-column search, which is especially useful in circumstances of complicated schemas with lots of of columns, together with nested ones. It additionally supplies highly effective case delicate and case insensitive tokenized search semantics.
Regardless that the SEARCH operate could be very versatile and highly effective, it might not at all times present the precise consequence semantics one could also be in search of. For instance, contemplate the next desk that incorporates a simplified entry log of a file sharing system:
Desk: Occasions
[ad_2]
Source link
