External table

Concepts

You can create, search for, configure, process, and configure external tables by using a visual interface in the DataWorks console. You can also query, compute, and analyze data in external tables. DataWorks is powered by MaxCompute.

Network and access authorization

Since MaxCompute is separate from OSS, network connectivity between them on different clusters may affect the ability of MaxCompute to access the data stored in OSS. We recommend that you use the private endpoint which ends with -internal.aliyuncs.com when you access the data stored in OSS from MaxCompute.

Create an external table

1. Use Data Definition Language (DDL) to create an external table.

   Go to the Data Analytics page and use a DDL statement to create an external table. The DDL statement must comply with the MaxCompute syntax. If you have completed STS authorization, you do not need to include the odps.properties.rolearn attribute in the DDL statement.

   The following section provides a sample DDL statement, in which EXTERNAL indicates an external table:

   CREATE EXTERNAL TABLE IF NOT EXISTS ambulance_data_csv_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' --Required. The STORED BY statement specifies the name of a custom storage handler class or other file format.
   with SERDEPROPERTIES (
   'delimiter'='\\|', -- Optional. The SERDEPROPERTIES clause specifies the parameters used when serializing or deserializing data. These parameters can be passed into the EXTRACT logic through DataAttributes.
   'odps.properties.rolearn'='acs:ram::xxxxxxxxxxxxx:role/aliyunodpsdefaultrole'
   )
   LOCATION 'oss://oss-cn-shanghai-internal.aliyuncs.com/oss-odps-test/Demo/SampleData/CustomTxt/AmbulanceData/'     --Required. The LOCATION parameter specifies the location of the external table.
   USING 'odps-udf-example.jar'; --Required if you use a custom format class. The USING parameter specifies the JAR package where the custom format class resides. 

   STORED BY can be followed by a parameter indicating the storage handler built in for the Comma Separated Value (CSV) or Tab Separated Value (TSV) file. The details are as follows:

   • com.aliyun.odps.CsvStorageHandler indicates that the table is stored as a CSV file. With this specification, the column delimiter is a comma (,) and the newline character is \n. One example is STORED BY'com.aliyun.odps.CsvStorageHandler'.
   • com.aliyun.odps.TsvStorageHandler indicates that the table is stored as a TSV file. With this specification, the column delimiter is \t and the newline character is \n.
   STORED BY can also be followed by a parameter indicating an open-source external table, such as ORC, Parquet, sequence file, Record Columnar File (RCFile), Avro, or text file. For example, you can specify the org.apache.hive.hcatalog.data.JsonSerDe class to save the table as a text file.

   • org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe: The table is stored as a text file.
   • org.apache.hadoop.hive.ql.io.orc.OrcSerde: The table is stored in the Optimized Row Columnar (ORC) format.
   • org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe: The table is stored in the Parquet format.
   • org.apache.hadoop.hive.serde2.avro.AvroSerDe: The table is stored in the Avro format.
   • org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe: The table is stored as a sequence file.
   The following section provides a DDL statement for creating an open-source external table:

     CREATE EXTERNAL TABLE [IF NOT EXISTS] (<column schemas>)
     [PARTITIONED BY (partition column schemas)]
     [ROW FORMAT SERDE '']
     STORED AS 
     [WITH SERDEPROPERTIES ( 'odps.properties.rolearn'='${roleran}'
     [,'name2'='value2',...]
     ) ]
     LOCATION 'oss://${endpoint}/${bucket}/${userfilePath}/';

   The following table lists attributes of the SERDEPROPERTIES clause.

   Attribute	Valid value	Default value	Description
   odps.text.option.gzip.input.enabled	true or false	false	Indicates whether the file to be read is compressed.
   odps.text.option.gzip.output.enabled	true or false	false	Indicates whether the file to be written is compressed.
   odps.text.option.header.lines.count	N (a non-negative integer)	0	Skip the first N lines of the file.
   odps.text.option.null.indicator	String	Empty string	The string that represents NULL.
   odps.text.option.ignore.empty.lines	true or false	true	Specifies whether to ignore blank lines.
   odps.text.option.encoding	UTF-8, UTF-16, or US-ASCII	UTF-8	The encoding of the file.

   Note MaxCompute can also read CSV and TSV files in GZIP format by using a built-in extracter. You can select whether the file is GZIP compressed, which determines attributes you need to specify.

   The LOCATION parameter is in the oss://oss-cn-shanghai-internal.aliyuncs.com/Bucket name/Directory name format. You can obtain the OSS directory in the graphical user interface (GUI). You do not need to add a filename next to the directory.

   You can find tables created by using DDL statements in the node tree on the Workspace Tables tab. You can modify the values of level 1 folder and level 2 folder to change the table locations.

2. Create a Table Store external table.
   The following section describes a statement used to create a Table Store external table:

   CREATE EXTERNAL TABLE IF NOT EXISTS ots_table_external(
   odps_orderkey bigint,
   odps_orderdate string,
   odps_custkey bigint,
   odps_orderstatus string,
   odps_totalprice double
   )
   STORED BY 'com.aliyun.odps.TableStoreStorageHandler' 
   WITH SERDEPROPERTIES (
   'tablestore.columns.mapping'=':o_orderkey,:o_orderdate,o_custkey, o_orderstatus,o_totalprice', -- (3)
   'tablestore.table.name'='ots_tpch_orders'
   'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole'
   )
   LOCATION 'tablestore://odps-ots-dev.cn-shanghai.ots-internal.aliyuncs.com'; 

   The parameters in the statement are described as follows:

   • com.aliyun.odps.TableStoreStorageHandler specifies the storage handler built in MaxCompute for processing data stored in Table Store.
   • SERDEPROPERTIES provides options for parameters. You must specify tablestore.columns.mapping and tablestore.table.name when using TableStoreStorageHandler.
     • tablestore.columns.mapping: Required. This parameter describes the columns of the table in Table Store that MaxCompute accesses, including the primary key columns and property columns. A primary key column is indicated with the colon sign (:) at the beginning of the column name. In this example, primary key columns are :o_orderkey and :o_orderdatee. The others are property columns.

       Table Store supports up to four primary key columns. The data types include STRING, INTEGER, and BINARY. The first primary key column is the partition key. You must specify all primary key columns of the table in Table Store when specifying the mapping. You only need to specify the property columns that MaxCompute accesses instead of specifying all property columns.

     • tablestore.table.name: the name of the table to access in Table Store. If the table name does not exist in Table Store, an error is returned. MaxCompute does not create a table in Table Store.
   • LOCATION: specifies the name and endpoint of the Table Store instance.
3. Create a table in the GUI.
   Go to the Data Analytics page and create a table in the GUI. An external table has the following attributes:

   • General
     • Table name (entered when you create a table)
     • Display name
     • Level 1 folder and level 2 folder
     • Description
   • Physical Model
     • Table Type: Select External Table.
     • Partitioning: Table Store external tables do not support partitioning.
     • Storage Address: In the Physical Model section, you can enter the value of the LOCATION parameter as the storage address. You can also click Click to Select to select a storage address. Then, click Authorize.
     • Storage Format: Select the file format as required. CSV, TSV, ORC, Parquet, sequence file, RCFile, Avro, text file, and custom file formats are supported. If you select a custom file format, you need to select the corresponding resource. After you commit a resource, DataWorks automatically parses out included class names and displays them in the Class Name drop-down list.
     • rolearn: If you have completed STS authorization, you can leave it unspecified.
   • Schema

     Parameter	Description
     Field Type	MaxCompute 2.0 supports fields of TINYINT, SMALLINT, INT, BIGINT, VARCHAR, and STRING types.
     Actions	The add, change, and delete actions are supported.
     Definition or Maximum Value Length	You can set the maximum length of the VARCHAR type fields. For complex data types, enter the definition.

Data types

If you want to use data types newly supported by MaxCompute 2.0 including TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, BINARY, and complex data types, you must include set odps.sql.type.system.odps2=true; before the statement used to create a table. Commit the set statement together with the table creation statement. If compatibility with Hive is required, we recommend that you include the odps.sql.hive.compatible=true; statement.

View and process external tables

Go to the Data Analytics page and click Workspace Tables in the left-side navigation pane to view external tables. External tables are processed in the same way as internal tables.