I am running docker vertica/vertica-ce image. But during start up it loads VMart database as example. I do not want this. Is there a way to disable this? I have tried to set - export WITH_VMART=false - export VMART_LOAD_DATA=n But it loads data anyway :'( Any ideas?

Until now I used Oracle to store xml file in blob data type column. Is there any option in Vertica to copy xml file into binary column? I saw there is varbinary data type but, I haven't succeeded to load this file into one row in this column. If someone is familiar with this process in PostgreSQL can maybe be helpful (share your knowledge)

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 tables in the production schema though. Im looking for the grants necessary to allow a user the ability to create tables. Do most environments create a schema for each user and then grant authorization for the user on that schema or what is the best practice?

Step by step scenario: 1. Start cmd from odcker 2. Run install.sh with list of files 3. sql file with schema loaded (I see tables) 4. sql with data alsoe excuted but no data present in tables. But If I execute same file (!) by Intellij Idea that connected to same docker container where I run vsql - I got data What is going on? How to debug/fix the situation? install.sh file is:

vsql -d mydb -U dbadmin -h localhost  -f /dump/schema.sql
vsql -d mydb -U dbadmin -h localhost  -f /dump/jailer/vertica.sql
vsql -d mydb -U dbadmin -h localhost  -f /dump/data/my_table.sql
vsql -d mydb -U dbadmin -h localhost  -f /dump/data-patch.sql

Same output for manual vsql -d mydb -U dbadmin -h localhost -f /dump/jailer/vertica.sql call:

Time: First fetch (1 row): 58.681 ms. All rows formatted: 58.766 ms
 OUTPUT
--------
     14
(1 row)

vertica.sql content:

Insert into mydb."public".atable(id, column) values
 (17, 'value'),
 (12, 'value');

IntelliJ output:

[2023-05-03 12:46:16] 852 row(s) affected in 7 sec, 881 ms

**Offtop**: somebody please add vsql tag it already present on stackoverflow

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 starting to initialize, but then they shut down. Using last good epoch or admintools -t start_db with --force key don't help. In logs (dbLog, vertica.log) I see only one type of error: SP_connect: unable to connect via UNIX socket to /tmp/4803 (pid=12533): Error: Connection Refused. It is enough space in /tmp and iptables service is disabled. Also I tried to create new database, so it was created and worked well.

hi I want to union 2 tables but I am not sure if the table is exits this is my query: select a from table union select case when exists(select a from table1) then (select a from table1) else (select a from table2) end; got this > ERROR: ERROR 4840: Subquery used as an expression returned more than one row I need to use limit 1 in a subquery but it's not the result that I wanted. do you have other suggestions? thanks table, for example, I just created them in my local env for testing: table 1

CREATE TABLE public.table1 
(
    a int,
    b varchar(80),
    c int
);


CREATE TABLE public.table
    a int,
    b varchar(80),
    c int
);

The incoming event stream data contains information for customer phone devices: - Platform: Android or iOS? - Operating System: (version) - UDID - Phone Model In the event stream, each event telemetry contains a pre-defined event (e.g. User swiped their screen, or, User clicked a button in app). I'd like to model these information into a dimension table, but I'm pretty new to Kimball and reading his books for an afternoon doesn't help too much. I'm wondering if I'm doing the right thing here? Details in next paragraph. I'm thinking about assigning each unique combination of (Platform, OS, UDID, Model) a surrogate key, and during ETL, I'll check to see if that unique combination is in the dimension table. If it's not there, the key self-increases and the combination is recorded. If it is, no change is made to the dimension table. The dimension table consists of 5 fields, a surrogate PK (join with fact table), and Platform, OS, UDID and Model. Say we have the following data coming in: UserID | EventName |...|Platform|OS|UDID|Model -------|-----------|---|--------|--|----|----- 111 | Swipe_Up |...|Android|Android 8.0|123-345-678|Google Pixel 111 | Click_UI |...|iOS|iOS 11.0|abcdefg|iPhone 11 200 | Swipe_Up |...|Android|Android 8.0|123-345-678|Google Pixel 201 | Swipe_Down |...|iOS|iOS 13.0|hijklmn|iPhone 12 201 | Swipe_Down |...|iOS|iOS 13.0|NULL|NULL 230 | Swipe_Up |...|iOS|NULL|NULL|NULL 300 | Swipe_Up |...|Android|Android 8.0|NULL|Google Pixel So you can see user 111 used two phones, so that should be two devices. User 200 has the same phone as User 111 (even same UDID) so that's not a new record for my dimension table. User 201's second row seems to be the same as his first row, but because of the NULLs it is technically a new device. Of course maybe business wants to assign some rules to say "Hi as long as a few fields are the same, we consider them as the same device". In total I should have 5 unique rows in my dimension table. Does this make sense to you?

In Vertica, the SELECT privilege on all system tables in V_CATALOG and V_MONITOR is granted to PUBLIC. Therefore, by default all users can query them. And not all system tables seem appropriate to be public. For the most part, for each of the entries, the results appear appropriately limited to a users specific grants. For instance, select * from v_catalog.columns; returns just columns for which the user has been granted SELECT for the table and USAGE for the schema, and fortunately SELECT * FROM v_catalog.passwords; returns no rows at all. But, some seem overly broad, such as SELECT * FROM v_catalog.users returns all users for all schemas. Is there any downside to REVOKE SELECT ON v_catalog.users for a user intended to have only SELECT access to specific tables in a specific schema? Or further revoke SELECT on V_CATALOG and V_MONITOR entirely for public users? https://dba.stackexchange.com/questions/118260/limit-access-to-specific-database-only-and-restrict-access-to-system-tables

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 should yield 800MB/s of "large block random I/O" throughput. Cost of the storage becomes pretty high, once we count in drives, RAID overhead and server chassis necessary to hold all the drives. 10k RPM drives are relatively small (SAS 10k RPM tops at 1.2TB max) and we need many of them to provide the total storage required. SSD are four times more expensive with SATA/SAS becoming the bottleneck. I have been thinking about "hybrid" solution of using super-fast NVMe storage (2GB/s thoughput) as cache for a much more slower HDD backend (couple of 10TB SATA drives in RAID1). This can be accomplished by using Bcache technology of linux kernel. The assumptions are: - most reporting queries to vertica cover only "hot" data area of hour/day/week worth of data. Bcache would cache the frequently used data onto NVMe. - as time goes, "hot" data area shifts on the backend disks surface and Bcache automatically adjusts cached blocks to represent it. - write queries (data import) of vertica will be much slower than reads and that would not block any read operations (write-through mode). This is ok, as we do data import in bulk and don't need it to be immediate. Back of envelope calculations show that cost of such system to be in range of $2000 as opposed to $8000 fully SSD-based solution (per server). Can this approach work for vertica (or any other analytics database)? Have anyone used the "fast cache in front of slow backend" approach for databases?

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, anchor_table_name, SUM(used_bytes), SUM(row_count) FROM v_monitor.projection_storage GROUP BY anchor_table_schema, anchor_table_name But for empty table (0 rows) I got non-zero SUM(used_bytes) and SUM(row_count). So this way doesn't work properly. And I calculate an audit by select audit('table_name') for each table, but it's very slowly. So my questions: 1. Is there other way to find compressed size (and maybe a row count) of a table? Because I got 80000000 bytes instead of zero. 2. Is there more faster way to calculate an audit? I know vertica calculates audits every day for each table, but I can't find where results is stored.

I'm trying to create a temporary table using CTE's/With. Is there a Vertica equivalent for this approach? WITH vals (k,v) AS (VALUES (0,-9999), (1, 100)) SELECT * FROM vals; I want to be able to use the data without creating a temp table https://dba.stackexchange.com/questions/86724/how-to-create-a-temporary-table-using-values-in-postgresql

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 SELECT on a table? I've poked around in the GRANTS and ROLES system tables, but I can't find any role->table mapping. I considered using HAS_TABLE_PRIVILEGE() but that does it on user level. I'd rather do it at the role level.

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 could not get it working properly. How can I solve that issue (maybe the problem is not in projection...) I've tried following code: CREATE TABLE public.ProjectionsTest ( id int primary key, a varchar(2000) ); INSERT INTO public.ProjectionsTest values (1,'a'); INSERT INTO public.ProjectionsTest values (2,'b'); INSERT INTO public.ProjectionsTest values (3,'c'); INSERT INTO public.ProjectionsTest values (4,'d'); INSERT INTO public.ProjectionsTest values (5,'e'); SELECT ANALYZE_STATISTICS('public.ProjectionsTest'); SELECT REFRESH('public.ProjectionsTest'); CREATE PROJECTION public.ProjectionsTest_lower_ob ( id, a ) AS SELECT id, lower(a) a FROM public.ProjectionsTest ORDER BY lower(a), id SEGMENTED BY hash(id) ALL NODES ; explain SELECT id, lower(a) FROM public.ProjectionsTest WHERE lower(a) = 'a' ORDER BY a, id; -- ROLLBACK DROP PROJECTION public.ProjectionsTest_lower_ob; DROP TABLE public.ProjectionsTest; And I am getting following result (the projection "public.ProjectionsTest_lower_ob" is not used): ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ explain SELECT id, lower(a) FROM public.ProjectionsTest WHERE lower(a) = 'a' ORDER BY a, id Access Path: +-SORT [Cost: 36, Rows: 4] (PATH ID: 1) | Order: ProjectionsTest.a ASC, ProjectionsTest.id ASC | +---> STORAGE ACCESS for ProjectionsTest [Cost: 35, Rows: 4] (PATH ID: 2) | | Projection: public.ProjectionsTest_super | | Materialize: ProjectionsTest.a, ProjectionsTest.id | | Filter: (lower(ProjectionsTest.a) = 'a')

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 : SELECT DISTINCT orig_h , resp_h from gustavo.conn; - 15.214.135.70 15.214.159.100 - 15.214.136.45 15.214.137.70 But How I can get from this result the just the rows that match with 15.214.135.70 15.214.159.100

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 to get things working. Thus, the internal Vertica configuration/metadata is not well tracked nor understood. Im looking for a way to export all the internal configuration details and metadata needed to 1) Document what the current configuration is, and 2) Deploy that configuration to a new TST Vertica database. Some of the things I am looking for are: 1. ACCESS/Authentication/LDAP config 2. Profiles/Resource/Roles/Users/Options 3. Object DDL, Flex tables, Schemas, Sequences 4. Anything else metadata related to be able to document/reproduce the configuration.

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 Required join Table output should be like Name,Address,Employee ID,Team 1.john,new York,null,null 2.Diana,new jersey,null,null 3.null.null,123,developer 4.null,null,568,tester I want above sample as output is there any way to solve this issue.

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 | 0 | 0.40 | 0 0.40 2016 | 7 | 0.50 | 0 | 0 0.50 2016 | 7 | 0 | 0 | 0.30 0.30 How I can combine to have a single line : year_created|month_created|loc_id_1|loc_id_2|loc_id_3| ------------------------------------------------------- 2016 | 7 | 0.50 | 0.40 | 0.30 This is my query : SELECT MONTH(created_at) as month_created, YEAR(created_at) as year_created, 1 - SUM(price)/SUM(order_total) as Total, CASE loc_id WHEN 1 THEN 1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_1', CASE loc_id WHEN 2 THEN 1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_2', CASE loc_id WHEN 3 THEN 1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_3' FROM data_price WHERE YEAR(created_at) = YEAR(NOW()) GROUP BY year_created, month_created ORDER BY year_created asc, month_created desc

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 extract license_size for an old date, but how to extract size of each table say a month ago.

Column names: Site_key. Billing_date ,Reason_type Things to Find: 1. Consecutive count for last 5 months: 2. Last 3 month Count 3. Last 6 month Count I have to find Consecutive count for last 5 months for a site_key based on the condition Reason_TYpe. There are 4 distinct Reason_type: Zero_bill Same_Units Units_Deviate Average_Bill\ I need to find for a particular site key say for ex: 3, billing_date is say 20-11-2015, and reason type ='Zero_Bill' 1. How many consecutive previous scenarios were there were site had Zero_Bill (excluding current month) 2. How many such Scenarios in last 3 months (including current months) 3. How many such Scenarios in last 6 months (including current months) Sample data: site_key | billing_date | Reason_type ----------+---------------------+--------------------- 3 | 2015-01-03 00:00:00 | AVERAGE_BILL 3 | 2015-02-03 00:00:00 | ZERO_Bill 3 | 2015-03-01 00:00:00 | ZERO_Bill 3 | 2015-04-03 00:00:00 | AVERAGE_BILL 3 | 2015-05-03 00:00:00 | AVERAGE_BILL 3 | 2015-06-03 00:00:00 | ZERO_BILL 3 | 2015-07-03 00:00:00 | ZERO_BILL 3 | 2015-08-03 00:00:00 | AVERAGE_BILL 3 | 2015-10-06 00:00:00 | ZERO_BILL Expected Output: site_key | billing_date | Reason_type |LAST_Consecutive| Last_3_Months | Last_6_months ----------+---------------------+----------------------- +----------------+---------------+-------------------- 3 | 2015-01-03 00:00:00 | AVERAGE_BILL |0 |1 |1 3 | 2015-02-03 00:00:00 | ZERO_Bill |0 |1 |1 3 | 2015-03-01 00:00:00 | ZERO_Bill |1 |2 |2 3 | 2015-04-03 00:00:00 | AVERAGE_BILL |0 |2 |2 3 | 2015-05-03 00:00:00 | AVERAGE_BILL |1 |2 |2 3 | 2015-06-03 00:00:00 | ZERO_BILL |0 |2 |3 3 | 2015-07-03 00:00:00 | ZERO_BILL |1 |2 |4 3 | 2015-08-03 00:00:00 | AVERAGE_BILL |0 |1 |3 3 | 2015-10-06 00:00:00 | ZERO_BILL |0 |3 |3 What I tried: For Consecutive count: Select site_key, billing_date, Reason_type, rank() over (partition by site_key,billing_date,reason_type order by billing_date) as consecutive_count from schema.AVG_SCN where site_key=3 order by 1,2; For Last_3_Months and Last_6_Months: Select site_key,Billing_Date,Case when cnt>3 then 3 else cnt end as Count_OF_3, Case when cnt>6 then 6 else cnt end as Count_OF_6 from ( Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Zero_Bill' group by billing_date,site_key,Reason_TYPE union all Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from Schema.AVG_SCN where reason_type='Average_Bill' group by billing_date,site_key,Reason_TYPE union all Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Units_Deviate' group by billing_date,site_key,Reason_TYPE union all Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Same_Units' group by billing_date,site_key,Reason_TYPE) as a;

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 is that users are confused about why they can see some tables in one but not the other. It's related to permissions, but it's not clear exactly how. For example, a user is telling me they have access to query a table. However, that table doesn't show up in TABLES and its columns don't show up in COLUMNS, but the table _does_ show up in ALL_TABLES. So, this leads to the following questions: 1. What's the difference between ALL_TABLES and TABLES? 2. How can users see a list of all the tables (and columns!) they have access to query?