HIVE 基础DDL+DML之表操作

hive表的数据导出到RDBMS(sqoop)

Hive数据抽象/结构
database HDFS一个目录
table HDFS一个目录 下面存的文件
data 文件
partition 分区表 HDFS一个目录 下面存的文件
data 文件
bucket 分桶 HDFS一个文件

desc database hive

desc database extended hive3

hive> set hive.cli.print.current.db;

hive> set hive.cli.print.current.db=true;

hive (default)> !clear;

#查看字段类型

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types 

#创建表

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Describe

#行与行的分隔符

LINES TERMINATED BY

#字段和字段的分隔符

FIELDS TERMINATED BY '\t'

#创建表

CREATE TABLE emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

#详细信息

desc extended emp;

hive (default)> desc formatted emp;

#加载数据 emp是表明

LOAD DATA LOCAL INPATH '/root/data/emp.txt' OVERWRITE INTO TABLE emp;

#看下是否有这个文件

[root@hadoop000 data]# hadoop fs -ls /user/hive/warehouse/emp

#查看文件

[root@hadoop000 data]# hadoop fs -text /user/hive/warehouse/emp/emp.txt

#DML 加载导出

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Loadingfilesintotables

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]

LOCAL：本地系统，如果没有local那么就是指的HDFS的路径
OVERWRITE：是否数据覆盖，如果没有那么就是数据追加

#这是本地的
LOAD DATA LOCAL INPATH ‘/home/hadoop/data/emp.txt’ OVERWRITE INTO TABLE emp;

#HDFS
LOAD DATA INPATH ‘hdfs://hadoop000:8020/data/emp.txt’ INTO TABLE emp;

#数据是从sql语句过来的
INSERT OVERWRITE LOCAL DIRECTORY ‘/tmp/hive/’
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
select empno,ename,sal,deptno from emp;

#首先emp 里面要有数据

hive (default)> create table emp2 as select * from emp;

hive (default)> create table emp3 as select empno,ename,job,deptno from emp;

#查询结果写到文件里面去 Writing data into the filesystem from queries —把这个结果写到本地路径

INSERT OVERWRITE LOCAL DIRECTORY '/root/data/hive/'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select empno,ename,sal,deptno from emp;

#sql语句
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

sql语句和关系数据库一样

是null的数据
hive (default)> select * from emp where comm is null;

#聚合函数

hive> select max(sal),min(sal),sum(sal),avg(sal) from emp;

求每个部门、工作岗位的平均工资

hive> select deptno,job,avg(sal) from emp group by deptno,job;

对于分组函数过滤要使用having

hive> select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal>2000;

#join

hive (default)> explain select e.empno,e.ename,e.sal,e.deptno,d.dname from emp e join dept d on e.deptno=d.deptno;

在hivedb库中

MANAGED_TABLE:内部表
删除表： HDFS上的数据被删除 & Meta也被删除

#外部表 加EXTERNAL 在hive运行

CREATE EXTERNAL TABLE emp_external(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
location '/external/emp/';

#数据加载到emp_external

LOAD DATA LOCAL INPATH '/root/data/emp.txt' OVERWRITE INTO TABLE emp_external;

EXTERNAL_TABLE
HDFS上的数据不被删除 & Meta被删除(hive、mysql没有了数据)

#分区表

create external table track_infos(
ip string,
country string,
province string,
city string,
url string,
time string,
page string
) partitioned by (day string)       #按天分区
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
location '/root/app/hive/';     #路径存放在哪

#写入表tck_info_pro 数据 ，按天分组 数据从哪来下面的sql语句

insert overwrite table tck_info_pro partition(day='2013-07-21') 
select province,count(*) as cnt from track_info where day='2013-07-21' group by province ;