hive> create table person5(
> id int,
> name string,
> likes array,
> address map)
> partitioned by(age int)
> row format delimited
> fields terminated by ','
> collection items terminated by '-'
> map keys terminated by ':';
OK
Time taken: 0.312 seconds
hive> desc formatted person5;
OK
# col_name data_type comment
id int
name string
likes array
address map
# Partition Information
# col_name data_type comment
age int
# Detailed Table Information
Database: default
Owner: root
CreateTime: Tue Nov 09 13:56:56 CST 2021
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://mycluster/user/hive_remote/warehouse/person5
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1636437416
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
colelction.delim -
field.delim ,
mapkey.delim :
serialization.format ,
Time taken: 0.162 seconds, Fetched: 37 row(s)
注意：分区字段不能出现在建表脚本中。
添加数据：
hive> load data local inpath "/root/data/person01.txt" into table person5 partition(age=10);
Loading data to table default.person5 partition (age=10)
Partition default.person5{age=10} stats: [numFiles=1, numRows=0, totalSize=514, rawDataSize=0]
OK
Time taken: 1.347 seconds
查询数据：
hive> select * from person5;
OK
1 小明 1 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
2 小明 2 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
3 小明 3 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
4 小明 4 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
5 小明 5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
6 小明 6 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
7 小明 7 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong "} 10
8 小明 8 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong "} 10
9 小明 9 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 10
NULL NULL NULL NULL 10
Time taken: 0.219 seconds, Fetched: 10 row(s)
hive> load data local inpath "/root/data/person01.txt" into table person5 partition(age=20);
Loading data to table default.person5 partition (age=20)
Partition default.person5{age=20} stats: [numFiles=1, numRows=0, totalSize=514, rawDataSize=0]
OK
Time taken: 1.018 seconds
hive> select * from person5;
OK
1 小明 1 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
2 小明 2 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
3 小明 3 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
4 小明 4 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
5 小明 5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
6 小明 6 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 10
7 小明 7 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong "} 10
8 小明 8 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong "} 10
9 小明 9 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 10
NULL NULL NULL NULL 10
1 小明 1 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20
2 小明 2 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20
3 小明 3 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20
4 小明 4 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20
5 小明 5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20
6 小明 6 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20
7 小明 7 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong "} 20
8 小明 8 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong "} 20
9 小明 9 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 20
NULL NULL NULL NULL 20
Time taken: 0.17 seconds, Fetched: 20 row(s)
2.双分区
hive> create table person6(
> id int,
> name string,
> likes array,
> address map
> )
> partitioned by(age int,sex string)
> row format delimited
> fields terminated by ','
> collection items terminated by '-'
> map keys terminated by ':';
OK
Time taken: 0.354 seconds
hive> load data local inpath "/root/data/person01.txt" into table person6 partition(age=20,sex="男");
Loading data to table default.person6 partition (age=20, sex=男)
Partition default.person6{age=20, sex=男} stats: [numFiles=1, numRows=0, totalSize=514, rawDataSize=0]
OK
Time taken: 2.905 seconds
hive> select * from person6;
OK
1 小明 1 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20 男
2 小明 2 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20 男
3 小明 3 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20 男
4 小明 4 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20 男
5 小明 5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20 男
6 小明 6 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong "} 20 男
7 小明 7 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong "} 20 男
8 小明 8 ["lol","book"] {"beijing":"xisanqi","shanghai":"pudong "} 20 男
9 小明 9 ["lol","book","movie"] {"beijing":"xisanqi","shanghai":"pudong"} 20 男
NULL NULL NULL NULL 20 男
Time taken: 0.409 seconds, Fetched: 10 row(s)
