INSERT IGNORE throws primary key violation using h2 in mysql mode

user_11546284 0
Viewed 861

I am scraping Scopus data into an h2 file database. There are over 46,000,000 records in the data, and each is treated as distinct which means that hundreds of GB of data is repeated (hence the relational db). In order to reduce the insert time of all this data, I initially create a set of temporary tables with no constraints and then copy the data into the real tables later using SELECT DISTINCT and GROUP BY to enforce uniqueness.

The one exception to this is the documents table and the referenced documents table. Due to the format of the data, I can guarantee that each record represents a unique document, so I can just INSERT INTO the documents table, and later concat only the rows from the referenced documents table which have IDs not already in the documents table.

Here's the relevant code:

CREATE TABLE document (docid varchar NOT NULL, title varchar, abstract varchar, docType varchar NULL, ref boolean);

CREATE TABLE refdoc (refid varchar NOT NULL, title varchar);

INSERT INTO document (docid, title, abstract, docType, ref)
VALUES ('2-s2.0-0000098715', 'title', 'abstract', 'ab', 'false');

INSERT INTO refdoc (refid, title)
VALUES ('2-s2.0-0000098715', 'title'),
VALUES ('2-s2.0-33947184743', 'title');

ALTER TABLE document
ADD PRIMARY KEY (docid);

ALTER TABLE document
ADD FOREIGN KEY (docType) REFERENCES doctype(abbrev);

INSERT IGNORE INTO document (docid, title, ref)
SELECT refid, title, 'true' FROM refdoc;
  • Create the documents table
  • Create the referenced documents table
  • Insert a record into the documents table
  • Insert two records into the refdoc table, including a duplicate
  • Alter the documents table with a primary key
  • Alter the documents table with a foreign key
  • Insert the rows from refdoc which do not conflict with document

The INSERT IGNORE query throws: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "CONSTRAINT_INDEX_6 ON PUBLIC.DOCUMENT(DOCID)

I also tried using WHERE NOT EXISTS:

INSERT INTO document (docid, title, ref)
SELECT refid, title, 'true'
FROM refdoc
WHERE NOT EXISTS (
SELECT refid FROM refdoc
INNER JOIN document
ON document.docid = refdoc.refid );

But it would seem that attempting to join tables that aren't indexed is effectively impossible - nothing I have attempted involving joins has worked.

As a last resort I can use a FileHashMap and just dump the contents of the refdoc table and then construct a mega-huge PreparedStatement like:

INSERT INTO document (docid, title, ref)
SELECT ?, ?, 'true'
WHERE NOT EXISTS (
SELECT docid FROM document
WHERE docid = ? );

But I'd obviously rather not do that since it will take forever.

h2 mysql database java
1 Answers

Finally found a solution that doesn't involve constructing a batch statement of 100,000,000 records. The issue was that I needed to both enforce that the refdocs I was inserting into document were not already in the document table, and also that I inserted only unique rows from the refdoc table. All of my solutions prior to this either failed to eschew conflicts, failed to enforce uniqueness, or involved joins on tables which had no indices.

Here's the solution SQL:

CREATE TABLE document (docid varchar NOT NULL, title varchar, abstract varchar, docType varchar NULL);

CREATE TABLE refdoc (refid varchar NOT NULL, title varchar);

INSERT INTO document (docid, title, abstract, docType)
VALUES ('2-s2.0-0000098715', 'title', 'abstract', 'ab');

INSERT INTO refdoc (refid, title)
VALUES ('2-s2.0-0000098715', 'title'),
VALUES ('2-s2.0-33947184743', 'title');

INSERT IGNORE INTO document (docid, title)
SELECT refid, MAX(title)
FROM refdoc
WHERE refid NOT IN (
SELECT docid FROM document )
GROUP BY refid;

ALTER TABLE document
ADD PRIMARY KEY (docid);

ALTER TABLE document
ADD FOREIGN KEY (docType) REFERENCES doctype(abbrev);

The logic is now:

  • Create the documents table
  • Create the referenced documents table
  • Insert a record into the documents table
  • Insert two records into the refdoc table, including a duplicate
  • Insert the rows from refdoc which do not conflict with document and are unique
  • Alter the documents table with a primary key
  • Alter the documents table with a foreign key

This has the added bonus of not indexing the documents table until after the inserts are completed.

It's still not entirely clear to me why I was getting a primary key constraint violation on a table with no primary key, but that sounds like something to submit to the h2 github as a bug report.

user_115462840
Related