linux统计hive建表语句,2、Hive的建表操作

一、hive支持的数据类型

1、数字型

2、日期型

1、Timestamps

支持传统的UNIX时间戳和可选的纳秒精度。

支持的转化：

整数数字类型：以秒为单位解释为UNIX时间戳

浮点数值类型：以秒为单位解释为UNIX时间戳，带小数精度

字符串：符合JDBC java.sql.Timestamp格式“YYYY-MM-DD HH：MM：SS.fffffffff”(9位小数位精度)

时间戳被解释为无时间的，并被存储为从Unix纪元的偏移量。 提供了用于转换到和从时区转换的便捷UDFs(to_utc_timestamp，from_utc_timestamp)。

所有现有的日期时间UDFs(月，日，年，小时等)都使用TIMESTAMP数据类型。 Text files中的时间戳必须使用格式yyyy-mm-dd hh:mm:ss [.f …]。 如果它们是另一种格式，请将它们声明为适当的类型(INT，FLOAT，STRING等)，并使用UDF将它们转换为时间戳。

在表级别上，可以通过向SerDe属性”timestamp.formats”(自版本1.2.0 with HIVE-9298)提供格式来支持备选时间戳格式。 例如，yyyy-MM-dd’T’HH:mm:ss.SSS，yyyy-MM-dd’T’HH:mm:ss。

2、Dates

DATE值描述特定的年/月/日，格式为YYYY-MM-DD。 例如，DATE’2013-01-01’。 日期类型没有时间组件。 Date类型支持的值范围是0000-01-01到9999-12-31，这取决于Java Date类型的原始支持。

Date types只能在Date, Timestamp, or String types之间转换。

3、字符型

4、复杂类型

二：建表操作

1、建表语句

create [external] table if not exists default.order_phone (

phone_nameSTRINGCOMMENT '手机名称',

phone_companySTRINGCOMMENT '手机厂商',

company_addrSTRINGCOMMENT '厂商地址',

exchange_numINTCOMMENT '成交数量',

phone_priceFLOATCOMMENT '手机单价',

total_priceDOUBLECOMMENT '总交易价钱',

order_timeTIMESTAMPCOMMENT '创建时间戳'

) partitioned by (order_time_p STRING)stored as parquet;

hive 支持的table 格式：RCFILE，PARQUET，TEXTFILE，SEQUENCEFILE

2、内部表和外部表的区别

1、内部表 ： 表目录会创建在hdfs的/user/hive/warehouse/的相应库目录中

2、外部表 ： 外部表会根据建表时Location关键字指定的路径创建表目录(如果没有指定LOCATION，则位置与内部表相同)

内部表的创建和外部表的创建，就差两个关键字： external location

举例：

内部表 —— create table t_inner(ID INT);

外部表 —— create [external] table t_external(ID INT) location '/AA/BB/XX';

HIVE中建表语句所做的两件事情：

1、创建HDFS上相应表目录

2、在元数据库中创建相应表的描述数据(元数据)

内部表和外部表在drop时有不同的特性：

1、 drop时，元数据都会被清除

2、 drop时，内部表的表数据目录会被删除，但是外部表的表数据目录不会被删除

所以： 整个数据仓库的最底层的表(与仓库外的数据对接的表，ODS层)

3、分区表：PARTITIONED BY (year string, moth string)

1、 可以将用户的整个表数据在存储时划分到多个子目录中(子目录以分区变量的值来命名),特别是有的信息是需要按照年份来进行分类存储的。 create table t_a(name string) partitioned by (year string) store as parquet; (一级分区)

create table t_b(name string) partitioned by (year string,moth string) store as parquet; 二级分区

2、分区表的数据导入方式 load data local inpath '/test.txt' into table t_f partition(year='2014',daystr='0929');

表中会多出一个字段(就是这个分区字段。)，在查询的时候，直接可以用于where +--------------------------+-----------------------+-----------------------+--+

| col_name | data_type | comment |

+--------------------------+-----------------------+-----------------------+--+

| id | int | |

| name | string | |

| age | int | |

| year | string | |

| | NULL | NULL |

| # Partition Information | NULL | NULL |

| # col_name | data_type | comment |

| | NULL | NULL |

| year | string | |

+--------------------------+-----------------------+-----------------------+--+

3、 分区表的意义

可以让用户在做数据统计时缩小数据扫描的范围，因为可以在select时指定要统计的分区 select count(*) from t_f where year='2014';

4、分桶表 CLUSTERED BY (col_name, col_name, ...)

将suffle之后每个reduce的结果保存起来。每个reduce的结果可以能是小明、小强。。。等人的数据集合(他们是hash % reducer数相同)。而且每个人都可能有多条相关的数据在里面，比如他们的上网流量。 区别于group by。group by结果一定是一个组一条数据，比如统计用户平均流量。得到的结果每个人肯定只有一条数据。

[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

1、 语法示例 drop table stu_buck;

create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)

clustered by(Sno)

sorted by(Sno DESC)

into 4 buckets

row format delimited fields terminated by ',';

2、分桶表的意义

为了保存分桶查询结果的分桶结构(数据已经按照分桶字段进行了hash散列)分桶表数据进行抽样和JOIN时可以提高MR程序效率

3、分桶表的使用

首先创建一个带分桶定义的表(分桶表)然后从原始数据(普通表)中用分桶查询得到分桶数据插入到分桶表中 例：

3.1、创建普通表 drop table t_stu;

create table t_stu(Sno int,Sname string,Sex string,Sage int,Sdept string)

row format delimited fields terminated by ',';

3.2、导入原始数据到普通表 load data local inpath '/root/hivedata/students.txt' into table t_stu;

3.4、创建分桶表 create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)

clustered by(Sno)

sorted by(Sno DESC)

into 4 buckets

row format delimited fields terminated by ',';

3.5、使用分桶查询，并将结果insert到分桶表中 首先将分桶查询开关开启：

hive> set hive.enforce.bucketing = true;

注意：粪桶表中定义了多少个桶，那么在查询时就必须制定分成多少个桶，如何制定呢？

hive> set mapreduce.job.reduces=4;

然后，查询(distribute : 根据这个列来散开)

insert into table stu_buck SELECT * FROM T_STU distribute BY (SNO) SORT BY (sno DESC);

或者：直接使用一个clustered ，将上面两个都包含了，只是没有那么灵活

insert into table stu_buck SELECT * FROM T_STU clustered BY (SNO);

4、分桶查询解析 DISTRIBUTE BY (SNO) -----指定mapreduce程序中getPartition()的依据字段为SNO

SORT BY (sno DESC) ----- 指定mapreduce程序中shuffle排序时所依据的字段及正序和倒序

SELECT * FROM T_STU distribute BY (SNO) SORT BY (sno);

SELECT * FROM T_STU CLUSTER BY (SNO);

上述两句效果相同,但是distribute BY (SNO) SORT BY (sno) 可以将partition字段和排序字段分别指定

三：表修改

1、修改表字段 添加分区 ：alter table aaa add partition(year="06"); 前提是这个year是分区字段。

删除分区 ：alter table aaa drop partition(year='06');

添加字段 ：alter table stu_buck add columns(gender string);

删除字段 ：alter table stu_buck drop columns(gender);

修改表名 ：alter table aaa rename to hello;

修改字段名和类型：alter table aaa change zd_type zd_type_other string;

修改类型 ：alter table aaa change zd_type zd_type string;

2、强制删除非空database drop database tmp cascade;

3、显示命令 show tables;

show databases;

show partitions table_name;

show functions;

desc table_name;

desc extended table_name;

desc formatted table_name;

四、总结：

分区表：主要是为了将文件分类存放。1、先创建分区表，2、在导入文件到自己指定的分区中。

分桶表：主要是用于保存一个表的分桶查询结果。1、先创建一个分桶表分桶表对应需要查询的表，2、对表进行分桶查询。插入分桶表中。