How to Update or Drop Hive Partition? Steps and Examples

In general, partitions in relational databases are used to increase the performance of the SQL queries. The partition is the concept of storing relevant data in the same place. For example, let us say you want to query the data monthly bases, then you can partition your data on month. In this article, we will check how to update or drop the Hive partition that you have already created. What are Partitions in Hive? Just like relational databases, Apache Hive partitions are used to improve the performance of the HiveQL…

Continue ReadingHow to Update or Drop Hive Partition? Steps and Examples
Comments Off on How to Update or Drop Hive Partition? Steps and Examples

Hive on Error Stop Script Execution – Options

When you build a data warehouse on top of Hadoop HDFS using Hive framework, you may have to execute HiveQL or SQL queries or HiveQL script containing a bunch of HiveQL statements. Hive and Beeline does provide option to execute a script file. There may be a scenario in which you may want to stop the script execution in case if any of the SQL statement fails. In this article, we will check stop script execution on error in Hive. We shall see both Hive and Beeline CLI options to exit script execution in case…

Continue ReadingHive on Error Stop Script Execution – Options
Comments Off on Hive on Error Stop Script Execution – Options

Hadoop Hive isnumeric Alternative and Examples

In a data warehouse environment, you will be working with heterogeneous data set. You may have to filter out unwanted data before loading it to the actual data into Hive table. For example, you many have a field1 of type string contains alphanumeric values. In many scenarios, you may get requirement to filter out non-numeric values. In this article, we will check Hadoop Hive isnumeric Alternative with some examples. Hadoop Hive isnumeric Function Many relational databases provide an extended SQL functions to help the data warehouse developers. Databases such as…

Continue ReadingHadoop Hive isnumeric Alternative and Examples
Comments Off on Hadoop Hive isnumeric Alternative and Examples

Hadoop Hive Transactional Table Update join and Example

As you know Apache Hive is a data warehouse framework on top of Hadoop HDFS. Since it contains tables, you may want to update records of that table based on how your data changes. Until recently Apache Hive was not supporting transactions. Starting Hive 0.14 and above supports transactional table. You need to enable ACID properties in order to use update, delete, merge in your Hive queries. In this article, we will address How to use update join on your Hive transactional table. You can also update Hive table without…

Continue ReadingHadoop Hive Transactional Table Update join and Example
Comments Off on Hadoop Hive Transactional Table Update join and Example

Apache Hive – Extract Value from JSON using Hive – Example

A JSON file is a file that stores simple data structures and objects in JavaScript Object Notation (JSON) format, which is a standard data interchange format. Json files are mainly used to transfer data in web applications. Many web applications use the json files for data transfer between application and servers. In this article, we will check how to extract or get value from json file using Hive queries. Extract Value from JSON using Hive Apache Hive provides limited support to JSON files. You can store json data into Hive…

Continue ReadingApache Hive – Extract Value from JSON using Hive – Example
Comments Off on Apache Hive – Extract Value from JSON using Hive – Example

Hive Array Functions, Usage and Examples

It is very common to store values in the form of an array in the databases. Later you can use array manipulation functions to manipulate the array types. In this article, we will check how to work with Hive array functions to manipulate array types. Hive Array Functions Below are some of the commonly used Hive array functions. Hive Array Function The very first most used function is array function. This function is used to create array out of integer or string values. Following is the syntax of array function.…

Continue ReadingHive Array Functions, Usage and Examples
Comments Off on Hive Array Functions, Usage and Examples

Apache Hive DUAL Table Support and Alternative

Apache Hive like many other relational databases does not support dual table. You can simply use the SELECT without FROM clause to display the results of the function or expression that you are testing. But, it may cause a problem when you are migrating from Oracle to Hive. You may find a lot of queries using dual tables. In this article, we will check what is the dual table alternative in Hive and how to use it. What is DUAL table in Relational Databases? In relation databases, the DUAL is…

Continue ReadingApache Hive DUAL Table Support and Alternative
Comments Off on Apache Hive DUAL Table Support and Alternative

What is Hive Lateral View and How to use it?

The best part of Apache Hive is it supports array types. i.e. you can store the array values in Hive table columns. With the help of an array, you can minimize the table rows by grouping together in the form of an array. In this article, we will check what is the Hive lateral view and how to use it with array values. You can use lateral view either with EXPLODE or INLINE function. What is Hive Lateral View? Before going in detail, let us check what is lateral view? In Hive, lateral view…

Continue ReadingWhat is Hive Lateral View and How to use it?
Comments Off on What is Hive Lateral View and How to use it?

Hive Insert into Partition Table and Examples

The Hive INSERT command is used to insert data into Hive table already created using CREATE TABLE command. Inserting data into partition table is a bit different compared to normal insert or relation database insert command. There are many ways that you can use to insert data into a partitioned table in Hive. In this article, we will check Hive insert into Partition table and some examples. Hive Insert into Partition Table As mentioned earlier, inserting data into a partitioned Hive table is quite different compared to relational databases. You…

Continue ReadingHive Insert into Partition Table and Examples
2 Comments

Apache Hive Type Conversion Functions and Examples

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

Continue ReadingApache Hive Type Conversion Functions and Examples
2 Comments