2、Hive的建表操作

最新推荐文章于 2022-11-05 14:31:35 发布

weixin_33924220

最新推荐文章于 2022-11-05 14:31:35 发布

阅读量229

点赞数 1

文章标签：大数据数据库 java

原文链接：https://my.oschina.net/liufukin/blog/798491

版权

2019独角兽企业重金招聘Python工程师标准>>>

一、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;