BigData

Cloudera Impala Extract Numbers using Regular Expressions

Impala supports various built in functions those you can use to extract the numbers from strings values. If you are working on various data sets then there is a possibility that you may get data which is corrupted or merged with other fields. You can separate that data using various string functions available in Impala. In this article, we will discuss about Impala extract numbers using regular expressions and examples. You can read about regular expression in my other post: Cloudera Impala Extract Function and Examples Cloudera Impala String Functions…

Continue ReadingCloudera Impala Extract Numbers using Regular Expressions
Comments Off on Cloudera Impala Extract Numbers using Regular Expressions
January 1, 2018
BigData

Cloudera Impala Create View Syntax and Examples

A View creates a pseudo-table or virtual table. It appears exactly as a regular table, you can use it in SELECT statements, JOINs etc. The Impala CREATE VIEW statement allows you to create a shorthand abbreviation for a more complicated query. The base query can have tables, joins, column alias etc. In this article, we will check Cloudera Impala create view syntax and some examples. Just like views or table in other database, an Impala view contains rows and columns. The fields in a view are fields from one or…

Continue ReadingCloudera Impala Create View Syntax and Examples
Comments Off on Cloudera Impala Create View Syntax and Examples
December 24, 2017
Redshift

Different Redshift Join Types and Examples

SQL Join is a clause that is used for combining specific fields from two or more tables based on the common columns available. Joins are used to combine rows from multiple tables. In this article, we will learn about different Redshift join types with some examples. Below are the tables that we will be using to demonstrate different Join types available in Redshift: Students: Id   name   city 1    AAA    London 2    BBB    Mumbai 3    CCC    Bangalore 4    DDD    Mumbai 5    EEE     Bangalore Dept: Did  name    sid 100  CS       1 101 …

Continue ReadingDifferent Redshift Join Types and Examples
2 Comments
December 21, 2017
Redshift

Amazon Redshift Extract Numbers using Regular Expressions

Amazon Redshift supports various built in functions, you can use them to extract the numbers, alphanumeric or specific patters from strings. In this article, we will discuss about Redshift extract numbers using regular expressions and examples. In data warehouse environment, you may have different types data files extracted from different data sources. Data might be corrupted or may have unwanted characters, you can clean such a data using Redshift regular expressions. You can read about regular expression in my other post: Redshift Regular Expression Functions and Examples Redshift Extract Numbers…

Continue ReadingAmazon Redshift Extract Numbers using Regular Expressions
Comments Off on Amazon Redshift Extract Numbers using Regular Expressions
December 19, 2017
Redshift

Redshift NVL and NVL2 Functions with Examples

Like many other relational database or data warehouse appliances, Redshift supports NVL and NVL2 functions. These functions are mainly used to handle the null values in Redshift tables. Redshift NVL Function An NVL expression returns the value of the first expression in the list that is not null. The NVL function replaces a NULL value with a replacement string that you provide in the function as argument. This function returns the first argument if it is not null, otherwise the second argument. The Redshift NVL function is equivalent to the…

Continue ReadingRedshift NVL and NVL2 Functions with Examples
Comments Off on Redshift NVL and NVL2 Functions with Examples
December 19, 2017
Redshift

How Redshift Distributes Table Data? Importance of right Distribution Key

Amazon Redshift uses the Massively parallel processing technique, Redshift automatically distributes data and query load across all nodes available in the cluster. In this article, we will check how Redshift distributes table data and importance of right distribution key. How Redshift distributes Table Data? Amazon Redshift uses the three types of distribution; EVEN, KEY and ALL. When you create tables you will have to tell the system which distribution it should use. You may read about distribution types and best practices: Amazon Redshift Distribution Types and Examples If you specify…

Continue ReadingHow Redshift Distributes Table Data? Importance of right Distribution Key
Comments Off on How Redshift Distributes Table Data? Importance of right Distribution Key
December 16, 2017
Redshift

Redshift ROWNUM Pseudo Column Alternative

There is no ROWNUM pseudo column in Redshift. If you are coming from Oracle database background, you will find it difficult in Redshift without ROWNUM pseudo column. The one possible solution to this is ROW_NUMBER() analytical function as Redshift ROWNUM pseudo column alternative. ROWNUM is sometime useful when you are working with multi-level SQL queries. You can restrict the rows using ROW_NUMBER functions. Read: Redshift String Functions and Examples Amazon Redshift Date Functions and Examples Redshift Analytics Functions and Examples Redshift also support a LIMIT clause to restrict the output.…

Continue ReadingRedshift ROWNUM Pseudo Column Alternative
1 Comment
December 15, 2017
Redshift

Amazon Redshift Date Format Conversion and Examples

Date data type is one of the complicated type is database. Date data types are used to store the date and time fraction values. Amazon Redshift Date format includes four data types, and are used to store the date with time details: DATE: for year, month, day storage. TIME: for hour, minute, second, fraction with (includes 6 decimal positions). TIMESTAMPTZ: same as TIME, also includes time zone information. TIMESTAMP: for year, month, day, hour, minute, second, fraction (includes 6 decimal positions). Amazon Redshift Date Format Conversion Redshift can convert quoted…

Continue ReadingAmazon Redshift Date Format Conversion and Examples
Comments Off on Amazon Redshift Date Format Conversion and Examples
December 15, 2017
BigData

Apache Hive ROWNUM Pseudo Column Equivalent

Hive is batch processing engine, you cannot use it as a transaction system. Sometimes you may need to generate sequence row number for document use. ROWNUM is sometime useful when you are working with multi-level SQL queries. There is no ROWNUM pseudo column in Apache Hive. In this article, we will check Hive ROWNUM pseudo column equivalent. If you are coming from Oracle or traditional database background, you will find it difficult in Hive without ROWNUM pseudo column. The one possible solution to this is ROW_NUMBER() analytical function as Hive…

Continue ReadingApache Hive ROWNUM Pseudo Column Equivalent
Comments Off on Apache Hive ROWNUM Pseudo Column Equivalent
December 15, 2017
Redshift

Redshift SET ON_ERROR_STOP using psql and Examples

On Redshift data warehouse appliance, you will be executing multiple SQL statement in single SQL file. In this article, we will discuss about the Redshift SET ON_ERROR_STOP variable to stop the execution in case one of the sql statement encountered error inside SQL script file. Without setting this system variable, Amazon Redshitf will continue with other statements ignoring the failed query. PostgreSQL (psql) EXIT Status psql returns following EXIT status when you are executing Redshfit SQL from files: 0: to the shell if it finished sql execution normally. 1: if…

Continue ReadingRedshift SET ON_ERROR_STOP using psql and Examples
Comments Off on Redshift SET ON_ERROR_STOP using psql and Examples
December 14, 2017