專案

一般

配置概況

下載 (16.1 KB)

工作單 #60 » GrafanaETL.java

marlboro chu, 2025-02-13 02:23

 
package mytest;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.client.RestTemplate;

import com.fasterxml.jackson.core.exc.StreamWriteException;
import com.fasterxml.jackson.databind.DatabindException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;

public class GrafanaETL {

private static ObjectMapper objectMapper = new ObjectMapper();
private static Connection conn;

private static final String DB_URL = "jdbc:mysql://192.168.10.209:3306/dev_grafana";
private static final String DB_USERNAME = "grafana";
private static final String DB_PASSWORD = "grafana16313302";

private static final String GRAFANA_A_URL = "https://gfn.veri-id-prd.com";
private static final String GRAFANA_B_URL = "http://192.168.10.209:3001";
private static final String API_KEY_A = "Bearer glsa_JBbC5bQZ0Jpj1N0B5cW89oIf6zckwH52_8c600c43"; // Grafana A 的 API
// Key
private static final String API_KEY_B = "Basic YWRtaW46YWRtaW4xNjMxMzMwMg=="; // Grafana B 的 API Key
private static final RestTemplate restTemplate = new RestTemplate();

private static final String BACKUP_FOLDER = "D:\\TEMP\\PRD\\";

public static void main(String args[]) {

try {
conn = getConnection();
// backupAllFolders();
// backupAllDashboardAndLibs();
// backupDataSource();

// importAllFolders();
// importAllDataSource();

// importAllLibrary();
importAllDashboard();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}

public static void backupAllFolders() throws Exception {

List<Map<String, Object>> folders = getAllFoldersFromGrafanaA();
ObjectMapper objectMapper = new ObjectMapper();
String json = objectMapper.writeValueAsString(folders);
writeToFile(BACKUP_FOLDER + "//folders.json", json);

}

public static void importAllFolders() {

try {
String deleteSQL = "delete from dashboard le where is_folder = 1";
Statement stat = conn.createStatement();
stat.execute(deleteSQL);
stat.close();
// 1️⃣ 取得 Grafana A 的所有資料夾
List<Map<String, Object>> folders = getAllFoldersFromGrafanaA();

// 2️⃣ 匯入資料夾到 Grafana B
for (Map<String, Object> folder : folders) {
createFolderInGrafanaB(folder);
}

System.out.println("✅ 資料夾同步完成！");

} catch (Exception e) {
e.printStackTrace();
}
}

public static List getAllFoldersFromGrafanaA() throws Exception {

String url = GRAFANA_A_URL + "/api/folders";
HttpHeaders headers = new HttpHeaders();
headers.set("Authorization", API_KEY_A);
headers.setContentType(MediaType.APPLICATION_JSON);
HttpEntity<String> entity = new HttpEntity<>(headers);

ResponseEntity<List> response = restTemplate.exchange(url, HttpMethod.GET, entity, List.class);
if (response.getStatusCode() == HttpStatus.OK) {
return (response.getBody());
// writeToFile(BACKUP_FOLDER + "//folders.json", new
// JSONArray(response.getBody()).toString());
}
return Collections.emptyList();
}

public static void createFolderInGrafanaB(Map<String, Object> folder) throws Exception {

String url = GRAFANA_B_URL + "/api/folders";
HttpHeaders headers = new HttpHeaders();
headers.set("Authorization", API_KEY_B);
headers.setContentType(MediaType.APPLICATION_JSON);

// 準備要傳遞的 JSON 資料
Map<String, Object> requestBody = new HashMap<>();
requestBody.put("title", folder.get("title")); // 設定資料夾名稱
requestBody.put("uid", folder.get("uid")); // 保留原始 UID

HttpEntity<Map<String, Object>> entity = new HttpEntity<>(requestBody, headers);
ResponseEntity<String> response = restTemplate.exchange(url, HttpMethod.POST, entity, String.class);

if (response.getStatusCode() == HttpStatus.OK || response.getStatusCode() == HttpStatus.CONFLICT) {
System.out.println("✅ 匯入資料夾: " + folder.get("title"));
String deletePermission = "delete from permission p where "
+ "`scope` like '%"+folder.get("uid")+"%' "
+ " and (role_id = 2 or role_id = 3)";
Statement stat = conn.createStatement();
stat.execute(deletePermission);
stat.close();
} else {
System.out.println("❌ 無法匯入資料夾: " + folder.get("title") + "，錯誤：" + response.getBody());
}

}

public static void backupAllDashboardAndLibs() {
try {

RestTemplate restTemplate = new RestTemplate();
HttpHeaders headers = new HttpHeaders();
headers.set("Authorization", API_KEY_A);
headers.setContentType(MediaType.APPLICATION_JSON);

String searchUrl = GRAFANA_A_URL + "/api/search?type=dash-db";
HttpEntity<String> request = new HttpEntity<>(headers);
ResponseEntity<String> searchResponse = restTemplate.exchange(searchUrl, HttpMethod.GET, request,
String.class);

ObjectMapper objectMapper = new ObjectMapper();
JsonNode searchResult = objectMapper.readTree(searchResponse.getBody());

for (JsonNode dashboard : searchResult) {

String dashboardUid = dashboard.get("uid").asText();

String dashboardTitle = dashboard.get("title").asText();

String dashboardUrl = GRAFANA_A_URL + "/api/dashboards/uid/" + dashboardUid;
ResponseEntity<String> dashboardResponse = restTemplate.exchange(dashboardUrl, HttpMethod.GET, request,
String.class);
JsonNode dashboardJson = objectMapper.readTree(dashboardResponse.getBody());

writeToFile(BACKUP_FOLDER + "dashboard\\" + dashboardTitle + ".json", dashboardJson);

JsonNode panels = dashboardJson.path("dashboard").path("panels");
for (JsonNode panel : panels) {

if (panel.has("libraryPanel")) {

String libraryPanelUid = panel.get("libraryPanel").get("uid").asText();
System.out.println("Found library panel with UID: " + libraryPanelUid);

String libraryElementsUrl = GRAFANA_A_URL + "/api/library-elements/" + libraryPanelUid;

HttpEntity<String> libraryRequest = new HttpEntity<>(headers);
ResponseEntity<String> libraryResponse = restTemplate.exchange(libraryElementsUrl,
HttpMethod.GET, libraryRequest, String.class);

String resBody = libraryResponse.getBody();

JsonNode libraryElementsJson = objectMapper.readTree(resBody);

writeToFile(BACKUP_FOLDER + "library\\"
+ libraryElementsJson.path("result").get("name").asText() + ".json",
libraryElementsJson);

}
}

}

} catch (Exception e) {
e.printStackTrace();
}
}

private static void backupDataSource() throws Exception {

String urlA = GRAFANA_A_URL + "/api/datasources";
HttpHeaders headers = new HttpHeaders();
headers.set("Authorization", API_KEY_A);
headers.setContentType(MediaType.APPLICATION_JSON);
HttpEntity<String> entity = new HttpEntity<>(headers);

ResponseEntity<String> response = restTemplate.exchange(urlA, HttpMethod.GET, entity, String.class);
if (response.getStatusCode() == HttpStatus.OK) {
System.out.println(response.getBody());
writeToFile(BACKUP_FOLDER + "//datasource.json", new JSONArray(response.getBody()).toString());
}

}

public static void importAllDataSource() {

try {

String deleteSQL = "delete from data_source";
Statement stat = conn.createStatement();
stat.execute(deleteSQL);
stat.close();
String datasourceList = getAllDataSourceFromGrafanaA();

JsonNode dataSources = objectMapper.readTree(datasourceList);

for (JsonNode datasource : dataSources) {
createDataSourceInGrafanaB(datasource);
}

System.out.println("✅ DataSource同步完成！");

} catch (Exception e) {
e.printStackTrace();
}
}

public static String getAllDataSourceFromGrafanaA() throws Exception {

String url = GRAFANA_A_URL + "/api/datasources";
HttpHeaders headers = new HttpHeaders();
headers.set("Authorization", API_KEY_A);
headers.setContentType(MediaType.APPLICATION_JSON);
HttpEntity<String> entity = new HttpEntity<>(headers);

ResponseEntity<String> response = restTemplate.exchange(url, HttpMethod.GET, entity, String.class);
if (response.getStatusCode() == HttpStatus.OK) {
return (response.getBody());
}
return "";
}

public static void createDataSourceInGrafanaB(JsonNode datasource) throws Exception {

String url = GRAFANA_B_URL + "/api/datasources";
HttpHeaders headers = new HttpHeaders();
headers.set("Authorization", API_KEY_B);
headers.setContentType(MediaType.APPLICATION_JSON);

((ObjectNode) datasource).remove("id");
String dataSourceJson = objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(datasource);

HttpEntity<String> entity = new HttpEntity<>(dataSourceJson, headers);
ResponseEntity<String> response = restTemplate.exchange(url, HttpMethod.POST, entity, String.class);

if (response.getStatusCode() == HttpStatus.OK || response.getStatusCode() == HttpStatus.CONFLICT) {
System.out.println("✅ 匯入DataSource: " + datasource.get("name").asText());
} else {
System.out.println("❌ 無法匯入DataSource: " + datasource.get("name").asText() + "，錯誤：" + response.getBody());
}

}

public static void importAllLibrary() throws Exception {

String folderPath = BACKUP_FOLDER + "library\\";
System.out.println(folderPath);
File dir = new File(folderPath);
if (dir.isDirectory()) {
File[] fs = dir.listFiles();
for (int i = 0; i < fs.length; i++) {
importLibrary(fs[i]);
}
}

}

public static void importLibrary(File f) throws Exception {

String titleName = "";
try {

String jsonStr = readFile(new FileInputStream(f), "UTF-8");
JSONObject jsonObject = new JSONObject(jsonStr).getJSONObject("result");

titleName = jsonObject.getString("name");

ObjectMapper objectMapper = new ObjectMapper();
Map<String, Object> jsonMap = jsonObject.getJSONObject("model").toMap();
String jsonModel = objectMapper.writeValueAsString(jsonMap);

String deleteSQL = "DELETE FROM library_element WHERE uid = ?";
PreparedStatement pstmt = conn.prepareStatement(deleteSQL);
pstmt.setString(1, jsonObject.getString("uid"));
pstmt.executeUpdate();
pstmt.close();

String insertSQL = "INSERT INTO library_element "
+ "(org_id, folder_id, uid, name, kind, type, description, model, created, created_by, updated, updated_by, version) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, ?, CURRENT_TIMESTAMP, ?, ?)";

pstmt = conn.prepareStatement(insertSQL);
pstmt.setInt(1, jsonObject.getInt("orgId"));
pstmt.setInt(2, 0);
pstmt.setString(3, jsonObject.getString("uid"));
pstmt.setString(4, jsonObject.getString("name"));
pstmt.setInt(5, jsonObject.getInt("kind"));
pstmt.setString(6, jsonObject.getString("type"));
pstmt.setString(7, ""); // description 預設為空
pstmt.setString(8, jsonModel); // **正確存入 `MEDIUMTEXT` 欄位**
pstmt.setInt(9, 1); // created_by 預設為 1
pstmt.setInt(10, 1); // updated_by 預設為 1
pstmt.setInt(11, 1); // version 預設為 1

pstmt.executeUpdate();
pstmt.close();

String updateSQL = "UPDATE library_element SET folder_id = (SELECT id FROM dashboard WHERE uid = ?) WHERE uid = ?";
pstmt = conn.prepareStatement(updateSQL);
pstmt.setString(1, jsonObject.getString("folderUid"));
pstmt.setString(2, jsonObject.getString("uid"));
pstmt.executeUpdate();
pstmt.close();

System.out.println("✅ 匯入 Library: " + titleName);

} catch (Exception e) {
e.printStackTrace();
System.out.println("insert " + titleName + " failed.");
}
}

public static void importAllDashboard() throws Exception {

String deleteSQL = "delete from dashboard le where is_folder = 0";
Statement stat = conn.createStatement();
stat.execute(deleteSQL);
stat.close();
String folderPath = BACKUP_FOLDER + "dashboard\\";
System.out.println(folderPath);
File dir = new File(folderPath);
if (dir.isDirectory()) {
File[] fs = dir.listFiles();
for (int i = 0; i < fs.length; i++) {
importDashboard(fs[i]);
}
}

}
private static void importDashboard(File f) {

String title = "";
try {

String dashboardString = readFile(new FileInputStream(f), "UTF-8");
JSONObject metaObj = new JSONObject(dashboardString).getJSONObject("meta");
JSONObject dashboardObj = new JSONObject(dashboardString).getJSONObject("dashboard");
dashboardObj.remove("id");
title = dashboardObj.getString("title");
JSONObject importObj = new JSONObject();

importObj.put("dashboard", dashboardObj);
importObj.put("folderUid", metaObj.getString("folderUid"));
importObj.put("overwrite", true);
importObj.put("inputs", new JSONArray());
importObj.put("version", 1);

String urlB = GRAFANA_B_URL + "/api/dashboards/import";
HttpHeaders headersB = new HttpHeaders();
headersB.set("Authorization", API_KEY_B);
headersB.setContentType(MediaType.APPLICATION_JSON);

String importString = importObj.toString();
//String importString = importObj.toString().replaceAll("\"collapsed\" : false", "\"collapsed\" : true");
HttpEntity<String> entityB = new HttpEntity<>(importString, headersB);

ResponseEntity<String> responseB = restTemplate.exchange(urlB, HttpMethod.POST, entityB, String.class);

if (responseB.getStatusCode() == HttpStatus.OK || responseB.getStatusCode() == HttpStatus.CREATED) {
System.out.println("✅ 匯入 Dashboard: " + dashboardObj.get("title"));
String deletePermission = "delete from permission p where "
+ "`scope` like '%"+dashboardObj.getString("uid")+"%' "
+ " and (role_id = 2 or role_id = 3)";
Statement stat = conn.createStatement();
stat.execute(deletePermission);
stat.close();
} else {
System.out.println(
"❌ 無法匯入 Dashboard: " + dashboardObj.get("title") + "，錯誤：" + responseB.getBody());
}

} catch (Exception e) {
System.out.println(
"❌ 無法匯入 Dashboard: " + title + "，錯誤：");
e.printStackTrace();
}

}

private static void writeToFile(String fileName, String data)
throws StreamWriteException, DatabindException, IOException {

ObjectMapper objectMapper = new ObjectMapper();
File outputFile = new File(fileName);
if (!outputFile.getParentFile().exists()) {
outputFile.getParentFile().mkdirs();
}
BufferedWriter writer = new BufferedWriter(new FileWriter(fileName));
writer.write(data);
writer.flush();
writer.close();

}

private static void writeToFile(String fileName, JsonNode data)
throws StreamWriteException, DatabindException, IOException {

ObjectMapper objectMapper = new ObjectMapper();
File outputFile = new File(fileName);
if (!outputFile.getParentFile().exists()) {
outputFile.getParentFile().mkdirs();
}
objectMapper.writerWithDefaultPrettyPrinter().writeValue(outputFile, data);
System.out.println("Dashboard saved to file: " + outputFile.getName());

}

public static String readFile(InputStream fis, String encoding) {

StringBuffer sb = new StringBuffer();

try {

BufferedReader in = new BufferedReader(new InputStreamReader(fis, encoding));
try {
String data = null;
while ((data = in.readLine()) != null) {
sb.append(data);
}
} finally {
in.close();
fis.close();
}
} catch (Exception e) {
e.printStackTrace();
}
return sb.toString();
}

private static Connection getConnection() throws Exception {

Class.forName("com.mysql.cj.jdbc.Driver");
return DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

}
}
    (1-1/1)