AnalyticDB:Data Warehouse Edition にデータをインポートするための外部テーブルの使用

前提条件

• AnalyticDB for MySQL Data Warehouse Edition クラスタが作成されていること。

• AnalyticDB for MySQL クラスタで ENI (Elastic Network Interface) が有効になっていること。

  重要

  • AnalyticDB for MySQL コンソール にログインし、クラスタ詳細ページの左側のナビゲーションウィンドウで [クラスタ管理] > [クラスタ情報] を選択し、[ネットワーク情報] セクションで ENI を有効にします。

  • ENI を有効または無効にすると、データベース接続が約 2 分間中断される場合があります。この間、読み取りまたは書き込み操作を実行することはできません。 ENI を有効または無効にする場合は注意してください。

• AnalyticDB for MySQL クラスタと同じリージョンに OSS バケットが作成されていること。詳細については、「OSS をアクティブ化する」をご参照ください。

サンプルデータ

この例では、oss_import_test_data.csv オブジェクトが OSS の testBucketname/adb/ ディレクトリにアップロードされています。行区切り文字は改行、列区切り文字はセミコロン (;) です。oss_import_test_data.csv オブジェクトに含まれるデータの一部を以下に示します。

uid;other
12;hello_world_1
27;hello_world_2
28;hello_world_3
33;hello_world_4
37;hello_world_5
40;hello_world_6        

手順

1. AnalyticDB for MySQL クラスタにデータベースを作成します。

   CREATE DATABASE adb_demo;

2. 外部テーブルを作成します。CREATE TABLE 文を実行して、CSV、Parquet、または ORC 形式の OSS 外部テーブルを作成できます。詳細については、このトピックの「OSS 外部テーブルを作成するための構文」セクションをご参照ください。

   この例では、CSV 形式の oss_import_test_external_table という名前の非パーティション外部テーブルを使用します。

   CREATE TABLE IF NOT EXISTS oss_import_test_external_table
   (
       uid string,
       other string
   )
   ENGINE='OSS'
   TABLE_PROPERTIES='{
       "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
       "url":"oss://testBucketname/adb/oss_import_test_data.csv",
       "accessid":"LTAI****************",
       "accesskey":"yourAccessKeySecret",
       "delimiter":";",
       "skip_header_line_count":1,
       "charset":"utf-8"
   }'; 

3. oss_import_test_external_table 外部テーブルのデータをクエリします。

   説明

   CSV、Parquet、および ORC オブジェクトに対するクエリでは、データ量が多いほどパフォーマンスの低下が大きくなります。クエリ効率を向上させるには、以下の手順 (4 と 5) を実行して、OSS 外部テーブルのデータをクエリするために AnalyticDB for MySQL にインポートすることをお勧めします。

   SELECT uid, other FROM oss_import_test_external_table WHERE uid < 100 LIMIT 10;

4. OSS 外部テーブルからインポートされたデータを格納するために、AnalyticDB for MySQL クラスタにテーブルを作成します。

   CREATE TABLE IF NOT EXISTS adb_oss_import_test
   (
       uid string,
       other string
   )
   DISTRIBUTED BY HASH(uid);

5. INSERT 文を実行して、OSS 外部テーブルから AnalyticDB for MySQL にデータをインポートします。

   重要

   デフォルトでは、INSERT INTO 文と INSERT OVERWRITE SELECT 文はデータを同期的にインポートします。数百ギガバイトのデータをインポートする場合、クライアントは AnalyticDB for MySQL サーバーとの接続を長時間維持する必要があります。このプロセス中、ネットワークの切断によりインポートが失敗する可能性があります。したがって、大量のデータをインポートする場合は、SUBMIT JOB INSERT OVERWRITE SELECT 文を実行してデータを非同期的にインポートすることをお勧めします。

   • 方法 1：INSERT INTO 文を実行してデータをインポートします。プライマリキーに重複する値がある場合、データは繰り返し挿入されず、INSERT INTO 文は INSERT IGNORE INTO 文と同じです。詳細については、「INSERT INTO」をご参照ください。

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • 方法 2：INSERT OVERWRITE 文を実行してデータをインポートします。プライマリキーに重複する値がある場合、元の値は新しい値で上書きされます。

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • 方法 3：INSERT OVERWRITE 文を実行してデータを非同期的にインポートします。ほとんどの場合、SUBMIT JOB 文は非同期ジョブを送信するために使用されます。データインポート文の前にヒント (/*+ direct_batch_load=true*/) を追加して、ジョブを高速化できます。詳細については、「INSERT OVERWRITE SELECT」トピックの「非同期書き込み」セクションをご参照ください。

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     サンプル結果：

     +---------------------------------------+
     | job_id                                |
     +---------------------------------------+
     | 2020112122202917203100908203303****** |

     非同期ジョブの送信方法については、「インポートジョブを非同期的に送信する」をご参照ください。

OSS 外部テーブルを作成するための構文

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"text|orc|parquet",
    "delimiter":";",
    "skip_header_line_count":1,
    "charset":"utf-8"
}';

CREATE TABLE adb_csv_hive_format_oss (
  a tinyint,
  b smallint,
  c int,
  d bigint,
  e boolean,
  f float,
  g double,
  h varchar,
  i varchar, -- binary
  j timestamp,
  k DECIMAL(10, 4),
  l varchar, -- char(10)
  m varchar, -- varchar(100)
  n date
) ENGINE = 'OSS' TABLE_PROPERTIES='{
    "format": "csv",
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "accessid":"LTAI****************",
    "accesskey":"yourAccessKeySecret",
    "url":"oss://testBucketname/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2
}';

parquet_partition_classic/
├── p1=2020-01-01
│ ├── p2=4
│ │ ├── p3=SHANGHAI
│ │ │ ├── 000000_0
│           └── 000000_1
│       └── p3=SHENZHEN
│ │     └── 000000_0
│ └── p2=6
│     └── p3=SHENZHEN
│         └── 000000_0
├── p1=2020-01-02
│ └── p2=8
│     ├── p3=SHANGHAI
│     │ └── 000000_0
│     └── p3=SHENZHEN
│         └── 000000_0
└── p1=2020-01-03
    └── p2=6
        ├── p2=HANGZHOU
        └── p3=SHENZHEN
            └── 000000_0

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, ...])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"parquet",
    "partition_column":"p1, p2, p3"
}';