We provide all the information about MCP servers via our MCP API.
curl -X GET 'https://glama.ai/api/mcp/v1/servers/okybaguslukmana/superset-mcp'
If you have feedback or need assistance with the MCP directory API, please join our Discord server
import axios, { AxiosInstance, AxiosError } from 'axios';
// Helper function to log axios errors with proper typing
function logAxiosError(context: string, error: unknown): void {
if (axios.isAxiosError(error)) {
const axiosError = error as AxiosError;
if (axiosError.response) {
console.error(`${context}. Status:`, axiosError.response.status);
console.error('Error response:', JSON.stringify(axiosError.response.data, null, 2));
} else {
console.error(`${context}:`, axiosError.message);
}
} else {
console.error(`${context}:`, error);
}
}
export interface Dashboard {
id: number;
dashboard_title: string;
slug: string;
url: string;
published: boolean;
}
export interface Chart {
id: number;
slice_name: string;
viz_type: string;
datasource_id: number;
datasource_type: string;
}
export interface Dataset {
id: number;
table_name: string;
schema: string | null;
database: { database_name: string; id: number };
columns: Column[];
}
export interface Column {
column_name: string;
type: string;
is_dttm: boolean;
filterable: boolean;
groupby: boolean;
}
export interface QueryResult {
columns: string[];
data: Record<string, unknown>[];
query_id: number;
status: string;
}
export class SupersetClient {
private client: AxiosInstance;
private accessToken: string | null = null;
private refreshToken: string | null = null;
private csrfToken: string | null = null;
private sessionCookies: string[] = []; // Store session cookies for CSRF
private baseUrl: string;
private username: string;
private password: string;
private isRefreshing: boolean = false;
private refreshSubscribers: Array<(token: string) => void> = [];
// Dashboard update locks to prevent race conditions
private dashboardLocks: Map<string | number, Promise<void>> = new Map();
constructor(baseUrl: string, username: string, password: string) {
this.baseUrl = baseUrl;
this.username = username;
this.password = password;
this.client = axios.create({
baseURL: baseUrl,
headers: {
'Content-Type': 'application/json',
},
});
// Add response interceptor to handle token expiration
this.client.interceptors.response.use(
(response) => response,
async (error: AxiosError) => {
const originalRequest = error.config as typeof error.config & { _retry?: boolean };
// If error is 401 and we haven't retried yet
if (error.response?.status === 401 && originalRequest && !originalRequest._retry) {
console.log('🔄 Token expired, attempting to refresh...');
if (this.isRefreshing) {
// If already refreshing, wait for the new token
return new Promise((resolve) => {
this.refreshSubscribers.push((token: string) => {
originalRequest.headers['Authorization'] = `Bearer ${token}`;
resolve(this.client(originalRequest));
});
});
}
originalRequest._retry = true;
this.isRefreshing = true;
try {
await this.refreshAccessToken();
this.isRefreshing = false;
// Notify all subscribers about the new token
this.refreshSubscribers.forEach((callback) => callback(this.accessToken!));
this.refreshSubscribers = [];
// Retry the original request with new token
originalRequest.headers['Authorization'] = `Bearer ${this.accessToken}`;
// Also refresh CSRF token for mutating requests
if (['post', 'put', 'patch', 'delete'].includes(originalRequest.method?.toLowerCase() || '')) {
await this.fetchCsrfToken();
originalRequest.headers['X-CSRFToken'] = this.csrfToken || '';
if (this.sessionCookies.length > 0) {
originalRequest.headers['Cookie'] = this.sessionCookies.join('; ');
}
}
console.log('✅ Token refreshed successfully, retrying request...');
return this.client(originalRequest);
} catch (refreshError) {
this.isRefreshing = false;
this.refreshSubscribers = [];
console.error('❌ Failed to refresh token:', refreshError);
throw refreshError;
}
}
return Promise.reject(error);
}
);
}
private async ensureAuthenticated(): Promise<void> {
if (!this.accessToken) {
await this.login();
}
}
private async fetchCsrfToken(): Promise<void> {
try {
const response = await this.client.get('/api/v1/security/csrf_token/', {
headers: this.getAuthHeaders(),
});
this.csrfToken = response.data.result;
// Capture session cookies from the response
const setCookieHeaders = response.headers['set-cookie'];
if (setCookieHeaders && Array.isArray(setCookieHeaders)) {
this.sessionCookies = setCookieHeaders.map(cookie => cookie.split(';')[0]);
console.log('✅ Session cookies captured:', this.sessionCookies.length, 'cookies');
}
console.log('✅ CSRF token fetched successfully');
} catch (error) {
console.error('⚠️ Failed to fetch CSRF token:', error);
}
}
private getAuthHeaders(): Record<string, string> {
return {
Authorization: `Bearer ${this.accessToken}`,
};
}
private async getAuthHeadersWithCsrf(): Promise<Record<string, string>> {
if (!this.csrfToken) {
await this.fetchCsrfToken();
}
const headers: Record<string, string> = {
Authorization: `Bearer ${this.accessToken}`,
'X-CSRFToken': this.csrfToken || '',
};
// Include session cookies if available
if (this.sessionCookies.length > 0) {
headers['Cookie'] = this.sessionCookies.join('; ');
}
return headers;
}
async login(): Promise<void> {
try {
const response = await this.client.post('/api/v1/security/login', {
username: this.username,
password: this.password,
provider: 'db',
});
this.accessToken = response.data.access_token;
this.refreshToken = response.data.refresh_token;
console.log('✅ Successfully authenticated with Superset');
} catch (error) {
console.error('❌ Failed to authenticate with Superset:', error);
throw new Error('Authentication failed');
}
}
async refreshAccessToken(): Promise<void> {
if (!this.refreshToken) {
await this.login();
return;
}
try {
const response = await this.client.post(
'/api/v1/security/refresh',
{},
{ headers: { Authorization: `Bearer ${this.refreshToken}` } }
);
this.accessToken = response.data.access_token;
} catch {
await this.login();
}
}
// ============ DASHBOARD OPERATIONS ============
async listDashboards(): Promise<Dashboard[]> {
await this.ensureAuthenticated();
try {
const response = await this.client.get('/api/v1/dashboard/', {
headers: this.getAuthHeaders(),
params: { q: JSON.stringify({ page_size: 100 }) },
});
return response.data.result.map((d: Record<string, unknown>) => ({
id: d.id,
dashboard_title: d.dashboard_title,
slug: d.slug,
url: d.url,
published: d.published,
}));
} catch (error) {
console.error('Failed to list dashboards:', error);
throw error;
}
}
async getDashboard(idOrSlug: string | number): Promise<Dashboard & { charts: Chart[] }> {
await this.ensureAuthenticated();
try {
const [dashboardRes, chartsRes] = await Promise.all([
this.client.get(`/api/v1/dashboard/${idOrSlug}`, {
headers: this.getAuthHeaders(),
}),
this.client.get(`/api/v1/dashboard/${idOrSlug}/charts`, {
headers: this.getAuthHeaders(),
}),
]);
return {
...dashboardRes.data.result,
charts: chartsRes.data.result || [],
};
} catch (error) {
console.error('Failed to get dashboard:', error);
throw error;
}
}
async createDashboard(title: string, slug?: string): Promise<Dashboard> {
await this.ensureAuthenticated();
try {
const response = await this.client.post(
'/api/v1/dashboard/',
{
dashboard_title: title,
slug: slug || title.toLowerCase().replace(/\s+/g, '-'),
published: false,
},
{ headers: await this.getAuthHeadersWithCsrf() }
);
return response.data.result;
} catch (error: unknown) {
logAxiosError('Failed to create dashboard', error);
throw error;
}
}
async addChartToDashboard(dashboardId: number | string, chartId: number): Promise<{ success: boolean; message: string }> {
await this.ensureAuthenticated();
// Wait for any existing operation on this dashboard to complete (mutex lock)
const existingLock = this.dashboardLocks.get(dashboardId);
if (existingLock) {
console.log(`⏳ Waiting for previous dashboard update to complete for dashboard ${dashboardId}...`);
await existingLock;
}
// Create a new lock for this operation
let releaseLock: () => void;
const lockPromise = new Promise<void>((resolve) => {
releaseLock = resolve;
});
this.dashboardLocks.set(dashboardId, lockPromise);
try {
// Get current dashboard to retrieve json_metadata
const dashboardRes = await this.client.get(`/api/v1/dashboard/${dashboardId}`, {
headers: this.getAuthHeaders(),
});
const dashboard = dashboardRes.data.result;
// Get chart info to get the slice name
let chartName = `Chart ${chartId}`;
try {
const chartRes = await this.client.get(`/api/v1/chart/${chartId}`, {
headers: this.getAuthHeaders(),
});
chartName = chartRes.data.result.slice_name || chartName;
} catch {
console.warn('⚠️ Could not fetch chart name, using default');
}
// Parse existing json_metadata or create new one
let jsonMetadata: Record<string, unknown> = {};
if (dashboard.json_metadata) {
try {
jsonMetadata = typeof dashboard.json_metadata === 'string'
? JSON.parse(dashboard.json_metadata)
: dashboard.json_metadata;
} catch {
console.warn('⚠️ Failed to parse existing json_metadata, creating new');
jsonMetadata = {};
}
}
// Initialize or get positions from json_metadata
let positions: Record<string, unknown> = {};
if (jsonMetadata.positions) {
positions = jsonMetadata.positions as Record<string, unknown>;
}
// Initialize required dashboard metadata if not exists
if (!positions['DASHBOARD_VERSION_KEY']) {
positions['DASHBOARD_VERSION_KEY'] = 'v2';
}
if (!positions['ROOT_ID']) {
positions['ROOT_ID'] = { type: 'ROOT', id: 'ROOT_ID', children: ['GRID_ID'] };
}
if (!positions['GRID_ID']) {
positions['GRID_ID'] = { type: 'GRID', id: 'GRID_ID', children: [], parents: ['ROOT_ID'] };
}
if (!positions['HEADER_ID']) {
positions['HEADER_ID'] = {
id: 'HEADER_ID',
type: 'HEADER',
meta: { text: dashboard.dashboard_title || 'Dashboard' }
};
}
// Generate unique random IDs for new chart components (similar to Superset format)
const randomId = () => Math.random().toString(36).substring(2, 15) + Math.random().toString(36).substring(2, 15);
const rowId = `ROW-${randomId()}`;
const chartComponentId = `CHART-${randomId()}`;
// Create chart component with width 12 (full width)
positions[chartComponentId] = {
type: 'CHART',
id: chartComponentId,
children: [],
parents: ['ROOT_ID', 'GRID_ID', rowId],
meta: {
width: 12,
height: 50,
chartId: chartId,
sliceName: chartName,
},
};
// Create row component to contain the chart
positions[rowId] = {
type: 'ROW',
id: rowId,
children: [chartComponentId],
parents: ['ROOT_ID', 'GRID_ID'],
meta: { background: 'BACKGROUND_TRANSPARENT' },
};
// Add row to GRID_ID children
const gridChildren = (positions['GRID_ID'] as Record<string, unknown>).children as string[];
if (!gridChildren.includes(rowId)) {
gridChildren.push(rowId);
}
// Update positions in json_metadata
jsonMetadata.positions = positions;
// Initialize chart_configuration if not exists
if (!jsonMetadata.chart_configuration) {
jsonMetadata.chart_configuration = {};
}
(jsonMetadata.chart_configuration as Record<string, unknown>)[String(chartId)] = {
id: chartId,
crossFilters: {
scope: 'global',
chartsInScope: []
}
};
// Update global_chart_configuration
if (!jsonMetadata.global_chart_configuration) {
jsonMetadata.global_chart_configuration = {
scope: { rootPath: ['ROOT_ID'], excluded: [] },
chartsInScope: []
};
}
const chartsInScope = (jsonMetadata.global_chart_configuration as Record<string, unknown>).chartsInScope as number[];
if (!chartsInScope.includes(chartId)) {
chartsInScope.push(chartId);
}
// Set other required fields if not exist
if (!jsonMetadata.timed_refresh_immune_slices) jsonMetadata.timed_refresh_immune_slices = [];
if (!jsonMetadata.expanded_slices) jsonMetadata.expanded_slices = {};
if (!jsonMetadata.refresh_frequency) jsonMetadata.refresh_frequency = 0;
if (!jsonMetadata.color_scheme) jsonMetadata.color_scheme = '';
if (!jsonMetadata.label_colors) jsonMetadata.label_colors = {};
if (!jsonMetadata.shared_label_colors) jsonMetadata.shared_label_colors = [];
if (!jsonMetadata.color_scheme_domain) jsonMetadata.color_scheme_domain = [];
if (jsonMetadata.cross_filters_enabled === undefined) jsonMetadata.cross_filters_enabled = true;
if (!jsonMetadata.default_filters) jsonMetadata.default_filters = '{}';
if (!jsonMetadata.filter_scopes) jsonMetadata.filter_scopes = {};
console.log('📊 Adding chart to dashboard with json_metadata');
console.log('📌 Chart ID:', chartId);
console.log('📌 Chart Name:', chartName);
console.log('📌 Row ID:', rowId);
console.log('📌 Chart Component ID:', chartComponentId);
console.log('📌 Total charts in grid:', gridChildren.length);
// Update dashboard with new json_metadata
await this.client.put(
`/api/v1/dashboard/${dashboardId}`,
{
json_metadata: JSON.stringify(jsonMetadata),
},
{ headers: await this.getAuthHeadersWithCsrf() }
);
console.log('✅ Chart added to dashboard successfully');
return {
success: true,
message: `Chart "${chartName}" (ID: ${chartId}) successfully added to dashboard ${dashboardId}`
};
} catch (error: unknown) {
logAxiosError('Failed to add chart to dashboard', error);
throw error;
} finally {
// Release the lock
releaseLock!();
this.dashboardLocks.delete(dashboardId);
}
}
/**
* Add multiple charts to a dashboard in a single operation
* This prevents race conditions and allows proper layout configuration
*/
async addChartsToDashboard(
dashboardId: number | string,
charts: Array<{ chartId: number; width?: number; height?: number; row?: number }>
): Promise<{ success: boolean; message: string }> {
await this.ensureAuthenticated();
// Wait for any existing operation on this dashboard to complete (mutex lock)
const existingLock = this.dashboardLocks.get(dashboardId);
if (existingLock) {
console.log(`⏳ Waiting for previous dashboard update to complete for dashboard ${dashboardId}...`);
await existingLock;
}
// Create a new lock for this operation
let releaseLock: () => void;
const lockPromise = new Promise<void>((resolve) => {
releaseLock = resolve;
});
this.dashboardLocks.set(dashboardId, lockPromise);
try {
// Get current dashboard to retrieve json_metadata
const dashboardRes = await this.client.get(`/api/v1/dashboard/${dashboardId}`, {
headers: this.getAuthHeaders(),
});
const dashboard = dashboardRes.data.result;
// Get chart info for all charts
const chartInfos: { id: number; name: string; width: number; height: number; row: number }[] = [];
for (const chart of charts) {
let chartName = `Chart ${chart.chartId}`;
try {
const chartRes = await this.client.get(`/api/v1/chart/${chart.chartId}`, {
headers: this.getAuthHeaders(),
});
chartName = chartRes.data.result.slice_name || chartName;
} catch {
console.warn(`⚠️ Could not fetch chart name for ${chart.chartId}, using default`);
}
chartInfos.push({
id: chart.chartId,
name: chartName,
width: chart.width || 12,
height: chart.height || 50,
row: chart.row ?? chartInfos.length // Default: each chart in its own row
});
}
// Parse existing json_metadata or create new one
let jsonMetadata: Record<string, unknown> = {};
if (dashboard.json_metadata) {
try {
jsonMetadata = typeof dashboard.json_metadata === 'string'
? JSON.parse(dashboard.json_metadata)
: dashboard.json_metadata;
} catch {
console.warn('⚠️ Failed to parse existing json_metadata, creating new');
jsonMetadata = {};
}
}
// Initialize or get positions from json_metadata
let positions: Record<string, unknown> = {};
if (jsonMetadata.positions) {
positions = jsonMetadata.positions as Record<string, unknown>;
}
// Initialize required dashboard metadata if not exists
if (!positions['DASHBOARD_VERSION_KEY']) {
positions['DASHBOARD_VERSION_KEY'] = 'v2';
}
if (!positions['ROOT_ID']) {
positions['ROOT_ID'] = { type: 'ROOT', id: 'ROOT_ID', children: ['GRID_ID'] };
}
if (!positions['GRID_ID']) {
positions['GRID_ID'] = { type: 'GRID', id: 'GRID_ID', children: [], parents: ['ROOT_ID'] };
}
if (!positions['HEADER_ID']) {
positions['HEADER_ID'] = {
id: 'HEADER_ID',
type: 'HEADER',
meta: { text: dashboard.dashboard_title || 'Dashboard' }
};
}
// Group charts by row number
const rowGroups = new Map<number, typeof chartInfos>();
for (const chart of chartInfos) {
const existingGroup = rowGroups.get(chart.row) || [];
existingGroup.push(chart);
rowGroups.set(chart.row, existingGroup);
}
// Generate unique random IDs
const randomId = () => Math.random().toString(36).substring(2, 15) + Math.random().toString(36).substring(2, 15);
const gridChildren = (positions['GRID_ID'] as Record<string, unknown>).children as string[];
const allChartIds: number[] = [];
// Create rows and charts
for (const [, chartsInRow] of rowGroups) {
const rowId = `ROW-${randomId()}`;
const chartComponentIds: string[] = [];
for (const chart of chartsInRow) {
const chartComponentId = `CHART-${randomId()}`;
chartComponentIds.push(chartComponentId);
allChartIds.push(chart.id);
// Create chart component
positions[chartComponentId] = {
type: 'CHART',
id: chartComponentId,
children: [],
parents: ['ROOT_ID', 'GRID_ID', rowId],
meta: {
width: chart.width,
height: chart.height,
chartId: chart.id,
sliceName: chart.name,
},
};
console.log(`📌 Adding chart: ${chart.name} (ID: ${chart.id}, width: ${chart.width})`);
}
// Create row component containing all charts for this row
positions[rowId] = {
type: 'ROW',
id: rowId,
children: chartComponentIds,
parents: ['ROOT_ID', 'GRID_ID'],
meta: { background: 'BACKGROUND_TRANSPARENT' },
};
// Add row to GRID_ID children
if (!gridChildren.includes(rowId)) {
gridChildren.push(rowId);
}
}
// Update positions in json_metadata
jsonMetadata.positions = positions;
// Initialize chart_configuration with cross-filters
if (!jsonMetadata.chart_configuration) {
jsonMetadata.chart_configuration = {};
}
for (const chartId of allChartIds) {
const otherChartIds = allChartIds.filter(id => id !== chartId);
(jsonMetadata.chart_configuration as Record<string, unknown>)[String(chartId)] = {
id: chartId,
crossFilters: {
scope: 'global',
chartsInScope: otherChartIds
}
};
}
// Update global_chart_configuration
if (!jsonMetadata.global_chart_configuration) {
jsonMetadata.global_chart_configuration = {
scope: { rootPath: ['ROOT_ID'], excluded: [] },
chartsInScope: []
};
}
const existingChartsInScope = (jsonMetadata.global_chart_configuration as Record<string, unknown>).chartsInScope as number[];
for (const chartId of allChartIds) {
if (!existingChartsInScope.includes(chartId)) {
existingChartsInScope.push(chartId);
}
}
// Set other required fields if not exist
if (!jsonMetadata.timed_refresh_immune_slices) jsonMetadata.timed_refresh_immune_slices = [];
if (!jsonMetadata.expanded_slices) jsonMetadata.expanded_slices = {};
if (!jsonMetadata.refresh_frequency) jsonMetadata.refresh_frequency = 0;
if (!jsonMetadata.color_scheme) jsonMetadata.color_scheme = '';
if (!jsonMetadata.label_colors) jsonMetadata.label_colors = {};
if (!jsonMetadata.shared_label_colors) jsonMetadata.shared_label_colors = [];
if (!jsonMetadata.color_scheme_domain) jsonMetadata.color_scheme_domain = [];
if (jsonMetadata.cross_filters_enabled === undefined) jsonMetadata.cross_filters_enabled = true;
if (!jsonMetadata.default_filters) jsonMetadata.default_filters = '{}';
if (!jsonMetadata.filter_scopes) jsonMetadata.filter_scopes = {};
console.log(`📊 Adding ${charts.length} charts to dashboard in ${rowGroups.size} rows`);
// Update dashboard with new json_metadata
await this.client.put(
`/api/v1/dashboard/${dashboardId}`,
{
json_metadata: JSON.stringify(jsonMetadata),
},
{ headers: await this.getAuthHeadersWithCsrf() }
);
console.log('✅ All charts added to dashboard successfully');
return {
success: true,
message: `Successfully added ${charts.length} charts to dashboard ${dashboardId}: ${chartInfos.map(c => c.name).join(', ')}`
};
} catch (error: unknown) {
logAxiosError('Failed to add charts to dashboard', error);
throw error;
} finally {
// Release the lock
releaseLock!();
this.dashboardLocks.delete(dashboardId);
}
}
// ============ CHART OPERATIONS ============
async listCharts(): Promise<Chart[]> {
await this.ensureAuthenticated();
try {
const response = await this.client.get('/api/v1/chart/', {
headers: this.getAuthHeaders(),
params: { q: JSON.stringify({ page_size: 100 }) },
});
return response.data.result.map((c: Record<string, unknown>) => ({
id: c.id,
slice_name: c.slice_name,
viz_type: c.viz_type,
datasource_id: c.datasource_id,
datasource_type: c.datasource_type,
}));
} catch (error) {
console.error('Failed to list charts:', error);
throw error;
}
}
async getChartData(chartId: number): Promise<unknown> {
await this.ensureAuthenticated();
try {
// First, get the chart config to extract query_context
const chartConfig = await this.getChart(chartId) as Record<string, unknown>;
let queryContext: Record<string, unknown>;
// Try to parse query_context from chart
if (chartConfig.query_context) {
try {
queryContext = typeof chartConfig.query_context === 'string'
? JSON.parse(chartConfig.query_context as string)
: chartConfig.query_context as Record<string, unknown>;
} catch {
console.warn('⚠️ Failed to parse query_context, building from params');
queryContext = this.buildDefaultQueryContext(chartConfig);
}
} else {
// Build query_context from chart params
queryContext = this.buildDefaultQueryContext(chartConfig);
}
console.log('📤 Fetching chart data with POST /chart/data');
// Use POST /api/v1/chart/data endpoint
const response = await this.client.post(
'/api/v1/chart/data',
queryContext,
{
headers: await this.getAuthHeadersWithCsrf(),
params: { form_data: JSON.stringify({ slice_id: chartId }) }
}
);
return response.data;
} catch (error) {
console.error('Failed to get chart data:', error);
throw error;
}
}
private buildDefaultQueryContext(chartConfig: Record<string, unknown>): Record<string, unknown> {
// Parse params if string
let params: Record<string, unknown> = {};
if (chartConfig.params) {
try {
params = typeof chartConfig.params === 'string'
? JSON.parse(chartConfig.params as string)
: chartConfig.params as Record<string, unknown>;
} catch {
params = {};
}
}
const datasourceId = chartConfig.datasource_id as number;
const datasourceType = (chartConfig.datasource_type as string) || 'table';
return {
datasource: {
id: datasourceId,
type: datasourceType,
},
force: false,
queries: [
{
filters: [],
extras: { having: '', where: '' },
columns: [],
metrics: ['count'],
orderby: [['count', false]],
row_limit: 10000,
order_desc: true,
},
],
form_data: {
datasource: `${datasourceId}__${datasourceType}`,
viz_type: chartConfig.viz_type || 'table',
slice_id: chartConfig.id,
...params,
},
result_format: 'json',
result_type: 'full',
};
}
async getChart(chartId: number): Promise<unknown> {
await this.ensureAuthenticated();
try {
const response = await this.client.get(`/api/v1/chart/${chartId}`, {
headers: this.getAuthHeaders(),
});
return response.data.result;
} catch (error) {
console.error('Failed to get chart:', error);
throw error;
}
}
async createChart(
name: string,
vizType: string,
datasourceId: number,
datasourceType: string = 'table',
params: Record<string, unknown> = {}
): Promise<Chart> {
await this.ensureAuthenticated();
try {
// Log what we received
console.log('📈 Metrics received:', JSON.stringify(params.metrics, null, 2));
console.log('📈 X-axis:', params.x_axis);
console.log('📈 Groupby:', JSON.stringify(params.groupby, null, 2));
console.log('📈 Viz type:', vizType);
// Check if this is a pie chart
const isPieChart = vizType === 'pie';
// AUTO-DETECT: If groupby is not provided for pie charts, or x_axis not provided for other charts
if (isPieChart) {
// Pie charts use groupby instead of x_axis
if (!params.groupby) {
console.log('🔍 No groupby provided for pie chart, running auto-detection...');
const analysis = await this.analyzeDatasetForVisualization(datasourceId);
if (analysis.suggestedDimensionColumns.length > 0) {
params.groupby = [analysis.suggestedDimensionColumns[0]];
console.log('📌 Auto-detected groupby for pie chart:', params.groupby);
}
// Use auto-detected metrics if not provided
if (!params.metrics && analysis.suggestedMetricColumns.length > 0) {
params.metrics = analysis.suggestedMetricColumns.slice(0, 1).map(m => ({
expressionType: 'SIMPLE',
column: { column_name: m.column_name },
aggregate: m.aggregate,
label: m.label
}));
console.log('📌 Auto-detected metrics for pie chart:', JSON.stringify(params.metrics, null, 2));
}
}
} else if (!params.x_axis) {
// Non-pie charts use x_axis
console.log('🔍 No x_axis provided, running auto-detection...');
const analysis = await this.analyzeDatasetForVisualization(datasourceId);
// Use time column or first dimension as x_axis
if (analysis.suggestedTimeColumn) {
params.x_axis = analysis.suggestedTimeColumn;
console.log('📌 Auto-detected time column for x_axis:', params.x_axis);
} else if (analysis.suggestedDimensionColumns.length > 0) {
params.x_axis = analysis.suggestedDimensionColumns[0];
console.log('📌 Auto-detected dimension for x_axis:', params.x_axis);
}
// Use auto-detected metrics if not provided
if (!params.metrics && analysis.suggestedMetricColumns.length > 0) {
params.metrics = analysis.suggestedMetricColumns.slice(0, 2).map(m => ({
expressionType: 'SIMPLE',
column: { column_name: m.column_name },
aggregate: m.aggregate,
label: m.label
}));
console.log('📌 Auto-detected metrics:', JSON.stringify(params.metrics, null, 2));
}
// Use auto-detected groupby if not provided
if (!params.groupby && analysis.suggestedDimensionColumns.length > 0) {
// Don't use as groupby if already used as x_axis
const availableDimensions = analysis.suggestedDimensionColumns.filter(d => d !== params.x_axis);
if (availableDimensions.length > 0) {
params.groupby = availableDimensions.slice(0, 1);
console.log('📌 Auto-detected groupby:', params.groupby);
}
}
}
// Prepare metrics - use 'count' as default if still not provided
let metricsForQuery: unknown[] = ['count']; // Default to count metric
if (params.metrics && Array.isArray(params.metrics) && params.metrics.length > 0) {
metricsForQuery = params.metrics as unknown[];
}
// Build query context based on visualization type
let queryContext: Record<string, unknown>;
let formData: Record<string, unknown>;
if (isPieChart) {
// PIE CHART: Uses groupby columns (string array), metric (singular), different structure
const groupbyColumns = (params.groupby as string[]) || [];
const primaryMetric = metricsForQuery[0];
formData = {
datasource: `${datasourceId}__${datasourceType}`,
viz_type: vizType,
groupby: groupbyColumns,
metric: primaryMetric, // PIE uses singular 'metric'
adhoc_filters: [
{
clause: 'WHERE',
subject: params.time_column || 'tanggal',
operator: 'TEMPORAL_RANGE',
comparator: params.time_range || 'No filter',
expressionType: 'SIMPLE'
}
],
row_limit: params.row_limit || 100,
sort_by_metric: true,
color_scheme: params.color_scheme || 'supersetColors',
show_labels_threshold: 5,
show_legend: true,
legendType: 'scroll',
legendOrientation: 'top',
label_type: 'key',
number_format: 'SMART_NUMBER',
date_format: 'smart_date',
show_labels: true,
labels_outside: true,
outerRadius: 70,
innerRadius: params.innerRadius || 30, // 30 for donut, 0 for regular pie
extra_form_data: {},
force: false,
result_format: 'json',
result_type: 'full',
...params, // Allow overrides
};
queryContext = {
datasource: {
id: datasourceId,
type: datasourceType,
},
force: false,
queries: [
{
filters: [
{
col: params.time_column || 'tanggal',
op: 'TEMPORAL_RANGE',
val: params.time_range || 'No filter'
}
],
extras: {
having: '',
where: '',
},
applied_time_extras: {},
columns: groupbyColumns, // PIE uses simple string array for columns
metrics: metricsForQuery,
orderby: [[primaryMetric, false]],
annotation_layers: [],
row_limit: params.row_limit || 100,
series_limit: 0,
order_desc: true,
url_params: {},
custom_params: {},
custom_form_data: {},
},
],
form_data: formData,
result_format: 'json',
result_type: 'full',
};
console.log('🥧 Building PIE chart query context');
} else {
// TIME SERIES / BAR / LINE CHARTS: Uses x_axis with complex column structure
const columns: unknown[] = [];
if (params.x_axis) {
columns.push({
columnType: 'BASE_AXIS',
sqlExpression: params.x_axis as string,
label: params.x_axis as string,
expressionType: 'SQL'
});
}
formData = {
datasource: `${datasourceId}__${datasourceType}`,
viz_type: vizType,
x_axis: params.x_axis || null,
metrics: metricsForQuery,
groupby: params.groupby || [],
adhoc_filters: [],
order_desc: true,
row_limit: 10000,
truncate_metric: true,
show_empty_columns: true,
comparison_type: 'values',
annotation_layers: [],
orientation: 'vertical',
color_scheme: 'supersetColors',
show_legend: true,
legendType: 'scroll',
legendOrientation: 'top',
x_axis_time_format: 'smart_date',
y_axis_format: 'SMART_NUMBER',
rich_tooltip: true,
extra_form_data: {},
force: false,
result_format: 'json',
result_type: 'full',
time_range: params.time_range || 'No filter',
...params, // Allow overrides
};
queryContext = {
datasource: {
id: datasourceId,
type: datasourceType,
},
force: false,
queries: [
{
filters: [],
extras: {
having: '',
where: '',
},
applied_time_extras: {},
columns: columns,
metrics: metricsForQuery,
orderby: metricsForQuery.length > 0 ? [[metricsForQuery[0], false]] : [],
annotation_layers: [],
row_limit: 10000,
series_columns: [],
series_limit: 0,
order_desc: true,
url_params: {},
custom_params: {},
custom_form_data: {},
time_offsets: [],
post_processing: columns.length > 0 ? [
{
operation: 'pivot',
options: {
index: [params.x_axis as string],
columns: [],
aggregates: {
[String(metricsForQuery[0])]: { operator: 'mean' }
},
drop_missing_columns: false
}
},
{
operation: 'flatten'
}
] : []
},
],
form_data: formData,
result_format: 'json',
result_type: 'full',
};
console.log('📊 Building time series chart query context');
}
// Chart params for storage
const chartParams: Record<string, unknown> = {
...formData,
query_context: JSON.stringify(queryContext),
};
// Debug logging
console.log('📊 Creating chart with query_context:', JSON.stringify(queryContext, null, 2));
const response = await this.client.post(
'/api/v1/chart/',
{
slice_name: name,
viz_type: vizType,
datasource_id: datasourceId,
datasource_type: datasourceType,
params: JSON.stringify(chartParams),
query_context: JSON.stringify(queryContext),
},
{ headers: await this.getAuthHeadersWithCsrf() }
);
console.log('✅ Chart created successfully with proper query_context');
return response.data.result;
} catch (error: unknown) {
logAxiosError('Failed to create chart', error);
throw error;
}
}
async generateChartData(
chartId: number,
datasourceId: number,
datasourceType: string,
chartParams: Record<string, unknown>
): Promise<unknown> {
await this.ensureAuthenticated();
// Build the request payload according to Superset API spec
const payload = {
datasource: {
id: datasourceId,
type: datasourceType,
},
force: true,
form_data: JSON.stringify(chartParams),
queries: [
{
time_range: chartParams.time_range || 'No filter',
granularity: chartParams.granularity_sqla || chartParams.x_axis || null,
groupby: chartParams.groupby || [],
metrics: chartParams.metrics || [],
columns: [],
row_limit: 10000,
extras: {
time_grain_sqla: chartParams.time_grain_sqla || 'P1D',
having: '',
where: '',
},
order_desc: true,
is_timeseries: true,
},
],
result_format: 'json',
result_type: 'full',
};
console.log('📤 Sending chart data payload:', JSON.stringify(payload, null, 2));
try {
const response = await this.client.post(
'/api/v1/chart/data',
payload,
{ headers: await this.getAuthHeadersWithCsrf() }
);
return response.data;
} catch (error: unknown) {
logAxiosError('Failed to generate chart data', error);
throw error;
}
}
// ============ DATASET OPERATIONS ============
async listDatasets(): Promise<Dataset[]> {
await this.ensureAuthenticated();
try {
const response = await this.client.get('/api/v1/dataset/', {
headers: this.getAuthHeaders(),
params: { q: JSON.stringify({ page_size: 100 }) },
});
return response.data.result;
} catch (error) {
console.error('Failed to list datasets:', error);
throw error;
}
}
async getDatasetSchema(datasetId: number): Promise<Column[]> {
await this.ensureAuthenticated();
try {
const response = await this.client.get(`/api/v1/dataset/${datasetId}`, {
headers: this.getAuthHeaders(),
});
return response.data.result.columns || [];
} catch (error) {
console.error('Failed to get dataset schema:', error);
throw error;
}
}
// ============ SQL LAB OPERATIONS ============
async executeSQL(databaseId: number, sql: string, schema?: string): Promise<QueryResult> {
await this.ensureAuthenticated();
try {
const response = await this.client.post(
'/api/v1/sqllab/execute/',
{
database_id: databaseId,
sql: sql,
schema: schema || null,
runAsync: false,
queryLimit: 1000,
},
{ headers: await this.getAuthHeadersWithCsrf() }
);
return {
columns: response.data.columns?.map((c: { name: string }) => c.name) || [],
data: response.data.data || [],
query_id: response.data.query_id,
status: response.data.status,
};
} catch (error: unknown) {
logAxiosError('Failed to execute SQL', error);
throw error;
}
}
// ============ VISUALIZATION ANALYSIS ============
/**
* Analyze dataset schema and suggest visualization configuration
* Auto-detects time columns, metric columns, and dimension columns
*/
async analyzeDatasetForVisualization(datasetId: number): Promise<{
suggestedTimeColumn: string | null;
suggestedMetricColumns: Array<{ column_name: string; aggregate: string; label: string }>;
suggestedDimensionColumns: string[];
}> {
const columns = await this.getDatasetSchema(datasetId);
console.log('🔍 Analyzing dataset columns:', columns.map(c => ({
name: c.column_name,
type: c.type,
is_dttm: c.is_dttm,
groupby: c.groupby,
filterable: c.filterable
})));
let suggestedTimeColumn: string | null = null;
const suggestedMetricColumns: Array<{ column_name: string; aggregate: string; label: string }> = [];
const suggestedDimensionColumns: string[] = [];
// Time column patterns
const timePatterns = ['date', 'time', 'created', 'updated', 'timestamp', 'tanggal', 'waktu'];
// Numeric types for metrics
const numericTypes = ['INTEGER', 'BIGINT', 'FLOAT', 'DOUBLE', 'DECIMAL', 'NUMERIC', 'REAL', 'INT', 'SMALLINT', 'NUMBER'];
// String types for dimensions
const stringTypes = ['VARCHAR', 'TEXT', 'CHAR', 'STRING', 'NVARCHAR', 'NCHAR'];
for (const col of columns) {
const colName = col.column_name.toLowerCase();
const colType = (col.type || '').toUpperCase();
// Detect time column
if (col.is_dttm || timePatterns.some(p => colName.includes(p))) {
if (!suggestedTimeColumn) {
suggestedTimeColumn = col.column_name;
}
continue;
}
// Detect ID columns to exclude
const isIdColumn = colName === 'id' || colName.endsWith('_id') || colName.startsWith('id_');
// Detect metric columns (numeric types, not ID columns)
const isNumeric = numericTypes.some(t => colType.includes(t));
if (isNumeric && !isIdColumn) {
// Suggest SUM for amount/total/count columns, AVG for others
const aggregate = (colName.includes('total') || colName.includes('amount') ||
colName.includes('sum') || colName.includes('count') ||
colName.includes('jumlah') || colName.includes('qty') ||
colName.includes('quantity') || colName.includes('penjualan') ||
colName.includes('harga') || colName.includes('price')) ? 'SUM' : 'AVG';
suggestedMetricColumns.push({
column_name: col.column_name,
aggregate,
label: `${aggregate}(${col.column_name})`
});
continue;
}
// Detect dimension columns (string/categorical types or groupable, not ID columns)
const isString = stringTypes.some(t => colType.includes(t));
if (!isIdColumn && (isString || col.groupby)) {
suggestedDimensionColumns.push(col.column_name);
}
}
console.log('📊 Auto-analysis result:', {
timeColumn: suggestedTimeColumn,
metrics: suggestedMetricColumns.map(m => m.label),
dimensions: suggestedDimensionColumns
});
return {
suggestedTimeColumn,
suggestedMetricColumns,
suggestedDimensionColumns
};
}
// ============ DATABASE OPERATIONS ============
async listDatabases(): Promise<{ id: number; database_name: string }[]> {
await this.ensureAuthenticated();
try {
const response = await this.client.get('/api/v1/database/', {
headers: this.getAuthHeaders(),
});
return response.data.result.map((db: Record<string, unknown>) => ({
id: db.id,
database_name: db.database_name,
}));
} catch (error) {
console.error('Failed to list databases:', error);
throw error;
}
}
}