I want to quickly identify the table and column that contains a certain keyword within a database stored in Vertica. Vertica provides Text Index feature for full-text search. However, a text index can only be built for a certain schema/relation, not the whole database.
Does anyone know whether there is an easy way to build an inverted index for a whole database in Vertica?
You cannot create a single text index that goes across multiple objects. Each index is an index only for a single column on a table.
You could perhaps generate the creates that you want by looking for columns in the column table (although it would be unusual to do them all honestly). This assumes every table has a PK named id though. I'm just trying to simplify.
select 'create text index ' || table_schema || '.t_' || table_name || '_' || column_name
|| '_index on ' || table_schema || '.' || table_name
|| '(id,' || column_name || ');'
from columns
where data_type ilike '%char';
Then you could create a view that selects from all the text indexes. For the record, though, I have no idea how well this will perform. One would think the optimizer would do what it needs to, but hard to say for sure. This could be generated similarly to the above query, and would look like this when done (note if you are partitions, you'll need to add a column for that).
create view v_all_text_indexes
as
select 'schema1' schema_name, 'table1' table_name, 'column1' column_name, word, doc_id
from schema1.t_table1_column1_index
union all
select 'schema1' schema_name, 'table1' table_name, 'column2' column_name, word, doc_id
from schema1.t_table1_column2_index
.
.
.
select 'schema1' schema_name, 'tablen' table_name, 'columnn' column_name, word, doc_id
from schema1.t_tablen_columnn_index
I have no idea how well this will perform for you, though. YMMV.