Hive partition table, bucket table, and external table

Source: Internet
Author: User
Tags hash modulus

1: Partition Table

Figure: Creating a partitioned Table


Figure: Importing Data


Figure: Display information in HDFs

The partition field is the identity of a folder


Figure: Creating a partition on multiple columns



Figure: Importing Data


Figure: Structure information formed on HDFs after partitioning is established on multiple columns

It is possible to make most of the fields in the table as partition fields. Partitions are not as much as possible, the more partitions, the more you load the data must pay attention to the partition, then the file will be controlled very small, map of the number of tasks increased,

Calculations are not necessarily fast. Hive limits the number of partitions. Select a number of fields to query more frequently to establish partitions, time fields and so on.

Queries are more frequent and do not have many fields of enumerated values, and are suitable as partitioning fields. The partition field is the virtual column.


Figure: The query results, where the partition fields appear, but do not exist in the data file, we call them virtual columns.

Unreasonable place, for example, we analyze a website's sales log, we partition according to the area of IP address, then some areas such as Beijing-Guangzhou-Shanghai area IP will be more,

resulting in uneven distribution of data, some files have more data, and some files have less data.

2: Bucket table

The opposite of the record to do a hash calculation, after the hash operation, and then the hash of the modulus calculation, such as MoD 10, then after the calculation of the modulus, the partition of the amount of data is similar,

Modulus of data for the bucket, modulo 10, then there are 10 barrels, then each map to calculate the same time, the disadvantage: With business fields to query, there is no effect.

When the table is connected, it is convenient to query the data, it is convenient to carry out the sampling query, and the comparison is in accordance with the bucket table.



Figure: Create a bucket table and load the data


Figure: Three files after the end of the bucket


3: External Table

The external table does not correspond to the folder for HDFs, but it can be found in the database. Deleting the data sheet does not affect the data.

External tables and partitioned tables are used more, and users have already divided the data according to certain fields when uploading data.



Figure: Creating an external table and querying






Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.