08HiveDataStructures

Hive Data Structures

Outline

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

Primitive Data Types

TINYINT, 1 byte, example: 10Y
SMALLINT, 2 bytes, example: 10S
INT, 4 bytes, example: 10
BIGINT, 8 bytes, example: 10L
FLOAT, 4 bytes, example: 0.1234567
DOUBLE, 8 bytes, example: 0.1234567891234
DECIMAL, (m,n), example: 3.14
BINARY, n bytes, example: 1011001
BOOLEAN, 1 byte example: TRUE
STRING, 2G bytes, example: 'Abcdef'
CHAR, 255 bytes, example: 'Hello'

Primitive Data Types

VARCHAR, 1 byte, example: 'Hive'
DATE, YYYY-MM-DD, example: '2017-05-03'
TIMESTAMP, YYYY-MM-DD HH:MM:SS[.fff...] example: '2017-05-03 15:10:00.345'

Hive Data Structures

ARRAY: list of values of the same types,

example: ['Hadoop', 'Pig','Hive']

access: bigdata[1]

MAP: a set of key-value pairs,

example: {'k1':'Hadoop', 'k2':'Pig'}

access: bigdata['k2']

STRUCT: user defined structure of any type of fields,

example: {name:'Hadoop', age:24, salary:50000.06}

access: bigdata.name

Key Differences:

Order: Arrays are ordered by index; Maps and Structs are generally unordered (though some map implementations might preserve insertion order).

Access Method: Arrays use numeric indices; Maps use unique keys; Structs use named fields.

Homogeneity: Arrays typically store elements of the same type; Maps and Structs can store values/fields of different types.

These three fundamental data structures are the building blocks for almost all complex data representations in programming and data processing.

Complex Data Types

ARRAY: list of values of the same types,

example: ['Hadoop', 'Pig','Hive']
access: bigdata[1]

MAP: a set of key-value pairs,

example: {'k1':'Hadoop', 'k2':'Pig'}
access: bigdata['k2']

STRUCT: user defined structure of any type of fields,

example: {name:'Hadoop', age:24, salary:50000.06}
access: bigdata.name

Understanding the INSERT INTO Command

The INSERT INTO statement is used to add new rows of data into the types table.

SQL

INSERT INTO types

SELECT array('bolt', 'nut', 'screw'),

map(1,'bolt', 2,'nut', 3,'screw'),

named_struct('a','bolt', 'b',5, 'c',0.5)

FROM DUAL;

INSERT INTO types: This specifies that data will be inserted into the types table.

SELECT ... FROM DUAL:

In many SQL dialects (like Oracle, or in this context, systems that support a similar construct like Hive/Spark SQL), DUAL is a special single-row, single-column table used for selecting constant values or evaluating expressions when you don't need to retrieve data from an actual table. It essentially creates one "dummy" row for the SELECT statement to operate on.

The SELECT clause generates the values for the columns of the new row.

array('bolt', 'nut', 'screw'):

This is a constructor function for an ARRAY. It creates an array containing the three string literal values: 'bolt', 'nut', and 'screw'.

This array will be inserted into the array_col.

map(1,'bolt', 2,'nut', 3,'screw'):

This is a constructor function for a MAP. It creates a map with three key-value pairs:

Key 1 (int) maps to value 'bolt' (string).

Key 2 (int) maps to value 'nut' (string).

Key 3 (int) maps to value 'screw' (string).

This map will be inserted into the map_col.

named_struct('a','bolt', 'b',5, 'c',0.5):

This is a constructor function for a STRUCT (often named_struct to specify field names explicitly). It creates a struct with the following fields and values:

Field a (string) gets the value 'bolt'.

Field b (int) gets the value 5.

Field c (double) gets the value 0.5.

This struct will be inserted into the struct_col.

Complex Data Types

The following CREATE TABLE command creates a table types with complex data types columns


CREATE TABLE types(
   array_col array<string>,
   map_col map<int,string>,
   struct_col struct<a:string, b:int, c:double> );

SELECT statement can be used to load data into a table with complex data types columns


INSERT INTO types
   SELECT array('bolt', 'nut', 'screw'),
          map(1,'bolt', 2,'nut', 3,'screw'),
          named_struct('a','bolt', 'b',5, 'c',0.5)
   FROM DUAL;

Hive Data Structures

Outline

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

In Hive, a database serves as a fundamental organizational unit. It's essentially a logical grouping of related tables, views, and other Hive objects. This conceptual grouping is vital for:

Organization: Keeping related data sets together (e.g., all tables for an e-commerce application, all tables for a specific analytical project, or all tables related to a particular data source).

Namespace Management: Preventing naming conflicts. You can have a table named customers in database_a and another table also named customers in database_b without any issues. To refer to a specific table, you use the database_name.table_name syntax (e.g., tpchr.customer).

Permissions and Security: In a multi-user environment, databases can be used as a boundary for setting permissions. You might grant specific users or groups access only to certain databases.

Physical Implementation on HDFS

The conceptual organization of Hive databases directly translates to a physical structure on the Hadoop Distributed File System (HDFS).

Database as a Folder: Each Hive database is physically represented as a directory (folder) in HDFS. This directory acts as the root location for all tables within that database.

Table Directories: Inside a database's directory, each Hive table is, in turn, typically represented as a subdirectory. This subdirectory contains the actual data files for that table.

Default Database and Locations

default Database: When you first start using Hive, there's a pre-existing database called default. If you don't explicitly specify a database when creating a table (e.g., CREATE TABLE my_table (...)), Hive will automatically create that table within the default database.

Default Location: The default database's HDFS directory is located at: /user/hive/warehouse

This is a crucial path in any Hive installation, as it's the central repository for all data managed by Hive unless explicitly configured otherwise.

New Database Creation: When you create a new database using a command like CREATE DATABASE my_new_db;, Hive does the following:

It creates a new directory within the /user/hive/warehouse location.

Databases

Database is a collection of conceptually related tables, i.e. tables that implement a conceptual schema
Database is implemented as a folder/directory in HDFS
A default database is located at /user/hive/warehouse
A new database is created in a folder /user/hive/warehouse
For example, a database tpchr is located at /user/hive/warehouse/tpchr.db

CREATE DATABASE tpchr;

This command is used to create a new database in Hive.

tpchr: This is the name given to the new database.

What happens behind the scenes:

When this command is executed, Hive performs several actions:

Logical Entry: It registers tpchr as a new database in its metastore (a central repository for Hive's metadata).

Physical Directory Creation: It creates a corresponding directory in HDFS. By default, this directory will be located under the Hive warehouse directory, typically /user/hive/warehouse/. So, for tpchr, a directory named /user/hive/warehouse/tpchr.db will be created. This directory will serve as the default storage location for any tables you create within the tpchr database.

Purpose:

To establish a new logical container for grouping related tables and other Hive objects, and to create the underlying physical storage location in HDFS for that container.

DESCRIBE DATABASE tpchr;

This command is used to display detailed information about a specific database.

DESCRIBE DATABASE: These keywords instruct Hive to provide metadata about a database. (Note: DESCRIBE SCHEMA is a synonym and can be used interchangeably).

tpchr: This specifies the name of the database for which you want to retrieve information.

The output typically includes:

name: The name of the database (e.g., tpchr).

comment: Any descriptive comment associated with the database (if provided during creation).

location: The HDFS path where the database's data is stored (e.g., hdfs://:8020/user/hive/warehouse/tpchr.db).

owner: The user who created the database.

parameters: Any additional key-value properties set for the database.

Purpose:

To inspect the metadata and configuration of an existing database, which is useful for auditing, troubleshooting, or simply understanding the environment.

Databases

The following CREATE DATABASE command creates a database tpchr
To find more information about a database we can use DESCRIBE DATABASE command
A command USE makes a database "current" (there is no need to prefix a table name with a database name)
To delete a database we can use DROP DATABASE command

Hive Data Structures

Outline

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

Internal Tables (Managed Tables)

An internal table is a table where Hive takes full responsibility for managing both the table's metadata (schema, column types, etc.) and its data files on HDFS. When you create an internal table and load data into it, Hive moves or copies that data into its own managed HDFS directory structure.

Key Characteristics:

Creation: Created using a standard CREATE TABLE statement without specifying a LOCATION property.

CREATE TABLE internal_users (

id INT,

name STRING,

email STRING);

Data loaded into this table would go to /user/hive/warehouse/internal_users/ (if using the default database) or /user/hive/warehouse/my_db.db/internal_users/ (if using my_db).

Data Location: Hive stores the data files for internal tables within its default warehouse directory (e.g., /user/hive/warehouse/ or within a specific database's directory like /user/hive/warehouse/my_db.db/).

Lifecycle Management: Hive fully manages the lifecycle of both the table schema and the underlying data.

Adding/Deleting Data: When you INSERT, UPDATE, or DELETE data, Hive manages the physical files on HDFS.

Dropping the Table: When you execute DROP TABLE internal_users;:

Hive deletes the table's metadata from the Hive Metastore.

Hive deletes the corresponding data directory and all data files from HDFS. This is a permanent and irreversible deletion of your data.

External Tables

An external table is a table where Hive manages only the metadata (schema, column types, etc.) in its Metastore, but the data files themselves reside in a location on HDFS that is outside of Hive's default warehouse directory. Hive does not manage the lifecycle of the data files for external tables.

Key Characteristics:

Creation: Created using a CREATE EXTERNAL TABLE statement and explicitly specifying the LOCATION property, pointing to the HDFS path where the data already exists. SQL

CREATE EXTERNAL TABLE external_logs (

timestamp BIGINT,

level STRING,

message STRING)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

LOCATION '/data/logs/apache'; -- Data already exists here

Data Location: The data for external tables is stored in an HDFS location that you specify using the LOCATION clause. This location can be anywhere on HDFS, as long as Hive has permission to read it.

Lifecycle Management: Hive manages only the table's metadata; it does not manage the data files' lifecycle.

Adding/Deleting Data: Hive can read and write data to/from external tables, but the data files themselves are expected to be managed by an external process or tool (e.g., a Spark job, a custom script, another ETL tool).

Dropping the Table: When you execute DROP TABLE external_logs;:

Hive deletes the table's metadata from the Hive Metastore.

Hive DOES NOT delete the data files from the HDFS LOCATION specified. The data remains intact in its original HDFS path.

Tables

An internal table (or managed table) is a table created by Hive in HDFS
If data is already stored in HDFS then an external Hive table can be created to provide a tabular view of the data
Location in HDFS of data stored in an external table is specified in the LOCATION properties instead of the default warehouse directory
Hive fully manages the life cycle (add/delete data, create/drop table) of internal tables and data in the internal tables
When an external table is deleted its metadata information is deleted from a metastore and the data is kept in HDFS

CREATE TABLE IF NOT EXISTS intregion(

R_REGIONKEY DECIMAL(12),

R_NAME VARCHAR(25),

R_COMMENT VARCHAR(152) )

ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'

STORED AS TEXTFILE;

This command defines a new table named intregion within Hive. Because the EXTERNAL keyword is not used, this will create an internal (or managed) table.

CREATE TABLE IF NOT EXISTS intregion:

CREATE TABLE: The standard keywords to initiate table creation.

IF NOT EXISTS: This is a very useful clause. If a table named intregion already exists, Hive will simply do nothing and not throw an error. If the table doesn't exist, it will proceed to create it. This prevents errors when running scripts multiple times.

intregion: This is the name of the table being created. As it's an internal table, Hive will manage its data files in its warehouse directory (e.g., /user/hive/warehouse/intregion/ or /user/hive/warehouse/.db/intregion/).

(R_REGIONKEY DECIMAL(12), R_NAME VARCHAR(25), R_COMMENT VARCHAR(152)):

This defines the schema of the table, specifying the column names and their respective data types:

R_REGIONKEY DECIMAL(12): A column named R_REGIONKEY that can store decimal numbers with up to 12 digits of precision.

R_NAME VARCHAR(25): A column named R_NAME that can store variable-length strings up to 25 characters.

R_COMMENT VARCHAR(152): A column named R_COMMENT that can store variable-length strings up to 152 characters.

ROW FORMAT DELIMITED FIELDS TERMINATED BY '|':

This clause specifies how Hive should interpret the format of the data rows within the data files.

ROW FORMAT DELIMITED: Indicates that the data within each row is separated by a delimiter.

FIELDS TERMINATED BY '|': Specifies that the individual fields (columns) within each row are separated by the pipe character (|). For example, a row of data might look like 0|ASIA|some comment here.

STORED AS TEXTFILE:

This clause defines the file format in which the table's data will be stored on HDFS.

TEXTFILE: Indicates that the data will be stored as plain text files, with each line representing a row. This is a common and human-readable format, though not always the most efficient for large-scale analytics (other formats like Parquet or ORC are often preferred for performance).

Tables

CREATE TABLE statement creates an internal table


CREATE TABLE IF NOT EXISTS intregion(
   R_REGIONKEY DECIMAL(12),
   R_NAME VARCHAR(25),
   R_COMMENT VARCHAR(152) )
      ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
      STORED AS TEXTFILE;

LOAD DATA statement loads data into an internal table


LOAD DATA LOCAL INPATH 'region.tbl' INTO TABLE intregion;

CREATE EXTERNAL TABLE IF NOT EXISTS extregion(

R_REGIONKEY DECIMAL(12),

R_NAME VARCHAR(25),

R_COMMENT VARCHAR(152) )

ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'

STORED AS TEXTFILE LOCATION '/user/tpchr/region';

This command defines a new table named extregion in Hive, specifically declaring it as an external table.

CREATE EXTERNAL TABLE IF NOT EXISTS extregion:

EXTERNAL: This is the crucial keyword that distinguishes this from an internal table. It tells Hive that it will not manage the underlying data files' lifecycle.

IF NOT EXISTS: As with internal tables, this prevents an error if extregion already exists.

extregion: The name of the table being created.

(R_REGIONKEY DECIMAL(12), R_NAME VARCHAR(25), R_COMMENT VARCHAR(152)):

This defines the schema of the table, identical to the internal table example. The columns, their names, and data types (DECIMAL(12), VARCHAR(25), VARCHAR(152)) are specified here.

ROW FORMAT DELIMITED FIELDS TERMINATED BY '|':

This specifies the format of the data within the files, indicating that fields are delimited by a pipe (|) character. Hive needs this information to correctly parse the data when you query the table.

STORED AS TEXTFILE:

This specifies the file format of the data. TEXTFILE means the data is in plain text files.

LOCATION '/user/tpchr/region':

This is the most critical part for an external table. It explicitly tells Hive the HDFS path where the actual data files for this table are (or will be) located.

/user/tpchr/region: This is an HDFS directory. Unlike internal tables which use Hive's managed warehouse directory, external tables point to a user-specified HDFS location. This directory does not have to be empty; if data files already exist at this location and conform to the specified ROW FORMAT and STORED AS properties, Hive will immediately be able to query them after the table is created.

LOAD DATA LOCAL INPATH 'region.tbl' INTO TABLE extregion;

This command is used to populate the extregion table with data from a source file, just like with an internal table, but its implications for external tables are slightly different.

LOAD DATA: The command to load data.

LOCAL INPATH 'region.tbl':

LOCAL: Indicates the source file region.tbl is on the local file system of the machine where the Hive client is running.

INPATH 'region.tbl': The path to the source data file.

INTO TABLE extregion:

INTO TABLE: Specifies the target table for the data.

extregion: The external table we just created.

Tables

CREATE EXTERNAL TABLE statement creates an external table


CREATE EXTERNAL TABLE IF NOT EXISTS extregion(
   R_REGIONKEY DECIMAL(12),
   R_NAME VARCHAR(25),
   R_COMMENT VARCHAR(152) )
      ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
      STORED AS TEXTFILE LOCATION '/user/tpchr/region';

LOAD DATA statement loads data into an external table


LOAD DATA LOCAL INPATH 'region.tbl' INTO TABLE extregion;

Creating an external table over already existing data in HDFS is a very common and efficient pattern in big data environments.

The example demonstrates a three-step process:

Prepare the data in HDFS.

Verify the data's presence and permissions in HDFS.

Create the external Hive table, pointing it to that existing HDFS location.

Step 1: Prepare the Data in HDFS (hadoop fs -mkdir and hadoop fs -put)

hadoop fs -mkdir /user/tpchr/nation

hadoop fs -put nation.tbl /user/tpchr/nation

Step 2: Verify Data Presence and Permissions (hadoop fs -ls)

hadoop fs -ls /user/tpchr/nation

Step 3: Create the External Hive Table (CREATE EXTERNAL TABLE)

CREATE EXTERNAL TABLE IF NOT EXISTS extnation(

N_NATIONKEY DECIMAL(12),

N_NAME VARCHAR(25),

N_COMMENT VARCHAR(152) )

ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'

STORED AS TEXTFILE LOCATION '/user/tpchr/nation';

CREATE EXTERNAL TABLE IF NOT EXISTS extnation(...):

EXTERNAL: This keyword is paramount. It signifies that Hive will not manage the data's lifecycle. It will only create a metadata entry for this table.

extnation: The name of the Hive table being created.

Schema Definition (N_NATIONKEY DECIMAL(12), N_NAME VARCHAR(25), N_COMMENT VARCHAR(152)):

This defines the expected structure and data types of the data within the nation.tbl file. Hive will use this schema to interpret the data when queries are run. It's crucial that this schema matches the actual format of the data in nation.tbl.

ROW FORMAT DELIMITED FIELDS TERMINATED BY '|':

This tells Hive that each line in nation.tbl is a row, and the columns within that row are separated by the pipe (|) character. For example, a line might look like 0|ALGERIA| some nation comment here.

STORED AS TEXTFILE:

This specifies that the data files at the LOCATION are plain text files.

LOCATION '/user/tpchr/nation':

This is the link! This clause tells Hive exactly where to find the data files for this table on HDFS. Hive will look for all files (like nation.tbl) within this /user/tpchr/nation directory and treat them as part of the extnation table.

Tables

An external table can be created "over" an already existing file in HDFS


hadoop fs -mkdir /user/tpchr/nation
hadoop fs -put nation.tbl /user/tpchr/nation
hadoop fs -ls /user/tpchr/nation
-rw-r--r-- 3 janusz supergroup 401 2017-07-02 10:24 /user/tpchr/nation/nation.tbl

	     
CREATE EXTERNAL TABLE IF NOT EXISTS extnation(
   N_NATIONKEY DECIMAL(12),
   N_NAME      VARCHAR(25),
   N_COMMENT   VARCHAR(152) )
      ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE LOCATION '/user/tpchr/nation';

Hive Data Structures

Outline

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

Let's explain the concept of partitioning in Hive and how the provided CREATE TABLE statement sets up a partitioned table.

The Problem: Unnecessary Full Table Scans

In big data scenarios, tables can become enormous, storing terabytes or even petabytes of data. If you have a massive table (e.g., customer transactions for many years, or sensor data from various regions) and you only want to query a small subset (e.g., transactions for a specific month, or sensor data from a particular region), scanning the entire table becomes incredibly inefficient and slow. It wastes computational resources (CPU, I/O, network bandwidth).

The Solution: Partitioning

Partitioning is a technique used in Hive (and other data warehousing systems) to divide a large table into smaller, more manageable segments called partitions. Each partition corresponds to a specific value (or combination of values) of one or more designated columns, known as partition columns.

How it Works (Physical Implementation):

Subfolders in HDFS: Physically, each partition corresponds to a separate subdirectory within the main HDFS directory of the table.

Data Organization: When data is loaded into a partitioned table, Hive (or the loading process) places the data into the appropriate partition (subdirectory) based on the values in the partition columns.

Example: If you partition a sales table by year and month, you might have HDFS directories like:

/user/hive/warehouse/sales/year=2023/month=01/

/user/hive/warehouse/sales/year=2023/month=02/

/user/hive/warehouse/sales/year=2026/month=01/

Benefit: Pruning (Eliminating Unnecessary Scans):

Targeted Access: When you query a partitioned table and include the partition columns in your WHERE clause, Hive's query optimizer can intelligently determine which specific partition subdirectories contain the relevant data.

Reduced I/O: Instead of scanning the entire table's data across all HDFS subdirectories, Hive only reads data from the required partitions. This significantly reduces the amount of data read from HDFS and processed, leading to much faster query execution times and more efficient resource utilization.

Example: If you query SELECT * FROM sales WHERE year = 2024 AND month = 01;, Hive will only access the /user/hive/warehouse/sales/year=2024/month=01/ directory and its files, completely ignoring data from other years and months.

Partitions

To eliminate unnecessary scans of entire table when only a fragment is needed a table can be divided into partitions
A partition corresponds to predefined columns and it is stored as subfolder in HDFS

When a table is searched only required partitions are accessed


CREATE TABLE IF NOT EXISTS part(
   P_PARTKEY DECIMAL(12),
   P_NAME VARCHAR(55),
   P_TYPE VARCHAR(25),
   P_SIZE DECIMAL(12),
   P_COMMENT VARCHAR(23) )
   PARTITIONED BY (P_BRAND VARCHAR(20))
      ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
      STORED AS TEXTFILE;

Let's continue our explanation of Hive partitioning, focusing on how partitions are added, data is loaded into them, and how they physically manifest in HDFS.

1. Adding a Partition (ALTER TABLE ... ADD PARTITION)

When you define a table as PARTITIONED BY, Hive knows the potential for partitions, but it doesn't create the physical subdirectories for them until you explicitly tell it to, or when you load data in a way that automatically creates them (though explicit addition is often recommended for better control).

ALTER TABLE part ADD PARTITION (P_BRAND='GoldenBolts');

ALTER TABLE part: This specifies that we are modifying the part table.

ADD PARTITION: This clause instructs Hive to add a new partition.

(P_BRAND='GoldenBolts'): This defines the specific partition being added. It means we are adding a partition where the value of the P_BRAND partition column is 'GoldenBolts'.

What happens behind the scenes:

Metastore Update: Hive updates its Metastore to record the existence of a new partition for part table with P_BRAND='GoldenBolts'.

HDFS Directory Creation: Hive creates the corresponding physical directory in HDFS for this partition. Based on the table's location and partition definition, this would be something like /user/hive/warehouse/part/p_brand=GoldenBolts/. At this point, the directory is empty.

Verifying Partitions (SHOW PARTITIONS)

show partitions part;

This command is used to list all the partitions that have been registered for a given table in the Hive Metastore.

p_brand=GoldenBolts

Time taken: 0.072 seconds, Fetched: 1 row(s)

The output confirms that a partition named p_brand=GoldenBolts now exists for the part table. This indicates that Hive is aware of this partition and its corresponding HDFS path has been created.

To inspect the current partition structure of a table, ensuring that partitions were added correctly and to see what data segments are available for querying.

2. Loading Data into a Partition (LOAD DATA LOCAL INPATH ... INTO TABLE ... PARTITION)

Once a partition is defined (either explicitly or implicitly), you can load data specifically into that partition. This ensures that the data is placed in the correct HDFS subdirectory, allowing for efficient partition pruning during queries.

LOAD DATA LOCAL INPATH '/local/home/janusz/HIVE-EXAMPLES/TPCHR/part.txt' OVERWRITE INTO TABLE part PARTITION (P_BRAND='GoldenBolts');

This command loads data from a local file directly into the previously created partition of the part table.

LOAD DATA LOCAL INPATH '/local/home/janusz/HIVE-EXAMPLES/TPCHR/part.txt':

LOCAL INPATH: Specifies that the source file is part.txt and it's located on the local file system at the given absolute path.

Crucial assumption: The part.txt file is expected to contain data only for the P_BRAND='GoldenBolts' partition, and not include the P_BRAND column itself within its data rows, as P_BRAND is a partition column. The schema of part.txt should match P_PARTKEY, P_NAME, P_TYPE, P_SIZE, P_COMMENT.

OVERWRITE: This is an important keyword.

If the partition P_BRAND='GoldenBolts' already contains data, OVERWRITE will delete all existing data files within that specific partition's HDFS directory before copying the new part.txt data.

If OVERWRITE is omitted, LOAD DATA will append the new data to the existing data within the partition.

INTO TABLE part: Specifies the target table is part.

PARTITION (P_BRAND='GoldenBolts'): This is the key part for loading into a partition. It explicitly tells Hive which partition (identified by P_BRAND='GoldenBolts') the data from part.txt should be loaded into. Hive will place the data into the HDFS directory associated with this specific partition.

Partitions

A partition must be added before data is loaded


ALTER TABLE part ADD PARTITION (P_BRAND='GoldenBolts');


show partitions part;
OK
p_brand=GoldenBolts
Time taken: 0.072 seconds, Fetched: 1 row(s)

A command that loads a file into a table can be used to load a partition


LOAD DATA LOCAL INPATH '/local/home/janusz/HIVE-EXAMPLES/TPCHR/part.txt'
OVERWRITE INTO TABLE part PARTITION (P_BRAND='GoldenBolts');

A partition is stored in HDFS as a subfolder


hadoop fs -ls /user/hive/warehouse/part
Found 1 items
drwxrwxr-x - janusz supergroup 0 2017-07-01
19:00 /user/hive/warehouse/part/p_brand=GoldenBolts

Hive Data Structures

Outline

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

Bucketing in Hive is an optimization technique that complements partitioning to further speed up queries, particularly joins and sampling.

While partitioning divides a table based on column values into separate HDFS directories, bucketing divides the data within each partition (or within the entire table if not partitioned) into a pre-defined number of segments called buckets.

Segment of a File: Physically, each bucket typically corresponds to a separate file within an HDFS directory (either the table's main directory or a partition's subdirectory).

Hash-Based Distribution: The values in a designated bucket column are put through a user-defined hash function. The result of this hash function (modulo the total number of buckets) determines which specific bucket (file) a row belongs to. This ensures an even distribution of data across the buckets based on the bucket column's values.

Example: If you bucket a customer table by C_CUSTKEY into 2 buckets, a customer with C_CUSTKEY=100 might go into bucket_00000, and a customer with C_CUSTKEY=200 might go into bucket_00001, based on their hash values.

Bucketing provides several significant advantages:

Faster Joins (Bucket-Join Optimization):

If two tables are bucketed on the same column with the same number of buckets, Hive can perform a highly efficient "bucket-aware join."

Instead of performing a full shuffle of data, Hive can join corresponding buckets directly. For example, bucket_0 from Table A only needs to be joined with bucket_0 from Table B, dramatically reducing the amount of data shuffled over the network. This is essentially a sort-merge join where the data is pre-sorted/pre-hashed into buckets.

Efficient Sampling:

Because data is evenly distributed across buckets based on a hash, it's very easy to take a representative sample of your data. You can tell Hive to select data from, say, "every 1st bucket out of 10," and you'll get a statistically representative subset of your data without scanning the whole table.

Example: SELECT * FROM customer TABLESAMPLE (BUCKET 1 OUT OF 2 ON C_CUSTKEY);

Data Pruning (Similar to Partitioning, but within a Partition):

If you're looking for a specific value in a bucketed column, Hive can narrow down the search to just one or a few buckets, instead of scanning all files in a partition. This is less common than join optimization but still a benefit.

Buckets

Another way to speed up processing of a table is to divide it into buckets
A bucket corresponds to segment of file in HDFS

The values in a bucket column will be hashed by a user defined number into buckets.


CREATE TABLE customer(
   C_CUSTKEY DECIMAL(12),
   C_NAME VARCHAR(25),
   C_PHONE CHAR(15),
   C_ACCTBAL DECIMAL(12,2) )
   CLUSTERED BY (C_CUSTKEY) INTO 2 BUCKETS
      ROW FORMAT DELIMITED FIELDS TERMINATED BY '|';


set map.reduce.tasks = 2;
set hive.enforce.bucketing = true;

Buckets

INSERT can be used to populate a bucket table


INSERT INTO customer
values(1,'Customer#000000001','25-989-741-2988',711.56);
INSERT INTO customer
values(2,'Customer#000000002','23-768-687-3665',121.65);
INSERT INTO customer
values(3,'Customer#000000003','11-719-748-3364',7498.12);
INSERT INTO customer
values(4,'Customer#000000004','14-128-190-5944',2866.83);
INSERT INTO customer
values(5,'Customer#000000005','13-750-942-6364',794.47)

Hive Data Structures

Outline

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

Let's explain Views in the context of databases, which are a powerful feature, especially in data warehousing environments like Hive.

A view is a virtual table based on the result set of a SQL query. It does not store data itself; instead, it's a saved SQL query that you can treat like a regular table. When you query a view, the database system (like Hive) executes the underlying query defined for the view and returns the results.

Think of it as a pre-defined window or lens through which you look at your data.

Key Characteristics and Concepts

Virtual Table:

Views are not physical tables. They don't have their own data stored on disk.

The data you see through a view is always the current data from the underlying base tables at the time the view is queried.

Based on a Query:

A view is defined by a SELECT statement. This statement can be as simple or as complex as needed, involving joins, filters, aggregations, subqueries, etc.

Metadata Only:

When you create a view, only its definition (the SQL query) is stored in the database's metadata (Hive's Metastore, in this case). No data is copied or moved.

Views

Views are logical data structures that simplify queries
Views do not store data or get materialized
Once a views is created its definition is frozen and changes in the tables used in the view definition are not reflected in the view schema

References

Gross C., GuptaA., Shaw S., Vermeulen A. F., Kjerrumgaar D., Practical Hive: A guide to Hadoop's Data Warehouse System, Apress 2016, Chapter 4 (Available through UOW library)
Lee D., Instant Apache Hive essentials how-to: leverage your knowledge of SQL to easily write distributed data processing applications on Hadoop using Apache Hive, Packt Publishing Ltd. 2013 (Available through UOW library)
Apache Hive