Vertica Derived Table and Examples

In a data warehouse environment, there are many places where you need to derive tables to meet certain requirements such as calculating columns, renaming table columns etc. You can use derived tables in place of temporary tables. In this article, we will check Vertica derived tables and how to use them in SQL queries. Vertica Derived Table A derived table in Vertica is basically a sub-query which is always in the FROM clause of a SQL query Statements. The reason it is called a derived table is because it essentially functions as a table as…

Continue ReadingVertica Derived Table and Examples
Comments Off on Vertica Derived Table and Examples

Vertica Update Join Syntax – Update using another Table

In a data warehouse, application may have various data sources. All these data sources are stored in tables in the database. Data is collected over the time and it may or may not be accurate. In some cases, you may want to update the table values based on the data available in another table over same or other database on the same server. For example, in a case of slowly changing dimension type 2, you may want to update the dimension table using a stage table that we created to…

Continue ReadingVertica Update Join Syntax – Update using another Table
Comments Off on Vertica Update Join Syntax – Update using another Table

Vertica Dynamic SQL Support and Alternative

Vertica dynamic SQL lets SQL statements be defined and run at run time, i.e. you can build SQL queries based on the user input and execute them to provide required output. For examples, pass a  date value or session specific values to the SQL queries dynamically. Most of the modern day databases such as Netezz, Oracle, etc support dynamic SQL as a part of stored procedure or functions. Unfortunately, Vertica database does not provide support for dynamic SQL. Hopefully, future versions of Vertica database may provide support to the dynamic…

Continue ReadingVertica Dynamic SQL Support and Alternative
Comments Off on Vertica Dynamic SQL Support and Alternative

Vertica EXPLAIN command and its Usage

A query plan is a sequence of step-like statements that the Vertica cost-based query optimizer uses to execute queries. Vertica can produce different query plans for a given query. The Vertica cost-based optimizer uses plan to identify best methods for joining tables, identifying right segment to get required data, etc. You can use Vertica EXPLAIN command to display actual execution plan that Vertica generates and uses while executing any query on the analytical system. In this article, we will check Vertica query plan and how to generate it using Vertica EXPLAIN plan command. Vertica Cost-Based Optimizer…

Continue ReadingVertica EXPLAIN command and its Usage
Comments Off on Vertica EXPLAIN command and its Usage

Vertica Cumulative Sum, Average and Example

The cumulative sum or running total is one of the interesting problem. Most of the modern analytical database line Netezza, Teradata, Oracle, Vertica provides supports to analytical functions. You can make use of those analytical functions along with window specification to calculate cumulative sum and average. In this article, we will check how to calculate Vertica Cumulative Sum (running total) or cumulative average. Vertica Cumulative Sum As explained earlier, cumulative sum or a running total is the summation of a sequence of numbers which is updated each time a new…

Continue ReadingVertica Cumulative Sum, Average and Example
Comments Off on Vertica Cumulative Sum, Average and Example

Vertica SET ON_ERROR_STOP and Examples

As a part of the database table refresh, you might execute multiple SQL statements on the Vertica analytical system. SQL statements could be some standalone or multiple statements in the single SQL file. You should have a mechanism to stop the execution in case one of the SQL statement encountered error inside the script. In this article, we will check one of such mechanism. i.e. Vertica SET ON_ERROR_STOP with some examples on how to use that variable. Vertica SET ON_ERROR_STOP The Vertica vsql command with option -f executes all SQL…

Continue ReadingVertica SET ON_ERROR_STOP and Examples
Comments Off on Vertica SET ON_ERROR_STOP and Examples

Connecting Vertica using Python pyodbc – Working Example

There are many ways to connect to Vertica from Python. Most of the modern-day applications uses either odbc or jdbc drivers to connect to relational databases. In my other post, we have discussed how to connect to Vertica database using JDBC and Python modules. Other methods include, using vertica_python, vertica_db_client python modules.  The vertica_db_client is a Vertica provided Python connector. In this article, we will check method on connecting Vertica using Python pyodbc driver with a working example. Vertica ODBC Driver Before trying to connect Vertica from either windows or…

Continue ReadingConnecting Vertica using Python pyodbc – Working Example
Comments Off on Connecting Vertica using Python pyodbc – Working Example

How to Export Vertica Query Results into CSV?

The critical systems do not allow any users to access to decision critical tables. Instead, flat files are created and shared with the client for review. Saving results of a query or database table in any relational database is one of important work. You can then transport create flat file or CSV file using any mode of electronic transport such as email, FTP, SFTP, etc. In this article, we will check one of such methods to export Vertica query results into CSV flat file. How to Export Vertica Query Results…

Continue ReadingHow to Export Vertica Query Results into CSV?
Comments Off on How to Export Vertica Query Results into CSV?

Commonly used Vertica Date Functions and Examples

Date functions are very important when you are working with various sources. For example, extract day from the date type and store it in database tables. In this article, we will check commonly used Vertica date functions and some examples. Vertica date functions are similar to other PostgreSQL systems such as Netezza. There are some date functions that are native to Vertica database. Most of the real-world applications use date functions to manipulate date values. Date types are highly formatted and are very complicated. Each date function will work on…

Continue ReadingCommonly used Vertica Date Functions and Examples
Comments Off on Commonly used Vertica Date Functions and Examples

Vertica Set Operators: UNION, EXCEPT/MINUS and INTERSECT

The Vertica set operators are used to combine similar data sets from two or more SELECT statements. Here the similar data set literally means, the data type of the result set should also match, otherwise you have to explicitly use type conversion function for data columns when using set operators. The set operators in SQL are extensively used to combine results from various query sets. Based on your requirement, you can choose suitable Vertica set operators. Related Articles: Vertica WITH Clause, Syntax, Usage and ExamplesVertica Type Conversion Functions and Examples…

Continue ReadingVertica Set Operators: UNION, EXCEPT/MINUS and INTERSECT
Comments Off on Vertica Set Operators: UNION, EXCEPT/MINUS and INTERSECT