Creating an OBS Table Using the DataSource Syntax

Function

This statement is used to create an OBS table using the DataSource syntax. The main differences between the DataSource and the Hive syntax lie in the supported data formats and the number of supported partitions. For details, see syntax and precautions.

Usage

• The size of the table will not be calculated during table creation.
• When data is added, the table size will be changed to 0.
• You can view the table size on OBS.

Syntax

1
2
3
4
5
6
7

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name 
  [(col_name1 col_type1 [COMMENT col_comment1], ...)]
  USING file_format 
  [OPTIONS (path 'obs_path', key1=val1, key2=val2, ...)] 
  [PARTITIONED BY (col_name1, col_name2, ...)]
  [COMMENT table_comment]
  [AS select_statement];

Keyword

• IF NOT EXISTS: Prevents system errors when the created table exists.
• USING: Specifies the storage format.
• OPTIONS: Specifies the attribute name and attribute value when a table is created.
• COMMENT: Field or table description.
• PARTITIONED BY: Partition field.
• AS: Run the CREATE TABLE AS statement to create a table.

Parameter

Precautions

• The table and column names are case-insensitive.
• Descriptions of table names and column names support only string constants.
• During table creation, you need to specify the column name and corresponding data type. The data type is primitive type.
• If a folder and a file have the same name in the OBS directory, the file is preferred as the path when creating an OBS table.
• During table creation, if the specified path is an OBS directory and it contains subdirectories (or nested subdirectories), all file types and content in the subdirectories are considered table content. Ensure that all file types in the specified directory and its subdirectories are consistent with the storage format specified in the table creation statement. All file content must be consistent with the fields in the table. Otherwise, errors will be reported in the query. You can set multiLevelDirEnable to true in the OPTIONS statement to query the content in the subdirectory. The default value is false (Note that this configuration item is a table attribute, exercise caution when performing this operation). Hive tables do not support this configuration item.
• The OBS storage path must be a directory on the OBS. The directory must be created in advance and be empty.
• When a partitioned table is created, the column specified in PARTITIONED BY must be a column in the table, and the partition type must be specified. The partition column supports only the string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, and timestamp type.
• When a partitioned table is created, the partition field must be the last one or several fields of the table field, and the sequence of the partition fields must be the same. Otherwise, an error occurs.
• A maximum of 7,000 partitions can be created in a single table.
• The CREATE TABLE AS statement cannot specify table attributes or create partitioned tables.

Example

• Create a parquetTable OBS table.

  1	CREATE TABLE parquetTable (name string, id int) USING parquet OPTIONS (path "obs://bucketName/filePath");

• Create a parquetZstdTable OBS table and set the compression format to zstd.

  CREATE TABLE parquetZstdTable (name string, id string) USING parquet OPTIONS (path "obs://bucketName/filePath",compression='zstd');

• Create a student table that has two fields name and scoreand partition the table by classNo.

  1	CREATE TABLE IF NOT EXISTS student(name STRING, score DOUBLE, classNo INT) USING csv OPTIONS (PATH 'obs://bucketName/filePath') PARTITIONED BY (classNo);

  

  The classNo field is a partition field and must be placed at the end of the table field, that is, student(name STRING, score DOUBLE, classNo INT).

• To create table t1 and insert data of table t2 into table t1, run the following statement:

  1	CREATE TABLE t1 USING parquet OPTIONS(path 'obs://bucketName/tblPath') AS select * from t2;