csv存入influxdb python influxdb导出csv

转载

kcoufee 2024-06-16 16:31:05

文章标签 数据库 java CSV ci spring 文章分类 Python 后端开发

一直在想如何实现响应式导出数据，之前一直研究excel 的xlsx 格式的文件的响应式导出，但是因为当前excel 导出的框架都是阻塞的，实现不了响应式导出（在我当前的认知中），结果就有了csv 的文件格式导出。希望在后续对响应式开发不断的探索中能够找到解决的办法。
根据我的测试下述代码可以实现秒级下载，单表 100万条数据只需要几秒（两秒左右）就可以持续下载，下载完成耗时20多秒，200万条数据50多秒。
先上代码

@GetMapping("/export")
    public Flux<DefaultDataBuffer> export(ServerHttpResponse response) {
        Long startTime = System.currentTimeMillis();
        // 设置被下载的文件名称
        response.getHeaders().set(org.springframework.http.HttpHeaders.CONTENT_DISPOSITION, "attachment; " +
                "filename=demo.csv");
        response.getHeaders().add("Accept-Ranges", "bytes");
        Flux flux = userRepository.searchBy(PageRequest.of(1, 1000000))
                .map(userDO -> csv(userDO))// 将数据转换为csv 格式
                .map(str -> csvToDataBuffer(str))//将csv 字符串 转换为 dataBataBuffer
                .doFinally((s) -> { // 完成后打印耗时
                    long l = System.currentTimeMillis() - startTime;
                    System.out.println("导出耗时2：" + l);
                });
        // 合并标题和数据
       return Flux.merge(Mono.just(title()).map(s -> csvToDataBuffer(s)),flux);
    }

    private String title(){
        StringBuilder builder = new StringBuilder();
        builder.append("主键id").append(CSV_COLUMN_SEPARATOR);
        builder.append("年龄").append(CSV_COLUMN_SEPARATOR);
        builder.append("性别").append(CSV_COLUMN_SEPARATOR);
        builder.append("身份证号").append(CSV_COLUMN_SEPARATOR);
        builder.append("身高").append(CSV_COLUMN_SEPARATOR);
        builder.append("名字").append(CSV_COLUMN_SEPARATOR);
        builder.append("体重").append(CSV_COLUMN_SEPARATOR);
        builder.append(CSV_RN);
        return builder.toString();
    }

    private String csv(UserDO userDO) {
        StringBuilder builder = new StringBuilder();
        builder.append(userDO.getId()).append(CSV_COLUMN_SEPARATOR);
        builder.append(userDO.getAge()).append(CSV_COLUMN_SEPARATOR);
        builder.append(userDO.getSex()).append(CSV_COLUMN_SEPARATOR);
        builder.append(userDO.getIdCard()).append(CSV_COLUMN_SEPARATOR);
        builder.append(userDO.getHeight()).append(CSV_COLUMN_SEPARATOR);
        builder.append(userDO.getName()).append(CSV_COLUMN_SEPARATOR);
        builder.append(userDO.getWeight()).append(CSV_COLUMN_SEPARATOR);
        builder.append(CSV_RN);
        return builder.toString();
    }

    private DefaultDataBuffer csvToDataBuffer(String user) {
        DefaultDataBuffer dataBuffer = new DefaultDataBufferFactory().allocateBuffer();
        return dataBuffer.write(user.getBytes(StandardCharsets.UTF_8));
    }

Repository

public interface UserRepository extends R2dbcRepository<UserDO, Long> {
    Flux<UserDO> searchBy(Pageable pageable);
}

UserDO

package com.tiktok.ads.sdk.domain;

import com.tiktok.ads.sdk.util.CertNoUtil;
import com.tiktok.ads.sdk.util.RandInfo;
import lombok.Data;
import lombok.experimental.Accessors;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;

@Data
@Table("user")
@Accessors(chain = true)
public class UserDO {
    @Id
    private Long id;
    private String name;
    private String weight;
    private String height;
    private String idCard;
    private String sex;
    private Integer age;

    public static UserDO init() {
        String sex = RandInfo.getSex();
        return new UserDO().setHeight(RandInfo.getHeightBySex(sex))
                .setAge(RandInfo.getAge())
                .setIdCard(CertNoUtil.getRandomID())
                .setSex(sex)
                .setName(RandInfo.getFamilyName() + RandInfo.getNameBySex(sex))
                .setWeight(RandInfo.getWeightBySex(sex));
    }
}

配置文件

# Spring
spring:
  #配置 Jpa
  jpa:
    show-sql: true #打印执行的sql语句，false则不打印sql
    properties:
      hibernate:
        ddl-auto: none
        dialect: org.hibernate.dialect.MySQL5InnoDBDialect
    open-in-view: true
  r2dbc:
    password: root
    username: root
    url: r2dbc:mysql://localhost:3306/r2dbc

表结构

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sex` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `height` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `weight` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `id_card` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5535554 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

表总的数据量

个人造了500万条数据

csv存入influxdb python influxdb导出csv_CSV

下面是自动生成测试数据的代码

package com.tiktok.ads.sdk.component;

import com.tiktok.ads.sdk.domain.UserDO;
import com.tiktok.ads.sdk.mapper.UserRepository;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;
import reactor.core.publisher.Flux;

import javax.annotation.Resource;

@Component
public class Test implements CommandLineRunner {
    @Resource
    private UserRepository userRepository;

    @Override
    public void run(String... args) throws Exception {
        Flux.range(1, 10000000)
                .map((i) -> UserDO.init()) // 初始化用户数据
                .buffer(1500) // 缓存1500个
                .flatMap(users -> userRepository.saveAll(users).log()) // 保存数据
                .subscribe();
        System.out.println("---------------------------------------");
    }
}

网上找的工具类

package com.tiktok.ads.sdk.util;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Random;
 
public class CertNoUtil {
 
    // 18位身份证号码各位的含义:
    // 1-2位省、自治区、直辖市代码；
    // 3-4位地级市、盟、自治州代码；
    // 5-6位县、县级市、区代码；
    // 7-14位出生年月日，比如19670401代表1967年4月1日；
    // 15-17位为顺序号，其中17位（倒数第二位）男为单数，女为双数；
    // 18位为校验码，0-9和X。
    // 作为尾号的校验码，是由把前十七位数字带入统一的公式计算出来的，
    // 计算的结果是0-10，如果某人的尾号是0－9，都不会出现X，但如果尾号是10，那么就得用X来代替，
    // 因为如果用10做尾号，那么此人的身份证就变成了19位。X是罗马数字的10，用X来代替10
 
    public static String getRandomID() {
        String id = "";
        // 随机生成省、自治区、直辖市代码 1-2
        String provinces[] = { "11", "12", "13", "14", "15", "21", "22", "23",
                "31", "32", "33", "34", "35", "36", "37", "41", "42", "43",
                "44", "45", "46", "50", "51", "52", "53", "54", "61", "62",
                "63", "64", "65", "71", "81", "82" };
        String province = provinces[new Random().nextInt(provinces.length - 1)];
        // 随机生成地级市、盟、自治州代码 3-4
        String citys[] = { "01", "02", "03", "04", "05", "06", "07", "08",
                "09", "10", "21", "22", "23", "24", "25", "26", "27", "28" };
        String city = citys[new Random().nextInt(citys.length - 1)];
        // 随机生成县、县级市、区代码 5-6
        String countys[] = { "01", "02", "03", "04", "05", "06", "07", "08",
                "09", "10", "21", "22", "23", "24", "25", "26", "27", "28",
                "29", "30", "31", "32", "33", "34", "35", "36", "37", "38" };
        String county = countys[new Random().nextInt(countys.length - 1)];
        // 随机生成出生年月 7-14
        SimpleDateFormat dft = new SimpleDateFormat("yyyyMMdd");
        Date beginDate = new Date();
        Calendar date = Calendar.getInstance();
        date.setTime(beginDate);
        date.set(Calendar.DATE,
                date.get(Calendar.DATE) - new Random().nextInt(365 * 100));
        String birth = dft.format(date.getTime());
        // 随机生成顺序号 15-17
        String no = new Random().nextInt(999) + "";
        // 随机生成校验码 18
        String checks[] = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9",
                "X" };
        String check = checks[new Random().nextInt(checks.length - 1)];
        // 拼接身份证号码
        id = province + city + county + birth + no + check;
 
        return id;
    }
 
}

package com.tiktok.ads.sdk.util;
 
import java.util.Random;
 
/**
 * @version 1.0
 * @PACKAGE_NAME: com.example.searchdemo.search.controller
 * @date 2021/4/29 11:14 周四
 */
public class RandInfo {
 
   static String familyName1 = "赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻水云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳鲍史唐费岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅卞齐康伍余元卜顾孟平"
            + "黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计成戴宋茅庞熊纪舒屈项祝董粱杜阮席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田胡凌霍万柯卢莫房缪干解应宗丁宣邓郁单杭洪包诸左石崔吉"
            + "龚程邢滑裴陆荣翁荀羊甄家封芮储靳邴松井富乌焦巴弓牧隗山谷车侯伊宁仇祖武符刘景詹束龙叶幸司韶黎乔苍双闻莘劳逄姬冉宰桂牛寿通边燕冀尚农温庄晏瞿茹习鱼容向古戈终居衡步都耿满弘国文东殴沃曾关红游盖益桓公晋楚闫";
   static  String familyName2 = "欧阳太史端木上官司马东方独孤南宫万俟闻人夏侯诸葛尉迟公羊赫连澹台皇甫宗政濮阳公冶太叔申屠公孙慕容仲孙钟离长孙宇文司徒鲜于司空闾丘子车亓官司寇巫马公西颛孙壤驷公良漆雕乐正宰父谷梁拓跋夹谷轩辕令狐段干百里呼延东郭南门羊舌微生公户公玉公仪梁丘公仲公上公门公山公坚左丘公伯西门公祖第五公乘贯丘公皙南荣东里东宫仲长子书子桑即墨达奚褚师吴铭";
    static String girlName = "秀娟英华慧巧美娜静淑惠珠翠雅芝玉萍红娥玲芬芳燕彩春菊兰凤洁梅琳素云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧璐娅琦晶妍茜秋珊莎锦黛青倩婷姣婉娴瑾颖露瑶怡婵雁蓓纨仪荷丹蓉眉君琴蕊薇菁梦岚苑婕馨瑗琰韵融园艺咏卿聪澜纯毓悦昭冰爽琬茗羽希宁欣飘育滢馥筠柔竹霭凝晓欢霄枫芸菲寒伊亚宜可姬舒影荔枝思丽";
    static String boyName = "伟刚勇毅俊峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘";
 
    /**
     * 功能：随机产生姓氏
     *
     * @return
     */
    public static String getFamilyName() {
        String str = "";
        int randNum = new Random().nextInt(2) + 1;
        int strLen = randNum == 1 ? familyName1.length() : familyName2.length();
        int index = new Random().nextInt(strLen);
        if (randNum == 1) {
            str = String.valueOf(familyName1.charAt(index));
        } else {
            str = (index & 1) == 0 ? familyName2.substring(index, index + 2) :
                    familyName2.substring(index - 1, index + 1);
        }
        return str;
    }
 
    /**
     * 功能：随机产生性别
     *
     * @return
     */
    public static String getSex() {
        int randNum = new Random().nextInt(2) + 1;
        return randNum == 1 ? "男" : "女";
    }
 
    /**
     * 功能：传入性别参数，依据性别产生名字
     *
     * @param sex
     * @return
     */
    public static String getNameBySex(String sex) {
        int randNum = new Random().nextInt(2) + 1;
        int strLen = sex.equals("男") ? boyName.length() : girlName.length();
        int index = (randNum & 1) == 0 ? new Random().nextInt(strLen - 1) :
                new Random().nextInt(strLen);
        return sex.equals("男") ? boyName.substring(index, index + randNum) :
                girlName.substring(index, index + randNum);
    }
 
    /**
     * 功能：随机产生18-21的整数
     *
     * @return
     */
    public static int getAge() {
        return new Random().nextInt(4) + 18;
    }


    public static String getHeightBySex(String sex) {
      if(sex.equals("男")){
          return get()+170+"";
      }
      return get()+150+"";
    }
    public static Integer get(){
        int max=20;
        int min=10;
        Random random = new Random();
        int s = random.nextInt(max)%(max-min+1) + min;
        return s;
    }

    public static String getWeightBySex(String sex) {
        if(sex.equals("男")){
            return get()+130+"";
        }
        return get()+90+"";
    }
}

pom

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.1</version>
        <relativePath/>
    </parent>

    <groupId>org.example</groupId>
    <artifactId>tiktokDemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        </dependency>
        <dependency>
            <groupId>dev.miku</groupId>
            <artifactId>r2dbc-mysql</artifactId>
            <version>0.8.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.squareup.okhttp3</groupId>
            <artifactId>okhttp</artifactId>
            <version>4.10.0</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.11.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.jsoup/jsoup -->
        <dependency>
            <groupId>org.jsoup</groupId>
            <artifactId>jsoup</artifactId>
            <version>1.15.3</version>
        </dependency>

        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpmime</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpcore</artifactId>
            <version>4.4.15</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webflux</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectreactor</groupId>
            <artifactId>reactor-spring</artifactId>
            <version>1.0.1.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>2.0.12</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webflux</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-integration</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>9</source>
                    <target>9</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。