【编程学习】大数据平台基础课程要点总结——Hive基础操作

在此附上老师教学课件地址:

Hive

  • Start Hive from a Terminal: hive
  • Execute command within Hive dfs -ls /;
  • Exit Hive: exit;
  • hive脚本可以保存为.hql文件运行hive -f /path/to/file/withqueries.hql

DDL

1
2
3
4
5
6
7
8
9
10
SHOW DATABASES;

CREATE DATABASE IF NOT EXISTS myname;
CREATE DATABASE IF NOT EXISTS myname LOCATION '/user/yanfei/hive'; 
-- Location here is the HDFS path where the data for the database will be stored. Default is /user/hive/warehouse/.
DROP DATABASE IF EXISTS myname;

SHOW DATABASES;.
SHOW DATABASES LIKE "d*";.
USE myname.
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE IF NOT EXISTS myname.employees (
    name STRING COMMENT 'Employee name',
    salary FLOAT COMMENT 'Employee salary'
) COMMENT 'Description of the table' 
TBLPROPERTIES ('creator'='me', 'created_at'='2012-01-02 10:00:00');

SHOW TABLES;
DESCRIBE myname.employees;

ALTER TABLE employees RENAME TO employees;
ALTER TABLE employees ADD COLUMNS (age int);

DROP TABLE employees;
1
2
3
SHOW FUNCTIONS [like "str*"];
SHOW DATABASES [like "h*"];
SHOW TABLES [like "m*"];
1
2
3
DESCRIBE DATABASE myname;
DESCRIBE employees;
DESCRIBE FUNCTION like;

DML

假设现在有文件/home/usr/data/stocks.txt

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
use myname;

create external table if not exists stocks (
    symbol string, 
    ymd string, 
    price_open float, 
    price_high float, 
    price_low float, 
    price_close float, 
    volume int, 
    price_adj_close float )
row format delimited fields terminated by ','
LOCATION '/data/';

LOAD DATA LOCAL INPATH '/home/usr/data/stocks.txt' OVERWRITE INTO TABLE stocks; 

-- LOCAL 代表文件在服务器文件系统，去掉LOCAL后，代表文件在HDFS
  • Hive stores tables files by default at /user/hive/warehouse location on HDFS.

Hive&Python

1
2
3
4
5
6
7
8
9
10
11
#! /usr/bin/env python3
# 一个吧数据中的时间变为工作日虚拟变量的过程
# 位置在：hdfs:///user/code/weekday_mapper.py
import sys
import datetime

for line in sys.stdin:
  line = line.strip()
  userid, movieid, rating, unixtime = line.split('\t')
  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
  print('\t'.join([userid, movieid, rating, str(weekday)]))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
use myname;

-- 创建一个新表，准备放python处理后数据
CREATE EXTERNAL TABLE if not exists u_data_new (
  userid INT,
  movieid INT,
  rating INT,
  weekday INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

-- 创建一个新表，准备放原始数据
CREATE EXTERNAL TABLE if not exists u_data (
  userid INT,
  movieid INT,
  rating INT,
  unixtime INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';
-- 放原始数据
LOAD DATA LOCAL INPATH '/home/usr/data/u.data'
OVERWRITE INTO TABLE u_data;

-- 添加python处理文件
add FILE hdfs:///user/code/weekday_mapper.py; # 

-- 用python处理文件后加入新数据库
INSERT OVERWRITE TABLE u_data_new
SELECT
  TRANSFORM (userid, movieid, rating, unixtime)
  USING 'python3 weekday_mapper.py'
  AS (userid, movieid, rating, weekday)
FROM u_data;

-- 查询新数据库数据
SELECT weekday, COUNT(*)
FROM u_data_new
GROUP BY weekday;

基本知识点

  1. hive内部表和外部表的区别：未被external修饰的是内部表，被external修饰的为外部表。其中，内部表数据由Hive自身管理，存储在hive.metastore.warehouse.dir（默认：/user/hive/warehouse）;外部表数据由HDFS管理，存储位置由自己制定，默认在HDFS上的/user/hive/warehouse文件夹下。删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除。
  2. sort by 和 order by 的区别：order by 会对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序）只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。sort by不是全局排序，其在数据进入reducer前完成排序. 因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1， 则sort by只保证每个reducer的输出有序，不保证全局有序。