File Analysis

SinceVersion 1.2.0

With the Table Value Function feature, Doris is able to query files in object storage or HDFS as simply as querying Tables. In addition, it supports automatic column type inference.

Usage

For more usage details, please see the documentation:

  • S3: supports file analysis on object storage compatible with S3
  • HDFS: supports file analysis on HDFS

The followings illustrate how file analysis is conducted with the example of S3 Table Value Function.

Automatic Column Type Inference

  1. MySQL [(none)]> DESC FUNCTION s3(
  2.     "URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
  3.     "ACCESS_KEY"= "minioadmin",
  4.     "SECRET_KEY" = "minioadmin",
  5.     "Format" = "parquet",
  6.     "use_path_style"="true");
  7. +---------------+--------------+------+-------+---------+-------+
  8. | Field         | Type         | Null | Key   | Default | Extra |
  9. +---------------+--------------+------+-------+---------+-------+
  10. | p_partkey     | INT          | Yes  | false | NULL    | NONE  |
  11. | p_name        | TEXT         | Yes  | false | NULL    | NONE  |
  12. | p_mfgr        | TEXT         | Yes  | false | NULL    | NONE  |
  13. | p_brand       | TEXT         | Yes  | false | NULL    | NONE  |
  14. | p_type        | TEXT         | Yes  | false | NULL    | NONE  |
  15. | p_size        | INT          | Yes  | false | NULL    | NONE  |
  16. | p_container   | TEXT         | Yes  | false | NULL    | NONE  |
  17. | p_retailprice | DECIMAL(9,0) | Yes  | false | NULL    | NONE  |
  18. | p_comment     | TEXT         | Yes  | false | NULL    | NONE  |
  19. +---------------+--------------+------+-------+---------+-------+

An S3 Table Value Function is defined as follows:

  1. s3(
  2.     "URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
  3.     "ACCESS_KEY"= "minioadmin",
  4.     "SECRET_KEY" = "minioadmin",
  5.     "Format" = "parquet",
  6.     "use_path_style"="true")

It specifies the file path, connection, and authentication.

After defining, you can view the schema of this file using the DESC FUNCTION statement.

As can be seen, Doris is able to automatically infer column types based on the metadata of the Parquet file.

Besides Parquet, Doris supports analysis and auto column type inference of ORC, CSV, and Json files.

Query and Analysis

You can conduct queries and analysis on this Parquet file using any SQL statements:

  1. SELECT * FROM s3(
  2.     "URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
  3.     "ACCESS_KEY"= "minioadmin",
  4.     "SECRET_KEY" = "minioadmin",
  5.     "Format" = "parquet",
  6.     "use_path_style"="true")
  7. LIMIT 5;
  8. +-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
  9. | p_partkey | p_name                                   | p_mfgr         | p_brand  | p_type                  | p_size | p_container | p_retailprice | p_comment           |
  10. +-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
  11. |         1 | goldenrod lavender spring chocolate lace | Manufacturer#1 | Brand#13 | PROMO BURNISHED COPPER  |      7 | JUMBO PKG   |           901 | ly. slyly ironi     |
  12. |         2 | blush thistle blue yellow saddle         | Manufacturer#1 | Brand#13 | LARGE BRUSHED BRASS     |      1 | LG CASE     |           902 | lar accounts amo    |
  13. |         3 | spring green yellow purple cornsilk      | Manufacturer#4 | Brand#42 | STANDARD POLISHED BRASS |     21 | WRAP CASE   |           903 | egular deposits hag |
  14. |         4 | cornflower chocolate smoke green pink    | Manufacturer#3 | Brand#34 | SMALL PLATED BRASS      |     14 | MED DRUM    |           904 | p furiously r       |
  15. |         5 | forest brown coral puff cream            | Manufacturer#3 | Brand#32 | STANDARD POLISHED TIN   |     15 | SM PKG      |           905 |  wake carefully     |
  16. +-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+

You can put the Table Value Function anywhere that you used to put Table in the SQL, such as in the WITH or FROM clause in CTE. In this way, you can treat the file as a normal table and conduct analysis conveniently.

Data Ingestion

Users can ingest files into Doris tables via INSERT INTO SELECT for faster file analysis:

  1. // 1. Create Doris internal table
  2. CREATE TABLE IF NOT EXISTS test_table
  3. (
  4.     id int,
  5.     name varchar(50),
  6.     age int
  7. )
  8. DISTRIBUTED BY HASH(id) BUCKETS 4
  9. PROPERTIES("replication_num" = "1");
  11. // 2. Insert data using S3 Table Value Function
  12. INSERT INTO test_table (id,name,age)
  13. SELECT cast(id as INT) as id, name, cast (age as INT) as age
  14. FROM s3(
  15.     "uri" = "${uri}",
  16.     "ACCESS_KEY"= "${ak}",
  17.     "SECRET_KEY" = "${sk}",
  18.     "format" = "${format}",
  19.     "strip_outer_array" = "true",
  20.     "read_json_by_line" = "true",
  21.     "use_path_style" = "true");