LoginSignup
2
2

Oracle Database: SQLcl で Object Storage にある CSVファイルを データ・ロードしてみてみた

Last updated at Posted at 2023-09-17

Oracle SQL Developer Command Line (SQLcl)は、SQL*Plus と SQL Developerの機能を組み合わせたOracle Database用のコマンドライン・インタフェースです。
SQLclリリース20.3以降では、クラウド・ストレージ(cs)コマンドを使用して、Oracle Cloud Infrastructure Object StorageまたはOracle SWIFT Object Storageを使用して、クラウド・ストレージ内のオブジェクトにアクセスできます。
ということで、SQLcl で CSVファイルをOracle Database のテーブルへデータ・ロードしてみてみます。

■ 前提条件

事前に Object Storageの Buket作成と、SQLcl と OCI CLIを設定します。

⚫︎ SQLcl 確認

Oracle Database 19c では、SQLcl がデフォルトで入っています。
SQLcl リリース20.3以降であることを確認

[oracle@db19c-node1 ~]$ sql system/<Password>@pdb
	
	SQLcl: Release 21.4 Production on Sun Sep 17 06:28:46 2023
	
	Copyright (c) 1982, 2023, Oracle.  All rights reserved.
	
	
		New version: 23.2.0 available to download
	
	Last Successful login time: Sun Sep 17 2023 06:28:47 +00:00
	
	Connected to:
	Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
	Version 19.17.0.0.0

SQL> 
SQL> select BANNER_FULL from v$version;

                                                                               BANNER_FULL
__________________________________________________________________________________________
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

⚫︎ OCI CLI設定

クラウド・ストレージ・コマンドを使用するには、最初に認証およびアクセス用のOCIプロファイルを設定する必要があります。
OracleCloud Infrastructure(OCI) の Oracle Database Service、Exadata Database Service には、コマンド・ライン・インタフェース(CLI)がデフォルトでインストールされています。
ということで次を参考に設定します。

・参考: Oracle Database へ OCI コマンド・ライン・インタフェース(CLI) をインストール

1) OCI CLI バージョン確認

[oracle@db19c-node1 ~]$ oci -v
    3.4.4

2) OCI CLI config確認

[oracle@db19c-node1 ~]$  cat ~/.oci/config
    [DEFAULT]
    user=<User OCID>
    fingerprint=b0:46:17:f4:8ece:ca:16:94:6d:1a
    key_file=/home/oracle/.oci/oci_api_key.pem
    tenancy=<Tenancy OCID>
    region=ap-tokyo-1

3) Object Storage アクセス確認

事前に作成した Object Storageへ CLIアクセスできることを確認

[oracle@db19c-node1 ~]$ oci os bucket get --bucket-name bucket --output table
    +-------------------+------------------+--------------+-------------------------------------------------------------+---------------------------------------------------------------------------------+--------------+--------------------------------------+---------------+-----------------------------------------------------+--------------+------------+----------+--------+------------+-----------------------+------------------------------+--------------------+---------------------+--------------+----------------------------------+------------+
    | approximate-count | approximate-size | auto-tiering | compartment-id                                              | created-by                                                                      | defined-tags | etag                                 | freeform-tags | id                                                  | is-read-only | kms-key-id | metadata | name   | namespace  | object-events-enabled | object-lifecycle-policy-etag | public-access-type | replication-enabled | storage-tier | time-created                     | versioning |
    +-------------------+------------------+--------------+-------------------------------------------------------------+---------------------------------------------------------------------------------+--------------+--------------------------------------+---------------+-----------------------------------------------------+--------------+------------+----------+--------+------------+-----------------------+------------------------------+--------------------+---------------------+--------------+----------------------------------+------------+
    | None              | None             | None         | ocid1.compartment.oc1..aaaaaaaa2dmjokxjxiyx5fijqnsfafkl3gef | ocid1.saml2idp.oc1..aaaaaaaarponux6ehq4p2iu3k356p6rx27lc5f3q7/shirok@oracle.com | {}           | 0ad5-44c8-4ec7-afd-55f4f11 | {}      | ocid1.bucket.oc1.ap-tokyo-1.aaaaaaaabfrg2tiomuszwcuvmhvly2g2jm4vm7q | False        | None       | {}       | bucket | shirok | False                 | None                         | NoPublicAccess     | False               | Standard     | 2021-02-02T03:59:12.873000+00:00 | Disabled   |
    +-------------------+------------------+--------------+-------------------------------------------------------------+---------------------------------------------------------------------------------+--------------+--------------------------------------+---------------+-----------------------------------------------------+--------------+------------+----------+--------+------------+-----------------------+------------------------------+--------------------+---------------------+--------------+----------------------------------+------------+

■ CSVファイル・アップロード

次の Object Storage URI へCSVファイルを作成してアップロードします。

・ CSVファイル名: file1.csv
・ Object Storage URI: https://shirok.objectstorage.ap-tokyo-1.oci.customer-oci.com/n/shirok/b/bucket/o/file1.csv

⚫︎ CSVファイル作成

ここではロード用テストデータを作成
1行目は表の列名のヘッダー行をつけます。

[user@macbook ~]$ vi file1.csv
[user@macbook ~]$ cat file1.csv
    X,Y
    1,2
    4,5

⚫︎ Object Storageへアップロード

ここでは、OCIコンソールから Drag & Drop でファイルをアップロードします。

1) アップロード

OCIコンソールから対象の Buket へ CSVファイルをアップロード
01_ObjectStorageデータアップロード01.png

2) アップロードしたファイルの URLパス(URI) 確認

アップロードしたファイルの[オブジェクトの詳細]をクリックして URIを確認
01_ObjectStorageデータアップロード03.png

■ テーブルへのクラウド・ストレージ・ファイルのロード

SYSTEMユーザーへ TEST_TABLE表を作成して CSVファイルをロードしてみてみます。

1) SQLcl 接続

[oracle@db19c-node1 ~]$ sql system/<Password>@pdb

    SQLcl: Release 21.4 Production on Sun Sep 17 06:28:46 2023
	
	Copyright (c) 1982, 2023, Oracle.  All rights reserved.
	
	
		New version: 23.2.0 available to download
	
	Last Successful login time: Sun Sep 17 2023 06:28:47 +00:00
	
	Connected to:
	Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
	Version 19.17.0.0.0

    SYSTEM@pdb SQL>

2) OCI CLIプロファイルを設定

ここでは、初期状態の profile DEFAULT を使用します。

SYSTEM@pdb SQL> oci profile DEFAULT
    Region set to: ap-tokyo-1
    OCI Profile set to DEFAULT
    Transfer method set to oci

3) Cloud Storage URL を設定

csコマンドで Object Storage BuketのURIを設定

SYSTEM@pdb SQL> cs https://shirok.objectstorage.ap-tokyo-1.oci.customer-oci.com/n/shirok/b/bucket
    DBMS_CLOUD Credential: Not Set
    OCI Profile: DEFAULT
    Transfer Method: oci
    URI as specified: https://shirok.objectstorage.ap-tokyo-1.oci.customer-oci.com/n/shirok/b/bucket

4) 表(TEST_TABLE) 作成

SYSTEM@pdb SQL> create table TEST_TABLE(X NUMBER(5),Y VARCHAR2(40));

    Table TEST_TABLE created.

5) Object Storage ファイル・ロード

ファイルのパスを設定して load実行

SYSTEM@pdb SQL> load TEST_TABLE cs /o/file1.csv

    DBMS_CLOUD Credential: Not Set
    OCI Profile: DEFAULT
    Transfer Method: oci
    URI as specified: https://shirok.objectstorage.ap-tokyo-1.oci.customer-oci.com/n/shirok/b/bucket
    Qualifier: /o/file1.csv
    Final URI: https://shirok.objectstorage.ap-tokyo-1.oci.customer-oci.com/n/shirok/b/bucket/o/file1.csv

    Load data into table SYSTEM.TEST_TABLE

    csv
    column_names on
    delimiter ,
    enclosures ""
    encoding UTF8
    row_limit off
    row_terminator default
    skip_rows 0
    skip_after_names

    #INFO Number of rows processed: 2
    #INFO Number of rows in error: 0
    #INFO Last row processed in final committed batch: 2
    SUCCESS: Processed without errors

6) ロード確認

SYSTEM@pdb SQL> select count(*) from TEST_TABLE;

    COUNT(*)
    ___________
            2

SYSTEM@pdb SQL> select * from TEST_TABLE;

    X    Y
    ____ ____
    1 2
    4 5

■ 参考

 ・ OCI コマンドライン・インタフェース(CLI)
 ・ Oracle SQLcl
 ・ SQLcl クラウド・ストレージ・コマンドの使用
 ・ SQLcl ファイルのロードおよび表データのアンロード
 ・ SQLcl クラウド・ストレージ・コマンドの使用
 ・ SQLcl よくある質問
 ・ SQLclコマンド(アルファベット順)
 ・ SQLclの SQL*Plusでサポートされていないコマンドおよび機能のリスト

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
2