一. 简单的表操作
1. 创建一个表
create table test(id int);
2. 从本地导入数据
LOAD DATA LOCAL INPATH '/opt/hive/current/testdata/a.txt' INTO TABLE test;
其中a.txt 内容为:
1
2
3
4
3. 测试数据是否导入
select * from test;
二. 表中的集合数据类型
1. 创建表
CREATE TABLE employees (
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING, FLOAT>,
address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '|'
MAP KEYS TERMINATED BY ':';
2. 导入数据
load data local inpath '/opt/hive/current/testdata/b.txt' into table employees;
数据内容
John Doe,100000.0,MarySmith|Todd Jones,Federal Taxes:.2|State Taxes:.05|Insurance:.1,1 MichiganAve.|Chicago|IL|60600
MarySmith,80000.0,Bill King,Federal Taxes:.2|State Taxes:.05|Insurance:.1,100Ontario St.|Chicago|IL|60601
ToddJones,70000.0,,Federal Taxes:.15|State Taxes:.03|Insurance:.1,200 ChicagoAve.|Oak Park|IL|60700
BillKing,60000.0,,Federal Taxes:.15|State Taxes:.03|Insurance:.1,300 ObscureDr.|Obscuria|IL|60100
3. 查询操作
查询整个表:
select * from employees;
查询单个字段:
select name from employees;
查询数组:
select subordinates from employees;
select subordinates[0] from employees;
查询MAP:
select deductions from employees;
select deductions['Federal Taxes'] from employees;
查询结构体:
select address from employees;
select address.street from employees;
三. 数据库操作
1. 创建数据库
create database financials;
2. 切换数据库
use financials;
3. 在新数据库创建表
create table test (id int);
show tables;
describe extended test;
4. 查看数据库基本信息
describe database financials;
四. 管理表和外部表
1. 管理表
我们一般建立的都是管理表, 可以控制他的生命周期, 上面的我们建立的表都是管理表
2. 外部表
表的定义与数据分离,删除表的时候,数据内容不会被删除
创建表:
create external table if not exists test2(
id int
)
location '/tmp/hive';
加载数据:
load data local inpath '/opt/hive/current/testdata/a.txt' into table test2;
五. 分区表
创建分区表, 分区键不在表字段定义中
create table partition_test
(member_id string,
name string
)
partitioned by (
stat_date string,
province string);
六. 表结构修改
添加列:
alter table test add columns (name string)
删除列:
alter table test replace columns ( age int);