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…

Continue ReadingCommonly used Cloudera Impala String Functions and Examples
Comments Off on Commonly used Cloudera Impala String Functions and Examples
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…

Continue ReadingCloudera Impala Regular Expression Functions and Examples
Comments Off on Cloudera Impala Regular Expression Functions and Examples
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…

Continue ReadingHadoop Hive Regular Expression Functions and Examples
Comments Off on Hadoop Hive Regular Expression Functions and Examples
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…

Continue ReadingRedshift Table Data Skew and How to avoid it
Comments Off on Redshift Table Data Skew and How to avoid it
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:…

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

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
December 2, 2017
Redshift

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
November 30, 2017
Redshift

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
November 30, 2017
Netezza

How to Identify Netezza Server I/O Usage using System Views?

You may have to query Netezza system tables or views to get information about system, for example, to identify Netezza server I/O Usage. These kind of information will help you to manage Netezza server and databases. As a server maintenance activity, you may have to regularly to identify Netezza Server I/O and monitor regularly to minimize the performance degrade. Identify Netezza Server I/O Usage using System Views Below query provides the Netezza I/O usage i.e. identify read and write details on Netezza disks. select cast(START_TIME as date) as startDate, cast(END_TIME…

Continue ReadingHow to Identify Netezza Server I/O Usage using System Views?
Comments Off on How to Identify Netezza Server I/O Usage using System Views?
November 24, 2017
Netezza

How to List Netezza Database High Volume Tables?

You may have to query Netezza system tables or views to get information about system, for example, list Netezza database high volume tables. These kind of information will help you to manage Netezza server and databases. As a server maintenance activity, you may have to regularly verify the Netezza database table size and identify the growing tables in server to minimize the performance degrade. How to List Netezza Database High Volume Tables? Below query provides the information of big or high volume tables of database available in the Netezza data…

Continue ReadingHow to List Netezza Database High Volume Tables?
Comments Off on How to List Netezza Database High Volume Tables?
November 24, 2017