A.28 Rebuilding Indices in OpenSearch

Indices rebuild is required when there is mismatch between the records in the database and OpenSearch indexes. The following steps are also applicable even if there is a mismatch between any of the indexes.

To rebuild indices in the OpenSearch, follow these steps:
  1. Execute the following curl command to delete the index:
    curl -XDELETE http://hostname:port/load-to-open-search/idx/deleteIndex/
    <Index name>
    For example,
    curl -XDELETE http://testserver.oracle.com:7053/load-to-open-search/idx/
    deleteIndex/stg_party_812

    Note:

    The curl command should be executed in the Compliance Studio server.
  2. Execute the following to Load data for __prev index for the runskey LESS THAN the last successful ER batch runskey.
    1. URL: http://<hostname>:<port>/load-to-open-search/idx/createIndex

      For example: http://testserver.com:7053/load-to-open-search/idx/ createIndex

    2. Request Body: The JSON request body can be obtained using below query:
      SELECT V_IDX_JSON FROM FCC_IDX_M_JSON_MAP WHERE
      V_PIPELINE_ID='<PIPELINE_ID>';

      Note:

      In the below request, ##LATEST_RUN_SKEY_OF_LAST_SUCCESSFULL_ERJOB## is the latest runskey for which all 4 ER Jobs were executed successfully.
      1. Make the following changes in the json keys:
        "loadType": "DeltaLoad"
        "tableName" :"FCC_ER_FULL"
        "filterCondition": "N_RUN_SKEY <
        ##LATEST_RUN_SKEY_OF_LAST_SUCCESSFULL_ERJOB##"
        For example,
        "loadType": "DeltaLoad"
        "tableName" :"FCC_ER_FULL"
        "filterCondition": "N_RUN_SKEY <196"

        Note:

        Here, 196 is the latest runskey for which all 4 ER Jobs were executed successfully.
      2. Ensure that the "deletedProfilesTableName" key and its value are not in the request body.
      3. Provide the ER schema alias name as "schemaName".

        For example, "schemaName": "ER_SCHEMA_ALIAS"

      4. Provide the wallet path in the "walletFilePath" key.

        For example, "walletFilePath": "/scratch/test/testpath/compStudio_ 31010949/OFS_- COMPLIANCE_STUDIO/wallet"

      5. Provide tnsnames.ora file path in the "walletFilePath" key.

        For example, "tnsOraFilePath": "/scratch/test/testpath/compStudio_31010949/OFS_- COMPLIANCE_STUDIO/wallet"

        The sample Request body for __prev index is as follows:
        {
        "schemaName": "ER_SCHEMA_ALIAS",
        "walletFilePath":"/scratch/test/testPath/OFS_COMPLIANCE_STUDIO/
        wallet",
        "tnsOraFilePath":"/scratch/test/testPath/OFS_COMPLIANCE_
        STUDIO/wallet",
        "tableName": "FCC_ER_FULL",
        "filterCondition": "N_RUN_SKEY < 196",
        "indexName": "stg_party_812",
        "indexAlias": "csa_812_alias",
        "indexLogicalName": "csa_stg_party_812",
        "indexBusinessName": "csa_stg_party_812",
        "indexKeyAttribute": "original_id",
        "deleteProfilesIdxKeyAttribute":"v_party_id",
        "loadType": "DeltaLoad",
        "shards": 1,
        "replicas": 3,
        "attributes": [
        {
        "name": "address",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "address",
        "fields": []
        },
        {
        "name": "business_domain",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "Organization",
        "fields": []
        },
        {
        "name": "city",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "address",
        "fields": []
        },
        {
        "name": "country",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "address",
        "fields": []
        },
        {
        "name": "given_name",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "namestop",
        "fields": []
        },
        {
        "name": "middle_name",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "namestop",
        "fields": []
        },
        {
        "name": "family_name",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "namestop",
        "fields": []
        },
        {
        "name": "concat_name",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "namestop",
        "fields": []
        },
        {
        "name": "alias",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "namestop",
        "fields": []
        },
        {
        "name": "mdm_id",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "pipe_delimiter",
        "fields": []
        },
        {
        "name": "state",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "address",
        "fields": []
        }
        ],
        "customAnalyzer": [],
        "customFilter": [],
        "customCharFilter": [],
        "customTokenizer": [],
        "others": [
        "original_id",
        "orgname",
        "dob",
        "source_name",
        "start_date",
        "jurisdiction",
        "industry",
        "naics_code",
        "tax_id",
        "doc_id",
        "email",
        "phone",
        "postal_code"
        ],
        "replaceCharFields": [
        {
        "name": "address",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "city",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "country",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "state",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "given_name",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "middle_name",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "family_name",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "concat_name",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "alias",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        }
        ],
        "replaceEmptyFields": [],
        "translateFields": ["middle_name","family_name","concat_
        name","alias","given_name", "address", "city", "country",
        "state"]
        }
        CURL COMMAND:
        curl -XPOST http://hostname:port/load-to-open-search/idx/
        createIndex -H 'Content-Type: application/json' -d'<request_body>'
        For example,
        curl -XPOST http://testserver:7053/load-to-open-search/idx/
        createIndex -H 'Content-Type: application/json' -d'<request_body>'
  3. Execute the following to load data for __delta index for the runskey EQUAL TO last successful ER batch runskey.
    1. URL: http://<hostname>:<port>/load-to-open-search/idx/createIndex

      For example: http://testserver.com:7053/load-to-open-search/idx/ createIndex

    2. Request Body: The JSON request body can be obtained using below query:
      SELECT V_IDX_JSON FROM FCC_IDX_M_JSON_MAP WHERE
      V_PIPELINE_ID='<PIPELINE_ID>';

      Note:

      In the below request, ##LATEST_RUN_SKEY_OF_LAST_SUCCESSFULL_ERJOB## is the latest runskey for which all 4 ER Jobs were executed successfully.
      1. Make the following changes in the json keys:
        "loadType": "DeltaLoad"
        "tableName" :"FCC_ER_FULL"
        "filterCondition": "N_RUN_SKEY =
        ##LATEST_RUN_SKEY_OF_LAST_SUCCESSFULL_ERJOB##>"
        For example,
        "loadType": "DeltaLoad"
        "tableName" :"FCC_ER_FULL"
        "filterCondition": "N_RUN_SKEY = 196"

        Note:

        Here, 196 is the latest runskey for which all 4 ER Jobs were executed successfully.
      2. Ensure that the "deletedProfilesTableName" key and its value are not in the request body.
      3. Provide the ER schema alias name as "schemaName".

        For example, "schemaName": "ER_SCHEMA_ALIAS"

      4. Provide the wallet path in the "walletFilePath" key.

        For example, "walletFilePath": "/scratch/test/testpath/compStudio_ 31010949/OFS_- COMPLIANCE_STUDIO/wallet"

      5. Provide tnsnames.ora file path in the "walletFilePath" key.

        For example, "tnsOraFilePath": "/scratch/test/testpath/compStudio_31010949/OFS_- COMPLIANCE_STUDIO/wallet"

        The sample Request body for __delta index is as follows:
        {
        "schemaName": "ER_SCHEMA_ALIAS",
        "walletFilePath":"/scratch/test/testPath/OFS_COMPLIANCE_STUDIO/
        wallet",
        "tnsOraFilePath":"/scratch/test/testPath/OFS_COMPLIANCE_
        STUDIO/wallet",
        "tableName": "FCC_ER_FULL",
        "filterCondition": "N_RUN_SKEY = 196",
        "indexName": "stg_party_812",
        "indexAlias": "csa_812_alias",
        "indexLogicalName": "csa_stg_party_812",
        "indexBusinessName": "csa_stg_party_812",
        "indexKeyAttribute": "original_id",
        "deleteProfilesIdxKeyAttribute":"v_party_id",
        "loadType": "DeltaLoad",
        "shards": 1,
        "replicas": 3,
        "attributes": [
        {
        "name": "address",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "address",
        "fields": []
        },
        {
        "name": "business_domain",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "Organization",
        "fields": []
        },
        {
        "name": "city",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "address",
        "fields": []
        },
        {
        "name": "country",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "address",
        "fields": []
        },
        {
        "name": "given_name",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "namestop",
        "fields": []
        },
        {
        "name": "middle_name",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "namestop",
        "fields": []
        },
        {
        "name": "family_name",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "namestop",
        "fields": []
        },
        {
        "name": "concat_name",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "namestop",
        "fields": []
        },
        {
        "name": "alias",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "namestop",
        "fields": []
        },
        {
        "name": "mdm_id",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "pipe_delimiter",
        "fields": []
        },
        {
        "name": "state",
        "type": "text",
        "similarity": "boolean",
        "analyzerType": "address",
        "fields": []
        }
        ],
        "customAnalyzer": [],
        "customFilter": [],
        "customCharFilter": [],
        "customTokenizer": [],
        "others": [
        "original_id",
        "orgname",
        "dob",
        "source_name",
        "start_date",
        "jurisdiction",
        "industry",
        "naics_code",
        "tax_id",
        "doc_id",
        "email",
        "phone",
        "postal_code"
        ],
        "replaceCharFields": [
        {
        "name": "address",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "city",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "country",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "state",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "given_name",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "middle_name",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "family_name",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "concat_name",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        },
        {
        "name": "alias",
        "charArray": [";", "~"],
        "replaceWith": [",", ";"]
        }
        ],
        "replaceEmptyFields": [],
        "translateFields": ["middle_name","family_name","concat_
        name","alias","given_name", "address", "city", "country",
        "state"]
        }
        CURL COMMAND:
        curl -XPOST http://hostname:port/load-to-open-search/idx/
        createIndex -H 'Content-Type: application/json' -d'<request_body>'

        For example,

        curl -XPOST http://testserver:7053/load-to-open-search/idx/createIndex
        -H 'Content-Type: application/json' -d'<request_body>'