MaxCompute:Custom storage handler

Usage notes

• OSS external tables do not support the cluster property.

• The size of a single file cannot exceed 2 GB. You must split files that are larger than 2 GB.

Create an external table

CREATE EXTERNAL TABLE [IF NOT EXISTS] mc_oss_extable_name
(
  col_name data_type,
  ...
)
[comment table_comment]
[partitioned BY (col_name data_type, ...)] 
stored BY '<YOUR_DEFINED_STORAGEHANDLER>' 
WITH serdeproperties (
  ['property_name'='property_value',...]
) 
location 'oss_location' 
USING 'jar_name';

SET odps.sql.unstructured.data.single.file.split.enabled=true;

Write data

For more information about the MaxCompute write syntax, see Write data to OSS.

Query and analyze data

• For more information about the SELECT syntax, see Read data from OSS.

• For more information about how to optimize query plans, see Query optimization.

Example: Create an OSS external table

This example shows how to map an external table to the SampleData/ directory described in Appendix: Prepare sample data. The directory is prepared for use with a custom storage handler. The procedure is as follows:

1. Prerequisites

   • A MaxCompute project is created.

   • An OSS bucket and folder are available. For more information, see Create a bucket and Manage folders.

     MaxCompute supports automatic folder creation in OSS. If an SQL statement involves external tables and user-defined functions (UDFs), you can use a single statement to read from and write to the tables and use the UDFs. You can also create folders manually.

     MaxCompute is deployed only in specific regions. To avoid potential issues with cross-region data connections, ensure that your OSS bucket is in the same region as your MaxCompute project.

   • Authorization

     • You must have permissions to access OSS. You can use an Alibaba Cloud account, a Resource Access Management (RAM) user, or a RAM role to access OSS external tables. For more information about authorization, see Authorize access in STS mode for OSS.

     • You must have the CreateTable permission in the MaxCompute project. For more information about table permissions, see MaxCompute permissions.

2. Use MaxCompute Studio to create the TextExtractor.java, TextOutputer.java, SplitReader.java, and TextStorageHandler.java Java classes. For more information about how to develop Java programs, see Develop UDFs.

3. Use the one-click packaging feature of MaxCompute Studio to package TextStorageHandler.java and upload the package as a MaxCompute resource.

   Assume that the resource is named javatest-1.0-SNAPSHOT.jar. For more information about how to package, upload, and register a resource, see Package, upload, and register.

   Note

   If you have multiple dependencies, package each one and upload it as a separate MaxCompute resource.

4. Run the following command to create the OSS external table:

   CREATE EXTERNAL TABLE ambulance_data_txt_external
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongtitue DOUBLE,
     recordTime STRING,
     direction STRING
   )
   stored BY 'com.aliyun.odps.udf.example.text.TextStorageHandler' 
     WITH serdeproperties (
       'delimiter'='|',  
       'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
     )
   location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/SampleData/'
   USING 'javatest-1.0-SNAPSHOT.jar'; 
   
   -- You can run the 'desc extended ambulance_data_txt_external;' command to view the schema of the created external table.

   Note

   The delimiter parameter is a custom property that defines the column separator in the OSS file. You can specify any valid string as the delimiter.

5. Read data from OSS. Sample command:

   SELECT recordId, patientId, direction FROM ambulance_data_txt_external WHERE patientId > 25;

   The following result is returned:

   +----------+-----------+-----------+
   | recordid | patientid | direction |
   +----------+-----------+-----------+
   | 1        | 51        | S         |
   | 3        | 48        | NE        |
   | 4        | 30        | W         |
   | 5        | 47        | S         |
   | 7        | 53        | N         |
   | 8        | 63        | SW        |
   | 10       | 31        | N         |
   +----------+-----------+-----------+

6. Write data to the OSS external table.

   INSERT INTO ambulance_data_txt_external VALUES (1,16,76,1,'46.81006','-92.08174','9/14/2014 0:10','SW');
   
   -- Query the table again to check whether the data is written. You can also check whether a new file is generated in the OSS directory.
   SELECT * FROM ambulance_data_txt_external WHERE recordId='16';

FAQ