CCA 175 - Dynamic insert for customers table

cca-175
#1

Hello Durga sir and all,
We all know how to insert data dynamically into a partitioned table using Hive, as we seen in the videos, we inserted orders data into the orders table which is partitioned as per order_month, but what if instead of orders table we use a customers table and insert into a customers table partitioned in such a way where queriying on the state field would be easier. In orders table where we used order_month because order_date is given in the table, but how to partition the customers table here. Please help me with this, I am just trying to play with data but not knowing on what i have to partition the customers table if the requirement says queries on state field should be processed easier. Thank you.

Regards
Mohan

0 Likes

How to create dynamic table partition on hive tables?
#2

I did not get your question completely. Do you have state field in customers table?

0 Likes

#3

Yes, we have state field in the customers table.

0 Likes

#4

I did not get your question completely.

Requirement will not be like queries on state field should be processed easier. Report requirements is the one which drive the table designing.

Are you looking for syntax or some thing else?

0 Likes

#5

Sir, If we need to partition customers table, how do we do it?

0 Likes

#6

It is the same way as I have demonstrated with ORDERS

CREATE TABLE table_name (
all columns except partitioned column
) PARTITIONED BY (state string)
row_format_clause
stored_as_clause

1 Like

#7

I think we don’t require any explanation on it.
It is self explanatory

Step 1 :

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=1000;

Step 2 :

DROP TABLE IF EXISTS emp_details_partition;

CREATE TEMPORARY TABLE emp_details(
efirstname VARCHAR(64),
elastname VARCHAR(64),
eaddress STRING,
ecountry VARCHAR(64),
ecity VARCHAR(64),
estate VARCHAR(64),
eemail STRING )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n’
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH ‘/home/mohitgupta108/employee.txt’ INTO TABLE emp_details;

Step 3:

CREATE TABLE emp_details_partition(
efirstname VARCHAR(64),
elastname VARCHAR(64),
eaddress STRING,
ecity VARCHAR(64),
eemail STRING )
PARTITIONED BY (ecountry VARCHAR(64), estate VARCHAR(64))
STORED AS SEQUENCEFILE;

Step 4:

INSERT INTO TABLE emp_details_partition
PARTITION (ecountry, estate)
SELECT efirstname ,
elastname ,
eaddress ,
ecity ,
ecountry ,
estate
FROM emp_details;

Hey Mohan !!! Make sure that this is not for customer table :wink:

2 Likes

#8

Thank you. @itversity @mohitkumar . Ive got a clear idea of what to do.

0 Likes