IDE
The IDE module provides powerful data query capabilities. Depending on your account permissions, you can query data across domains, including data within the workspace, within the AE system, and in externally linked data sources. In the IDE module, you can validate data, explore linked catalog data, or modify table structures (supported in later versions).
Query page
Click IDE in the navigation bar to open the IDE page, then click Start or + to create a query tab. The IDE page is divided into left and right panels.
The left side is the schema area. Click to view the data structures and table structures under the current data source, and use shortcut features to build query statements.
The right side is the query area, where you write query statements. Some data sources support querying data with different query engines; use the engine switcher to switch to the query engine you want.
Switch data source
Before querying data, make sure the current data source is what you expect. The current data source is shown directly above the schema area. If it isn't the data domain you want to query, hover over that area and select the data source you want from the Switch data source menu that appears. Note that users with different permissions can query different data sources. The data sources are described below:
-
Workspace: Data that the current space can manage, including the space tables of the current space, IDE tables created in the IDE module (not yet supported), and the system tables of projects authorized to this space. The data for each Schema is as follows:
- ws_<space_code>_product: Production environment space tables of this space
- ws_<space_code>_dev: Development environment space tables of this space
- ws_<space_code>_ide: IDE tables created by the IDE module in this space (not yet supported)
- ws_<space_code>_hidden: Temporary data storage area of this space
- ta: Main system tables of projects authorized to this space
- ta_dim: Dimension tables of projects authorized to this space
-
ETL Warehouse: All data in the data warehouse where the AE system resides. Its scope usually covers the Workspace source.
The data sources here are unrelated to the data sources in the Integration module. They are only used for queries.
Schema area
The left area of the query page is the schema area. Here you can view the data structure under the selected data source, select a table or view to see its table structure, and use shortcut features to build query statements. This section uses the Workspace source as an example to introduce the main features of the schema area:
The main part of the schema area has a three-level structure: data catalog (hive in the image above), database (ws_default_product in the image above), and table. The table level is further divided into two subdirectories, tables and views, and the innermost level shows the tables or views. Hover over a table or view name and click ··· to open the shortcut menu, where you can copy the table name, DDL, or SELECT statement.
Click a table name to expand the table structure area below the schema area, where you can view the fields of the selected table and their types. To query the table, select the fields you need or the checkbox in front of the first row, then click Generate Query to copy a SELECT statement that queries this table for the selected fields.
Click the button next to the data catalog to choose which databases are shown in the pop-up window:
Enter the name of the table you want to query in the search bar and press Enter to search. The schema area then shows the matching tables:
If the table or view you want to query, or a newly created catalog or database, isn't shown in the schema area, click the refresh button to update the schema area. Note that refreshing the schema area structure may take several minutes, so avoid clicking repeatedly and wasting resources.
You can also hover over the refresh button to see the last refresh time and decide whether the schema area structure needs to be refreshed.
Query area
You can write and run query statements in the query area. The query area also provides productivity features to help you write query statements more effectively.
Click Format to format the SQL you entered, or click Run (Limit 1000) to run the statement. Note that queries return only the first 1000 rows, so adjust your statement to avoid missing results. You can also press Ctrl / ⌘+Enter to run the statement.
Right-click in the input box to show the available actions and their shortcuts. You can replace content in bulk, copy selected content, and more.
For data sources that support switching query engines, click the language switcher in the bottom left corner of the input box to switch the query engine. Note that switching the query engine clears the query content, so copy your content first to avoid losing it.
Data download
You can download the result tables of your queries
- Export: Downloads in the browser, limited to 1000 rows
- Download Full: Runs an asynchronous download task that supports downloading full query results, up to 5 million rows