阿里云云原生大数据计算服务 MaxCompute使用SQL管理外部项目-云淘科技

背景信息

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

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

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

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

基于数据湖构建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配置内容，避免每次手动输入。

• 更多分片上传功能信息，请参见.NET分片上传。

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

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

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

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

--指定写出到HDFS的用户名（非必须）。
set odps.external.hadoop.user=;

--读取Hive表数据。
select * from  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=，避免每次手动输入。

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

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