Tag: hive

Categories
hive

drop partition in hive

How to drop partition in hive

In this post let us learn how to drop partition in hive using examples

Partition in hive

Hive table can have one or multiple partition keys by which the query performance has been improved. By including the partition keys in our query , we can actually achieve the query optimization.

External and internal table in hive

Two types of tables available in hive are external and internal

For external tables , table properties need to be changed before dropping partitions .

alter table employee set tblproperties ('EXTERNAL'='FALSE');

Drop partition in hive

We can drop the respective partition of the external table using the following command .

ALTER TABLE employee set tblproperties ('EXTERNAL'='FALSE');
ALTER TABLE employee drop PARTITION (state='Bihar');
#After dropping the partition table properties should be reverted to external
ALTER TABLE employee set tblproperties ('EXTERNAL'='TRUE');

Resetting the location

We can even set the partition to the new location using the following command .  

ALTER TABLE employee PARTITION(state = 'Bihar', dep = 'accounts') 
SET LOCATION 'hdfs://retail/company/employee/accounts/Bihar

Dropping the range of partition in hive

For dropping range of data , we can try like below. 

ALTER TABLE employee DROP PARTITION (entry_date>'2021-03-14',entry_date<'2021-12-16');

Dropping all the partitions

Following one drop all the partitions

ALTER TABLE employee DROP if exists (state<>'');

Reference

https://cwiki.apache.org/confluence/display/hive/languagemanual+ddl#LanguageManualDDL-DropPartitions

Categories
hive

How to create external table in hive

How to drop partition in hive

In this post let us learn how to create external table in hive

Types of table in hive

The following are the two types of tables in the hive.

  1. Internal table or Managed table
  2. External table

For getting familiar with internal tables, Please refer to the below link.

https://beginnersbug.com/how-to-create-a-table-in-hive/  

What is External table ?

For creating an external table, we need to use one keyword called external.

The remaining syntax will remain the same for both types.

How to create ?

Following is the syntax for creating an external table.

Fields terminated by  ‘,’ – need to specify the delimiter of the file to be loaded into the table.

hive> create external table temp_details (year string,temp int,place string) 
> row format delimited 
> fields terminated by ','; 
OK Time taken: 0.093 seconds

To get familiar with loading the table, Please refer to the following link.

https://beginnersbug.com/how-to-load-data-into-a-hive-table/

Categories
hive

How to load data into a hive table

How to drop partition in hive

In this post , we will learn how to load data into a hive table .

Types of table in hive

The following are the two types of tables in the hive .

  1. Internal table or Managed table
  2. External table

In this post, let us discuss the internal tables and their loading ways.

What are the ways to load data into a table ?

There are three ways to load data into a hive table.

  • Loading data from a file 
  • Inserting values into a table 
  • Loading data from some other table 
1 . Loading data from a file

We can refer to the path of the file as below.  More importantly, we need to specify the details of the file like delimiter while creating the table itself.

Please refer to the below link to understand it clearly.

https://beginnersbug.com/how-to-create-a-table-in-hive/

hive> load data local inpath '/tmp/temp.csv' into table truck;
Loading data to table vehicle_details.truck
OK
Time taken: 0.846 seconds
hive> select * from truck limit 5;
OK
1901    50      bangalore
1903    48      kolkata
1900    26      pune
1902    12      darjeling
1903    23      delhi
Time taken: 0.954 seconds, Fetched: 5 row(s)
2 . Inserting values into a table 

We shall insert values into the table manually like below.

We can even add multiple records into the table in a similar way.

hive> insert into table truck1 values ('2020',65,'Chennai');

Let us verify whether the inserted data looks good.

hive> select * from truck1 where year='2020';
OK
2020    65      Chennai
Time taken: 0.161 seconds, Fetched: 1 row(s)
3 . Loading data from some other table 

We can create one more table with the following command.

hive> create table if not exists truck1
    > (year string , temp int , place string)
    > row format delimited
    > fields terminated by ','
    > ;
OK
Time taken: 0.099 seconds

As below, we can select the data from the existing table and load it into the new table.

hive> insert into table truck1 select * from truck where temp < 50;

After loading, we could find the new table loaded with the selected data.

hive> select * from truck1 limit 5;
OK
1903    48      kolkata
1900    26      pune
1902    12      darjeling
1903    23      delhi
1902    25      delhi
Time taken: 0.145 seconds, Fetched: 5 row(s)
Categories
hive

How to create a table in hive

how to create a table in hive

In this post, we will learn how to create a table in hive .

Creating a database in hive

In Hive, we either have to use an existing database or to create a new database before creating a table.

The following show command lists the number of available databases in the hive.

hive> show databases; 
OK 
default Time taken: 0.009 seconds, Fetched: 1 row(s)

The create database command helps us to create a new database as shown below.

hive> create database vehicle_details;
OK
Time taken: 0.208 seconds 
hive> show databases;
OK
default
vehicle_details 
Time taken: 0.011 seconds, Fetched: 2 row(s)
Creating table

The create syntax contains the following options.

if not exists(optional) – If any table exists already, it will ignore the statement.

other options – The specifications of the file to be loaded with the table. 

tblproperties – can skip the header in the file with this property.

hive> create table if not exists truck 
    > (year string , temp int , place string )
    > row format delimited
    > fields terminated by ','
    > stored as textfile
    > tblproperties ("skip.header.line.count"="1");
OK
Time taken: 0.278 seconds
hive> show tables; 
OK 
deliveries 
matches 
truck 
Time taken: 0.012 seconds, Fetched: 3 row(s)
How the file looks like
cat /tmp/temp.csv |head -6
year|temp|place
1900,45,chennai 
1901,50,bangalore 
1903,48,kolkata 
1900,26,pune 
1902,12,darjeling
Reference

https://cwiki.apache.org/confluence/display/Hive/Tutorial#Tutorial-Creating,Showing,Altering,andDroppingTables