Pivoted Value Select

This API gets the result by rotating the output data in Row format in Column format.

HTTP Request

Parameters

Optional	Type	Description	Example
type	string	select type	&type=pivot
tag_name	string	tag name with , separator	&tag_name=tag1,tag2,tag3
calc_mode	string	min,max,cnt,avg,sum	&calc_mode=min (default : avg)
date_format	string	date format of select time or timestamp type ("SECOND", "MILLISECOND", "MICROSECOND", "NANOSECOND")	&date_format=YYYY-MM-DD HH24:MI:SS or &date_format=MILLISECOND (default : YYYY-MM-DD HH24:MI:SS)
start_time	string	start time of select (MUST be less than end_time!) type 1 : time string format start_time=2018-01-16 14:40:05 000 or start_time=2018-01-16 14:40:05.000 type 2 : timestamp (second or millisecond) start_time=1618813188 or start_time=1618813188123	&start_time=2018-01-16 14:40:05 000 or &start_time=2018-01-16 14:40:05.000 or &start_time=1618813188 or &start_time=1618813188123 datetime of parameter supports up to millisecond.
end_time	string	end time of select (MUST be more than start_time!) same as start_time	same as start_time
interval_type	string	sec,min,hour,day (or s,m,h,d)	&interval_type=hour (default : sec)
interval_value	int	duration value	&interval_value=10 (default : 1)
limit	int	count of display	&limit=1000 (default : Restricted by 'lake tier' by lake tier)
offset	int	count of skip	&offset=500
direction	int	ascend or descent by time	&direction=0 0 : ascend (default) 1 : descend

Request Example

Response Example

Sample Code

window(batch)

github

chcp 65001 
:: Text Encoding to UTF-8 in CMD
:: Example For Select Pivoted Data for tag API by using curl in window script
:: written by yeony kim

set API_KEY=YOUR_API_KEY
set LAKE_ID=YOUR_LAKE_ID
set CLOUD_VENDOR=YOUR_CLOUD_VENDOR
set CLOUD_REGION=YOUR_CLOUD_REGION

set CONTENT_HEADER="Content-Type: application/json"
set API_HEADER="x-api-key: %API_KEY%"
set URL="https://%CLOUD_VENDOR%.%CLOUD_REGION%.machlake.com/lakes/%LAKE_ID%/values/pivoted"

set SELECT_TYPE=pivot

:: ------------------------------------------------------------------------------------------------- ::

:: CASE - Pivoted DATA GET with date format YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn
set TAG_NAME=sensor1,sensor2
set CALC_MODE=min
set DATE_FORMAT=YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn
set START_TIME=2021-01-06 17:00:00 000:000:000
set END_TIME=2021-01-06 21:00:00 000:000:000

curl -k -G %URL% -H %CONTENT_HEADER% -H %API_HEADER% \
    --data-urlencode "type=%SELECT_TYPE%" \
    --data-urlencode "tag_name=%TAG_NAME%" \
    --data-urlencode "calc_mode=%CALC_MODE%" \
    --data-urlencode "start_time=%START_TIME%" \
    --data-urlencode "end_time=%END_TIME%" \
    --data-urlencode "date_format=%DATE_FORMAT%"

:: Return Format
:: {
::     "success": true,
::     "reason": "success",
::     "data":{
::         "columns":[
::             {"name":"TIME","type":5,"length":4096},
::             {"name":"sensor1","type":20,"length":17},
::             {"name":"sensor2","type":20,"length":17}
::         ],
::         "rows":[
::             ["2021-01-06 17:00:00 000:000:000",1,1],
::             ["2021-01-06 17:00:01 000:000:000",null,1.5],
::             ["2021-01-06 17:00:02 000:000:000",null,2],
::             ["2021-01-06 17:00:03 000:000:000",null,1],
::             ["2021-01-06 17:00:04 000:000:000",null,1.5],
::             ["2021-01-06 17:00:05 000:000:000",null,2],
::             ["2021-01-06 18:00:00 000:000:000",2,2],
::             ["2021-01-06 20:00:04 000:000:000",2,2],
::             ["2021-01-06 20:00:05 000:000:000",2.2.5],
::             ["2021-01-06 20:00:06 000:000:000",2,2]
::         ]
::     }
:: }

linux(shell)

github

# Text Encoding to UTF-8 in CMD
# Example For Select Pivoted Data for tag API by using curl in ubuntu

LAKE_ID=$YOUR_LAKE_ID
API_KEY=$YOUR_API_KEY
CLOUD_VENDOR=$YOUR_CLOUD_VENDOR
CLOUD_REGION=$YOUR_CLOUD_REGION

CONTENT_HEADER=Content-Type:application/json
API_HEADER=x-api-key:$API_KEY
URL=https://${CLOUD_VENDOR}.${CLOUD_REGION}.machlake.com/v1/lakes/${LAKE_ID}/values

# ------------------------------------------------------------------------------------------------- #

# CASE - Pivoted DATA GET with date format YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn
SELECT_TYPE=pivot
TAG_NAME=sensor1,sensor2
CALC_MODE=min
DATE_FORMAT="YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn"
START_TIME="2021-01-06 17:00:00 000:000:000"
END_TIME="2021-01-06 21:00:00 000:000:000"

curl -k -G $URL -H $CONTENT_HEADER -H $API_HEADER \
    --data-urlencode "type=$SELECT_TYPE" \
    --data-urlencode "tag_name=$TAG_NAME" \
    --data-urlencode "calc_mode=$CALC_MODE" \
    --data-urlencode "start_time=$START_TIME" \
    --data-urlencode "end_time=$END_TIME" \
    --data-urlencode "date_format=$DATE_FORMAT"

# Return Format
# {
#     "success": true,
#     "reason": "success",
#     "data":{
#         "columns":[
#             {"name":"TIME","type":5,"length":4096},
#             {"name":"sensor1","type":20,"length":17},
#             {"name":"sensor2","type":20,"length":17}
#         ],
#         "rows":[
#             ["2021-01-06 17:00:00 000:000:000",1,1],
#             ["2021-01-06 17:00:01 000:000:000",null,1.5],
#             ["2021-01-06 17:00:02 000:000:000",null,2],
#             ["2021-01-06 17:00:03 000:000:000",null,1],
#             ["2021-01-06 17:00:04 000:000:000",null,1.5],
#             ["2021-01-06 17:00:05 000:000:000",null,2],
#             ["2021-01-06 18:00:00 000:000:000",2,2],
#             ["2021-01-06 20:00:04 000:000:000",2,2],
#             ["2021-01-06 20:00:05 000:000:000",2.2.5],
#             ["2021-01-06 20:00:06 000:000:000",2,2]
#         ]
#     }
# }

# ------------------------------------------------------------------------------------------------- #

javascript

github

// Example For Select Pivoted Data for tag API by using request in node js
// written by yeony kim
// sensor1, sensor2 is applied in lake

// process.env.NODE_TLS_REJECT_UNAUTHORIZED ="0";

const API_KEY ="YOUR_API_TOKEN";
const LAKE_ID ="YOUR_LAKE_ID"
const CLOUD_VENDOR="CLOUD_VENDOR";
const CLOUD_REGION="CLOUD_REGION";
const URL = `https://${CLOUD_VENDOR}.${CLOUD_REGION}.machlake.com/v1/lakes/${LAKE_ID}/values`;

var request = require('request');

const TAG_NAME = "sensor1,sensor2";
const SELECT_TYPE="pivot";


/* ------------------------------------------------------------------------------------------------- */

// CASE - Pivoted DATA GET with date format YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn

request.get({
    url: URL, 
    qs: {
        type: SELECT_TYPE,
        tag_name: TAG_NAME,
        calc_mode: 'min',
        date_format: 'YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn',
        start_time: '2021-01-06 17:00:00 000:000:000',
        end_time: '2021-01-06 21:00:00 000:000:000'
    },
    headers: {
        'x-api-key': API_KEY
    }}, 
    function(error, response, body) {
        console.log(body);
        // Return Format
        // {
        //     "success": true,
        //     "reason": "success",
        //     "data":{
        //         "columns":[
        //             {"name":"TIME","type":5,"length":4096},
        //             {"name":"sensor1","type":20,"length":17},
        //             {"name":"sensor2","type":20,"length":17}
        //         ],
        //         "rows":[
        //             ["2021-01-06 17:00:00 000:000:000",1,1],
        //             ["2021-01-06 17:00:01 000:000:000",null,1.5],
        //             ["2021-01-06 17:00:02 000:000:000",null,2],
        //             ["2021-01-06 17:00:03 000:000:000",null,1],
        //             ["2021-01-06 17:00:04 000:000:000",null,1.5],
        //             ["2021-01-06 17:00:05 000:000:000",null,2],
        //             ["2021-01-06 18:00:00 000:000:000",2,2],
        //             ["2021-01-06 20:00:04 000:000:000",2,2],
        //             ["2021-01-06 20:00:05 000:000:000",2.2.5],
        //             ["2021-01-06 20:00:06 000:000:000",2,2]
        //         ]
        //     }
        // }
    })

python

github

"""
Example For Select Pivoted Data for tag API by using request in python
written by yeony kim
sensor1, sensor2 is applied in lake
"""

import requests

API_KEY = "YOUR_API_TOKEN"
LAKE_ID = "YOUR_LAKE_ID"
URL = f"https://{LAKE_ID}.machlake.com/lakes/values/pivoted"

headers = {
    'Content-Type': 'application/json',
    'x-api-key': API_KEY
}

# CASE - Pivoted DATA GET with date format YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn

params = {
    'tag_name': 'sensor1',
    'calc_mode': 'min',
    'date_format': 'YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn',
    'start_time': '2021-01-06 17:00:00 000:000:000',
    'end_time': '2021-01-06 21:00:00 000:000:000'
}

response = requests.get(URL, headers=headers,  params=params, verify=False)
print(response.content.decode('utf-8'))  # {"data":{"calc_mode":"MIN","columns":[{"name":"TIME","type":5,"length":4096},{"name":"sensor1","type":20,"length":17}],"samples":[{"data":[{"TIME":"2021-01-06 17:00:00 000:000:000","sensor1":1},{"TIME":"2021-01-06 18:00:00 000:000:000","sensor1":2},{"TIME":"2021-01-06 20:00:04 000:000:000","sensor1":2},{"TIME":"2021-01-06 20:00:05 000:000:000","sensor1":2.5},{"TIME":"2021-01-06 20:00:06 000:000:000","sensor1":2}]}]},"status":"success"}


# CASE - Pivoted DATA GET with another value form

params = {
    'tag_name': 'sensor1,sensor2',
    'calc_mode': 'cnt',
    'date_format': 'YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn',
    'start_time': '2021-01-06 17:00:00 000:000:000',
    'end_time': '2021-01-06 21:00:00 000:000:000',
    'value_return_form': 1
}

response = requests.get(URL, headers=headers,  params=params, verify=False)
print(response.content.decode('utf-8'))  # {"data":{"calc_mode":"COUNT","columns":[{"name":"TIME","type":5,"length":4096},{"name":"sensor1","type":12,"length":20},{"name":"sensor2","type":12,"length":20}],"samples":[{"data":{"TIME":["2021-01-06 17:00:00 000:000:000","2021-01-06 17:00:01 000:000:000","2021-01-06 17:00:02 000:000:000","2021-01-06 17:00:03 000:000:000","2021-01-06 17:00:04 000:000:000","2021-01-06 17:00:05 000:000:000","2021-01-06 18:00:00 000:000:000","2021-01-06 20:00:04 000:000:000","2021-01-06 20:00:05 000:000:000","2021-01-06 20:00:06 000:000:000"],"sensor1":[9,0,0,0,0,0,3,1,1,1],"sensor2":[1,1,1,1,1,1,3,1,1,1]}}]},"status":"success"}