ApsaraDB for SelectDB：Analisis File

Amazon S3 TVF

Amazon S3 TVF, s3(), memungkinkan Anda membaca dan mengakses isi file dalam sistem penyimpanan objek yang kompatibel dengan Amazon S3 sebagaimana Anda mengakses isi file dalam database relasional. Format file yang didukung meliputi CSV, CSV_with_names, CSV_with_names_and_types, JSON, Parquet, dan ORC.

Sintaksis

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

Parameter

Setiap parameter dalam Amazon S3 TVF adalah pasangan kunci-nilai dalam format "key" = "value". Tabel berikut menjelaskan parameter umum.

Contoh

1. Membaca dan mengakses file CSV dalam sistem penyimpanan objek yang kompatibel dengan Amazon S3.

   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;  

   Anda dapat menggunakan TVF ini bersama dengan fungsi DESC FUNCTION untuk menanyakan skema file.

   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. Gunakan gaya virtual-hosted untuk mengakses Object Storage Service (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");

3. Jika parameter use_path_style diatur ke true, gaya path digunakan untuk mengakses 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");

4. Jika parameter use_path_style diatur ke false, gaya virtual-hosted digunakan untuk mengakses 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(), memungkinkan Anda membaca dan mengakses isi file dalam HDFS sebagaimana Anda mengakses isi file dalam database relasional. Format file yang didukung meliputi CSV, CSV_with_names, CSV_with_names_and_types, JSON, Parquet, dan ORC.

Sintaksis

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

Parameter

Setiap parameter dalam HDFS TVF adalah pasangan kunci-nilai dalam format "key" = "value". Tabel berikut menjelaskan parameter umum.

Contoh

Membaca dan mengakses file CSV dalam HDFS.

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");
            
-- Contoh tanggapan   
+------+---------+------+
| c1   | c2      | c3   |
+------+---------+------+
| 1    | alice   | 18   |
| 2    | bob     | 20   |
| 3    | jack    | 24   |
| 4    | jackson | 19   |
| 5    | liming  | 18   |
+------+---------+------+

Membaca dan mengakses file CSV dalam HDFS dalam mode high availability (HA).

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

-- Contoh tanggapan
+------+---------+------+
| c1   | c2      | c3   |
+------+---------+------+
| 1    | alice   | 18   |
| 2    | bob     | 20   |
| 3    | jack    | 24   |
| 4    | jackson | 19   |
| 5    | liming  | 18   |
+------+---------+------+

Anda dapat menggunakan TVF ini bersama dengan fungsi DESC FUNCTION untuk menanyakan skema file.

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

Penggunaan

Bagian ini menjelaskan cara menggunakan TVF untuk menganalisis data file. Dalam contoh ini, Amazon S3 TVF digunakan.

Otomatis menyimpulkan tipe kolom file

ApsaraDB for SelectDB dapat menganalisis dan menyimpulkan tipe kolom file dalam format Parquet, ORC, CSV, dan 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"
);

-- Contoh tanggapan
+---------------+--------------+------+-------+---------+-------+
| 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  |
+---------------+--------------+------+-------+---------+-------+

Contoh tanggapan menunjukkan bahwa ApsaraDB for SelectDB dapat secara otomatis menyimpulkan tipe kolom file Parquet berdasarkan metadata file.

Membaca skema file CSV

Secara default, ApsaraDB for SelectDB memproses tipe semua kolom dalam file CSV sebagai STRING. Anda dapat menggunakan parameter csv_schema untuk menentukan nama kolom dan tipe kolom file CSV secara terpisah. ApsaraDB for SelectDB membaca skema file CSV berdasarkan tipe kolom yang ditentukan. Format:

name1:type1;name2:type2;...

ApsaraDB for SelectDB mengembalikan NULL untuk kolom dengan tipe yang tidak cocok atau kolom yang hilang. Misalnya, tipe kolom adalah STRING tetapi Anda menentukan tipe kolom sebagai INT, atau Anda menentukan lima kolom untuk file yang berisi empat kolom. Tabel berikut menjelaskan tipe kolom yang didukung.

Contoh:

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

Kueri dan analisis data

Anda dapat menggunakan TVF bersama dengan pernyataan SQL untuk menanyakan dan menganalisis file.

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;

-- Contoh tanggapan
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
| 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     |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+

Anda dapat menempatkan TVF di mana saja Anda menempatkan tabel dalam pernyataan SQL, seperti dalam klausa WITH atau FROM ekspresi tabel umum (CTE). Anda dapat menganalisis file sebagai tabel standar.

Anda juga dapat membuat tampilan logis untuk TVF dengan menggunakan pernyataan CREATE VIEW. Ini memungkinkan Anda mengakses TVF ini dan mengelola izinnya sebagaimana Anda melakukan operasi pada tampilan lainnya. Anda juga dapat mengizinkan pengguna lain untuk mengakses TVF ini.

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;

Impor data

Anda dapat menggunakan TVF bersama dengan pernyataan INSERT INTO SELECT untuk mengimpor data file ke tabel di ApsaraDB for SelectDB guna analisis yang lebih cepat.

-- 1. Buat tabel internal di 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. Masukkan data menggunakan 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");

Catatan penggunaan

• Jika tidak ada file yang cocok untuk URI yang ditentukan oleh Amazon S3 atau HDFS TVF, atau jika semua file yang cocok kosong, Amazon S3 atau HDFS TVF mengembalikan set hasil kosong. Dalam hal ini, jika Anda menjalankan pernyataan DESC FUNCTION untuk menanyakan skema file yang ditentukan oleh TVF, kolom dummy __dummy_col dikembalikan, yang dapat diabaikan.

• Jika format file yang ditentukan oleh TVF adalah CSV dan file yang dibaca tidak kosong tetapi baris pertama file kosong, pesan kesalahan The first line is empty, can not parse column numbers dilaporkan.