Amazon Released PartiQL – An Open Source SQL Compatible Query language

Today, most of the business critical decisions are driven by data. As data grows, it is typically spread across a combination of relational databases, non-relational data stores, and data lakes. As per Harvard business review, on average, less than half of structured data is being used to make business critical decision. Structured data could be a data from relational databases. They do no have facilities to consider other data sources such as the local file system or NoSQL database data or data lakes. In this article, we will check newly…

Continue ReadingAmazon Released PartiQL – An Open Source SQL Compatible Query language
Comments Off on Amazon Released PartiQL – An Open Source SQL Compatible Query language

Best SQL Query Format Tools Online

Who does not like formatted SQL codes? They are easy to read, understand and follow the complex logic. Sometimes, when you export SQL code from the database, the code will become messy and hard to understand. In this article, we will check Best SQL query format tools available for free on the internet. Some of them provide an API so that you can integrate in your application. Best SQL Query Format Tools Before jumping on the tools introduction. let us understand why the SQL code formatting is important? Why do…

Continue ReadingBest SQL Query Format Tools Online
Comments Off on Best SQL Query Format Tools Online

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

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…

Continue ReadingWhat is SQL Cursor Alternative in Spark SQL?
Comments Off on What is SQL Cursor Alternative in Spark SQL?

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…

Continue ReadingVertica NULL Handling Functions: ISNULL, IFNULL, NULLIF, COALESCE, NVL, NVL2, ZEROIFNULL, NULLIFZERO
Comments Off on Vertica NULL Handling Functions: ISNULL, IFNULL, NULLIF, COALESCE, NVL, NVL2, ZEROIFNULL, NULLIFZERO

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…

Continue ReadingVertica Sequence- How to Create and Use it?
Comments Off on Vertica Sequence- How to Create and Use it?

Vertica Type Conversion Functions and Examples

Vertica analytics data warehouse system supports various types of conversion functions. These Vertica type conversion functions are commonly used to changed data type of values stores in database tables. In SQL, type conversion is sometimes called type casting as well. These type conversion functions use common calling functions i.e. the first argument is the value to be formatted which will be either expression or table column name, and the second argument is a template that defines the output or input format. Related articles: Vertica Regular Expression Functions and ExamplesVertica Set…

Continue ReadingVertica Type Conversion Functions and Examples
Comments Off on Vertica Type Conversion Functions and Examples

SQL SET Operator MINUS Alternative in Hive and Examples

The set operators in SQL are used to combine similar data set of two or more SELECT statements. Here similar data set literally means the number of columns and its data type should match, otherwise you must explicitly type cast the data types of the values in SELECT statements. Hive does support UNION and UNION ALL set operator, INTERSECT and MINUS are not supported as of now. In this article, we will check SQL set operator MINUS alternative in Hive with an example. SQL SET Operator MINUS Alternative in Hive…

Continue ReadingSQL SET Operator MINUS Alternative in Hive and Examples
Comments Off on SQL SET Operator MINUS Alternative in Hive and Examples