Work with text analyzers

TheCREATE SEARCH INDEX DDLstatement,SEARCH function, andTEXT_ANALYZEfunctionsupport advanced text analyzer configuration options. UnderstandingBigQuery's text analyzers and their options lets you refine your searchexperience.

This document provides an overview of the different text analyzers available inBigQuery and their configuration options, as well as examples ofhow text analyzers work withsearch inBigQuery. For more information about text analyzer syntax, seeText analysis.

Text analyzers

BigQuery supports the following text analyzers:

• NO_OP_ANALYZER
• LOG_ANALYZER
• PATTERN_ANALYZER

NO_OP_ANALYZER

Use theNO_OP_ANALYZER when you have pre-processed data that you want to matchexactly. There is no tokenization or normalization applied to the text. Sincethis analyzer does not perform tokenization or normalization, it accepts noconfiguration. For more information aboutNO_OP_ANALYZER, seeNO_OP_ANALYZER.

LOG_ANALYZER

TheLOG_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 calledtokens by the followingdelimiters:

  [] < >(){}|!;,' " * & ? + / : = @ . - $ % \ _ \n \r \s \t %21 %26%2526 %3B %3b %7C %7c %20 %2B %2b %3D %3d %2520 %5D %5d %5B %5b %3A %3a %0A%0a %2C %2c %28 %29

  If you don't want to use the default delimiters, you can specify thedelimiters you want to use as text analyzer options.LOG_ANALYZER lets youconfigure specific delimiters and token filters for more control over yoursearch results. For more information about thespecific configuration options available when using theLOG_ANALYZER, seedelimiters analyzeroptionandtoken_filters analyzeroption.

PATTERN_ANALYZER

ThePATTERN_ANALYZER text analyzer extracts tokens from text using a regularexpression. The regular expression engine and syntax used withPATTERN_ANALYZER isRE2.PATTERN_ANALYZERtokenizes 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 ofPATTERN_ANALYZER token extraction:

Using thePATTERN_ANALYZER text analyzer gives you more control over thetokens extracted from a text when used with theSEARCHfunction. The followingtable shows how different patterns and results result in differentSEARCHresults:

Examples

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

LOG_ANALYZER with NFKC ICU normalization and stop words

The following example configuresLOG_ANALYZER options withNFKC ICUnormalization and stop words. The example assumes the following data table withdata already populated:

CREATETABLEdataset.data_table(text_dataSTRING);

To create a search index with NFKC ICU normalization and a list of stop words,create a JSON-formatted string in theanalyzer_options option of theCREATESEARCH INDEX DDLstatement.For a complete list of options available in when creating a search index withtheLOG_ANALYZER, seeLOG_ANALYZER.For this example, our stop words are"the", "of", "and", "for".

CREATEORREPLACESEARCHINDEX`my_index`ON`dataset.data_table`(ALLCOLUMNS)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 extractionfor various values oftext_data. Note that in this document the doublequestion mark character (⁇) has been italicized to differentiate betweentwo question marks (??):

Now that the search index has been created, you can use theSEARCHfunction to search thetable using the same analyzer configurations specified in the search index. Notethat if the analyzer configurations in theSEARCH function don't match thoseof the search index, the search index won't be used. Use the following query:

SELECTSEARCH(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 followingtable demonstrates various results based on different search text and differentvalues ofsearch_query:

PATTERN_ANALYZER for IPv4 search with stop words

The following example configures thePATTERN_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:

CREATETABLEdataset.data_table(text_dataSTRING);

To create a search index thepattern option and a list of stop words, create aJSON-formatted string in theanalyzer_options option of theCREATE SEARCHINDEX DDLstatement.For a complete list of options available in when creating a search index withthePATTERN_ANALYZER, seePATTERN_ANALYZER.For this example, our stop words are the localhost address,127.0.0.1.

CREATESEARCHINDEXmy_indexONdataset.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 withanalyzer_options, include threeleading\ 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 oftext_data

Now that the search index has been created, you can use theSEARCHfunction to search thetable based on the tokenization specified inanalyzer_options. Use thefollowing query:

SELECTSEARCH(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 followingtable demonstrates various results based on different search text and differentvalues ofsearch_query:

What's next

• For an overview of search index use cases, pricing, required permissions, andlimitations, see theIntroduction to search inBigQuery.
• For information about efficient searching of indexed columns, seeSearch with an index.