Vithal S, Author at DWgeek.com

Amazon Redshift Architecture and its Components

Amazon Redshift Architecture is a shared nothing MPP architecture. The architecture is similar to the other MPP data warehouse systems such as Netezza, MS PDW, Greenplum etc. Amazon Redshift is a result of database integration, processing CPU and storage in a system. Amazon Redshift architecture is depicted in below diagram: SQL Client applications There are many SQL client applications that you can use to connect and query data stored in Amazon Redshift database: SQL Workbench/J: It is a free, open-source SQL client application for Windows, MacOS and Linux. pgAdmin: provides a psql, another…

Comments Off

June 6, 2017

Redshift

Redshift Analytic Functions and Examples

Redshift analytic functions compute an aggregate value that is based on a group of rows in the tables. A Redshift PostgreSQL analytic function works on the group of rows and ignores the NULL in the data if you specify explicitly in the function. Redshift is basically a data warehouse analytics system and provides many useful functions that can perform day to day aggregations that save lot of times during the development. Usage of Redshift analytic function improves the performance of the query. Frequently used Redshift analytical functions are as follows:…

Comments Off

June 5, 2017

Netezza

Run Netezza SQL Script File using nzsql Variable Substitution

Netezza allows the variable substitution using -v option in Netezza nzsql queries. The variable substitution is very important when you are calling the Netezza nzsql 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 Netezza nzsql script file using nzsql variable substitution. We also see some of the working examples that uses the Netezza variable substitution method. Run Netezza SQL Script File using nzsql Variable Substitution You can set…

Comments Off

June 4, 2017

Netezza

Netezza Conversion Functions and Examples

Netezza supports various conversion functions. Below are some of commonly used Netezza conversion functions. These functions uses 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. Read: nzsql command and its Usage Netezza Analytics Functions and Examples Explicit and implicit Netezza Type Casting With Examples Netezza Data Functions and Examples How to Resolve Netezza SPU Swap Partition Error Netezza Cast Function You can convert any data type to other using Netezza cast…

Comments Off

June 3, 2017

BigData

Sqoop Command with Secure Password

Sqoop commands allows you to exchange the data between Hadoop and relational databases such as Netezza, Oracle etc. Sqoop required the password to connect to various databases and of course it has to be secured. In this article, we will discuss on various ways to execute the Sqoop Command with Secure Password. Read: Sqoop import Relational Database Table into HBase Table Import data using Sqoop Export data using Sqoop Sqoop Architecture – Mappers with No Reducers Sqoop Command with Secure Password Below are the some of the methods that we can…

Comments Off

June 3, 2017

Netezza

Netezza DECODE Function Syntax and Examples

Netezza supports the DECODE function and it is similar to DECODE function in other relational databases such as Oracle, SQL Server, MySQL, Redshift etc. You can use the Netezza DECODE function to implement if-then-else statement in Netezza nzsql. This command is a short-hand form of Netezza CASE function. Netezza DECODE Function Syntax DECODE (expr, expr_val1, ’expr_rep1’, expr_val2, ’expr_rep2’, expr_val3, ’expr_rep3’, ’else-expr’) ; Execution Flow: If 'expr' is equal to expr_val1 then ’expr_rep1’ is returned, if 'expr' is equal to 'expr_val2' then ’expr_rep2’ is return, if 'expr' is equal to 'expr_val3'…

Comments Off

June 3, 2017

Netezza

Netezza NVL and NVL2 Functions with Examples

Netezza supports NVL and NVL2 functions. Netezza NVL Function The NVL function replaces a NULL value with a replacement string that you provide in the function. Returns the first argument if it is not null, otherwise it returns the second argument The Netezza NVL function is equivalent to the Netezza SQL coalesce function. The Function will return the non-NULL value. Note that, You cannot replace the blank value using NVL function. Some databases supports this nut Netezza does not. Syntax: NVL (exp,replacement-exp); Returns 'exp' if not null otherwise 'replacement-exp' is…

2 Comments

June 3, 2017

General

Database ACID Properties and Explanation

ACID (Atomicity, Consistency, Isolation, Durability) is a set of database properties that makes sure that database transactions are processed reliably and completly. Relational databases makes sures that its transactions are completed propely as per ACID properties . Database ACID Properties Following sections explains about various database ACID properties: Atomicity In ACID properties, Atomicity requires that each and every transaction is fully completed and committed or nothing at all. If any one part of the current transaction fails, then the entire transaction fails, and the database state is left unchanged (databse…

Comments Off

June 3, 2017

Netezza

Netezza SQL Identifiers

A Netezza SQL identifier is the name of a Netezza database object such as a table, column, user, group, user-defined object, and database names. Netezza supports the SQL 1999 definition for naming identifiers, and they can be up to 128 bytes in length. Read: Netezza SQL command and its usage Netezza Create user command and its Usage IBM Netezza Alter user command and its Usage Netezza Create, Drop, Rename Database Commands and Examples Netezza SQL Identifiers There are two types of Netezza SQL identifiers: regular and delimited. Netezza SQL Regular…

Comments Off

June 2, 2017

Netezza

Netezza Create, Drop, Rename Database Commands and Examples

Using Netezza SQL, you can create, drop, rename and change the database owners. These are some important commands you should know if you are working as a Netezza database administrator. In this article, we will check Netezza create, drop, rename database commands and some of examples. Read: Netezza SQL Identifiers Netezza Create Database Command You can use CREATE DATABASE command to create new database in Netezza system: system(admin)=> CREATE DATABASE newdb; CREATE DATABASE Database names can be up to a maximum length of 128 bytes, system displays an error message if…

Comments Off

June 2, 2017