Tutorial for HiveQL basics

hive-basic.sql

/* How to create a database */
CREATE DATABASE test_db;
-- Show the query in the history

/* If try to create a database that already exists, it fails */
CREATE DATABASE test_db;
-- Explain the query result
-- Show the query in the history and that indicates the failure

/* In order to avoid failure in scripting, we can use IF NOT EXISTS keyword */
CREATE DATABASE IF NOT EXISTS test_db;

/* To get information on a database */
DESCRIBE DATABASE test_db;

/* To get a list of databses */
SHOW DATABASES;

/* list databases */
SHOW DATABASES LIKE 'bdsf2001_*'

/* In order to drop a database */
DROP DATABASE test_db;

/* If try to drop a database that doesn't exist, you get an error again */
DROP DATABASE test_db;

/* In order to avoid failure in scripting, we can use IF EXISTS keyword */
DROP DATABASE IF EXISTS test_db;

/* A database can be created with some properties */
CREATE DATABASE bdss2001_test
WITH DBPROPERTIES('creator' = 'John Doe', 'date' = '2010-10-18');

/* The properties don't show up in the normal describe and we should use extended version. */
DESCRIBE DATABASE bdss2001_test;
DESCRIBE DATABASE EXTENDED bdss2001_test;

/* Simple create table statement */
CREATE TABLE students (
	id int,
    first_name	string,
    last_name	string
);

/* Get information from the table */
DESCRIBE students;
DESCRIBE EXTENDED students;
DESCRIBE FORMATTED students;

/* Simple insert statement */
INSERT INTO students VALUES (1, 'John', 'Doe');
/* Query records from the table */
SELECT * FROM students
LIMIT 10;
-- Always use a limit to avoid accidentally getting all the data of a huge table

/* Insert a second student */
INSERT INTO students VALUES (1, 'John', 'Smith');

-- Check the output on HDFS /user/hive/warehouse/bdss2001_test.db/students
-- There is one file per insert
-- Check the YARN resource manager
-- There is one MapReduce job per SQL command

/* 
In order to make Hive to avoid MapReduce, you can use 
	SET hive.exec.mode.local.auto=false;
It asks Hive for a configured size of data, use local processing instead of MR.
It will speed up small data queries.
*/

/** CLEAN UP **/
DROP DATABASE bdss2001_test CASCADE;