V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
imherer
V2EX  ›  程序员

关于大量数据导出到 excel 或 csv 实现方案

  •  
  •   imherer · 2019-08-15 11:40:41 +08:00 · 7658 次点击
    这是一个创建于 1687 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近有个需求,需要将 PostgreSQL 里的数据导出到 excel 里(或者 csv ),可能出现的情况有 3 种:

    • 1.导出单张表某个时间段的数据
    • 2.导出单张的全部数据
    • 3.导出所有表的全部数据(一张表一个文件存储)

    现在问题是如果导出的数据太大的话会 OOM,如何解决呢?

    现在想预估一个安全行数,在导出的行数达到安全行数后就保存这个 excel,然后再从数据库读取再追加到这个 excel 里。疑问是再次向这个文件里追加数据的时候这个文件里之前已有的数据会载到内存里来吗?

    有做过的前辈分享下经验吗?

    语言:go

    公司内部项目,所以用户量很少,基本上就几个人使用

    46 条回复    2019-08-16 16:51:24 +08:00
    taotaodaddy
        1
    taotaodaddy  
       2019-08-15 11:47:45 +08:00 via Android
    为什么不自己试试呢
    myself659
        2
    myself659  
       2019-08-15 12:01:47 +08:00
    有时间我可以将这个功能加上
    https://github.com/myself659/csvdata
    但是一般 csv 不会这么大
    myself659
        3
    myself659  
       2019-08-15 12:03:47 +08:00   ❤️ 1
    对于大大 程序分片一下就可以了
    都那么大导出了一个 csv 中
    不存在这种需求
    反过来想一想都在一个文件里面,哪得用什么电脑才能打开 csv
    wanganjun
        4
    wanganjun  
       2019-08-15 12:11:37 +08:00 via iPhone   ❤️ 1
    pg 有导出数据到 csv 的 sql 命令,还支持重定向数据到外部程序的功能,可以实现导出到压缩文件的功能
    auser
        5
    auser  
       2019-08-15 12:14:41 +08:00   ❤️ 2
    csv 有个 65535 限制的问题 .....

    这个问题应该是 Go 的内存管理与第三方解析库的问题。数据量太大的话,要自己从“底层”实现了。

    我这边处理的 xlsx 对应数据表量级在 30 万左右,几百兆的数据库文件。导出功能都是按日期筛选的,还没遇到要考虑内存的程度。

    建议直接花钱加内存解决,相比解决这个问题的工钱是小钱了。如果真到了加内存都解决不了的数据量,我觉得文件使用者也没有电脑能处理这么大的 excel 文件吧。
    zjj2008se
        6
    zjj2008se  
       2019-08-15 12:26:18 +08:00 via Android   ❤️ 1
    为什么不试试神奇的 pandas 呢?直接从数据库里面取出来存成你想要的各种格式
    x2ve
        7
    x2ve  
       2019-08-15 12:29:23 +08:00
    哟哟切克闹 etl 来一套
    hoyixi
        8
    hoyixi  
       2019-08-15 12:31:24 +08:00   ❤️ 1
    很多数据库,本身就支持把数据备份成 csv
    littlewing
        9
    littlewing  
       2019-08-15 12:32:11 +08:00 via iPhone
    mysql 可以直接一条 sql 导出的
    话说你是 select * from table ?
    imherer
        10
    imherer  
    OP
       2019-08-15 12:49:31 +08:00
    @littlewing 嗯,因为是全字段都需要的。
    liprais
        11
    liprais  
       2019-08-15 13:03:25 +08:00   ❤️ 1
    pgdump 之后再处理呗
    多读点文档没啥坏处
    或者你可以用 spark 开多个 jdbc 链接做导出
    encro
        12
    encro  
       2019-08-15 13:46:06 +08:00   ❤️ 2
    读数据,一次 10 万以内的读;
    写 csv,一行一行写;
    根本就不占用内存。
    Kaiux
        13
    Kaiux  
       2019-08-15 14:13:41 +08:00   ❤️ 1
    https://github.com/alibaba/easyexcel
    阿里巴巴开源的, 虽然是 Java 做的,但是思路可以参考
    我本地导出 10 万数据都是 5 秒以内, 没有 OOM
    希望可以帮到你
    wayne1027
        14
    wayne1027  
       2019-08-15 14:18:23 +08:00
    @auser #5 csv 没有行数限制吧?哪来的 65535 ?几百年前的 xls?
    maierhuang
        15
    maierhuang  
       2019-08-15 14:29:29 +08:00
    copy 命令了解一下 就是不知道怎么和 go 结合
    Michaelssss
        16
    Michaelssss  
       2019-08-15 14:34:41 +08:00
    1G 左右 CSV,excel 2016 就已经打不开了。。我不确定你说大量要多大。。。
    SbloodyS
        17
    SbloodyS  
       2019-08-15 14:38:51 +08:00   ❤️ 1
    copy 导出 csv,Excel 一个 sheet 最多 104W,按小于等于 104W 行的数量 分片处理为多个 sheet 就好
    追加数据就新开一个 sheet 来存
    之前试过 1 亿行数据十几分钟就好了,很快的,不过一般 Excel 10 个左右的满 sheet 打开就慢到爆炸了
    imherer
        18
    imherer  
    OP
       2019-08-15 14:40:02 +08:00
    @maierhuang 嗯,我也搜到了这个命令,直接写成 sql 语句在程序里执行就行,还挺好用的
    augustheart
        19
    augustheart  
       2019-08-15 15:06:14 +08:00
    虽然我不懂 go,但是 csv 不就是文本?按文本处理你想怎么追加都行。
    maierhuang
        20
    maierhuang  
       2019-08-15 15:08:18 +08:00
    @imherer 那能用 copy 就完美解决
    auser
        21
    auser  
       2019-08-15 15:15:12 +08:00
    @wayne1027

    文件格式本身不限制,但身边环境大家常用的 Numbers.app 不支持打开 65535 行的 csv.

    最麻烦的是这个格式在与 Windows 系统交换时经常遇到乱码问题。目前统一使用 xlsx.
    imherer
        22
    imherer  
    OP
       2019-08-15 15:20:07 +08:00
    @auser 对,我现在也遇到了,在 Mac 下导出 csv 中文正常,windows 下导出就乱码…… 头疼……
    imherer
        23
    imherer  
    OP
       2019-08-15 15:31:09 +08:00
    @maierhuang 嗯,但是好像不支持参数
    COPY (SELECT * FROM test LIMIT $1) to '/absolute path/file_name.csv' with csv header
    直接报错
    auser
        24
    auser  
       2019-08-15 15:31:15 +08:00   ❤️ 1
    @imherer

    哈哈,对于文本格式的文件,还有 BOM 这个可能出现的“小坏蛋”呢。

    几年前刚接触新团队的时候,现有成员全部用的 Windows 开发。Windows 的 git 有个换行符自动转换功能,可是很多人安装 git for windows 的时候一直下一步或者压根就不知道这个问题,加上 git 图形版客户端与编辑器各用各的,源码文件跟 git diff 没办法看。我还遇到一个项目内的某些源码文件竟然编码不唯一的神奇问题,很诡异,也很好奇怎么会出现这种情况。这个大型 C++项目在 VS 下有时候编译不过去,最后发现也是文件编码问题造成的。

    这是纯文本文件固有的问题。

    所以有统一的跨平台格式,优先选择它可能会少遇到些绊脚石。
    Vegetable
        25
    Vegetable  
       2019-08-15 15:36:40 +08:00   ❤️ 1
    xls 65535,xlsx 大概是一百来万行,所以这两个基本就不考虑了,没等 oom 本身就已经装不了这么多数据了.
    只能是 csv.csv 可以 stream 进去,完全不存在 oom 的问题.
    gamexg
        26
    gamexg  
       2019-08-15 15:44:25 +08:00   ❤️ 1
    csv 格式省心, 打开文件一行一行的写,内存不会炸。

    没用过 pg@go,查了下也是支持 stream 的: https://github.com/go-pg/pg/issues/82
    gamexg
        27
    gamexg  
       2019-08-15 15:57:47 +08:00
    @gamexg #26 确认了,lib/pg 也是默认就是游标方式工作,直接用 go 的 sql 标准库就可以处理大量数据。
    mengdodo
        28
    mengdodo  
       2019-08-15 16:00:33 +08:00
    pandas 就问你要多大
    habicat
        29
    habicat  
       2019-08-15 16:12:08 +08:00
    pandas+1
    lmingzhi08
        30
    lmingzhi08  
       2019-08-15 16:47:25 +08:00   ❤️ 1
    感觉可以先将数据表 copy to 导出一个 csv 文件,csv 本身是一个文件文件,然后可以按行数切割文件了

    http://burnignorance.com/linux-tips-and-tricks/splitting-a-large-csv-files-into-smaller-files-in-ubuntu/

    To split large CSV (Comma-Separated Values) file into smaller files in Linux/Ubuntu use the split command and required arguments.

    split -d -l 10000 source.csv tempfile.part.

    Here “ 10000 ” indicates that each new file contains 10000 records,you change it to any number you want to, the smaller files would have that number of records. The new files are created with numbers suffixed. For example in this case the file names are tempfile.part.00.csv, tempfile.part.01.csv and so on.
    changdy
        31
    changdy  
       2019-08-15 20:32:44 +08:00
    我也在好奇 excel 用什么语言做导出比较合适.
    楼上有提到过 阿里巴巴的 开源项目.
    但是个人感觉从命名到来看 代码并不美观.并且封装的也不够完整.
    想问下 V2EX 的各位道友 用什么语言的那个项目比较合适?

    @imherer 友情提示 win offcie 默认是 gbk... 这方面都不如 wps.
    abcbuzhiming
        32
    abcbuzhiming  
       2019-08-15 23:34:19 +08:00
    @Michaelssss excel 是存在最大行限制的。所以如果打算导出为 excel,无论是 xls 还是 xlsx 都得考虑最大行限制
    danmu17
        33
    danmu17  
       2019-08-16 02:42:17 +08:00
    不出意外的话你的需求就是 pandas 典型的应用场景。
    loading
        34
    loading  
       2019-08-16 07:03:44 +08:00 via Android
    先查总条数
    然后循环分批读写,每次一万行写一个 csv
    然后根据总天数得到你 csv 个数,你可以预设到一个 unix 时间戳建立的文件夹,叫 1.csv 2.csv

    最后是打压缩包下载还是 copy 拼起来就看你们了。
    windedge
        35
    windedge  
       2019-08-16 07:09:01 +08:00
    用 petl, 对文本文件(csv), petl 内存占用很低, 写法也很简单:

    ```
    import petl as etl
    import psycopg2
    connection = psycopg2.connect('dbname=example user=postgres')
    table = etl.fromdb(connection, 'SELECT * FROM example')
    etl.tocsv(table1, 'example.csv')
    ```
    IamNotShady
        36
    IamNotShady  
       2019-08-16 07:48:31 +08:00
    写到文件就行了 分批次写
    Mithril
        37
    Mithril  
       2019-08-16 08:01:20 +08:00
    写大文件可以 File Mapping,很容易。
    但是不建议做成 CSV,这东西不是一个非常标准的规范,总有各种各样的问题。
    如果你导出的数据只是内部使用,可以直接导出到 Sqlite 或者 Access 数据库。
    这种的 Excel 也能直接用
    sonyxperia
        38
    sonyxperia  
       2019-08-16 08:28:03 +08:00
    直接用 dump 嘛
    wqzjk393
        39
    wqzjk393  
       2019-08-16 10:09:12 +08:00
    先考虑下你是不是真的需要这么多数据这么多字段,保存成 excel csv 以后无论用什么工具打开都会非常慢,保存下来真的能用到么?
    maierhuang
        40
    maierhuang  
       2019-08-16 10:28:09 +08:00
    @imherer 那直接在外面拼接好了 sql 再执行
    jalena
        41
    jalena  
       2019-08-16 16:17:27 +08:00
    不是又个命令是 copy 嘛,可以直接输出到文件
    cmonkey
        42
    cmonkey  
       2019-08-16 16:20:38 +08:00
    是时候让公司那几个人学习 PostgreSQL 了,给他们开 read 权限
    imherer
        43
    imherer  
    OP
       2019-08-16 16:24:24 +08:00
    @cmonkey 😂😅
    imherer
        44
    imherer  
    OP
       2019-08-16 16:24:40 +08:00
    @jalena 嗯,目前就是这样实现的
    yogogo
        45
    yogogo  
       2019-08-16 16:51:21 +08:00
    先导出到服务器,再把文件下载
    shangfabao
        46
    shangfabao  
       2019-08-16 16:51:24 +08:00
    用 postgresql 自带命令执行不好么??
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   983 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 21:59 · PVG 05:59 · LAX 14:59 · JFK 17:59
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.