Work with text analyzers

The CREATE SEARCH INDEX DDL statement, SEARCH function, and TEXT_ANALYZE function support advanced text analyzer configuration options. Understanding BigQuery's text analyzers and their options lets you refine your search experience.

This document provides an overview of the different text analyzers available in BigQuery and their configuration options, as well as examples of how text analyzers work with search in BigQuery. For more information about text analyzer syntax, see Text analysis.

Text analyzers

BigQuery supports the following text analyzers:

• NO_OP_ANALYZER
• LOG_ANALYZER
• PATTERN_ANALYZER

NO_OP_ANALYZER

Use the NO_OP_ANALYZER when you have pre-processed data that you want to match exactly. There is no tokenization or normalization applied to the text. Since this analyzer does not perform tokenization or normalization, it accepts no configuration. For more information about NO_OP_ANALYZER, see NO_OP_ANALYZER.

LOG_ANALYZER

The LOG_ANALYZER modifies data in the following ways:

• Text is made lowercase.

• ASCII values greater than 127 are kept as is.

• Text is split into individual terms called tokens by the following delimiters:

  [ ] < > ( ) { } | ! ; , ' " * & ?   / : = @ . - $ % \ _ \n \r \s \t ! &
  %26 ; ; | |   + + = = %20 ] ] [ [ : : 
  
  
   , , ( )
  

  If you don't want to use the default delimiters, you can specify the delimiters you want to use as text analyzer options. LOG_ANALYZER lets you configure specific delimiters and token filters for more control over your search results. For more information about the specific configuration options available when using the LOG_ANALYZER, see delimiters analyzer option and token_filters analyzer option.

PATTERN_ANALYZER

The PATTERN_ANALYZER text analyzer extracts tokens from text using a regular expression. The regular expression engine and syntax used with PATTERN_ANALYZER is RE2. PATTERN_ANALYZER tokenizes patterns in the following order:

1. It finds the first substring that matches the pattern (from the left) in the string. This is a token to be included in the output.
2. It removes everything from the input string until the end of the substring found in step 1.
3. It repeats the process until the string is empty.

The following table provides examples of PATTERN_ANALYZER token extraction:

Using the PATTERN_ANALYZER text analyzer gives you more control over the tokens extracted from a text when used with the SEARCH function. The following table shows how different patterns and results result in different SEARCH results:

Examples

The following examples demonstrates the use of text analysis with customization options to create search indexes, extract tokens, and return search results.

LOG_ANALYZER with NFKC ICU normalization and stop words

The following example configures LOG_ANALYZER options with NFKC ICU normalization and stop words. The example assumes the following data table with data already populated:

CREATE TABLE dataset.data_table(
  text_data STRING
);

To create a search index with NFKC ICU normalization and a list of stop words, create a JSON-formatted string in the analyzer_options option of the CREATE SEARCH INDEX DDL statement. For a complete list of options available in when creating a search index with the LOG_ANALYZER, see LOG_ANALYZER. For this example, our stop words are "the", "of", "and", "for".

CREATE OR REPLACE SEARCH INDEX `my_index` ON `dataset.data_table`(ALL COLUMNS) OPTIONS(
  analyzer='PATTERN_ANALYZER',
  analyzer_options= '''{
    "token_filters": [
      {
        "normalizer": {
          "mode": "ICU_NORMALIZE",
          "icu_normalize_mode": "NFKC",
          "icu_case_folding": true
        }
      },
      { "stop_words": ["the", "of", "and", "for"] }
    ]
  }''');

Given the previous example, the following table describes the token extraction for various values of text_data. Note that in this document the double question mark character (⁇) has been italicized to differentiate between two question marks (??):

Now that the search index has been created, you can use the SEARCH function to search the table using the same analyzer configurations specified in the search index. Note that if the analyzer configurations in the SEARCH function don't match those of the search index, the search index won't be used. Use the following query:

SELECT
  SEARCH(
  analyzer => 'LOG_ANALYZER',
  analyzer_options => '''{
    "token_filters": [
      {
        "normalizer": {
          "mode": "ICU_NORMALIZE",
          "icu_normalize_mode": "NFKC",
          "icu_case_folding": true
        }
      },
      {
        "stop_words": ["the", "of", "and", "for"]
      }
    ]
  }''')

Replace the following:

• search_query: The text you want to search for.

The following table demonstrates various results based on different search text and different values of search_query:

PATTERN_ANALYZER for IPv4 search with stop words

The following example configures the PATTERN_ANALYZER text analyzer to search for a specific pattern while filtering certain stop words. In this example, the pattern matches an IPv4 address and ignores the localhost value (127.0.0.1).

This example assumes that the following table is populated with data:

CREATE TABLE dataset.data_table(
  text_data STRING
);

To create a search index the pattern option and a list of stop words, create a JSON-formatted string in the analyzer_options option of the CREATE SEARCH INDEX DDL statement. For a complete list of options available in when creating a search index with the PATTERN_ANALYZER, see PATTERN_ANALYZER. For this example, our stop words are the localhost address, 127.0.0.1.

CREATE SEARCH INDEX my_index
ON dataset.data_table(text_data)
OPTIONS (analyzer = 'PATTERN_ANALYZER', analyzer_options = '''{
  "patterns": [
    "(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)[.]){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)"
  ],
  "token_filters": [
    {
      "stop_words": [
        "127.0.0.1"
      ]
    }
  ]
}'''
);

When using regular expressions with analyzer_options, include three leading \ symbols to properly escape regular expressions that include a \ symbol, such as \d or \b.

The following table describes the tokenization options for various values of text_data

Now that the search index has been created, you can use the SEARCH function to search the table based on the tokenization specified in analyzer_options. Use the following query:

SELECT
  SEARCH(dataset.data_table.text_data
  "search_data",
  analyzer => 'PATTERN_ANALYZER',
  analyzer_options => '''{
    "patterns": [
      "(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)[.]){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)"
      ],
    "token_filters": [
      {
        "stop_words": [
          "127.0.0.1"
        ]
      }
    ]
  }'''
);

Replace the following:

• search_query: The text you want to search for.

The following table demonstrates various results based on different search text and different values of search_query:

What's next

• For an overview of search index use cases, pricing, required permissions, and limitations, see the Introduction to search in BigQuery.
• For information about efficient searching of indexed columns, see Search with an index.