Data warehouse table

This topic describes how to create and manage internal tables and foreign tables. To create and edit tables in a warehouse project, you must have the change permission on the database to which the warehouse project is bound. For more information, see Permission management.

Internal table

Internal tables are objects in a data warehouse engine. To create and manage internal tables, follow these steps: Go to Data Warehouse Developer. On the left-side navigation submenu, click Develop Space. Click the target warehouse project in the left-side navigation pane, choose Warehouse table > Internal table > More, and then select an item from the drop-down menu.

Create an internal table

Currently, you must use SQL scripts to create internal tables in Data Management Service (DMS). When you create an internal table, you can add a hint before SQL statements to specify the theme and layer of the table. After themes are specified for internal tables, you can search for the tables by theme in Data Map, which will be available soon. After layers are specified for internal tables, you can manage the tables by hierarchy.

 

  
/* theme: 0 | Theme name
  
    layer: Layer name */
  
CREATE TABLE IF NOT EXISTS inner_table_name (c1 INT, c2 VARCHAR(20))
 

The hint must be placed before SQL statements and enclosed in / /. In this hint, the theme information and layer information are separated in two lines. The theme information is in the theme: [0-1] | Theme name format, where 0 indicates the level-1 theme and 1 indicates the level-2 theme. The theme level and theme name are separated with a vertical bar (|). The layer information is in the layer: Layer name format.

Import an existing table as an internal table

If you have created tables in the data warehouse engine by using other methods, Data Warehouse Developer of DMS cannot detect these tables in real time. Data Warehouse Developer provides the internal table import feature for you to import existing tables as internal tables.

You can also specify the theme and layer for such tables.

Add an internal table to a folder

If you have many internal tables, you can classify them into different folders by department or business. To add an internal table to a folder, create a folder, such as table_folder_1, and drag the table to the folder. Alternatively, set the Destination folder parameter in the Move dialog box.

Foreign table

Foreign tables are mapped to tables in other systems outside a data warehouse engine. You must define a foreign table in Data Warehouse Developer before you can use data in the foreign table. If you have stored a small amount of data in an external system, you can define a foreign table to import the data from the external system and query the data jointly with the data in internal tables. Foreign tables will be available soon.

If your task flow uses a foreign table, the task node that references the foreign table is run by Dynamic SQL Object (DSQL). For more information, see Cross-database query.