Hi everyone, i have around 146 Million rows in a SQL server, i want to migrate it to questDB instance, i was thinking writing a script to migrate data, is there a direct way to do this ?

hi Akshay, can you export data from the SQL server as CSV files? 146M of rows is not a small table, but it’s not terribly large either. Let’s assume each row takes 1KB when exported as CSV. You would end-up with 146GB of data, that’s not too bad and QuestDB imports it via SQL COPY: https://questdb.io/docs/guides/importing-data/ Some tips: • Make sure your CSV files are ordered by time • Export a small subset of your table first, practice the import procedure and only then do it with full data set • Use fast disks if you can - this make a big difference in CSV import performance

hey @Akshay Hartalkar, what hardware will you use for the import?

the questdb is installed on a 2vCPU and 4GB ram aws instance

oh, that’s a rather small box. is the disk instance-local or remote/EBS?

another option to migrate the data with a bit of code. in Java it would look something like this:

import io.questdb.client.Sender;
import java.sql.*;

public class DataMigration {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try (Sender sender = Sender.builder().address("questdbhost:9009").build()) {
            String dbURL = "jdbc:sqlserver://...";
            String user = "sa";
            String pass = "pwd";
            conn = DriverManager.getConnection(dbURL, user, pass);

            String sql = "SELECT * FROM table_to_migrate";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                // read record from SQL Server
                String strField = rs.getString("NAME");
                String symbolField = rs.getString("GENDER"); //return "M" or "F"
                int intField = rs.getInt("AGE");

                // write to QuestDB
                sender.table("table_to_migrate")
                        .symbol("gender", symbolField)
                        .stringColumn("name", strField)
                        .longColumn("age", intField)
                        .atNow();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

that’s a good option too. assuming the target QuestDB table uses a designated timestamp then it would be good to use

ORDER BY

in the SQL query: To get results sorted by time. It will make the ingestion much faster.

SELECT * FROM table_to_migrate ORDER BY ts ASC;

There is command line tool to copy from Postgres https://github.com/questdb/questdb/tree/master/utils#copy-table-from-one-instance-to-another-using-postgres-wire-to-read-and-ilp-to-write again in java

Also note the code above uses .

atNow(),

which would assign as designated timestamp the ingestion time. If that’s not what you want, then you can use

.at(long)

to pass the nanoseconds value representing your timestamp

Thanks for all the replies, @Jaromir Hamala the instance might be small but doable for the use case, as for the storage its a EBS volume(gp2 300 IOPS) attached to the instance. i assume import CSV might put a huge load on server

you should consider moving to gp3. It is always cheaper than gp2 and more performant by default. https://aws.amazon.com/es/blogs/storage/migrate-your-amazon-ebs-volumes-from-gp2-to-gp3-and-save-up-to-20-on-costs/