SQL Operators¶

The MI 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.