SQLImporter with PostgreSQL

Dear all,

I am using Onisci Enterprise Edition Trial Version.
I have tried to import PostgreSQL data to Omnisci with JDBC, but I received error message as below.

Any advice would be very much appreciated.

Thank you.

Best regards,

Firman Hadi.

java -cp /opt/omnisci/bin/mapd-1.0-SNAPSHOT-jar-with-dependencies.jar:/tmp/postgresql-42.2.10.jar com.mapd.utility.SQLImporter -s localhost -u admin -p HyperInteractive -db omnisci --port 6279 -t desa_dukcapil -su postgres -sp postgres -c "jdbc:postgresql://127.0.0.1/kominfo" -ss "select * from public.province" 
2020-03-01 15:15:41 INFO  SQLImporter:executeQuery:345 - Connecting to database url :jdbc:postgresql://127.0.0.1/kominfo
2020-03-01 15:15:41 ERROR SQLImporter:createMapDConnection:619 - Connection failed - org.apache.thrift.TApplicationException: Invalid method name: 'connect'

I have added the parameter --http and changed the port to 6278.
Another error message appeared as below.

Regards,

Firman.

2020-03-01 16:17:24 ERROR SQLImporter:createMapDTable:570 - Error processing the metadata - java.sql.SQLException: multiple column definitions [MULTIPOLYGON:MULTIPOLYGON] found for column_name [geom]

Hi @firmanhadi -

I have encountered this behavior before, and I’m working with our Java engineers to fix it in the codebase. In the meantime, the error occurs because multiple tables in your database have a geospatial column name geom, and when we do a metadata lookup to get the table type from Postgres/Postgis, we get multiple column definitions (although in your case, both geom columns in their respective tables are MULTIPOLYGON).

The simplest way for you to proceed right now is either to do an ALTER TABLE command in Postgres to change the column name to anything other than geom or create a view from the public.province data where you rename the column.

As an aside, you can probably use port 6274 and remove the http parameter from your call, and it will likely go faster. Port 6274 is our binary connection, and 6273/6278 are our http connections.

Best,
Randy

Dear Randy,

Thank you very much for the answer.

I have tried with another table that has different name for geometry column (wkb_geometry), but still failed.

Thank you.

Best regards,

Firman Hadi.

firmanhadi@pop-os:~$ java -cp /opt/omnisci/bin/mapd-1.0-SNAPSHOT-jar-with-dependencies.jar:/home/firmanhadi/Downloads/postgresql-42.2.5.jar com.mapd.utility.SQLImporter -u admin -p HyperInteractive --binary -db omnisci --port 6274 -t desa_valid -su postgres -sp postgres -c “jdbc:postgresql://localhost:5432/kominfo” -ss “select * from public.desa_dukcapil_160518_valid”
2020-03-02 07:09:50 INFO SQLImporter:executeQuery:345 - Connecting to database url :jdbc:postgresql://localhost:5432/kominfo
2020-03-02 07:09:52 ERROR SQLImporter:createMapDTable:570 - Error processing the metadata - java.sql.SQLException: multiple column definitions [MULTIPOLYGON:MULTIPOLYGON] found for column_name [wkb_geometry]

Unfortunately, the bug is not the name geom that’s a problem, but that two or more columns across any combination of tables have the same name. For example:

table A:
col1 int,
col2 int,
wkb_geometry geometry

table B:
col1 float,
col2 int,
col3 string,
wkb_geometry geometry

Any combination of tables and geospatial column names will cause the same problem. So if you take a table like table B and do the following with a unique name, it will work.

create view vw_tableB as
select
col1,
col2,
col3,
wkb_geometry as wkb_geometry_firman;

Then you can make your -ss parameter select * from vw_tableB and it will import into OmniSci. The destination table name in OmniSci will also have to have the “new” column name wkb_geometry_firman, but once the data are loaded, you can do ALTER TABLE tblname RENAME COLUMN wkb_geometry_firman TO wkb_geometry

Obviously, this workaround is pretty inelegant, so we’ll try and get a patch out for this as soon as we can.

Dear Randy,

Thank you very much for the detail instruction.
I will try the solution.

Best regards,

Firman.

Dear Randy,

I just want to confirm that it works!
Thank you.

Best regards,

Firman.

2 Likes

Thanks @firmanhadi,

feedbacks are always appreciated :wink: