本文为您介绍在构建湖仓一体时，如何使用SQL方式执行外部项目（External Project）管理日常操作。

背景信息

您在通过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;

--查看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：更多分片上传功能信息，请参见.NET分片上传。
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=true和set.odps.external.hadoop.user=<hadoop_name>，避免每次手动输入。

对于外部项目的用户以及权限操作

当External Project创建完毕之后，其中的表的所有者归属于创建External Project的账号。如果您需要授权其他用户外部表项目操作权限，请按照如下命令进行授权。
--切换为External project
use <my_external_project>;

--如果使用RAM用户，需要在External Project中先打开RAM支持
add accountprovider ram;

-- 如果是基于Hadoop外部数据源的外部项目，需要打开Hive兼容模式。
set odps.sql.hive.compatible=true;

--添加新用户。
add user <阿里云账号>;

--赋予show tables权限。
grant List on project external_project to USER <阿里云账号>;

--赋予某张表全部权限。
grant All on table <table_name> to user <阿里云账号>;

--切换为运行作业的项目。
use <main_project_name>;

--添加用户。
add user <阿里云账号>;

--赋予执行作业的权限。
grant CreateInstance on project <main_project_name> to user <阿里云账号>;
说明 当需要对其他阿里云账号（主账号）或RAM用户（子账号）授予External project表访问权限时，请您按照MaxCompute的标准安全规范进行授权。详情请参见权限概述。

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

如果需要创建与外部数据源中表结构相同的表，可以使用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；