New post
Question
· Jan 4, 2019

How to extract data from Hive into IRIS?

#InterSystems IRIS

Hi guys,

I have a customer trying to extract data from Hive. I've tried to use Data migration to migrate a table into IRIS with JDBC driver. But the migration task always fails as below

Then I tried to create a link table, the task fails too with the following error:

I've tried several ID combinations such as rowkey+userid, userid + datatime, the results are all errors caused by stream property. 

The source table are composed of only Hive string fields.I wonder how may I migrate Hive tables into IRIS. Thanks.

Discussion (4)1
Log in or sign up to continue
Robert Cemper · Jan 4, 2019

HIVE docs on  String Types shows me:

and

Varchar

Varchar types are created with a length specifier (between 1 and 65535), which defines the maximum number of characters allowed in the character string. If a string value being converted/assigned to a varchar value exceeds the length specifier, the string is silently truncated. Character length is determined by the number of code points contained by the character string.

Related to your  ERROR that tells us that STRING has no size limit => it is a STREAM in our terms.
 

So if you don't convert a STRING to VARCHAR   [ preferable VARCHAR(255) ] you won't be able to use an alphanumeric ID
You may, of course,  add some artificial numbering of type BIGINT to be used as ID.

In any case, just with data type STRING I'd call this a rather a text file than an SQL usable table.

Without touching the original source you may need to write your own loader:

  • reading the HIVE "table" sequentially row by row
  • insert it into a manually designed table/class with automatic id
0 0
Nicky Zhu Jan 10, 2019 to Robert Cemper

Hi Robert，

Thank you very much for your help.

So far we simply used exported csv files to load legacy data. 

During the tests, we found:

1. Under Hive JDBC drivers:

a. By changing rowkey to Varchar(255) is not working. Data migration and link table tasks all failed.

b. Using sql inbound adapter to read data is not working either(select * from sometable), the query failed due to the missing of some toCLOB(0,2) method in the jdbc driver

2. then we tried hive odbc driver

a.  link table task succeed

b. we can use sql to select * from linked table which can also be used with sql inbound adapter. But when we used where clause (select * from sometable where rowkey=' a long string key'), the query failed again due to the follwoing error:

Any idea?

0 0