Redshift ANALYZE Command to Collect Statistics and Best Practices

Redshift Analyze command is used to collect the statistics on the tables that query planner uses to create optimal query execution plan using Redshift Explain command. Analyze command obtain sample records from the tables, calculate and store the statistics in STL_ANALYZE table. You can generate statistics on entire tables or on subset of columns. You can specify comma-separated column list for analyze command. How Statistics are collected? Redshift collects statistics in various ways. Statistics are automatically collected for certain database operations. Collect statistics for entire table or subset of columns…

Continue ReadingRedshift ANALYZE Command to Collect Statistics and Best Practices
Comments Off on Redshift ANALYZE Command to Collect Statistics and Best Practices

Amazon Redshift VACUUM Command Syntax, Usage and Examples

Redshift VACUUM command is used to reclaim disk space and resorts the data within specified tables or within all tables in Redshift database. When you delete or update data from the table, Redshift logically deletes those records by marking it for delete. Vacuum command is used to reclaim disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations. This command also sorts the data within the tables when specified.  Read: Redshift ANALYZE Command to Collect Statistics and Best Practices Commonly used Redshift Date Functions…

Continue ReadingAmazon Redshift VACUUM Command Syntax, Usage and Examples
Comments Off on Amazon Redshift VACUUM Command Syntax, Usage and Examples

Steps to Connect to Redshift cluster using PostgreSQL – psql

PostgreSQL (psql) is interactive terminal, you can type the queries and get output on terminal. After you create an Amazon Redshift cluster, you can access it using a terminal-based front end from PostgreSQL, psql, to query the data in your Redshift database. Steps to Connect to Redshift cluster using PostgreSQL - psql Below are the steps you can follow to connect to Redshift cluster using PostgreSQL - psql: Install PostgreSQL on Ubuntu – psql If you are using Ubuntu, you can use apt-get to install PostgreSQL. Below is the command:…

Continue ReadingSteps to Connect to Redshift cluster using PostgreSQL – psql
2 Comments

Redshift Unique Key Constraint and Syntax

Redshift Unique key constraint is informational only; they are not enforced by Amazon Redshift. Amazon data warehouse appliance supports referential integrity constraints such as Redshift primary key, foreign key, and unique keys as part of SQL-92 standard requirement. You can create Unique key constraint while creating tables in Redshift database but it will not be enforced while loading Redshift tables. Redshift query planner uses these constraints to create better query execution plan. Read: Redshift Primary Key Constraint and SyntaxAmazon Redshift Foreign Key Constraint and Syntax Redshift Unique Key Constraint Syntax…

Continue ReadingRedshift Unique Key Constraint and Syntax
Comments Off on Redshift Unique Key Constraint and Syntax

Redshift Foreign Key Constraint and Syntax

Redshift foreign key constraint is informational only; they are not enforced by Amazon Redshift. Amazon data warehouse appliance supports referential integrity constraints such as Redshift primary key, foreign key, and unique keys as part of SQL-92 standard requirement. You can create foreign key constraint while creating tables in Redshift database but it will not be enforced while loading Redshift tables. Redshift query planner uses these constraints to create better query execution plan. Read: Amazon Redshift Primary Key Constraint and Syntax Redshift Unique Key Constraint and Syntax Redshift Foreign Key Constraint…

Continue ReadingRedshift Foreign Key Constraint and Syntax
Comments Off on Redshift Foreign Key Constraint and Syntax

Redshift Primary Key Constraint and Syntax

Redshift Primary key constraint is informational only; they are not enforced by Amazon Redshift. Amazon data warehouse appliance supports referential integrity constraints such as Redshift primary key, foreign key, and unique keys as part of SQL-92 standard requirement. You can create primary key constraint while creating tables in Redshift database but it will not be enforced while loading Redshift tables. Redshift query planner uses these constraints to create better query execution plan. If Primary key is set at the column level, it must be on a single column. If PRIMARY…

Continue ReadingRedshift Primary Key Constraint and Syntax
Comments Off on Redshift Primary Key Constraint and Syntax

Amazon Redshift Data Types and Best Practices

Data type is an attribute that specifies type of data of any object that is going to be stored in that particular column. Each column, variable and expression has related data type in SQL. However, different database offers the different data types for columns. Redshift data types are almost similar to what the traditional RDBMS supports. Amazon Redshift data types are similar to IBM Netezza data types. When you issue Redshift create table command each column in a database tables must have name and a data type associated with it.…

Continue ReadingAmazon Redshift Data Types and Best Practices
Comments Off on Amazon Redshift Data Types and Best Practices

Amazon Redshift WITH Clause Syntax, Usage and Examples

Redshift WITH Clause is an optional clause that always precedes SELECT clause in the query statements. WITH clause has a subquery that is defined as a temporary tables similar to View definition. Each subquery in the WITH clause specifies a table name, an optional list of column names, and a query expression that evaluates to a table (usually a SELECT statement). In SQL, WITH clause are commonly referred to as Common Table Expressions (CTE). A CTE or WITH clause is a syntactical sugar for a subquery. Where you can use…

Continue ReadingAmazon Redshift WITH Clause Syntax, Usage and Examples
Comments Off on Amazon Redshift WITH Clause Syntax, Usage and Examples

Netezza and Redshift Comparison – Netezza vs Redshift

If you are performing PoC to choose between the Netezza and Redshift, then the common question arises which one is better compared to other. In this article, we will check Netezza and Redshift Comparison - Netezza vs Redshift.  These two data warehouse appliances have their own advantages and disadvantages. We will try to compare the features and difference between Netezza and Redshift. Read: Hadoop and Netezza Comparison - Netezza vs Hadoop Netezza Data Warehouse Appliance Features Read: Netezza TwinFin Architecture Following are some of the feature on Netezza data warehouse appliance:…

Continue ReadingNetezza and Redshift Comparison – Netezza vs Redshift
3 Comments

Commonly used Redshift Date Functions and Examples

Redshift is a data warehousing solution provided by Amazon Web Services (AWS). It supports a variety of date and time functions that can be used to perform operations on date and time data stored in Redshift tables. This article is about detailed descriptions and examples of the commonly used Amazon Redshift date functions that you can use to manipulate date data types in Resdshift SQL. In the real word scenarios many application manipulate the date and time data types. Date types are highly formatted and very complicated. Each date value…

Continue ReadingCommonly used Redshift Date Functions and Examples
Comments Off on Commonly used Redshift Date Functions and Examples