This page documents AlloyDB Omni version 18.3.0 using the container orchestrator deployment option. Choose a different deployment option.

Access OpenSearch data from AlloyDB Omni

Select a documentation version:

You can access and search data stored in OpenSearch using the external search integration in AlloyDB Omni. This integration lets you perform federated search across existing OpenSearch indexes and your relational tables without copying or moving data.

Before you begin

Before you begin, complete the following:

Install AlloyDB Omni using the container orchestrator.
Deploy and configure OpenSearch with an accessible public endpoint.
Enable the security plugin on your OpenSearch cluster and create a read-only user in the internal users database. AlloyDB Omni uses these credentials to access your OpenSearch cluster.

Store OpenSearch credentials in Secret Manager

AlloyDB Omni stores and reads your OpenSearch credentials from Secret Manager. For Basic HTTP authentication, the value of the secret in Secret Manager must be a string formatted as username:password. For more information on how to use Secret Manager, see Create and access a secret using Secret Manager.

Make sure that the service account that AlloyDB Omni uses has the secretmanager.secretAccessor permission to read the secret from Secret Manager. For more information, see Manage access to secrets.

Enable and configure the `external_search_fdw` extension

To start your integration with OpenSearch, configure access to your OpenSearch cluster through a foreign data server.

Connect to your database and enable the extension:
```
CREATE EXTENSION external_search_fdw;
```
Create a foreign server:
```
CREATE SERVER OPENSEARCH_SERVER_NAME
FOREIGN DATA WRAPPER external_search_fdw
OPTIONS (
  server 'OPENSEARCH_SERVER_HOST_PORT',
  search_provider 'opensearch',
  auth_mode 'secret_manager',
  auth_method 'AUTH_METHOD',
  secret_path 'SECRET_PATH'
);
```
Replace the following variables:
- OPENSEARCH_SERVER_NAME: name for your foreign data server. For example, opensearch.
- OPENSEARCH_SERVER_HOST_PORT: public-facing URL for your OpenSearch cluster. For example, https://search-my-domain.us-east-1.es.amazonaws.com:443.
- AUTH_METHOD: type of authentication to use. For example, Basic.
- SECRET_PATH: Secret Manager path to your OpenSearch authentication credentials. For example, projects/PROJECT_ID/secrets/opensearch-credentials/versions/1.

Define the user mapping for the OpenSearch server:

CREATE USER MAPPING FOR CURRENT_USER
SERVER OPENSEARCH_SERVER_NAME;

Map the schema of your OpenSearch index to a PostgreSQL foreign table:
```
CREATE FOREIGN TABLE OPENSEARCH_FD_TABLE(
    metadata external_search_fdw_schema.OpaqueMetadata,
    OPENSEARCH_FIELDS)
       SERVER OPENSEARCH_SERVER_NAME
       OPTIONS(
            remote_table_name 'OPENSEARCH_INDEX_NAME'
       );
```
Replace the following variables:
- OPENSEARCH_FD_TABLE: the name of the foreign data table that represents your OpenSearch table. For example, my-fd-opensearch-table.
- OPENSEARCH_FIELDS: a comma-separated list where each entry follows the format opensearch_field_name PG_DATA_TYPE. For a list of supported OpenSearch data types and their corresponding PostgreSQL types, see Supported data types.
- OPENSEARCH_INDEX_NAME: the name of your OpenSearch index. For example, my-opensearch-index.

Query your OpenSearch data

AlloyDB Omni converts SQL queries to OpenSearch REST API queries.

Standard SQL queries

You can use standard SQL with Lucene syntax for the search expression.

SELECT id, body
FROM OPENSEARCH_FD_TABLE
WHERE FILTER
ORDER BY metadata <@> 'QUERY';

Replace the following variables:

OPENSEARCH_FD_TABLE: name of the foreign data table that represents your OpenSearch table. For example, my-fd-opensearch-table.
FILTER: (Optional) filter to apply to your OpenSearch query. For example, a = 10 AND b < 105.
QUERY: query to send to OpenSearch. For example, body:database.

Query DSL

For advanced use cases, use OpenSearch JSON-style Query Domain Specific Language (DSL).

SELECT id, title
FROM OPENSEARCH_FD_TABLE
ORDER BY metadata <@> $${
  "query": {
    "bool": {
      "must": { "match": { "title": "opensearch" } },
      "filter": { "term": { "category": "software" } }
    }
  },
  "sort": [
    { "price": { "order": "desc" } }
  ]
}$$
LIMIT 1;

Replace OPENSEARCH_FD_TABLE with the name of the foreign data table that represents your OpenSearch table. For example, my-fd-opensearch-table.

Hybrid searches

To perform a hybrid search on your OpenSearch data, join OpenSearch token search results with AlloyDB Omni vector search results.

SELECT *
FROM ai.hybrid_search(
  ARRAY[
    '{"limit": LIMIT,
      "weight": WEIGHT,
      "table_name": "OPENSEARCH_FD_TABLE",
      "key_column": "id",
      "query_text_input": "QUERY"}'::jsonb
  ])
ORDER BY score DESC;