Hive Tutorial For Beginners | What Is Hive | Hive In Hadoop | Apache Hive Tutorial | Simplilearn Video Lecture | Taming the Big Data with HAdoop and MapReduce - Software Development

[Music]
after completing this lesson you will be
able to improve query performance with
the concepts of data file partitioning
define hive query language or hive QL
describe ways in which hive QL can be
extended overview of data file
partitioning in the first topic of this
lesson you will learn to get an overview
of data file partitioning in the
previous lessons you have learned that
by default all files in the dataset are
stored in a single Hadoop distributed
file system or HDFS directory hive is
considered a tool of choice for
performing queries on large datasets
especially those that require full table
scans hive has advanced partitioning
features data file partitioning is very
useful to prune data during query in
order to reduce query times there are
many instances where users need to
filter data on specific column values
using the partitioning feature of hive
that subdivides the Buddha hive users
can identify the columns which can be
used to organize the data
partitioning the analysis can be done
only on the relevant subset of data
resulting in a highly improved
performance of hive queries in K
partition tables sub-directories are
created under the table data directory
for each unique value of a partitioned
column you will learn more about the
partitioning features in the subsequent
screens let's begin with an example of a
non partitioned table in non partitioned
tables by default all queries have to
scan all files in the directory
this means that hive will need to read
all the files in a tables data directory
this can be a very slow and expensive
process especially when the tables are
large in this example you can see that
there is a state column created in hive
the requirement is to convert this to a
state wise partition so that separate
tables are created for separate States
the customer details are required to be
partitioned by state for fast retrieval
of subset data pertaining to the
customer category remember that you can
perform the same queries in Impala as
well in the next screen you will see an
example of how this table is partitioned
state wise so that a full scan on the
entire table is not required here is an
example of a partitioned table this
example shows you how the previously non
partitioned table is now partitioned you
can see that the state column is no
longer included in the create table
definition but it is included in the
partition definition partitions are
actually horizontal slices of data that
allow larger sets of data to be
separated into more manageable chunks
this essentially means that you can use
partitioning to store data in separate
files by state as shown in the example
at the time of table creation partitions
are defined using the partitioned by
clause with a list of column definitions
for partitioning
partition column is a virtual column
where data is not actually stored in the
file partition table
in this demo you will learn how to
create a partition table in hive type
the command in this demonstration an
external table which is partitioned by
state is created the column is string
type and the table will be stored in
simply learned slash accounts by state
directory in HDFS
you
now the command has created a partition
table
this brings you to the end of this
demonstration you have now learned how
to create a partitioned table in hive
describe
in table
in this demo you will view the details
of the partition table named accounts by
states which was created in the previous
demo type describe accounts underscore
buy underscore state and execute to see
more details you can type describe and
the table name which in this
demonstration is accounts - bye - state
you can now see all the other details
along with the partitioned column
details this partitioned column is like
a virtual column
this brings you to the end of this
demonstration you have now learned how
to view the details of the partition
table
that you-know-what partitioning is let's
understand how you can insert data into
partition tables data insertion into
partition tables can be done in two ways
or modes static partitioning dynamic
partitioning you will learn more about
these concepts in the subsequent screens
let's begin with static partitioning in
the static partitioning mode you can
insert or input the data files
individually into a partition table you
can create new partitions as needed and
to find the new partitions using the add
partition Clause while loading data you
need to specify which partition to store
the data in this means that with each
load you need to specify the partition
column value you can add a partition in
the table and move the data file into
the partition of the table as you can
see in this example you can add a
partition for each new day of account
data with dynamic partitioning
partitions get created automatically at
load times new partitions can be created
dynamically from existing data
partitions are automatically created
based on the value of the last column if
the partition does not already exist it
will be created in case the partition
does exist it will be overwritten by the
overwrite keyword as shown in the
example as you can see a partition is
being overwritten when you have a large
amount of data stored in the table then
dynamic partition is suitable keep in
mind that by default dynamic
partitioning is disabled in hive to
prevent accidental partition creation to
use dynamic partitioning you need to
enable it by using these settings set
space hive exec dynamic dot partition
equals true semicolon
set space hive exact dynamic partition
dot mode equals non-strict semicolon hey
want to become an expert in Big Data
then subscribe to the simply learn
channel and click here to watch more
such videos centered up and get
certified in Big Data click here