Vertex AI Search Agent for Wordpress Intranet Portal

[sijohnmathew]

We are bringing a consolidated search experience at our customer place and successfully bought in Drive, Calendar & BigQuery data. The challenge is now that they maintain a very old Intranet portal based on Wordpress.

I know there is no built in connectors, so my thought was to get the Wordpress MySQL data streamed to a Cloud SQL or BigQuery and feed it as another Data Source to the same Search Agent. But the main challenge is that, how do I point back the search result back to the original source (Wordpress portal).

Does anyone have any suggestions or pointers in this direction ?

Sijohn

[dawnberdan]

Hi @sijohnmathew,

Welcome to Google Cloud Community!

Wordpress is a great tool for making websites, but its data is stored in a different way than modern databases. To include Wordpress data in a single search, you need to index it correctly and make sure search results link to the right pages on the Wordpress site.

Here's a guide for potential solutions, along with a reference link.

1. Data Extraction & Transformation:

• Accessing the WordPress Database: To begin, you need to connect to the WordPress MySQL database. This involves setting up a connection to the database server. You can refer to WordPress hosting provider's guidelines or check the WordPress database documentation.
• Data Extraction: Utilize various tools and methods to extract data from the WordPress database:

1. SQL Queries: Write SQL queries to retrieve data from WordPress tables such as wp_posts, wp_terms, wp_users, etc.
2. Database Tools: Employ tools like MySQL Workbench, DBeaver, or Python libraries like mysql.connector for data extraction.

• Data Transformation: Prepare your WordPress data for loading into Cloud SQL or BigQuery by performing the following tasks.

2. Data Loading: 

• Cloud SQL: You can load your extracted and transformed data directly into a Cloud SQL instance, which facilitates quicker access during the indexing process.
• BigQuery: BigQuery is a fully managed data warehouse that supports various methods for loading data, including the BigQuery load CLI command and APIs.

3. Search Indexing: When using a search agent, you'll need to configure it to index data from Cloud SQL or BigQuery. This usually involves defining the schema and setting up the appropriate indexing options.

4. Linking Back to WordPress:

• Unique Identifiers: It’s crucial to include a unique identifier for each piece of content when extracting data from WordPress. This could be the WordPress post ID, a custom URL slug, or another unique attribute.
• Search Results Mapping: Ensure that your search results display this unique identifier. When users click on a search result, use this identifier to:

1. Redirect: Implement server-side logic, such as URL rewrite rules or backend scripts, to direct users to the corresponding page on the WordPress portal based on the unique identifier.
2. Dynamic Page Generation: For greater control over content presentation, dynamically generate the WordPress page content using a backend script or API based on the unique identifier, and display it within your unified search experience.

Additional Tips: To ensure your search results remain up-to-date, consider implementing incremental updates to your database, which involves updating only the data that has changed since the last update. Additionally, optimizing indexing and search performance is essential; focus on refining your data structures and indexing strategies, and consider employing caching mechanisms to enhance response times.

I hope the above information is helpful.