Hive ----DDL

1、创建数据库

create database test_db comment 'test database';create database if not exists test_db comment 'test database';create database test_db location 'hdfs path';create database test_db with dbproperties('creator'='bpf','date'='20171106');

2、查看数据库

show databases;show databases like 'test*';

3、查看具体数据库结构

describe database test_db;describe database extended test_db;

4、使用（切换）数据库

use test_db

5、删除数据库

drop database if exists test_db;drop database if exists test_db cascade; 

6、创建表

create table [if not exists] [test_db.]test_table(  a string [comment 'string field'],  b float [comment 'float field'],  c array<string> [comment 'array field'],  d map<string,int> [comment 'map field'],  e struct<field1:string,field:int> [comment 'struct field'])[comment 'description for this table'][tblproperties ('k1' = 'v1', 'k2' ='v2')][row format delimitedfields terminated by '\001'collection items terminated by '\002'map keys terminated by '\003'][lines terminated by '\t'][store as textfile][location 'hdfs path'][as select_statement];   -- (这句不支持外部表) 

create table [if not exists] [test_db.]test_table like [test_db.]other_table [location 'hdfs path'] 

7、数据类型

data_type  : primitive_type  | array_type  | map_type  | struct_type  | union_type  -- (Note: Available in Hive 0.7.0 and later)

primitive_type  : TINYINT  | SMALLINT  | INT  | BIGINT  | BOOLEAN  | FLOAT  | DOUBLE  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)  | STRING  | BINARY      -- (Note: Available in Hive 0.8.0 and later)  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)  | DATE        -- (Note: Available in Hive 0.12.0 and later)  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)  | CHAR        -- (Note: Available in Hive 0.13.0 and later)array_type  : ARRAY < data_type >map_type  : MAP < primitive_type, data_type >struct_type  : STRUCT < col_name : data_type [COMMENT col_comment], ...>union_type   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)

8、文件格式

file_format:  : SEQUENCEFILE  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)  | ORC         -- (Note: Available in Hive 0.11.0 and later)  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)  | AVRO        -- (Note: Available in Hive 0.14.0 and later)  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

9、查看可用表

show tables;show tables in test_db;show tables like 'test*';

10、查看表tblproperties

show tblproperties test_db;

11、查看表详细信息

desc test_table;desc extended test_table;desc formatted test_table;

12、查看表中某一列的信息

desc test_table.column;

13、创建外部表

create external table if not exists test_table(  field string)[as select_statement]不可用

14、创建分区表

create table test)table(  field string)partitioned by (k1 string, k2 int);//分区后会在hdfs中的表对应的目录下生成子目录 k1/k2，便于管理分类，针对性查询时也可以提高性能

15、查看表中的分区

show partitions test_db;show partitions test_table partition(k1='value');

16、加载数据到分区表

load data [local] inpath 'path/file' into table test_table partition(k1='v1',k2='v2');//有Local代表本地路径，没有代表HDFS路径

17、增加分区

alter table test_table add partition(...) location 'hdfs path'

18、修改分区对应的目录地址

alter table test_table partition(k1=v1, k2=v2) set location 'hdfs path';

19、删除分区

alter table test_table drop if exists partition(...);

20、修改表

ALTER TABLE name RENAME TO new_nameALTER TABLE name ADD COLUMNS (字段1及其属性，字段2及其属性)ALTER TABLE name DROP [COLUMN] column_nameALTER TABLE name CHANGE column_name new_name new_type

21、删除表

DROP TABLE [IF EXISTS] table_name;