AnalyticDB:外部テーブルを使用してデータをdata Warehouse Editionにインポートする

前提条件

• AnalyticDB for MySQL Data Warehouse Editionクラスターが作成されました。

  説明

  AnalyticDB for MySQLクラスターがData Warehouse Edition in elastic modeの場合、クラスター情報 ページの ネットワーク情報 セクションでEniネットワークをオンにする必要があります。

• OSSバケットは、AnalyticDB for MySQLクラスターと同じリージョンに作成されます。 詳細については、「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":"LTAIF****5FsE",
       "accesskey":"Ccw****iWjv",
       "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. AnalyticDB for MySQLクラスターにテーブルを作成し、OSS外部テーブルからインポートされたデータを保存します。

   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":"txt|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":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "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"
}';