sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sowjanya Kakarala <sowja...@agrible.com>
Subject Re: Migrating data from Postgres to Hive using Sqoop
Date Thu, 22 Mar 2018 14:34:35 GMT
Hey Fero,

Thank you so much for the response. 
I used array_to_string from Postgres, which imported the data in text format in Hive.
However, couldn’t query that column as an array to get a range based data. I had to write
a UDF.

Thanks
Sowjanya

> On Mar 22, 2018, at 9:22 AM, Fero Szabo <fero@cloudera.com> wrote:
> 
> Hi Sowjanya,
> 
> I'm only guessing here, but I don't think that this type (Double Precision array) is
supported by Sqoop at all. So, a workaround would be to convert the data to something else
in the database (depending on your use case), and then import it with sqoop. Text types are
easily imported, though I guess Double too, could work.
> 
> Anyway, the error is thrown by the ClassWriter class which probably means that this issue
happens before hive is even touched, at the phase of class generation. You could try using
the --map-column-java to get past this, then possibly the --map-column-hive as well... A long
shot, but worth a try.
> 
> (Though, then again, you might just end up at a different error message, because this
is an array datatype.)
> 
> Kind Regards,
> Fero
> 
> 
> 
> On Tue, Mar 20, 2018 at 10:56 PM, Sowjanya Kakarala <sowjanya@agrible.com <mailto:sowjanya@agrible.com>>
wrote:
> Hi Guys,
> 
> I am trying to migrate one of the tables from Postgres database to Hive.
> 
> Schema of Postgres:
> db=> \d table
>                 Table "public.table"
>  Column  |        Type        | Collation | Nullable | Default 
> ---------+--------------------+-----------+----------+---------
>  cell_id | integer            |           |          | 
>  yr2010  | double precision[] |           |          | 
> 
> Data in yr2010 has 365 days data in it like:
> select yr2010[1:365] from table where cell_id=502070;
> 
> {0,0,0,0,0.07,0.05,0,0,0.04,0,0,0.02,0.09,0.06,0,0,0,0,0,0,0,0,0,0,0.06,0.01,0.4,0.03,0.01,0,0,0,0.01,0,0,0,0,0,0,0.09,1.83,1.76,0,0,0,0,0,0.02,0.02,0,0.01,0.08,0,0,0,0,0.89,0,0,0,0,0,0,0,0,0,0.47,0.07,0.43,0,0,0,0,0,0,0,0.45,0,0,0,0,0,0.08,0,0,0,0.58,0,0,0.4,0,0.78,0,0,1.69,0.09,0,0,0.46,0,0,0.38,0.6,0,0,0,0,0.18,0.21,0.1,0.14,0,0,0,0,0,0.78,0.11,0.57,0.75,0.14,0,0,0,0,0.26,0.77,0.04,0,0,0,0.1,2.05,0,1.26,0,0,0,0,0,0,0,0,0,0.52,0.01,0.65,0.03,0.56,0,0,0,0.94,0.59,0,0,0,0.01,0.08,0.58,0.48,1.37,0,0.26,0,0,0.31,0,0.47,0.72,0,1.09,0.03,0,0,0.02,0,0,0,0,0,0,0,0,1.33,0,0,0.19,0.05,0,0,0.74,0,0,0.14,0.11,0.01,0,0,0.13,0,0,0.02,0,0.76,0,0,0,0.51,0,0,0,0.08,0,0,0.83,0,0,0.07,0,0,0,0.19,0,0,0,0,0.21,0,0,0.69,0,0.14,0,0,0,0,0,0,1.29,0,0,0,0,0,0,0,0,0,0,0.04,0,0,0,0,0,0,0,0,0,0,0,0.01,0,0.02,0.02,0.4,0.04,0.54,0.05,0,0,0,0.45,0,0,0,0,0.48,0,2.21,0.23,0,0,0,0.2,0.23,0.01,0.05,0,0,0,0,0,1.38,0.09,0.01,0,0,0.53,0,0.27,0.67,0,0,0,0.02,0,0,0,0,0,0,0,0,0,0,0,0.66,0.84,0.44,0,0.1,0,0,0,0,0.26,0.08,0,0,0,0.05,0,0,0.97,0,0,0,0.02,0,0.96,0.07,0,0,0.84,0.02,0,0,0,0,0,0.04,0.01,0.02,0.09,0.12,0.28,0.25,0.08,0.16,0,0,0.09,0}
> 
> 
> My Sqoop query is :
> 
> sqoop import --connect 'jdbc:postgresql://path-dev:5432/db? ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory'
--table 'table' --columns 'cell_id,yr2010' --username 'uname' -P --hcatalog-database 'db_in_hive'
--hcatalog-table 'table_in_hive' --map-column-hive yr2010=double --create-hcatalog-table -m
1
> 
> I tried with
>  --map-column-hive yr2010=double
>  --map-column-hive yr2010=array<double>
> --map-column-hive yr2010=double[]
> 
> Getting the same error for all queries as follows:
> 
> 18/03/19 13:12:13 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "table"
AS t LIMIT 1
> 18/03/19 13:12:13 ERROR orm.ClassWriter: Cannot resolve SQL type 2003
> 18/03/19 13:12:13 ERROR orm.ClassWriter: No Java type for SQL type 2003 for column yr2010
> 18/03/19 13:12:13 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
> java.lang.NullPointerException
> 	at org.apache.sqoop.orm.ClassWriter.parseNullVal(ClassWriter.java:1385)
> 
> 
> 
> Can any one help with the mapping which works better to Sqoop in?
> Appreciate your help.
> 
> Thanks
> Sowjanya
> 
> 
> 
> 
> -- 
> Ferenc Szabo
> Software Engineer
> 


Mime
View raw message