Sqoop export to Postgresql with geometry column

[#1]

Using Sqoop export on CDP cluster (hdfs), I'm trying to export data geometry to Postgresql table but I'm facing a Sqoop ERROR orm.ClassWriter: No Java type for SQL type 1111 for column 'geom'.

The 'geom' column definition on PostGis is : geometry (Point,3945).

Here is my Sqoop export query :
```
sqoop export --connect "jdbc:postgresql://$database_server/$database_source_name" \
--username $username --password $password_alias \
--table $database_table_name \
--export-dir "${DIR_OUT_HDFS}" \
--fields-terminated-by '|' --lines-terminated-by "\n" \
-- --schema $database_table_name 1>>${LOG_FILE} 2>>${LOG_FILE}
```

Here is Sqoop output error :
```
ERROR orm.ClassWriter: No Java type for SQL type 1111 for column geom
ERROR orm.ClassWriter: No Java type for SQL type 1111 for column geom
ERROR orm.ClassWriter: No Java type for SQL type 1111 for column geom
ERROR orm.ClassWriter: No Java type for SQL type 1111 for column geom
ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
at org.apache.sqoop.orm.ClassWriter.parseColumn(ClassWriter.java:1417)
at org.apache.sqoop.orm.ClassWriter.myGenerateParser(ClassWriter.java:1555)
at org.apache.sqoop.orm.ClassWriter.generateParser(ClassWriter.java:1518)
at org.apache.sqoop.orm.ClassWriter.generateClassForColumns(ClassWriter.java:1979)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1764)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:77)
at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:113)
at org.apache.sqoop.Sqoop.run(Sqoop.java:151)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:81)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:187)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:241)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:250)
at org.apache.sqoop.Sqoop.main(Sqoop.java:259)
```

[#2]

Check column type for table geom.
During runtime sqoop was not able to identify data in column geom causing this issue

Workaround :
add --map-column-java geom string 

https://docs.cloudera.com/sqoop/1.4.7.7.1.6.0/user-guide/index.html#_controlling_type_mapping

[#3]

Thanks for hint. I customed it has following to make it running but facing new issue...

Here with right syntaxe : --map-column-java geom=String  (need uppercase for first datatype)

Looks better but facing this output error :

Error: column « geom » has type geometry but expression type is character varying
Hint: You should rewrite expression or make type transformation.

Tried few queries but not working...

1) --map-column-java geom=Geometry \

Had output : 

ERROR orm.ClassWriter: No ResultSet method for Java type Geometry
ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.IllegalArgumentException: No ResultSet method for Java type Geometry
java.lang.IllegalArgumentException: No ResultSet method for Java type Geometry

2) --map-column-java geom=String,value=Geometry \

ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.IllegalArgumentException: No column by the name valuefound while importing data; expecting one of [Y_REEL, INCERTITUDE_Z, DATE_POSE, geom, COTE_FIL_EAU, INCERTITUDE_XY, ANTI_ODEUR, OBSERVATION_RES, USER_VALID, DATE_GEOLOC, PRESTATAIRE, WM_VERSION, USER_CRE, USER_MAJ, LTT_FEATURE_ID, id, WM_USERNAME, id_metier, WM_ROWID, MARQUE_MATERIEL, X_REEL, Z_REEL, SOURCE_MAJ, FORMAT_ORIGINE_XY, WM_CREATEWORKSPACEID, LTT_STATUS, DATE_CRE, DATE_MAJ, COMMUNE, FORMAT_ORIG_ZRAD, NUM_SERIE_MATERIEL, WM_CREATETIME, TYPE_AVALOIR, ENTREPRISE_RELEVE, WM_RETIRETIME, COTE_TN, QUALITE_GEOLOC_XY, WM_WORKSPACE, FORMAT_ORIG_ZTN, RESEAU, MAITRE_OUVRAGE, QUALITE_GEOLOC_Z, QUAL_ANNEE, niveau, WM_OPTYPE, LTT_CURRENT_REVSET_ID, ID1, COTE_RADIER, WM_RETIREWORKSPACEID, EXPLOITANT]
java.lang.IllegalArgumentException: No column by the name valuefound while importing data; expecting one of [Y_REEL, INCERTITUDE_Z, DATE_POSE, geom, COTE_FIL_EAU, INCERTITUDE_XY, ANTI_ODEUR, OBSERVATION_RES, USER_VALID, DATE_GEOLOC, PRESTATAIRE, WM_VERSION, USER_CRE, USER_MAJ, LTT_FEATURE_ID, id, WM_USERNAME, id_metier, WM_ROWID, MARQUE_MATERIEL, X_REEL, Z_REEL, SOURCE_MAJ, FORMAT_ORIGINE_XY, WM_CREATEWORKSPACEID, LTT_STATUS, DATE_CRE, DATE_MAJ, COMMUNE, FORMAT_ORIG_ZRAD, NUM_SERIE_MATERIEL, WM_CREATETIME, TYPE_AVALOIR, ENTREPRISE_RELEVE, WM_RETIRETIME, COTE_TN, QUALITE_GEOLOC_XY, WM_WORKSPACE, FORMAT_ORIG_ZTN, RESEAU, MAITRE_OUVRAGE, QUALITE_GEOLOC_Z, QUAL_ANNEE, niveau, WM_OPTYPE, LTT_CURRENT_REVSET_ID, ID1, COTE_RADIER, WM_RETIREWORKSPACEID, EXPLOITANT]

[#4]

Hi,

Do I need specific JDBC driver in Sqoop librairies to manage postgresl/postgis database ?

tkx

[#5]

This issue doesn't seems to be driver dependent , but moreover conversion of Geometry data type to Java compatible data type (string could be the best suited for mixed data types)

For now you can cast Geometry column to VarChar in a select query and pass the same query in sqoop import command using "--query <sql_query>" tag 

[#6]

@Daco, Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.  

[#7]

Hi,

Unfortunatly --query is not an Sqoop export argument...

I'm still facing this issue

[#8]

I believe that issue you are facing here is due to column type Geometry

When data is present in HDFS , thought is mapped to a Hive Table, particular column must be in STRING  which is incompatible with Geometry type column in destination postgres, 
Only option we have is to alter the column type to VARCHAR at postgres