Cloudera Impala Extract Numbers using Regular Expressions

Impala supports various built in functions those you can use to extract the numbers from strings values. If you are working on various data sets then there is a possibility that you may get data which is corrupted or merged with other fields. You can separate that data using various string functions available in Impala. In this article, we will discuss about Impala extract numbers using regular expressions and examples. You can read about regular expression in my other post: Cloudera Impala Extract Function and Examples Cloudera Impala String Functions…

Continue ReadingCloudera Impala Extract Numbers using Regular Expressions
Comments Off on Cloudera Impala Extract Numbers using Regular Expressions

Cloudera Impala 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. The Impala CREATE VIEW statement allows you to create a shorthand abbreviation for a more complicated query. The base query can have tables, joins, column alias etc. In this article, we will check Cloudera Impala create view syntax and some examples. Just like views or table in other database, an Impala view contains rows and columns. The fields in a view are fields from one or…

Continue ReadingCloudera Impala Create View Syntax and Examples
Comments Off on Cloudera Impala Create View Syntax and Examples

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

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

Access HBase Tables from Impala working Examples

As you know Hadoop Hive or Impala does not properly support transaction data. HBase is best suited for the table which required lot of delete, update, insert etc. You may want to explore the data stored in the HBase table. This article, helps you to understand how to access HBase tables from Impala and we will check out process with an example. Read other article on loading HBase table from Hive: Loading HBase Table from Apache Hive Why you want to access the HBase tables from Impala? This is obvious…

Continue ReadingAccess HBase Tables from Impala working Examples
Comments Off on Access HBase Tables from Impala working Examples

Cloudera Impala Type Conversion Functions and Examples

Impala has some very strict rules regarding data types for function parameters that you provide while executing it. Impala type conversion functions are used to explicitly convert the required format. For example, Impala does not convert DOUBLE to FLOAT, INT to STRING etc. In my other post, we have discussed on Impala date functions and examples. In this article, we will check out Cloudera Impala type conversion functions with an examples. Read: Commonly used Cloudera Impala Date Functions and Examples Impala Type Conversion Functions These type conversion functions uses common…

Continue ReadingCloudera Impala Type Conversion Functions and Examples
Comments Off on Cloudera Impala Type Conversion Functions and Examples

Clouderal Impala SQL Join Types and Examples

Impala SQL Join is a clause that is used for combining specific fields from two or more tables based on the common columns. The joins in the Impala are similar to the SQL and Hive joins. Joins are used to combine rows from multiple tables. In this article, we will learn about different Impala SQL join types with examples. Different Impala Join Types Following are Different Hive Join Types INNER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN SEMI JOIN ANTI JOIN CROSS JOIN Below are the tables…

Continue ReadingClouderal Impala SQL Join Types and Examples
Comments Off on Clouderal Impala SQL Join Types and Examples

Commonly used Cloudera Impala Date Functions and Examples

This article is about short descriptions and examples of the commonly used Cloudera Impala date functions that you can use to manipulate date columns in Impala SQL. In the real word scenarios many application manipulate the date and time data types. Impala SQL supports most of the date and time functions that relational databases supports. Date types are highly formatted and very complicated. Each date value contains the century, year, month, day, hour, minute, and second. We shall see how to use the Impala date functions with an examples. Cloudera…

Continue ReadingCommonly used Cloudera Impala Date Functions and Examples
3 Comments

Impala or Hive Slowly Changing Dimension – SCD Type 2 Implementation

Slowly changing dimensions in Data warehouse are commonly known as SCD, usually captures the data that changes slowly but unpredictably, rather than regular bases. Slowly changing dimension type 2 is most popular method used in dimensional modelling to preserve historical data. Since Cloudera impala or Hadoop Hive does not support update statements, you have to implement the update using intermediate tables. In this article, we will check Cloudera Impala or Hive Slowly Changing Dimension - SCD Type 2 Implementation steps with an example. For demonstration purpose, lets take the example…

Continue ReadingImpala or Hive Slowly Changing Dimension – SCD Type 2 Implementation
2 Comments

Cloudera Impala Truncate Table Statement Examples

Cloudera Impala TRUNCATE TABLE statement removes all records from the table while keeping the table structure as it is. This statement is low overhead alternative for dropping and re-creating the tables. This statement is also low overhead compared to the INSERT OVERWRITE to replace the existing data from the HDFS directory before copying data. This is one of the features added in CDH 5.5 or higher. This statement helps when you are performing ELT/ELT operation cycles on Cloudera Impala where you have to empty the table after the data has…

Continue ReadingCloudera Impala Truncate Table Statement Examples
Comments Off on Cloudera Impala Truncate Table Statement Examples