5. Data Hub Querying
- Jenni Wallin
- Former user (Deleted)
- Kevin Wu
You can query the data stored in Data Hub via the 
Web UI. This requires that your user account belongs to an access group with execute permissions on Data Hub. For further information about access groups, see /wiki/spaces/MZD73/pages/5609086.
The Web UI is available at http://<platform host>:<web interface port>/mz/.
DataHub in the Web UI
Data Hub Profile Panel
To query Data Hub you must first select a Data Hub profile and one of its associated database tables in the Datahub Profile panel.
Data Hub profile panel
Query Panel
When you have selected a database table, you can configure the query- and filter parameters in the Query panel.
The Query panel represents an expression based on the columns in the selected table. When you run a query, rows in the table that matches the expression will be displayed in the Query Results.
Data Hub query panel
| Item | Description |
|---|---|
AND
| Click this button to apply AND logic on a set of rules or groups. |
OR
| Click this button to apply AND logic on a set of rules or groups. |
| Column | This drop-down list contains columns in the selected database table that you can use in your query. The listed columns are grouped according to their type. Partition columns, listed under Partition Columns significantly increases the speed of a query. It is highly recommended that you always include a rule that uses partition column. The columns, listed under Other Columns are used to further narrow down the result set.
Columns |
| Operator | This is a comparison operator that is applied to the values in the specified column and the value. The available operators depend on the data type of the selected column. All types: equal , not equal Integer-, floating-point-, and date types: less, less or equal, greater, greater or equal, between, not between, is null, is not null String types: equal, not equal, begins with, doesn't begin with, contains, doesn't contain, ends with, doesn't end with, is empty, is not empty, is null, is not null |
| Value | This field contains a value that is used for comparison with the selected column. When between and not between is the selected operator, two value fields are displayed. When you click this field and the column is of a date type, or a column with a type hint in the selected profile, a date picker will be displayed. |
Add Rule
| Click this button to add a new rule to the expression. |
Add Group
| Click this button to add a group of rules within the expression. The groups can be nested |
Delete
| Click this button to delete a rule or a group. When you delete a group, the rules contained within it will be deleted as well. |
| Reset | Click this button to clear all rules and groups from the Query panel. |
| Run Query | Click this button to run a new query based on the configured rules. |
| Max Results | Select the maximum number or rows that can be added to the result set. The default value is 1000 rows. |
| Timeout | Select the maximum duration of the query before it times out. The default value is 300 seconds. |
Running a Query
To run a query:
- If your query should contain both AND and OR logic, delete the first rule by clicking on the Delete button, then and add a new group by clicking the Add Group button. Groups are not required, if the query contains only AND or OR.
- If you have created a new group, click the Add rule button to add a new rule. Select a column that you want to use in a rule.
- Select an operator.
- Enter a value for comparison with the selected column.
- Use the Add group and Add rule buttons to add more groups conditions to the expression.
- Click the Run Query button. The result will be displayed in data grid in the Query Results panel. It may take a few seconds or several minutes for the query to complete.
- If the query fails due to a timeout, increase the value of the Timeout parameter, or update the rules to limit the result set.
Query Results Panel
By default, the Query Results panel will display pages of 20 rows including all columns. You can limit the number of columns that are displayed and increase or decrease the number of rows that appears on each page using the settings in this panel.
Data Hub query results
| Item | Description |
|---|---|
| Displayed Columns | This is a list of columns in the selected table that are displayed in the Query Results panel. Select one or more columns and click the right-arrow to move it to the Hidden columns list. |
| Hidden Columns | This is a list of columns in the selected table that are hidden in the Query Results. Select one or more columns and click the left-arrow to move it to the Displayed columns list. |
Export CSV
| Click this button to export the query result content to a CSV file. For further information, see the section below, Exporting Query Result Set as CSV. |
| Search Rows | Enter a search string to filter out rows. The search function will attempt to match the string with all visible columns in the query result content. |
| Rows per Page | Enter the maximum number of rows that can be displayed in the data grid. If the actual number of rows exceeds this number, a pagination control will displayed under the data grid. Hint! The Datahub profile and Query panels can be minimized to increase the screen size that is available for the Query results panel. To minimize a panel click the down-arrow in its top-right corner |
Pagination Control
| The pagination control is displayed if the query result content cannot be displayed on one page. You can use this control to navigate between pages. |
Exporting Query Result Set as CSV
To export a query result set:
- Click the button Export CSV in the Query Results Panel. A dialog opens.
Export as CSV dialog - Enter a character or string that should represent the separator token, e g a comma or a semi-colon.
- Enter a filename containing alphanumeric- or dash (-) characters. Do not add a file extension or a path.
- Select the check box Include Header to add the column names to first row in the file.
Click the Export button. The file will be saved as
<filename>.csvin your default download folder. All the columns of the table will be included in the file.