Hive partition table, bucket table, and external table

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