ํด์ฌ๊น์ง ์ผ๋ง ๋จ์ ์๊ฐ์ ์ผ๋ง๋ ์ค์ฉ์ ์ธ ์ ๋ฌด๋ฅผ ์ ๊ณตํ ์ ์์๊น? ๋ง์ง๋ง ์ง์ฅ์ธ์ผ๋ก ์ปค๋ฆฌ์ด๋ฅผ ์๋ ์ผ์ด์๋ค. ํญ์ ๋ชจ๋ ์ผ์ ์์ด์ ๋ฐ์ดํฐ๋ฅผ ๋ถ์ํ๋ ์ผ์ด ๊ฐ์ฅ ์ค์ํ๋ค๊ณ ์๊ฐํ๋ค. ๋ฐ์ดํฐ ๊ณผํ์, ๋ฐ์ดํฐ ๋ถ์๊ฐ ๋ฑ ๋ง์ ํ์ด ์๊ธธ ์ ๋๋ '๋ผ๋ผ'๋ DB ์ค๊ณํ๋ฉฐ ๊ทธ๋ฅ ํ๋ ์ผ์ธ๋ฐ ๐ญ , ์์ฆ์ ์ธ๋ถํ๋๊ณ ์ ๊ท๊ณผ์ ์ ์๋ฐ์ผ๋ฉด ํด๋น ์ ๋ฌด๋ฅผ ๋งก๊ธด ์ด๋ ค์ฐ๋ ์ฐธ์ผ๋ก ์ฌํํ์ค์ด๋ค.
ํ์ง๋ง ๋ชจ๋ ํ์ฌ์ ๊ทธ๋งํผ ๋๋ํ ์ธ๋ ฅ์ด ๋ฐฐ์น๊ฐ ๋๋ค๊ณ ๋ ์๊ฐํ๋ค. ์ฌํํผ ์
๋ฌด๋ฅผ ํ๋ค๋ณด๋ ์ข
์ข
๋ฆฌ๋๊ฐ ์๊ตฌํ๋ ์งํ๊ฐ ์๋ค. ๋ชจ๋ํฐ๋ง ์๋น์ค๊ฐ ๊ฐ๋
์ฑ์ด ๋จ์ด์ ธ ์ฃผ๊ธฐ์ ์ธ ๋ฆฌํฌํ
๋ฐฉ๋ฒ์ ์๊ฐํ๊ฒ ๋์๋ค.
Datadog Log & ImportJson
- Aggregate ์ฌ์ฉ API : https://api.datadoghq.com/api/v2/logs/analytics/aggregate
- ImportJson : https://gist.github.com/chrislkeller/5719258
- Importjson์ ์์ค ๋ณต์ฌํด์ ์ฌ์ฉํ๋ฉด ๋๊ธฐ ๋๋ฌธ์ ๊ธฐ์ฌํ์ง ์์๋ค
function analyticsAggregate(querystring,from,to,pagelimit,groupBy,parseOptions) {
var parseOptions = "";
var url = "https://api.datadoghq.com/api/v2/logs/analytics/aggregate";
var query = "/data/buckets/computes/c0,/data/buckets/by/@groubyํ๋๋ช
";
/* ํ
์คํธ ์ํ
querystring = ๊ฒ์ ์กฐ๊ฑด"
from = "2022-01-11";
to = "2022-02-11";
pagelimit = "50";
groupBy = "groupbyํ๋๋ช
";
*/
from = dateToUnixtime(from);
to = dateToUnixtime(to);
var body = JSON.stringify({
"filter": {
"query": querystring,
"from": from,
"to": to ,
"indexes": [
"*"
]
},
"group_by": [
{
"facet": groupBy,
"limit": pagelimit,
"sort":{"metric":"count","aggregation":"count","type":"measure"}
}
]
});
var requestOptions = {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'DD-API-KEY': APIKEY,
'DD-APPLICATION-KEY': APPKEY
},
'payload': body,
};
var data = ImportJSONAdvanced(url, requestOptions, query, parseOptions, includeXPath_, defaultTransform_) ;
Logger.log(data); //๋ฐ์ดํฐ๊ฐ ๊ถ๊ธํ ๋ ์ฐ๋ ๋ฒ
var arrOrder = new Array() ;
var count = new Array() ;
for (var i = 0; i < data[0].length; i++) {
for(var j = 0; j < data.length; j++){
if(data[0][i] == "by/groupbyํ๋๋ช
"){
if(data[j][i] != "groupbyํ๋๋ช
" && arrOrder.indexOf(data[j][i]) == -1){
arrOrder.push(data[j][i]);
Logger.log(data[j][i]);
}
}
if(data[0][i] == "computes/c0"){
count.push(data[j][i]);
Logger.log(data[j][i]);
}
}
}
var reportValue = new Array();
reportValue.push(arrOrder);
reportValue.push(count);
/* ์ด์ฐจ์ ๋ฐฐ์ด ์ธ๋ก๋ก ๋ณํ */
var transposed = Array.from({ length:arrOrderAgencyId.length }, () => new Array(2).fill(0))
for(var i = 0; i < data[0].length; i++) {
for(var j = 0; j < data.length; j++) {
[transposed[j][i]] = [reportValue[i][j]];
}
}
transposed[0].splice(0,1,"arrOrder");
transposed[0].splice(1,1,"Count");
return transposed;
}
* ์ฐธ๊ณ ์ฌํญ
//unixtimestamp
function dateToUnixtime(date){
var d = new Date(date);
return d.getTime();
}
Google APP Script
- ์น ์ฑ ๋งํฌ : https://developers.google.com/apps-script/guides/web
- ํ์ดํ์ ์คํ๋ ๋์ํธ ์์์ค ์ด๋ผ๊ณ ์๊ฐํ๋ฉด ๋๋ค
- ์์ค ๋ด์ฉ : ์ด๋ ค์๋ ๊ตฌ๊ธ ์คํ๋ ๋ ์ํธ์ ๋ฐ์ดํฐ๋ฅผ ์ฐจํธํ ํ๋ค.
- ๋๋ ํ์ 3์นธ ์ดํ๋ถํฐ ๋ฐ์ดํฐ๋ฅผ ๋ถ๋ฌ์์ TITLE ์ด๋ผ๋ ์์๋ฅผ ์ ์ํ๋ค.
const SHEET_NAME = "์ํธ๋ช
";
const INFO = "์ฐพ๊ณ ์ํ๋ ์ ๋ณด";
const TITLE = 3;
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// ์ถํ์ ํ์ฉํ ์์ ์ด๋ค
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
function doGet() {
return HtmlService
.createTemplateFromFile('Index')
.evaluate();
}
function getDataUntactMemoSheet(){
var a = "";
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(SHEET_NAME);
var headers = sheet.getRange(TITLE, 1, 1, sheet.getLastColumn()).getValues()[0];
//Find column by name
column = headers.indexOf(INFO);
Logger.log(column);
Logger.log(sheet.getLastRow());
var data = sheet.getRange(TITLE + 1, column + 1, sheet.getLastRow()-1, 2).getValues();
var results = [];
//Get data and parse it to Chart.js format
for (row in data) {
if(data[row][0]!="")
{
results.push({
"x": data[row][0],
"y": data[row][1]
})
}
}
Logger.log(results); //Logger is very usefull for debugging.
return results;
}
์ฐจํธ ์คํฌ๋ฆฝํธ : Chart.Js
- ์ฐจํธ ๋งํฌ : https://www.chartjs.org/docs/latest/
- ์ฐจํธ ์คํฌ๋ฆฝํธ๋ ์ฌ๊ธฐ์ ๊ธฐ ์์ ๋ฅผ ๋ณด๋ฉด ๊ธ๋ฐฉ ๋ฐ๋ผ ํ ์ ์๋ค. ๋ค๋ง ์ดํดํ๊ณ ์ปค์คํ ํ๋๊ฒ ์กฐ๊ธ ์๊ฐ์ด ๊ฑธ๋ฆฐ๋ค
- Chart.js ์ธ๋ ์ ์ฉํ ์๋ฐ์คํฌ๋ฆฝํธ Parsing Tool https://beautifier.io/
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<link href="https://unpkg.com/survey-jquery@1.8.21/modern.css" type="text/css" rel="stylesheet" />
<title>Chart.js and Google Sheets example</title>
</head>
<!-- ๋ณธ๋ฌธ์์ -->
<body>
<main role="main" class="container">
<h3>์ ํด์ฌ ์ฃผ๋ฌธ์ ์ API ํ๋๋ณ ๋ฐ์ดํฐ ๋ชจ๋ํฐ๋ง</h3><br/>
<!-- ์ฐจํธ ๊ทธ๋ฆฌ๋ canvas ์์
-->
<div id="myChartContainer" style="width:600px height:400px">
<canvas id="noUntact" width="600" height="400"></canvas>
</div>
</main>
<script src="https://code.jquery.com/jquery-3.3.1.min.js" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.min.js" integrity="sha512-d9xgZrVZpmmQlfonhQUvTR7lMPtO7NkZMkA0ABN3PHCbKA5nqylQ/yWlFAyY6hYgdF1Qh6nYiuADWwKB4C2WSw==" crossorigin="anonymous"></script>
<!-- ๋ฐ์ดํฐ ๋ฐ๋ ๋ ๋ค์ ๊ทธ๋ฆฌ๋ ์์
-->
<script>
$("#noUntact").remove();
$("#myChartContainer").append('<canvas id="noUntact"></canvas>');;
var ctx = $("#noUntact");
var HISTOGRAM = JSON.parse(<?= JSON.stringify(getDataUntactMemoSheet()); ?>);
console.log(HISTOGRAM.columns);
var myChart = new Chart(ctx, {
type: 'bar',
data: {
labels: HISTOGRAM.map(function(r) {return r.x}),
datasets: [{
label: '๋ฉ๋ชจ ์ฃผ๋ฌธ์',
minBarLength: 10,
data: HISTOGRAM,
backgroundColor: [
'rgba(255, 99, 132, 0.2)',
'rgba(54, 162, 235, 0.2)',
'rgba(255, 206, 86, 0.2)',
'rgba(75, 192, 192, 0.2)',
'rgba(153, 102, 255, 0.2)',
'rgba(255, 159, 64, 0.2)'
],
borderColor: [
'rgba(255, 99, 132, 1)',
'rgba(54, 162, 235, 1)',
'rgba(255, 206, 86, 1)',
'rgba(75, 192, 192, 1)',
'rgba(153, 102, 255, 1)',
'rgba(255, 159, 64, 1)'
],
borderWidth: 1
}]
},
options: {
// ์ปจํ
์ด๋๊ฐ ์ํ ํ ๋ ์ฐจํธ ์บ๋ฒ์ค์ ํฌ๊ธฐ๋ฅผ ์กฐ์ (dafalut : true)
responsive: true,
// ํฌ๊ธฐ ์กฐ์ ์ด๋ฒคํธ ํ ์ ํฌ๊ธฐ๋ก ์ ๋๋ฉ์ด์
ํ๋ ๋ฐ ๊ฑธ๋ฆฌ๋ ์๊ฐ(๋ฐ๋ฆฌ ์ด) (defalut : 0)
responsiveAnimationDuration: 1000,
// (width / height) ํฌ๊ธฐ๋ฅผ ์กฐ์ ํ ๋ ์๋ ์บ๋ฒ์ค ์ข
ํก๋น๋ฅผ ์ ์ง (defalut : true)
maintainAspectRatio: true,
// ์บ๋ฒ์ค ์ข
ํก๋น( width / height, ์ ์ฌ๊ฐํ ์บ๋ฒ์ค๋ฅผ ๋ํ๋ด๋ ๊ฐ) ๋์ด๊ฐ ์์ฑ์ผ๋ก ๋๋ ์คํ์ผ ํตํด ๋ช
์์ ์ผ๋ก ์ ์๋ ๊ฒฝ์ฐ์ด ์ต์
์ ๋ฌด์
aspectRatio: 2,
// ํฌ๊ธฐ ์กฐ์ ์ด ๋ฐ์ํ ๋ ํธ์ถ
onResize: function () {
console.log('onResize');
},
//ํดํ
tooltips: {
mode: 'index', intersect: false,
},
//๋ง์ฐ์ค์ค๋ฒ
hover: {
mode: 'nearest', intersect: true
},
//x,y์ถ ๋ผ์ธ ๋ฐ ์์น
scales: {
xAxes: [{ gridLines: { display: true }, }],
yAxes: [{ gridLines: { drawBorder: false},
ticks: { display: true, max: 400000, min: 0 } }]
},
//์ ๋๋ฉ์ด์
ํจ๊ณผ
animation:false,
showValue:{
fontStyle: 'Helvetica', //Default Arial
fontSize: 20
}
}
});
</script>
</body>
</html>
๊ฒฐ๊ณผ๋ฌผ
ํดํ๋ ๋จ๊ณ ๊ฐ ์ฐจํธ x,y์ถ ์ค์ ๋ ๊ฐ๋ฅํ๋ค. ์์๋ x์ถ๋ณ๋ก ์ง์ ์ด ๊ฐ๋ฅํ์ง๋ง ํ๋์๊ฐ ๋ง์ผ๋ฉด ๊ท์ฐฎ๋ค. ํ๋ค๋ณด๋ ์ฌ๋๋ค์ ์ github์ ์์ค ์ ๋ฆฌํ๋์ง ์๊ฒ ๋ค. ์ด ๊ธด๊ธ์ ์ฝ์ผ๋ ค๋ ํผ๋ก๋๊ฐ ๋๋ค. ๋๋ ์ด ์ฐธ์ ํฌํธํด๋ฆฌ์ค์ฉ github์ ๋ง๋ค์ด์ผ๊ฒ ๋ค.
๋๊ธ