How to Change Oracle Data Storage Type

jhiatt09

I'm hoping to use the VARCHAR2 column storage type in Oracle rather than NVARCHAR2, which appears to be the default storage type used by Dataiku for strings in Dataiku-managed datasets stored in Oracle. What's the best way to do this?

pmasiphelps

Hi,

As a one-off workaround, in your output dataset (stored in Oracle), go to Settings -> Advanced -> Table creation mode -> Manually define

You can then manually change the column storage types in this field.

You can find/replace all instances of "NVARCHAR2" with "VARCHAR2" by:

1. Type "CTRL + OPTION + F" in your keyboard. A find/replace box will pop up. Enter "NVARCHAR2" here, then hit the enter key:

   

2. Write "VARCHAR2" in the "with" field, then hit the enter key:

   

3. Then choose replace "All":

   

Now your dataset should have column data types of "VARCHAR2".

Best,

Pat

Clément_Stenac

Hi,

It's not possible to change this. We use NVARCHAR2 since it can accomodate all characters, which is not the case of VARCHAR or VARCHAR2.

We'd be interested in hearing more about your use case for using VARCHAR2.

Devillers

https://community.dataiku.com/discussion/comment/24885#Comment_24885

Dear Clément,

I have the same issue,

In order to reduce the compute time I focus on reducing the volume/weight of my data. One way to do that is to optimize the length of the data stored.

However Dataiku either display wrong information or does not offer optimal configuration options/ flexibility.

The goal to use varchar2 instead of nvarchar2 is to reduce by 50% the weight of the data stored (nvarchar2= varchar2*2). However, even if I force varchar2 in my SQL query I still get nvarchar2 on my Dataiku schema which is useless and a total waste. The weird thing is that when I perform query to check the type of storage in my Oracle DB I can see Varchar2 (as expected) but Dataiku display Nvarchar2.

Who is right ? And why Dataiku behave like that ?

Same when I use Number(1), it displays integer. However integer means 22 bytes which far more than the weight of Number (1).

Also, when I use timestamp format in my query I get Nvarchar2 (2000) in my Dataiku which is not ideal.

To sum up, my query - > Dataiku

Varchar2 → Nvarchar2

Number(1) → integer

Timestamp → Nvarchar2(2000)

The workaround offered works, however being forced to set the schema manually is a total waste of time and flexibility.

Im on the 13.2 version.

Do you have any real fixes to offer ? Could you explain why a such behaviour ? Is it due to Oracle ?

Thanks a lot,

Louis