E-MapReduce:Basic Hive operations

Prerequisites

Before you begin, ensure that you have:

• An EMR cluster with the Hive service selected. For more information, see Create a cluster

Open the Hive CLI

1. Log on to the EMR cluster over SSH. See Log on to a cluster.

2. Switch to the hadoop user:

   su hadoop

3. Start the Hive CLI:

   hive

Manage databases

The following examples use a database named testdb.

Create a database

create database if not exists testdb;

If the output contains OK, the database is created.

View database details

desc database testdb;

Switch to a database

use testdb;

Delete a database

drop database if exists testdb;

If the output contains OK, the database is deleted.

Manage tables

The following examples use a table named t with two columns: id (bigint) and value (string).

Create a table

create table if not exists t (id bigint, value string);

If the output contains OK, the table is created.

View table details

desc formatted t;

List all tables

show tables;

Expected output:

OK
t

Delete a table

drop table if exists t;

If the output contains OK, the table is deleted.

Run SQL statements

The following examples insert a row into t and then query it.

Insert a row

insert into table t select 1, 'value-1';

Expected output:

OK
Time taken: 14.73 seconds

Query rows

select * from t limit 10;

Expected output:

OK
1       value-1
Time taken: 11.48 seconds, Fetched: 1 row(s)

Aggregate data

select value, count(id) from t group by value;

Expected output:

OK
value-1 1
Time taken: 20.11 seconds, Fetched: 1 row(s)