Hot answers tagged

15

There are several ways to get the size of a database, each suited to a slightly different use case. It's important to note that Vertica uses raw and compressed data in different ways, and that you should be conscious of which size you require. For example, licensing is based on the raw data size. Raw Size The raw size is useful for capacity planning or ...


7

Vertica supports ANSI SQL-92 isolation levels with standard ACID properties. If a COPY is terminated, interrupted, or an error occurs, it will be rolled back. By default, HP Vertica uses the READ COMMITTED isolation level. The COPY command automatically commits itself and any current transaction (except when loading temporary tables). HP recommends ...


5

With respect to your question about using Vertica during the ETL, it's very rarely necessary (although not unknown) to use a different type of database for the ETL. I would not do that unless you perceive a specific need to do so. The only times I've ever heard of this being done due to interactions with legacy data sources. Although @Kermit works with ...


5

Given that the referenced paper is 10 years old, I would recommend looking at a The Vertica Analytic Database: C-Store 7 Years Later since Vertica has more automatic epoch advancement mechanisms. For reference, the acronyms used now are: WOS - Write Optimized Store ROS - Read Optimized Store AHM - Ancient History Marker (Low Water Mark) LGE - Last Good ...


4

The query needs ORDER BY in the end, not in the subqueries (and note that the parentheses are optional there, at least in standard SQL, not sure if Vertica requires them): ( SELECT country_id, country_name FROM country_dim WHERE iso_country_code LIKE 'US') UNION ( SELECT country_id, country_name FROM country_dim WHERE iso_country_code NOT LIKE '...


4

I'm going to use MS SQL Server to do this since I have a copy to hand. I believe most any of the majors would do it similarly. First a sample table, with data. I use a table variable but it's the same for any flavour of table. declare @t table (FirstName char(10), LastName char(10)); insert @t(FirstName,LastName) values ('John','Smith'),('John','Doe'),('...


4

Vertica includes an ODBC driver for C. Read "Programmer's > Guide Vertica Client Libraries > Programming ODBC Client Applications" This section details the process for configuring the Vertica ODBC driver. It also explains how to use the ODBC API to connect to Vertica in your own client applications.


4

I'm going to assume that you already have the budget to implement some data warehouse solution. Just briefly talking about Vertica; it is a load and read optimized platform, and certainly not designed for OLTP. The piece on staging and processing data would need some more thought. Vertica isn't really designed to have data staged, cleansed, and moved into ...


4

SELECT * FROM grants ORDER BY grantor, grantee; The privileges_description column shows the privilege types, such as (in your case) SELECT. (If you only want the SELECTs, you can refine that query.) Source: Viewing Privileges Granted on Objects in the Admin Guide. (That's from the documentation for version 7.1.x.)


4

The driver in the connection string needs to be Vertica, not Vertica ODBC Driver 4.1. EXEC dbo.sp_addlinkedserver @server = N'VERTICA', @srvproduct=N'', -- SQL Server driver @provider=N'MSDASQL', -- Connection string @provstr=N'Driver=Vertica; Servername=ServerName; Port=5433; Database=...


3

HP Vertica has their community edition for download at https://my.vertica.com/download-community-edition/. If you don't have one already, you will be prompted to create a MyVertica login. If you have problems creating a login, please email me at dsandahl@vertica.com. If you have further issues loading your data, you can tweet at @VerticaHelp and support will ...


3

Try to export the data into csv format, and recreate the table in Vertica using the DDL from Oracle. Check the types as they differ from Oracle to Vertica. Example: assuming the following table structure on Oracle: CREATE TABLE PEOPLE ( ID NUMBER (10), NAME VARCHAR2 (50), ADDRESS VARCHAR2 (250), PRIMARY KEY (ID) ) Assuming a compatible ...


2

Vertica only runs on Linux, you can have a Windows client to acesso Vertica. But don't get scared Linux is not such a scary dragon kkkkk. Vertica installs as 1,2,3 check this link for a full installation article. Vertica uses SQL ANSI so no need for you to learn other languages, it has it's differences but is easy to work with. No need for you to break ...


2

This certainly looks like a bug, whether you want to characterize it as an oversight, unfinished feature, or something else. Vertica support tells me this is a known issue and a fix is being tracked as part of VER-36735. (Unfortunately, their issue tracker is not visible to the public.) The problem appears to be related to the LOCAL clause, which is used ...


2

I have to admit my question was a bit flawed. What I really needed was not "for every pair of FirstName's from the first one contains a count of the common LastName's". In fact, I do not care about pairs with zero counts. When the question is corrected, the solution becomes much faster. Given the input: create local temp table t (FirstName char(10), ...


2

Vertica documentation about LONG datatypes, in either version 7.1 or 8.0 states: Notes For optimal performance of LONG data types, HP Vertica recommends that you: Use the LONG data types as storage only containers; HP Vertica does not support operations on their content. Use the VARBINARY and VARCHAR data types, instead of their LONG ...


2

ALL_TABLES shows tables and views. TABLES shows only tables: dbadmin=> create or replace view test as select now() ; CREATE VIEW dbadmin=> dbadmin=> select * from test; now ------------------------------- 2018-08-13 13:04:24.500297-04 (1 row) dbadmin=> dbadmin=> select * from all_tables where table_type = 'VIEW'...


1

Unless you keep a log of it , you cannot. There is no history mechanism to keep track of tbl sizes. you also can get a lot of scripts and articles on Vertica here www.aodba.com What i do to keep an eye on this kind of stuff is logging this info in a dba use table using a cron job. ##will run every 30 minutes */30 * * * * /home/dbadmin/scripts/dba/...


1

Here is small example of how you can improve the query speed. One way i do it is by using bucket columns based on default value at load time.(this can make your load slower thou). see sample code drop table test_tbl; CREATE TABLE test_tbl ( id INT, name VARCHAR(20), bkt_col int default( CASE WHEN name=...


1

To enable the newly granted role you need to issue the command SET ROLE mynewrole in that user's session. If you need this role to be effective every time you may want to change the user's default role: ALTER USER ... DEFAULT ROLE .... References: Enabling roles Changing default role


1

This is a pretty open ended question and you might want to just test it yourself and see. It depends on how you query and write the data. This will determine how many IOPS you need at peak use, which if you want to ensure you'd figure out by testing then deciding. However if that is not an option, do you have hundreds of gigs of RAM? Some systems do. ...


1

Doh! Here's a better way: SELECT city_a, city_b, COUNT(*) FROM ( SELECT a.city city_a, a.state, b.city city_b FROM us a CROSS JOIN us b WHERE a.state = b.state AND a.city < b.city ) x GROUP BY city_a, city_b ORDER BY 3 DESC; Output: +-----------+---------...


1

Vertica support have acknowledged this issue and are tracking a fix under VER-44735. The Vertica issue tracker is not publicly visible, unfortunately. Vertica refused to characterize this as a bug and labeled it instead as a "new feature request", but regardless this should be addressed in an upcoming release.


1

Vertica (actually, I think most if not all databases) behaves in this manner because the part that processes the BEGIN TRANSACTION SQL statement, namely the query parsing and execution engine, is not aware of the client AUTOCOMMIT setting. On the other hand, the client is not aware of the meaning of the string 'BEGIN TRANSACTION'. The flow of control looks ...


1

Vertica query performance depends highly on the predicate used in the query . To get the gist of your performance , try getting the projection name of the selected columns of the query you are firing . The columns in the order by clause of the projection is very important in deciding the performance of your select. you can get that by running explain on ...


1

The problem in your query actually comes from the first row. It has the same Reason_type so it gives the extra ranking to the bottom records. I think you can use CONDITIONAL_CHANGE_EVENT, maybe like: select *, dense_rank() over (partition by site,change_num,reason_type order by billing_date) as rank from ( Select site, billing_date, ...


1

You shouldn't be surprised that a load takes longer when there's more resource usage. However, there's probably a simple explanation for your poor performance. I would examine the following: The INSERT with UNION ALLs is highly suspicious. Is the load more than 100MB? If yes, use COPY. Are the projections optimized for load? Is the query profiled and no ...


1

Without knowing your environment, there are a couple things you can try. The first being adding an additional filter to make the output file smaller. The other is using a pipe viewer to monitor the progress. If you're using the enterprise edition, you should report the issue to support.


1

Try option ABORT ON ERROR - will output a reason for rejection. COPY 'table name' FROM '/path/to/data.txt' DIRECT RECORD TERMINATOR ',' ABORT ON ERROR Check rejected and exception files or define it in COPY: COPY 'table name' FROM '/path/to/data.txt' DIRECT RECORD TERMINATOR ',' REJECTED DATA 'path' EXCEPTIONS 'path' https://community.vertica.com/...


1

From the cross-posted site: Vertica only works on Linux, and only some flavors of Linux are supported officially (can't support everything). Vertica's SQL is based on PostgreSQL. 1 Vertica instance runs over multiple nodes acting in parallel. All of them together constitute the cluster.


Only top voted, non community-wiki answers of a minimum length are eligible