20HIVE的基本操作——好程序

  

注释
//
/**
*/
<!---->
#
-- 

语法规则：
hive的数据库名、表名不区分大小写。mysql是可以设置区分大小写的
命名规则：
1、名字不能使用数字开头
2、不能使用关键字
3、尽量不要使用特殊符号

hive中有一个默认的数据库default，如果不指定要使用哪个数据库，则使用默认的数据库。（通过use 数据库名进行切换）

可以查看数据库
show databases;

再切换库:
use gp1923;
另一种使用方式：gp1923.t2

显示当前库的名称：(只在当前界面有效)
set hive.cli.print.current.db=true;

看表
show  tables;

加载数据：
1、insert into  / update 禁止使用(要不重新操作)
2、hdfs dfs -put 
3、建表语法：（一般性的建表语法）

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
   [(col_name data_type [COMMENT col_comment], ...)] --列的说明
   [COMMENT table_comment] --表的说明
   [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
   [CLUSTERED BY (col_name, col_name, ...) 
   [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
   [ROW FORMAT row_format] 
   [STORED AS file_format] 
   [LOCATION hdfs_path]

 

 

 

不同类型的表

create external table if not exists t2(--内部表
id int,
name string,
age int
)
;

create external table if not exists t3(--外部表
id int,
name string,
age int
)
location '/t3'  --指定的一定是目录（只是路径而已）
;

hive中表的类型：
1、内部表    表目录创建在hdfs上的，hive的根目录下，对应的数据库目录中
2、外部表    外部表会根据建表时的LOCATION指定的路径来创建目录，如果没有指定LOCATION关键字，则表目录的位置跟内部表相同 

hive在创建表时要做两件事：
1、在hdfs上创建目录
2、在元数据库mysql中创建表的相应的描述信息（元数据）

 

内部表和外部表的区别，主要就差两个关键字：EXTERNAL LOCATION

内部表和外部表在drop时不同的特性：
1、drop表时，元数据都会被删除（内外表的元数据都被删除）
2、drop表时，内部表的表目录会被删除，但是外部表的表目录不会被删除，只是删除结构而已（表的结构被删除而已，但是数据还在）

 

外部表的使用场景：使用后数据不想被删除的情况下使用外部表（推荐使用）
所以，整个数据仓库的最底层的表使用外部表。

ods层，是原样的copy数据过来，而且表结构是完全一致的。表结构一致只适合关系型数据库

业务系统设计的思想是用于存储数据，则需要保证存储的数据量少，减少数据冗余，增加表的关联操作

做分析的时候，如果多个表的join，则导致查询性能很低，但是业务系统要求查询的数据都是少量的数据，所以他不符合分析的需求，则就需要增加一层，也就是所谓的数据仓库，存储的是部分历史数据，或者所有的历史数据，但是会重新建模，设置表的结构，可能几百张表，是为分析做准备工作，尽可能将底层的join在生成这份数据的时候就已经做了，一次join，尽量冗余，是解决join的操作，在Hive表里面就少用join

建模的目的就是增加每张表的冗余数据，减少join操作

建好表了之后，写sql分析语句

 

 

 

创建表:

create table if not exists t4(
id int comment 'user id',
name string comment 'user name',
age int comment 'user age'
) comment 'user info'
row format delimited 
fields terminated by ','
;

建表的本质：在hdfs上的hive的根目录下的数据库目录中创建一个表目录（表名字命名的目录）

加载数据：
加载方式：
LOAD DATA [LOCAL] INPATH '/xxx/yyy/aaa/' INTO TABLE table_name;（写了local指定的是Linux的目录，没写则指定的是hdfs的目录）

加载数据的本质：
1、如果数据在本地，加载数据的本质就是将数据copy到hdfs下的表目录下
2、如果数据在hdfs上，加载数据的本质是移动数据到hdfs下的表目录下

hive使用的时严格的读时模式，加载数据时不检查数据的完整性。读时如果数据不匹配，则使用NULL来代替。
mysql是严格的写时模式。

批量导入数据：

insert into select批量导入数据：
insert into t3 
select * 
from t4
where id>2
;

设置本地模式：set   hive.exec.mode.local.auto=true;

 

克隆表：不带数据 用like  ：  create table if not exists t5 like t4;

克隆表带数据：
create table if not exists t6 like t4 LOCATION '/user/hive/warehouse/gp1923.db/t4/';

更灵活的方式：
create table if not exists t7
as
select id,name from t4
where id <6
;

查看库的描述：
desc/describe database [extended] gp19231;

查看表的描述：
desc/describe [extended] t7;
desc formatted t7;

show create table t7;
 

 

案例：

1 15649428888 00-0C-29-CD-75-8C:CMCC-EASY 192.169.17.8 www.bjfkfu.com 88 25 751 2694 200 1446307200
2 13243983434 06-0C-29-CD-79-8C:CMCC-EASY 192.169.0.46 www.fgd.com 99 88 1913 3440 200 1446307200

CREATE TABLE log(
id string COMMENT 'this is id column',
phonenumber bigint,
mac string,
ip string,
url string,
state01 string,
state02 string,
upflow int,
downflow int,
status int,
dt string
)
COMMENT 'this is log table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
LINES TERMINATED BY '\n'
stored as textfile;

load data local inpath '/hivedata/data.log.txt' into table log;

 

1、通过每一个用户的上行流量、下行流量、总流量 

select l.phonenumber,
sum(l.upflow) sumupflow,
sum(l.downflow) sumdownflow,
sum(l.upflow + l.downflow) sumflow
from log l
group by l.phonenumber
order by sumflow desc
;

 

2、第二个需求，求访问次数排名前3的website

select
l.url website,
count(1) urlcount ,
count(*) ,
count(l.url)
from log l
group by l.url
order by urlcount desc 
limit 3
;