ApsaraDB for SelectDB：檔案分析

S3表函數

S3表函數可以讓您像訪問關係型資料庫一樣，讀取並訪問S3相容的Object Storage Service上的檔案內容。目前支援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",
  ...
);

參數說明

S3 TVF中的每一個參數都是一個"key"="value"索引值對。常用參數如下：

使用樣本

1. 讀取並訪問S3相容的Object Storage Service上的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;  

   配合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");

2. OSS的場合，需採用Virtual-hosted Style方式訪問，樣本如下。

   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");

3. 設定了"use_path_style"="true"的場合，將採用path style方式訪問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");

4. 設定了"use_path_style"="false"的場合，將採用virtual-hosted style方式訪問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表函數

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"索引值對。常用參數如下：

使用樣本

讀取並訪問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   |
+------+---------+------+

配合DECS 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");

使用方法

本章節將通過S3 TVF舉例說明如何進行檔案分析。

自動推斷檔案列類型​

目前支援對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  |
+---------------+--------------+------+-------+---------+-------+

可以看到，對於Parquet檔案，SelectDB會根據檔案內的元資訊自動推斷列類型。

CSV Schema

預設情況下，CSV格式檔案的所有列類型均會被SelectDB視為String。您可以通過csv_schema屬性單獨指定列名和列類型。SelectDB會使用指定的列類型進行檔案讀取。格式如下：

name1:type1;name2:type2;...

對于格式不匹配的列（例如檔案中實際為字串，您定義為int），或缺失列（比如檔案中實際存在4列，您定義了5列），則這些列將返回null。當前支援的列類型為：

樣本如下。

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"
)

查詢分析

您可以使用任意的SQL語句，對TVF進行分析，樣本如下。

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中Table能出現的任意位置，如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;

資料匯入​

配合INSERT INTO SELECT文法，您可以方便將檔案匯入到SelectDB表中進行更快速的分析，樣本如下。

-- 1. 建立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. 使用S3 Table Value Function插入資料
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");

注意事項

• 如果S3或HDFS TVF指定的URI匹配不到檔案，或者匹配到的所有檔案都是空檔案，那麼S3或HDFS TVF將會返回空結果集。在這種情況下使用DESC FUNCTION查看這個檔案的Schema，會得到一列虛假的列__dummy_col，可忽略這一列。

• 如果指定TVF的format為CSV，所讀檔案不為空白檔案但檔案第一行為空白，則會產生報誤The first line is empty, can not parse column numbers。