2 hive库-表-数据加载-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_3893/article/details/82615799

1 库操作

1 默认数据库"default"
2 使用#hive命令后，不使用hive>use <数据库名>，系统默认的数据库。可以显式使用hive> use default;
3 创建一个新库
hive>CREATE DATABASE  
[IF NOT EXISTS] mydb  
[COMMENT] '....';
[LOCATION] '/.......'  

hive>SHOW DATABASES;

hive>DESCRIBE DATABASE [extended] mydb;   <-------------------->  对比与   desc extended tablename; 展示表详细信息

hive>DROP DATABASE [IF EXISTS] mydb [CASCADE];    
说明：
CASCADE 表示级联删除
IF EXISTS 常在脚本中见到，比如ETL中 02_all_step.sql中，drop table if exists liuchengtmp1_%CHINDAASDATE%;


简单创建数据库写法:
hive (default)> create database if not exists mydb;
OK
Time taken: 0.263 seconds
hive (default)> desc  database extended mydb;
OK
db_name comment location        parameters
mydb            hdfs://chinadaas109:8020/hive1/user/hive/warehouse/mydb.db
hive (mydb)> drop database mydb;
OK
Time taken: 0.301 seconds
hive (mydb)> drop database mydb;
OK
Time taken: 0.301 seconds

2 表操作：

hive>CREATE TABLE IF NOT EXISTS t1(...) 
[COMMENT '....'] 
[LOCATION '...']

hive>SHOW TABLES in mydb;    查看mydb数据库下的所有表

hive>CREATE TABLE t2 LIKE t1;  根据t1结构定义t2表的结构，
类比于  create table t2 as select name ,age from t1; 这是将表结构和字段值都拷贝到t2

hive>DESCRIBE t2;

 

查看更详细信息 比如如下可以看到t1在hdfs文件路径 .和操作这个文件的输入 输出format类类型

TextInputFormat HiveIgnoreKeyTextOutputFormat 

hive (default)> describe extended t1;   
OK
col_name        data_type       comment
username                string                                      
                 
Detailed Table Information      Table(tableName:t1, dbName:default, owner:root, createTime:1426309182, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:username, type:string, comment:null)], location:hdfs://h2single:9000/user/hive/warehouse/t1, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{transient_lastDdlTime=1426309182}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)
Time taken: 0.463 seconds, Fetched: 3 row(s)

3 修改列 , 实际工作中，修改列名称和增加列会用到，但是移动列的位置是不实用的

修改列的名称、类型、位置、注释
hive>ALTER TABLE t3 CHANGE COLUMN old_name new_name String COMMENT '...' AFTER column2;  
修改列名称，需要制定新列名称 新列类型 ， 同时可以给列加入注释， 并重新定义列的位置。 注意，如果表里面已经有数据，你变更列的位置
并不能将列下的value值也对应变更过来，仅仅是变更了列名称而已。 看下面案例：

此时文件t在~下内容为 
1,zhangsan
2,lisi
3,wangwu

hive (default)>create table t(id int, name string) row format delimited fields terminated by ',';
hive (default)>load data local inpath  't' into table t;   
hive (default)>alter table t change id id1 string after name;
hive (default)> select * from t;                                                                              
OK
name1   id1
1       zhangsan
2       lisi
3       wangwu
Time taken: 1.201 seconds

增加列
hive>ALTER TABLE t3 ADD COLUMNS(gender int);

4 hive表的分类 hive加载数据：

4.1) 内部表，外部表，两者创建的唯一区别就是后者有关键字 external

hive默认创建的表就是管理表(内部表)

hive内部表，上传数据时，
如果数据是hdfs的文件，那么会将hdfs的文件移动到hive所在的hdfs路径下
如果数据是linux的文件，那么是拷贝到hive所在的hdfs路径下

hive外部表，上传数据时，
数据是hdfs的文件，那么仅仅是针对hdfs的文件创建一个快捷链接而已，

如果数据是linux的文件，那么是拷贝到hive外表所在的hdfs路径下

4.2) 如何知道已经创建表的类型：

连接MySQL数据库，查看表 tbls内的数据，此表内存储所有内外部表名称类型等信息。

或者 hive>desc extened tablename; 查看 tableType的结果

4.3) 外部表创建写法： create external table xxx location '/dir'; location可以不写

4.4) 内部表：别名管理表受控表，删除表时，元数据与数据都会被删除

4.4.1) 内部表加载数据：

a) 使用load方式加载：

load命令写法： LOAD DATA 【LOCAL】 INPATH ‘....’ 【OVERWRITE】 INTO TABLE t1 【PARTITION (...)】  
以上内容中，方括号中的表示可选部分。分别解释如下：
LOCAL 表示从本地加载数据，即Linux文件系统，这时数据会复制到HDFS的Hive仓库中；
如果不使用LOCAL，指的是数据从HDFS的某个路径移动到Hive仓库中。
OVERWRITE指的是否覆盖原始数据。
如果不使用OVERWRITE，但是已经导入过这批数据，那么新的导入依然能够成功,即产生两份，而不是覆盖掉原来的那一份
PARTITION指的是导入到指定分区表中。

表中只有一个字段的load写法：

hive (default)> create table t1(id int);
hive (default)> load data inpath '/usr/local/test1' into table t1;
hive (default)> select * from t1;
OK
t1.id
1
2
3
4
5

表中字段为2个及以上load字段的写法：其中行规定写法关键字为  row format delimited fields terminated by "\t";  表示行中字段以 \t分割方式加载外部文件数据
hive>create table t1 (id int, name string) row format delimited fields terminated by "\t";
加载linux数据
hive>load data local inpath '/root/inner_table.dat' into table t1;

4.5) 外部表：

4.5.1) 外部表的优势：

数据仓库是在hdfs数据很大情况下才使用，那么hdfs很大的时候，已经按照固定目录结构存储好了
如果使用内部表来加载hdfs数据，势必会移动原来目录的hdfs文件，

这样破坏了结构不说万一删除hive内部表对应的hdfs将物理删除

4.5.2) 外部表创建写法：
create external table xxx location '/dir'; 要跟上hdfs文件所在的目录

   

linux目录。 
比如： 
CREATE EXTERNAL TABLE test10(id INT, name string) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ‘\t’ 

LOCATION ‘file:home/hjl/sunwg/’;



hdfs目录：

create external table t2(id int) location '/data'

或者也可以像内部表导入数据一样，使用 hive>load local data inpath 'hdfs文件' into table t 方式将数据导入外部表

5 hive load的读模式和关系型数据库写模式：

如果通过hdfs 向 user/hive/warehouse/t1（hive t1表对应的hdfs存储路径）放入文件，那么在Hive中 select * from t1 会被识别到
这是因为： hive的metastore 只认数据类型之间的关系  对于数据信息是不存储的。
Hive的这个特性 可以让用户不通过hive命令就将hdfs数据上传到hive表对应的路径下，但是
如果上传数据的类型和hive表对应字段类型不一致，那么上传后的结果就是一个NULL，
造成的后果就是读取的时候 一堆的NULL。

这样的流程就是Hive是读模式

带来的好处是：  load（写入）时加载快，  加载时不需要验证文件类型，不需要判断每一行数据的类型  （几百G数据的量下LOAD过程的耗时可想而知） ----> 面向大数据
什么时候做类型校验呢：  读取的时候  eg: select * from XXX



RDBMS是写模式:  mysql 等关系类型数据时候   存入数据时候 就进行类型校验  校验失败就存入失败报错给操作者看
Hive是读模式

6 内部表分之--->hive 分区表：

分区字段和创建表的表内字段名称是不能重复的

分区即索引：  是将hive表中的一些业务字段 作为分区字段 映射到hdfs时则变成以这个字段为文件夹名称    用于快速定位到hdfs文件查询的目的。
hive所有的 Partition 的字段数据都存储在对应的hdfs目录中。

例如：test表中包含 date 和 city 两个 Partition，
则对应于date=20130201, city = bj 的 HDFS 子目录为：
/warehouse/test/date=20130201/city=bj
对应于date=20130202, city=sh 的HDFS 子目录为；
/warehouse/test/date=20130202/city=sh

定义分区表： 
create table stu(name string,age string)  指定表字段   
partitioned by(area string,city string)   指定分区字段
row format delimited fields terminated by '\t';  指定加载文件时的分隔符


加载数据到分区    这里分区字段用静态添加的方式
hive>load data local inpath '/usr/local/stu' into table stu partition (area='chaoyang',city='bj');


如何使用分区表： 按照分区查 

主意事项:  分区字段 谁在前 那么对应的hdfs目录谁就是上一级， 
           分区字段不应太多，否则真正的hdfs文件的层次会非常深，检索时也麻烦
		   

		
show partitions t1; 查看表t1的分区记录

增加分区
alter table partition_table add partition (daytime='2013-02-04',city='bj');
通过load data 加载数据

删除分区
alter table partition_table drop partition (daytime='2013-02-04',city='bj')
元数据，数据文件删除，但目录daytime=2013-02-04还在

动态增加分区：

严格模式：语句中至少有一个列要用静态方法指定分区列
eg: 
hive>INSERT OVERWRITE TABLE t3 PARTITION(province='bj', city) 
SELECT  t.name,t.age ,t.province, t.cityFROM temp t WHERE t.province='bj';   


非严格模式： 那么分区列可以都不静态指定
eg:
hive>INSERT OVERWRITE TABLE t3 PARTITION(province, city) 
SELECT t.province, t.city FROM temp t;




动态分区装载数据【动态列位于select子句最后】
hive>set hive.exec.dynamic.partition=true;
hive>set hive.exec.dynamic.partition.mode=nostrict;
hive>set hive.exec.max.dynamic.partitions.pernode=1000;   任务最多能产生1000个分区

hive>INSERT OVERWRITE TABLE t3 PARTITION(province='bj', city) 
SELECT  t.name,t.age ,t.province, t.cityFROM temp t;

后面两个t.province, t.city, 是给分区列赋值 
剩下的才是查询表数据

7 内部表分之---> hive 桶表：

桶表是对数据进行哈希取值，然后放到不同文件中存储。
创建表
	create table bucket_table(id string) clustered by(id) sorted by (id) into 4 buckets row format delimited fields terminated by '\t' ;
    根据ID取hash值 并模4 放在4个桶中	
加载数据   桶表只能通过从hive表中加载数据方式来load数据 

	set hive.enforce.bucketing = true;
	insert into table bucket_table select name from stu;	
	insert overwrite table bucket_table select name from stu;
数据加载到桶表时，会对字段取hash值，然后与桶的数量取模。把数据放到对应的文件中。

8 数据加载三方式简介：

从文件中装载数据  ETL的L
hive>LOAD DATA [LOCAL] INPATH '...' [OVERWRITE] INTO TABLE t2 [PARTITION (province='beijing')];

通过查询表装载数据  ETL的T
hive>INSERT OVERWRITE TABLE t2 PARTITION (province='beijing') SELECT * FROM xxx WHERE xxx

一次插入多个表     ETL的T     每一个表操作时候都用MR 这样一次操作多表目的是节省了多次mr操作  
hive>FROM t4 (手机号码日志表)
 INSERT OVERWRITE TABLE t1 SELECT ...WHERE... 
 INSERT OVERWRITE TABLE t2 PARTITION ('神州行') SELECT ...WHERE... 
 INSERT OVERWRITE TABLE t3 PARTITION ('动感地带') SELECT ...WHERE...