hive初学 有map格式(各种建表语句)

hive和beeline(hiveserver2)的区别:
hive不需要启动服务再访问
beeline需要先启动服务端再访问客户端
beeline在查询效率上比hive高，beeline不支持update和delete

hive：
1、连接方式
    hive -h hostname -p port
2、加载文件
    
3、库级操作
   同mysql
4、表级操作
   默认内部表，会默认在指定的存储空间中建立对应文件夹
   只要把文件放入，表就可以读取到数据(需要和表结构匹配)
   分区表，会在表下创建文件夹，数据在各分区文件夹下 
5、行级操作 

create table employee(
name string,
address array<string>,
personalInfo array<string>,
technol map<string,int>,
jobs map<string,string>)
row format delimited
fields terminated  by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

drop table employee_id;
create table employee_id(
name string,
id int,
address array<string>,
info struct<sex:string,age:int>,
workAndSal map<String,int>,
jobAndRole map<string,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';


create table employee_id(
name string,
id int,
address array<string>,
Info struct<sex:string,age:int>,
technol map<string,int>,
jobs map<string,string>)
row format delimited
fields terminated  by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

 create table employee_partiton(
 name string,
 address array<string>,
 info struct<gender:string,age:int>,
 technol map<string,int>,
 jobs map<string,string>)
 partitioned by (country string,add string)
 row format delimited
 fields terminated by '|'
 collection items terminated by ','
 map keys terminated by ':'
 lines terminated by '\n';
 
 create table employee_partiton(
 name string,
 address array<string>,
 info struct<gender:string,age:int>,
 technol map<string,int>,
 jobs map<string,string>)
 partitioned by (country string,add string)
 row format delimited
 fields terminated by '|'
 collection items terminated by ','
 map keys terminated by ':'
 lines terminated by '\n';



load data local inpath '/software/employee.txt'
into table employee_partition
partition (country='china',add='LiaoNing');

load data inpath '/opt/hive/warehouse/hivetest1.db/employee/employee.txt' 
into table employee_partiton 
partition(country="china",add="nanjing");
select * from employee_partiton where add='nanjing';
select * from employee_partiton where country='China';

create table p_test(
pid int,
pname string)
partitioned by (person string)
row format delimited fields
terminated by ','
lines terminated by '\n';

insert into p_test partition(person='bob') values(4,'d'),(5,'e'),(6,'f');

create external table emp_id(
name string,
id int,
address array<string>,
Info struct<gender:string,age:int>,
technol map<string,int>,
jobs map<string,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/usr/test/employee1';

create external table emp_bucket(
name string,
id int,
address array<string>,
Info struct<gender:string,age:int>,
technol map<string,int>,
jobs map<string,string>)
clustered by(id) into 3 buckets
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/usr/test/bucket'
set hive.enforce.bucketing=ture;