使用SQL管理外部项目

构建湖仓一体时,您需要创建外部项目(External Project)用于关联Hadoop集群,或关联DLF及OSS。本文为您介绍如何使用SQL方式对不同场景中已创建的外部项目、及其中的表进行管理,例如查询或更新表数据等操作。

背景信息

您在通过DataWorks控制台的数据湖集成界面创建External Project后,可以使用SQL方式操作External Project,场景如下所示。

基于数据湖构建DLF和对象存储OSS的外部项目操作

在创建External Project后,您可以通过MaxCompute客户端进入创建的External Project空间,在关联的MaxCompute项目下,对External Project的表或数据进行操作。

与常规MaxCompute SQL不同的是,External Project表需要以external_project_name.table_name格式引用。目前暂不支持在外部项目中创建表。

  • 向表中插入数据。

    • 向非分区表插入数据。

      --进入关联的MaxCompute项目(非外部项目)。
      use doc_test_prod;
      
      --向非分区表中插入数据。
      insert into table ext_mcdlf_ddl.ddl_test values(1,"ddl_test_1");
    • 向分区表中插入数据。

      --进入关联的MaxCompute项目。
      use doc_test_prod;
      
      --向表中插入数据。
      insert overwrite table ext_mcdlf_ddl.range_ptstring_ptint partition (pt1 = 'ds1', pt2=2) values (4, 'val4'), (5, 'val5');
  • 查看External Project中的表信息。

    --进入关联的MaxCompute项目。
    use doc_test_prod;
    
    --查看External Project下的表。
    show tables in doc_test_prod;
    
    --查看ext_mcdlf_ddl.ddl_test表结构。
    desc extended ext_mcdlf_ddl.ddl_test;
  • 查询External Project中的表数据。

    --进入关联的MaxCompute项目。
    use doc_test_prod;
    
    --查询非分区表ext_mcdlf_ddl.ddl_test数据。
    select * from ext_mcdlf_ddl.ddl_test limit 10;
    
    --查询分区表ext_mcdlf_ddl.range_ptstring_ptint的数据。
    select * from ext_mcdlf_ddl.range_ptstring_ptint where pt1='ds1';
说明
  • 对于某些特殊场景,需要在执行 SQL 时添加参数,例如:

    -- 如果用户的orc 版本大于 orc135。 则应设置如下参数:
    set odps.ext.oss.orc.native=true;
    
    -- 打开Hive兼容模式后,MaxCompute才支持Hive指定的各种语法
    set odps.sql.hive.compatible=true;
    
    -- 通过OSS分片上传功能将数据写入OSS
    set odps.sql.unstructured.oss.commit.mode=true;
    -- 支持通过如下语句全project 默认打开该功能
    setproject odps.sql.unstructured.oss.commit.mode=true;
  • 常规MaxCompute SQL语法,请参见表操作插入或覆写数据(INSERT INTO | INSERT OVERWRITE)

  • 您可以在odps_config.ini文件中增加set配置内容,避免每次手动输入。

  • 更多分片上传功能信息,请参见分片上传

基于Hadoop外部数据源的外部项目操作

按照如下命令,查询Hadoop中Hive表数据。

--执行作业需要在普通项目下,不能是外部项目。
use <main_project_name>;

--访问阿里云E-MapReduce表必须设置,访问MaxCompute内部表不需要。
set odps.sql.hive.compatible=true;

--指定写出到HDFS的用户名(非必须)。
set odps.external.hadoop.user=<hadoop_name>;

--读取Hive表数据。
select * from <my_hive_table> limit 10;
说明
  • 目前无法对关联的External Project执行DDL类的SQL语句(add partition和drop partition除外)。DDL SQL语句,详情请参见SQL概述

  • 您可以在odps_config.ini文件中增加set配置set.odps.sql.hive.compatible=trueset.odps.external.hadoop.user=<hadoop_name>,避免每次手动输入。

创建与外部数据源中表结构相同的表

如果需要创建与外部数据源中表结构相同的表,可以使用create table...like命令复制外部数据源中表的结构,命令示例如下。

说明

create table...like命令详细说明请参见表操作

-- 创建表
create table from_exe_tbl like external_project.testtbl;
-- 此时from_exe_tbl的表结构与 external_project.testtbl 完全相同。
-- 除生命周期属性外,列名、列注释以及表注释等均相同。但external_project.testtbl中的数据不会被复制到from_exe_tbl表中。
-- 查询表
SELECT * from from_exe_tbl;
-- 查看表结构
desc from_exe_tbl;

相关文档

如果您想给外部项目或其中的表进行授权,可参见对于外部项目的用户以及权限操作