SQL Operators


The AI Pipelines provides the following operators related to database connection:

  • Hive Operator

  • Mysql Operator

  • Mysql Writer Operator

  • Internal Influxdb Writer Operator

  • Internal Influxdb Reader Operator

  • TSDB Operator

Hive Operator


The Hive operator is used to process HIVE SQL, supports Hive SQL syntax, and can be used to create new tables and execute SQL query and other Hive calculation tasks. In the current operating environment of EnOS, Hive SQL cannot be executed across environments due to the network isolation between different environments.

Input Parameters Description


Name

Required/Optional

Type

Description

data_source_name

Required

String

Data source name from the data source connection configuration.

sqls

Required

List

Specify the SQL statement to be run (list type). Enter one list element per line. See the following sample of list elements:

queue

Optional

String

Specify the name of the big data queue required to run HIVE SQL, which is the name of the Batch Data Processing - Big Data Queue requested through the Resource Management.

List Element Sample

If you need to enter ["a","b","c"], enter as follows:

"a"
"b"
"c"


If you need to enter [1,2,3], enter as follows:

1
2
3

Output Parameters Description


Name

Type

Description

resultset

File

Result set outputted after running HIVE SQL.

Mysql Operator


The Mysql operator is used to process MySQL SQL, supports MySQL syntax, and can be used to create new tables, execute SQL query and other tasks.

Input Parameters Description


Name

Required/Optional

Type

Description

data_source_name

Required

String

Data source name from the data source connection configuration.

sqls

Required

List

Specify the MySQL statement that needs to be run.

Output Parameters Description


Name

Type

Description

resultset

File

Result set outputted after running MySQL.

Mysql Writer Operator


The Mysql Writer operator is used to insert data into specified MySQL database.

Input Parameters Description


Name

Required/Optional

Type

Description

data_source_name

Required

String

Data source name from the data source connection configuration.

statement

Required

String

Enter the MySQL statement.

data

Required

File

Specify the data that need to be inserted.

skip_header

Optional

Boolean

Specify whether to skip the header. Select true to skip the header, select false to include the header.

Output Parameters Description


The Mysql Writer operator does not have fixed output parameters.

Internal Influxdb Writer Operator


The Internal Influxdb Writer operator is used to write data to the internal Influxdb storage.

Input Parameters Description


Name

Required/Optional

Type

Description

data

Required

File

Select the data that need to be written.

table_name

Required

String

Enter the table name.

Output Parameters Description


Name

Type

Description

result

String

Results of the data writing.

Internal Influxdb Reader Operator


The Internal Influxdb Reader operator is used to read data from the internal Influxdb storage.

Input Parameters Description


Name

Required/Optional

Type

Description

sqls

Required

List

Enter the MySQL statement.

database

Required

String

Enter the database name. The default value is app_portal.

Output Parameters Description


Name

Type

Description

result

Directory

Directory of the data.

TSDB Operator


The TSDB operator is used to get the raw data between a specified time range from the TSDB database.

Input Parameters Description


Name

Required/Optional

Type

Description

raw_url

Required

String

Enter the TSDB database address.

access_key

Required

String

Enter the AccessKey used to access the TSDB database.

secret_key

Required

password

Enter the SecretKey used to access the TSDB database. This field is displayed as blank when you enter the pipeline design page again after entering or updating the value.

org_id

Required

String

The organization ID which the asset belongs to.

asset_ids

Required

List

The asset ID. How to get asset ID>>

point_ids

Required

List

The measurement point ID. How to get measurement point ID>>

start_time

Required

timestamp

The start time of the sampling data, where both UTC and local time formats are supported.
  • The application queries the asset data by the local time of where the device is located. The local time format is YYYY-MM-DD HH:MM:SS.

  • The application queries all the asset data by the unified start timestamp and end timestamp when the UTC time format is used. Timezone information is required for the UTC time format. For example, 2019-06-01T00:00:00+08:00.

end_time

Required

timestamp

The end time of the sampling data. Its format must be consistent with the start time.

model_id

Optional

String

The model ID. How to get model ID>>

page_size

Optional

number

The upper limit of the returned records in a single page for a single measurement point of a single device.

with_quality

Optional

Boolean

Specify whether the quality indicator is included in the response. Select true to include, select false to exclude.

local_time_accuracy

Optional

Boolean

Specify whether millisecond precision with timestamp is included in the response. Select true to include, select false to exclude.

order_by

Optional

String

Specify whether to sort the response according to a certain field.

local_time_format

Optional

number

Specify whether the local time value includes timezone information of devices. 0 excludes the timezone information, 1 includes the timezone information.

item_format

Optional

number

Specify the displaying format of the returned device data. Available options are 0, 1, and 2. For more information about each displaying format, see Item Format Example.

boundary_type

Optional

String

Specify the data type if the measurement point data is compressed. Available options are:
  • inside: return compressed raw data only

  • outside: return compressed raw data and the closest data records outside the time range

  • interpolated: return compressed raw data and interpolated data records at the specified start time and end time

  • sample: return interpolated data records by specified time interval and algorithm within the specified time range

interval

Optional

number

Specify the time interval for generating the interpolated data records when boundaryType=sample. The unit is second.

interpolation

Optional

String

Specify the algorithm for generating the interpolated data records when boundaryType=sample. Available options are:
  • linear: linear interpolation

  • previous: previous value interpolation

  • near: near value interpolation

  • after: next value interpolation

type

Optional

String

Specify the TSDB storage type to query data from. If not specified, query measurement point data from the AI Raw, DI, PI, and Generic storage. Available options are:
  • ai: query AI Raw Data only

  • ai_normalized: query AI Normalized Data only

  • di: query DI Data only

  • pi: query PI Data only

  • generic: query Generic Data only

Output Parameters Description


Name

Type

Description

result

File

Retrieved data.