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;