ApsaraDB for SelectDB でのファイルデータの分析 - ApsaraDB for SelectDB

ApsaraDB for SelectDB は、テーブル値関数（TVF）を提供します。これにより、Amazon Simple Storage Service（Amazon S3）や Hadoop Distributed File System（HDFS）などの一般的なリモートストレージ内のファイルデータを ApsaraDB for SelectDB のテーブルにマッピングできます。このようにして、ファイルデータを簡単に分析できます。

Amazon S3 TVF

Amazon S3 TVF である s3() を使用すると、リレーショナルデータベース内のファイルコンテンツにアクセスするのと同じ方法で、Amazon S3 と互換性のあるオブジェクトストレージシステム内のファイルコンテンツを読み取り、アクセスできます。 CVS、CSV_with_names、CVS_with_names_and_types、JSON、Parquet、および ORC ファイル形式がサポートされています。

構文

s3(
  "uri" = "..",
  "s3.access_key" = "...",
  "s3.secret_key" = "...",
  "s3.region" = "...",
  "format" = "csv",
  "keyn" = "valuen",
  ...
);

パラメーター

Amazon S3 TVF の各パラメーターは、"key" = "value" 形式のキーと値のペアです。次の表に、共通パラメーターを示します。

パラメーター	必須	有効な値	説明
uri	はい	文字列	Amazon S3 にアクセスするための Uniform Resource Identifier（URI）。
s3.access_key	はい	文字列	Amazon S3 へのアクセスに使用するアクセスキー ID。
s3.secret_key	はい	文字列	Amazon S3 へのアクセスに使用するシークレットアクセスキー。
s3.region	はい	文字列	Amazon S3 のリージョン。デフォルト値：`us-east-1`。
s3.session_token	いいえ説明一時セッション認証が有効になっている場合、このパラメーターは必須です。	文字列	Amazon S3 へのアクセスに使用する一時セッショントークン。
use_path_style	いいえ	true false	パススタイルを使用して Amazon S3 にアクセスするかどうかを指定します。デフォルトでは、Amazon S3 SDK は仮想ホストスタイルを使用します。ただし、一部のオブジェクトストレージシステムでは、仮想ホストスタイルが有効になっていないか、サポートされていない場合があります。この場合、`use_path_style` パラメーターを指定して、パススタイルを強制的に使用できます。たとえば、MinIO はデフォルトでパススタイルのみを許可します。 MinIO にアクセスするには、`use_path_style` パラメーターを true に設定します。デフォルト値：`false`。説明次の URI スキーマがサポートされています：`http://`、`https://`、および `s3://`。 `http://` または `https://` が使用されている場合、システムは `use_path_style` パラメーターの値に基づいて、パススタイルを使用して Amazon S3 にアクセスするかどうかを判断します。 `s3://` が使用されている場合、仮想ホストスタイルを使用して Amazon S3 にアクセスします。指定された URI が存在しないか、一致するファイルがすべて空の場合、Amazon S3 TVF は空の結果セットを返します。
format	はい	cvs csv_with_names csv_with_names_and_types json parquet orc	Amazon S3でアクセスするファイルの形式。
column_separator	いいえ	文字列	列の区切り文字。デフォルト値：`,`。
line_delimiter	いいえ	文字列	行の区切り文字。デフォルト値：`\n`。
compress_type	いいえ	`unknown` `plain` `gz` `lzo` `bz2` `lz4frame` `deflate`	ファイルの圧縮タイプ。デフォルト値：unknown。これは、URI のサフィックスに基づいて圧縮タイプが自動的に推測されることを示します。
read_json_by_line	いいえ	true false	JSON 形式のデータを行ごとに読み取るかどうかを指定します。デフォルト値：true。
num_as_string	いいえ	true false	数値を文字列として処理するかどうかを指定します。デフォルト値：false。
fuzzy_parse	いいえ	true false	JSON 形式のデータのインポート効率を上げるかどうかを指定します。デフォルト値：false。
jsonpaths	いいえ	文字列	JSON 形式のデータから抽出するフィールド。形式：`jsonpaths: [\"$.k2\", \"$.k1\"]`。
strip_outer_array	いいえ	true false	JSON 形式のデータを配列として表示するかどうかを指定します。各要素はデータの行と見なされます。デフォルト値：false。形式：`strip_outer_array: true`。
json_root	いいえ	文字列	JSON 形式のデータのルートノード。 ApsaraDB for SelectDB は、`json_root` パラメーターで指定されたルートノードの要素を抽出して解析します。デフォルトでは、このパラメーターは空のままです。形式：`json_root: $.RECORDS`。
path_partition_keys	いいえ	文字列	指定されたファイルパスに含まれるパーティションキー列の名前。たとえば、ファイルパスが /path/to/city=beijing/date="2023-07-09" の場合、このパラメーターを city,date に設定します。この場合、ApsaraDB for SelectDB はデータのインポート中にパスから対応する列名と列値を自動的に読み取ります。

例

Amazon S3 と互換性のあるオブジェクトストレージシステムの CSV ファイルを読み取ってアクセスします。

SELECT * FROM s3("uri" = "http://127.0.0.1:9312/test2/student1.csv",
                "s3.access_key"= "minioadmin",
                "s3.secret_key" = "minioadmin",
                "format" = "csv",
                "use_path_style" = "true") ORDER BY c1;

この TVF を DESC FUNCTION 関数と一緒に使用して、ファイルスキーマをクエリできます。

MySQL [(none)]> Desc FUNCTION s3("uri" = "http://127.0.0.1:9312/test2/student1.csv",
                 "s3.access_key"= "minioadmin",
                 "s3.secret_key" = "minioadmin",
                 "format" = "csv",
                 "use_path_style" = "true");

オブジェクトストレージサービス（OSS）にアクセスするには、仮想ホストスタイルを使用する必要があります。

SELECT * FROM s3(
    "uri" = "http://example-bucket.oss-cn-beijing.aliyuncs.com/your-folder/file.parquet",
    "s3.access_key"= "ak",
    "s3.secret_key" = "sk",
    "format" = "parquet",
    "use_path_style" = "false");

use_path_style パラメーターが true に設定されている場合、パススタイルを使用して Amazon S3 にアクセスします。

SELECT * FROM s3(
    "uri" = "https://endpoint/bucket/file/student.csv",
    "s3.access_key"= "ak",
    "s3.secret_key" = "sk",
    "format" = "csv",
    "use_path_style"="true");

use_path_style パラメーターが false に設定されている場合、仮想ホストスタイルを使用して Amazon S3 にアクセスします。

SELECT * FROM s3(
    "uri" = "https://bucket.endpoint/bucket/file/student.csv",
    "s3.access_key"= "ak",
    "s3.secret_key" = "sk",
    "format" = "csv",
    "use_path_style"="false");

HDFS TVF

HDFS TVF である hdfs() を使用すると、リレーショナルデータベース内のファイルコンテンツにアクセスするのと同じ方法で、HDFS 内のファイルコンテンツを読み取り、アクセスできます。 CVS、CSV_with_names、CVS_with_names_and_types、JSON、Parquet、および ORC ファイル形式がサポートされています。

構文

hdfs(
  "uri" = "..",
  "fs.defaultFS" = "...",
  "hadoop.username" = "...",
  "format" = "csv",
  "keyn" = "valuen" 
  ...
);

パラメーター

HDFS TVF の各パラメーターは、"key" = "value" 形式のキーと値のペアです。次の表に、共通パラメーターを示します。

パラメーター	必須	有効な値	説明
uri	はい	文字列	HDFS にアクセスするための URI。指定された URI が存在しないか、一致するファイルがすべて空の場合、HDFS TVF は空の結果セットを返します。
fs.defaultFS	はい	文字列	HDFS のホスト名とポート番号。
hadoop.username	はい	文字列	HDFS へのアクセスに使用するユーザー名。値を空の文字列にすることはできません。
hadoop.security.authentication	いいえ	Simple Kerberos	HDFS の認証方法。有効な値：Simple および Kerberos。
hadoop.kerberos.principal	いいえ	文字列	HDFS で Kerberos 認証が有効になっている場合の Kerberos プリンシパル。
hadoop.kerberos.keytab	いいえ	文字列	HDFS で Kerberos 認証が有効になっている場合の Kerberos キータブファイルのパス。
dfs.client.read.shortcircuit	いいえ	true false	ローカル HDFS ショートサーキットデータを読み取るかどうかを指定します。値は BOOLEAN 型です。
dfs.domain.socket.path	いいえ	文字列	DataNode とローカル HDFS クライアント間の通信用の UNIX ドメインソケットを指すパス。パスに文字列 "_PORT" を指定すると、文字列は DataNode の TCP ポートに置き換えられます。
dfs.nameservices	いいえ	文字列	サービスを提供するネームサービスの論理名。このパラメーターは、core-site.xml ファイルの dfs.nameservices フィールドに対応します。
dfs.ha.namenodes.your-nameservices	いいえ説明 Hadoop 高可用性（HA）デプロイメントが使用されている場合、このパラメーターは必須です。	文字列	NameNode の論理名。
dfs.namenode.rpc-address.your-nameservices.your-namenode	いいえ説明 Hadoop HA デプロイメントが使用されている場合、このパラメーターは必須です。	文字列	NameNode が listen する HTTP URL。
dfs.client.failover.proxy.provider.your-nameservices	いいえ説明 Hadoop HA デプロイメントが使用されている場合、このパラメーターは必須です。	文字列	使用可能な状態の NameNode へのクライアント接続のフェイルオーバープロキシプロバイダーの実装クラス。
read_json_by_line	いいえ	true false	JSON 形式のデータを行ごとに読み取るかどうかを指定します。デフォルト値：true。
num_as_string	いいえ	true false	数値を文字列として処理するかどうかを指定します。デフォルト値：false。
fuzzy_parse	いいえ	true false	JSON 形式のデータのインポート効率を上げるかどうかを指定します。デフォルト値：false。
jsonpaths	いいえ	文字列	JSON 形式のデータから抽出するフィールド。形式：`jsonpaths: [\"$.k2\", \"$.k1\"]`。
strip_outer_array	いいえ	true false	JSON 形式のデータを配列として表示するかどうかを指定します。各要素はデータの行と見なされます。デフォルト値：false。形式：`strip_outer_array: true`。
json_root	いいえ	文字列	JSON 形式のデータのルートノード。 ApsaraDB for SelectDB は、`json_root` パラメーターで指定されたルートノードの要素を抽出して解析します。デフォルトでは、このパラメーターは空のままです。形式：`json_root: $.RECORDS`。
trim_double_quotes	いいえ	true false	CSV ファイルの各フィールドの最も外側の二重引用符（"）をトリミングするかどうかを指定します。デフォルト値：false。
skip_lines	いいえ	[0-Integer.MaxValue]	値は INTEGER 型です。デフォルト値：0。CSV ファイルの最初の数行をスキップするかどうかを指定します。 format パラメーターが `csv_with_names` または `csv_with_names_and_types` に設定されている場合、このパラメーターは無効になります。
path_partition_keys	いいえ	文字列	指定されたファイルパスに含まれるパーティションキー列の名前。たとえば、ファイルパスが /path/to/city=beijing/date="2023-07-09" の場合、このパラメーターを city,date に設定します。この場合、ApsaraDB for SelectDB はデータのインポート中にパスから対応する列名と列値を自動的に読み取ります。

例

HDFS 内の CSV ファイルを読み取ってアクセスします。

MySQL [(none)]> SELECT * FROM hdfs(
            "uri" = "hdfs://127.0.0.1:842/user/doris/csv_format_test/student.csv",
            "fs.defaultFS" = "hdfs://127.0.0.1:8424",
            "hadoop.username" = "doris",
            "format" = "csv");
            
-- サンプルレスポンス
+------+---------+------+
| c1   | c2      | c3   |
+------+---------+------+
| 1    | alice   | 18   |
| 2    | bob     | 20   |
| 3    | jack    | 24   |
| 4    | jackson | 19   |
| 5    | liming  | 18   |
+------+---------+------+

高可用性（HA）モードで HDFS 内の CSV ファイルを読み取ってアクセスします。

MySQL [(none)]> SELECT * FROM hdfs(
            "uri" = "hdfs://127.0.0.1:842/user/doris/csv_format_test/student.csv",
            "fs.defaultFS" = "hdfs://127.0.0.1:8424",
            "hadoop.username" = "doris",
            "format" = "csv",
            "dfs.nameservices" = "my_hdfs",
            "dfs.ha.namenodes.my_hdfs" = "nn1,nn2",
            "dfs.namenode.rpc-address.my_hdfs.nn1" = "nanmenode01:8020",
            "dfs.namenode.rpc-address.my_hdfs.nn2" = "nanmenode02:8020",
            "dfs.client.failover.proxy.provider.my_hdfs" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider");

-- サンプルレスポンス
+------+---------+------+
| c1   | c2      | c3   |
+------+---------+------+
| 1    | alice   | 18   |
| 2    | bob     | 20   |
| 3    | jack    | 24   |
| 4    | jackson | 19   |
| 5    | liming  | 18   |
+------+---------+------+

この TVF を DESC FUNCTION 関数と一緒に使用して、ファイルスキーマをクエリできます。

MySQL [(none)]> DECS FUNCTION hdfs(
            "uri" = "hdfs://127.0.0.1:8424/user/doris/csv_format_test/student_with_names.csv",
            "fs.defaultFS" = "hdfs://127.0.0.1:8424",
            "hadoop.username" = "doris",
            "format" = "csv_with_names");

使用方法

このセクションでは、TVF を使用してファイルデータを分析する方法について説明します。この例では、Amazon S3 TVF を使用します。

ファイルの列タイプを自動的に推測する

ApsaraDB for SelectDB は、Parquet、ORC、CSV、および JSON 形式のファイルの列タイプを分析および推測できます。

> DESC FUNCTION s3 (
    "URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
    "s3.access_key"= "ak",
    "s3.secret_key" = "sk",
    "format" = "parquet",
    "use_path_style"="true"
);

-- サンプルレスポンス
+---------------+--------------+------+-------+---------+-------+
| Field         | Type         | Null | Key   | Default | Extra |
+---------------+--------------+------+-------+---------+-------+
| p_partkey     | INT          | Yes  | false | NULL    | NONE  |
| p_name        | TEXT         | Yes  | false | NULL    | NONE  |
| p_mfgr        | TEXT         | Yes  | false | NULL    | NONE  |
| p_brand       | TEXT         | Yes  | false | NULL    | NONE  |
| p_type        | TEXT         | Yes  | false | NULL    | NONE  |
| p_size        | INT          | Yes  | false | NULL    | NONE  |
| p_container   | TEXT         | Yes  | false | NULL    | NONE  |
| p_retailprice | DECIMAL(9,0) | Yes  | false | NULL    | NONE  |
| p_comment     | TEXT         | Yes  | false | NULL    | NONE  |
+---------------+--------------+------+-------+---------+-------+

サンプルレスポンスはApsaraDB for SelectDB がファイルのメタデータに基づいて Parquet ファイルの列タイプを自動的に推測できることを示しています。

CSV ファイルのスキーマを読み取る

デフォルトでは、ApsaraDB for SelectDB は CSV ファイルのすべての列のタイプを STRING として処理します。 csv_schema パラメーターを使用して、CSV ファイルの列名と列タイプを個別に指定できます。 ApsaraDB for SelectDB は、指定された列タイプに基づいて CSV ファイルのスキーマを読み取ります。形式：

name1:type1;name2:type2;...

ApsaraDB for SelectDB は、タイプが一致しない列または欠落している列に対して NULL を返します。たとえば、列のタイプが STRING ですが、列タイプを INT として指定した場合、または 4 つの列を含むファイルに 5 つの列を指定した場合などです。次の表に、サポートされている列タイプを示します。

列タイプ	マッピングタイプ
tinyint	tinyint
smallint	smallint
int	int
bigint	bigint
largeint	largeint
float	float
double	double
decimal(p,s)	decimalv3(p,s)
date	datev2
datetime	datetimev2
char	string
varchar	string
string	string
boolean	boolean

例：

s3 (
    "URI" = "https://bucket1/inventory.dat",
    "s3.access_key"= "ak",
    "s3.secret_key" = "sk",
    "format" = "csv",
    "column_separator" = "|",
    "csv_schema" = "k1:int;k2:int;k3:int;k4:decimal(38,10)",
    "use_path_style"="true"
)

データのクエリと分析

TVF を SQL ステートメントと一緒に使用して、ファイルをクエリおよび分析できます。

SELECT * FROM s3(
    "URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
    "s3.access_key"= "ak",
    "s3.secret_key" = "sk",
    "format" = "parquet",
    "use_path_style"="true")
LIMIT 5;

-- サンプルレスポンス
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
| p_partkey | p_name                                   | p_mfgr         | p_brand  | p_type                  | p_size | p_container | p_retailprice | p_comment           |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
|         1 | goldenrod lavender spring chocolate lace | Manufacturer#1 | Brand#13 | PROMO BURNISHED COPPER  |      7 | JUMBO PKG   |           901 | ly. slyly ironi     |
|         2 | blush thistle blue yellow saddle         | Manufacturer#1 | Brand#13 | LARGE BRUSHED BRASS     |      1 | LG CASE     |           902 | lar accounts amo    |
|         3 | spring green yellow purple cornsilk      | Manufacturer#4 | Brand#42 | STANDARD POLISHED BRASS |     21 | WRAP CASE   |           903 | egular deposits hag |
|         4 | cornflower chocolate smoke green pink    | Manufacturer#3 | Brand#34 | SMALL PLATED BRASS      |     14 | MED DRUM    |           904 | p furiously r       |
|         5 | forest brown coral puff cream            | Manufacturer#3 | Brand#32 | STANDARD POLISHED TIN   |     15 | SM PKG      |           905 |  wake carefully     |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+

TVF は、SQL ステートメントでテーブルを配置する場所であればどこにでも配置できます。たとえば、共通テーブル式（CTE）の WITH 句または FROM 句などです。ファイルを標準テーブルとして分析できます。

CREATE VIEW ステートメントを使用して、TVF の論理ビューを作成することもできます。これにより、他のビューに対する操作を実行するのと同じ方法で、この TVF にアクセスし、その権限を管理できます。他のユーザーがこの TVF にアクセスできるようにすることもできます。

CREATE VIEW v1 AS 
SELECT * FROM s3(
    "URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
    "s3.access_key"= "ak",
    "s3.secret_key" = "sk",
    "format" = "parquet",
    "use_path_style"="true");

DESC v1;

SELECT * FROM v1;

GRANT SELECT_PRIV ON db1.v1 TO user1;

データのインポート

TVF を INSERT INTO SELECT ステートメントと一緒に使用して、ファイルのデータを ApsaraDB for SelectDB のテーブルにインポートし、分析を高速化できます。

-- 1. ApsaraDB for SelectDB に内部テーブルを作成します。
CREATE TABLE IF NOT EXISTS test_table
(
    id int,
    name varchar(50),
    age int
)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES("replication_num" = "1");

-- 2. Amazon S3 TVF を使用してデータを挿入します。
INSERT INTO test_table (id,name,age)
SELECT cast(id as INT) as id, name, cast (age as INT) as age
FROM s3(
    "uri" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
    "s3.access_key"= "ak",
    "s3.secret_key" = "sk",
    "format" = "parquet",
    "use_path_style" = "true");

使用上の注意

Amazon S3 または HDFS TVF で指定された URI に一致するファイルがない場合、または一致するファイルがすべて空の場合、Amazon S3 または HDFS TVF は空の結果セットを返します。この場合、DESC FUNCTION ステートメントを実行して TVF で指定されたファイルのスキーマをクエリすると、ダミー列 __dummy_col が返されます。これは無視できます。
TVF で指定されたファイル形式が CSV で、読み取られたファイルは空ではないが、ファイルの最初の行が空の場合、The first line is empty, can not parse column numbers エラーメッセージが報告されます。