9.22日记-526互联

hive 中数据库为shuju
、、、、、、
create table daiin as select day_id as day_id,buy_nbr as buy_nbr, sum (cnt) as cnt, sum (round) as round from test3 where buy_nbr like 'O%' group by day_id,buy_nbr;

create table daili as select daiin.day_id as day_id,daiout.sale_nbr as sale_nbr,daiin.cnt as incnt,daiin.round as inround,daiout.cnt as outcnt,daiout.round as outround,daiout.round-daiin.round as li from daiin join daiout on (daiin.buy_nbr=daiout.sale_nbr);

insert overwrite table profit select * from daili where day_id between '2023-09-01' and '2023-09-15';

#text3_1入库
CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput'

select dboutput('jdbc:mysql://localhost:3306/hive3?useSSL=false','root','pwd','INSERT INTO text3_1(day_id, sale_nbr, cnt, round) VALUES (?, ?, ?, ?)',day_id, sale_nbr, cnt, round) from text3_1

#text3_2入库
CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput'

select dboutput('jdbc:mysql://node1:3306/hive3?useSSL=false','root','pwd','INSERT INTO text3_2(day_id, sale_nbr, cnt, round) VALUES (?, ?, ?, ?)',day_id, sale_nbr, cnt, round) from text3_2