excel 到dremio 查询处理

参考解决方案

 

 

原理说明

用户通过rest api 请求上传excel 文件，可以存储到s3中，然后我们可以通过监听s3的webhooks 或者上传的时候进行格式转换同时存储s3
格式可以支持的包含jsonlines，parquet。。。同时基于dremio直接查询s3的能力，方便的进行业务分析

环境准备

 

version: "3"

services:

  dremio:

      image: dremio/dremio-oss

      ports:

        - "9047:9047"

  s3:

      image: minio/minio

      environment:

        - "MINIO_ACCESS_KEY=minio"

        - "MINIO_SECRET_KEY=minio123"

      command: server /data  --console-address ":9001"

      ports:

        - "9000:9000"

        - "9001:9001"

参考代码

• 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>

 

    <groupId>org.example</groupId>

    <artifactId>excel-reader</artifactId>

    <version>1.0-SNAPSHOT</version>

 

    <properties>

        <maven.compiler.source>8</maven.compiler.source>

        <maven.compiler.target>8</maven.compiler.target>

    </properties>

 

    <dependencies>

        <dependency>

            <groupId>org.projectlombok</groupId>

            <artifactId>lombok</artifactId>

            <version>1.18.20</version>

            <scope>provided</scope>

            <optional>true</optional>

        </dependency>

        <dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>easyexcel</artifactId>

            <version>2.2.8</version>

        </dependency>

        <dependency>

            <groupId>io.minio</groupId>

            <artifactId>minio</artifactId>

            <version>8.3.0</version>

            <exclusions>

                <exclusion>

                    <groupId>com.fasterxml.jackson.core</groupId>

                    <artifactId>jackson-core</artifactId>

                </exclusion>

                <exclusion>

                    <groupId>com.fasterxml.jackson.core</groupId>

                    <artifactId>jackson-annotations</artifactId>

                </exclusion>

                <exclusion>

                    <groupId>com.fasterxml.jackson.core</groupId>

                    <artifactId>jackson-databind</artifactId>

                </exclusion>

            </exclusions>

        </dependency>

        <dependency>

            <groupId>commons-io</groupId>

            <artifactId>commons-io</artifactId>

            <version>2.11.0</version>

        </dependency>

        <dependency>

            <groupId>com.fasterxml.jackson.core</groupId>

            <artifactId>jackson-databind</artifactId>

            <version>2.12.5</version>

        </dependency>

    </dependencies>

</project> 

• java 代码
  代码使用了easyexcel 进行文件读取，同时基于jackson 生成jsonlines 文件，之后存储s3

 

package com.dalong;

import com.alibaba.excel.EasyExcel;

import com.alibaba.excel.context.AnalysisContext;

import com.alibaba.excel.enums.CellExtraTypeEnum;

import com.alibaba.excel.event.AnalysisEventListener;

import com.alibaba.excel.metadata.CellExtra;

import com.fasterxml.jackson.databind.ObjectMapper;

import com.fasterxml.jackson.databind.SequenceWriter;

import io.minio.MinioClient;

import io.minio.PutObjectArgs;

import io.minio.errors.*;

import lombok.SneakyThrows;

 

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.security.InvalidKeyException;

import java.security.NoSuchAlgorithmException;

import java.util.ArrayList;

import java.util.List;

import java.util.function.Consumer;

 

public class Application {

    private static final ObjectMapper objectMapper = new ObjectMapper();

    private static MinioClient minioClient =

            MinioClient.builder()

                    .endpoint("http://localhost:9000")

                    .credentials("minio", "minio123")

                    .build();

    public static void main(String[] args) {

        readExcelFile();

    }

    public static void writeExcelContent2S3JSONLines(List<Object> input) throws ServerException, InsufficientDataException, ErrorResponseException, IOException, NoSuchAlgorithmException, InvalidKeyException, InvalidResponseException, XmlParserException, InternalException {

        try (ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()) {

            try (SequenceWriter seq = objectMapper.writer()

                    .withRootValueSeparator("\n").writeValues(byteArrayOutputStream)) {

                input.forEach(new Consumer<Object>() {

                    @SneakyThrows

                    @Override

                    public void accept(Object s) {

                        seq.write(s);

                    }

                });

                try (ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray())) {

                    minioClient.putObject(

                            PutObjectArgs.builder().bucket("demo").object("demoapp.ldjson").stream(

                                            byteArrayInputStream, -1, 10485760)

                                    .build());

                }

            } catch (IOException e) {

                e.printStackTrace();

            }

        }

 

 

    }

 

    private static void readExcelFile() {

        InputStream stream = Application.class.getClassLoader().getResourceAsStream("excel-jsonlines.xlsx");

        EasyExcel.read(stream, new AnalysisEventListener() {

            private List<Object> results = new ArrayList<>();

 

            @SneakyThrows

            @Override

            public void invoke(Object o, AnalysisContext analysisContext) {

                results.add(o);

                System.out.println(objectMapper.writeValueAsString(o));

            }

 

            @SneakyThrows

            @Override

            public void extra(CellExtra extra, AnalysisContext context) {

                System.out.println(extra.getType());

                System.out.println(objectMapper.writeValueAsString(extra));

            }

 

            @SneakyThrows

            @Override

            public void doAfterAllAnalysed(AnalysisContext analysisContext) {

                writeExcelContent2S3JSONLines(results);

                System.out.println("after");

            }

        }).extraRead(CellExtraTypeEnum.MERGE).sheet().doRead();

    }

}

 

• dremio 查询效果

 

 

说明

实际上dremio 是可以读取excel的，但是很多时候可能因为一些限制或者原因，造成使用问题（比如dremio 对于excel 文件大小限制，以及数据使用场景的问题）
基于上边的方式都是一个不错的选择

参考资料

https://jsonlines.org/examples/
https://www.yuque.com/easyexcel/doc/easyexcel