外部テーブルを使用してOSSからdata Warehouse Editionにデータをインポートする - AnalyticDB for MySQL

このトピックでは、外部テーブルを使用してObject Storage Service (OSS) オブジェクトをクエリし、そのオブジェクトをAnalyticDB for MySQL Data Warehouse Edition (V3.0) にインポートする方法について説明します。 Parquet、CSV、またはORC形式のOSSオブジェクトは、外部テーブルを使用してインポートできます。

前提条件

OSSが有効化されています。バケットとディレクトリがOSSに作成されます。詳細については、「OSSコンソールのトピックを使用して開始する」の「OSSの有効化」セクション、バケットの作成、およびディレクトリの作成をご参照ください。
AnalyticDB for MySQL Data Warehouse Edition (V3.0) クラスターは、OSSバケットと同じ仮想プライベートクラウド (VPC) に作成されます。ホワイトリストが設定され、クラスターのデータベースアカウントが作成されます。詳細については、「AnalyticDB For MySQL Data Warehouse Edition (V3.0) の使用」をご参照ください。
AnalyticDB for MySQL Data Warehouse Edition (V3.0) クラスターがエラスティックモードの場合、クラスター情報 ページの ネットワーク情報 セクションでEniネットワークがオンになります。

サンプルデータ

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

uid; その他
12;hello_world_1
27;hello_world_2
28;hello_world_3
33;hello_world_4
37;hello_world_5
40;hello_world_6
...

手順

この例では、oss_import_test_data.txtオブジェクトのデータが、adb_demoという名前のAnalyticDB for MySQLデータベースにインポートされます。

AnalyticDB for MySQLクラスターに接続します。詳細については、「AnalyticDB For MySQLクラスターへの接続」をご参照ください。
データベースを作成します。詳細については、「データベースの作成」をご参照ください。
この例では、adb_demoという名前のデータベースがAnalyticDB for MySQLクラスターに作成されます。
外部テーブルを作成します。 CREATE TABLEステートメントを実行して、adb_demoデータベースにCSV、Parquet、またはORC形式のOSS外部テーブルを作成できます。構文の詳細については、このトピックの「非パーティションオブジェクトのOSS外部テーブルの作成」または「パーティションオブジェクトのOSS外部テーブルの作成」をご参照ください。
OSSデータを照会します。この例では、oss_import_test_external_table外部テーブルが使用されています。
外部テーブルを照会する構文は、AnalyticDB for MySQLテーブルを照会する構文と同じです。次の文を使用して、oss_import_test_external_table OSS外部テーブルからデータを照会できます。
```
SELECT uid、その他FROM oss_import_test_external_table WHERE uid < 100 LIMIT 10;
```
説明
CSV、Parquet、およびORCオブジェクトに対するクエリの場合、データ量が多いとパフォーマンスが低下します。クエリ効率を向上させるために、データをクエリする前に、次の手順を実行してオブジェクトをAnalyticDB for MySQLにインポートすることを推奨します。
宛先テーブルを作成します。 adb_demoデータベースにadb_oss_import_testという名前のターゲットテーブルを作成して、OSSからインポートされたデータを保存できます。次の文を使用して、宛先テーブルを作成できます。
```
が存在しない場合はテーブルを作成します。adb_oss_import_test
(
    uid文字列、
    その他の文字列
)
ハッシュによって分布 (uid); 
```
を実行するExecute anインサート文を使用して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」をご参照ください。例：
```
adb_oss_import_testに挿入
SELECT * からoss_import_test_external_table; 
```
- 方法2: INSERT OVERWRITEステートメントを実行してデータをインポートします。主キーの値が重複している場合、元の値は新しい値で上書きされます。例：
```
INSERT OVERWRITE adb_oss_import_test
SELECT * からoss_import_test_external_table; 
```
- 方法3: INSERT OVERWRITEステートメントを実行して、データを非同期的にインポートします。ほとんどの場合、SUBMIT JOBステートメントは非同期ジョブの送信に使用されます。ジョブを高速化するために、データインポートステートメントの前にヒント (/* + direct_batch_load=true */) を追加できます。詳細については、「INSERT OVERWRITE SELECT」トピックの「非同期書き込み」セクションをご参照ください。例：
```
ジョブ挿入の上書きを送信adb_oss_import_test
SELECT * からoss_import_test_external_table; 
```
  サンプル結果:
```
+ --------------------------------------- +
| job_id |
+ --------------------------------------- +
| 2020112122202917203100908203303 ****** | 
```
  非同期ジョブの送信方法については、「インポートジョブの非同期送信」をご参照ください。

パーティション分割されていないオブジェクトのOSS外部テーブルの作成

OSS外部テーブルをCSV形式で作成

次のステートメントは、CSV形式で外部テーブルを作成する方法を示しています。

テーブルを作成しない場合は、oss_import_test_external_table
(
    uid文字列、
    その他の文字列
)
エンジン='OSS'
TABLE_PROPERTIES='{
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com" 、
    "url":"oss://<bucket-name>/adb_data/" 、
    "accessid":"LTAIF **** 5FsE" 、
    "accesskey":"Ccw **** iWjv" 、
    "delimiter":";",
    "skip_header_line_count":1、} ';

パラメーター	必須	説明
`エンジン='OSS'`	可	外部テーブルに使用されるストレージエンジン (OSS) 。
`テーブル_プロパティ`		AnalyticDB for MySQLがOSSへのアクセスに使用する接続情報。
`エンドポイント`		OSSバケットのエンドポイント。説明 AnalyticDB for MySQLは、VPC経由でElastic Compute Service (ECS) インスタンスからのみOSSにアクセスできます。 OSSコンソールにログインしてバケットを見つけ、[概要] ページでエンドポイントを表示します。
`url`		OSS内のソースオブジェクトまたはディレクトリの絶対パス。ディレクトリの絶対パスの末尾にスラッシュ (/) を使用することをお勧めします。例：オブジェクト: `oss://<bucket-name>/adb/oss_import_test_data.csv` ディレクトリ: `oss://<bucket-name>/adb_data/` 説明このパラメーターをソースディレクトリの絶対パスに設定した場合、作成された外部テーブルにはディレクトリ内のすべてのデータが含まれます。
`accessid`		OSSのオブジェクトまたはディレクトリへのアクセスに使用されるAccessKey ID。 AccessKey IDとAccessKeyシークレットの取得方法については、「アカウントと権限」をご参照ください。
`accesskey`		OSSのオブジェクトまたはディレクトリへのアクセスに使用されるAccessKeyシークレット。
`区切り文字`		CSV形式のオブジェクトの列区切り文字。たとえば、列の区切り文字をコンマ (,) に設定できます。
`null_値`	任意	CSV形式のオブジェクトの`NULL`値。デフォルトでは、空の文字列は`NULL`として定義されます。これは `"null_value": "`です。説明このパラメーターは、V3.1.4.2以降のAnalyticDB for MySQLクラスターでのみサポートされます。詳細については、「リリースノート」をご参照ください。
`ossnull`		オブジェクトの`NULL`値をCSV形式で定義するためのルール。有効な値： 1 (デフォルト): `EMPTY_SEPARATORS` 空の文字列のみが`NULL`として定義されます。たとえば、`a、"" 、、c`は、このルールに基づいて "a" 、"" 、NULL、"c" と解釈されます。 2: `EMPTY_QUOTES`。引用符 (`""`) のみが`NULL`として定義されます。たとえば、`a、"" 、、c`は、このルールに基づいて "a" 、NULL、"" 、"c" と解釈されます。 3: `両方`。空の文字列と引用符 (`""`) はどちらも`NULL`として定義されます。たとえば、`a、"" 、、c`は、このルールに基づいて "a" 、NULL、NULL、"c" と解釈されます。 4: `NEITHER`. 空の文字列と引用符 (`""`) は`NULL`として定義されません。たとえば、`a、"" 、、c`は、このルールに基づいて "a" 、"" 、"、" 、"、" c "と解釈されます。説明上記の例は、`"null_value": ""` を前提としています。
`skip_header_line_count`		データをインポートするときにスキップするヘッダー行の数。 CSVオブジェクトの最初の行はテーブルヘッダーです。このパラメーターを1に設定すると、データをインポートするときにオブジェクトの最初の行がスキップされます。このパラメーターのデフォルト値は0で、行をスキップしないことを指定します。
`oss_ignore_quote_and_escape`		引用符 (") とエスケープ文字を無視するかどうかを指定します。このパラメーターのデフォルト値は`false`で、引用符 (") とエスケープ文字を無視しないように指定します。説明このパラメーターは、V3.1.4.2以降のAnalyticDB for MySQLクラスターでのみサポートされます。詳細については、「リリースノート」をご参照ください。

AnalyticDB for MySQLでは、CSV形式のOSS外部テーブルを使用して、HiveのTEXTファイルのデータを読み書きできます。次のステートメントを使用して、外部テーブルを作成できます。

CREATE TABLE adb_csv_hive_format_oss (
  tinyint、
  b smallint,
  c int,
  d bigint,
  e boolean、
  fフロート,
  gダブル,
  h varchar、
  i varchar, -- binary
  jタイムスタンプ,
  kデシマル (10、4) 、
  l varchar, -- char (10)
  m varchar, -- varchar(100)
  nの日付
) ENGINE = 'OSS' TABLE_PROPERTIES='{
    "format": "csv" 、
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com" 、
    "accessid":"LTAIF **** 5FsE" 、
    "accesskey":"Ccw **** iWjv" 、
    "url":"oss://<bucket-name>/adb_data/" 、
    "delimiter": "\\1" 、
    "null_value": "\\\\ N" 、
    "oss_ignore_quote_and_escape": "true" 、
    "ossnull": 2、} ';

説明

OSS外部テーブルをCSV形式で作成して、HiveでTEXTファイルのデータを読み書きする場合は、次の項目に注意してください。

HiveのTEXTファイルのデフォルトの列区切り文字は \1です。 OSS外部テーブルを使用してHiveのTEXTファイルのデータを読み書きする場合は、delimiterパラメーターの \1を \\1にエスケープする必要があります。
デフォルトでは、HiveのTEXTファイルのNULL値は \Nです。 OSS外部テーブルを使用してHiveのTEXTファイルのデータを読み書きする場合は、null_valueパラメーターの \Nを \\\\ Nにエスケープする必要があります。
HiveのBINARY、CHAR(N) 、およびVARCHAR(N) データ型はすべて、AnalyticDB for MySQLのVARCHARデータ型に対応します。 BOOLEANなどのHiveのその他の基本データ型は、AnalyticDB for MySQLと同じです。

ParquetまたはORC形式でOSS外部テーブルを作成する

次のステートメントは、Parquet形式で外部テーブルを作成する方法を示しています。

テーブルを作成しない場合は、oss_import_test_external_table
(
    uid文字列、
    その他の文字列
)
エンジン='OSS'
TABLE_PROPERTIES='{
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com" 、
    "url":"oss://<bucket-name>/adb_data/" 、
    "accessid":"LTAIF **** 5FsE" 、
    "accesskey":"Ccw **** iWjv" 、
    "format":"parquet"
} ';

パラメーター	説明
`エンジン='OSS'`	外部テーブルに使用されるストレージエンジン (OSS) 。
`テーブル_プロパティ`	AnalyticDB for MySQLがOSSへのアクセスに使用する接続情報。
`エンドポイント`	OSSバケットのエンドポイント。説明 AnalyticDB for MySQLはVPC経由のECSインスタンスからのみOSSにアクセスできます。 OSSコンソールにログインしてバケットを見つけ、[概要] ページでエンドポイントを表示します。
`url`	OSS内のソースオブジェクトまたはディレクトリの絶対パス。ディレクトリの絶対パスの末尾にスラッシュ (/) を使用することをお勧めします。例：オブジェクト: `oss://<bucket-name>/adb/oss_import_test_data.parquet` ディレクトリ: `oss://<bucket-name>/adb_data/` 説明外部テーブルを作成するときは、`url`パラメーターに実際の絶対パスを指定します。このパラメーターをソースディレクトリの絶対パスに設定した場合、作成された外部テーブルにはディレクトリ内のすべてのデータが含まれます。
`accessid`	OSSのオブジェクトまたはディレクトリへのアクセスに使用されるAccessKey ID。 AccessKey IDとAccessKeyシークレットの取得方法については、「アカウントと権限」をご参照ください。
`accesskey`	OSSのオブジェクトまたはディレクトリへのアクセスに使用されるAccessKeyシークレット。
`形式`	オブジェクトの形式。 Parquet形式で外部テーブルを作成する場合、このパラメーターを`parquet`に設定する必要があります。 ORC形式で外部テーブルを作成する場合、このパラメーターを`orc`に設定する必要があります。説明このパラメーターを指定しない場合は、CSV形式が使用されます。

説明

外部テーブルを作成するためにステートメントで使用される列名は、ParquetまたはORCファイルの列名と同じである必要があります。列名は大文字と小文字を区別しません。ステートメントの列の順序は、ParquetまたはORCファイルの列の順序と同じである必要があります。
外部テーブルを作成するときは、ParquetファイルまたはORCファイルの特定の列のみを外部テーブルの列として選択できます。 ParquetファイルまたはORCファイルで選択されていない列はインポートされません。
外部テーブルの作成に使用されるステートメントにParquetまたはORCファイルにない列が含まれている場合、この列にはNULLが返されます。

次の表は、ParquetとAnalyticDB for MySQL V3.0のデータ型間のマッピングを示しています。

基本タイプin Parquet	Parquetの論理型	AnalyticDB for MySQL V3.0のデータ型
BOOLEAN	N/A	BOOLEAN
INT32	INT_8	TINYINT
INT32	INT_16	SMALLINT
INT32	N/A	INTまたはINTEGER
INT64	N/A	BIGINT
FLOAT	N/A	FLOAT
DOUBLE	N/A	DOUBLE
FIXED_LEN_BYTE_ARRAY BINARY INT64 INT32	DECIMAL	DECIMAL
BINARY	UTF-8	VARCHAR STRING JSON (Parquet形式のオブジェクトにJSON形式の列が含まれている場合に使用できます)
INT32	日付	日付
INT64	TIMESTAMP_MILLIS	TIMESTAMPまたはDATETIME
INT96	N/A	TIMESTAMPまたはDATETIME

重要

STRUCTデータ型の列を使用するParquet外部テーブルは作成できません。

次の表は、ORCとAnalyticDB for MySQL V3.0のデータ型間のマッピングを示しています。

ORCのデータ型	AnalyticDB for MySQL V3.0のデータ型
BOOLEAN	BOOLEAN
BYTE	TINYINT
短い	SMALLINT
INT	INTまたはINTEGER
LONG	BIGINT
DECIMAL	DECIMAL
FLOAT	FLOAT
DOUBLE	DOUBLE
BINARY STRING VARCHAR	VARCHAR STRING JSON (ORC形式のオブジェクトにJSON形式の列が含まれている場合に使用できます)
TIMESTAMP	TIMESTAMPまたはDATETIME
日付	日付

重要

LIST、STRUCT、またはUNIONデータ型の列を使用するORC外部テーブルは作成できません。 MAPデータ型の列を使用するORC外部テーブルは作成できますが、照会はできません。

パーティションオブジェクトのOSS外部テーブルの作成

パーティションを含むOSSデータ用に階層ディレクトリが生成されます。例：

parquet_partition_classic /
â ─ ─ p1=2020-01-01
│ ├─ ─ p2=4
│ │ ├─ ─ p3=上海
│ │ │ ├─ 000000_0
│ │ │ ά ─ 000000_1
│ │ 様 ─ ─ p3=深セン
│ │ ά ─ ─ 000000_0
│ õ ─ ─ p2=6
│ õ ─ ─ p3=深セン
│ └ ─ ─ 000000_0
â ─ ─ p1=2020-01-02
│ õ ─ ─ p2=8
│ ├─ ─ p3=上海
│ │ ά ─ ─ 000000_0
│ õ ─ ─ p3=深セン
│ └ ─ ─ 000000_0
└ ─ ─ p1=2020-01-03
    └ ─ ─ p2=6
        â ─ ─ p2=杭州
        └ ─ ─ p3=深セン
            └ ─ ─ 000000_0

上記の例では、p1はレベル1のパーティションを示し、p2はレベル2のパーティションを示し、p3はレベル3のパーティションを示します。パーティションごとにデータをクエリする場合は、OSS外部テーブルの作成に使用するステートメントでパーティション列を指定する必要があります。次のステートメントは、OSS外部テーブルを作成し、外部テーブルでパーティション列を指定する方法を示しています。この例では、Parquetオブジェクトが使用されます。

oss_parquet_partition_tableが存在しない場合は

CREATE TABLE
(
  uid varchar,
  その他のvarchar,
  p1日付、
  p2 int,
  p3 varchar
)
エンジン='OSS'
TABLE_PROPERTIES='{
  "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com" 、
  "url":"oss://<bucket-name>/adb/oss_parquet_data_dir" 、
  "accessid":"LTAIF **** 5FsE" 、
  "accesskey":"Ccw **** iWjv" 、
  "format":"parquet" 、
  "partition_column":"p1, p2, p3"
} ';

説明

TABLE_PROPERTIESのpartition_columnプロパティは、パーティション列 (例ではp1、p2、p3など) を宣言する必要があります。 partition_columnで指定されるパーティション列のシーケンスは、外部テーブルの作成に使用されるステートメントで定義されているパーティション列のシーケンスと同じである必要があります。
外部テーブルでパーティション列を定義する場合は、パーティションを含む列 (例のp1、p2、p3など) とそのデータ型を指定する必要があります。ステートメントの最後にパーティション列を指定する必要があります。
外部テーブルの作成に使用されるステートメントで定義されているパーティション列の順序は、partition_columnプロパティで指定されているパーティション列の順序と同じである必要があります。
パーティション列は、BOOLEAN、TINYINT、SMALLINT、INT、INTEGER、BIGINT、FLOAT、DOUBLE、DECIMAL、VARCHAR、STRING、DATE、およびTIMESTAMPをサポートします。
パーティション列のクエリに使用される構文と、クエリ結果の表示方法は、他の列と同じです。
formatパラメーターが指定されていない場合は、CSV形式が使用されます。