一、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_name STRING COMMENT '手机名称',
phone_company STRING COMMENT '手机厂商',
company_addr STRING COMMENT '厂商地址',
exchange_num INT COMMENT '成交数量',
phone_price FLOAT COMMENT '手机单价',
total_price DOUBLE COMMENT '总交易价钱',
order_time TIMESTAMP COMMENT '创建时间戳'
) 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、对表进行分桶查询。插入分桶表中。