Index Documents into Vertica
OpenText Connector can index documents into Vertica, so that you can run queries on structured fields (document metadata).
Depending on the metadata contained in your documents, you could investigate the average age of documents in a repository. You might want to answer questions such as: How much time has passed since the documents were last updated? How many files are regularly updated? Does this represent a small proportion of the total number of documents? Who are the most active users?
TIP: In most cases, OpenText recommends sending documents to a Connector Framework Server (CFS). CFS extracts metadata and content from any files that the connector has retrieved, and can manipulate and enrich documents before they are indexed. CFS also has the capability to insert documents into more than one index, for example IDOL Server and a Vertica database. For information about sending documents to CFS, see Send Data to Connector Framework Server
- OpenText Connector supports indexing into Vertica 7.1 and later.
- You must install the appropriate Vertica ODBC drivers (version 7.1 or later) on the machine that hosts OpenText Connector. If you want to use an ODBC Data Source Name (DSN) in your connection string, you will also need to create the DSN. For more information about installing Vertica ODBC drivers and creating the DSN, refer to the Vertica documentation.
New, Updated and Deleted Documents
When documents are indexed into Vertica, OpenText Connector adds a timestamp that contains the time when the document was indexed. The field is named
VERTICA_INDEXER_TIMESTAMP and the timestamp is in the format
When a document in a data repository is modified, OpenText Connector adds a new record to the database with a new timestamp. All of the fields are populated with the latest data. The record describing the older version of the document is not deleted. You can create a projection to make sure your queries only return the latest record for a document.
When OpenText Connector detects that a document has been deleted from a repository, the connector inserts a new record into the database. The record contains only the
DREREFERENCE and the field
VERTICA_INDEXER_DELETED set to
Fields, Sub-Fields, and Field Attributes
Documents that are created by connectors can have multiple levels of fields, and field attributes. A database table has a flat structure, so this information is indexed into Vertica as follows:
- Document fields become columns in the flex table. An IDOL document field and the corresponding database column have the same name.
- Sub-fields become columns in the flex table. A document field named
my_fieldwith a sub-field named
subfieldresults in two columns,
- Field attributes become columns in the flex table. A document field named
my_field, with an attribute named
my_attributeresults in two columns,
my_fieldholding the field value and
my_field.my_attributeholding the attribute value.