This topic describes how to analyze data in a MaxCompute external table by using DLA.

MaxCompute, formerly referred to as ODPS, is a fast and fully hosted data warehousing solution. It can process terabytes or petabytes of data. MaxCompute provides a framework to process unstructured data. This allows you to export data stored in MaxCompute to OSS by using the INSERT statement. You can also associate MaxCompute external tables with OSS to export data stored in MaxCompute to OSS.

When you create an external table for MaxCompute, you must set the directory.odps attribute to true. This way, DLA reads data in files based on the structure of the directory where the external table is saved.

Before you perform the following operations, make sure that you have created an external table and written data into the table. For more information, see Export unstructured data to OSS

Procedure

1. Use the following statement to create an OSS schema:

​​​​​​​ CREATE SCHEMA dla_oss_db with DBPROPERTIES(
  catalog='oss',
  location 'oss://dlaossfile1/dla/'
  )​

2. Use the following statement to create an OSS external table:

CREATE EXTERNAL TABLE
 a int,
 b int,
 c int
 )
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
STORED AS `TEXTFILE`
LOCATION 'oss://dlaossfile1/dla/odps/'
 TBLPROPERTIES (
    'directory.odps' = 'true'
)​
  • The field names and data types must be the same as those defined in MaxCompute.
  • The parameters related to the file format must correspond to those defined in MaxCompute.
  • 'directory.odps' = 'true' indicates that the directory is organized based on the structure of the directory where the external table is saved.

3. Use the following statement to query data in the external table:

​mysql> select * from odps;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|   30 |   11 |   12 |
|   33 |   14 |   15 |
|   36 |   17 |   18 |
|   41 |    2 |    3 |
|   44 |    5 |    6 |
|   47 |    8 |    9 |
|   50 |   11 |   12 |
|   53 |   14 |   15 |
|   56 |   17 |   18 |
+------+------+------+
9 rows in set (9.69 sec)​