Fetch character columns as raw bytes in the database character set into data frames (Thick mode)

[a-hirota]

1. Describe your new request in detail

Summary

We would like to fetch character columns into data frames (fetch_df_all() / fetch_df_batches()) as raw bytes in the database character set, i.e. without any character set conversion on the server and without decoding on the client, and then decode them in the application.

This needs two changes, which are only useful together:

• (a) Allow requested_schema to request the Arrow binary types (BINARY, FIXED_SIZE_BINARY, LARGE_BINARY) for character columns.
• (b) Allow the client character set used in Thick mode to be configured, instead of always being UTF-8.

Background

My database uses the JA16SJISTILDE character set (Japanese Shift_JIS). The data contains user-defined characters (gaiji) and vendor-specific characters. When the database converts this data to UTF-8 for the client, these characters are lost or mapped ambiguously, so the original data cannot be reproduced. To preserve it exactly, we need the original Shift_JIS bytes and decode them ourselves with our own mapping table.

The current workaround is to wrap every character column in UTL_RAW.CAST_TO_RAW():

select utl_raw.cast_to_raw(col1) as col1, utl_raw.cast_to_raw(col2) as col2, ...
from some_table

This works functionally, but the per-cell SQL function call is very expensive on the server. In our measurements on production-like data (several tables, from ~0.5M rows x ~750 columns to ~55M rows x ~70 columns):

• about 2.5 µs per character cell of server-side overhead,
• fetch became about 24-31 times slower than a plain `
• fetch accounted for 74-82% of the total extract time, dominated by this overhead.

With changes (a) and (b) applied to a locally built python-oracledb (Thick mode, client
characteracter setso that no conversion takes place), the UTL_RAW.CAST_TO_RAW() calls could be removed
entirely.ey arestored in the database and decoded on the client, and the output was byte-for-byte identical to the workaround.

(a) Binary Arrow types for character columns

Currently, requesting a binary type for a character column raises DPY-3038 (database type "DB_TYPE_VARCHAR" cannot be converted to Apache Arrow type ...):

import pyarrow

schema = pyarrow.schema([("NAME", pyarrow.large_binary())])
odf = conn.fetch_df_all(
    "select cast('abc' as varchar2(10)) as name from dual",
    requested_schema=schema,
)

The converted:convert_oracle_data_to_arrow() sends STRING,
LARGE_STBINARY andLARGE_STRING, BINARY, FIXED_SIZE_BINARY and
LARGE_BIconvert_str_to_arrow(), which copies the bytes. Only the requested-type check in OracleMewing change in src/oracledb/impl/base/metadata.pyx is sufficient:

         elif db_type_num in (
             DB_TYPE_NUM_CHAR,
             DB_TYPE_NUM_CLOB,
             DB_TYPE_NUM_LONG_NVARCHAR,
             DB_TYPE_NUM_LONG_VARCHAR,
             DB_TYPE_NUM_VARCHAR,
             DB_TYPE_NUM_NCHAR,
             DB_TYPE_NUM_NCLOB,
             DB_TYPE_NUM_NVARCHAR
         ):
             if arrow_type in (
                 NANOARROW_TYPE_STRING,
                 NANOARROW_TYPE_LARGE_STRING
             ):
                 ok = True
+
+            # character data can also be fetched without being decoded, in
+            # which case the bytes are returned exactly as they were received
+            # from the database; this is the data frame equivalent of calling
+            # Cursor.var() with the parameter bypass_decode set to True
+            elif arrow_type in (
+                NANOARROW_TYPE_BINARY,
+                NANOARROW_TYPE_FIXED_SIZE_BINARY,
+                NANOARROW_TYPE_LARGE_BINARY
+            ):
+                ok = True

This is also the data frame equivalent of Cursor.var(..., bypass_decode=True). That option cannot be used with the data frame API because an output type handler cannot be set on the internally created cursor.

Example tests:

@pytest.mark.parametrize("db_type_name", ["CHAR", "NCHAR", "VARCHAR2", "NVARCHAR2"])
@pytest.mark.parametrize(
    "dtype", [pyarrow.binary(length=9), pyarrow.binary(), pyarrow.large_binary()]
)
def test_string_types_as_binary(db_type_name, dtype, conn):
    value = "test_1633"
    requested_schema = pyarrow.schema([("STRING_COL", dtype)])
    statement = f"select cast('{value}' as {db_type_name}(9)) from dual"
    ora_df = conn.fetch_df_all(statement, requested_schema=requested_schema)
    tab = pyarrow.table(ora_df)
    assert tab.field("STRING_COL").type == dtype
    assert tab["STRING_COL"][0].as_py() == value.encode()


@pytest.mark.parametrize("db_type_name", ["CLOB", "NCLOB"])
@pytest.mark.parametrize("dtype", [pyarrow.binary(), pyarrow.large_binary()])
def test_clob_types_as_binary(db_type_name, dtype, conn):
    value = "test_dataframe_1634"
    requested_schema = pyarrow.schema([("CLOB_COL", dtype)])
    statement = f"select to_{db_type_name.lower()}('{value}') from dual"
    ora_df = conn.fetch_df_all(statement, requested_schema=requested_schema)
    tab = pyarrow.table(ora_df)
    assert tab.field("CLOB_COL").type == dtype
    assert tab["CLOB_COL"][0].as_py() == value.encode()


def test_fetch_df_batches_as_binary(conn):
    values = ["test_1635_a", "test_1635_b"]
    dtype = pyarrow.large_binary()
    requested_schema = pyarrow.schema([("STRING_COL", dtype)])
    statement = """
        select cast(:1 as varchar2(20)) from dual
        union all
        select cast(:2 as varchar2(20)) from dual
    """
    fetched = []
    for ora_df in conn.fetch_df_batches(
        statement, values, size=1, requested_schema=requested_schema
    ):
        tab = pyarrow.table(ora_df)
        assert tab.field("STRING_COL").type == dtype
        fetched.extend(v.as_py() for v in tab["STRING_COL"])
    assert fetched == [v.encode() for v in values]

(b) Configurable client character set in Thick mode

In Thick mode the client character set is fixed to UTF-8 in src/oracledb/impl/thick/utils.pyx (init_oracle_client()):

params.defaultEncoding = "utf-8"

Because of this, the database always converts character data to UTF-8 before sending it, even when the bytes are requested as binary. We made this value configurable in our local build and set it to the database character set, so that no conversion takes place. Combined with (a), this delivered the raw database bytes into the data frame.

Possible ways to expose this (we have no strong preference):

• a new parameter of oracledb.init_oracle_client(), for example encoding="SHIFT_JIS" (defaulting to "utf-8"), or
• a per-column option, so that a character column requested as a binary Arrow type is fetched in the database character set without conversion (for example by setting the character set on the define), while all other columns keep using UTF-8. This would avoid affecting string decoding elsewhere.

We understand that UTF-8 is used deliberately, and that a non-UTF-8 client character set would affect decoding of strings, metadata and error messages. If (b) is not acceptable, we would still appreciate (a), since it would reduce what we need to maintain locally.

Documentation notes (for (a))

• In the "explicit mapping" table of doc/src/user_guide/dataframes.rst, add BINARY / FIXED SIZE BINARY / LARGE_BINARY for the character database types.
• Note that the bytes are those sent by the database, i.e. in the client character set (UTF-8 by default). Only the client-side decode step is bypassed.

2. Give supporting information about tools and operating systems. Give relevant product version numbers

• python-oracledb 26.0.0, Thick mode
• Oracle Database with JA16SJISTILDE database character set
• Python 3.12, Linux x86_64

[cjbj]

Interesting use case. I suspect (b) may not be feasible but I'll let Anthony comment.