Table and Projection are physical or logical?

Table:
    Tables do not occupy any physical storage at all in Vertica.

Projection:
    Physical storage consists of collections of table columns called projections.

What is Projection?

Optimized collections of table columns that provide physical storage for data. A projection can contain some or all of the columns of one or more tables. A projection that contains all of the columns of a table is called a super-projection. A projection that joins one or more tables is called a pre-join projection.

Vertica Interview Questions

1. What is WOS in Vertica?
2. What is ROS in Vertica?
3. What is Tuple Mover (TM) in Vertica? 
4. What is Projection?
5. Table and Projection are physical or logical?
6. What is K-Safe in Vertica?
7. What is Hash-Segmentation and Unsegmented?
8. What is DBD in Vertica?
9. What ANALYZE_STATISTICS will do?
10. What is Copy Command in Vertica?
11. Howto monitor Copy Command?
12. What is Mergeout in Vertica?
13. What is Encoding and Compression in Vertica?
14. How to find currently running sessions?
15. How to Close/Kill currently running session?

See also:

DB User:

• User Creation
• GRANT USAGE ON SCHEMA PUBLIC
• Create Schema
• Grant usage privileges on a schema to the user
• Grant all privileges on a schema to the user
• Set default search path
• Change Vertica User Privileges from Existing User to New user
• Execute Permissions on User Defined Functions For New Users
• Grant Execute Permissions on ODBC

Vertica Table

• Creation
• Rename Table, Table Column Or View in Vertica
• Projection Creation
• Create A New Table Using Existing Table
• Create Table Partitions
• Change the Owner of Table
• Drop Partition

Sequence:

• Create Sequence Number
• Alter Sequence
• Drop Sequence
• Add Sequence Or Set Default value to a table
• Drop Default Or Sequence on a Table
• Change the Owner of Sequence

Vertica Sessions

• Close Session in Vertica
• Currently Running Sessions in Vertica

File Loading Issue using copy cmd in Vertica ERROR 2344:

       ERROR 2344: Cannot allocate sufficient memory for COPY statement (536870912 requested, 268435456 permitted)

   The above error occurred  while loading the file data into Vertica DB using copy command (even after putting rejected data file).


Solution:

• First check the Source file type.
• Open the file in "Notepad + +". In the bottom side we can find the file type (eg: Dos\Windows)

  

•  Ideally the file type should be "Dos\Windows".
• If the file type is different the we need to convert it.

File conversion using "Notepad + +":

• Open the file in "Notepad + +".
• Go to  "Edit" and select "EOL Conversion" then select the required format.

• Save the file.

How to copy file data into Vertica database

   Using Copy command we can copy file data into Vertica database.

Syntax:
COPY <Table Name> FROM LOCAL '<File Path>' DIRECT DELIMITER '<Delimiter>' ENCLOSED BY '"';

Example:
COPY Sample_Table FROM LOCAL '/DataFiles/Sample.txt' DIRECT DELIMITER '|' ENCLOSED BY '"';

isnumeric function

isnumeric function is not a built in function in Vertica, but we can create it.

CREATE FUNCTION ISNUMERIC(X VARCHAR) RETURN VARCHAR
AS
BEGIN
RETURN REGEXP_COUNT(X, '^[0-9.-]+$');

HP Vertica

DB User:

• User Creation
• GRANT USAGE ON SCHEMA PUBLIC
• Create Schema
• Grant usage privileges on a schema to the user
• Grant all privileges on a schema to the user
• Set default search path
• Change Vertica User Privileges from Existing User to New user
• Execute Permissions on User Defined Functions For New Users
• Grant Execute Permissions on ODBC

Vertica Table

• Creation
• Rename Table, Table Column Or View in Vertica
• Projection Creation
• Create A New Table Using Existing Table
• Create Table Partitions
• Change the Owner of Table
• Drop Partition

Sequence:

• Create Sequence Number
• Alter Sequence
• Drop Sequence
• Add Sequence Or Set Default value to a table
• Drop Default Or Sequence on a Table
• Change the Owner of Sequence

Vertica Sessions

• Close Session in Vertica
• Currently Running Sessions in Vertica