StarRocks之查询数据湖(外部数据)

发表于 更新于 阅读次数： 阅读次数：

背景

验证一轮查询外部数据湖

Catalog

  • JDBC ✅已验证

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE EXTERNAL CATALOG jdbc1
    PROPERTIES
    (
        "type"="jdbc",
        "user"="root",
        "password"="XXX",
        "jdbc_uri"="jdbc:mysql://172.16.0.9:3306",
        "driver_url"="https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar",
        "driver_class"="com.mysql.cj.jdbc.Driver"
    );

  • es

    • 存算一体+自建es ✅已验证
    • 基于腾讯云cos存算分离 + 腾讯云es产品 ✅已验证

      存在问题，当使用腾讯云默认索引开了动态模板，starrocks解析mapping有bug，会导致show tables出不来

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      CREATE EXTERNAL CATALOG es_test
      COMMENT 'es'
      PROPERTIES
      (
          "type" = "es",
          "es.type" = "_doc",
          "hosts" = "http://172.16.0.8:9200",
          "es.net.ssl" = "false",
          "user" = "elastic",
          "password" = "xxx",
          "es.nodes.wan.only" = "false"
      );

EXTERNAL TABLE

  • COS文件外部表 ❌验证失败
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    CREATE EXTERNAL TABLE user_behavior_fet
    (
        UserID int, 
        ItemID int,
        CategoryID int,
        BehaviorType string
    ) 
    ENGINE=file
    PROPERTIES 
    (
        "path" = "s3://race-1301288126/input/user_behavior_ten_million_rows.parquet", 
        "format" = "parquet",
        "aws.s3.enable_ssl" = "false",
        "aws.s3.enable_path_style_access" = "true",
        "aws.s3.endpoint" = "cos.ap-guangzhou.myqcloud.com",
        "aws.s3.access_key" = "AKIDAFICxeJwnror5OarCRYXQMF1f5X7lJtO",
        "aws.s3.secret_key" = "XXX"
    );