SEARCH_INDEX_KEY

Target database: Google BigQuery, Snowflake

Used to define the columns for the Search optimization (Snowflake) and Search Index (Google BigQuery). Requires the STORAGE.SEARCH_OPTIMIZATION to be set true.

Target DMBS

Entity types

Feature

Snowflake

TABLE

Defines the columns being utilised in the search optimization service.

By default columns are handled as parameters for EQUALITY search method.

Index options - part of the SEARCH_INDEX_KEY can be used as a template in where one can use following SQL snippets: <search_index_key_attributes> for comma separated list of search index key attributes

<search_index_key_attribute_1> for specific search index key attribute based on their position.

Google BigQuery

TABLE

Defines the columns being utilised in the search index column definition part.

By default columns are listed in the column definition part as they are.

Index options - part of the SEARCH_INDEX_KEY can be used as a template in where one can use following SQL snippets: <search_index_key_attributes> for comma separated list of search index key attributes

<search_index_key_attribute_1> for specific search index key attribute based on their position.

See also:

Usage

Choose the attributes wanted to be used in the Search Optimization Service (Snowflake) or Search Index (Google BigQuery). For further customisation use the index options field of the key.

Notes

Check Export SQL/Entity SQL after defining a SEARCH_INDEX_KEY to see generated DDL.

Examples

Snowflake Search Optimization Service with specified columns

Key definition:

DEFINITION

KEY NAME: SIDX_F_TRIP
KEY TYPE: SEARCH_INDEX_KEY
ATTRIBUTE NAME: category

Physical option definition:

DEFINITION

Physical option name: STORAGE.SEARCH_OPTIMIZATION
Physical option value: true

DDL generated by Agile Data Engine:

SQL

ALTER TABLE pub.F_TRIP ADD SEARCH OPTIMIZATION ON EQUALITY(category);

Snowflake Search Optimization Service with specified columns and index options

Key definition:

DEFINITION

KEY NAME: SIDX_F_TRIP
KEY TYPE: SEARCH_INDEX_KEY
ATTRIBUTE NAMES: category, location
INDEX OPTIONS: SUBSTRING(<search_index_key_attribute_1>),GEO(<search_index_key_attribute_2>)

Physical option definition:

DEFINITION

Physical option name: STORAGE.SEARCH_OPTIMIZATION
Physical option value: true

DDL generated by Agile Data Engine:

SQL

ALTER TABLE pub.F_TRIP ADD SEARCH OPTIMIZATION ON SUBSTRING(category),GEO(location);

Google BigQuery search index with specified columns

Key definition:

DEFINITION

KEY NAME: SIDX_F_TRIP
KEY TYPE: SEARCH_INDEX_KEY
ATTRIBUTE NAME: category

Physical option definition:

DEFINITION

Physical option name: STORAGE.SEARCH_OPTIMIZATION
Physical option value: true

DDL generated by Agile Data Engine:

SQL

CREATE SEARCH INDEX SIDX_F_TRIP ON publish.F_TRIP (category);

Google BigQuery search index with specified columns and index options

Key definition:

DEFINITION

KEY NAME: SIDX_F_TRIP
KEY TYPE: SEARCH_INDEX_KEY
ATTRIBUTE NAME: category, event_time
INDEX OPTIONS: <search_index_key_attribute_1> OPTIONS(index_granularity = 'COLUMN'),
  <search_index_key_attribute_2>

Physical option definition:

DEFINITION

Physical option name: STORAGE.SEARCH_OPTIMIZATION
Physical option value: true

DDL generated by Agile Data Engine:

SQL

CREATE SEARCH INDEX SIDX_F_TRIP ON publish.F_TRIP (category OPTIONS(index_granularity = 'COLUMN'),event_time);

Google BigQuery search index with specified columns, index options and datatypes

Key definition:

DEFINITION

KEY NAME: SIDX_F_TRIP
KEY TYPE: SEARCH_INDEX_KEY
ATTRIBUTE NAME: category, event_time
INDEX OPTIONS: <search_index_key_attribute_1> OPTIONS(index_granularity = 'COLUMN'),
  <search_index_key_attribute_2>

Physical option definitions:

DEFINITION

Physical option name: STORAGE.SEARCH_OPTIMIZATION
Physical option value: true
Physical option name: STORAGE.SEARCH_OPTIMIZATION_OPTIONS
Physical option value: data_types = ['STRING','INT64']

DDL generated by Agile Data Engine:

SQL

CREATE SEARCH INDEX SIDX_F_TRIP ON 
  publish.F_TRIP(category OPTIONS(index_granularity = 'COLUMN'),event_time)
  OPTIONS(data_types = ['STRING','INT64']);