HQL学习小记

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;