drop权限 hive hive drop columns

修改表结构

将表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;

从本地文件中装载数据

[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

CLUSTERED BY

SORTED BY

SKEWED BY (col_name, col_name, ...) ON ([(col_value, col_value, ...), ...|col_value, col_value, ...])

STORED AS DIRECTORIES]   ]

ROW FORMAT

STORED AS file_format] | STORED BY

LOCATION

TBLPROPERTIES

  [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')