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
Redshift

Run Redshift SQL Script File using psql Variable Substitution

PostgreSQL or psql allows the variable substitution using -v option in Redshift sql query files. The variable substitution is very important when you are calling the Redshift sql scripts from shell or any other scripting language. You can pass the values to query that you are calling using -v option. In this article, we will see how to run Redshift sql script file using psql variable substitution. We also see some of the working examples that uses the psql variable substitution method. Run Redshift SQL Script File using psql Variable…

Continue ReadingRun Redshift SQL Script File using psql Variable Substitution
2 Comments
December 14, 2017
Redshift

Amazon Redshift 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. A view can be created from a subset of rows or columns of another table, or many tables via a JOIN. Redshift uses the CREATE VIEW statement from PostgreSQL syntax to create View. In this article, we will check Redshift create view syntax and some examples on how to create views. Just like views or table  in other database, a Redshift view contains rows and columns.…

Continue ReadingAmazon Redshift Create View Syntax and Examples
Comments Off on Amazon Redshift Create View Syntax and Examples
December 14, 2017
Redshift

Amazon Redshift Hash Functions and Examples

You can use Amazon Redshift hash functions to encode the data by transforming the input into a hash code or hash values. If you use the hash function, no two inputs have same hash values. The hash algorithm are designed to minimize the collision (two inputs having same hash value). You can use the hash function to speed up the data record retrieval by using simple one way lookup. Hash code is used as an index into a hash table which has a pointer to data records. Redshift hash function…

Continue ReadingAmazon Redshift Hash Functions and Examples
2 Comments
December 12, 2017
Netezza

Netezza Stored Procedure Return Resultset and working Example

You can use the stored procedure to return the resultset in your applications. Typically, stored procedure returns a unique value, it can also return resultset in the form of tables. In this article, we will see Netezza Stored Procedure Return Resultset with an example. Netezza Stored Procedure Return Resultset Below are the steps you can follow to return resultset using stored procedure: Create table that you will be using as a REFTABLE. The table specified in the RETURNS value must exist at the time that the stored procedure is created,…

Continue ReadingNetezza Stored Procedure Return Resultset and working Example
Comments Off on Netezza Stored Procedure Return Resultset and working Example
December 12, 2017
Redshift

Redshift Type Conversion Functions and Examples

Redshift supports various type conversion functions. You can use Redshift type conversion functions to change data type of column values while using it in queries. These functions use common calling functions i.e. the first argument is the value to be formatted, and the second argument is a template that defines the output or input format. Below are some of commonly used Redshift type conversion functions and examples on usage. Redshift CAST Function You can do run-time conversions between compatible data types by using the CAST functions. This function is similar…

Continue ReadingRedshift Type Conversion Functions and Examples
Comments Off on Redshift Type Conversion Functions and Examples
December 8, 2017