hive基础知识2(hive 0.1.4)，

hive基础知识2(hive 0.1.4)，

修改表结构

将表t1的列名name 修改为username ,增加列注释 new name

alter table t1 change column name username string comment 'new name';

增加列

alter table t1 add colums(gender string)

删除列，不建议这么做，可以重新创建一个表

假设t1原来有name,age,sex三列，下面语句删除了age列

alter table t1 rep;ace columns(name string,sex int);

这里删除了表的元数据，但是表里的数据存在hdfs上，并没有删除，select * from t1  数据会有问题，因为表只有两列，而表里的数据还是有三列，所以会把原来age的数据解析为sex的值

表类型

管理表 ，也称为内部表，表的数据在hdfs上

desc formatted t1  ;

会显示Table Type: MANAGED_TABLE

drop内部表，表里的数据也会删除 

指定表数据存放的位置（不指定，默认是在/usr/hive/warehouse）

create table t3(id int)location '/home/t3';

内部表转换为外部表（删除表是数据还在）

alter table t3  set tblproperties('EXTERNAL'='TRUE')

注意：EXTERNAL'='TRUE必须大写，小写虽然不会报错，但不会生效

外部表，删除表后，hdfs的数据还在，以后新建表，只要表名一样，表的数据还在

创建外部表

create external table t5(id int);

将数据上传至hadoop的t5表下，查询t5表就有数据了

hadoop fs -put log /user/hive/warehouse/t5

分区表

分区可以理解为分类，通过分类把不同类型的数据放到不同的目录下。 分类的标准就是分区字段，可以一个，也可以多个。 分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段，就会全部扫描。

create table t2(name string ,age int) partitioned by (class string)

增加分区字段

alter table t2 add partition(class='job1');

查看分区

show partitions t2;

向分区中上传数据

hadoop fs put log /user/hive/warehouse/t2/class=job1

查看分区中的数据

select * from t2 where class='job1'

还可以指定分区的路径

alter table t2 add partition(class='job1') location /tables/t2/job1

如果在hdfs上新建目录/user/hive/warehouse/t2/class=job2，再向该目录下上传数据，虽然hdfs上有class=job2这个目录，但是show partitions t2 显示不出job2这个分区，也不能从job2这个分区中查出数据，所以还是需要增加t2的job2分区

hadoop fs put log /user/hive/warehouse/t2/class=job2/

alter table t2 add partition(class='job2')

删除分区

alter table t2 drop partition(class='job2')

设置分区不能被删除

alter table t2  partition(class='job2') enable no_drop;

设置分区可以被删除

alter table t2  partition(class='job2') disable no_drop;

设置分区不能被查询

alter table t2  partition(class='job2') enable offline

设置分区不能被查询后，不能执行select * from t2,必须指定分区 ，比如select * from t2 where class='job1'

设置分区能被查询

alter table t2  partition(class='job2') disable offline

创建多级分区表

create table t3(id int) partitioned by (class string,city string);

增加分区

alter table t3 add partition(class='job1',city='sh');

上传数据

hadoop fs -put log /user/hive/warehouse/t3/class=job1/city=sh

查询数据

select * from t3 where class='job1' and city='sh';

桶表是对数据进行哈希取值，然后放到不同文件中存储。 创建表 create table bucket_table(id string) clustered by(id) into 4 buckets; 加载数据 set hive.enforce.bucketing = true; insert into table bucket_table select name from stu;（不覆盖原有数据） insert overwrite table bucket_table select name from stu;(覆盖原有数据) 数据加载到桶表时，会对字段取hash值，然后与桶的数量取模。把数据放到对应的文件中。 注意： 物理上，每个桶就是表(或分区）目录里的一个文件 一个作业产生的桶(输出文件)和reduce任务个数相同
桶表的抽样查询 select * from bucket_table tablesample(bucket 1 out of 4 on id); tablesample是抽样语句 语法解析：TABLESAMPLE(BUCKET x OUT OF y) y必须是table总bucket数的倍数或者因子。 hive根据y的大小，决定抽样的比例。 例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。 例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。
hive视图的操作
使用视图可以降低查询的复杂度 视图的创建 create view v1 as select  t1.name from t1;
show tables 可以查看多以v1表
视图的删除 drop view if exists v1;
查询视图，视图不存储数据，查询数据还是从表t1中查询 select * from v1;

hive索引的操作
创建索引 create index t1_index on table t1(id) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild in table t1_index_table; as指定索引器， index t1_index是索引名称，on table t1(id) 表示对表t1中的id字段建立索引，t1_index_table是索引信息存储表 重建索引  alter index t1_index on t1 rebuild; 重建之后会执行mapreduce程序，遍历表中的数据建立索引，重建完成之后可以执行select * from t1_index_table 查看产生的索引信息 显示索引 show formatted index on t1; 删除索引 drop index  if exists t1_index on t1;
从本地文件中装载数据 LOAD DATA  INPATH '...' [OVERWRITE] INTO TABLE t2[PARTITION(province='beijing')];
从hdfs中转载数据 load data inpath '/path/data' [overwrite] into table t2 [partition(province='beijing')];  实际上是将hdfs的/path/data的数据移动到/user/hive/warehouse/t2/province=beijing/目录下
通过查询表装载数据 INSERT OVERWRITE TABLE t2 PARTITION (province='beijing') SELECT * FROM xxx WHERE xxx

FROM t4 INSERT OVERWRITE TABLE t3 PARTITION (class='cl1') SELECT id,age WHERE name='ww' INSERT OVERWRITE TABLE t3 PARTITION (class='cl2') SELECT id,age WHERE name='kk'
INSERT OVERWRITE TABLE t3 PARTITION (class='cl3') SELECT id,age WHERE name='wk'
完整建表语法 CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name   [(col_name data_type [COMMENT col_comment], ...)]     [COMMENT table_comment]     [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]     [CLUSTERED BY (col_name, col_name, ...)     [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]     [SKEWED BY (col_name, col_name, ...) ON ([(col_value, col_value, ...), ...|col_value, col_value, ...])     [STORED AS DIRECTORIES]   ]   [ [ROW FORMAT row_format]     [STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]   ]   [LOCATION hdfs_path]   [TBLPROPERTIES (property_name=property_value, ...)]        [AS select_statement]  (Note: not supported when creating external tables.)
hive表可以映射到别的集群的hdfs上面，只要在location的地方写hdfs的绝对路径 CREATE EXTERNAL TABLE `kafka_hive_daily_count_old`(
  `db_name` string COMMENT 'database name', 
  `tb_partition` string COMMENT 'table&partition', 
  `num_rows` string COMMENT 'record count')
PARTITIONED BY ( 
  `inc_day` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hftp://10.116.77.16:50070/result/o_inc_sgs_core/dm_kafka_hive_daily_count'
TBLPROPERTIES (
  'transient_lastDdlTime'='1487728687')