Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

not able to query hive table built over parquet file

avatar
Explorer

I want to store data in hdfs as parquet file and then create an external table over it as parquet. 

 

i used sqoop command to pull data from Oracle into hdfs.Column4 is date field in Oracle table so i want to store it as timestamp in hive.When i ran select * from table in hue i got the error below.

 

Please help , looks like data type issue because of column 4 .If i import only first 3 fields (string) from oracle to hdfs  and create hive table using first 3 columns then it is working fine. Issue came once i added column4.

 

Step1 :

 

CREATE external TABLE IF NOT EXISTS `abc`
(
column1 STRING,
column2 STRING,
column3 STRING
column4 TIMESTAMP
)
STORED AS PARQUET
LOCATION '/data/encrypt/abc';


Step 2:

sqoop import --connect *** --username *** --password ****** --query 'select column1,column2,column3,column4 from abc where $CONDITIONS' --split-by column1 --delete-target-dir --target-dir /data/encrypt/abc --compression-codec org.apache.hadoop.io.compress.SnappyCodec --as-parquetfile

 

Hue :

 

select * from abc;

 

Error:

 

  • Bad status for request TFetchResultsReq(fetchType=0, operationHandle=TOperationHandle(hasResultSet=True, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret='\xc5/w\xfd|0NL\x89\xbf\xa4)8\xaf\xa89', guid='\xaf\xca\x1b\xf0\xcd\xe3D4\xa2M\x94\xb8a\xed\xd3\x18')), orientation=4, maxRows=100): TFetchResultsResp(status=TStatus(errorCode=0, errorMessage='java.io.IOException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.UnsupportedOperationException: Cannot inspect org.apache.hadoop.io.LongWritable', sqlState=None, infoMessages=['*org.apache.hive.service.cli.HiveSQLException:java.io.IOException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.UnsupportedOperationException: Cannot inspect org.apache.hadoop.io.LongWritable:14:13', 'org.apache.hive.service.cli.operation.SQLOperation:getNextRowSet:SQLOperation.java:463', 'org.apache.hive.service.cli.operation.OperationManager:getOperationNextRowSet:OperationManager.java:294', 'org.apache.hive.service.cli.session.HiveSessionImpl:fetchResults:HiveSessionImpl.java:769', 'org.apache.hive.service.cli.CLIService:fetchResults:CLIService.java:462', 'org.apache.hive.service.cli.thrift.ThriftCLIService:FetchResults:ThriftCLIService.java:691', 'org.apache.hive.service.cli.thrift.TCLIService$Processor$FetchResults:getResult:TCLIService.java:1553', 'org.apache.hive.service.cli.thrift.TCLIService$Processor$FetchResults:getResult:TCLIService.java:1538', 'org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39', 'org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39', 'org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor:process:HadoopThriftAuthBridge.java:762', 'org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286', 'java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1149', 'java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:624', 'java.lang.Thread:run:Thread.java:748', '*java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.UnsupportedOperationException: Cannot inspect org.apache.hadoop.io.LongWritable:16:2', 'org.apache.hadoop.hive.ql.exec.FetchTask:fetch:FetchTask.java:154', 'org.apache.hadoop.hive.ql.Driver:getResults:Driver.java:2058', 'org.apache.hive.service.cli.operation.SQLOperation:getNextRowSet:SQLOperation.java:458', '*org.apache.hadoop.hive.ql.metadata.HiveException:java.lang.UnsupportedOperationException: Cannot inspect org.apache.hadoop.io.LongWritable:23:7', 'org.apache.hadoop.hive.ql.exec.ListSinkOperator:processOp:ListSinkOperator.java:90', 'org.apache.hadoop.hive.ql.exec.Operator:forward:Operator.java:815', 'org.apache.hadoop.hive.ql.exec.SelectOperator:processOp:SelectOperator.java:84', 'org.apache.hadoop.hive.ql.exec.Operator:forward:Operator.java:815', 'org.apache.hadoop.hive.ql.exec.TableScanOperator:processOp:TableScanOperator.java:98', 'org.apache.hadoop.hive.ql.exec.FetchOperator:pushRow:FetchOperator.java:425', 'org.apache.hadoop.hive.ql.exec.FetchOperator:pushRow:FetchOperator.java:417', 'org.apache.hadoop.hive.ql.exec.FetchTask:fetch:FetchTask.java:140', '*java.lang.UnsupportedOperationException:Cannot inspect org.apache.hadoop.io.LongWritable:28:5', 'org.apache.hadoop.hive.ql.io.parquet.serde.primitive.ParquetStringInspector:getPrimitiveJavaObject:ParquetStringInspector.java:77', 'org.apache.hadoop.hive.ql.io.parquet.serde.primitive.ParquetStringInspector:getPrimitiveJavaObject:ParquetStringInspector.java:28', 'org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils:copyToStandardObject:ObjectInspectorUtils.java:336', 'org.apache.hadoop.hive.serde2.SerDeUtils:toThriftPayload:SerDeUtils.java:167', 'org.apache.hadoop.hive.ql.exec.FetchFormatter$ThriftFormatter:convert:FetchFormatter.java:61', 'org.apache.hadoop.hive.ql.exec.ListSinkOperator:processOp:ListSinkOperator.java:87'], statusCode=3), results=None, hasMoreRows=None).
1 ACCEPTED SOLUTION

avatar
Explorer

Yes, try ORACLE TO_TIMESTAMP() format if needed

View solution in original post

6 REPLIES 6

avatar
Is the date stored in Oracle as a DATETIME field? Hive used the Unix date format, and I don't believe these two are compatible.

You can try loading it as a string and see if that fixes the error; if so, then you'll need to convert it during import if you want a Hive TIMESTAMP field.

avatar
Explorer

date is stored as a DATE datatype in oracle. I tried to create column4 as String in hive external table then also i got same error. But i want to store it as TIMESTAMP.

 

How can i achieve this ? -  you'll need to convert it during import if you want a Hive TIMESTAMP field 

 

like i want to store other datatype number (oracle) as integer in both hdfs and hive?

avatar
Explorer

I tried to import as Timestamp but got this error for both Timestamp and java.sql.Timestamp

 

--map-column-java Column4=Timestamp

--map-column-java Column4=java.sql.Timestamp

 

ERROR tool.ImportTool: Import failed: No ResultSet method for Java type Timestamp

 

ERROR tool.ImportTool: Import failed: Cannot convert to AVRO type java.sql.Timestamp

avatar
Explorer
Can you try doing cast(col4 as timestamp) in your sqoop query.

Not sure if it would work. But give a try

avatar
Explorer

It worked but i can see NULL values in column4 in hive table. May be format issue ?

avatar
Explorer

Yes, try ORACLE TO_TIMESTAMP() format if needed