将数百万行从Teradata传输到mySQL [英] Transferring Millions of rows from teradata to mySQL

问题描述

我必须将大约500万行数据从Teradata传输到MySQL.任何人都可以建议我以最快的方式在不使用文件系统的情况下通过网络执行此操作.我是Teradata和MySQL的新手.我想每周一次将此传输作为批处理作业运行，因此我正在寻找可以完全自动化的解决方案.任何建议或提示将不胜感激.

I have to transfer around 5 million rows of data from Teradata to MySQL. Can anyone please suggest me the fastest way to do this over the network, without using the filesystem. I am new to Teradata and MySQL. I want to run this transfer as a batch job on weekly basis, so I am looking for the solution which can be fully automated. Any suggestions or hints will be greatly appreciated.

我已经使用JDBC编写了代码，以从Teradata获取记录并将其插入MySQL.但这非常慢，因此我希望使该代码更高效.我之所以保持通用，是因为我的解决方案不受实现方式的束缚，在使现有代码更高效的同时，我也对其他替代方案持开放态度.但是我不想使用文件系统，因为维护或更新脚本并不容易.

I have already written the code using JDBC to get the records from the Teradata and insert them into the MySQL. But it is very slow, so I am looking to make that code more efficient. I kept in generic because I didn't have the solution to be constrained by my implementation, as along with making existing code more efficient I am open to other alternatives also. But I don't want to use the file system since it's not easier to maintain or update the scripts.

我的实现:

从teradata获取记录:

connection  =   DBConnectionFactory.getDBConnection(SOURCE_DB);

    statement = connection.createStatement();
    rs = statement.executeQuery(QUERY_SELECT);
    while (rs.next()) {

        Offer offer = new Offer();
        offer.setExternalSourceId(rs.getString("EXT_SOURCE_ID"));
        offer.setClientOfferId(rs.getString("CLIENT_OFFER_ID"));
        offer.setUpcId(rs.getString("UPC_ID"));

        offers.add(offer);
    }

在mySQL中插入记录:

int count = 0;
    if (isUpdated) {
        for (Offer offer : offers) {

            count++;

            stringBuilderUpdate = new StringBuilder();
            stringBuilderUpdate = stringBuilderUpdate
                    .append(QUERY_INSERT);

            stringBuilderUpdate = stringBuilderUpdate.append("'"
                    + offer.getExternalSourceId() + "'");

            statement.addBatch(stringBuilderUpdate.toString());

            queryBuilder = queryBuilder.append(stringBuilderUpdate
                    .toString() + SEMI_COLON);

            if (count > LIMIT) {
                countUpdate = statement.executeBatch();
                LOG.info("DB update count : " + countUpdate.length);
                count = 0;
            }

        }
        if (count > 0) {
            // Execute batch
            countUpdate = statement.executeBatch();
        }

任何人都可以告诉我是否可以使此代码更高效???

Can anybody please tell me if we can make this code more efficient ???

谢谢

PS:由于此代码可以正常工作，请忽略上述代码中的语法错误.由于复制和粘贴，某些信息可能会丢失.

PS: Please ignore the syntax error in above code as this code is working fine. Some info might be missing because of copy and paste.

推荐答案

mysqlimport ，它是LOAD DATA INFILE的命令行界面，它涉及从文件加载数据，最好是驻留在本地文件系统上.

The fastest method of importing data to MySQL is by using LOAD DATA INFILE or mysqlimport, which is a command line interface to LOAD DATA INFILE and it involves loading data from a file, preferably residing on a local filesystem.

从文本文件加载表时，请使用LOAD DATA INFILE.这是 通常比使用INSERT语句快20倍.

When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements.

因此，尽管您实际上不想使用文件系统，但我还是建议您考虑创建转储到文件，将其传输到MySQL服务器，并使用上述方法加载数据.

Therefore despite the fact that you don't want to use the filesystem I'd suggest to consider creating a dump to a file, transfer it to a MySQL server and use above mentioned means to load the data.

所有这些任务都可以通过脚本完全自动化.

All these tasks can be fully automated via scripting.

这篇关于将数百万行从Teradata传输到mySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！