Different tables in Data Lake Analytics (DLA) support different table properties.

table_mapping

If a table name in DLA is inconsistent with a table name in the underlying data source, you can use the table_mapping property to specify the underlying table name. For example, if the table name in DLA is person and the underlying table name is staff, you can execute the following SQL statement to add a mapping between the two tables:

​​​​​​​create external table person (
    id int,
    name string,
    age int
) tblproperties(
  table_mapping = 'staff'
); 

column_mapping

If the column name of a table in DLA is inconsistent with that of the underlying data source or the underlying table contains no columns, you can use the column_mapping property to specify the mapping between the column names in a DLA table and the column names in the underlying table.

Example:

​create external table person (
    id int,
    name string,
    age int
) tblproperties(
  column_mapping = 'id,identifier;name,title;'
);​

In the preceding example, the column name id in DLA maps to identifier in the underlying table, and name in DLA maps to title in the underlying table. The column name age in DLA is consistent with the column name age in the underlying table. You do not need to specify the mapping between them.

OSS

auto.create.location

When you create a table in DLA, you can use the auto.create.location property to specify a directory that does not exist in OSS for the table. After you specify this property, DLA automatically creates the required directory in OSS. Example:

​CREATE EXTERNAL TABLE person (
    `id` int,
    `name` string,
    `age` int    
)
STORED AS TEXTFILE
LOCATION 'oss://bucket001/path_which_does_not_exist/'
TBLPROPERTIES (
    'auto.create.location'='true'
);​

compression.type

If you use DLA to write data to OSS, the default compression type is Snappy. You can also use the compression.type property to specify the compression type as needed.

​CREATE EXTERNAL TABLE person (
    `id` int,
    `name` string,
    `age` int    
)
STORED AS TEXTFILE
LOCATION 'oss://bucket001/path_which_does_not_exist/'
TBLPROPERTIES (
    'compression.type'='gzip'
);​

Valid values:

  • SNAPPY
  • GZIP
  • None (no compression required)

directory.odps

If you analyze data of an OSS external table stored in MaxCompute by using DLA, you must specify the directory.odps property.

​CREATE EXTERNAL TABLE person (
    `id` int,
    `name` string,
    `age` int    
)
STORED AS TEXTFILE
LOCATION 'oss://bucket001/path_which_does_not_exist/'
TBLPROPERTIES (
    'directory.odps'='true'
);​

This is because the directory structure of an OSS external table stored in MaxCompute is different from that of an OSS table. If this property is not specified, the OSS external table cannot be found.