Hot answers tagged

12

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 ...


6

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

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

You have two options really: You can create a delimited file with your data, like the following: row1col1data,row1col2data,row1col3data,row1col4data row2col1data,row2col2data,row2col3data,row2col4data row3col1data,row3col2data,row3col3data,row3col4data You can then load that into Vertica using the COPY command and vsql. If you have database superuser ...


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 ...


3

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.)


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 ...


3

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.


3

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=...


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 ...


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

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

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), ...


1

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 ...


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.


1

I'm pretty sure leaf expression means something like "a?b:c". A discrete value from a conditional. But basically the overall message is saying you need to avoid null values. They can't partition on a null.


1

The namespace refers to a schema (select * from [schema].[tablename]). If you search the "SQL Reference guide" you will find the term used interchangeably, although, schema is used a lot more since its standard SQL.



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