Pivoted Value Select

[ 1 HTTP Request ] [ 1.1 Parameters ] [ 2 Request Example ] [ 3 Response Example ] [ 4 Sample Code ]

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

HTTP Request

Parameters

Optional	Type	Description	Example

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

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]
::         ]
::     }
:: }

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]
#         ]
#     }
# }

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

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]
        //         ]
        //     }
        // }
    })

github

"""
Example For Select Pivoted Data for tag API by using request in python
written by yeony kim
"""

import requests

API_KEY = "YOUR_API_TOKEN"
LAKE_ID = "YOUR_LAKE_ID"
CLOUD_VENDOR="CLOUD_VENDOR"
CLOUD_REGION="CLOUD_REGION"

URL = f"https://{CLOUD_VENDOR}.{CLOUD_REGION}.machlake.com/v1/lakes/{LAKE_ID}/values"

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

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 = {
    "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'
}

response = requests.get(URL, headers=headers,  params=params, verify=False)
print(response.content.decode('utf-8'))

# 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]
#         ]
#     }
# }

MachLake

Pivoted Value Select

Analytics

HTTP Request

Parameters

Request Example

Response Example

Sample Code

Related content