How to batch insert some data


#1

hi, i want to insert serveral thousands records into mapd for join query, mapdql can not support batch insert clause, it just do it one by one, and I also do not use copy…from files. How to do it quickly ? what’s your how to do it quickly ?


#2

@giverking my own go-to is pymapd for anything requiring parsing, cleaning, complex joins, etc. I’ve authored some examples that include plenty of create_table and load_table calls.

We also have a javascript library if you don’t like python, and several other tools and examples are available in our github repos.


#3

Do you have Java library to handle this ?


#4

We don’t have an officially supported one, but community member @jonrmayer built one and you may find it useful; here’s a thread describing it:


#5

Since that project is community-driven, we’ll do what we can to support it, but you may have to fork it and tweak it to get it to work.

Also, I hadn’t coded anything more complex than HTML and CSS and macros in at least 10 years before I started at OmniSci, and I learned python just to be able to do more with the platform. So if working with that java library isn’t a good option, I do recommend pymapd.


#6

Hi @giverking, we do support this in our JDBC. You can find the documentation on how to use prepared statements and its addBatch method here: https://www.omnisci.com/docs/latest/6_jdbc.html

I’ve provided some sample code below:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class BatchInsertExample {
	static final String JDBC_DRIVER = "com.mapd.jdbc.MapDDriver";
	static final String DB_URL = "jdbc:mapd:localhost:9091:mapd";
	static final String USER = "mapd";
	static final String PASS = "password";


    public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER, PASS);
			assertNotEquals(null, conn);

			ps = conn.prepareStatement("insert into tablename values (?)");
			
			ps.setString(1, "1");
			ps.addBatch();
			
			ps.setString(1, "2");
			ps.addBatch();

			ps.setString(1, "3");
			ps.addBatch();
			
			ps.setString(1, "4");
			ps.addBatch();
			
			ps.executeBatch();
			
			ps.close();
			conn.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException se2) {
				se2.printStackTrace();
				ps = null;
			}
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException se) {
				se.printStackTrace();
				conn = null;
			}
		}
    }
}

#7

OH, Thank You all… i try …


#8

Another option is to use our StreamImporter utility which is located in the bin folder of the OmniSci installation. I’ve provided a simple example here to illustrate how powerful it can be when combined with redirects from the commandline: https://www.omnisci.com/docs/latest/6_loading_data.html#sqlimporter

sam@mithryl:~$ echo "create table tabletracking (tblname text encoding dict(32));" | /apps/mapd/mapd/bin/mapdql --user mapd --passwd HyperInteractive --port 9091 --db mapd -q
sam@mithryl:~$ echo "\t" | /apps/mapd/mapd/bin/mapdql --user mapd --passwd HyperInteractive --port 9091 --db mapd -q |  /apps/mapd/mapd/bin/StreamImporter --database mapd --table tabletracking --user mapd --passwd HyperInteractive --batch 5 
Field Delimiter: ,
Line Delimiter: \n
Null String: \N
Insert Batch Size: 5
5 Rows Inserted, 0 rows skipped.
10 Rows Inserted, 0 rows skipped.
15 Rows Inserted, 0 rows skipped.
20 Rows Inserted, 0 rows skipped.
25 Rows Inserted, 0 rows skipped.
30 Rows Inserted, 0 rows skipped.
35 Rows Inserted, 0 rows skipped.
40 Rows Inserted, 0 rows skipped.
45 Rows Inserted, 0 rows skipped.
50 Rows Inserted, 0 rows skipped.