Advanced Search
Advanced Query Language
To perform an advanced search, you write a search query using the StreamSets advanced query language (SAQL).
A search query includes one or more conditions joined by an AND or OR logical operator. Each condition clause uses the following syntax:
<property><operator><value>
For example, the following query finds objects modified at any time on January 1, 2022:
modified_on=="2022-01-01"
The object that you are searching for, such as pipelines or jobs, determines the available search properties. The search property type determines the available operators.
The advanced query language allows you to specify criteria that cannot be defined in basic search, such as defining complex conditions or changing the order of precedence for multiple conditions. When needed, you can change to basic mode to perform a basic search.
You can save an advanced search for later use. You can also configure Control Hub to persist the last configured advanced search when you return to the view.
Multiple Condition Clauses
- Logical AND - Use
and
or a semicolon (;
). - Logical OR - Use
or
or a comma (,
).
name==lo* and version==1
name==lo*;version==1
When you include multiple condition clauses, the AND operator takes precedence over OR. Control Hub first evaluates all clauses connected by an AND operator, and then evaluates each clause connected by an OR operator. You can use parentheses in the query to define a different order of precedence.
For example, let's say that you define the following query:
version==1 or version==2 and committed_on>="2022-06-01"
- Finds pipelines with a version of 2 and that were committed on or after June 1, 2022.
- Finds pipelines with a version of 1 with no date restriction.
If you use the following parentheses in the query to define a different order of precedence:
(version==1 or version==2) and committed_on>="2022-06-01"
- Finds pipelines committed with a version of 1 or 2.
- Within those search results, finds pipelines committed on or after June 1, 2022.
Search Properties
Each searchable object type includes a fixed set of properties that you create search conditions for. All search property names are case sensitive.
Pipeline and Fragment Properties
A pipeline or fragment search finds the most recent version of each pipeline or fragment
that matches all search conditions, which might not be the latest version of the
pipeline or fragment. To find only the latest version, define a condition using the
latest_version
property. To find a specific version, define a
condition using the version
property.
Property Name | Type | Description |
---|---|---|
commit_message |
String | Commit message for the pipeline or fragment. |
commit_id |
String | Pipeline or fragment commit ID. |
committed_by |
String | User who committed the pipeline or fragment. Specify the user email address. For example: rita@mycompany.com |
committed_on |
Datetime | Date and time the pipeline or fragment was committed. |
description |
String | Pipeline or fragment description. |
draft |
Boolean | Indicates whether the pipeline or fragment is a draft version. |
engine_type |
Enumerator | Type of engine for the pipeline or fragment. You can specify the
following fixed values:
|
engine_version |
String | Version of the authoring engine used to last modify the pipeline
or fragment. For example, 5.0.0 or
5.1.0 . |
execution_mode |
Enumerator | Execution mode of the pipeline or fragment, based on the selected
engine type. You can specify the following fixed values:
|
label |
String | Pipeline or fragment label. Returns only published pipelines or fragments matching the specified label, not draft pipelines or fragments. To search for nested labels, such as Test/ADLSGen2 or Test/Elasticsearch, see Nested Labels and Tags. |
latest_version |
Boolean | Indicates whether the pipeline or fragment version is the latest
version. When set to When set to For more information about searching for a pipeline or fragment version, see Pipeline and Fragment Versions. |
library_name |
String | Name of a stage library used in the pipeline or fragment. For example, you might search for all fragments that use stages included in the Apache Kafka 3.1.0 stage library. |
modified_by |
String | User who last modified the pipeline or fragment. Specify the user email address. For example: rita@mycompany.com |
modified_on |
Datetime | Date and time the pipeline or fragment was last modified. |
name |
String | Name of the pipeline or fragment. |
pipeline_id |
String | Pipeline or fragment ID. |
stage_name |
String | Name of a stage used in the pipeline or fragment. For example, you might search for all pipelines that include an Amazon S3 stage. |
version |
String | Pipeline or fragment version. You can specify a draft version,
such as 1-DRAFT. Or a published version, such
as 3. Note: On some Control Hub pages, the Version column
includes a v prefix so that the version number displays as
v1 or v3 . On other pages, the Version column does
not include the prefix so that the version number displays as 1 or
3 . In basic searches, you can include or exclude the prefix. For
example, you can search for either v1 or
1. In advanced searches, you must exclude the prefix. For
example, you must search for 1.For more information about searching for a pipeline or fragment version, see Pipeline and Fragment Versions. |
Job Template Properties
Property Name | Type | Description |
---|---|---|
archived |
Boolean | Indicates whether the job template is archived. |
created_by |
String | User who created the job template. Specify the user email address. For example: rita@mycompany.com |
created_on |
Datetime | Date and time the job template was created. |
description |
String | Job template description. |
engine_label |
String | Engine labels assigned to the job template. |
engine_type |
Enumerator | Type of engine for the job template. You can specify the
following fixed values:
|
failover |
Boolean | Indicates whether pipeline failover is enabled for the job template. |
id |
String | Job template ID. |
instances |
Integer | Number of pipeline instances configured for the job
template. Available only for Data Collector job templates. |
modified_by |
String | User who last modified the job template. Specify the user email address. For example: rita@mycompany.com |
modified_on |
Datetime | Date and time the job template was last modified. |
name |
String | Name of the job template. |
pipeline_name |
String | Name of the pipeline included in the job template. |
pipeline_version |
String | Version of the pipeline included in the job template. You can
specify a draft version, such as v1-DRAFT. Or
a published version, such as v3. Note: On some Control Hub pages, the
Version column includes a v prefix so that the version number displays
as v1 or v3 . On other pages, the Version column does
not include the prefix so that the version number displays as 1 or
3 . In basic searches, you can include or exclude the prefix. For
example, you can search for v1 or 1. In
advanced searches, you must include the prefix. For example, you must search for
v1. |
tag |
String | Job template tag. To search for nested tags, such as Test/ADLSGen2 or Test/Elasticsearch, see Nested Labels and Tags. |
Job Instance Properties
Property Name | Type | Description |
---|---|---|
attached_to_template |
Boolean | Indicates whether the job instance is attached to a parent job
template. When set to When set to For more information, see Attached and Detached Job Instances. |
color |
Enumerator | Color of the job instance status. You can specify the following
fixed values:
|
created_by |
String | User who created the job instance. Specify the user email address. For example: rita@mycompany.com |
created_on |
Datetime | Date and time the job instance was created. |
description |
String | Job instance description. |
engine_label |
String | Engine labels assigned to the job instance. |
engine_type |
Enumerator | Type of engine for the job instance. You can specify the
following fixed values:
|
failover |
Boolean | Indicates whether pipeline failover is enabled for the job instance. |
id |
String | Job instance ID. |
instances |
Integer | Number of pipeline instances configured for the job
instance. Available only for Data Collector job instances. |
modified_by |
String | User who last modified the job instance. Specify the user email address. For example: rita@mycompany.com |
modified_on |
Datetime | Date and time the job instance was last modified. |
name |
String | Name of the job instance. |
pipeline_name |
String | Name of the pipeline included in the job instance. |
pipeline_status |
Enumerator | Status of the pipeline included in the job instance. For the list
of fixed values that you can specify, see Pipeline Status. Note: For Data Collector jobs that run multiple pipeline instances, all pipeline
instances must have the same status. For example, let's say
that you search for jobs that include a pipeline with the
RUNNING status. The search does not find a job that has one
pipeline instance with the RUNNING status and another with
the RUN_ERROR status.
|
pipeline_version |
String | Version of the pipeline included in the job instance. You can
specify a draft version, such as v1-DRAFT. Or
a published version, such as v3. Note: On some Control Hub pages, the
Version column includes a v prefix so that the version number displays
as v1 or v3 . On other pages, the Version column does
not include the prefix so that the version number displays as 1 or
3 . In basic searches, you can include or exclude the prefix. For
example, you can search for v1 or 1. In
advanced searches, you must include the prefix. For example, you must search for
v1. |
status |
Enumerator | Job instance status. You can specify the following fixed
values:
Note: The ACTIVATION_ERROR status is
not used at this time. |
tag |
String | Job instance tag. To search for nested tags, such as Test/ADLSGen2 or Test/Elasticsearch, see Nested Labels and Tags. |
Draft Run Properties
Property Name | Type | Description |
---|---|---|
color |
Enumerator | Status color of the job automatically created for the draft run.
You can specify the following fixed values:
|
created_by |
String | User who created the draft run. Specify the user email address. For example: rita@mycompany.com |
created_on |
Datetime | Date and time the draft run was created. |
engine_type |
Enumerator | Type of engine for the draft run. You can specify the following
fixed values:
|
id |
String | ID of the job automatically created for the draft run. |
modified_by |
String | User who last modified the draft run. Specify the user email address. For example: rita@mycompany.com |
modified_on |
Datetime | Date and time the draft run was last modified. |
name |
String | Name of the draft run. |
pipeline_name |
String | Name of the pipeline included in the draft run. |
pipeline_status |
Enumerator | Status of the pipeline included in the draft run. For the list of fixed values that you can specify, see Pipeline Status. |
pipeline_version |
String | Version of the pipeline included in the draft run. You can
specify a draft version, such as
v1-DRAFT. Note: On some Control Hub pages, the Version column includes a v
prefix so that the version number displays as
v1-DRAFT or v3-DRAFT . On
other pages, the Version column does not include the prefix so
that the version number displays as 1-DRAFT or
3-DRAFT . In basic searches, you can include
or exclude the prefix. For example, you can search for
v1-DRAFT or
1-DRAFT. In advanced searches, you
must include the prefix. For example, you must search for
v1-DRAFT. |
status |
Enumerator | Status of the job automatically created for the draft run. You
can specify the following fixed values:
Note: The ACTIVATION_ERROR status is
not used at this time. |
Sequence Properties
Property Name | Type | Description |
---|---|---|
created_by_email |
String | User who created the sequence. Specify the user email address. For example: rita@mycompany.com |
created_on |
Datetime | Date and time the sequence was created. |
description |
String | Sequence description. |
id |
String | Sequence ID. |
job_name |
String | Name of a job included in the sequence. |
last_modified_by_email |
String | User who last modified the sequence. Specify the user email address. For example: rita@mycompany.com |
last_run_time |
Datetime | Date and time the sequence was last run. |
modified_on |
Datetime | Date and time the sequence was last modified. |
name |
String | Name of the sequence. |
next_run_time |
Datetime | Date and time that the sequence is scheduled to next run. |
status |
Enumerator | Status of the sequence. You can specify the following fixed
values:
|
created_by_email
,
job_name
, and last_modified_by_email
properties
are not immediately reflected in sequence searches. If expected sequences do not appear
in your search results, wait a few minutes and then try again. Operators
The operators that you can use in a condition depend on the property type. For example, you can use one set of operators to search for string properties, and another set to search for datetime properties.
Boolean Operators
- Equal to:
==
- Not equal to:
!=
- Empty or not empty:
=null=
All operators for boolean properties accept only a true
or
false
value. For example, the following condition finds all jobs
enabled for pipeline failover:
failover==true
Datetime Operators
- Equal to:
==
- Not equal to:
!=
- Less than:
=lt=
or<
- Less than or equal to:
=le=
or<=
- Greater than:
=gt=
or>
- Greater than or equal to:
=ge=
or>=
- Empty or not empty:
=null=
- Datetime formats
- Use one of the following formats:
YYYY
YYYY-MM
YYYY-MM-DD
YYYY-MM-DD hh
YYYY-MM-DD hh:mm
YYYY-MM-DD hh:mm:ss
- Enclose datetime values in quotation marks
-
Enclose datetime values in single or double quotation marks.
For example, the following condition finds all fragments modified at any time on January 1, 2022:
modified_on=="2022-01-01"
The following condition finds all fragments modified on or after 10 am on January 1, 2022:
modified_on>="2022-01-01 10:00:00"
- Empty or not empty operator
-
The
=null=
operator accepts only atrue
orfalse
value.For example, the following condition finds all fragments that do not have a committed date:
committed_on=null=true
Enumerator Operators
An enumerator property allows you to select from a set of fixed values, such as the
engine_type
property.
- Equal to:
==
- Not equal to:
!=
- In:
=in=
- Not in:
=out=
- Empty or not empty:
=null=
- Case sensitive
-
Enumerator values are case sensitive.
For example, to find pipelines that use the streaming execution mode, you must enter the value in all uppercase, as follows:
execution_mode==STREAMING
- Multiple enumerator values
-
Use the
=in=
and=out=
operators to search for multiple enumerator values. Enclose multiple enumerator values in parentheses and separate the values with commas.For example, the following condition finds all Data Collector or Transformer pipelines:
engine_type=in=(COLLECTOR,TRANSFORMER)
- Empty or not empty operator
-
The
=null=
operator accepts only atrue
orfalse
value.For example, the following condition finds all pipelines that have a defined execution mode:
execution_mode=null=false
Integer Operators
- Equal to:
==
- Not equal to:
!=
- Less than:
=lt=
or<
- Less than or equal to:
=le=
or<=
- Greater than:
=gt=
or>
- Greater than or equal to:
=ge=
or>=
- In:
=in=
- Not in:
=out=
- Empty or not empty:
=null=
Use the following guidelines to define integer values:
- Multiple integer values
- Use the
=in=
and=out=
operators to search for multiple integer values. Enclose multiple integer values in parentheses and separate the values with commas. - Empty or not empty operator
- The
=null=
operator accepts only atrue
orfalse
value.
String Operators
- Equal to:
==
- Not equal to:
!=
- Less than:
=lt=
or<
- Less than or equal to:
=le=
or<=
- Greater than:
=gt=
or>
- Greater than or equal to:
=ge=
or>=
- In:
=in=
- Not in:
=out=
- Empty or not empty:
=null=
- Case insensitive
- String values are case insensitive.
- Enclose strings with spaces in quotation marks
- Enclose a string that includes spaces in single or double quotation marks.
For example:
name=="HDFS to ADLS2"
- Asterisk wildcard
- Include the asterisk wildcard (*) in string values to perform a multiple
character wildcard search.
For example, the following condition finds the LogsToKafka, LogsToKinesis, and LogsToS3 pipelines:
name==logs*
You can also include the asterisk wildcard in the middle of a string value. For example, the following search condition finds the LogsToKafka and LogsToKinesis pipelines:
name==logs*k*
- Escape special characters with a backslash
- Escape special characters with a backslash (\). Special characters include single quotation marks ('), double quotation marks ("), and asterisks (*).
- Multiple string values
- Use the
=in=
and=out=
operators to search for multiple string values. Enclose multiple string values in parentheses and separate the values with commas. - Less than and greater than operators
- Use the less than (
<
and<=
) and greater than (>
and>=
) operators to search for string values that are in a lower or higher alphabetic order, wherea
is the lowest andz
is the highest. - Empty or not empty operator
- The
=null=
operator accepts only atrue
orfalse
value.
For details about searching for string values for job instance or job template tags or for pipeline or fragment labels and versions, see the following topics:
Query Examples
- failover==true and status==INACTIVE
- Finds jobs enabled for pipeline failover and that have an INACTIVE status.
- name==DL0* and committed_on>"2021-06-05"
- Finds fragments with a name that begins with DL0 and that were committed after June 5, 2021.
- (modified_by==melinda@mycompany.com or modified_by==louis@mycompany.com) and modified_on>="2022-01-01"
- Finds pipelines last modified by Melinda or Louis and last modified at any time on or after January 1, 2022.
- engine_version==5.1.0 and (name==*snowflake* or name==oracle*)
- Finds pipelines that use a 5.1.0 engine version and with a name that contains the string snowflake or a name that begins with the string oracle.
- (engine_version==5.1.0 or description=="For testing*") and name==*kafka*
- Finds pipelines that use a 5.1.0 engine version or that have a description beginning with the string For testing and that have a name that contains the string kafka.
- engine_type=in=(COLLECTOR,TRANSFORMER) and engine_label==tutorial
- Finds Data Collector and Transformer jobs that are assigned the tutorial engine label.