Rocket® Vertica

Hi, Have the following packages available in our Development Vertica instance: [dbadmin@nbodev ~]$ cd /opt/vertica/packages/ [dbadmin@nbodev packages]$ ll total 24 drwxr-xr-x 5 root root 4096 Nov 3 2015 approximate -rw-r--r-- 1 root root 0 Aug 28 2015 build.ts drwxr-xr-x 5 root root 4096 Nov 3 2015 flextable drwxr-xr-x 4 root root 4096 Nov 3 2015 geospatial drwxr-xr-x 6 root root 4096 Nov 3 2015 hcat drwxr-xr-x 5 root root 4096 Nov 3 2015 pload drwxr-xr-x 5 root root 4096 Nov 3 2015 txtindex [dbadmin@nbodev packages]$ [dbadmin@nbodev packages]$ admintools -t list_packages Package Description approximate Probabilistic/Approximate Aggregation Functions txtindex Common string-manipulation functions pload hcat Java/HCatalog data

Need to reinstall some packages on our development box. We have the following packages: [dbadmin@nbodev ~]$ cd /opt/vertica/packages/ [dbadmin@nbodev packages]$ ll total 24 drwxr-xr-x 5 root root 4096 Nov 3 2015 approximate -rw-r--r-- 1 root root 0 Aug 28 2015 build.ts drwxr-xr-x 5 root root 4096 Nov 3 2015 flextable drwxr-xr-x 4 root root 4096 Nov 3 2015 geospatial drwxr-xr-x 6 root root 4096 Nov 3 2015 hcat drwxr-xr-x 5 root root 4096 Nov 3 2015 pload drwxr-xr-x 5 root root 4096 Nov 3 2015 txtindex [dbadmin@nbodev packages]$ dbadmin=> select lib_name, lib_version from user_libraries; lib_name | lib_version --------------------------+------------- decisionTree_mab_lib | ApproximateLib | 1.0 TxtIndexLib | FlexTableLib | 1.0.0 DTPredLib_fact | DTTrainLib | Trying the following command but fails: [dbadmin@nbodev packages]$

I am creating a Vertica cluster on OpenStack (Kilo) through Trove. It is possible to create a 2-node, 4-node or 6-node Vertica cluster but it is failed to create 8-node Vertica cluster (formed by 8 Nova instances). Background: I have 4 physical server (1 controller node, 3 compute nodes), each of them has 8 physical CPU and 128 RAM. For creating Vertica cluster, the flavor includes 2 VCPUs, 4 GB RAM, 15 GB root disk, 2 GB swap disk. As OpenStack allows CPU over-committing 16:1 and RAM over-committing 1.5:1, therefore 512 vcpu and 768 GB memory could be used in the environment. (At most 8 vcpu is assigned to each instance). After creating the Vertica cluster, I found that the status of Trove instances of the cluster is ERROR. Then, I use /opt/vertica/bin/admintools to view the status of the database cluster state which is UP. I login to the database, and try the database with '\d' or '\dn' but it stuck with no any responds. I cannot find any hints in vertica.log, install.log and adm

We have a lot of direct inserts in our database. So, we have a lot of mergeout operations. But sometimes we observe strange behaviour of that operations. Some mergeout operations consume CPU on one node for couple of hours. LA of that node increase and sometimes this node have a huge delay in Global Catalog operations with this node (transactions lock GC at this node with ~20 second delay). So, sometimes we have GCL timeout problem. This mergeout sessions can be seen in system_sessions table, but doesn't appear in tuple_mover_operations table. We can grep it in vertica.log This looks like this: Average ROS containers count for our projections is about 300-400. Recently, we had similar problem, but it was solved by proper TM configuration: https://forum.vertica.com/discussion/238751/high-cpu-usage So, is this normal behaviour? Has somebody such problems in mergeouts?

We observe high cpu usage on all nodes of cluster. It occurs second day in row and lasts for several hours in each day (3 hours in a first day and 4 hours in a second). We had to turn off out ETL while CPU usage growth. It looks like: We are trying to find out reasons of that behavior. Our observation and checks: 1. We observe great growth of waiting global catalog. Throughput of GCL significantly decrease. 2. RAM, Threads, FileHandles, Network consumption does not differ from usual day. 3. TM does not have any queue. It operates as usual. We can't see any unusual TM operations. 4. We checked all queries in sessions and system_sessions. All as usual, nothing suspicious. System services table shows us usual normal run rate. 5. We don't have any rebalance, partition reorganize operations while problem occurence. 6. We check all data_collector tables (about 200 tables). No success. 7. We can observe light correlation with disk latency. When cpu usage have short gap, disk latency sign

When I'm trying to configure tokenizer with parameter used set to true, I get such error:[5861] [Vertica][VJDBC](5861) ERROR: Error calling processBlock() in User Function SET_TOKENIZER_PARAMETER at [src/SetTokenizerParameters.cpp:194], error code: 0, message: Modification of a config file which is being used for a tokenizer is not allowed. How can I reset parameter used to False?

We get a zipped file of 20 GB in size and we load it through copy command. Could you guys help me in improving the load time in this process? No.of Nodes in Cluster - 62

We have recently configured the Vertica Forum to allow you to share your discussions on your Twitter or Facebook account. Feel free to share our tips and solutions on your social media sites.

Hi, I have a question regarding collecting of statistics. It's possible to collect them on a topk projection? how? I am using 8.0 version. Tried with: select analyze_histogram(projection_name,100); select analyze_statistics(projection_name); Even if the operations completed with 0, with success, the projections' columns appear having statistics_type = ROWCOUNT , and the projection having has_statistics = false. My problem is related to the velocity of querying the projection. It seems querying the topk projection the response time is 10 times greater then querying the table itself. The topk projection is order by appropriated columns. The explain plan shows a lower cost (1000 times lower) when the projection is used, but the reality is different. Thank you, Veronica

Has anyone tried implementing an IsolationForeset R package on RHEL7.2 to use in conjunction with Vertica? It doesn't appear there is an IsolationForest package in CRAN so the other option is to download a version from Github, SourceForge or RForge. Any recommendations? https://datascience.stackexchange.com/questions/18018/is-there-a-r-implementation-of-isolation-forest-for-anomaly-detection

After migration from 5.0 to 7.1, the BODS job failed with below message: SQLSTATE 0A000 Description ERRCODE_FEATURE_NOT_SUPPORTED resulted in error <ERROR 3119:  DML query with a predicate that could not                                                       be pushed below joins and does not refer solely to the target table is not supported                                                       HINT:  Try putting the joins from the UPDATE's from clause into a subquery                                                       >. The SQL submitted is <UPDATE DIMENSION.DIM_PP_OFFER                                                       SET RECORD_END_DATE = '5-JUN-2015',                                                       CURRENT_RECORD_FLAG = FALSE                                                       FROM STAGING.STG_CM9_VOUCHER_OFFER_MAP A                                                       LEFT JOIN (SELECT DISTINCT PARAMETER_NAME VOUCHER_TYPE, PARAMETER_VALUE CHARGE_CODE      

Hi, I am new to Vertica. So far I understand that Stored/external procedures and functions in Vertica do not offer the equivalent complexity/functionality as offered by ORACLE stored procedures and functions. However, Vertica do offer to create UDFs, which can be written in JAVA/Python/R. I am not sure to which extent UDFs support the sql operations. I have two questions regarding UDFs => Do the UDFs offer the same complexity such as - Table joins, Returning Multi-row cursors in the same way as ORACLE procedures do ? I am trying to explore options to develop functions in JAVA is there any step by step instruction to create UDFs using an IDE like Eclipse ? Thanks

'll haDeveloping UDxs in Java Tutorial Part 1: Introduction & Setting Up Welcome to the first part of our tutorial series on developing User Defined Extensions (UDxs) with the Java programming language. In this part, you’ll learn what UDxs are and why you would want to develop one. We’ll also show you how to set up a development environment to make developing your UDxs easier. Read the full story here

Hi I am looking to see if there is an Oracle SQL Cast Collect equivalent in Vertica. The was an easy way to "collect" column values together into the same cell instead of creating a new row. Example: Lake, Boats, Red Lake, Boats, Blue Would look like... Lake, Boats, [Red | Blue]

Good moorning. Need know the size the table. Thanks.

Hi, I trying to connect a REST API developed with Ruby to vertica database. I found a ruby gems for vertica connection but it's not a vertica official project and it's not too match used, so we can't use it in production latre. I think vertica is compatible with PostgreSQL but not 100%. It's safe to use a PostgreSQL ruby connector for vertica ( only for select queries ) ? if no, what is the disadvantage of using a PostgreSQL connector ?

What should be done as an alternative?We want to partition by day but do not want the number of partitions to grow too much that it impinges performance?

Join us for an all- day workshop designed for existing users and people who are already familiar with Vertica. During this workshop, you will learn advanced capabilities and hear from experts about best practices as well as industry tips and tricks! Date/time: October 3, 2017 @ 8:30 a.m. Audience: Data scientists, DBA, IT Managers, Engineers Location: The Westin Bonaventure Hotel 404 South Figueroa Street Hollywood Ballroom Los Angeles, California 90071

We've just updated to 8.1.1-4 and now we observe strange error. All view owners can't select from their views. Code to reproduce: SELECT * FROM DMAB.advert_chargeback; -- SUCCESS CREATE or replace view dmab.test_view INCLUDE SCHEMA PRIVILEGES AS SELECT * FROM DMAB.advert_chargeback; -- SUCCESS SELECT * FROM dmab.test_view; -- Permission denied for relation test_view GRANT ALL PRIVILEGES ON dmab.test_view TO lkp; -- Permission denied for relation test_view DROP VIEW dmab.test_view; -- SUCCESS Workaround is to create view from different user or dbadmin. But this is inacceptable for many use cases.

Configuring the HPE Proliant DL380 Gen9 24-SFF CTO Server as a Vertica Node Click here for a PDF version of the DL380 Hardware Guide. Read the full story here

Hello, I have been trying to connect to Vertica using Powershell as the client.When I plug in the command below Powershell just hangs, as if its processing something but never completes. I have tried this in CMD prompt and it works fine. Has anyone experienced this issue before? PS C:\Users> vsql -h 010.010.1.010 -d MY_DATABASE

My prospect wants to backup large genomic data (500TB) from the point of view of operation mistakes. The genomic data is not updated frequently. Do we generally back up it to shared volumes using Vbr.py? Could you give me your recommendation for this scale?

Hi forum, today I could not find a download offer for a virtual machine of Community Edition. Can you help me find it please? As we refer to this VM in our documentation (Getting Started Guide) we should have an offer available. Thanks Dieter

Hello We are developing a tool to automate and secure data transfer between different DBs (ex : DEV-->TEST). The tool works as follows it has to run on the cluster of the source DB it runs EXPORT command, to be able to transfer either a full object or the result of a query We would like to include operations on **target tables **: optional truncate before copy, analyse_histograms after copy But, even though we issue a successful remote connection to the target DB for the transfer, we cannot execute other commands, they are not allowed : Ex: connect to vertica DEVDB user dbadmin password '*****' on 'HOST',5433; SELECT * FROM DEVDB.COMMON.T1_TABLE; 11:22:54 [CONNECT - 0 rows, 0.064 secs] Command processed. No rows were affected ** 11:22:54 [SELECT - 0 rows, 0.058 secs] [Code: 2983, SQL State: 0A000] [Vertica]VJDBC ERROR: Database "GBADEV" does not exist** Should we do a specific configuration on Server side? Is Vertica handling the notion known as dblink in Oracle ? Or

Dear Team Iam trying to use third party replicaiton solution like arcserve to replicate the vertica node to DR site. Replication works fine but when i start the database after the replicaiton , it never starts . This is file based replication .Iam replicating the entire folder structure of vertica .Is there anything which i need to take care of while doing this kind of replicaiton . Btw copy cluster works fine but Iam looking for somethign automated for vertica solution cheers Santhosh