My Oracle Support Banner

Exporting CLOB Data from an Oracle Database Using the External Table oracle_datapump into HDFS on a BDA with Copy to Hadoop Does Not Display CLOBS Correctly in Hive. (Doc ID 2236260.1)

Last updated on MARCH 18, 2017

Applies to:

Oracle Big Data SQL - Version 3.1.0 to 3.1.0 [Release 3.1]
Linux x86-64

Symptoms

Exporting CLOB Data from an Oracle Database Using the External Table oracle_datapump into HDFS on a BDA with Copy to Hadoop Does Not Display CLOBS Correctly in Hive.

For example a test as below illustrates the problem:

1. In an Oracle Database:

create table hebrew_string (mystring varchar2(100), myclob clob)

insert into hebrew_string values ('שלום ולהתראות', 'שלום ולהתראות');
commit;

select * from hebrew_string;

2. Export the table to a datapump file:

CREATE DIRECTORY exportdir AS '/tmp/exportdir';
drop table datapump_tbl;
CREATE TABLE datapump_tbl
ORGANIZATION EXTERNAL (
TYPE oracle_datapump
DEFAULT DIRECTORY exportdir
LOCATION ('datapumpfile1.dmp')
) PARALLEL 1
AS SELECT * FROM hebrew_string;

3. Copy the exported datapump file to HDFS on the BDA:

$ hdfs dfs -put /tmp/exportdir/datapumpfile1.dmp /tmp/exportdir/

4. In the Hive CLI:

CREATE EXTERNAL TABLE datapump_tbl
ROW FORMAT
SERDE 'oracle.hadoop.hive.datapump.DPSerDe'
STORED AS
INPUTFORMAT 'oracle.hadoop.hive.datapump.DPInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '/tmp/exportdir';

select * from datapump_tbl;

Result:

שלום ולהתראות éÜÕÝ ÕÜÔêèÐÕê

This works properly with VARCHAR but not with CLOB.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.