Hive创建分区表

如下
1 在hive上创建一个外部表,四个字段,ip地址,项目名称,ctime 创建时间, content 类型为struct.struct可以理解为hive的自定义类型,格式为 struct<key:type,key:type,key,type>. 本例中content类型为struct.其中又嵌套了一个struct类型的字段properties
2PARTITIONED BY(logday string) 指定分区字段,这个名称logday可以自定义.此时表中并没有分区.因为还没有导入数据.
3LOCATION '/sources/news/'; 指定表原始文件的位置

CREATE EXTERNAL TABLE if not exists news1 (
    ip string,
    project string,
    ctime string,
    content struct<distinct_id:string,event:string,properties:struct<model:string,network_type:string,is_charging:string,app_version:string,element_name:string,element_page:string,carrier:string,os:string,imei:string,battery_level:string,screen_width:string,screen_height:string,device_id:string,client_time:string,ip:string,manufacturer:string>>
)
PARTITIONED BY(logday string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION '/sources/news/';

查看表结构

hive (ods_db_news)> desc news1;
OK
ip                      string                  from deserializer
project                 string                  from deserializer
ctime                   string                  from deserializer
content                 struct<distinct_id:string,event:string,properties:struct<model:string,network_type:string,is_charging:string,app_version:string,element_name:string,element_page:string,carrier:string,os:string,imei:string,battery_level:string,screen_width:string,screen_height:string,device_id:string,client_time:string,ip:string,manufacturer:string>>      from deserializer
logday                  string

表创建完成,需要向表中导入数据,由于是分区表,不能用load into方法.

hive (ods_db_news)> show partitions news1;
OK
hive (ods_db_news)> alter table ods_db_news.news1 drop if exists PARTITION(logday='20201103');
OK
hive (ods_db_news)> alter table ods_db_news.news1 add partition (logday='20201103') location 'hdfs://mypc01:8020/sources/news/20201103/';
OK
hive (ods_db_news)> show partitions news1;
OK
logday=20201103
hive (ods_db_news)> select * from news1 limit 5;
OK
xx.1x6.208.130  news    1604402197754   {"distinct_id":"4291","event":"AppPageView","properties":{"model":"iPad Air2","network_type":"","is_charging":"","app_version":"1.2","element_name":"","element_page":"我的","carrier":"中国电信","os":"Windows","imei":"673172260816","battery_level":"7","screen_width":"1024","screen_height":"320","device_id":"TAIJIXXXXA46DE0A18EB8","client_time":"2020-11-03 19:16:17","ip":"36.63.118.197","manufacturer":"Apple"}}      20201103