neo4j-etl icon indicating copy to clipboard operation
neo4j-etl copied to clipboard

Import from PostgreSql fails on boolean data type

Open davidlrosenblum opened this issue 7 years ago • 12 comments

If one of the tables has a boolean data type, the Import using Cypher Shell fails when the data is written to the csv. The error is:

  • Command failed due to error (RuntimeException: org.postgresql.util.PSQLException: Bad value for type byte : t). Rerun with --debug flag for detailed diagnostic information.

The complete error from the log is attached here: etl_err_psql_boolean.txt

davidlrosenblum avatar Dec 15 '17 18:12 davidlrosenblum

Can you share the generated file?

Alberto it would be good if we covered all the data types and input modes in IT. Perhaps we can have one generic IT that is parameterized with both databases and input modes? And only checks on the resulting graph?

jexp avatar Dec 16 '17 12:12 jexp

I have attached all the files I could think of etl err1.zip

davidlrosenblum avatar Dec 17 '17 02:12 davidlrosenblum

I have the same problem with latest version- neo4j-etl-cli-1.2.0-RC1.

jaroslav-jaros avatar May 16 '18 15:05 jaroslav-jaros

Do you have this problem with the latest release?

albertodelazzari avatar Jun 21 '18 09:06 albertodelazzari

@davidlrosenblum ping?

jexp avatar Jul 04 '18 19:07 jexp

On Holiday. Will check on Monday.

David

On Jul 4, 2018, at 3:03 PM, Michael Hunger [email protected] wrote:

@davidlrosenblum https://github.com/davidlrosenblum ping?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/neo4j-contrib/neo4j-etl/issues/6#issuecomment-402544170, or mute the thread https://github.com/notifications/unsubscribe-auth/AUNwGDA3VnIqGM90yeKXfvM3z9kF8O0cks5uDRGMgaJpZM4RD3iI .

davidlrosenblum avatar Jul 05 '18 18:07 davidlrosenblum

I can confirm this is fixed in ETL 1.2.1

davidlrosenblum avatar Jul 09 '18 13:07 davidlrosenblum

I got similar problem in etl 1.2.1:

./bin/neo4j-etl export --rdbms:url jdbc:postgresql://myurl:5432/public --rdbms:schema example --rdbms:user my_user --rdbms:password pass --destination /var/lib/neo4j/data/databases/graph.db/ --import-tool /usr/share/neo4j/bin --csv-directory /var/lib/neo4j/import --force

and getting: Peak memory usage: 0.00 B', Stderr: 'WARNING: neo4j-import is deprecated and support for it will be removed in a future version of Neo4j; please use neo4j-admin import instead. Error in input data Caused by:ERROR in input data source: BufferedCharSeeker[source:/var/lib/neo4j/import/csv-003/example/NODE_xxxxxxxxxx.xxxx_1703350c-43dc-48ad-a059-4a5d36d4e5f8.csv, position:225, line:0] in field: success:byte:10 for header: [:ID(xxxxxxxxxx.xxxx), installedRank:long, installedBy:string, description:string, script:string, installedOn:string, executionTime:long, version:string, checksum:long, success:byte, type:string, :LABEL] raw field value: true original error: Not an integer: "true"

success column is boolean in postgresql

grubytolman avatar Sep 11 '18 17:09 grubytolman

@grubytolman can you share the table definition for that column?

Which Postgres version are you using?

jexp avatar Sep 12 '18 08:09 jexp

PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu 64-bit

CREATE TABLE xxx.xxx ( installed_rank integer NOT NULL, version character varying(50), description character varying(200) NOT NULL, type character varying(20) NOT NULL, script character varying(1000) NOT NULL, checksum integer, installed_by character varying(100) NOT NULL, installed_on timestamp without time zone NOT NULL DEFAULT "now"(), execution_time integer NOT NULL, success boolean NOT NULL, CONSTRAINT xxx_pk PRIMARY KEY ("installed_rank") )

CREATE INDEX xxx_xxx_s_idx ON xxx.xxx USING btree ("success");

values in column success are true, false in postgresql

Neo4j Server version: 3.4.7 (community)

I've also made simple test base/table on different pc: Ubuntu 16.04 PostgreSQL 9.5.14 on x86_64-pc-linux-gnu neo4j community 3.4.6 neo4j-etl 1.2.1

CREATE TABLE testbool ( id integer NOT NULL, "boolColumn" boolean DEFAULT true, CONSTRAINT prim_key PRIMARY KEY (id) );

insert into testbool values (1,true); insert into testBool values (2,false); insert into testBool values (3,true); insert into testBool values (4,false);

etl exec: ./bin/neo4j-etl export --rdbms:url jdbc:postgresql://localhost:5432/northwind --rdbms:schema testbool --rdbms:user xxx --rdbms:password xxx --destination /var/lib/neo4j/data/databases/graph.db/ --import-tool /usr/share/neo4j/bin --csv-directory /var/lib/neo4j/import --force

and getting: Data statistics is not available. Peak memory usage: 0.00 B', Stderr: 'WARNING: neo4j-import is deprecated and support for it will be removed in a future version of Neo4j; please use neo4j-admin import instead. Error in input data Caused by:ERROR in input data source: BufferedCharSeeker[source:/var/lib/neo4j/import/csv-009/neo4import/NODE_neo4import.testbool_21686a3f-4981-4bef-847e-1ac14a5e6dbd.csv, position:15, line:0] in field: boolColumn:byte:3 for header: [:ID(neo4import.testbool), id:long, boolColumn:byte, :LABEL] raw field value: true original error: Not an integer: "true"

grubytolman avatar Sep 12 '18 08:09 grubytolman

hi , did you solve it? I got similar problem.

ltw18 avatar Dec 08 '19 14:12 ltw18

Kind of, I changed "true" to 1 and "false" to 0 in csv files with external script and then imported without problem. For bigger tables I wrote custom exporter and importer.

grubytolman avatar Dec 08 '19 16:12 grubytolman