今天碰到一个需求，大量的数据需要导入Postgres，作为一个小白，记录一下操作过程，以做记录

  • 背景1： 使用Postgres作为存储
  • 导入的文件：count.txt

问题1：装一个postgreSQL 用什么版本？

  • 这个问题是第一个问题，也是可能对后续操作有很大影响的步骤。
  • 踩坑一：使用了所谓的最新稳定版本postgres:12
1. 那这是一个坑，问题在于很多客户端软件的支持还没有到位，或者说我安装的版本支持并不好，

2. 导致通过命令行建立的表在可视化中不可见，也就不能使用客户端软件类似于导入这一类的功能，因而失败

3. 而且一些错误的提示百度而不得，对于小白加上需要解决问题的方面来说，理智的做法是更换版本，而不是追根究底一些原理或者去提一些issue求救之类的做法
  • 而后就放弃了尝鲜，换了最广泛使用的9.x版本，使用docker选了9.6版本，构建了简单的Postgres数据库，这也是比较成熟，支持的比较广泛的版本（在这个版本上没有发现客户端和命令行的不同步不可见问题）；
docker 安装postgres
创建挂载卷：
docker volume create pgdata

下载镜像：
docker pull postgres:9.6

运行postgres:
docker run -it --rm -v pgdata:/var/lib/postgresql/data -p 5432:5432 --name postgres -d postgres:9.6

问题2：采用什么方式进行sql的导入？

  • 这个问题也是一个很重要的方向性决策，我的第一个想法是使用Java代码，原因是一些format可以调整，主键也可以自动生成插入，一切就会很顺利，然而操作起来并不是这样；
  • 问题一：用springboot写的接口，springboot（版本：2.0.4.RELEASE）对默认上传的问题大小有限制，默认1M，需要扩大需自行配置：
spring
  servlet:(请注意这个参数，根据springboot版本的不同，可能在http，也可能在这个servlet下)
    multipart:
      max-file-size: 10MB
      max-request-size: 10MB
  • 如果想继续执行，那么就要书写Excel导入的代码，这个成熟的代码真是太多了，springboot结合easypoi或者ExcelKit都是轻而易举，但是问题没有这么简单；
  • 当你正确的书写并执行，你就会看到，为什么导入的过程卡主迟迟没有响应？是程序的问题吗？
  • 那我原以为是某一个框架的问题，因而easypoi和ExcelKit我都有尝试，但是问题原来最终出在最基本的org.apache.poi，它的操作过程就是没有那么高效；
  • 根据网上也有人说，导入5000条就需要很久， 至少我等了一两分钟我的程序始终没有响应，那结论就是这个方法根本不可能支持大数据量；
  • 以上方法最终也被抛弃：
从整体角度来说，

1. HTTP下上传下载文件都不应该是超大文件（除非文件系统），不应该随意的放宽文件上传下载的限制来占用大量内存读写；

2. 并且Excel上传下载组件对大数据量的操作支持的可能性极低（需要进行百万级别量的交互）；
  • 另外可能高效的方式，除却复杂的数据同步插件或者组件的使用，最基础的也就是数据库自身支持的数据导入导出了，对于比较局限的数据库导入导出操作如何实现上述的需求呢？
  • Excel的文件导入需要借用一下简单的客户端工具（当然靠命令都可以实现），选择"导入"-> “选择文件” -> “列的默认处理（日期之类的）” -> “列的字段映射” -> "导入形式（append）"然后就开始了，这个过程没有问题；
  • 相对比较迅速地导入了几十万的数据；
  • 查看表，确认字段映射和数据导入准确；

问题3：对于没有的字段如何补充？

  • 这个问题主要面对的是，如何填补主键，在传输过来的数据中，只有业务数据，那每一条数据对应需要基于一个递增或者UUID类型的主键，这个如何实现？
  • 目前的需求，主键是UUID
  • 方法一：使用create extension方式，下面详述；
  • 方法二：没有默认安装，需要导入SQL，psql -U postgres -f 安装路径/share/contrib/uuid-ossp.sql ：
  • 以下是方法一的操作过程：
创建UUID扩展插件：
create extension "uuid-ossp";

测试正常使用:
select uuid_generate_v4();

如果有输出，那就没有问题了
  • create extension说明：
CREATE EXTENSION将新扩展加载到当前数据库中。 必须没有已加载的同名扩展。

加载扩展基本上等于运行扩展的脚本文件。 该脚本通常会创建新的SQL对象，例如函数，数据类型，运算符和索引支持方法。
 
 另外，CREATE EXTENSION记录所有已创建对象的标识，以便在发出DROP EXTENSION时可以再次删除它们。

加载扩展需要具有创建其组件对象所需的相同权限。 对于大多数扩展，这意味着需要超级用户或数据库所有者权限。 

运行CREATE EXTENSION的用户将成为扩展的所有者，以便以后进行权限检查，以及扩展程序脚本创建的任何对象的所有者。
  • 接下来需要对上述导入完成的数据添加主键；
  • update xxx set id = uuid_generate_v4() where id is null;
  • 首先使用默认带中横线的UUID形式，再修改;
  • update xxx set id = (SELECT REPLACE(ID,’-’,’’)) where id like ‘%-%’;
  • 这几个步骤也比较顺利，能够正确的初始化id的值;

问题4：（新增的需求）如何从现有数据中提炼出省市的信息?

  • 提炼省和市，主要就是将省和市的字段进行组合并输出去重
  • SELECT distinct(concat_ws(’,’,province,city)) FROM “xxx”;
  • 导出到文件后，通过文件导入，建立树形结构的省市关系；

附录

1.docker宿主机和容器如何传输文件？
1、从容器里面拷文件到宿主机
docker cp 容器名：要拷贝的文件在容器里面的路径       要拷贝到宿主机的相应路径 

docker cp postgres:/opt/area.csv /xxx/xxx/xxx/xxx/


2、从宿主机拷文件到容器里面
docker cp 要拷贝的文件路径 容器名：要拷贝到容器里面对应的路径

docker cp /opt/test.js tomcat:/xxx/xxx/xxx/xxxx/test/js
2.如何将postgres查询的结果输出到文件？
方法一：使用输出流的方式
  • 步骤一：\o test.txt， 打开一个文件
  • 步骤二：此时已经建立输出流，执行SQL即可将结果输出到文件，select * from a_txt;
  • 步骤三：\o ,关闭文件输出，如果不关闭，接下来的结果都会输出到你指定的文件，但是通过"\o"关闭了，就继续切换到屏幕输出结果
方法二：使用拷贝的方式
  • COPY (SELECT * FROM a_txt) TO ‘/tmp/query.csv’ (format csv, delimiter ‘,’)
3. 多字段合并输出？
  • 同一条数据中，将多个字段合并：concat_ws(’:’,a,b),a和b用冒号隔开
  • 同一个查询中，将多条结果合并：string_agg(c,’ \r\n ')，每条数据c之间使用\r\n隔开
  • 组合操作：string_agg(concat_ws(’:’,a,b),’ \r\n ’ order by a asc) as result ,结果就是： 0:a \r\n 1:b
4. 一些postgres常用命令
\l  列出数据库

\c test  切换数据库

\d  列出数据表

\d a_test  查看数据表
5.数据类型的映射
  • 在使用mysql的时候习惯了用LocalDate和LocalDateTime来记录时间，到了postgres并不可行；
  • mysql中：
程序中：LocalDate
mysql: date

程序中：LocalDateTime
mysql: datetime
  • postgres中：
程序中：Date
postgres: date

程序中：Date
postgres: timestamp

程序中：Date
postgres: timestamptz(带时区)
6.部分参考列表

如果有朋友，对于大数据量导入的场景有经验的，欢迎留言交流，我一定好好学习~