We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [vertica]

Vertica is a high performance commercial column-oriented DBMS that runs mainly in a Clustered environment. It uses ANSI SQL and runs on Linux.

Filter by
Sorted by
Tagged with
0
votes
1answer
39 views

How get in SQL pairs of connections in a FW Logs

I have a database with orign_ip, destination_ip, and other columns. I am looking for a way to get in SQL just the connections between two IP's, I need to get just the list. This is my first step : ...
0
votes
1answer
19 views

customized join output [closed]

Example consider two tables Persons and Employee Persons table sample: Name,Address 1. john,new York 2. Diana,new jersey Employee table sample: Employee ID,Team 1. 123,developer 2. 568,tester ...
0
votes
0answers
23 views

Why is Vertica Management Console reporting my TZ is not set

I am experimenting with setting up a Vertica Community Edition (9.2.0-0) cluster using three virtual machines. In the Vertica Management Console, when I enter my IP addresses and ask to "Validate Host"...
0
votes
1answer
174 views

What's the difference between ALL_TABLES and TABLES?

The docs are unenlightening: ALL_TABLES: Provides summary information about tables in a Vertica database. TABLES: Provides information about all tables in the database. The reason I'm asking ...
1
vote
1answer
59 views

Vertica Export configuration

I have a DEV Vertica database and need to create a new TST Vertica database. During the past few months both administrators and users have had pretty-much wide open access to do what ever was needed ...
1
vote
2answers
969 views

How “LIKE” by LONG VARCHAR?

I had DB Vertica 8.0. Create schema WAREHOUSE with table include field by type LONG VARCHAR. Now I tried to execude SELECT for example SELECT * FROM WAREHOUSE.ALL_EVENTS a WHERE a.original_data ...
1
vote
2answers
312 views

Vertica-cluster architecture

I know that MongoDB has master-slave architecture and Cassandra has peer to peer architecture. I didn't find any resources about Vertica's architecture. Is that master slave or peer to peer? ...
1
vote
1answer
263 views

NVMe+bcache as huge storage cache for vertica database

We are in process of selecting hardware for 3 machines vertica cluster capable to serve 20-30TB of data (15TB compressed). This translates to 8TB of post-RAID storage. With 16 CPU cores, the storage ...
1
vote
1answer
191 views

Vertica grant user create table

I have users (developers) in Vertica who need to create tables for testing purposes, which will later be deployed under specific production schema. I do not want them to have the ability to create ...
0
votes
1answer
184 views

How to get Size of db tables one month back

Recently there has been a drop in our db size and we want to compare the size of the tables back then with that of now. For this purpose we need the exact size of each table one month back. We can ...
-1
votes
1answer
247 views

Starting SQL script with bash passing values [closed]

My use case is as follows. I have some SQL scripts which copy data from a CSV to a Vertica database. For every run, I want to add a special date to the rows. Since I am starting those SQL scripts ...
0
votes
1answer
134 views

How to create projection for proper using of UPPER function?

we've faced the situation when the operation WHERE UPPER(columnName) IN ('PRED1', 'PRED2', 'PRED3') works very bad. If I remove UPPER then it performs good. I've tried to play with projections and ...
0
votes
1answer
142 views

Vertica DB user permissions becomes active only after connection is closed

I am using Vertica Analytic Database v7.1.1-12. I faced the issue then I am granting permissions to the role and then assigning role to the user and the permissions are finally granted only after I ...
1
vote
1answer
350 views

Vertica UNION and ORDER BY

I want to use ORDER BY clause but still have one specific entry on the top. I thought this might do it but Vertica is ignoring the ORDER BY clause on the second query: (SELECT country_id, ...
0
votes
1answer
2k views

I can't restart Vertica database

I have database in Vertica on 4 nodes (RedHat OS). It was working until I decided to restart my DB. After nodes were shut down, they can't be UP anymore. I try to start DB via adminTools: nodes are ...
1
vote
1answer
269 views

How can I reduce the Tuple Mover's disk I/O impact for mergeouts?

I have a cluster with a constant trickle load of new data fed by a stream of large batch COPY statements and also simultaneously support interactive read-queries against the most recently added data, ...
0
votes
1answer
67 views

In a data warehouse with small amount of writes, would RAID 1 be better than RAID 1+0? [closed]

We are using HP Vertica database 7.2 community version. 300GB of data on one node. Thats a small amount of data. HP officially recommends RAID 1+0. https://community.dev.hpe.com/t5/Vertica-...
1
vote
2answers
158 views

Problem of SUM of the SUM [closed]

When I execute my query I have always the same problem : year_created|month_created|loc_id_1|loc_id_2|loc_id_3|Total ------------------------------------------------------------ 2016 | 7 ...
2
votes
1answer
69 views

Why does CURRENT_LOAD_SOURCE() return “Batch No. 1” instead of the actual source file name?

I'm loading some data into a table like this: COPY table1 ( col1, col2, file_name AS CURRENT_LOAD_SOURCE() ) FROM LOCAL :src_file REJECTED DATA :rejected_file EXCEPTIONS :exceptions_file ...
1
vote
2answers
2k views

Size, audit and rowcount of a table in Vertica

I need to find a compressed size, an audit and a row count for each table in some schemes. For a compressed size and a row count I tried to use projection_storage: SELECT anchor_table_schema, ...
2
votes
2answers
1k views

Why would Vertica let me start a transaction if I can't roll it back?

I run the following statements against a Vertica database, one at a time: BEGIN TRANSACTION; UPDATE table SET col1 = 'something' WHERE col2 = 'something else'; SELECT COUNT(*) FROM table WHERE col1 ...
-1
votes
1answer
2k views

COPY in TSV data enclosed by quotes; treat empty strings as NULLs

I have a TSV file like this, except with many more columns: "name"\t"color"\t"lucky_number" "Nick"\t"Red"\t"" ""\t"Blue"\t"8" I'm trying to COPY this file into a table and have empty cells (i.e. "") ...
1
vote
1answer
2k views

Vertica performance on select from view with union

We have a huge number of rows(tens of millions), and for each table we have an associated view that is a select with UNION ALL between that table and several others. Now, we are doing a select from ...
4
votes
1answer
240 views

Is Vertica's COPY atomic?

Seems like a simple thing, but I can't seem to find it in the docs for COPY. Is Vertica's COPY atomic? If a data load is interrupted or somehow fails, does Vertica take care of the cleanup?
1
vote
0answers
763 views

How to find consecutive count last 3 months count and last 6 months counts

Column names: Site_key. Billing_date ,Reason_type Things to Find: Consecutive count for last 5 months: Last 3 month Count Last 6 month Count I have to find Consecutive count for last 5 months for a ...
0
votes
1answer
88 views

How to Find Consecutive count of a column based on a their status

I have a table containing various sites and billing information of those sites. Ex: site_key | billing_date | Reason_type | rank ----------+---------------------+-------------+------ ...
0
votes
1answer
41 views

Set Default Maximum Rows for Export

I am more of a Vertica database user than admin, so I'm not even sure if what I'm asking is absurd, but here goes nothing... Could I, as an admin, put default restrictions on the number of rows that ...
1
vote
1answer
870 views

Vertica INSERT speed highly depends on server load

I faced a problem with very slow INSERTs in Vertica DB. I made such test: create empty table with no projections and start inserting in it in batch, using > INSERT INTO table SELECT 111,222,333 ...
-1
votes
2answers
268 views

Vertica 6.1: vsql creashes with large resultset

I'm writing a script to export data from our Vertica cluster. The command I'm using is: vsql MY_DB_NAME -At -F$'\t' -o /data/vertica_archive/MY_DATA_2015_07_01.tsv -c "select * from DATA_TABLE where ...
3
votes
1answer
275 views

Why does the Tuple Mover in C-Store consider only rows older than LWM?

In the Tuple Mover section of the C-Store paper by Michael Stonebraker (link: http://db.csail.mit.edu/projects/cstore/vldb.pdf) the following is described: MOP (merge out process) will find all ...
4
votes
4answers
500 views

Pairwise intersections

I have a table with two columns, let's say FirstName and LastName. I need to get another table, which for every pair of FirstName's from the first one contains a count of the common LastName's. Is ...
1
vote
2answers
748 views

How to architect an analytical BI platform? [closed]

I am working on a new BI project for a medium sized corporation. Right now there is no Analytical infrastructure in place and the reporting is done manually in Excel. There are a few different data ...
2
votes
1answer
4k views

Where do I find the tables that a role has access to in Vertica?

I'm trying to use the System Tables to fetch some metadata about the whole Vertica cluster at a table level (including permissions). Where can I find the information about permissions required to ...
6
votes
3answers
15k views

How do I get the size of a Vertica database?

I have a Vertica database. How can I tell how big the database is? I need a rough answer for the purposes of reporting basic statistics about our environment to upper management.
1
vote
1answer
2k views

Why bulk loading a Vertica database table does not load all lines?

I have a small csv file I would like to bulk load into a Vertica 6.1 database table, this file has 92 lines. dbadmin=> COPY games FROM '/tmp/games.csv' DELIMITER ','; Rows Loaded ------------- ...
4
votes
1answer
2k views

create linked server to vertica

I'm attempting to create a linked server from SQL Server to Vertica to pull over some data. I've attempted using the following ODBC found here. The code I'm currently trying is: EXEC dbo....
1
vote
2answers
3k views

Does HP Vertica run on Windows and is it compatible with our existing Oracle SQL?

I am new to HP Vertica. I read the installation documentation for HP Vertica, but it seems to be totally unix focused. Can we install HP Vertica under Windows? Additionally, will we need to update ...
0
votes
0answers
613 views

Vertica pagination

I have a query that gives the total number of rows to use in a pagination: select count(1) as total_rows from (subquery with some sum() and 2 inner joins) as as total_sums ; when I run only ...
2
votes
2answers
157 views

What is a “leaf expression”?

What is a "leaf expression"? Partition-clause expressions are limited in the following ways: .... You can specify leaf expressions, functions, and operators in the partition clause ...
1
vote
2answers
1k views

In the Vertica database, what is a namespace?

In the Vertica database, what does the term "namespace" mean? I have reviewed the entire Vertica documentation and cannot find what this means.
0
votes
3answers
2k views

how to find vertica community edition and how to export / copy data

I have the hardest time downloading anything other than enterprise edition from vertica.com. It is supposed to have a community edition on https://my.vertica.com/community/ but all the links just show ...
3
votes
1answer
3k views

How To dump data from Oracle to Vertica

I want to transfer data from Oracle to Vertica and I want to know what is best way to do it? I s there a tool for it? I tried to dump the Oracle data and load it to Vertica using 'Copy Direct' ...
3
votes
2answers
2k views

How to load data into Vertica from C?

I'm investigating column oriented databases and came across Vertica. My need is to feed the Vertica database from C code. I don't succeed in grabbing this information from Vertica: I'm told to use "...