DWgeek.com

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…

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.…

Comments Off

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…

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,…

Comments Off

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…

Comments Off

December 8, 2017

BigData

Commonly used Cloudera Impala String Functions and Examples

In this article, we will discuss on the various Cloudera Impala string functions and usage. The Impala SQL string functions are similar to the SQL string functions. Cloudera Impala String Functions The commonly used string functions in Cloudera Impala are listed below: Impala String Functions Descriptions ascii(string str) Returns the numeric ASCII code of the first character of the argument. btrim(string a) btrim(string a, string chars_to_trim) Removes all instances of one or more characters from the start and end of a STRING value. Optionally, you can provide characters to be…

Comments Off

December 7, 2017

BigData

Cloudera Impala Regular Expression Functions and Examples

The Cloudera Impala 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. In this article, we will be checking some commonly used Cloudera Impala regular expression functions with an examples. Types of Cloudera Impala Regular Expression Functions As of now, Cloudera Impala supports only three regular expression functions: regexp_extract regexp_like regexp_replace Impala regexp_extract Function The Impala…

Comments Off

December 6, 2017

BigData

Hadoop Hive Regular Expression Functions and Examples

The Hadoop Hive 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. In this article, we will be checking some commonly used Hadoop Hive regular expressions with an examples. Types of Hadoop Hive regular expression functions As of now, Hive supports only two regular expression functions: REGEXP_REPLACE REGEXP_EXTRACT Hive REGEXP_REPLACE Function Searches a string for a…

Comments Off

December 6, 2017

Redshift

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…

Comments Off

December 5, 2017

Redshift

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:…

Comments Off

December 5, 2017