從雲端的檔案載入資料
PL/SQL 套件 DBMS_CLOUD
支援將資料從雲端中的檔案載入至 Autonomous Database on Dedicated Exadata Infrastructure 中建立的表格。
DBMS_CLOUD
提供的下列 PL/SQL 程序,從不同的檔案格式載入資料:
- 雲端中的文字檔,使用
DBMS_CLOUD.COPY_DATA
程序 - 雲端中的 JSON 檔案,使用
DBMS_CLOUD.COPY_TEXT
程序
- 來源檔案可在您的從屬端電腦中以本機檔案形式使用,或上傳到雲端物件存放區 (例如 Oracle Cloud Infrastructure Object Storage),供嘗試載入資料的資料庫使用者存取。
- 使用
DBMS_CLOUD.CREATE_CREDENTIAL
程序儲存您的雲端物件儲存證明資料。請參閱建立證明資料以瞭解詳細資訊。
套裝程式 DBMS_CLOUD
支援從下列雲端服務中的檔案載入:Oracle Cloud Infrastructure Object Storage 、Oracle Cloud Infrastructure Object Storage Classic 、Azure Blob Storage 及 Amazon S3 。
相關主題
建立證明資料
瞭解如何使用 DBMS_CLOUD.CREATE_CREDENTIAL
程序儲存雲端物件儲存證明資料。
DBMS_CLOUD.CREATE_CREDENTIAL
程序 (以 Web 為基礎的 SQL Developer 工具)。舉例而言:BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL
(
credential_name => 'DEF_CRED_NAME',
username => 'adb_user@oracle.com',
password => 'password'
);
END;
/
您為 username
和 password
提供的值取決於您使用的雲端物件儲存服務:
-
Oracle Cloud Infrastructure Object Storage :
username
是您的 Oracle Cloud Infrastructure 使用者名稱,而password
是您的 Oracle Cloud Infrastructure 認證權杖。請參閱使用認證權杖。 -
Oracle Cloud Infrastructure Object Storage Classic :
username
是您的 Oracle Cloud Infrastructure Classic 使用者名稱,而password
是您的 Oracle Cloud Infrastructure Classic 密碼。
此作業會以加密格式將證明資料儲存在資料庫中。您可以使用任何證明資料名稱。請注意,除非您的物件儲存證明資料變更,否則此步驟僅需要一次。儲存證明資料之後,就可以在所有資料載入使用相同的證明資料名稱。
從文字檔載入資料
瞭解如何使用 DBMS_CLOUD.COPY_DATA
程序將雲端文字檔中的資料載入至 Autonomous Database 。
此範例中的來源檔案 channels.txt
具有下列資料:
S,Direct Sales,Direct T,Tele Sales,Direct C,Catalog,Indirect I,Internet,Indirect P,Partners,Others
將分隔文件的 JSON 檔案載入集合中
瞭解如何使用 DBMS_CLOUD.COPY_DATA
程序將分隔文件的 JSON 檔案載入至 Autonomous Database 中的集合。
此範例會從以行分隔的檔案載入 JSON 值,並使用 JSON 檔案 myCollection.json
。每個值 (每一行) 都會以單一文件形式載入 Autonomous Database 上的集合。
以下是此類檔案的範例。它有三行,每行只有一個物件。這些物件都會以個別的 JSON 文件載入。
{ "name" : "apple", "count": 20 } { "name" : "orange", "count": 42 } { "name" : "pear", "count": 10 }
將 JSON 文件的陣列載入集合
瞭解如何使用 DBMS_CLOUD.COPY_COLLECTION
程序將 JSON 文件陣列載入 Autonomous Database 中的集合。
此範例使用 JSON 檔案 fruit_array.json
。下列顯示檔案 fruit_array.json
的內容:
[{"name" : "apple", "count": 20 }, {"name" : "orange", "count": 42 }, {"name" : "pear", "count": 10 }]
監控及疑難排解資料載入
使用 PL/SQL 套裝程式
完成的所有資料載入作業都會記錄在 DBMS_CLOUD
dba_load_operations
和 user_load_operations
表格中:
-
dba_load_operations
:顯示所有載入作業。 -
user_load_operations
:顯示綱要中的載入作業。
查詢這些表格以查看有關進行中和已完成資料載入的資訊。例如,在 TYPE
資料欄上使用 SELECT
敘述句搭配 WHERE
子句述詞,顯示類型為 COPY
的載入作業:
SELECT table_name, owner_name, type, status, start_time, update_time, logfile_table, badfile_table
FROM user_load_operations WHERE type = 'COPY';
TABLE_NAME OWNER_NAME TYPE STATUS START_TIME UPDATE_TIME LOGFILE_TABLE BADFILE_TABLE
---------- ----------- ------- ---------- ---------------------- --------------------- --------------- ------------- ------------- -------------
CHANNELS SH COPY COMPLETED 04-MAR-21 07.38.30.522711000 AM GMT 04-MAR-21 07.38.30.522711000 AM GMT COPY$1_LOG COPY$1_BAD
LOGFILE_TABLE
資料欄顯示您可以查詢的表格名稱,以查看載入作業的日誌。例如,下列查詢會顯示載入作業的日誌:
select * from COPY$21_LOG;
BADFILE_TABLE
資料欄會顯示您可以查詢的表格名稱,以查看載入期間發生錯誤的資料列。例如,下列查詢會顯示載入作業的拒絕記錄:
select * from COPY$21_BAD;
視日誌中顯示的錯誤以及指定之 BADFILE_TABLE
表格中顯示的資料列而定,您可以在 DBMS_CLOUD.COPY_DATA
中指定正確的格式選項來更正錯誤。
附註:
每個載入作業會儲存LOGFILE_TABLE
和 BADFILE_TABLE
表格兩天,然後自動移除。
請參閱 DELETE_ALL_OPERATIONS 程序,瞭解清除 user_load_operations
表格的資訊。
代表擴充純量值的文字 JSON 物件
原生二進位 JSON 資料 (OSON 格式) 可新增對應 SQL 類型且不屬於 JSON 標準的純量類型 (例如日期),藉此擴充 JSON 語言。Oracle Database 也支援使用文字 JSON 物件來代表 JSON 純量值,包括這類非標準值。
當您從包含這類擴充物件的文字 JSON 資料建立原生二進位 JSON 資料時,可以選擇性地將它們取代為對應的 (原生二進位) JSON 純量值。
擴充物件的範例為 {"$numberDecimal":31}
。它代表非標準類型十進位數的 JSON 純量值,以及解譯為這類值時,會以原生二進位格式的十進位數字取代。
例如,當您使用 JSON 資料類型建構子 JSON
時,如果您使用關鍵字 EXTENDED
,則文字輸入中可辨識的延伸物件會被原生二進位 JSON 結果中的對應純量值取代。如果您未包含關鍵字 EXTENDED
,則不會發生這類取代;文字延伸 JSON 物件會直接轉換為原生二進位格式的 JSON 物件。
相反地,當您使用 Oracle SQL 函數 json_serialize
將二進位 JSON 資料序列化為文字 JSON 資料 (VARCHAR2
、CLOB
或 BLOB
) 時,您可以使用關鍵字 EXTENDED
將 (原生二進位) JSON 純量值取代為對應的文字延伸 JSON 物件。
附註:
如果您使用的資料庫是 Oracle Autonomous Database,則可以使用 PL/SQL 程序 DBMS_CLOUD.copy_collection
,從 JSON 資料檔案 (例如一般 NoSQL 資料庫 (包括 Oracle NoSQL Database) 建立 JSON 文件收集。
如果您使用 ejson
作為程序的 type
參數值,則輸入檔中已辨識的擴充 JSON 物件會被產生的原生二進位 JSON 集合中對應的純量值取代。另一方面,您可以使用關鍵字 EXTENDED
的函數 json_serialize
,將純量值取代為所產生文字 JSON 資料中的延伸 JSON 物件。
以下是擴充物件的兩個主要使用案例:
-
Exchange (匯入 / 匯出):
-
擷取包含擴充物件的現有 JSON 資料 (從某處)。
-
將原生二進位 JSON 資料序列化為文字 JSON 資料與擴充物件,供資料庫外部使用。
-
-
檢查原生二進位 JSON 資料:查看對應的擴充物件以查看您擁有的內容。
為了進行交換,您可以從通用 NoSQL 資料庫 (包括 Oracle NoSQL Database) 產生的檔案擷取 JSON 資料,將延伸的物件轉換成原生二進位 JSON 純量。另一方面,您可以將原生二進位 JSON 資料匯出為文字資料,並以對應的文字延伸 JSON 物件取代 Oracle 特定的純量 JSON 值。
秘訣:
例如檢查,將原生 JSON 資料序列化的結果視為 {"dob" : "2000-01-02T00:00:00"}
之類的物件。"2000-01-02T00:00:00"
是將類型為 date 的原生二進位值序列化的結果,還是原生二進位值只是字串?搭配關鍵字 EXTENDED
使用 json_serialize
可讓您知道。
一般而言,延伸物件欄位與純量 JSON 類型的對應是多對一:可將一種以上的延伸 JSON 物件對應至指定的純量值。例如,延伸的 JSON 物件 {"$numberDecimal":"31"}
和 {"$numberLong:"31"}
都會轉譯為 JSON 語言純量類型編號的值 31,而項目方法 type()
則會傳回每個 JSON 純量值的 "number"
。
項目方法 type()
會報告其目標值的 JSON 語言純量類型 (作為 JSON 字串)。某些純量值在內部均可區別,即使具有相同的純量類型。這通常允許函數 json_serialize
(含關鍵字 EXTENDED
) 重新建構原始延伸 JSON 物件。這類純量值在內部會透過使用不同的 SQL 類型來實行,或使用衍生的擴充 JSON 物件類型來標記這些純量值。
當 json_serialize
重新建構原始延伸 JSON 物件時,結果並非一律以文字方式與原始物件相同,但一律相等。例如,即使欄位值的類型 (字串和數字) 不同,{"$numberDecimal":"31"}
和 {"$numberDecimal":31}
都是語意等值。它們會轉譯成相同的內部值,而且每個值都會標記成衍生自 $numberDecimal
延伸物件 (相同標記)。但序列化之後,兩個結果便是 {"$numberDecimal":31}
。Oracle 一律使用與欄位值最直接相關的類型,在此情況下為純量類型編號的 JSON 語言值 31
。
表格 - 顯示使用之各種類型的通信。它會對應 (1) 種作為輸入的延伸物件類型、(2) 項目方法報告的類型 type()
、(3) 內部使用的 SQL 類型、(4) 函數 json_serialize
作為輸出的標準 JSON 語言類型,以及 (5) 指定關鍵字 EXTENDED
時由 json_serialize
輸出的延伸物件類型。
表格 - 擴充的 JSON 物件類型關係
擴充物件類型 (輸入) | Oracle JSON 純量類型 (按 type() 報告) | SQL 純量類型 | 標準 JSON 純量類型 (輸出) | 擴充物件類型 (輸出) |
---|---|---|---|---|
$numberDouble ,值為 JSON 數字、代表數字的字串,或下列其中一個字串:"Infinity" 、"-Infinity" 、"Inf" 、"-Inf" 、"Nan" 註腳 1
|
雙精準數 | BINARY_DOUBLE |
數字 |
值為 JSON 數字的 $numberDouble 或下列其中一個字串:"Inf" 、"-Inf" 、"Nan" 註腳 2 |
$numberFloat 的值與 $numberDouble 的值相同 |
浮點數 | BINARY_FLOAT |
數字 |
$numberFloat 的值與 $numberDouble 的值相同 |
$numberDecimal 的值與 $numberDouble 的值相同 |
數字 | NUMBER |
數字 |
$numberDecimal 的值與 $numberDouble 的值相同 |
$numberInt 值為正負號的 32 位元整數,或代表數字的字串
|
數字 | NUMBER |
數字 |
$numberInt 的值與 $numberDouble 的值相同 |
$numberLong ,值為 JSON 數字或代表數字的字串
|
數字 | NUMBER |
數字 |
$numberLong 的值與 $numberDouble 的值相同 |
值為下列其中之一的
當值是 base-64 字元的字串時,延伸物件也可以具有值為 0 或 4 的欄位 |
二進位 | BLOB 或 RAW |
字串 轉換等同於使用 SQL 函數 |
下列其中一項:
|
值為 24 個十六進位字元字串的 $oid
|
二進位 | RAW(12) |
字串 轉換等同於使用 SQL 函數 |
值為 24 個十六進位字元字串的 $rawid
|
值為 $rawhex 的字串為偶數目的十六進位字元
|
二進位 | RAW |
字串 轉換等同於使用 SQL 函數 |
$binary 的值是 base-64 個字元的字串,以 = 字元填補右側
|
值為 24 或 32 個十六進位字元字串的 $rawid
|
二進位 | RAW |
字串 轉換等同於使用 SQL 函數 |
$rawid |
值為 ISO 8601 日期字串的 $oracleDate
|
日期 | DATE |
字串 |
值為 ISO 8601 日期字串的 $oracleDate
|
值為 ISO 8601 時戳字串的 $oracleTimestamp
|
時戳 | TIMESTAMP |
字串 |
值為 ISO 8601 時戳字串的 $oracleTimestamp
|
值為 ISO 8601 時戳字串的 $oracleTimestampTZ ,其中包含數字時區位移或 Z |
含時區的時戳 | TIMESTAMP WITH TIME ZONE |
字串 |
值為 ISO 8601 時戳字串的 $oracleTimestampTZ ,其中包含數字時區位移或 Z |
|
含時區的時戳 | TIMESTAMP WITH TIME ZONE |
字串 |
值為 ISO 8601 時戳字串的 $oracleTimestampTZ ,其中包含數字時區位移或 Z |
指定給 SQL 函數 to_dsinterval 的 ISO 8601 間隔字串值為 $intervalDaySecond |
daysecondInterval | INTERVAL DAY TO SECOND |
字串 |
指定給 SQL 函數 to_dsinterval 的 ISO 8601 間隔字串值為 $intervalDaySecond |
指定給 SQL 函數 to_yminterval 的 ISO 8601 間隔字串值為 $intervalYearMonth |
yearmonthInterval | INTERVAL YEAR TO MONTH |
字串 |
指定給 SQL 函數 to_yminterval 的 ISO 8601 間隔字串值為 $intervalYearMonth |
兩個欄位:
|
向量 | VECTOR |
數字陣列 |
兩個欄位:
|
註腳 1 字串值會以不區分大小寫的方式解譯。例如,接受 "NAN"
"nan"
和 "nAn"
,等同於 "INF"
、"inFinity"
和 "iNf"
。使用完整字或縮寫接受無限大 ("Infinity"
或 "Inf"
) 和小 ("-Infinity"
或 "-Inf"
) 數字。
註腳 2 輸出時,只會使用這些字串值 — 沒有全字的 Infinity 或字母大小寫的變體。
另請參閱: