Hive-HelloWord

一、概述

• 创建数据库和表。
• 上传本地文件到hdfs。
• hive基本查询。

二、流程

1、新建test.log文档

vi test.log

1,小明1,lol-book-movie,changsha:meixihu-changsha:shizhengfu
2,小明1,lol-book-movie,changsha:meixihu-changsha:shizhengfu
3,小明1,lol-book-movie,changsha:meixihu-changsha:shizhengfu
4,小明1,lol-book-movie,changsha:meixihu-changsha:shizhengfu
5,小明1,lol-book-movie,changsha:meixihu-changsha:shizhengfu
6,小明1,lol-book-movie,changsha:meixihu-changsha:shizhengfu
7,小明1,lol-book-movie,changsha:meixihu-changsha:shizhengfu
8,小明1,lol-book-movie,changsha:meixihu-changsha:shizhengfu
9,小明1,lol-book-movie,changsha:meixihu-changsha:shizhengfu
10,小明1,lol-book-movie,changsha:meixihu-changsha:shizhengfu

2、创建数据库和表

# 进入hive
hive

# 创建库
hive> CREATE DATABASE test;

# 创建表(分隔符根据test.log的格式来定)
# FIELDS TERMINATED BY 属性分隔符
# COLLECTION ITEMS TERMINATED BY 集合分隔符
# MAP KEYS TERMINATED BY map分隔符
# LINES TERMINATED BY 分隔符

hive> use test;
hive> CREATE TABLE psn (
    > id int,
    > name string,
    > likes array<string>,
    > address map<string,string>
    > )
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY ','
    > COLLECTION ITEMS TERMINATED BY '-'
    > MAP KEYS TERMINATED BY ':'
    > LINES TERMINATED BY '\n';

3、从本地选择文件(添加LOCAL)插入数据

hive> LOAD DATA LOCAL INPATH '/data/test.log' INTO TABLE psn;

• /data/hive/warehouse：hive-site.sh中配置的hive.metastore.warehouse.di  r

<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/data/hive/warehouse</value>
</property>

• test.db：创建的数据库
• psn：创建的表

4、查询测试

hive> select * from psn;
OK
1	小明1	["lol","book","movie"]	{"changsha":"meixihu"}
2	小明1	["lol","book","movie"]	{"changsha":"meixihu"}
3	小明1	["lol","book","movie"]	{"changsha":"meixihu"}
4	小明1	["lol","book","movie"]	{"changsha":"meixihu"}
5	小明1	["lol","book","movie"]	{"changsha":"meixihu"}
6	小明1	["lol","book","movie"]	{"changsha":"meixihu"}
7	小明1	["lol","book","movie"]	{"changsha":"meixihu"}
8	小明1	["lol","book","movie"]	{"changsha":"meixihu"}
9	小明1	["lol","book","movie"]	{"changsha":"meixihu"}
10	小明1	["lol","book","movie"]	{"changsha":"meixihu"}
Time taken: 6.786 seconds, Fetched: 10 row(s)
hive>