hive 分区表和分桶表
1、创建分区表
hive> create table weather_list(year int,data int) partitioned by (createtime string,area string) row format delimited fields terminated by ",";
修改表：
hive> alter table weather_list change data new_data int; hive> alter table weather_list change year new_year int;
1.1、加载数据
hive> load data local inpath '/home/hadoop/sampler/w2.csv' into table weather_list partition(createtime='2011-01-01',area='bj'); Loading data to table busdata.weather_list partition (createtime=2011-01-01, area=bj) OK Time taken: 1.455 seconds hive> load data local inpath '/home/hadoop/sampler/w3.csv' into table weather_list partition(createtime='2011-01-02',area='sc'); Loading data to table busdata.weather_list partition (createtime=2011-01-02, area=sc) OK Time taken: 1.394 seconds hive> load data local inpath '/home/hadoop/sampler/w4.csv' into table weather_list partition(createtime='2011-01-03',area='tj'); Loading data to table busdata.weather_list partition (createtime=2011-01-03, area=tj) OK Time taken: 1.568 seconds hive> load data local inpath '/home/hadoop/sampler/w4.csv' into table weather_list partition(createtime='2011-01-04',area='sz'); Loading data to table busdata.weather_list partition (createtime=2011-01-04, area=sz) OK Time taken: 1.209 seconds hive> load data local inpath '/home/hadoop/sampler/w5.csv' into table weather_list partition(createtime='2011-01-05',area='gz'); Loading data to table busdata.weather_list partition (createtime=2011-01-05, area=gz) OK Time taken: 1.148 seconds hive> load data local inpath '/home/hadoop/sampler/w5.csv' into table weather_list partition(createtime='2011-01-01',area='gz'); Loading data to table busdata.weather_list partition (createtime=2011-01-01, area=gz) OK Time taken: 1.278 seconds
partition的分区字段体现在存储目录上，与文件中的实际存储字段没有关系。
hive> dfs -ls /hive/warehouse/busdata.db/weather_list/createtime=2011-01-01/area=gz; Found 6 items -rw-r--r-- 1 hadoop supergroup 18018 2019-03-05 22:14 /hive/warehouse/busdata.db/weather_list/createtime=2011-01-01/area=gz/w1.csv -rw-r--r-- 1 hadoop supergroup 18022 2019-03-05 22:14 /hive/warehouse/busdata.db/weather_list/createtime=2011-01-01/area=gz/w2.csv -rw-r--r-- 1 hadoop supergroup 18028 2019-03-05 22:14 /hive/warehouse/busdata.db/weather_list/createtime=2011-01-01/area=gz/w3.csv -rw-r--r-- 1 hadoop supergroup 18022 2019-03-05 22:14 /hive/warehouse/busdata.db/weather_list/createtime=2011-01-01/area=gz/w4.csv -rw-r--r-- 1 hadoop supergroup 18027 2019-03-05 22:12 /hive/warehouse/busdata.db/weather_list/createtime=2011-01-01/area=gz/w5.csv -rw-r--r-- 1 hadoop supergroup 18027 2019-03-05 22:14 /hive/warehouse/busdata.db/weather_list/createtime=2011-01-01/area=gz/w5_copy_1.csv
hive> dfs -ls /hive/warehouse/busdata.db/weather_list/createtime=2011-01-01; Found 2 items drwxr-xr-x - hadoop supergroup 0 2019-03-05 22:09 /hive/warehouse/busdata.db/weather_list/createtime=2011-01-01/area=bj drwxr-xr-x - hadoop supergroup 0 2019-03-05 22:14 /hive/warehouse/busdata.db/weather_list/createtime=2011-01-01/area=gz
1.2、显示分区信息
hive> show partitions weather_list; OK createtime=2010-01-01/area=bj createtime=2010-01-01/area=sh createtime=2010-01-01/area=yn createtime=2010-01-02/area=sh createtime=2011-01-01/area=bj createtime=2011-01-01/area=gz createtime=2011-01-02/area=sc createtime=2011-01-03/area=tj createtime=2011-01-04/area=sz createtime=2011-01-05/area=gz Time taken: 0.584 seconds, Fetched: 10 row(s)
1.3、分区列属于表的正式列，但是文件中没有存储分区列信息。分区列的信息是从目录中读取的。
hive> select * from weather_list where area='bj' limit 10; OK 1999 71 2010-01-01 bj 1994 57 2010-01-01 bj 1995 33 2010-01-01 bj 1993 44 2010-01-01 bj 1994 99 2010-01-01 bj 1994 83 2010-01-01 bj 1995 59 2010-01-01 bj 1991 32 2010-01-01 bj 1992 74 2010-01-01 bj 2000 56 2010-01-01 bj Time taken: 2.527 seconds, Fetched: 10 row(s)
2、分桶表
2.1、检查分桶属性，设置分桶属性是为了使用hive来自动分桶，因为分桶是根据分桶字段和数量进行hash取余，也可以自己分桶后导入。
hive> set hive.enforce.bucketing; hive.enforce.bucketing=false hive> set hive.enforce.bucketing=true; hive> set hive.enforce.bucketing; hive.enforce.bucketing=true
2.2、建立分桶表
hive> create table bucket_userinfo(userid int,username string) clustered by (userid) sorted by (userid asc) into 2 buckets row format delimited fields terminated by ","; hive> desc formatted bucket_userinfo; OK # col_name data_type comment userid int username string # Detailed Table Information Database: busdata OwnerType: USER Owner: hadoop CreateTime: Wed Mar 06 23:11:37 CST 2019 LastAccessTime: UNKNOWN Retention: 0 Location: hdfs://bigdata-senior01.home.com:9000/hive/warehouse/busdata.db/bucket_userinfo Table Type: MANAGED_TABLE Table Parameters: COLUMN_STATS_ACCURATE {\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"userid\":\"true\",\"username\":\"true\"}} SORTBUCKETCOLSPREFIX TRUE bucketing_version 2 numFiles 0 numRows 0 rawDataSize 0 totalSize 0 transient_lastDdlTime 1551885097 # 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: 2 Bucket Columns: [userid] Sort Columns: [Order(col:userid, order:1)] Storage Desc Params: field.delim , serialization.format , Time taken: 0.379 seconds, Fetched: 34 row(s)
2.3、使用hive自动分桶，这种情况是针对源数据已经导入hive。
hive> insert overwrite table bucket_userinfo select userid,username from userinfo; 然后hive启动作业分桶导入数据，本例中分两个桶，所以最终会根据userid的奇偶生成两个文件。
hive> dfs -ls /hive/warehouse/busdata.db/bucket_userinfo; Found 2 items -rw-r--r-- 1 hadoop supergroup 106 2019-03-06 23:13 /hive/warehouse/busdata.db/bucket_userinfo/000000_0 -rw-r--r-- 1 hadoop supergroup 103 2019-03-06 23:13 /hive/warehouse/busdata.db/bucket_userinfo/000001_0
hive> dfs -cat /hive/warehouse/busdata.db/bucket_userinfo/000000_0; 2,xu.dm 4,user123 6,user2 8,user4 10,user6 14,user8 16,user10 18,user12 20,user14 22,soldier2 24,soldier4 hive> dfs -cat /hive/warehouse/busdata.db/bucket_userinfo/000001_0; 1,admin 3,myuser 5,user1 7,user3 9,user5 13,user7 15,user9 17,user11 19,user13 21,soldier1 23,soldier3
hive> select * from bucket_userinfo; OK 2 xu.dm 4 user123 6 user2 8 user4 10 user6 14 user8 16 user10 18 user12 20 user14 22 soldier2 24 soldier4 1 admin 3 myuser 5 user1 7 user3 9 user5 13 user7 15 user9 17 user11 19 user13 21 soldier1 23 soldier3 Time taken: 0.238 seconds, Fetched: 22 row(s)
2.4、从外部文件导入数据，结果与上面一样
hive> create table bucket_userinfo2(userid int,username string) clustered by (userid) sorted by (userid) into 2 buckets row format delimited fields terminated by ",";
hive> load data local inpath '/home/hadoop/userinfo2.txt' into table bucket_userinfo2;
hive> select * from bucket_userinfo2; OK 2 xu.dm 4 user123 6 user2 8 user4 10 user6 14 user8 16 user10 18 user12 20 user14 22 soldier2 24 soldier4 1 admin 3 myuser 5 user1 7 user3 9 user5 13 user7 15 user9 17 user11 19 user13 21 soldier1 23 soldier3
hive>dfs -ls /hive/warehouse/busdata.db/bucket_userinfo2; Found 2 items -rw-r--r-- 1 hadoop supergroup 106 2019-03-07 22:44 /hive/warehouse/busdata.db/bucket_userinfo2/000000_0 -rw-r--r-- 1 hadoop supergroup 103 2019-03-07 22:44 /hive/warehouse/busdata.db/bucket_userinfo2/000001_0
2.4、对桶数据采样
hive> select * from bucket_userinfo tablesample(bucket 1 out of 2 on userid); OK 2 xu.dm 6 user2 10 user6 20 user14 3 myuser 7 user3 17 user11 19 user13 21 soldier1 Time taken: 0.077 seconds, Fetched: 9 row(s)
