Rocket® Vertica

I am working on the migration to Vertica DB. I want to migrate the spark job to use vertical DB data. But we are using spark version 2.3.0 & scala 2.11.8. So I am using vertica-spark2.1_scala2.11.jar along with vertica-jdbc-23.4.0-0.jar & vertica-jdbc-12.0.4-0.jar. But I am getting below exception: Exception in thread "main" java.lang.ClassNotFoundException: Failed to find data source: com.vertica.spark.datasource.VerticaSource. Please find packages at http://spark.apache.org/third-party-projects.html at org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSource(DataSource.scala:657) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:194) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:167) at com.turn.platform.verticaconnectorpoc.BasicReadWriteExamples$.main(VerticaToHDFSAndProcess.scala:64) at com.turn.platform.verticaconnectorpoc.BasicReadWriteExamples.main(VerticaToHDFSAndProcess.scala) at sun.reflect

Anyone have any tips for outputing numerics (i.e. with precision and scale) from AnalyticPartitionWriter? It seems there is no setNumeric() method.https://docs.vertica.com/24.4.x/sdkdocs/CppSDK/class_vertica_1_1_analytic_partition_writer.htm I'm writing a UDx and it would be very handy to produce VNumerics. Instead I'm using floats which make me nervous. Seems Java has it... why not C++? Is there some other method?

Restoring objects: XXX_PXXX.TABLE1 Approximate bytes to copy: 0. Dry run finished. Run vbr without --dry-run to complete the task. While preprocessing catalog: vertica-extract-snapshot failed:Table XXX_PAXX..TABLE2 references XXX_PXXX.TABLE1 in a SET USING or DEFAULT expression, but is not included in the snapshot. HINT: Back up the tables together, or alter table removing SET USING/DEFAULT on attributes

I have changed the export address to export data from another cluster. Now the migration is completed. How can I change the export address to default value now (same as node address)?

I am following this document but not able to find VerticaDialect.jar on the Micro Focus Vertica marketplace. Please provide this jar for vertica-jdbc-9.1.0-0 and vertica-jdbc-7.2.3-0.jar or Can we use org.hibernate.dialect.PostgreSQLDialect to connect vertica, Is it feasible?

The Vertica partitioning capability divides one large table into smaller pieces based on values in one or more columns. Partitions can make data lifecycle management easier and improve the performance of queries whose predicate is included in the partition expression. To partition a table by more than one column, use the HASH function! Example: dbadmin=> CREATE TABLE some_fact_table (c1 INT NOT NULL, c2 INT NOT NULL, c3 VARCHAR(1) NOT NULL, c4 INT, c5 VARCHAR(100)) PARTITION BY (c1, c2); ERROR 4331: PARTITION BY expression cannot return a tuple dbadmin=> CREATE TABLE some_fact_table (c1 INT NOT NULL, c2 INT NOT NULL, c3 VARCHAR(1) NOT NULL, c4 INT, c5 VARCHAR(100)) PARTITION BY HASH(c1, c2); CREATE TABLE Helpful Link:https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/partition-clause.htm Have fun!

This is driving me nuts today. How do you set the delimiter to tab when spooling out to a file with vsql on windows ? I've tried giving all sorts of values to the -F option but can't get it to work. vsql is interpreting the what I give as literals e.g. if I give -F /t it makes the delimiter in the outout /t, -F '/t' makes it '/t' and -F E'\t'. Any clue ? Thanks Dave

https://forum.vertica.com/discussion/comment/249876#Comment_249876

I'm currently executing a Create Table As operation, but I'm unsure about how to track its progress. I've been examining counters within v_monitor.execution_engine_profiles and v_internal_events, but they appear to be null. Any insights on how to effectively monitor the progress of this operation would be appreciated.

[dbadmin@yyyyyyyyyy ~]$ vsql Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, protocol: TLSv1.2) Bad terminal type: "xterm". Will assume vt100. yyyyydb=> \q I just upgraded from 12.0.4 to 23.3 and noticed that when initiating vsql, I get Bad terminal type: "xterm". Will assume vt100. Is there something need to be done ? Regards Raul

Hi, First: Vertica version: Vertica Analytic Database v11.1.1-11 Visual studio version: 2022 SQL Server Integration Services Project: 1.3.2 I've been playing around with SSIS and Vertica as a destination. As suggested, I'm using ADO.NET as the driver type for both Vertica source and destination. Although I have also tried ODBC and OleDB just in case. Problem: When inserting into column of type TIMESTAMP, the row is never written, nor throws the package any errors and no rows are redirected as error rows. The log from the debug session (the text in the output text window) also states 0 rows written. Receiving column type in Vertica is plain TIMESTAMP / DATETIME, not TIMESTAMP(3) or anything that would restrict the data. I have also ensured that the incoming data pipeline's column type is DT_DBTIMESTAMP and that SSIS is recognizing the Vertica's datatype as DT_DBTIMESTAMP. What I've tried: Tests have been done with both text files and databases (SQL Server, Oracle) as sources with

I have a database with k-safe=1. Hence there are two projections (suffixed with b0 and b1) in place of one super projection. So the data for each table is doubled(may be). In this scenario, how can I get the exact size of a table?? AUDIT function does not provide the exact size,so does COLUMN_STORAGE table. Can anyone help me in finding the exact size of a table as well as schema??

Hi , We are looking for RHEL8.x supported appliance in AWS market place as there are some critical security vulnerabilities with the current RHEL 7.5 appliance. Any update/guidance on this would be greatly appreciated.

Hello everyone, Environment Info I installed Vertica for OPTIC Data Lake on both Linux CENTOS 8 servers. I have one Vertica server and one OPTIC Data Lake (master node) server. I have created a self-signed certificate for the Vertica server. I deployed OPTIC Data Lake with external Vertica. During the deployment process, everything seemed to be good, with all pods up and in a healthy status. However, I realized that there may be no connection between the Vertica and ODL servers. Upon entering the bvd-quex pod, I found the following log message: 'Unable to verify the first certificate.' I also ran the disystemcheck.sh script to understand the connectivity between the Vertica and ODL servers, but I encountered an error. Do you have any suggestion ?

Hello, According to wikipedia : "In relational databases, the information schema (information_schema) is an ANSI-standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database" I think such a feature would help the support of Vertica by many more tools and it's never a bad thing when you follow the SQL norm. Standard, normalization and interoperability matter. Especially when your competition has it (these last years, it has been implemented in Hive or Monet DB as an example) Best regards, Simon

https://forum.vertica.com/discussion/242823/usage-privileges-on-a-schema

When trying to talk to Vertica, we get rejected with the following error. Severity: FATAL, Message: New session rejected due to limit, already 50 sessions active, Sqlstate: 57V03, Routine: addClientSession, File: /scratch_a/release/vbuild/vertica/Session/SessionManager.cpp, Line: 544 I'm not aware of where the 50 session limit is set. When I look at the number of active sessions, it is well below below 50. Is there a way I can raise the limit on the cluster? Or is it baked in?

Hello! There is an ODBCLoader example (https://github.com/vertica/ODBC-Loader) that shows how to avoid fetching unused columns from the source system. But you still have to pass them filled with nulls through the Vertica SDK. Turns out it is very time-consuming too. If you query a single column out of external table with 20 columns it can be 10 times slower than fetching a single column out of external table with only one column. Can you advise me if there is a way to avoid passing nulls for pruned columns out of UDParser?

HI all, I am trying to configure a MAXMEMORYSIZE for a resource pool used for application queries. For this, I profiled a set of representative queries to figure out maximum memory need for these queries. While profiling, I noticed that the memory acquired by the query changes based on which pool I am using. If I used general pool for profiling the query, it uses more than 3 GB and completes in less than 3 seconds. However, if I used another pool with '3G' MEMORYSIZE and planned concurrency of 9,  the query completes in 20 seconds and uses 231 MB. GROUP BY HASH is the most expensive operation in this query and no other queries were runing when the query was profiled. Because I see different values of "Total memory required by the query" when profling the query using different pools, I am wondering whay would be the best way to estimate MAXMEMORYSIZE parameter for the pool. Any suggestions?  Thanks,Rupendra

Does anyone have experience to integrate Vertica with scikit-learn? Any limitation?

Whats does it mean EXECUTIONPARALLELISM? and how we can set it? Why it is required?

To all concerned, We have been notified that AWS has removed the RHEL listings for Micro Focus Vertica from the AWS marketplace overnight in error. We are currently working with AWS to determine the issue, and get the listing reinstated. For customers that have already deployed Micro Focus Vertica on RHEL from the marketplace, AWS states you are unaffected, and can continue spinning up new instances on the RHEL based AMIs. We apologize for this inconvenience, and hope to have it rectified quickly. -Chris

https://forum.vertica.com/discussion/comment/249339#Comment_249339

I've recently deleted some data from a table after inserting them in a different table (using select-insert). I do notice the diskspace usage of the current partition had suddenly shot up (by 500-600GB) after performing the select-insert command. Deleting the data from the original table does not free up the diskspace. I've read about Purging and I would also like to ask: 1. does purging happen automatically or must it be triggered manually? 2. In one of the rows, the used_bytes value is 571,187,565 for one node. Each node has about 600GB diskspace left. Is this enough to perform purging for this projection? 3. Is there a way to perform partial purging? in case 2 is not possible. I am on Vertica 10.1.

We have a requirement of installing Vertica 12 in EON mode on a NetApp ONTAP (NOT StorageGrid) 9 based environment. Does anyone have any experience or suggestions on how to install Vertica 12 using s3 mode?