内外部表区别
1.内部表由hive自己管理数据(所以hive删表会删元数据和主数据)，(主)数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），但也可以自己指定路径;
外部表数据由HDFS管理(所以hive删表会删元数据，但不会删主数据)，(主)数据存储位置由自己建表时指定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的数据库名/表名创建一个文件夹，并将属于这个表的数据存放在这里）。
2.使用技巧 因为内外部表均可以自己指定路径，所以可以通过将内部表指定为外部表的路径来删除外部表的主数据。 外部表本身不能truncate和drop掉主数据。
hive元数据存储位置
Hive将元数据存储在关系型数据库中(如MySQL、derby),Hive的元数据包括数据库名、表名及类型、字段名称及数据类型、数据所存储的位置等。
使用场景
内部表作为ETL临时表使用，外部表作为生产业务表。
1.内部表 (managed table)
use test;
create table t1(
id int
,name string
,hobby array<string>
,add map<String,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
;
hdfs path: hdfs://namenode:9000/user/hive/warehouse/test.db/t1
1,xiaoming,book-TV-code,beijing:chaoyang-shagnhai:pudong
2,lilei,book-code,nanjing:jiangning-taiwan:taibei
3,lihua,music-book,heilongjiang:haerbin
load data local inpath '/home/hadoop/Desktop/data' overwrite into table t1;
select * from t1;
2. 外部表(external table)
use test;
create external table t2(
id int
,name string
,hobby array<string>
,add map<String,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/user/t2'
;
hdfs path: hdfs://namenode:9000/user/t2
load data local inpath '/home/hadoop/Desktop/data' overwrite into table t2;
desc formatted t1;
desc formatted t2;
分区表
分区相关参数列表：
| 名称 | 默认值 | 描述 |
|---|
| hive.exec.dynamic.partition | false | 是否开启动态分区功能，默认false关闭，使用动态分区时候，该参数必须设置成true | | hive.exec.dynamic.partition.mode | strict | 动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区， 一般需要设置为nonstrict | | hive.exec.max.dynamic.partitions.pernode | 100 | 在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。 | | hive.exec.max.dynamic.partitions | 1000 | 在所有执行MR的节点上，最大一共可以创建多少个动态分区。 | | hive.exec.max.created.files | 10w | 整个MR Job中，最大可以创建多少个HDFS文件 | | hive.error.on.empty.partition | false | 当有空分区生成时，是否抛出异常 |
在hive0.9.0之前版本hive建分区表时默认情况下该分区表是静态的，当启用以下设置后，则所有分区表变为动态的，则可以往分区表动态插入分区数据，否则只能对指定分区插入数据。
注意：
动态分区的创建是由输入列的值决定的。动态分区列必须在SELECT语句中列的最后一个指定，并按照它们在partition（）子句中出现的顺序指定。从hive3.0.0（Hive-19083）开始，就不需要指定动态分区列。如果没有指定分区规范，配置单元将自动生成分区规范，也就是只需保证select最后的那列是分区列即可。
LanguageManual DML - Apache Hive - Apache Software Foundation
在配置单元0.9.0之前，默认情况下禁用动态分区插入，在配置单元0.9.0及更高版本中默认启用动态分区插入
hive> SET hive.exec.dynamic.partition=true;
hive> SET hive.exec.dynamic.partition.mode=nonstrict;
hive> SET hive.exec.max.dynamic.partitions.pernode=1000;
对指定分区插入数据：
create table if not exists partition_table002 like partition_table001;
insert overwrite table partition_table002 partition (dt='20150617', ht='00') select name, ip from partition_table001 where dt='20150617' and ht='00';
往分区表动态插入分区数据：
insert overwrite table partition_table002 partition (dt, ht) select * from partition_table001 where dt='20150617';
静态分区和动态分区可以混合使用:
INSERT OVERWRITE TABLE T PARTITION (ds='2010-03-03', hr)
SELECT key, value, /*ds,*/ hr FROM srcpart WHERE ds is not null and hr>10;
临时表(TEMPORARY TABLE)
临时表的表将仅对当前会话可见,数据将存储在用户的scratch目录中，并在会话结束时删除。
如果在永久表上创建一个名字一样的临时表，则用户在该session中访问的是临时表，直到session关闭或删除临时表或reaname临时表，才能访问永久表上的数据。
如何往分区表插入多个分区历史数据，而无需指定具体的分区
1.先修改hive默认分区配置参数
SET hive.exec.dynamic.partition=true; ?SET hive.exec.dynamic.partition.mode=nonstrict;?
2.先将表数据同步到非分区表(建表时除了不指定分区字段和external，其他与分区表保持一致)
3.从非分区表将数据同步到分区表
hive > SET hive.exec.dynamic.partition=true;
hive > SET hive.exec.dynamic.partition.mode=nonstrict;?
--mysql 建表
CREATE TABLE test2.temp_t
(
name VARCHAR(255),
ip VARCHAR(255),
yaer VARCHAR(255),
month VARCHAR(255),
day VARCHAR(255)
)
INSERT into test2.temp_t SELECT 'zhangsan','10.210.32.11','2021','07','24';
INSERT into test2.temp_t SELECT 'lisi','10.210.32.11','2021','07','24';
INSERT into test2.temp_t SELECT 'zhangsan','10.210.32.11','2021','07','23';
INSERT into test2.temp_t SELECT 'zhangsan','10.210.32.11','2021','08','23';
INSERT into test2.temp_t SELECT 'zhangsan','10.210.32.11','2021','08','24';
--hive 中建表
CREATE external TABLE partition_t
(
name STRING,
ip STRING
)
PARTITIONED BY (yaer STRING, month STRING,day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
CREATE TABLE temp_t
(
name STRING,
ip STRING,
yaer STRING,
month STRING,
day string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
--用datax从mysql 同步数据到hive非分区表
-- mysql2hdfs.json
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": [""],
"table": ["partiton_t"]
}
],
"password": "passwd",
"username": "root",
"where": ""
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"column": [ {
"index": 0,
"name": "name",
"type": "string"
},
{ "index": 1,
"name": "ip",
"type": "string"
},
{ "index": 2,
"name": "year",
"type": "string"
},
{ "index": 3,
"name": "month",
"type": "string"
},
{ "index": 3,
"name": "day",
"type": "string"
}
],
"defaultFS": "hdfs://10.85.x.x",
"fieldDelimiter": ",",
"fileName": "temp_t_file",
"fileType": "text",
"path": "/user/hive/warehouse/test.db/temp_t",
"writeMode": "append"
}
}
}
],
"setting": {
"speed": {
"channel": "1"
}
}
}
}
--用sqoop导Mysql到hive
sqoop import \
--connect jdbc:mysql://192.168.108.81:3306/test2?useSSL=false \
--username root \
--password passwd \
--table temp_t \
--hive-database test \
--hive-table temp_t \
--fields-terminated-by "," \
--target-dir /user/hive/warehouse/test.db/temp_t/ \
--delete-target-dir \
--num-mappers 1
sqoop import --help
–hive-import 必须参数，指定导入hive
–hive-database default hive库名
–hive-table people hive表名
–fields-terminated-by hive的分隔符
–hive-overwrite 重写重复字段
–create-hive-table 帮创建好 hive 表，但是表存在会出错。不建议使用这个参数，因为到导入的时候，会与我们的字段类型有出入。
–hive-partition-key “dt” 指定分区表的字段
–hive-partition-value “2018-08-08” 指定分区表的值
--从非分区表写入分区表
INSERT INTO TABLE partition_t PARTITION (year,month,day)
SELECT * FROM temp_t
其中Partition clause should be optional for:
INSERT INTO VALUES
INSERT OVERWRITE
INSERT SELECT
|