hive向mysql导数据_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