DBMS_CLOUD Suporte a Avro, ORC e Parquet
Esta seção abrange o suporte a Avro, ORC e Parquet do DBMS_CLOUD
fornecido com o Autonomous Database.
Opções de Formato do Pacote DBMS_CLOUD para Avro, ORC ou Parquet
O argumento de formato em DBMS_CLOUD
especifica o formato de arquivos de origem.
As duas maneiras de especificar o argumento de formato são:
format => '{"format_option" : “format_value” }'
E:
format => json_object('format_option' value 'format_value'))
Exemplos de:
format => json_object('type' VALUE 'CSV')
Para especificar várias opções de formato, separe os valores com uma vírgula (",
").
Por exemplo:
format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
Opção de Formato | Descrição | Sintaxe |
---|---|---|
|
Quando o valor de Os caracteres "*" e "?" são considerados caracteres curinga quando o parâmetro Padrões de expressão regular só são suportados para o nome do arquivo ou caminho da subpasta em seus URIs e a correspondência de padrões é idêntica à executada pela função Para tabelas externas, essa opção só é suportada com as tabelas criadas em um arquivo no Object Storage. Por exemplo:
Consulte REGEXP_LIKE Condition em Oracle Database 19c SQL Language Reference ou Oracle Database 23ai SQL Language Reference para obter mais informações sobre a condição |
Valor padrão: |
type |
Especifica o tipo de arquivo. |
|
|
Quando o esquema é definido como Os nomes de coluna corresponderão aos encontrados em Avro, ORC ou Parquet. Os tipos de dados são convertidos de tipos de dados Avro, ORC ou Parquet para tipos de dados Oracle. Todas as colunas são adicionadas à tabela. O valor O valor Default: If Observação: Para arquivos de formato Avro, ORC ou Parquet, a opção de formato |
|
DBMS_CLOUD Mapeamento de Tipo de Dados Pacote Avro para Oracle
Descreve o mapeamento de tipos de dados Avro para tipos de dados Oracle.
Observação:
Tipos complexos, como mapas, arrays e structs, são suportados a partir do Oracle Database 19c. Consulte DBMS_CLOUD Tipos Complexos Avro, ORC e Parquet para obter informações sobre como usar tipos complexos Avro.Tipo Avro | Tipo Oracle |
---|---|
INT | NUMBER(10) |
LONG | NÚMERO (19) |
BOOL | NÚMERO (1) |
UTF8 BYTE_ARRAY | RAW(2000) |
FLT | BINARY_FLOAT |
DBL | BINARY_DOUBLE |
DECIMAL(p) | NÚMERO(p) |
DECIMAL(p,s) | NUMBER(p,s) |
DATE | DATE |
STRING | VARCHAR2 |
TIME_MILLIS | VARCHAR2(20 BYTES) |
TIME_MICROS | VARCHAR2(20 BYTES) |
TIMESTAMP_MILLIS | TIMESTAMP(3) |
TIMESTAMP_MICROS | TIMESTAMP(6) |
ENUM | VARCHAR2(n) Em que: "n" é o tamanho máximo real dos valores possíveis de AVRO ENUM |
DURATION | RAW(2000) |
FIXED | RAW(2000) |
NULL | VARCHAR2(1) BYTE |
Consulte DBMS_CLOUD Tipos Complexos Avro, ORC e Parquet para obter informações sobre como usar tipos complexos Avro.
DBMS_CLOUD Mapeamento do Tipo de Dados ORC para Oracle do Pacote
Descreve o mapeamento de tipos de dados ORC para tipos de dados Oracle.
Consulte DBMS_CLOUD Tipos Complexos Avro, ORC e Parquet para obter informações sobre como usar tipos complexos ORC.
Tipo ORC | Tipo Oracle | Mais informações |
---|---|---|
array | Formato JSON VARCHAR2(n) | DBMS_CLOUD Tipos Complexos de Pacote Avro, ORC e Parquet |
bigint (64 bits) | NÚMERO (19) | |
binário | BLOB | |
booliano (1 bit) | NÚMERO (1) | |
caractere | CHAR(n) | |
data | DATE | |
duplo | BINARY_DOUBLE | |
flutuante | BINARY_FLOAT | |
int (32 bits) | NUMBER(10) | |
list | Formato JSON VARCHAR2(n) | DBMS_CLOUD Tipos Complexos de Pacote Avro, ORC e Parquet |
map | Formato JSON VARCHAR2(n) | DBMS_CLOUD Tipos Complexos de Pacote Avro, ORC e Parquet |
smallint (16 bits) | NÚMERO (5) | |
string | VARCHAR2(4000) | |
struct | Formato JSON VARCHAR2(n) | DBMS_CLOUD Tipos Complexos de Pacote Avro, ORC e Parquet |
timestamp | TIMESTAMP | |
smallint (8 bits) | NÚMERO (3) | |
união | Formato JSON VARCHAR2(n) | DBMS_CLOUD Tipos Complexos de Pacote Avro, ORC e Parquet |
varchar | VARCHAR2(n) |
DBMS_CLOUD Mapeamento de Tipo de Dados Pacote Parquet para Oracle
Descreve o mapeamento de tipos de dados Parquet para tipos de dados Oracle.
Observação:
Tipos complexos, como mapas, arrays e structs, são suportados a partir do Oracle Database 19c. Consulte DBMS_CLOUD Tipos Complexos Avro, ORC e Parquet para obter informações sobre como usar tipos complexos Parquet.Tipo Parquet | Tipo Oracle |
---|---|
UINT_64 | NÚMERO (20) |
INT_64 | NÚMERO (19) |
UINT_32 | NUMBER(10) |
INT_32 | NUMBER(10) |
UINT_16 | NÚMERO (5) |
INT_16 | NÚMERO (5) |
UINT_8 | NÚMERO (3) |
INT_8 | NÚMERO (3) |
BOOL | NÚMERO (1) |
UTF8 BYTE_ARRAY | VARCHAR2(4000 BYTES) |
FLT | BINARY_FLOAT |
DBL | BINARY_DOUBLE |
DECIMAL(p) | NÚMERO(p) |
DECIMAL(p,s) | NUMBER(p,s) |
DATE | DATE |
STRING | VARCHAR2(4000) |
TIME_MILLIS | VARCHAR2(20 BYTES) |
TIME_MILLIS_UTC | VARCHAR2(20 BYTES) |
TIME_MICROS | VARCHAR2(20 BYTES) |
TIME_MICROS_UTC | VARCHAR2(20 BYTES) |
TIMESTAMP_MILLIS | TIMESTAMP(3) |
TIMESTAMP_MILLIS_UTC | TIMESTAMP(3) |
TIMESTAMP_MICROS | TIMESTAMP(6) |
TIMESTAMP_MICROS_UTC | TIMESTAMP(6) |
TIMESTAMP_NANOS | TIMESTAMP(9) |
Consulte DBMS_CLOUD Tipos Complexos Avro, ORC e Parquet para obter informações sobre como usar tipos complexos Parquet.
DBMS_CLOUD - Mapeamento de Tipo de Dados Oracle para Parquet do Pacote
Descreve o mapeamento de tipos de dados Oracle para tipos de dados Parquet.
Tipo Oracle | Tipo Parquet |
---|---|
BINARY_DOUBLE | DBL |
BINARY_FLOAT | FLT |
DATE | DATE |
NUMBER(p,s) | DECIMAL(p,s) |
NÚMERO(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 |
Parâmetros da Sessão NLS
Os parâmetros de sessão NLS NLS_DATE_FORMAT
, NLS_TIMESTAMP_FORMAT
, NLS_TIMESTAMP_TZ_FORMAT
e NLS_NUMERIC_CHARACTERS
definem como a data, o timestamp, o timestamp com formato de fuso horário e o separador de radix para timestamp com marcador decimal devem ser mostrados quando uma tabela com esses tipos de coluna for consultada.
Além disso, quando você exporta dados usando DBMS_CLOUD.EXPORT_DATA
e especifica a saída Parquet, o Autonomous Database lê os valores desses parâmetros na tabela NLS_SESSION_PARAMETERS
. O Autonomous Database usa esses valores para converter os tipos de dados DATE
ou TIMESTAMP
da Oracle em tipos Parquet.
Os parâmetros NLS_SESSION_PARAMETERS
suportam uma máscara de formato RR
(especificação de dois caracteres por ano).
A máscara de formato RR
do ano não é suportada para esses parâmetros quando você exporta dados para o Parquet com DBMS_CLOUD.EXPORT_DATA
. Um erro de aplicativo será gerado se você tentar exportar para parquet e o NLS_SESSION_PARAMETERS
estiver definido para usar a máscara de formato RR
(o valor padrão para o formato RR
depende do valor do parâmetro NLS_TERRITORY
).
Quando um dos parâmetros NLS_DATE_FORMAT
, NLS_TIMESTAMP_FORMAT
ou NLS_TIMESTAMP_TZ_FORMAT
usa a máscara de formato RR
, você deve alterar o valor do formato para o valor suportado para exportar dados para o Parquet com DBMS_CLOUD.EXPORT_DATA
. Por exemplo:
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
:SELECT value FROM NLS_SESSION_PARAMETERS
WHERE parameter IN ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT');
Se NLS_DATE_FORMAT
for definido, ele se aplicará às colunas com o tipo de dados DATE
. Se NLS_TIMESTAMP_FORMAT
for definido, ele se aplicará às colunas com o tipo de dados TIMESTAMP
. Se NLS_TIMESTAMP_TZ_FORMAT
for definido, ele se aplicará às colunas com o tipo de dados TIMESTAMP WITH TIME ZONE
.
Consulte Parâmetros de Data e Hora no Oracle Database 19c Database Globalization Support Guide ou Oracle Database 23ai Database Globalization Support Guide e Exibições do Dicionário de Dados NLS no Oracle Database 19c Database Globalization Support Guide ou Oracle Database 23ai Database Globalization Support Guide para obter mais informações.
Tipos Complexos de Pacote DBMS_CLOUD Avro, ORC e Parquet
Descreve o mapeamento de tipos complexos de dados Avro, ORC e Parquet para tipos de dados Oracle.
O Autonomous Database suporta tipos de dados complexos, incluindo os seguintes:
-
struct
-
list
-
map
-
união
-
array
Quando você especifica um tipo de arquivo de origem Avro, ORC ou Parquet e o arquivo de origem inclui colunas complexas, as consultas do Autonomous Database retornam JSON para as colunas complexas. Isso simplifica o processamento dos resultados da consulta; você pode usar os recursos avançados de parsing de JSON da Oracle de forma consistente nos tipos de arquivo e de dados. A seguinte tabela mostra o formato dos tipos complexos no Autonomous Database:
Observação:
Os campos complexos são mapeados para colunasVARCHAR2
e são aplicados limites de tamanho VARCHAR2
.
Tipo | Parquet | ORC | Avro | Oracle |
---|---|---|---|---|
Lista: sequência de valores | List | List | Array | VARCHAR2 (formato JSON)
|
Map: lista de objetos com uma única chave | Mapear | Mapear | Mapear | VARCHAR2 (formato JSON)
|
Union: valores de tipo diferente | Não Disponível | União | União | VARCHAR2 (formato JSON)
|
Object: zero ou mais pares de chave/valor | Struct | Struct | Registro | VARCHAR2 (formato JSON)
|
Se seus arquivos de origem ORC, Parquet ou Avro contiverem tipos complexos, você poderá consultar a saída JSON para esses tipos complexos comuns. Por exemplo, a seguir é mostrado um arquivo ORC, movie-info.orc
, com um tipo complexo (o mesmo tratamento de tipo complexo se aplica aos arquivos de origem Parquet e Avro).
Considere o arquivo movie-info.orc
com o seguinte esquema:
id int
original_title string
overview string
poster_path string
release_date string
vote_count int
runtime int
popularity double
genres array<struct<id:int,name:string>
Observe que cada filme é categorizado por vários genres
usando um array de genres
. O array genres
é um array
de structs
e cada item tem um id
(int
) e um name
(string
). O array genres
é considerado um tipo complexo. Você pode criar uma tabela sobre esse arquivo ORC usando DBMS_CLOUD.CREATE_EXTERNAL_TABLE
da seguinte forma:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE
(
table_name =>'movie_info',
credential_name =>'OBJ_STORE_CRED',
file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/mytenancy/b/movies/o/movie-info.orc',
format => '{"type":"orc", "schema": "first"}');
END;
/
Quando você cria a tabela externa, o banco de dados gera automaticamente as colunas com base no esquema do arquivo ORC (se você estiver usando Avro ou Parquet, o mesmo será aplicado). Para esse exemplo, DBMS_CLOUD.CREATE_EXTERNAL_TABLE
cria uma tabela no seu banco de dados da seguinte forma:
CREATE TABLE "ADMIN"."MOVIE_INFO"
( "ID"
NUMBER(10,0),
"ORIGINAL_TITLE" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
"OVERVIEW" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
"POSTER_PATH" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
"RELEASE_DATE" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
"VOTE_COUNT" NUMBER(10,0),
"RUNTIME" NUMBER(10,0),
"POPULARITY" BINARY_DOUBLE,
"GENRES" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP"
) DEFAULT COLLATION "USING_NLS_COMP"
ORGANIZATION EXTERNAL
( TYPE ORACLE_BIGDATA
DEFAULT DIRECTORY "DATA_PUMP_DIR"
ACCESS PARAMETERS
( com.oracle.bigdata.credential.name=OBJ_STORE_CRED
com.oracle.bigdata.fileformat=ORC
)
LOCATION
(
'https://objectstorage.us-phoenix-1.oraclecloud.com/n/mytenancy/b/movies/o/movie-info.orc'
)
)
REJECT LIMIT UNLIMITED
PARALLEL;
)
Agora você pode consultar os dados do filme:
SELECT original_title, release_date, genres
FROM movie_info
WHERE release_date > '2000'
ORDER BY original_title;
Isso produz a seguinte saída:
original_title release_date genres
(500) Days of Summer 2009 [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},{"id":17,"name":"Horror"},{"id":19,"name":"Western"},{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
10,000 BC 2008 [{"id":6,"name":"Comedy"}]
11:14 2003 [{"id":9,"name":"Thriller"},{"id":14,"name":"Family"}]
127 Hours 2010 [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"}]
13 Going on 30 2004 [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
1408 2007 [{"id":45,"name":"Sci-Fi"},{"id":6,"name":"Comedy"},{"id":17,"name":"Horror"},{"id":6,"name":"Comedy"},{"id":18,"name":"War"}]
Observe que o tipo complexo genres
é retornado como array JSON.
Para tornar os dados JSON mais úteis, você pode transformar a coluna usando funções JSON da Oracle. Por exemplo, você pode usar a notação JSON "." bem como as funções de transformação mais avançadas, como JSON_TABLE
.
Consulte Simple Dot-Notation Access to JSON Data no Oracle Database 19c JSON Developer's Guide ou no Oracle Database 23ai JSON Developer's Guide para obter informações sobre a notação ".".
Consulte SQL/JSON Function JSON_TABLE no Oracle Database 19c JSON Developer's Guide ou Oracle Database 23ai JSON Developer's Guide para obter informações sobre JSON_TABLE
.
SELECT original_title, release_date, m.genre_name, genres
FROM movie_info mi,
JSON_TABLE(mi.genres, '$.name[*]'
COLUMNS (genre_name VARCHAR2(25) PATH
'$')
) AS m
WHERE rownum < 10;
O JSON_TABLE
cria uma linha para cada valor do array, considera a junção externa e a estrutura é analisada para extrair o nome do gênero. Isso produz a seguinte saída:
original_title release_date genre_name genres
(500) Days of Summer 2009 Drama [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
{"id":17,"name":"Horror"},{"id":19,"name":"Western"},
{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer 2009 Comedy [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
{"id":17,"name":"Horror"},{"id":19,"name":"Western"},
{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer 2009 Horror [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
{"id":17,"name":"Horror"},{"id":19,"name":"Western"},
{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer 2009 Western [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
{"id":17,"name":"Horror"},{"id":19,"name":"Western"},
{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer 2009 War [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
{"id":17,"name":"Horror"},{"id":19,"name":"Western"},
{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer 2009 Romance [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
{"id":17,"name":"Horror"},{"id":19,"name":"Western"},
{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
10,000 BC 2008 Comedy [{"id":6,"name":"Comedy"}]
11:14 2003 Family [{"id":9,"name":"Thriller"},{"id":14,"name":"Family"}]
11:14 2003 Thriller [{"id":9,"name":"Thriller"},{"id":14,"name":"Family"}]
127 Hours 2010 Comedy [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"}]
127 Hours 2010 Drama [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"}]
13 Going on 30 2004 Romance [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
13 Going on 30 2004 Comedy [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
13 Going on 30 2004 War [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
13 Going on 30 2004 Drama [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
Mapeamento de Nome de Coluna Avro, ORC e Parquet para Oracle do Pacote DBMS_CLOUD
Descreve as regras de como os nomes de coluna Avro, ORC e Parquet são convertidos em nomes de coluna Oracle.
Há suporte para nomes de coluna Avro, ORC e Parquet, mas pode exigir o uso de aspas duplas para referências de Oracle SQL em tabelas externas. Assim, para facilitar o uso e evitar ter de usar aspas duplas ao mencionar nomes de coluna, se possível, não use os seguintes itens nos nomes de coluna Avro, ORC e Parquet:
-
Espaços em branco incorporados
-
Números à esquerda
-
Sublinhados à esquerda
-
Palavras reservadas do Oracle SQL
A tabela a seguir mostra vários tipos de nomes de coluna Avro, ORC e Parquet e regras para usar os nomes de coluna em nomes de coluna Oracle em tabelas externas.
Nome Avro, ORC ou Parquet | Nome CREATE TABLE | CATÁLOGO Oracle | SQL Válido | Notas |
---|---|---|---|---|
parte, Parte ou PARTE | parte, Parte ou PARTE | PARTE |
|
A Oracle coloca implicitamente os nomes de coluna não cotados |
Nº da Ordem | "Nº da Ordem" | Nº da Ordem | select "Ord No" |
As aspas duplas são obrigatórias quando há espaços em branco incorporados, o que também preserva o uso de letras maiúsculas e minúsculas |
__index_key__ | "__index_key__" | __index_key__ | select "__index_key__" |
As aspas duplas são obrigatórias quando há um sublinhado à esquerda, o que também preserva o uso de letras maiúsculas e minúsculas |
6Way | "6Way" | 6Way | select "6Way" |
As aspas duplas são obrigatórias quando há um dígito numérico à esquerda, o que também preserva o uso de letras maiúsculas e minúsculas |
criar, Criar ou CRIAR e assim por diante. (qualquer variação de maiúsculas/minúsculas), partição, Partição, PARTIÇÃO etc. (para uma palavra Reservada da Oracle) | "CRIAR" "PARTIÇÃO" | CRIAR PARTIÇÃO |
|
É obrigatório colocar entre aspas duplas as palavras Reservadas do Oracle SQL. Elas são forçadas a ficar em letras maiúsculas, mas devem sempre estar entre aspas duplas quando usadas em qualquer lugar do SQL |
ROWID, ROWID, ROWid etc. (para ROWID, consulte as observações) | ide da linha |
|
Para ROWID, qualquer variação entre letras minúsculas ou mistas de ROWID preserva as maiúsculas/minúsculas e deve ficar sempre entre aspas duplas e usar as variações de maiúsculas/minúsculas originais. Por causa do conflito inerente com o Oracle ROWID da tabela, se você especificar o ROWID em caixa alta, ele será automaticamente armazenado como "ROWID" em caixa baixa e sempre deverá estar entre aspas duplas quando for mencionado. |
Notas:
-
Em geral, um nome de coluna em uma tabela externa pode ser mencionado sem estar entre aspas duplas.
-
A menos que haja um espaço em branco incorporado, um sublinhado à esquerda ("_") ou um dígito numérico à esquerda ("0" a "9") no nome da coluna, as letras maiúsculas/minúsculas originais do nome da coluna serão preservadas e sempre deverão ser mencionadas entre aspas duplas e usar as letras originais (maiúsculas, minúsculas ou mistas) do nome de coluna Avro, ORC ou Parquet.
-
Depois de usar
DBMS_CLOUD.CREATE_EXTERNAL_TABLE
para criar uma tabela externa com o formato especificado comoavro
,orc
ouparquet
, use o comandoDESCRIBE
no SQL*Plus para exibir os nomes de coluna da tabela. -
Quando as Palavras Reservadas do Oracle SQL são usadas nos nomes de coluna Avro, ORC ou Parquet, elas sempre devem estar entre aspas duplas quando referenciadas em qualquer lugar em SQL. Consulte Oracle SQL Reserved Words em Oracle Database 23ai SQL Language Reference ou Oracle Database 23ai SQL Language Reference para obter mais informações.