Greenplum

How to Export Greenplum Query Results into CSV?

In a data warehouse environment, most of the tables are secured. The database administrator will allow only authorized used to access records from the tables. The decision makers analyze the flat files created from the database tables to come up with business-critical decision. Saving results of a query or a database table in any relational database is one of the basic and important work. The created flat files or CSV files then be transported using any mode of electronic transport such as email, FTP, SFTP, etc. in this article, we…

Continue ReadingHow to Export Greenplum Query Results into CSV?
Comments Off on How to Export Greenplum Query Results into CSV?
May 7, 2019
Greenplum

Greenplum Explain Command and its Usage

A query plan is a sequence of step-like statements containing nodes that the Greenplum cost-based query optimizer uses to execute queries. Based on the complexity of the supplied SQL query, Greenplum planner generates different plan. In this article, we will check Greenplum Explain Command and its usage with some examples. Greenplum Explain Command The Greenplum EXPLAIN displays the query plan that the Greenplum planner generates for the supplied SQL query statement. These query plans are usually a tree plan of nodes. Each node in the plan represents a single operation, such as table scan, join,…

Continue ReadingGreenplum Explain Command and its Usage
Comments Off on Greenplum Explain Command and its Usage
May 6, 2019
Vertica

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
May 4, 2019
Vertica

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
May 3, 2019
Vertica

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
May 2, 2019
Vertica

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
May 1, 2019
Vertica

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?
April 30, 2019
Vertica

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
April 29, 2019
Greenplum

How to Connect Greenplum using JDBC Driver from Python?

The Greenplum database is one of the widely used MPP machine. There are many methods that you can use to connect to Greenplum, for example, you can use JDBC, ODBC, etc. The Greenplum analytics database support connection from any programming language that supports JDBC driver. In this article, we will check how to connect Greenplum using JDBC driver from Python programming language. Connection string will remain same if you are using any programming language other than Python. Greenplum JDBC Driver The Greenplum database provides support to JDBC driver. You can…

Continue ReadingHow to Connect Greenplum using JDBC Driver from Python?
Comments Off on How to Connect Greenplum using JDBC Driver from Python?
April 26, 2019
Vertica

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
April 25, 2019