Queries
Use queries to retrieve design-time and run-time objects.
You can retrieve design-time objects from a Model repository or run-time objects that were deployed to a Data Integration Service. To build a query, use query parameters to determine the objects that you want to retrieve. You can make a query more specific by using the where clause and operators.
Query Structure
Use parameters, operations, and the where clause to build a query.
You can structure a query by using parameters, comparison operators, logical operators, and the where clause. You can control the query precedence by using parentheses.
A query is structured with the following elements:
- Query parameters
- Query parameters are categorized into subject, time, status, and location. Each query parameter must be combined with a comparison operator. For example,
mapping=Mapping1
- Comparison operators
- Comparison operators are used to specify criteria to query objects. Comparison operators are used with the query parameters to build a query.
- Logical operators
- Logical operators are used to test a condition in a query. Logical operators can have multiple query parameters. For example,
mapping=Mapping1 || createdBy=admin
- Where clause
- The where clause is used to restrict the query scope. For example,
name=mapping1 where project=project1, folder=folder1.
Query Parameters
Use query parameters to query design-time objects in a Model repository and run-time objects that are deployed to a Data Integration Service. You can use subject, time, status, and location to build a query.
Query parameters are divided into the following parameters types:
- Subject
- Parameters that test a subject such as specific object or user. The following table lists the subject parameters:
Parameter | Object Type | Description |
|---|
name | Design-time object Run-time object | Name of the object that you want to query. You can specify the name of one of the following types of objects: - - Mapping
- - Physical data object
- - Parameter set
|
tag | Design-time object | Tag that is assigned to the object. |
createdBy | Design-time object | User that created the object. |
lastModifiedBy | Design-time object | User that last modified the object. |
- Time
- Parameters that test the time when an object was changed. The following table lists the time parameters:
Parameter | Object Type | Description |
|---|
lastModifiedTime | Design-time object | Time when the object was last modified. |
checkInTime | Design-time object | Time when the object was last checked in. Note: Applies only if the Model repository is integrated with a version control system. |
checkOutTime | Design-time object | Time when the object was last checked out. Note: Applies only if the Model repository is integrated with a version control system. |
creationTime | Design-time object | Time when the object was created. |
- Status
- Parameters that test the status of an object. The following table lists the status parameters:
Parameter | Object Type | Description |
|---|
versionStatus | Design-time object | Version status of the object. The version status can either be checked in or checked out. Note: Applies only if the Model repository is integrated with a version control system. |
- Location
- Parameters that test where an object is located such as specific project, folder, or run-time application. The following table lists the location parameters:
Parameter | Object Type | Description |
|---|
folder | Design-time object | Folder that contains the object. |
project | Design-time object | Project that contains the object. |
application | Run-time object | Name of the run-time application that contains the object. |
Comparison Operators
Use the comparison operators with query parameters to build a query. You can use comparison operators to specify criteria when you query objects.
The following table lists the comparison operators that you can use with each type of query parameter:
Query Parameter Type | Includes Query Parameters | Comparison Operators | Examples |
|---|
Subject | name tag createdBy lastModifiedBy | ~contains~ ~not-contains~ ~not-ends-with~ ~not-starts-with~ ~ends-with~ ~starts-with~ = != ~in~ ~not-in~ | name ~contains~ Mapping tag ~in~ (tg_1, tg_2, tg_3) createdBy = Administrator lastModifiedBy ~ends-with~ visitor |
Time | lastModifiedTime checkInTime checkOutTime creationTime | > < ~within-last~ ~between~ ~not-between~ | lastModifiedTime < 2019-02-26 20:32:54 checkInTime ~between~ (2018-12-26 20:32:54, 2018-05-26 20:32:54) checkOutTime ~within-last~ 10 (days) |
Status | versionStatus | ~is-checkedin~ ~is-checkedout~ | versionStatus ~is-checkedin~ versionStatus ~is-checkedout~ |
Location | folder project application | ~contains~ ~not-ends-with~ ~not-contains~ ~not-starts-with~ ~ends-with~ ~starts-with~ = != ~in~ ~not-in~ | name ~contains~ Mapping where project ~ends-with~ _1 lastModifiedBy ~ends-with~ trator where folder ~not-in~ (Folder_3, Folder_2) all where project=Project_1, folder=Folder_1 name = Mapping where project=Project_1, folder=/Folder_1/Folder_2/ name = Mapping where project=Project_1, folder=/ name = captain_america where app~in~ (MapGenTest, MapGenEg) |
If you have build a query specifying a criterion by using comparison operators, the query returns the object that satisfies the criterion to the client.
For example, you can build a query to fetch objects that have the name mapping 1.
name=mapping1
Note: The time format is YYYY-MM-DD HH24:MI:SS.
Specifying a Folder Path
Use a recursive or non-recursive folder path to build a query. You can specify the folder path to access objects inside a folder.
You can use the following types of folder paths:
- •Recursive. Includes objects in the folder and all subfolders.
- •Non-recursive. Includes only the objects inside the root folder.
Folder paths are recursive by default. To specify a non-recursive folder path, use a forward slash at the end of the folder path.
The following table describes sample queries with both recursive and non-recursive folder paths:
Sample Query | Description |
|---|
name=map1 folder=/ | Non-recursive. The query examines only the objects that are nested directly under the project. |
name=map1 folder=/f1/f2/ | Non-recursive. The query examines only the objects that are located in the path /f1/f2/. |
name=map1 folder=f1 | Recursive. The query examines all objects that are located in folder f1 and all subfolders within f1. |
name=map1 folder=/f1/f2 | Recursive. The query examines all objects that are located in the path /f1/f2 and all subfolders of f2. |
Note: If you use a forward slash to specify a non-recursive folder path, you can only use the comparison operators =, !=, ~in~, and ~not-in~.
Logical Operators
Use logical operators to test whether one or more conditions in a query are TRUE or FALSE.
You can use the following logical operators:
Logical Operator | Description | Example |
|---|
! | NOT | ! name ~not-starts-with~ M_ |
&& | AND | name ~starts-with~ map_&& lastModifiedBy ~ends-with~ visitor |
|| | OR | checkInTime > 2018-12-26 20:32:54 || lastModifiedTime > 2019-02-26 20:32:54 |
Note: You cannot use logical operators to test location query parameters, including folder names, project names, and application names.
Where Clause
Use a where clause to restrict the scope of a query.
You can specify only location query parameters inside a where clause. Location query parameters do not support logical operators, so you cannot use logical operators inside the where clause.
For example, the following query locates a mapping within a specific project and folder:
name=mapping1 where project1, folder=folder1
You can use parentheses outside of the where clause. For example, the following query uses expressions (name contains super && name ends-with boy) and (name contains ragnarok) that are enclosed in parentheses and are outside of the where clause:
(name contains super && name ends-with boy) || (name contains ragnarok) where project=MapGenTest
You can use all keyword to locate all design-time objects on a Model repository or all run-time objects that are deployed to a Data Integration Service. You can use all keyword with the where clause.
For example, the following query locates all objects within a specific folder:
all where folder=Folder_1