---王燕行转列sql
select split(concat_ws(',',collect_set(cast(smzq as string))),',')[1] ,split(concat_ws(',',collect_set(cast(smzq as string))),',')[2] from wyy;
---mysql 导入 导出数据部分
--mysql 导入
load data infile 'C:\\Users\\wanghongbo1\\Downloads\\sum.csv' into table `mars_tianchi_sum`
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\n';
--mysql导出
select * from mars_tianchi_result_avg15 into outfile 'e://avg15.csv' fields terminated by ','optionally enclosed by ''lines terminated by '\n';
--hive 建表及导入数据部分
create table mars_tianchi_songs
(song_id string,
artist_id string,
publish_time string,
song_init_plays string,
Language string,
gender string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
--hive 导入
load data local inpath '/test_whb/mars_tianchi_songs.csv' into table mars_tianchi_songs;
--hive 导出
INSERT OVERWRITE local DIRECTORY'/home/wizad/lmj/inserest2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY','
select * fromwizad_mdm_dev_lmj_edition_insterest
导入到本地可直接用-e命令,默认使用\t分隔:
hive -e 'use wizad;
select * fromwizad_mdm_dev_lmj_edition_insterest;'>> mytest