DBMS_CLOUD Package Avro, ORC, and Parquet to Oracle Column Name Mapping
Describes rules for how Avro, ORC, and Parquet column names are converted to Oracle column names.
The following are supported for Avro, ORC, and Parquet column names, but may require use of double quotes for Oracle SQL references in external tables. Thus, for ease of use and to avoid having to use double quotes when referencing column names, if possible do not use the following in Avro, ORC, and Parquet column names:
-
Embedded blanks
-
Leading numbers
-
Leading underscores
-
Oracle SQL reserved words
The following table shows various types of Avro, ORC, and Parquet column names, and rules for using the column names in Oracle column names in external tables.
Avro, ORC, or Parquet Name | CREATE TABLE Name | Oracle CATALOG | Valid SQL | Notes |
---|---|---|---|---|
part, Part, or PART | part, Part, PART | PART |
|
Oracle implicitly uppercases unquoted column names |
Ord No | "Ord No" | Ord No | select "Ord
No" |
Double quotes are required when there are embedded blanks, which also preserves the character case |
__index_key__ | "__index_key__" | __index_key__ | select
"__index_key__" |
Double quotes are required when there is a leading underscore, which also preserves the character case |
6Way | "6Way" | 6Way | select
"6Way" |
Double quotes are required when there is a leading numeric digit, which also preserves the character case |
create, Create, or CREATE, and so on. (any case variation) partition, Partition, PARTITION, and so on (for an Oracle Reserved word) | "CREATE" "PARTITION" | CREATE PARTITION |
|
Double quotes are required around Oracle SQL Reserved words. These are forced to uppercase, but must always be double-quoted when used anywhere in SQL |
rowid, Rowid, ROWid, and so on (for ROWID see notes) | rowid |
|
For ROWID, any mixed or lower-case variation of ROWID preserves the case and must always be double-quoted and use the original case variations. Due to the inherent conflict with Oracle ROWID for the table, if you specify upper-case ROWID, it is automatically stored as lower-case "rowid" and must always be double-quoted when referenced. |
-
In general a column name in an external table can be referenced without double quotes.
-
Unless there is an embedded blank, a leading underscore ("_") or leading numeric digit ("0" through "9") in the column name, the original case of the column name is preserved, and it must always be referenced with double quotes and using the original case (upper, lower or mixed-case) of the Avro, ORC, or Parquet column name.
-
After using
DBMS_CLOUD.CREATE_EXTERNAL_TABLE
to create an external table with the format specified asavro
,orc
, orparquet
, use theDESCRIBE
command in SQL*Plus to view the table's column names. -
When Oracle SQL Reserved Words are used in Avro, ORC, or Parquet column names, they must always be double-quoted when referenced anywhere in SQL. See Oracle SQL Reserved Words for more information.
Parent topic: DBMS_CLOUD Avro, ORC, and Parquet Support