记录用Python从MySQL迁移数据到aws的redshift历程

前段时间需要将业务库MySQL中的数据迁移到redshift中，由于是新手，从0开始，所以遇到很多坑，也成长不少

跨库迁移数据涉及到卸载源库数据，备份到文件中，迁移文件到redshift中并同步进数仓

由于数据库和数仓的表结构不同，本人采用卸载纯数据到csv的方式

---

一、从MySQL中卸载数据到CSV

 MySQL常规卸载数据到csv会用 into outfile 的方式备份数据；

但是我们这边由于环境都是docker统一安装好的，且file权限被关闭了，所以不能采用这种方式

固本人琢磨之后直接采用在服务器上用命令行连接数据库查询，然后经过Linux上的sed处理后另存为csv文件，如下：

os.system(f"""
    mysql -hHOST -uUSER -pPASSWORD -Ddbname -N -e "{select_sql}" |sed "s/NULL//g;s/\\t/|/g" > {os.path.join(os.getcwd(), 'execl', file_name)}.csv
    """)

解释一下：前面部分是连接库 -N是忽略表头 -e是执行引号中的sql语句，查询出来后用sed对NULL值和分隔符进行替换操作，换位|作为分隔符， 然后保存到服务器上

二、把服务器上的csv文件上传到redshift的S3中

对redshift的操作，可以直接查看AWS的官方文档，很全面，AWS的COPY命令就是官方推荐的

我们首先要拿到aws的aws_access_key_id 和 aws_secret_access_key 

此处用Python对它进行操作，需要用到boto3这个包，也是官方的api

对于S3中桶的创建可以看这里：创建和配置S3存储桶

用boto3从S3下载文件看这里，其他的操作也在里面：documentation

from boto3.session import Session


class AWS:
    
    def __init__(self):
        """
        连接redshift的access_id和access_key
        bucket_name 是S3中的桶名
        """
        self.aws_id = access_id
        self.aws_key = access_key
        self.session = Session(
            aws_access_key_id=f"{self.aws_id}",
            aws_secret_access_key=f"{self.aws_key}"
        )
        self.bucket_name = ""
    
    """
    对s3操作有client和resource两种方式，不尽相同
    对S3的操作可以自行查询  也可以看官方文档  不再赘述
    """

    def aws_resource(self):
        s3 = self.session.resource('s3')
        bucket = s3.Bucket(self.bucket_name)
        # 查看S3中有哪些桶
        for i in s3.buckets.all():
            print(i)
        # 查看桶中全部文件
        for obj in bucket.objects.all():
            print('obj name:%s' % obj.key)
        # 查看包含某字段的文件并下载
        for obj in bucket.objects.filter(Prefix=''):
            bucket.download_file(obj.key, f"{obj.key}.csv")

    def aws_client(self):
        # 上传文件到S3
        s3 = self.session.client('s3')
        s3.upload_file(.csv, self.bucket_name,.csv)
        

三、最后就是把S3上面的文件用官方copy语句copy到表中

具体可以看这里：COPY  UNLOAD

copy from是加载数据，copy to是保存数据  unload是卸载数据

文档中已经说的很清楚了，在copy的时候出错的话可以用下面的query查询

SELECT * FROM STL_LOAD_ERRORS where filename like '%S3上的文件名%' order by starttime desc limit 10

最后两列err_code,err_reason。根据error_reason就是具体的错误原因

err_code 可以对应官方的文档查看：COPY error

这里要特别注意的一个点就是  在copy时一定要充分利用redshift的并发操作，

即：1.把需要copy的数据切分开放在S3的同一个文件夹下，批量copy文件夹下的所有文件

2.把多个文件夹压缩起来，直接用压缩包进行copy，这些操作在其官方文档中都有，慢慢琢磨