Pig array typeaccess bigdata map set key-value pairs

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

BIGINT, 8 bytes, example: 10L

FLOAT, 4 bytes, example: 0.1234567

Primitive Data Types
VARCHAR, 1 byte, example: 'Hive'

DATE, YYYY-MM-DD, example: '2017-05-03'

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

access: bigdata.name

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

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

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

/user/hive/warehouse

A new database is created in a folder /user/hive/warehouse

The following CREATE DATABASEcommand creates a database tpchr

Creating a database CREATE DATABASE tpchr;

Making a database current USE tpchr;

To delete a database we can use

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

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;

LOAD DATAstatement loads data into an internal table

CREATE EXTERNAL TABLEstatement creates an external table

Creating an external table

Tables

Hive Data Structures
Outline

A partition corresponds to prede\ned columns and it is stored as subfolder in HDFS

When a table is searched only required partitions are accessed

A partition must be added before data is loaded

Adding a partition

Loading data into a partition LOAD DATA LOCAL INPATH '/local/home/bigdata/HIVE-EXAMPLES/TPCHR/part.txt'
OVERWRITE INTO TABLE part PARTITION (P_BRAND='GoldenBolts');

A partition is stored in HDFS as a subfolder

Primitive Data Types
Complex Data Types
Databases
Tables
Partitions
Buckets
Views

Creating a table with 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 '|';

INSERTcan be used to populate a bucket table

Inserting into a table with buckets

Views

CREATE VIEW vcustomer AS
SELECT C_CUSTKEY, C_NAME, C_PHONE
FROM CUSTOMER
WHERE C_CUSTKEY < 5;