1 创建表
CREATE DATABASE IF NOT EXISTS bookcrossing;
USE bookcrossing;
CREATE TABLE IF NOT EXISTS users
(user_id INT,
location STRING,
age INT)
COMMENT 'Book Crossing users cleaned'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\073'
STORED AS TEXTFILE;
LOAD DATA LOCAL INPATH 'cda/bookcrossing-data/BX-Users-Prepro.txt' OVERWRITE INTO TABLE users;
CREATE TABLE IF NOT EXISTS users2
(user_id INT,
location STRING,
age INT)
COMMENT 'Book Crossing users2 cleaned'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\073'
STORED AS TEXTFILE;
LOAD DATA LOCAL INPATH 'cda/bookcrossing-data/BX-Users-Prepro-Sample.txt' OVERWRITE INTO TABLE users2;
CREATE EXTERNAL TABLE IF NOT EXISTS books
(isbn STRING,
title STRING,
author STRING,
year INT,
publisher STRING,
image_s STRING,
image_m STRING,
image_l STRING)
COMMENT 'Book crossing books list cleaned'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\073'
STORED AS TEXTFILE
LOCATION '/user/hduser/book-crossing/books';
2 简单查询
2.1 启动Hive
$ hive
2.2 查询年龄在18~34岁之间的用
Hive使用一个MapReduce job在背后执行数据过滤操作
hive> select user_id,location,age from users where age>18 and age<34 limit 10;
2.2 统计满足上一条件的用户总数量(即有多少个用户的年龄在18~34岁之间)
Hive将这个查询转换为一个MapReduce计算
hive> select count(*) from users where age>18 and age<34;
2.3 按年龄分组统计用户数量:
hive> select age, count(*) FROM users GROUP BY age;
2.4 按年龄分组统计用户数量,并对结果按用户数量由高到低排序:
hive> SELECT age, count(*) as c FROM users GROUP BY age ORDER BY c DESC;
2.5 将查询结果保存到表tmp_users
hive> create table tmp_users as select user_id,location,age from users where age>18 and age<34;
2.6 将查询结果插入到一个已经存在的表中
hive> insert into table tmp_users select user_id,location,age from users where age>33 and age<51;
2.7 使用查询在一张表上创建视图
hive> create view tmp_users_view as select user_id,location,age from tmp_users where age>18 and age<34;