このトピックでは、外部テーブルを使用してObject Storage Service (OSS) オブジェクトをクエリし、そのオブジェクトをAnalyticDB for MySQL Data Warehouse Editionにインポートする方法について説明します。 Parquet、CSV、またはORC形式のOSSオブジェクトは、外部テーブルを使用してインポートできます。
前提条件
OSSが有効化されています。 バケットとディレクトリがOSSに作成されます。 詳細については、「OSSの有効化」、「バケットの作成」、「ディレクトリの作成」をご参照ください。
AnalyticDB for MySQL Data Warehouse Editionクラスターは、OSSバケットと同じ仮想プライベートクラウド (VPC) に作成されます。 ホワイトリストが設定され、クラスターのデータベースアカウントが作成されます。 詳細については、「AnalyticDB For MySQL Data Warehouse Editionの使用」をご参照ください。
AnalyticDB for MySQL Data Warehouse Editionクラスターがエラスティックモードの場合、クラスター情報 ページの ネットワーク情報 セクションでEniネットワークがオンになります。
サンプルデータ
この例では、oss_import_test_data.csv
オブジェクトがOSSの <bucket-nam e>.oss-cn-hangzhou.aliyuncs.com/adb/
ディレクトリにアップロードされます。 行の区切り文字は改行で、列の区切り文字はセミコロン (;
) です。 次のコードは、このオブジェクトに含まれるデータの一部を示しています。
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
...
手順
この例では、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, other FROM oss_import_test_external_table WHERE uid < 100 LIMIT 10;
説明CSV、Parquet、およびORCオブジェクトに対するクエリの場合、データ量が多いとパフォーマンスが低下します。 クエリ効率を向上させるために、データをクエリする前に、次の手順を実行してオブジェクトをAnalyticDB for MySQLにインポートすることを推奨します。
宛先テーブルを作成します。
adb_demo
データベースにadb_oss_import_test
という名前のターゲットテーブルを作成して、OSSからインポートされたデータを保存できます。 次の文を使用して、宛先テーブルを作成できます。CREATE TABLE IF NOT EXISTS adb_oss_import_test ( uid string, other string ) DISTRIBUTED BY HASH(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」をご参照ください。 例: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外部テーブルの作成
OSS外部テーブルをCSV形式で作成
次のステートメントは、CSV形式で外部テーブルを作成する方法を示しています。
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://<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/
説明このパラメーターをソースディレクトリの絶対パスに設定した場合、作成された外部テーブルにはディレクトリ内のすべてのデータが含まれます。
アスタリスク (
*
) で終わるパス:oss://<bucket-name>/adb_data/list_file_with_prefix/test *
説明上記のサンプルパスは、
oss://<bucket-name>/adb_data/list_file_with_prefix/testfile1
、oss://<bucket-name>/adb_data/list_file_with_prefix/test1/file2
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 ( 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://<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形式で外部テーブルを作成する方法を示しています。
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://<bucket-name>/adb_data/", "accessid":"LTAIF****5FsE", "accesskey":"Ccw****iWjv", "format":"parquet" }';
パラメーター
説明
エンジン='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.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のデータ型間のマッピングを示します。
基本タイプin Parquet
Parquetの論理型
AnalyticDB for MySQLのデータ型
BOOLEAN
非該当
BOOLEAN
INT32
INT_8
TINYINT
INT32
INT_16
SMALLINT
INT32
非該当
INTまたはINTEGER
INT64
非該当
BIGINT
FLOAT
非該当
FLOAT
DOUBLE
非該当
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
非該当
TIMESTAMPまたはDATETIME
重要STRUCT
データ型の列を使用するParquet外部テーブルは作成できません。次の表に、ORCとAnalyticDB for MySQLのデータ型間のマッピングを示します。
ORCのデータ型
AnalyticDB for MySQLのデータ型
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=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
上記の例では、p1はレベル1のパーティションを示し、p2はレベル2のパーティションを示し、p3はレベル3のパーティションを示します。 パーティションごとにデータをクエリする場合は、OSS外部テーブルの作成に使用するステートメントでパーティション列を指定する必要があります。 次のステートメントは、OSS外部テーブルを作成し、外部テーブルでパーティション列を指定する方法を示しています。 この例では、Parquetオブジェクトが使用されます。
oss_parquet_partition_tableが存在しない場合は
CREATE TABLE IF NOT EXISTS oss_parquet_partition_table
(
uid varchar,
other varchar,
p1 date,
p2 int,
p3 varchar
)
ENGINE='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形式が使用されます。