Rebuilding the search index schema in SAS® Viya® 3.5


This SAS KB article documents the steps for rebuilding the search index schema in SAS Viya 3.5. When rebuilding the schema, you should choose a time of the day when system use in your environment is low. The reason to do so is because the SAS Search service is used by SAS® Cloud Analytic Services (CAS) in calls from batch code as well as from Model Studio applications, SAS® Visual Analytics Report Viewer, and so on.

Follow the steps below as the sas installer account with root privileges to rebuild the search index schema:

    1. Stop your SAS Search service(s) by executing the following commands:
      cd /etc/init.d 

      systemctl stop sas-viya-search-default
    2. Retrieve the current password for your dbmsowner service account by executing the following commands:
      source /opt/sas/viya/config/consul.conf

      export CONSUL_HTTP_TOKEN=$(sudo cat /opt/sas/viya/config/etc/SASSecurityCertificateFramework/tokens/consul/default/client.token)

      /opt/sas/viya/home/bin/sas-bootstrap-config kv read config/application/sas/database/postgres/password 

      Write down the password that is returned by the last command above.

 

3. Log on to your SharedServices PostgreSQL database by executing the commands below:

cd /opt/sas/viya/home/postgresql11/bin/

./psql -h localhost -p 5431 -U dbmsowner -d SharedServices

When you are prompted for a password, specify the password that you retrieved in step 2. If the authentication is successful, you should see a SharedServices=# prompt similar to the following:

psql (11.5)

SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, bits: 256, compression: off)

Type "help" for help.

SharedServices=#

4. Execute the following command within the prompt to list all your schemas:

SELECT schema_name FROM information_schema.schemata;

You should see a searchindex schema toward the bottom if you press the spacebar on your keyboard until you reach the end of the list. Make sure that the schema exists in the list, as shown in this example:

. . .more schemas listed above this line. . .

text_gateway

reportalerts

reportimageservice

searchindex

(109 rows)

5. Rename the searchindex schema to searchindex_old by executing the command below:

ALTER SCHEMA searchindex RENAME TO searchindex_old;

You can verify whether the schema is renamed by re-executing the SELECT command from step 4. You should see the new schema name (searchindex_old) in the list.

 

6. Exit out of the prompt by executing \q command.

 

7. Start your SAS Search service(s) back up by executing the commands below:

cd /etc/init.d

systemctl start sas-viya-search-default

 

8. Wait a few minutes to allow your SAS Search Service(s) to fully initialize. To confirm that the initialization is fully complete, look at the service log(s) located in the /opt/sas/viya/config/var/log/search/default directory and wait until you see the messages that are similar to the following:

2021-05-07 16:19:07.603  INFO 7232 --- [
ask-8:searchIndex] c.s.n.bootstrap.SubscriptionsPatcher     : service [404cddab710bb838] Bootstrapping patches is complete
2021-05-07 16:19:24.127  INFO 7232 --- [0-auto-2-exec-4] o.a.c.c.C.[.[localhost].[/searchIndex]   : service Initializing Spring DispatcherServlet 'dispatcherServlet'
2021-05-07 16:19:24.127  INFO 7232 --- [0-auto-2-exec-4] o.s.web.servlet.DispatcherServlet        : service Initializing Servlet 'dispatcherServlet'
2021-05-07 16:19:24.190  INFO 7232 --- [0-auto-2-exec-4] o.s.web.servlet.DispatcherServlet        : service Completed initialization in 63 ms

Now, you should see a new searchindex schema created in the database. If you follow steps 3 and 4 again, you will see the new schema listed, in addition to the old "searchindex_old" schema, as shown below:

. . .more schemas listed above this line. . .

text_gateway

reportalerts

reportimageservice

searchindex_old

searchindex

(110 rows)

It takes at least an hour for the new searchindex schema to be fully populated with new index. Make sure that you wait for an hour or more before using the search functionality again.

Optional: Once the new searchindex schema is fully populated, you should be able to search your reports within SAS Visual Analytics again. After you verify that the search works as expected, you can delete the  searchindex_old schema from your database, especially if you have a lot of content that is indexed and is searchable. The schema will be too large in such cases. To delete the old schema, execute the command below in the SharedServices=# prompt from step 3 above:

DROP SCHEMA searchindex_old CASCADE;