Best Of

Unfortunately, there is currently no mechanism to retrieve the Kafka message keys unless they're part of the message payload e.g { {"kafka_key":"i-01234567 ","loginAt": "2020-08-28T23:59:59Z"}

Check the V_CATALOG.PROFILES and V_CATALOG.PROFILE_PARAMETERS system tables.

Doc references:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/PROFILES.htm
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/PROFILE_PARAMETERS.htm

Maybe something like this?

SELECT DISTINCT 
       l.lib_name 
     , l.description
     , f.schema_name function_schema
     , f.function_name
     , f.procedure_type function_type
  FROM user_functions f
  JOIN user_libraries l ON f.function_definition ILIKE '%' || lib_name || '%'
  ORDER BY 1, 2, 3, 4;

LISTAGG doesn't need special permissions. PUBLIC has execution privilege. Try running this query to verify the functionality is installed
vsql -f /opt/vertica/packages/VFunctions/ddl/isinstalled.sql
If it's not, you can re-install it with admintools.

This is a known issue with the license auditor in 9.3.1. It was fixed in Vertica 9.3.1-4.

Hi. What Vertica version are you using? Did you upgrade recently?

@karthik
The message LGE is behind: 214191296 instead of 214199608, indicating there are some projections holding epoch 214191296 and
some projections holding epoch 214199608.
[1]
Run the below query to Identify impacted projections
SELECT e.node_name, t.table_schema, t.table_name, e.projection_schema, e.projection_name, checkpoint_epoch FROM projection_checkpoint_epochs e, projections p, tables t WHERE e.projection_id = p.projection_id and p.anchor_table_id = t.table_id and is_behind_ahm ;
[2]
Once you find the projections and decided to bring the DB up from higher epoch you need to run abortrecovery on those projection.
You can use the below query and that will create the abort command.
select 'select do_tm_task('||'''abortrecovery'''||','||''''||projection_schema||'.'|| projection_name ||''');' as command from (select distinct projection_schema, projection_name from v_catalog.projection_checkpoint_epochs
where checkpoint_epoch = ) as v1;

https://softwaresupport.softwaregrp.com/

You can also use admintools to reinstall the any package:

Example:

[dbadmin@localhost ~]$ admintools -t list_packages
             Package Description
                 AWS Amazon Web Services Package
        ComplexTypes Functions for Complex Types
     MachineLearning In-dataBase Machine Learning package.
       ParquetExport Functions that support Parquet Export
      SparkConnector Spark connector for reading from and writing into Vertica
       TFIntegration Using Tensorflow models in Vertica
          VFunctions Vertica functions
         approximate Probabilistic/Approximate Aggregation Functions
           flextable Flexible Tables Data Load and Query
                hcat Java/HCatalog data load and query
               kafka Kafka streaming load and export
           logsearch Common Advanced text search functions
               place Geospatial Data Query
            txtindex Common string-manipulation functions
       voltagesecure Voltage SecureData functions

[dbadmin@localhost ~]$ admintools -t install_package -d verticademos -P approximate --force-reinstall
Installing package approximate...
...Success!

Documenation:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/InstallationGuide/Upgrade/PostUpgrade/UpgradingandReinstallingPackages.htm

Not exactly sure what you are trying to do, but you could just use the -c option to pass in a simple command to run, then vsql will exit.

I am also using the -o option to output to a file (i.e. a log file).

[dbadmin@localhost ~]$ cat l.sh
VSQLPATH=/opt/vertica/bin/vsql
TECHNOLOGY=vertica
HOST=*******
PORT=5433
DB_NAME=verticademos
USERNAME=dbadmin
PWD=*****
log_file=/home/dbadmin/log.txt
if [ "${TECHNOLOGY}" = "vertica" ]
then
export PASSWORD="${PWD}"
${VSQLPATH} --host="${HOST}" --port="${PORT}" --username="${USERNAME}" --dbname="${DB_NAME}" -w "${PWD}" -c "SELECT version();" -o ${log_file}
fi

[dbadmin@localhost ~]$ ./l.sh

[dbadmin@localhost ~]$ cat log.txt
               version
-------------------------------------
 Vertica Analytic Database v10.0.1-2
(1 row)