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

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

Redshift Table Data Skew and How to avoid it

You will hear a lot about "Data Skew" if you are developing data warehouse on Redshift, Netezza, Teradata, hive or Impala database. In the MPP database, performance of the system is directly linked to uniform distribution of the user data across all data node in the system. When you create a table and then load the data into the system, the rows of the table should be distributed uniformly among all the data nodes. If some data node slices have more rows of a table than others, this scenarios is…

Continue ReadingRedshift Table Data Skew and How to avoid it
Comments Off on Redshift Table Data Skew and How to avoid it

Redshift CREATE, ALTER, DROP, RENAME Database Commands and Examples

You can use Redshift PostgreSQL to create, drop, rename and change the database owners. Below given some important commands that you should know if you are working as a Redshift database administrator. In this article, we will check Redshift create, drop, rename database commands and some of examples. Read: Redshift Create View Syntax and Examples Amazon Redshift Data Types and Best Practices Amazon Redshift Data Types and Best Practices Netezza Create Database Command You can use CREATE DATABASE command to create new database in Redshift cluster: Redshift CREATE DATABASE Syntax:…

Continue ReadingRedshift CREATE, ALTER, DROP, RENAME Database Commands and Examples
Comments Off on Redshift CREATE, ALTER, DROP, RENAME Database Commands and Examples

Redshift Fixed-Width File Loading Options and Examples

Fixed width text files are special cases of text files where the format is specified by column widths, pad character and left or right alignment. In this format column width are in terms of units of characters. In this article, we will learn about Redshift Fixed-Width file loading options and examples. Fixed-Width File Overview In general, fixed-length format files use ordinal positions, which are offsets to identify where fields are within the record of the file. Fixed-width data files have uniform lengths for each column of data. Each field in…

Continue ReadingRedshift Fixed-Width File Loading Options and Examples
Comments Off on Redshift Fixed-Width File Loading Options and Examples

Amazon Redshift Update Join Syntax – Update using other Table

In the database world, we use database tables (in fact tables) to store information. Data is collected over the specific period of time and it may or may not be accurate at the time of loading. In some cases, you may want to update the table based on the data available in other table over same or other database on same server. In this article, check Amazon Redshift Update Join Syntax and example on how to change table with data from other table. Amazon Redshift Update Join Table The process…

Continue ReadingAmazon Redshift Update Join Syntax – Update using other Table
Comments Off on Amazon Redshift Update Join Syntax – Update using other Table

Access Redshift using psql without Password Prompt – Use psql System Variables

Users can access Amazon Redshift database using a PostgreSQL- compatible psql client. Users can always connect to the Redshift database via leader node. In this article, we will see how to access redshift using psql without password prompt. You can use psql system variables to store required credentials. Access Redshift using psql without Password Prompt Password less access is useful when you are trying to execute Redshift queries from scripts such as shell or perl scripts. Option 1. Export Amazon Redshift Database Environmental Variables In order to access Redshift database…

Continue ReadingAccess Redshift using psql without Password Prompt – Use psql System Variables
Comments Off on Access Redshift using psql without Password Prompt – Use psql System Variables

Export Redshift Table Data to Local CSV format

You can export the Redshift table in many ways. Redshift export table is done using either UNLOAD command, COPY command or PostgreSQL command. Using UNLOAD or COPY command is fasted way to export Redshift table, but with those commands you can unload table to S3 bucket. You have to use the PostgreSQL or psql to export Redshift table to local CSV format. Export Redshift Table Data to Local CSV format To export Redshift table to local directory, you must install the PostgreSQL in your machine. You can use the psql…

Continue ReadingExport Redshift Table Data to Local CSV format
Comments Off on Export Redshift Table Data to Local CSV format

Redshift Set Operators: UNION, EXCEPT/MINUS and INTERSECT

You can use the Redshift set operators to combine similar data sets from two or more SELECT statements. Here the similar data set literally mean, the data type of the result set should also match, otherwise you have to explicitly type cast data when using Redshift set operators. Types of Redshift Set Operators Redshift supports the three types of set operators: UNION [DISTINCT] and UNION ALL INTERSECT [DISTINCT] EXCEPT [DISTINCT] or MINUS [DISTINCT] Redshift Set Operators Syntax Below is the syntax of Set operators in Redshift: query { UNION […

Continue ReadingRedshift Set Operators: UNION, EXCEPT/MINUS and INTERSECT
Comments Off on Redshift Set Operators: UNION, EXCEPT/MINUS and INTERSECT

Redshift Regular Expression Functions and Examples

The Redshift regular expression functions identify precise patterns of characters in the given string and are useful for extracting string from the data and validation of the existing data, for example, validate date, range checks, checks for characters, and extract specific characters from the data. The regular expression functions allow you to perform complex string manipulations within Redshift SQL statements, such as searching for and extracting specific patterns, validating data, and replacing values. In this article, we will be checking some commonly used Redshift regular expressions with examples. Read: Amazon…

Continue ReadingRedshift Regular Expression Functions and Examples
Comments Off on Redshift Regular Expression Functions and Examples