DBMS_CLOUD Package Oracle Data Type to Parquet Mapping
Describes the mapping of Oracle data types to Parquet data types.
Oracle Type | Parquet Type |
---|---|
BINARY_DOUBLE | DBL |
BINARY_FLOAT | FLT |
DATE | DATE |
NUMBER(p,s) | DECIMAL(p,s) |
NUMBER(p) | DECIMAL(p) |
TIMESTAMP(3) | TIMESTAMP_MILLIS |
TIMESTAMP(3) | TIMESTAMP_MILLIS_UTC |
TIMESTAMP(6) | TIMESTAMP_MICROS |
TIMESTAMP(6) | TIMESTAMP_MICROS_UTC |
TIMESTAMP(9) | TIMESTAMP_NANOS |
VARCHAR2(4000) | STRING |
NLS Session Parameters
The NLS session parameters NLS_DATE_FORMAT
,
NLS_TIMESTAMP_FORMAT
, NLS_TIMESTAMP_TZ_FORMAT
and NLS_NUMERIC_CHARACTERS
define how the date, timestamp,
timestamp with time zone format, and radix separator for timestamp with decimal
marker should be shown when a table with those column types are queried.
In addition, when you export data using DBMS_CLOUD.EXPORT_DATA
and specify Parquet output,
Autonomous Database reads the values
of these parameters from the NLS_SESSION_PARAMETERS
table. Autonomous Database uses these values to
convert the Oracle data types DATE
or TIMESTAMP
to
Parquet types.
The NLS_SESSION_PARAMETERS
parameters support an
RR
format mask (two character year specification).
The RR
format mask for the year is not supported for
these parameters when you export data to Parquet with DBMS_CLOUD.EXPORT_DATA
. An application error is
raised if you attempt to export to parquet and the
NLS_SESSION_PARAMETERS
are set to use the RR
format mask (the default value for the RR
format depends on the
value of the NLS_TERRITORY
parameter).
When one of the parameters NLS_DATE_FORMAT
,
NLS_TIMESTAMP_FORMAT
or
NLS_TIMESTAMP_TZ_FORMAT
uses the RR
format
mask, you must change the format value to supported value to export data to Parquet
with DBMS_CLOUD.EXPORT_DATA
. For
example:
ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY";
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH:MI:SS.FF TZH:TZM';
NLS_SESSION_PARAMETERS
view:SELECT value FROM NLS_SESSION_PARAMETERS
WHERE parameter IN ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT');
If NLS_DATE_FORMAT
is set, it applies to the columns with
DATE
datatype. If NLS_TIMESTAMP_FORMAT
is set,
it applies to the columns with TIMESTAMP
datattype. If
NLS_TIMESTAMP_TZ_FORMAT
is set, it applies to the columns with
TIMESTAMP WITH TIME ZONE
datatype.
See Date and Time Parameters and NLS Data Dictionary Views for more information.
Parent topic: DBMS_CLOUD Avro, ORC, and Parquet Support