hive建库建表与数据导入，hql运行-hive内部表与外部表，分区表，数据导入导出（twenty-three day）

hive脚本化运行hql

    大量的hive查询任务，如果用交互式shell来进行输入的话，显然效率及其低下，因此，生产中更多的是使用脚本化运行机制：

该机制的核心点是：hive可以用一次性命令的方式来执行给定的hql语句

[root@hdp20-04 ~]#  hive -e "insert into table t_dest select * from t_src;"

然后，进一步，可以将上述命令写入shell脚本中，以便于脚本化运行hive任务，并控制、调度众多hive任务，示例如下：

vi t_order_etl.sh

#!/bin/bash hive -e "select * from db_order.t_order" hive -e "select * from default.t_user" hql="create table  default.t_bash as select * from db_order.t_order" hive -e "$hql"

如果要执行的hql语句特别复杂，那么，可以把hql语句写入一个文件：

vi x.hql

select * from db_order.t_order; select count(1) from db_order.t_user;

然后，用hive -f /root/x.hql 来执行

 

hive建库建表与数据导入

1、建库

hive中有一个默认的库：

库名： default

库目录：hdfs://hdp20-01:9000/user/hive/warehouse

新建库：

create database db_order;

库建好后，在hdfs中会生成一个库目录：

hdfs://hdp20-01:9000/user/hive/warehouse/db_order.db

2、建表

基本建表语句

use db_order;

create table t_order(id string,create_time string,amount float,uid string);

表建好后，会在所属的库目录中生成一个表目录

/user/hive/warehouse/db_order.db/t_order

只是，这样建表的话，hive会认为表数据文件中的字段分隔符为 ^A

正确的建表语句为：

create table t_order(id string,create_time string,amount float,uid string)

row format delimited

fields terminated by ',';

这样就指定了，我们的表数据文件中的字段分隔符为 ","

3、删除表

drop table t_order;

删除表的效果是：

hive会从元数据库中清除关于这个表的信息；

hive还会从hdfs中删除这个表的表目录；

 

hive内部表与外部表

内部表(MANAGED_TABLE)：表目录按照hive的规范来部署，位于hive的仓库目录/user/hive/warehouse中

外部表(EXTERNAL_TABLE)：表目录由建表用户自己指定，1、为了方便 2、防止采集错误

create external table t_access(ip string,url string,access_time string)

row format delimited

fields terminated by ','

location '/access/log';

外部表和内部表的特性差别：

1. 内部表的目录在hive的仓库目录中 VS 外部表的目录由用户指定
2. drop一个内部表时：hive会清除相关元数据，并删除表数据目录
3. drop一个外部表时：hive只会清除相关元数据；

一个hive的数据仓库，最底层的表，一定是来自于外部系统，为了不影响外部系统的工作逻辑，在hive中可建external表来映射这些外部系统产生的数据目录；

然后，后续的etl操作，产生的各种表建议用managed_table

 

hive分区表

分区表的实质是：在表目录中为数据文件创建分区子目录，以便于在查询时，MR程序可以针对分区子目录中的数据进行处理，缩减读取数据的范围。

比如，网站每天产生的浏览记录，浏览记录应该建一个表来存放，但是，有时候，我们可能只需要对某一天的浏览记录进行分析

这时，就可以将这个表建为分区表，每天的数据导入其中的一个分区；当然，每日的分区目录，应该有一个目录名（分区字段）

 

1. 一个分区字段的实例：

示例如下：

1. 创建带分区的表

create table t_access(ip string,url string,access_time string) partitioned by(dt string) row format delimited fields terminated by ',';

注意：分区字段不能是表定义中的已存在字段

1. 向分区中导入数据

load data local inpath '/root/access.log.2017-08-04.log' into table t_access partition(dt='20170804');

load data local inpath '/root/access.log.2017-08-05.log' into table t_access partition(dt='20170805');

1. 针对分区数据进行查询

a、统计8月4号的总PV：

select count(*) from t_access where dt='20170804';

实质：就是将分区字段当成表字段来用，就可以使用where子句指定分区了

b、统计表中所有数据总的PV：

select count(*) from t_access;

实质：不指定分区条件即可

 

1. 多个分区字段示例

建表：

create table t_partition(id int,name string,age int)

partitioned by(department string,sex string,howold int)

row format delimited fields terminated by ',';

导数据：

load data local inpath '/root/p1.dat' into table t_partition partition(department='xiangsheng',sex='male',howold=20);

 

1. CTAS建表语法

可以通过已存在表来建表：

1、create table t_user_2 like t_user;

新建的t_user_2表结构定义与源表t_user一致，但是没有数据

1. 在建表的同时插入数据

create table t_access_user as select ip,url from t_access;

t_access_user会根据select查询的字段来建表，同时将查询的结果插入新表中

1. hive数据导入导出(将数据文件导入hive的表)

方式1：导入数据的一种方式：

手动用hdfs命令，将文件放入表目录；

方式2：在hive的交互式shell中用hive命令来导入本地数据到表目录

hive>load data local inpath '/root/order.data.2' into table t_order;

方式3：用hive命令导入hdfs中的数据文件到表目录

hive>load data inpath '/access.log.2017-08-06.log' into table t_access partition(dt='20170806');

 

注意：导本地文件和导HDFS文件的区别：

本地文件导入表：复制

hdfs文件导入表：移动

 

将hive表中的数据导出到指定路径的文件

1、将hive表中的数据导入HDFS的文件

insert overwrite directory '/root/access-data'

row format delimited fields terminated by ','

select * from t_access;

2、将hive表中的数据导入本地磁盘文件

insert overwrite local directory '/root/access-data'

row format delimited fields terminated by ','

select * from t_access limit 100000;