SQLImporter with PostgreSQL

Programming & Data Ecosystem Tools Java/JDBC
#1

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'
#2

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]

#3

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

#4

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]

#5

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.

#6

Dear Randy,

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

Best regards,

Firman.

#7

Dear Randy,

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

Best regards,

Firman.

2 Likes
#8

Thanks @firmanhadi,

feedbacks are always appreciated :wink:

#9

Any update to this bug fix? Its a major data engineering PIA when we have a couple hundred postgres tables to ingest all using the same geometry column name.

#11

Hi,

We have a fix in the pipe line for the case where columns in multiple tables have the same name and the same type.

The program will still raise an exception in the situation where columns in different tables have the same name, but different types - for example table_one.columnX is a point while table_two.columnX is a polygon. Will this still be a problem for your data load?

#12

This would still cause an issue since we are using postgres with the postgis extension and all tables use the same geometry column name of geometry regardless of point, linestring, polygon, or multipolygon vector type. This issue only occurs when using the JDBC SqlImporter. I can extract the postgres tables to geojson or csv with a wkt column, then use omnisql to copy that in to omnisciDB. Each table I create in omnisciDB with this method has the same geometry field name and different types. This adds a lot of complexity and ETL runtime overhead to migrate or sync a postgres database with omnisciDB if we have to first extract to a geojson or wkt before ingesting to omnisciDB, and would be greatly simplified if the JDBC SqlImporter could do the same thing as omnisql copy.