This page documents AlloyDB Omni version 17.9.0 using the containers deployment option. Choose a different deployment option.

Access Apache Solr data from AlloyDB Omni

Select a documentation version:

You can access and search data stored in Apache Solr using the external search integration in AlloyDB Omni. This integration lets you offload intensive full-text search, complex query parsing, and advanced faceting workloads from your operational database to Solr, improving query latency and overall system throughput.

Before you begin

Before you begin, complete the following:

Store Solr credentials in Secret Manager

AlloyDB Omni stores and reads your Solr credentials from Secret Manager. For Basic HTTP authentication, the value of the secret in Secret Manager must be a base64-encoded string in the format 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 Solr, configure access to your Solr 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 SOLR_SERVER_NAME
FOREIGN DATA WRAPPER external_search_fdw
OPTIONS (
  server 'SOLR_SERVER_HOST_PORT',
  search_provider 'solr',
  auth_mode 'secret_manager',
  auth_method 'AUTH_METHOD',
  secret_path 'SECRET_PATH'
);
```
Replace the following variables:
- SOLR_SERVER_NAME: name for your foreign data server. For example, solr.
- SOLR_SERVER_HOST_PORT: Public-facing URL for your Solr cluster. For example, https://node1.solr.test.com:8983.
- AUTH_METHOD: type of authentication to use. For example, Basic.
- SECRET_PATH: Secret Manager path to your Solr authentication credentials. For example, projects/PROJECT_ID/secrets/apikey/versions/1.

Define the user mapping for the Solr server:

CREATE USER MAPPING FOR CURRENT_USER
SERVER SOLR_SERVER_NAME;

Map the schema of your Solr collection to a PostgreSQL foreign table.
```
CREATE FOREIGN TABLE SOLR_FD_TABLE(
    metadata external_search_fdw_schema.OpaqueMetadata,
    SOLR_FIELDS)
       SERVER SOLR_SERVER_NAME
       OPTIONS(
            remote_table_name 'SOLR_COLLECTION_NAME',
            unique_key_sort_suffix 'UNIQUE_KEY_SORT_SUFFIX'
);
```
Replace the following variables:
- SOLR_FD_TABLE: the name of the foreign data table that represents your Solr table. For example, my-fd-solr-table.
- SOLR_FIELDS: a comma-separated list where each entry follows the format solr_field_name PG_DATA_TYPE. For a list of supported Solr data types and their corresponding PostgreSQL types, see Supported data types.
- UNIQUE_KEY_SORT_SUFFIX: (Optional) the unique key field in Solr, or a comma-separated list of multiple fields with their sort orders&mdashfor example, myid0 ASC, myid1 DESC, used for pagination. Defaults to id.
- PG_DATA_TYPE: the PostgreSQL type that you want to map it to. For example:
  - TEXT for string data.
  - INTEGER for numeric data.
- SOLR_COLLECTION_NAME: the name of your Solr collection. For example, my-solr-collection.

Query your Solr data

AlloyDB Omni converts SQL queries to Solr REST API queries.

Standard SQL queries

For the search expression, you can use standard SQL with Lucene syntax.

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

Replace the following variables:

SOLR_FD_TABLE: name of the foreign data table that represents your Solr table. For example, my-fd-solr-table.
FILTER: (Optional) filter to apply to your Solr query. For example, AND qubits < 105.
QUERY: query to send to Solr. For example, body:database.

Query DSL

For advanced use cases — such as implementing nested queries, customized sorting, or complex filtering — use Solr JSON-style Query DSL.

SELECT id, title
FROM SOLR_FD_TABLE
ORDER BY metadata <@> $${
  "query": "title:solr",
  "filter": ["category:software", "inStock:true"],
  "sort": "price desc"
}$$
LIMIT 1;

Replace SOLR_FD_TABLE with the name of the foreign data table that represents your Solr table. For example, my-fd-solr-table.

Hybrid searches

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

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