Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

sqoop import SDO_GEOMETRY type failing

Highlighted

sqoop import SDO_GEOMETRY type failing

New Contributor

I am trying to import a SDO_GEOMETRY data type from oracle DB to hadoop but its populating null if I

import with option --map-column-java SHAPE=String. is there any other data type I can use for it? or is there a better way to import into hadoop/hive?

2 REPLIES 2
Highlighted

Re: sqoop import SDO_GEOMETRY type failing

New Contributor

I used spark for this job and basically I tried to create temporary columns and then union ther values at the end of the job as one column.

Here is how I basically created the temporary columns:

select *
from (with source_tbl as (
select a.*, dbms_lob.getlength(SDO_UTIL.TO_WKTGEOMETRY(SDO_CS.TRANSFORM(a.shape, 4326))) shape_len
from shape_tbl a)
select source_tbl.*,
TO_CHAR(SUBSTR(SDO_UTIL.TO_WKTGEOMETRY(SDO_CS.TRANSFORM(shape, 4326)), 0, 4000)) shape_part1,
case
when shape_len > 4000 then TO_CHAR(
SUBSTR(SDO_UTIL.TO_WKTGEOMETRY(SDO_CS.TRANSFORM(shape, 4326)), 4001, 4000)) end shape_part2,
case
when shape_len > 8000 then TO_CHAR(
SUBSTR(SDO_UTIL.TO_WKTGEOMETRY(SDO_CS.TRANSFORM(shape, 4326)), 8001, 4000)) end shape_part3,
case
when shape_len > 12000 then TO_CHAR(
SUBSTR(SDO_UTIL.TO_WKTGEOMETRY(SDO_CS.TRANSFORM(shape, 4326)), 12001, 4000)) end shape_part4
-- other cases keep increasing the length condition by 4000
from source_tbl);

I see this more as a workaround since usually these SDO_GEOMETRY objects are really big and trying to cover them with strings of length up to 4000 can lead to hundreds of columns.

 

Highlighted

Re: sqoop import SDO_GEOMETRY type failing

New Contributor

Actually you can do it better, and import it as a clob, and in spark works perfect:

SELECT SDO_UTIL.TO_WKTGEOMETRY(SDO_CS.TRANSFORM(shape, 4326)) AS SHAPE, FROM oracle_tbl

 

Don't have an account?
Coming from Hortonworks? Activate your account here