DataWorks:External tables

Overview

You can use DataWorks together with MaxCompute to create, search for, and configure external tables by using a visual interface in the DataWorks console. You can also query, process, and analyze data in external tables.

Network and access authorization

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

Create an external table

1. Create an external table by executing DDL statements.

   Go to the DataStudio page and execute DDL statements to create an external table. For more information, see Create and manage MaxCompute tables. The DDL statements must comply with the MaxCompute syntax. If STS authorization is complete, you do not need to include the odps.properties.rolearn property in the DDL statements.

   The following code 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 clause specifies the name of a custom storage handler class or another file format for the external table. 
   with SERDEPROPERTIES (
   'delimiter'='\\|', -- Optional. The SERDEPROPERTIES clause specifies the parameters used when data is serialized or deserialized. 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 built-in storage handler for a CSV or TSV file.

   • com.aliyun.odps.CsvStorageHandler: defines how to read data from and write data to CSV files. With this specification, the column delimiter is a comma (,) and the line feed is \n. Example: STORED BY'com.aliyun.odps.CsvStorageHandler'.

   • com.aliyun.odps.TsvStorageHandler: defines how to read data from and write data to TSV files. With this specification, the column delimiter is \t and the line feed is \n.

   STORED BY can also be followed by a parameter indicating an external table of which data is in one of the following open source formats: ORC, PARQUET, SEQUENCEFILE, RCFILE, AVRO, and TEXTFILE. 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 -> stored as textfile

   • org.apache.hadoop.hive.ql.io.orc.OrcSerde -> stored as orc

   • org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe -> stored as parquet

   • org.apache.hadoop.hive.serde2.avro.AvroSerDe -> stored as avro

   • org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe -> stored as sequencefile

   DDL statement used to create an external table of which data is in an open source format:

     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 describes the properties of the SERDEPROPERTIES clause.

   Property	Valid value	Default value	Description
   odps.text.option.gzip.input.enabled	true and false	false	Specifies whether to compress a file whose data you want to read.
   odps.text.option.gzip.output.enabled	true and false	false	Specifies whether to compress a file whose data you want to write.
   odps.text.option.header.lines.count	A non-negative integer	0	Skips the first N lines of a text file.
   odps.text.option.null.indicator	A string	Empty string	The string that represents NULL.
   odps.text.option.ignore.empty.lines	true and false	true	Specifies whether to ignore blank lines.
   odps.text.option.encoding	UTF-8, UTF-16, and US-ASCII	UTF-8	Specifies the encoding format of a text file.

   Note

   MaxCompute allows you to use only a built-in extractor to read CSV or TSV files in the GZIP format from OSS. You can select whether a file is GZIP compressed, which determines the properties that 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 an OSS directory in the graphical user interface (GUI). You do not need to add a file name next to the directory.

   You can find tables created by using DDL statements in the Workspace Tables pane. You can modify level-1 folders and level-2 folders of tables to change the table locations.

2. Create a Tablestore external table.

   Statement used to create a Tablestore 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'; 

   Parameter description:

   • com.aliyun.odps.TableStoreStorageHandler specifies the built-in storage handler of MaxCompute for processing data stored in Tablestore.

   • SERDEPROPERTIES provides options for parameters. You must specify tablestore.columns.mapping and tablestore.table.name when you use TableStoreStorageHandler.

     • tablestore.columns.mapping: Required. This parameter specifies the columns of the Tablestore table that MaxCompute wants to access. The columns include 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_orderdate. The others are property columns.

       A Tablestore table supports a maximum of four primary key columns. The data types of these columns must be STRING, INTEGER, or BINARY. The first primary key column is also the partition key column. You must specify all primary key columns of the table in Tablestore when you specify a mapping. For other columns, you need to specify only the property columns that MaxCompute needs to access.

     • tablestore.table.name: the name of the Tablestore table. If the table name does not exist in Tabletore, an error is reported, and MaxCompute does not create a table in Tablestore.

   • LOCATION: specifies the name and Endpoints of the Tablestore instance.

3. Create a table in the GUI.

   Go to the DataStudio page and create a table in the GUI. For more information, see Create and manage MaxCompute tables. An external table has the following properties:

   • General

     • Table Name (entered when you create a table)

     • Display Name

     • Level-1 Theme and Level-2 Theme

     • Description

   • Physical Model

     • Table Type: Select External Table.

     • Partition Type: Tablestore external tables do not support partitioning.

     • Storage Address: corresponds to the LOCATION parameter. In the Physical Model section, you can enter the value of the LOCATION parameter as the storage address. You can also click Select to select a storage address. Then, click Authorize.

     • File Format: Select the file format based on your business requirements. The following formats are supported: CSV, TSV, ORC, PARQUET, SEQUENCEFILE, RCFILE, AVRO, TEXTFILE, and a custom file format. 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 empty.

   • Schema

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

Supported data types

If you want to use data types newly supported by MaxCompute 2.0, such as 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, and 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

To view external tables, perform the following operations: Go to the DataStudio page of your workspace. In the left-side navigation pane, click Workspace Tables. For more information, see Manage tables. External tables are processed in the same way as internal tables.