This topic describes how to create, commit, and query data tables and provides basic knowledge about the layers of the data warehouse.

Create a table

  1. Log on to the DataWorks console. In the left-side navigation pane, click Workspaces. On the Workspaces page, find the target workspace and click Data Analytics in the Actions column.
  2. In the left-side navigation pane, click Workspace Tables.
  3. On the Workspace Tables page, click Create.
  4. In the Create Table dialog box, enter a name in Table Name and click Commit.
  5. Configure the table. For more information, see Table.

Commit a table

After editing the schema of a table, you can commit the table to the development environment and production environment.
Parameter Description
Load from Development Environment If the table has been committed to the development environment, the button is highlighted. After you click the button, the information about the table you create in the development environment overwrites the table information on the current page.
Commit in Development Environment The system first checks whether you have configured all the required items on the current editing page. If any item is missing, an alert is triggered and the table cannot be committed.
Load from Production Environment The detailed information of the table committed to the production environment overwrites the table information on the current page.
Commit to Production Environment After you click this button, the table is created in a workspace in the production environment.

Query tables by environment

DataWorks allows you to query tables in the development or production environment and displays queried tables in levels.

  • Development Environment: Display the tables in the development environment only.
  • Production Env: Display the tables in the production environment only. Use caution when operating the tables in the production environment.
Note Tables with the name starting with tmp_pyodps are temporary tables generated by the PyODPS node, and they will not be automatically deleted. You can clear PyODPS temporary tables regularly by using a script or SQL statement.

Modify the table name

If your table has not been committed, you can delete it and re-create one to modify the table name. If you have committed a table to the development or production environment, you can run the ALTER statement to modify the table name. on the MaxCompute client. For more information, see Install and configure the odpscmd client.

Divide a data warehouse into layers

You can divide your data warehouse into layers in the Physical Model section of the Workspace Tables page. This helps you obtain a clearer planning and control over your managed data. Typically, a data warehouse is divided into three layers: Operational Data Store (ODS), Common Data Model (CDM), and Application Data Service (ADS).

  • ODS layer
    The ODS layer is closest to data in the data stores and is the layer where you operate data. Data in the data stores is imported to this layer through the extract-transform-load (ETL) process. Data in the ODS layer can be classified in the same way as the data classification in the source business systems.
    Note Data in the ODS layer is not the same as data in the data stores. Many tasks such as denoising, deduplication, dirty data removal, extraction, and normalization are required before the source data is imported to this layer.
  • CDM layer

    The CDM layer is the principal of a data warehouse. The CDM layer establishes various data models for the data obtained from the ODS layer based on table folders.

  • ADS layer

    The ADS layer provides data results about data products, data mining, and data analytics for online systems. For example, report data or wide tables are usually stored at the ADS layer.