CREATE-EXTERNAL-TABLE

Name

CREATE EXTERNAL TABLE

Description

此语句用来创建外部表，具体语法参阅 CREATE TABLE。

主要通过 ENGINE 类型来标识是哪种类型的外部表，目前可选 MYSQL、BROKER、HIVE、ICEBERG 、HUDI

  1. 如果是 mysql，则需要在 properties 提供以下信息：

    1. PROPERTIES (
    2.     "host" = "mysql_server_host",
    3.     "port" = "mysql_server_port",
    4.     "user" = "your_user_name",
    5.     "password" = "your_password",
    6.     "database" = "database_name",
    7.     "table" = "table_name"
    8. )

    以及一个可选属性”charset”，可以用来设置mysql连接的字符集, 默认值是”utf8”。如有需要,你可以设置为另外一个字符集”utf8mb4”。

    注意：

    • “table” 条目中的 “table_name” 是 mysql 中的真实表名。而 CREATE TABLE 语句中的 table_name 是该 mysql 表在 Doris 中的名字，可以不同。

    • 在 Doris 创建 mysql 表的目的是可以通过 Doris 访问 mysql 数据库。而 Doris 本身并不维护、存储任何 mysql 数据。

  2. 如果是 broker，表示表的访问需要通过指定的broker, 需要在 properties 提供以下信息：

    1. PROPERTIES (
    2.     "broker_name" = "broker_name",
    3.     "path" = "file_path1[,file_path2]",
    4.     "column_separator" = "value_separator"
    5.     "line_delimiter" = "value_delimiter"
    6. )

    另外还需要提供Broker需要的Property信息，通过BROKER PROPERTIES来传递，例如HDFS需要传入

    1. BROKER PROPERTIES(
    2.   "username" = "name",
    3.   "password" = "password"
    4. )

    这个根据不同的Broker类型，需要传入的内容也不相同

    注意：

    • “path” 中如果有多个文件，用逗号[,]分割。如果文件名中包含逗号，那么使用 %2c 来替代。如果文件名中包含 %，使用 %25 代替
    • 现在文件内容格式支持CSV，支持GZ，BZ2，LZ4，LZO(LZOP) 压缩格式。

  3. 如果是 hive，则需要在 properties 提供以下信息：

    1. PROPERTIES (
    2.     "database" = "hive_db_name",
    3.     "table" = "hive_table_name",
    4.     "hive.metastore.uris" = "thrift://127.0.0.1:9083"
    5. )

    其中 database 是 hive 表对应的库名字，table 是 hive 表的名字，hive.metastore.uris 是 hive metastore 服务地址。

  4. 如果是 iceberg，则需要在 properties 中提供以下信息：

    1. PROPERTIES (
    2.     "iceberg.database" = "iceberg_db_name",
    3.     "iceberg.table" = "iceberg_table_name",
    4.     "iceberg.hive.metastore.uris" = "thrift://127.0.0.1:9083",
    5.     "iceberg.catalog.type" = "HIVE_CATALOG"
    6. )

    其中 database 是 Iceberg 对应的库名； table 是 Iceberg 中对应的表名； hive.metastore.uris 是 hive metastore 服务地址； catalog.type 默认为 HIVE_CATALOG。当前仅支持 HIVE_CATALOG，后续会支持更多 Iceberg catalog 类型。

  5. 如果是 hudi，则需要在 properties 中提供以下信息：

    1. PROPERTIES (
    2. "hudi.database" = "hudi_db_in_hive_metastore",
    3. "hudi.table" = "hudi_table_in_hive_metastore",
    4. "hudi.hive.metastore.uris" = "thrift://127.0.0.1:9083"
    5. )

    其中 hudi.database 是 hive 表对应的库名字，hudi.table 是 hive 表的名字，hive.metastore.uris 是 hive metastore 服务地址。

Example

  1. 创建MYSQL外部表

    直接通过外表信息创建mysql表

    1. CREATE EXTERNAL TABLE example_db.table_mysql
    2. (
    3.     k1 DATE,
    4.     k2 INT,
    5.     k3 SMALLINT,
    6.     k4 VARCHAR(2048),
    7.     k5 DATETIME
    8. )
    9. ENGINE=mysql
    10. PROPERTIES
    11. (
    12.     "host" = "127.0.0.1",
    13.     "port" = "8239",
    14.     "user" = "mysql_user",
    15.     "password" = "mysql_passwd",
    16.     "database" = "mysql_db_test",
    17. "table" = "mysql_table_test",
    18. "charset" = "utf8mb4"
    19. )

    通过External Catalog Resource创建mysql表

    1. # 先创建Resource
    2. CREATE EXTERNAL RESOURCE "mysql_resource" 
    3. PROPERTIES
    4. (
    5.   "type" = "odbc_catalog",
    6.   "user" = "mysql_user",
    7.   "password" = "mysql_passwd",
    8.   "host" = "127.0.0.1",
    9.    "port" = "8239"          
    10. );
    12. # 再通过Resource创建mysql外部表
    13. CREATE EXTERNAL TABLE example_db.table_mysql
    14. (
    15.     k1 DATE,
    16.     k2 INT,
    17.     k3 SMALLINT,
    18.     k4 VARCHAR(2048),
    19.     k5 DATETIME
    20. )
    21. ENGINE=mysql
    22. PROPERTIES
    23. (
    24.     "odbc_catalog_resource" = "mysql_resource",
    25.     "database" = "mysql_db_test",
    26.     "table" = "mysql_table_test"
    27. )

  2. 创建一个数据文件存储在HDFS上的 broker 外部表, 数据使用 “|” 分割，”\n” 换行

    1. CREATE EXTERNAL TABLE example_db.table_broker (
    2.     k1 DATE,
    3.     k2 INT,
    4.     k3 SMALLINT,
    5.     k4 VARCHAR(2048),
    6.     k5 DATETIME
    7. )
    8. ENGINE=broker
    9. PROPERTIES (
    10.     "broker_name" = "hdfs",
    11.     "path" = "hdfs://hdfs_host:hdfs_port/data1,hdfs://hdfs_host:hdfs_port/data2,hdfs://hdfs_host:hdfs_port/data3%2c4",
    12.     "column_separator" = "|",
    13.     "line_delimiter" = "\n"
    14. )
    15. BROKER PROPERTIES (
    16.     "username" = "hdfs_user",
    17.     "password" = "hdfs_password"
    18. )

  3. 创建一个hive外部表

    1. CREATE TABLE example_db.table_hive
    2. (
    3.   k1 TINYINT,
    4.   k2 VARCHAR(50),
    5.   v INT
    6. )
    7. ENGINE=hive
    8. PROPERTIES
    9. (
    10.   "database" = "hive_db_name",
    11.   "table" = "hive_table_name",
    12.   "hive.metastore.uris" = "thrift://127.0.0.1:9083"
    13. );

  4. 创建一个 Iceberg 外表

    1. CREATE TABLE example_db.t_iceberg 
    2. ENGINE=ICEBERG
    3. PROPERTIES (
    4.     "iceberg.database" = "iceberg_db",
    5.     "iceberg.table" = "iceberg_table",
    6.     "iceberg.hive.metastore.uris"  =  "thrift://127.0.0.1:9083",
    7.     "iceberg.catalog.type"  =  "HIVE_CATALOG"
    8. );

  5. 创建一个 Hudi 外表

    创建时不指定schema(推荐)

    1. CREATE TABLE example_db.t_hudi
    2. ENGINE=HUDI
    3. PROPERTIES (
    4. "hudi.database" = "hudi_db_in_hive_metastore",
    5. "hudi.table" = "hudi_table_in_hive_metastore",
    6. "hudi.hive.metastore.uris" = "thrift://127.0.0.1:9083"
    7. );

    创建时指定schema

    1. CREATE TABLE example_db.t_hudi (
    2.    `id` int NOT NULL COMMENT "id number",
    3.    `name` varchar(10) NOT NULL COMMENT "user name"
    4. )
    5. ENGINE=HUDI
    6. PROPERTIES (
    7. "hudi.database" = "hudi_db_in_hive_metastore",
    8. "hudi.table" = "hudi_table_in_hive_metastore",
    9. "hudi.hive.metastore.uris" = "thrift://127.0.0.1:9083"
    10. );

Keywords

  1. CREATE, EXTERNAL, TABLE

Best Practice