版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/qq_33323162/article/details/53514281
1、创建表
1.1、直接将一个表里的字段查出来放到新建的表中，字段hive会自动建好。
create table temp_table stored as rcfile as
select
id, name, age, school
from table1
where id=xxx;
1.2、将表字段创建好,根据性别和省份分区，（分区字段不用写到字段里）
create table temp_table1 (
id string,
name string,
age string,
school string,
)
partitioned by (gander string, prvnce string);
2、向表中插入数据
2.1、直接插入（注：overwrite会覆盖以前的数据，into不会覆盖以前的数据，具体使用时根据情况选择。）
INSERT overwrite（into） temp_table1 PARTITION (gander,prvnce)
select
id,name,age,school,gander,prvnce
from table1;
2.2、从本地文件中导入数据
#创建目标表，字段根据你文件字段对应创建
create table temp_table2(
license_number string
enterprise_name string
ds string
accs_nbr1 string
accs_nbr2 string )
row format delimited fields terminated by ',';
#导入数据
load data local inpath '/home/local/temp/table02.csv'
OVERWRITE INTO TABLE temp_table2;
#检验数据是否导入正确
SELECT count(1) FORM temp_table2;
3、连接查询用的比较多的就是left join 和inner join 了。
3.1、left join
table1 和table2
select a.*,b.school from table1 a left join
(select id,age,name,school from table2 where school='xxx大学' and age=18) b on a.id=b.id;
当使用left join时 如果table2 表中没有匹配到数据，查询也会把table1中的数据查出，b.school会是null。
3.2、inner join
table1 和table2
select a.*,b.school from table1 a inner join
(select id,age,name,school from table2 where school='xxx大学' and age=18) b on a.id=b.id;
当使用inner join时 如果table2 表中没有匹配到数据，则不会查出。当两个表中都有数据时才会显示。
4、查询条件
当一个表数据量太大，操作又很频繁时。可以将需要的数据一次性取出，放到不同的列，再操作。
例如：统计1-12月的每个用户的总消费额
样表table1：总共有2亿条数据
| user_id | billing | month_id | xxx | xxxx | xxx | xxx | xxx |
| | | | | | | |
建12个表，分别取出每个月的总消费额
CREATE TABLE temp_01 stored as rcfile as
select user_id,month_id, billing from table1 where month_id=01;
CREATE TABLE temp_02 stored as rcfile as
select user_id,month_id,billing from table1 where month_id=02;
CREATE TABLE temp_03 stored as rcfile as
select user_id,month_id, billing from table1 where month_id=03;
CREATE TABLE temp_04 stored as rcfile as
select user_id,month_id,billing from table1 where month_id=04;
CREATE TABLE temp_05 stored as rcfile as
select user_id,month_id, billing from table1 where month_id=05;
CREATE TABLE temp_06 stored as rcfile as
select user_id,month_id, billing from table1 where month_id=06;
CREATE TABLE temp_07 stored as rcfile as
select user_id,month_id, billing from table1 where month_id=07;
CREATE TABLE temp_08 stored as rcfile as
select user_id,month_id, billing from table1 where month_id=08;
CREATE TABLE temp_09 stored as rcfile as
select user_id,month_id, billing from table1 where month_id=09;
CREATE TABLE temp_010 stored as rcfile as
select user_id,month_id, billing from table1 where month_id=10;
CREATE TABLE temp_11 stored as rcfile as
select user_id,month_id, billing from table1 where month_id=11;
CREATE TABLE temp_12 stored as rcfile as
select user_id,month_id, billing from table1 where month_id=12;
把12个月的消费额取出
create table table_fin01 stored as rcfile as
select user_id,
case when b01.billing is null or b01.billing='' then 0L else b01.billing end as billing_01,
case when b02.billing is null or b02.billing='' then 0L else b02.billing end as billing_02,
case when b03.billing is null or b03.billing='' then 0L else b03.billing end as billing_03,
case when b04.billing is null or b04.billing='' then 0L else b04.billing end as billing_04,
case when b05.billing is null or b05.billing='' then 0L else b05.billing end as billing_05,
case when b06.billing is null or b06.billing='' then 0L else b06.billing end as billing_06,
case when b07.billing is null or b07.billing='' then 0L else b07.billing end as billing_07,
case when b08.billing is null or b08.billing='' then 0L else b08.billing end as billing_08,
case when b09.billing is null or b09.billing='' then 0L else b09.billing end as billing_09,
case when b10.billing is null or b10.billing='' then 0L else b10.billing end as billing_10,
case when b11.billing is null or b11.billing='' then 0L else b11.billing end as billing_11,
case when b12.billing is null or b12.billing='' then 0L else b12.billing end as billing_12
from (
select user_id from table where month_id=
)a
left join temp_01 b01 on a.user_id=b01.user_id
left join temp_02 b02 on a.user_id=b02.user_id
left join temp_03 b03 on a.user_id=b03.user_id
left join temp_04 b04 on a.user_id=b04.user_id
left join temp_05 b05 on a.user_id=b05.user_id
left join temp_06 b06 on a.user_id=b06.user_id
left join temp_07 b07 on a.user_id=b07.user_id
left join temp_08 b08 on a.user_id=b08.user_id
left join temp_09 b09 on a.user_id=b09.user_id
left join temp_10 b10 on a.user_id=b10.user_id
left join temp_11 b11 on a.user_id=b11.user_id
left join temp_12 b12 on a.user_id=b12.user_id ;
相加：
create table_fin02 stored as rcfile as
select user_id ,
(b01+b02+b03+b04+b05+b06+b07+b08+b09+b10+b11+b12) as billing
from table_fin01 group by user_id;