---王燕行转列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