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, bothgeom
columns in their respective tables areMULTIPOLYGON
).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 thangeom
or create a view from thepublic.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
parameterselect * from vw_tableB
and it will import into OmniSci. The destination table name in OmniSci will also have to have the "new" column namewkb_geometry_firman
, but once the data are loaded, you can doALTER 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.
-
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?
-
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.
Please sign in to leave a comment.
Comments
11 comments