如何处理大型CSV文件是最佳方法？

Question

如何处理大型CSV文件是最佳方法？

javasql-servercsvarchitecture

7

我有一个第三方系统，每天会生成大量数据（这些是存储在FTP上的CSV文件）。生成的文件类型有3种：

每15分钟（2个文件）。这些文件相当小（约2 Mb）
每天下午5点左右（约200-300 MB）
每天午夜（此CSV文件约1 GB）

总体而言，4个CSV文件占用1.5 GB。但我们应该考虑到有些文件每15分钟生成一次。这些数据也需要聚合（过程不太难，但肯定需要时间）。我需要快速响应。我在思考如何存储这些数据以及整体实施。

我们使用java技术栈。数据库是MS SQL Standard。根据我的测量，MS SQL Standard与其他应用程序一起无法处理这样的负载。我能想到以下解决方案：

将其升级为带有独立服务器的MS SQL Enterprise。
在单独的服务器上使用PostgreSQL。现在我正在为此方法进行PoC测试。

您会建议什么？可能有更好的替代方案。

编辑#1

那些大文件是每天的新数据。

- Yuri

2

1GB的CSV文件，这将是一个巨大的负载。我敢打赌前一天有很多重复记录。如果你能联系到生成文件的人，我们可能需要要求他们只发送更改后的数据。这是大多数系统为减少负载所做的。 - Zein Makki

是的，你说得对。在这种情况下，负载很大。我还没有完全讨论有关数据的要求，但这就是我从规格说明中得到的。根据规格说明，没有中间文件可以作为更新文件。它每次都包含新的/更新的数据。如果无法进行更新-我希望这些数据每天都会过时。 - Yuri

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Zein Makki

请详细说明这个大文件：它每天都有新数据还是累积的？ - gofr1

你所说的“process”是指导入/更新/交叉处理还是命令？ - Dennis C

4个回答

1

你可以考虑研究Apache Spark项目。在验证和整理数据后，可以使用Presto运行查询。

- Radu

1

你可以使用uniVocity-parsers来尽可能快地处理CSV，因为这个库提供了最快速的CSV解析器。我是这个库的作者，它是开源和免费的（Apache V2许可证）。

现在，为了将数据加载到数据库中，你可以尝试univocity框架（商业版）。我们使用它来快速地将大量数据加载到诸如SQL Server和PostgreSQL之类的数据库中，每秒从25K到200K行不等，具体取决于数据库及其配置。

以下是一个简单的示例，展示了从你的CSV迁移代码的样子：

public static void main(String ... args){
    //Configure CSV input directory
    CsvDataStoreConfiguration csv = new CsvDataStoreConfiguration("csv");
    csv.addEntitiesFromDirectory(new File("/path/to/csv/dir/"), "ISO-8859-1");

    //should grab column names from CSV files
    csv.getDefaultEntityConfiguration().setHeaderExtractionEnabled(true);

    javax.sql.DataSource dataSource = connectToDatabaseAndGetDataSource(); //specific to your environment

    //Configures the target database
    JdbcDataStoreConfiguration database = new JdbcDataStoreConfiguration("database", dataSource);

    //Use only for postgres - their JDBC driver requires us to convert the input Strings from the CSV to the correct column types.
    database.getDefaultEntityConfiguration().setParameterConversionEnabled(true);

    DataIntegrationEngine engine = Univocity.getEngine(new EngineConfiguration(csv, database));

    //Creates a mapping between data stores "csv" and "database"
    DataStoreMapping mapping = engine.map(csv, database);

    // if names of CSV files and their columns match database tables an their columns
    // we can detect the mappings from one to the other automatically
    mapping.autodetectMappings();

    //loads the database.
    engine.executeCycle();

}

为了提高性能，该框架允许您管理数据库模式并执行操作，例如删除约束和索引、加载数据以及重新创建它们。如果需要，数据和模式转换也得到了很好的支持。希望这可以帮助您。

- Jeronimo Backes

0

Pentaho数据集成（或类似的ETL工具）可以处理将数据导入SQL数据库并实时进行聚合。PDI有社区版，可以独立运行或通过Java API运行。

- lukfi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Yuri · Accepted Answer

好的。在花费了一些时间解决这个问题（包括阅读，咨询，实验，进行多个 PoC）后，我想出了以下解决方案。

Tl;dr

数据库: PostgreSQL 适用于 CSV 格式，免费且开源。

工具: Apache Spark 是此类任务的良好选择。性能良好。

数据库

关于数据库，这是一个重要的决定。应该选择什么，并如何在未来处理这样大量的数据。它肯定应该是单独的服务器实例，以不会给主数据库实例带来额外的负载并阻止其他应用程序。

NoSQL

我考虑过在这里使用 Cassandra，但现在这个解决方案过于复杂。 Cassandra 没有 ad-hoc 查询。 Cassandra 的数据存储层基本上是一个键值存储系统。这意味着您必须围绕所需查询“建模”数据，而不是围绕数据本身的结构。

RDBMS

我不想在这里过度设计。所以我做了选择。

MS SQL Server

这是一种方法，但缺点是价格高昂。企业版考虑到我们的硬件成本很高。关于定价，您可以阅读此政策文件。

这里的另一个缺点是对CSV文件的支持。这将是我们的主要数据来源。 MS SQL Server不能导入或导出CSV。

MS SQL Server默默地截断文本字段。
MS SQL Server的文本编码处理出了问题。

MS SQL Server因不理解引号或转义而抛出错误消息。有关该比较的更多内容，请参阅文章PostgreSQL vs. MS SQL Server。

PostgreSQL

这个数据库是一个成熟的产品，经过了很多实战考验。我从别人那里听到了很多积极的反馈（当然，也有一些权衡）。它具有更经典的SQL语法、良好的CSV支持，此外，它还是开源的。

值得一提的是SSMS比PGAdmin要好得多。SSMS有自动完成功能，可以得到多个结果（当您运行多个查询并获得多个结果时，在PGAdmin中只会得到最后一个结果）。

无论如何，现在我正在使用JetBrains的DataGrip。

处理工具

我已经查看了Spring Batch和Apache Spark。对于这个任务来说，Spring Batch有点太低级了，而且如果将来需要扩展，Apache Spark提供了更容易扩展的能力。不管怎样，Spring Batch也可以完成这项工作。

关于Apache Spark的示例，代码可以在learning-spark项目中找到。目前我的选择是Apache Spark。