本文共 6884 字,大约阅读时间需要 22 分钟。
1.查看创建表的信息 【show create table】
hive> show create table student;OKcreatetab_stmtCREATE TABLE `student`( `age` int, `name` string)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION 'hdfs://hadoop01:9000/user/hive/warehouse/test.db/student'TBLPROPERTIES ( 'numFiles'='0', 'last_modified_by'='root', 'last_modified_time'='1439964845', 'COLUMN_STATS_ACCURATE'='false', 'transient_lastDdlTime'='1439964845', 'numRows'='-1', 'totalSize'='0', 'rawDataSize'='-1')Time taken: 0.059 seconds, Fetched: 20 row(s)
2.查看表的字段信息 【desc】
hive> desc student;OKcol_name data_type commentage int name string Time taken: 0.08 seconds, Fetched: 2 row(s)
3.查看表的详细属性信息 【desc formatted】
hive> desc formatted student;OKcol_name data_type comment# col_name data_type comment age int name string # Detailed Table Information Database: test Owner: root CreateTime: Wed Aug 19 14:13:07 SGT 2015 LastAccessTime: UNKNOWN Protect Mode: None Retention: 0 Location: hdfs://hadoop01:9000/user/hive/warehouse/test.db/student Table Type: MANAGED_TABLE Table Parameters: COLUMN_STATS_ACCURATE false last_modified_by root last_modified_time 1439964845 numFiles 0 numRows -1 rawDataSize -1 totalSize 0 transient_lastDdlTime 1439964845 # 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: -1 Bucket Columns: [] Sort Columns: [] Storage Desc Params: field.delim \t serialization.format \t Time taken: 0.084 seconds, Fetched: 35 row(s)
4.重命名表 【Rename To】
hive> alter table student rename to students;OKTime taken: 0.107 seconds
5.添加新列 【Add Column】
hive> desc students; --修改前的列名及对应类型OKcol_name data_type commentage int name string Time taken: 0.077 seconds, Fetched: 2 row(s)hive> alter table students > add columns(id int); --增加id列,类型intOKTime taken: 0.173 secondshive> desc students; --修改后的列名及对应类型OKcol_name data_type commentage int name string id int Time taken: 0.127 seconds, Fetched: 3 row(s)
6.修改列名及对应类型 【Change Column】
hive> desc t081901; OKcol_name data_type commentte int --旧的列名和数据类型 name string date string country string Time taken: 0.091 seconds, Fetched: 4 row(s)hive> alter table t081901 > change column te --要进行修改的列名 > temperature int; --修改后的列名和数据类型OKTime taken: 0.164 secondshive> desc t081901;OKcol_name data_type commenttemperature int --查看修改后的效果 name string date string country string Time taken: 0.101 seconds, Fetched: 4 row(s)
7.替换【重置】表的列名和类型 【Replace Column】
这个操作其实是将原有的列删除,然后再添加新的指定的列。
hive> create table student( > name int, --旧的列名及对应的数据类型 > age string) > row format delimited > fields terminated by '\t';OKTime taken: 0.103 secondshive> alter table student > replace columns( --replace columns替换数据列 > age int, > name string);OKTime taken: 0.121 secondshive> desc student;OKcol_name data_type commentage int --替换后的列名及对应的数据类型 name string Time taken: 0.105 seconds, Fetched: 2 row(s)
8.创建一个模式一样的新表 【Like】
CREATE TABLE new_table LIKE existing_table;
8.1.清空表【delete和truncate】
9.增加分区【Add Partition】
hive> alter table logs add > partition(date='2015-01-03',country='USA') --分区 > location '/user/hive/warehouse/logs/date=2015-01-03/country=USA' --分区路径
hive> alter table logs add > partition(date='2015-01-03',country='USA'); --新增分区,不指定路径 OKTime taken: 0.261 seconds
10.删除分区【Drop Partition】
hive> alter table logs drop partition(date='2015-01-03',country='USA'); Dropped the partition date=2015-01-03/country=USAOKTime taken: 1.344 seconds
hive> alter table logs DROP partition(date='2015-01-03'); --直接删除一级分区 Dropped the partition date=2015-01-03/country=USAOKTime taken: 0.464 seconds
11.创建视图【Create View】
hive> CREATE VIEW stu_view AS select stu.name,stu.age from test.stu; --创建视图hive> show tables; --查看视图(show views命令)OKstustu_viewhive> select * from stu_view; OKTie 18Coat 19Hat 21Scarf 37hive> drop view stu_view; --删除视图(不能使用命令drop table)OKTime taken: 1.366 secondshive> show tables;OKstu
12.设置NULL值的替代字符
create table test (id string)ROW FORMAT SERDE'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'WITH SERDEPROPERTIES ('field.delim'='\t','serialization.null.format'=' ') STORED AS TEXTFILE;OR 或者alter table test SET SERDEPROPERTIES('serialization.null.format' = '');
转载地址:http://ykvws.baihongyu.com/