Mysql & Hive 导入导出数据

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