Stack Overflow
Stack Overflow

Questions tagged [vertica]

Vertica is an MPP column-store analytics database that runs on clusters of Linux servers or in the cloud. The original configuration (Enterprise mode) distributes data and query execution among nodes (typically with duplication). A newer configuration (Eon mode) separates compute from storage and relies on S3 for storage.

Vertica is a columnar database designed for data warehousing. Vertica is based on C-Store, which was an academic project involving academic and entrepreneur Michael Stonebraker (a co-founder of Vertica, Ingres, VoltDB) and colleagues from Brandeis University, Brown University, MIT, and University of Massachusetts Boston.

As part of the C-store architecture table data is stored on disk in columns rather than in rows. Columns are stored independently of other columns and allow for increased compression on storage and decreased I/O during query operations.

Vertica has advanced analytical and machine learning functions built-in.

Vertica is designed to run on clusters of Linux nodes. A Vertica cluster runs in a MPP (Massive Parallel Processing) fashion, and it is designed to scale linearly by adding nodes to the cluster.

Vertica clusters can be deployed on premise (in Enterprise Mode) or in the cloud (in either Enterprise Mode or Eon Mode). Enterprise Mode consists of Vertica nodes that share compute and storage resources. Eon Mode is a cloud based mode in which there is a separation of compute nodes (AWS EC2 instances) and storage (S3). This separation of compute and storage allows for rapid scalability (adding or removing compute nodes) without the need to rebalance data across the nodes in the cluster.

Veritca was purchased by HP in 2011, and then acquired by Micro Focus as part of the HPE merger in 2017. As such, Vertica is sometimes referred to in old documentation and community posts as HP Vertica or HPE Vertica.

Vertica comes with a command line utility called vsql for connecting to the database.

1420 questions
-1
votes
1 answer

How to join tables by date column with different date format in SQL Vertica?

I have two tables in SQL table1: date col2 2020-10-25 2 2020-10-20 20 table2: date col1 25.10.2020 1 20.10.2020 10 As you can see i have different format of date. My question is how can I in Vertica SQL join this two tables on…
dingaro
  • 2,156
  • 9
  • 29
-1
votes
2 answers

Do count(*) for each table which comes as output of another select

I want to use output of select as input of another. Select 1 - select table_name from tables; - this gives 15 tables. Select 2 - I want to do a count row count (*) for each table. I tried doing select count(*) from (select table_name from tables) as…
Manu Chadha
  • 15,555
  • 19
  • 91
  • 184
-1
votes
2 answers

TypeError: __init__() takes 1 positional argument but 2 were given Python

from locust import HttpLocust, TaskSet, task, between, events, Locust, User, HttpUser import vertica_python import time, logging def get_sample_query(): query = ''' SELECT COUNT(*) FROM tst.test_table''' conn = { 'host':…
J. Doe
  • 63
  • 2
  • 11
-1
votes
2 answers

With clause in vertica sql

Querying different tables/schema using with clause WITH T1 as ( SELECT something as s1 from schema1.table1 ), WITH T2 as ( SELECT something as s2 from schema2.table1 ) SELECT * FROM T1,T2; It gives error as : ERROR: Syntax error at or near…
supernatural
  • 1,107
  • 11
  • 34
-1
votes
1 answer

Applying Function On Every Record - SQL UDF or Spark

I am using Vertica and I have some functions written in Python that use numpy linear algebra capabilities/scipy optimization and are too complicated to write in SQL. Based on benchmarks which of the options will perform better - 1- Vertica UDF -…
Michael
  • 427
  • 1
  • 3
  • 13
-1
votes
4 answers

How how count rows between two dates in SQL?

I have a table with columns index(sometimes changes to B) ,price and pricedate. I want to add column counts, which shows the number of working day in the specific month I tried following code, however it shows that the column price should be also…
John Castello
  • 33
  • 4
-1
votes
2 answers

How to use SQL so the one Select is used if one condition and another Select if another condition

I have a table A, which is created when I use SELECT_A code and Table B, which is used with SELECT_B code. Additionally I have table C, where one of the columns is DATE. I want to create a query so that if maximum (latest) date in Table C (say it…
John Mayer
  • 103
  • 7
-1
votes
2 answers

Vertica SQL - pull a specific value in a column,

beginner here with Vertical SQL. There is a column called TEXT in our database. It has a bunch of information that I would like to parse that I do manually. Example Below TEXT Age = 51, Country = USA, State = NY, ..... How would I do a subquery…
Lawrence
  • 23
  • 4
-1
votes
3 answers

Lookup specific rows in SQL (vertica)

I have basically one table but I have created another(subset) table from the original one. I'll original table as Table 1 and subset table as Table 2. Table_1 looks like this: Table_2 looks like this: Final_table needs to look like this: Now as…
Shubham
  • 1
  • 2
-1
votes
1 answer

USING CAST STATEMENT IN INSERT INTO STATEMENT SQL

Below (1) is the query which I wrote and when I am not using CAST i am getting error (2) and when am using cast its throwing another syntax error(3) (1) /*DROP TABLE IF EXISTS LFB1_BACKUPTABLE CREATE TABLE LFB1_BACKUPTABLE AS (SELECT * FROM…
shehnaz
  • 1
  • 1
-1
votes
1 answer

How to generate daily score in join based on most recent date on reference table

SQL Vertica Question (but any SQL query answer is appreciated) I have a table "Base" currently in "day-url" level, and I try to get 'score' for each url in each day by joining a "day-url" level reference table "Score Update". The problem is the…
Virgil
  • 9
  • 3
-1
votes
2 answers

SQL - For each ID, values in other columns should be repeated

The table I am trying to create should look like this **ID** **Timeframe** Value 1 60 15 1 60 30 1 90 45 2 60 15 2 60 30 2 90 45 3 …
Sofia Ova
  • 1
-1
votes
1 answer

What is the fastest way of loading huge data(100 gb) in vertica database?

"What is the fastest way of loading data in vertica database ? If i have 100 GB of data which i need to laod in vertica table in a fast and efficient way."
Karam
  • 411
  • 4
  • 10
-1
votes
3 answers

COPY ... FROM LOCAL does not output error on console

COPY .. FROM LOCAL does not output error on console. For example: create table test (col1 INT); and I have a file (abc.data) containing data to insert to the table test as below 1237232632624232 111 222 I used the command as below to insert data…
hhy
  • 55
  • 1
  • 7
-1
votes
1 answer

SQL Queries on Python using Variables

I have a list of IDs L1=['A1','A14','B43'] I am trying to use a SQL script to extract information from a table where the ID is in the above list. sqlquery= "select * from table where ID in " + L1 cur.execute(sqlquery) I've connected to vertica…
Lax Mandis
  • 131
  • 4
  • 14
1 2 3
…
94 95