SQLImporter with PostgreSQL

Comments

11 comments

  • Avatar
    Firman Hadi

    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]

    0
    Comment actions Permalink
  • Avatar
    Randy Zwitch

    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

    0
    Comment actions Permalink
  • Avatar
    Firman Hadi

    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]

    0
    Comment actions Permalink
  • Avatar
    Randy Zwitch

    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.

    0
    Comment actions Permalink
  • Avatar
    Firman Hadi

    Dear Randy,

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

    Best regards,

    Firman.

    0
    Comment actions Permalink
  • Avatar
    Firman Hadi

    Dear Randy,

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

    Best regards,

    Firman.

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Thanks @firmanhadi,

    feedbacks are always appreciated ;)

    0
    Comment actions Permalink
  • Avatar
    mjj203

    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.

    0
    Comment actions Permalink
  • Avatar
    Jack Frampton

    (post withdrawn by author, will be automatically deleted in 24 hours unless flagged)

    0
    Comment actions Permalink
  • Avatar
    Jack Frampton

    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?

    0
    Comment actions Permalink
  • Avatar
    mjj203

    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.

    0
    Comment actions Permalink

Please sign in to leave a comment.