今日总结

//hdfs
insert overwrite directory '/user/hadoop/result2' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from result5;
//hive导入命令
load data local inpath '/home/hadoop/result.csv' into table result;

//sqoop语句
bin/sqoop export \
--connect jdbc:mysql://192.168.51.100:3306/myhive \
--username root \
--table result5 \
--num-mappers 1 \
--export-dir /user/hadoop/result2 \
--input-fields-terminated-by "\t"

 

drop table if exists result5;
truncate table 表名;

create table result5(name string,num int, id string)
ROW format delimited fields terminated by ',' STORED AS TEXTFILE ;

insert into yc1(nsr_id) select distinct nsr_id from yc3 where nsr_id not in (select nsr_id from yc2);

select distinct nsr_id from nsrxx where nsr_id not in (select xf_id from zzsfp) and nsr_id in (select gf_id from zzsfp) ;
select distinct nsr_id from yc2 where nsr_id not in (select nsr_id from yc3);

create table ceshi3(ip string,date string,day string,traffic string,type string,id string) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' with SERDEPROPERTIES ("separatorChar"=",","quotechar"="\"") STORED AS TEXTFILE;

create table result3(num int, id string)ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

create table lingshou_csv (InvoiceNo varchar(255),//mysql建表
StockCode varchar(255),
Description varchar(255),
Quantity varchar(255),
InvoiceDate varchar(255),
UnitPrice varchar(255),
CustomerID varchar(255),
Country varchar(255)) ；
ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

bin/hive -service metastore

insert overwrite table aaa select aa.ID as ID,aa.QA04 as QA04,aa.QA05 as QA05,aa.QA07 as QA07,aa.QA15 as QA15,aa.QA19 as QA19,aa.hangye as hangye,aa.Qb03 as Qb03,aa.QB03ONE as QB03ONE,aa.QB03TWO as QB03TWO,aa.QB03_1 as QB03_1,aa.QB06 as QB06 ,aa.QB16 as QB16,aa.QB16V as QB16V,aa.gaoxin as gaoxin,aa.QB16_1 as QB16_1,aa.QB16_1V as QB16_1V,aa.QC02 as QC02,aa.QC05_0 as QC05_0,aa.QC24 as QC24,aa.QC40 as QC40,aa.QD01 as QD01,aa.QD28 as QD28,aa.QJ09 as QJ09,aa.QJ20 as QJ20,aa.QJ55 as QJ55,aa.QJ74 as QJ74,concat(aa.QA19,b.dmms) as diyu,aa.SYEAR as SYEAR from aa join b where (b.dm==aa.QA19);

insert overwrite table result5 select name as name,sum(traffic) as num,id as id from result group by id,name ;

insert overwrite table result3 select num as num,id as id from result2 sort by num desc ;
create table c (day_id String,sale_nbr String,buy_nbr String,cnt int,round int)
clustered by (day_id) into 5 BUCKETS STORED AS ORC
TBLPROPERTIES ("transactional"="true",
"compactor.mapreduce.map.memory.mb"="2048",
"compactorthreshold.hive.compactor.delta.num.threshold"="4",
"compactorthreshold.hive.compactor.delta.pct.threshold"="0.5"
);

 

INSERT INTO aa (diyu) select concat(dm,'-',dmms) from aa join b on aa.QA19=b.dm;