Partitioning and Bucketing in Hive

-

Partitioning and Bucketing in Hive:

These two techniques are query optimization techniques.

Partitioning: 

·       It is the way to organize the data in partitions by dividing the table into different parts  based on partition keys.

·       If there are repeated values in the any field or column then partition technique can be applied.

·       Each table in hive can have one or more partitions based on column or partition key.

·       Packages to be imported for partitoning

sethive.exec.dynamic.partition.mode=nonstrict

sethive.exec.dynamic.partition= true

Bucketing:

·       It is the technique  which is used for segregating hive tables into  multiple files.

·       The divisionof hive tables or partitions  isbased on hash function of field which is present in table.

·       Package for bucketing

sethive.enforce.bucketing=true;

 

Partitioned Using Bucketking

create table txnByCat(txnno INT, txndate STRING, custno INT, amount DOUBLE,

product STRING, city STRING, state STRING, spendby STRING)

partitioned by (category STRING)

clustered by (state) INTO 10 buckets

row format delimited

fields terminated by ','

stored as textfile;

set hive.exec.dynamic.partition.mode=nonstrict;

set hive.exec.dynamic.partition=true;

set hive.enforce.bucketing=true;

from txn909 txn INSERT OVERWRITE TABLE txnByCat PARTITION(category) select txn.txnno,txn.txndate,txn.custno,txn.amount,txn.product,txn.city,txn.state,txn.spendby, txn.category DISTRIBUTE BY category;

·       SELECT txnno, product, state FROM  txnsgusto12 TABLESAMPLE(BUCKET 5 OUT OF 10)

·       SELECT txnno, product,state,category FROM txnsgusto12 TABLESAMPLE(BUCKET 4 OUT OF 5) LIMIT 10;

 

CUSTOMER TABLE (CUSTS)

CREATE EXTERNAL TABLE IF NOT EXISTS custsrecord(cusId INT, name1 STRING, name2 STRING,age INT)

PARTITIONED BY(profession STRING)

CLUSTERED BY (age) INTO 5 BUCKETS

row  format delimited

fields terminated by ','

stored as textfile location '/user/hive/warehouse/gusto/customer/';

 

 

DYNAMIC PARTITIONING

===============================================

 

create table txnBUC(txnno INT, txndate STRING, custno INT, amount DOUBLE,

product STRING, city STRING, state STRING, spendby STRING)

partitioned by (category STRING)

clustered by (state) INTO 10 buckets

row format delimited

fields terminated by ','

stored as textfile;

 

 

set hive.exec.dynamic.partition.mode=nonstrict;

set hive.exec.dynamic.partition=true;

set hive.enforce.bucketing=true;

 

 

 

 

Set hive.exec.mode.local.auto= true;

   (Specially For Partition)

 

 

from txn909 txn INSERT OVERWRITE TABLE txnBUC PARTITION(category) select txn.txnno,txn.txndate,txn.custno,txn.amount,txn.product,txn.city,txn.state,txn.spendby, txn.category DISTRIBUTE BY category;

 

 

 

 

 

 

 

 

CREATE EXTERNAL TABLE IF NOT EXISTS custsrecord(cusId INT, name1 STRING, name2 STRING,age INT)

PARTITIONED BY(profession STRING)

CLUSTERED BY (age) INTO 5 BUCKETS

row  format delimited

fields terminated by ',';

 

from txn909 txn INSERT OVERWRITE TABLE txnBUC PARTITION(category) select txn.txnno,txn.txndate,txn.custno,txn.amount,txn.product,txn.city,txn.state,txn.spendby, txn.category DISTRIBUTE BY category;

 

from customer cus INSERT OVERWRITE TABLE custsrecord PARTITION(profession) select cus.cusId,cus.name1,cus.name2,cus.age, cus.profession DISTRIBUTE BY profession;

 

from txnsgusto1 tINSERT OVERWRITE TABLE txnsgusto12PARTITION(category)SELECT t.txnno, t.txndate, t.custno, t.amount, t.product, t.city, t.state, t.spendby, t.category DISTRIBUTED BY category;

 

 

 

 

 

CREATE EXTERNAL TABLE IF NOT EXISTS CUST9(custno STRING, firstname STRING, lastname STRING,age INT)

PARTITIONED BY(profession STRING)

CLUSTERED BY (age) INTO 5 BUCKETS

row  format delimited

fields terminated by ','

STORED AS textfile;

 

 

 

from customer cus INSERT OVERWRITE TABLE CUST9 PARTITION(profession) select cus.custno,cus.firstname,cus.lastname,cus.age, cus.profession DISTRIBUTE BY profession;

 

 

 

 

 

custno                  string

firstname               string

lastname                string

age                     int

profession              string

 

 

txnBUC (t)           CUST9(c)

 

 

SELECT t.state, t.category, t.txnno, c.firstname, c.age FROM txnBUC t JOIN CUST9 c on t.custno= c.custno LIMIT 15;

 

SELECT t.product, t.category, t.custno, c.firstname,c.custno, c.profession FROM txnBUC t  LEFT OUTER JOIN CUST9 c on t.custno = c.custno LIMIT 10;

 

 

set hive.auto.convert.join = false;


Comments

Post a Comment

Popular posts from this blog

Problem Statement Of Real Estate Use Cases

-
Hive Use Case – Real Estate Analysis   Due to an industry, real estate activity is outlined as any economic dealings associated with the acquisition, sale, owner-operation or lease of property. This in addition includes income-generating residential properties, like flat, buildings and single-room rentals. Real estate services are not enclosed within the sector. Also, samples of real estate services embrace brokerages, property management, appraisers, investment property analysts and different consultants. All analysts, working with Big Data are using Hive or some other tool to query dataset and get results with ease. Although other querying languages exists, Hive gives us a variety of new features when compared to traditional approaches. So,On demand of accelerating consumers in real estate field, filtered series of information was collected and handed over to data analyst team. Load data into Hive. Problem Statement:           City wise list all the Villa which is not
Read more

Problem Statement Of Bank Marketing analysis

-
Analyze Marketing data for call campaign by bank Try yourself! Your client- a Portuguese banking institution—ran a marketing campaign to convince potential customers to invest in bank term deposit. The data is related to direct marketing campaigns of the bank. The marketing campaigns were based on phone calls. Often, more than one contact by phone to the same client was required, in order to assess if the product (bank term deposit) would be ('yes') or not ('no') subscribed: Data Fields age;"job";"marital";"education";"default";"balance";"housing";"loan";"contact";"day";"month";"duration";"campaign";"pdays";"previous";"poutcome";"y" 1 - age (numeric) 2 - job : type of job (categorical: 'admin.','blue-collar','entrepreneur','housemaid','management','retired
Read more

Hadoop

-
Image
“90% of the world’s data was generated in the last few years.” Due to the advent of new technologies, devices, and communication means like social networking sites, the amount of data produced by mankind is growing rapidly every year. The amount of data produced by us from the beginning of time till 2003 was 5 billion gigabytes. If you pile up the data in the form of disks it may fill an entire football field. The same amount was created in every two days in  2011 , and in every ten minutes in  2013 . This rate is still growing enormously. Though all this information produced is meaningful and can be useful when processed, it is being neglected. What is Big Data? Big data  is a collection of large datasets that cannot be processed using traditional computing techniques. It is not a single technique or a tool, rather it has become a complete subject, which involves various tools, technqiues and frameworks. What Comes Under Big Data? Big data involves the data produced by dif
Read more