hive> use gamedw;
OK
Time taken: 0.049 seconds
hive> select current_database();
OK
gamedw
Time taken: 0.485 seconds, Fetched: 1 row(s)
scala> hivecon.sql("select * from gamedw.customers where city='shenzhen'").show
+--------+---+---+----+
|custname|sex|age|city|
+--------+---+---+----+
+--------+---+---+----+
scala> hivecon.sql("insert overwrite table customers partition(city='shenzhen') select custname,sex,age from customers where city='nanjing'").show
18/09/10 17:24:18 ERROR hdfs.KeyProviderCache: Could not find uri with key [dfs.encryption.key.provider.uri] to create a keyProvider !!
18/09/10 17:24:18 WARN hive.log: Updating partition stats fast for: customers
18/09/10 17:24:18 WARN hive.log: Updated size to 96
++
||
++
++
scala> hivecon.sql("select * from gamedw.customers where city='shenzhen'").show
+---------------+---+---+--------+
| custname|sex|age| city|
+---------------+---+---+--------+
|tianyt_touch100| 1| 50|shenzhen|
| wangwu| 1| 85|shenzhen|
| zhangsan| 1| 20|shenzhen|
| liuqin| 0| 56|shenzhen|
| wangwu| 0| 47|shenzhen|
| liuyang| 1| 32|shenzhen|
| hello| 0|100|shenzhen|
+---------------+---+---+--------+
scala> hivecon.sql("insert into table customers partition(city='shenzhen') select custname,sex,age from customers where city='nanjing'").show
18/09/10 17:25:44 WARN hive.log: Updating partition stats fast for: customers
18/09/10 17:25:44 WARN hive.log: Updated size to 192
++
||
++
++
hive> alter table customers drop partition(city='luohe');
Dropped the partition city=luohe
OK
Time taken: 0.541 seconds
hive> alter table account clustered by (platid) sorted by(dateid) into 100 buckets;
OK
Time taken: 0.433 seconds
hive> show create table account;
OK
createtab_stmt
CREATE TABLE `account`(
`accountname` bigint,
`accid` bigint,
`platid` int,
`dateid` int,
`createtime` string)
COMMENT 'Imported by sqoop on 2018/08/30 14:07:03'
CLUSTERED BY (
platid)
SORTED BY (
dateid ASC)
INTO 100 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'field.delim'='u0001',
'line.delim'='
',
'serialization.format'='u0001')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://localhost:9000/user/hive/warehouse/gamedw.db/account'
TBLPROPERTIES (
'last_modified_by'='root',
'last_modified_time'='1536573809',
'numFiles'='4',
'numRows'='0',
'rawDataSize'='0',
'totalSize'='3967930',
'transient_lastDdlTime'='1536573809')
Time taken: 0.25 seconds, Fetched: 32 row(s)
altere table .... archive partition会将这个分区的文件打成一个hadoop压缩包(har)文件,这样仅仅是降低文件数据,降低namenode的压力,而不会减少任何存储空间。
hive> set hive.archive.enabled=true;
hive> alter table customers archive partition(city='shenzhen');
intermediate.archived is hdfs://localhost:9000/user/hive/warehouse/gamedw.db/customers/city=shenzhen_INTERMEDIATE_ARCHIVED
intermediate.original is hdfs://localhost:9000/user/hive/warehouse/gamedw.db/customers/city=shenzhen_INTERMEDIATE_ORIGINAL
Creating data.har for hdfs://localhost:9000/user/hive/warehouse/gamedw.db/customers/city=shenzhen
in hdfs://localhost:9000/user/hive/warehouse/gamedw.db/customers/city=shenzhen/.hive-staging_hive_2018-09-10_18-09-33_034_2042188454765235088-1/-ext-10000/partlevel
Please wait... (this may take a while)
Moving hdfs://localhost:9000/user/hive/warehouse/gamedw.db/customers/city=shenzhen/.hive-staging_hive_2018-09-10_18-09-33_034_2042188454765235088-1/-ext-10000/partlevel to hdfs://localhost:9000/user/hive/warehouse/gamedw.db/customers/city=shenzhen_INTERMEDIATE_ARCHIVED
Moving hdfs://localhost:9000/user/hive/warehouse/gamedw.db/customers/city=shenzhen to hdfs://localhost:9000/user/hive/warehouse/gamedw.db/customers/city=shenzhen_INTERMEDIATE_ORIGINAL
Moving hdfs://localhost:9000/user/hive/warehouse/gamedw.db/customers/city=shenzhen_INTERMEDIATE_ARCHIVED to hdfs://localhost:9000/user/hive/warehouse/gamedw.db/customers/city=shenzhen
OK
Time taken: 6.035 seconds