• Data Units
• Data Types
  • Primitive Data Types
    • Timestamp
  • Complex Data Types
• Statement Execution Order/Priority
• Operations
  • DDL: Data Definition Language
    • Create Tables
    • Browse Through Tables
    • Alter & Drop Tables
    • Metastore
  • DML: Data Manipulation Language
  • SQL
    • SELECT & FILTER
    • GROUP BY
    • JOIN
    • MULTITABLE INSERT
    • STREAMING
• Operators & Functions
  • Built-in Operators
    • Relational
    • Arithmetic
    • Logical
    • Operators on Complex Types
  • Built-in Functions
    • Time
    • Condition
    • String
    • Statistic

Data Units

Database, Table, Partition, Bucket

Data Types

Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

Primitive Data Types

Timestamp

If the application needs to handle globally consistent points in time, you should use timestamps with local time zone ("Instant" semantics). If you only need to record a combination of date and time without dealing with cross-time zone issues, you can use "LocalDateTime" semantics timestamps.

Complex Data Types

Complex types built up from primitive types and other composite types.

Examples:

• Structs

-- Create
CREATE TABLE employees (
  id INT,
  name STRING,
  address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
);
-- Insert
INSERT INTO employees VALUES (1, 'John Doe', NAMED_STRUCT('street', '123 Main St', 'city', 'Springfield', 'state', 'IL', 'zip', 62701));
INSERT INTO employees VALUES (2, 'Jane Smith', NAMED_STRUCT('street', '456 Elm St', 'city', 'Metropolis', 'state', 'NY', 'zip', 10001));
-- Select & Filter
SELECT id, name, address.city AS city FROM employees;
SELECT name FROM employees WHERE address.state = 'NY';

• Maps

-- Create
CREATE TABLE departments (
  dept_name STRING,
  employees MAP<STRING, INT>
);
-- Insert
INSERT INTO departments VALUES ('HR', MAP('Alice', 101, 'Bob', 102));
INSERT INTO departments VALUES ('Engineering', MAP('Charlie', 201, 'David', 202));
-- Select & Filter
SELECT dept_name, employees['Alice'] AS alice_id FROM departments;
SELECT dept_name FROM departments WHERE employees['Charlie'] IS NOT NULL;

• Arrays

-- Create
CREATE TABLE students (
  name STRING,
  scores ARRAY<INT>
);
-- Insert
INSERT INTO students VALUES ('Alice', ARRAY(85, 90, 95));
INSERT INTO students VALUES ('Bob', ARRAY(78, 82, 88));
-- Select & Filter
SELECT name, scores[0] AS first_score FROM students;
SELECT name FROM students WHERE scores[1] > 80;

Statement Execution Order/Priority

Operations

DDL: Data Definition Language

Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

Create Tables

CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

Browse Through Tables

show tables;
show tables '.*end';
desc invites;

Alter & Drop Tables

alter table invites rename to invites_new;
alter table invites add columns (new_col INT COMMENT 'new col comm');
alter table invites replace columns (first_col INT, second_col STRING);
drop table invites;

Metastore

Reference: https://cwiki.apache.org/confluence/display/Hive/AdminManual+Metastore+Administration

Metadata is in an embedded Derby database whose disk storage location is determined by the Hive configuration variable named javax.jdo.option.ConnectionURL. By default this location is ./metastore_db (see conf/hive-default.xml).

DML: Data Manipulation Language

Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML

-- Load data from local file system into hive.
LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');
-- Load data from HDFS file/directory into hive.
LOAD DATA INPATH '/user/name/kv3.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

SQL

SELECT & FILTER

Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

select a.foo from invites a where a.ds='2008-08-15';
insert overwrite directory '/tmp/hdfs_out' select xxx;
insert overwrite local directory '/tmp/hdfs_out' select xxx;
insert overwrite table invites select xxx;

GROUP BY

GROUP BY is used to aggregate data across multiple rows based on one or more specified columns.

INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;
FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(*) WHERE a.foo > 0 GROUP BY a.bar;

JOIN

JOIN is used to combine rows from two or more tables based on a related column between them.

FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;

MULTITABLE INSERT

FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

STREAMING

FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';

Operators & Functions

Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

show functions;
desc function <func_name>;
desc function extended <func_name>;

Built-in Operators

Relational

Arithmetic

+, -, *, /, &, |, ^, ~

Logical

AND &&, OR ||, NOT !

Operators on Complex Types

• A[n]: ARRAY<INT>
• M[k]: MAP<K, V>
• S.x: S{int x}

Built-in Functions

Time

Condition

String

Statistic