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…

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…

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…

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…

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…

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…

Comments Off on Vertica Set Operators: UNION, EXCEPT/MINUS and INTERSECT
April 25, 2019
Apache Spark

How to Update Spark DataFrame Column Values using Pyspark?

A dataFrame in Spark is a distributed collection of data, which is organized into named columns. You can compare Spark dataFrame with Pandas dataFrame, but the only difference is Spark dataFrames are immutable, i.e. You cannot change data from already created dataFrame. In this article, we will check how to update spark dataFrame column values using pyspark. The same concept will be applied to Scala as well. How to Update Spark DataFrame Column Values using Pyspark? The Spark dataFrame is one of the widely used features in Apache Spark. All…

Comments Off on How to Update Spark DataFrame Column Values using Pyspark?
April 19, 2019
Apache Spark

What is SQL Cursor Alternative in Spark SQL?

SQL Cursor is a database object to retrieve data from a result set one row at a time. You can also consider cursor as a temporary workspace created in database system memory when a SQL query is executed. SQL Cursor always returns one row at a time, you can perform your calculation on returned values. Cursors are usually written using SQL procedural language such as Oracle PL/SQL, Netezza NZPL/SQL. Sample SQL Cursor Example Below is the sample Oracle PL/SQL procedure with cursor defined: CREATE OR replace PROCEDURE Sample_proc IS str1…

Comments Off on What is SQL Cursor Alternative in Spark SQL?
April 15, 2019
Vertica

Vertica NULL Handling Functions: ISNULL, IFNULL, NULLIF, COALESCE, NVL, NVL2, ZEROIFNULL, NULLIFZERO

The NULL values are ‘unknown’ or unavailable values. You cannot treat these values like how you treat blank values. The data generation system may add NULL values if values are missing or unknown value. Vertica provides various NULL functions using those you can tell the database how to treat NULL values. In this article, we will check Vertica NULL handling functions usage with some examples. Vertica NULL Handling Functions As mentioned earlier, you can tell Vertica database on how to treat NULL values using NULL handling functions. Below is the…

Comments Off on Vertica NULL Handling Functions: ISNULL, IFNULL, NULLIF, COALESCE, NVL, NVL2, ZEROIFNULL, NULLIFZERO
April 9, 2019
Vertica

Vertica Sequence- How to Create and Use it?

A sequence in Vertica is named object in an individual Vertica database schema, which provides unique incremental values. In most of the cases, sequence in Vertica are used to generate unique numbers that can use used as a primary key or surrogate key. In this article, we will check what is Vertica sequence, how to create and use it, types of sequences and restriction on using sequences. Vertica Sequence Overview A sequence output value is an integer. You can use this integer values wherever you would use numeric values. You…

Comments Off on Vertica Sequence- How to Create and Use it?
April 8, 2019