Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Structure Difference between partitioning and bucketing in hive

I created two tables:

1) One for Partitioning with bucketing
2) Only bucketing table

I know the concepts of partitioning and bucketing in hive. But I am little bit confused because I have read that 'partition creates directory and bucketing creates files'. I agree with the first part because I can see that in HDFS Hive Warehouse but I am not able to see any files for the ONLY bucketing table in HDFS except the data file which I loaded into the table. So where are the files of ONLY bucketing table ? The files which I am able to see under partitioned directory is it that one like for eg: 00000_0but this one can be for the partitioned table but what about the other bucketed table ??
Below is my code for creating table:

CREATE TABLE Employee(
ID BIGINT,
NAME STRING, 
SALARY BIGINT,
COUNTRY STRING 
)
CLUSTERED BY(ID) INTO 5 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;  

Code for load data is as below :

load data local inpath '/home/cloudera/Desktop/SampleData.txt' into table employee;  

I have read that buckets are created when we create table.Please correct me if I am missing something or going wrong.Anyone help please ?

like image 527
whatsinthename Avatar asked Oct 09 '17 10:10

whatsinthename


People also ask

What is hive partitioning&bucketing?

Advantages and Disadvantages of Hive Partitioning & Bucketing It distributes execution load horizontally. In partition faster execution of queries with the low volume of data takes place.

What is partitioning and bucketing in Hadoop?

– Hadoop In Real World What is the difference between partitioning and bucketing a table in Hive? What is the difference between partitioning and bucketing a table in Hive? Both partitioning and bucketing are techniques in Hive to organize the data efficiently so subsequent executions on the data works with optimal performance.

How to force hive to create buckets for bucketed data?

This is because you have to enforce the bucketing during the insert to your bucketed table or create the buckets for yourself. If you are inserting the data into a bucket table you can use the following flags. That will force Hive to create the buckets.

How to partition a hive table for faster query response?

For a faster query response Hive table can be PARTITIONED BY (country STRING, DEPT STRING). Partitioning tables changes how Hive structures the data storage and Hive will now create subdirectories reflecting the partitioning structure like .../employees/ country=ABC/DEPT=XYZ.


2 Answers

I created hive external tables (which is usually my choice). You can stick on to yours.

Please follow these steps:

  1. Create a database

    CREATE DATABASE IF NOT EXISTS testdb LOCATION '/hivedb/testdb';
    
  2. Create a clustered table (bucketed table)

    CREATE TABLE testdb.Employee(
    ID BIGINT,
    NAME STRING, 
    SALARY BIGINT,
    COUNTRY STRING 
    )
    CLUSTERED BY(ID) INTO 5 BUCKETS
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    STORED AS TEXTFILE
    LOCATION '/hivedb/testdb/employee';
    
  3. Create a plain table

    CREATE TABLE testdb.Employee_plain_table(
    ID BIGINT,
    NAME STRING, 
    SALARY BIGINT,
    COUNTRY STRING 
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    STORED AS TEXTFILE
    LOCATION '/hivedb/testdb/employee_plain_table';
    
  4. Enforce bucketing, as recommended by @lake in the previous answer

    set hive.enforce.bucketing = true;
    
  5. Create a data file ('data.txt'). I created a data file with 20 records.

    1,AAAAA,1000.00,USA
    2,BBBBB,2000.00,CANADA
    3,CCCCC,3000.00,MEXICO
    4,DDDDD,4000.00,BRAZIL
    5,EEEEE,5000.00,ARGENTINA
    6,DDDDD,6000.00,CHILE
    7,FFFFF,7000.00,BOLIVIA
    8,GGGGG,8000.00,VENEZUELA
    9,HHHHH,9000.00,PERU
    10,IIIII,10000.00,COLOMBIA
    11,JJJJJ,11000.00,EQUADOR
    12,KKKKK,12000.00,URUGUAY
    13,LLLLL,13000.00,PARAGUAY
    14,MMMMM,14000.00,GUYANA
    15,NNNNN,15000.00,NICARAGUA
    16,OOOOO,16000.00,PANAMA
    17,PPPPP,17000.00,COSTA RICA
    18,QQQQQ,18000.00,HAITI
    19,RRRRR,19000.00,DOMINICA
    20,SSSSS,20000.00,JAMAICA
    
  6. Copy the data file to HDFS location '/hivedb/testdb/employee_plain_table'

    ./hadoop fs -put ~/so/data.txt /hivedb/testdb/employee_plain_table
    
  7. Run a select * command over testdb.Employee_plain_table

    select * from testdb.Employee_plain_table;
    

    This should display 20 records.

  8. Use an insert command

    insert overwrite table testdb.employee select * from employee_plain_table;
    

    This should run a map reduce job and insert the records to bucketed table.

    This will create 5 files as we have 5 buckets as per the DDL of employee table.

  9. Verify this using command:

    ./hadoop fs -ls /hivedb/testdb/employee
    Found 5 items
    -rwxr-xr-x   1 hduser supergroup 95 2017-10-19 11:04 /hivedb/testdb/employee/000000_0
    -rwxr-xr-x   1 hduser supergroup 81 2017-10-19 11:04 /hivedb/testdb/employee/000001_0
    -rwxr-xr-x   1 hduser supergroup 90 2017-10-19 11:05 /hivedb/testdb/employee/000002_0
    -rwxr-xr-x   1 hduser supergroup 88 2017-10-19 11:05 /hivedb/testdb/employee/000003_0
    -rwxr-xr-x   1 hduser supergroup 84 2017-10-19 11:05 /hivedb/testdb/employee/000004_0
    

Open up each file, compare with the original data file and you will get to know what has happened.

Hope this clarifies your query! Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL+BucketedTables

Update: You used load with "local", it is only a copy operation i.e. it copies the given input file from source to destination location. Load command from "local" is a copy, and the one from "hdfs" is a move operation. No mapreduce is involved, so no bucketing happened.

like image 119
Marco99 Avatar answered Oct 13 '22 14:10

Marco99


This is because you have to enforce the bucketing during the insert to your bucketed table or create the buckets for yourself. If you are inserting the data into a bucket table you can use the following flags.

 set hive.enforce.bucketing = true;  -- (Note: Not needed in Hive 2.x onward)

That will force Hive to create the buckets. You should be able to see a number of files equal to your number of buckets (if you have enough records and a proper distribution of your clustering column).

Update. Load command doesn't create any buckets, it just put the data into the the HDFS. You should load the data into another table and insert the data from one table to another using insert overwrite statement.

like image 4
hlagos Avatar answered Oct 13 '22 15:10

hlagos