Spark sql 数据迁移

数据迁移当然可以使用sqoop,但是我使用的时候发现配置不当有很多错误，使用起来很麻烦，于是自己就用了最原始的方法来迁移数据，熟练步骤了其实也很快，给大家一个参考

一、迁移一张表

查看表结构：desc table tb1;

查看建表sql语句：show create tabletb1;

1.同一个数据仓库中复制表和数据,使用CTAS语句;

常用的三种方案：

a.完全复制拷贝：

create table tb2 as select * from tb1;

b.拷贝时修改存储格式：

CREATE TABLE http_ORC STORED AS ORC as select *from int_s1u_http_17543_t where p_app=1;

c.拷贝时修改存储格式并压缩数据：

CREATE TABLE http_parquet STORED AS parquetTBLPROPERTIES ("parquet.compress"="GZIP") as select * fromint_s1u_http_17543_t where p_app=1;

存储格式可以修改其他的如：

SEQUENCEFILE、AVRO、parquet、textfile

压缩算法：

LZO、GZIP、ZIPPY、SNAPPY、LZIP

 

其他方案：

先复制表结构：

create table tb2 like tb1;

再插入数据：

insert overwrite into tb2 select * from tb1;

 

2.跨节点、跨数据仓库复制一张表：

a.先输入show create table tb1;获取到建表sql，复制一下再修改表名重新执行一遍即可生成新表。或者先手动创建表：

CREATE TABLE `test`(`id` int, `name` string)rowformat delimited fields terminated by ',' stored as textfile;

再手动导入数据: load data local inpath  '/root/db' into table test;

 

如果是分区表：

create table tb2(sid int,sname String)partioned by (p_app String) row format delimited fields terminated by ',';

导入到指定分区：load data local inpath  '/root/http.txt'  into table tb2 partition(p_app='9');

 

其他格式的建表语句如：CREATE TABLE parquet_test ( id int,name string)

PARTITIONED BY (part string)

STORED AS PARQUET [tblproperties("orc.compress"="NONE")];

[ ]为可选的内容

只需要把STORED AS PARQUET;修改为其他的格式即可，如STORED AS ORC，STORED AS PARQUET。

b.把表的数据导出

hdfs dfs -du -h /user/spark/warehouse/xdrtest1.db

hdfs dfs -get /user/spark/warehouse/xdrtest1.db/http ./

 

c.向新表导入数据

hdfs dfs -put /root/http/* user/hive/warehouse/xdrtest1.db/http

 

注意：如果是分区表，没有指定分区的话会识别不了数据，需要手动注册：

msck repair table http;

show partitions http;会看到分区

二、跨节点、跨数据库迁移数据库里所有的表

1.先把原始数据库的表名全部复制出来

使用脚本：

#!/bin/bash

spark-sql -e "usexdrtest1; show tables;" > tables.txt

得到所有的表名：

接下来要去掉数据库名字xdrtest1和后面的false：

在vi里面使用字符串替换命令：先按esc键，再输入%s#xdrtest1 # 回车,

再输入 %s# false#回车, 得到只有表名的文件：

2.使用脚本把每张表的表结构，也就是建表语句导出来：

#!/bin/bash

cat tables.txt | whileread eachline

do

 spark-sql -e "use xdrtest1; show createtable $eachline" >> tb2.txt

done

得到所有表的创建sql脚本tb2.txt：

注意：每个表的sql后面没有分号，需要手动添加；

3.重建一个数据库，并导入表结构

在另外一个地方先手动创建一个同名数据库：

create database xdrtest1;

再导入建表脚本：

source/root/lz/tb2.txt;

至此，表结构就全部复制成功了，输入show tables;查看所有表：

4.下载数据

先在HDFS上对应的数据仓库库目录里把文件下载下来：

输入hdfsdfs -du -h /user/spark/warehouse/xdrtest1.db，查看每个表的大小情况

再输入hdfs dfs -get/user/spark/warehouse/xdrtest1.db，把数据下载下来(如果数据量很大的话会很慢)

查看数据大小：du -hs/root/xdrtest1.db/

5.传输数据

先将数据打包，tar zcvf xdrtest1.tar.gz/root/xdrtest1.db   

（解压tar zxvf xdrtest1.tar.gz）

再用wincp或者其他方式传输数据到另外一个环境中，再解压到linux本地文件系统中。

#压缩

tar -czvf ***.tar.gz

tar -cjvf ***.tar.bz2

#解压缩

tar -xzvf ***.tar.gz

tar -xjvf ***.tar.bz2

压缩打包后数据：由之前的220M变成51M

6.数据导入

使用脚本将数据导入：

#!/bin/bash

dir=$(ls -l/root/lz/xdrtest |awk '/^d/ {print $NF}') #遍历所有目录名

for i in $dir

do

echo $i

#spark-sql -e "use xdrtest1;load data local inpath /root/xdrtest1.db/$i/*  into table$i;"

hdfs dfs -put/root/lz/xdrtest/$i/* /user/hive/warehouse/xdrtest1.db/$i

echo "hdfs dfs -put/root/lz/xdrtest/$i/* /user/hive/warehouse/xdrtest1.db/$i"  

done

 

注意：

hdfs dfs -put /root/http/*user/hive/warehouse/xdrtest1.db/http是将文件直接放到对应表的目录下；

而load datalocal inpath  '/root/http/*'  into table http;是用sql语句去导入表，这个每次都要use xdrtest1;。