Creating a Database or a Table

Definition of Database and Table in DLI

A database, built on the computer storage device, is a data warehouse where data is organized, stored, and managed based on its structure.

The table is an important part of the database. It consists of rows and columns. Each column functions as a field. Each value in a field (column) represents a type of data.

The database is a framework and the table contains data content. A database has one or more tables.

The basic database concept and usage in DLI are similar to those in the Oracle database. DLI permissions are granted on a per database basis.

You can create databases and tables on the management console or using SQL statements. This section describes how to create a database and a table on the management console.

A view can be created only by using SQL statements. You cannot create a view on the Create Table page.

Precautions

• If a folder and a file have the same name in the OBS directory, the file path is preferred as the path of the OBS table to be created.

Creating a Database

1. You can create a database on either the Data Management page or the SQL Editor page.
   • To create a database on the Data Management page:
     1. On the left of the management console, choose Data Management > Databases and Tables.
     2. In the upper right corner of the Databases and Tables page, click Create to create a database.
   • To create a database on the SQL Editor page:
     1. On the left of the management console, click SQL Editor.
     2. In the navigation pane on the left, click beside Databases, you can create a database.

2. In the displayed Create Database dialog box, specify Name and Description by referring to Table 1.

   Table 1 Parameter description

   Parameter	Description	Example
   Database Name	The database name can contain only digits, letters, and underscores (_), but cannot contain only digits or start with an underscore (_). The database name is case insensitive and cannot be left blank. The length of the database name cannot exceed 128 characters.	DB01
   Description	Description of a database.	-

   

   The default database is a built-in database. You cannot create the database named default.

3. Click OK.

   After a database is created, you can view and select the database for use on the Databases and Tables page or SQL Editor page.

Creating a Table

Before creating a table, ensure that a database has been created.

1. You can create a table on either the Databases and Tables page or the SQL Editor page.
   

   Datasource connection tables, such as View tables, HBase (CloudTable/MRS) tables, OpenTSDB (CloudTable/MRS) tables, DWS tables, RDS tables, and CSS tables, cannot be created. You can use SQL to create views and datasource connection tables. For details, see sections Creating a View and Creating a Datasource Connection Table in the Data Lake Insight SQL Syntax Reference.

   • To create a table on the Data Management page:
     1. On the left of the management console, choose Data Management > Databases and Tables.
     2. On the Databases and Tables page, select the database for which you want to create a table. Click Create Table in the Operation column to create a table in the current database.
   • To create a table on the SQL Editor page:
     1. On the left of the management console, click SQL Editor.
     2. In the navigation pane of the displayed SQL Editor page, click Databases. You can create a table in either of the following ways:
        • Click a database name. In the Tables area, click on the right to create a table in the current database.
        • Click on the right of the database and choose Create Table from the shortcut menu to create a table in the current database.

2. In the displayed Create Table dialog box, set parameters as required.
   • If you set Data Location to DLI, set related parameters by referring to Table 2.
   • If you set Data Location to OBS, set related parameters by referring to Table 2 and Table 3.

     Table 2 Common parameters

     Parameter	Description	Example
     Name	The table name can contain only digits, letters, and underscores (_), but cannot contain only digits or start with an underscore (_). The table name is case insensitive and cannot be left unspecified. The table name can contain the dollar sign ($). An example value is $test. The length of the table name cannot exceed 128 characters.	table01
     Data Location	Data storage location. Currently, DLI and OBS are supported.	OBS
     Table Description	Description of the table.	-
     Column Name	Name of a column in a table. The column name must contain at least one letter. It can contain underscores (_), but cannot contain only digits or the combination of digits and letters B, K, M, G, and D (with the letter as the end). You can select Normal or Partition. Partition columns are dedicated to partition tables. User data is partitioned to improve query efficiency. NOTE: The column name is case-insensitive and must be unique.	name
     Type	Data type of a column. This parameter corresponds to Column Name. int: The storage space is four bytes. string: Refers to the character string type. float: Refers to the single-precision floating point. The storage space is four bytes. double: The storage space is eight bytes. date: The date ranges from 0000-01-01 to 9999-12-31. boolean: The storage space is one byte. decimal: The valid bits are positive integers between 1 to 38, including 1 and 38. The decimal digits are integers less than 10. smallint/short: The storage space is two bytes. bigint/long: The storage space is eight bytes. tinyint: The storage space is one byte. Only OBS tables support this function. timestamp: Refers to the date and time, which can be accurate to six decimal points. char: Refers to the fixed-length character strings. varchar: Refers to the variable-length strings.	string
     Column Description	Description of a column.	-
     Operation	: Add Column : Delete Column NOTE: If the table to be created includes a great number of columns, you are advised to use SQL statements to create the table or import column information from the local EXCEL file.	-

     Table 3 Parameter description when Data Location is set to OBS

     Parameter	Description	Example
     Data Format	DLI supports the following data formats: Parquet: DLI can read non-compressed data or data that is compressed using Snappy and gzip. CSV: DLI can read non-compressed data or data that is compressed using gzip. ORC: DLI can read non-compressed data or data that is compressed using Snappy. JSON: DLI can read non-compressed data or data that is compressed using gzip. Carbon: DLI can read non-compressed carbon data. Avro: DLI can read uncompressed Avro data.	CSV
     Bucket Location	Enter or select an OBS path. The path can be a folder or a path. NOTE: If you need to import data stored in OBS to the OBS table, set this parameter to the path of a folder. If the table creation path is a file, data fails to be imported.	obs://obs1/sampledata.csv
     Table Header: No/Yes	This parameter is valid only when Data Format is set to CSV. Whether the data source to be imported contains the table header. Click Advanced Settings and select the check box next to Table Header: No. If the check box is selected, the table header is displayed. If the check box is deselected, no table header is displayed.	-
     User-defined Delimiter	This parameter is valid only when Data Format is set to CSV and you select User-defined Delimiter. The following delimiters are supported: Comma (,) Vertical bar (|) Tab character (\t) Others: Enter a user-defined delimiter.	Comma (,)
     User-defined Quotation Character	This parameter is valid only when Data Format is set to CSV and you select User-defined Quotation Character. The following quotation characters are supported: Single quotation mark (') Double quotation marks (") Others: Enter a user-defined quotation character.	Single quotation mark (')
     User-defined Escape Character	This parameter is valid only when Data Format is set to CSV and you select User-defined Escape Character. The following escape characters are supported: Backslash (\) Others: Enter a user-defined escape character.	Backslash (\)
     Date Format	This parameter is valid only when Data Format is set to CSV or JSON. This parameter specifies the format of the date in the table and is valid only Advanced Settings is selected. The default value is yyyy-MM-dd.	2000-01-01
     Timestamp Format	This parameter is valid only when Data Format is set to CSV or JSON. This parameter specifies the format of the timestamp in the table and is valid only Advanced Settings is selected. The default value is yyyy-MM-dd HH:mm:ss.	2000-01-01 09:00:00

3. Click OK.

   After a table is created, you can view and select the table for use on the Data Management page or SQL Editor page.

4. (Optional) After a DLI table is created, you can decide whether to directly import data to the table.