Virtual View Utilities

/**
 * Creates a virtual explore from a SQL query for visualization
 * Virtual explores behave like regular explores but are backed by custom SQL
 * @param virtualViewName - Name for the virtual view
 * @param sql - SQL query that defines the view
 * @param columns - Column definitions from the SQL results
 * @param warehouseClient - Warehouse client for SQL generation
 * @param label - Optional display label for the explore
 * @returns Explore object configured as a virtual view
 */
function createVirtualView(
  virtualViewName: string,
  sql: string,
  columns: VizColumn[],
  warehouseClient: WarehouseClient,
  label?: string
): Explore;

/**
 * Creates a temporary virtual view with fake warehouse client for testing
 * Useful for preview/validation without a real warehouse connection
 * @param virtualViewName - Name for the virtual view
 * @param sql - SQL query that defines the view
 * @param columns - Column definitions from the SQL results
 * @returns Explore object configured as a temporary virtual view
 */
function createTemporaryVirtualView(
  virtualViewName: string,
  sql: string,
  columns: VizColumn[]
): Explore;

import { createVirtualView, createTemporaryVirtualView, type VizColumn } from '@lightdash/common';

// Create virtual view from SQL Runner results
const sql = `
  SELECT
    date_trunc('month', order_date) as month,
    customer_segment,
    SUM(revenue) as total_revenue,
    COUNT(*) as order_count
  FROM orders
  GROUP BY 1, 2
`;

const columns: VizColumn[] = [
  { reference: 'month', type: DimensionType.DATE },
  { reference: 'customer_segment', type: DimensionType.STRING },
  { reference: 'total_revenue', type: DimensionType.NUMBER },
  { reference: 'order_count', type: DimensionType.NUMBER },
];

// Create virtual explore for visualization
const virtualExplore = createVirtualView(
  'revenue_by_segment',
  sql,
  columns,
  warehouseClient,
  'Revenue by Segment'
);

// Now you can use this explore for charts
const cartesianDataModel = new CartesianChartDataModel({
  resultsRunner: {
    rows: sqlResults.rows,
    getField: (fieldId) => virtualExplore.tables['revenue_by_segment'].dimensions[fieldId],
  },
  fieldConfig: {
    x: ['month'],
    y: [{ reference: 'total_revenue', aggregation: 'sum' }],
  },
});

import { createTemporaryVirtualView, type VizColumn } from '@lightdash/common';

// Create temporary virtual view for testing/preview
const sql = `
  SELECT
    product_category,
    COUNT(*) as product_count,
    AVG(price) as avg_price
  FROM products
  GROUP BY product_category
`;

const columns: VizColumn[] = [
  { reference: 'product_category', type: DimensionType.STRING },
  { reference: 'product_count', type: DimensionType.NUMBER },
  { reference: 'avg_price', type: DimensionType.NUMBER },
];

const tempExplore = createTemporaryVirtualView(
  'preview_view',
  sql,
  columns
);
// Can be used for validation without warehouse connection

import { createVirtualView, DimensionType } from '@lightdash/common';

async function createChartFromSqlRunner(
  sqlQuery: string,
  warehouseClient: WarehouseClient
): Promise<ChartConfig> {
  // Execute SQL query
  const results = await warehouseClient.runQuery(sqlQuery);

  // Create column definitions from results
  const columns: VizColumn[] = results.fields.map(field => ({
    reference: field.name,
    type: inferDimensionType(field.type),
  }));

  // Create virtual view
  const virtualExplore = createVirtualView(
    'sql_runner_view',
    sqlQuery,
    columns,
    warehouseClient,
    'SQL Runner Results'
  );

  // Build chart configuration
  return {
    type: 'cartesian',
    explore: virtualExplore,
    dimensions: columns.filter(c => c.type !== DimensionType.NUMBER),
    metrics: columns.filter(c => c.type === DimensionType.NUMBER),
  };
}

function inferDimensionType(sqlType: string): DimensionType {
  const lowerType = sqlType.toLowerCase();
  if (lowerType.includes('int') || lowerType.includes('numeric') || lowerType.includes('decimal')) {
    return DimensionType.NUMBER;
  } else if (lowerType.includes('date') || lowerType.includes('timestamp')) {
    return DimensionType.DATE;
  } else if (lowerType.includes('bool')) {
    return DimensionType.BOOLEAN;
  }
  return DimensionType.STRING;
}

import { createVirtualView } from '@lightdash/common';

async function addSqlRunnerChart(
  dashboardId: string,
  sqlQuery: string,
  chartConfig: ChartConfig
) {
  // Execute SQL
  const results = await executeSqlQuery(sqlQuery);

  // Extract columns from results
  const columns = results.fields.map(field => ({
    reference: field.name,
    type: mapSqlTypeToDimensionType(field.type),
  }));

  // Create virtual explore
  const explore = createVirtualView(
    `sql_chart_${Date.now()}`,
    sqlQuery,
    columns,
    warehouseClient,
    chartConfig.title
  );

  // Create chart using virtual explore
  const chart = await createChart({
    ...chartConfig,
    explore,
    dashboardId,
  });

  return chart;
}

import { createTemporaryVirtualView } from '@lightdash/common';

// Preview SQL query results as a chart without executing
function previewSqlChart(
  sqlQuery: string,
  sampleResults: { fields: Field[]; rows: Row[] }
) {
  const columns = sampleResults.fields.map(field => ({
    reference: field.name,
    type: DimensionType.STRING, // Default for preview
  }));

  const tempExplore = createTemporaryVirtualView(
    'preview',
    sqlQuery,
    columns
  );

  // Use temp explore for preview rendering
  return {
    explore: tempExplore,
    sampleData: sampleResults.rows.slice(0, 10),
  };
}

import { createVirtualView } from '@lightdash/common';

// API endpoint to create visualization from SQL
app.post('/api/sql-runner/visualize', async (req, res) => {
  const { sql, chartType } = req.body;

  try {
    // Get warehouse client for project
    const warehouseClient = await getWarehouseClient(req.user.projectId);

    // Execute SQL
    const results = await warehouseClient.runQuery(sql);

    // Create columns from results
    const columns: VizColumn[] = results.fields.map(field => ({
      reference: field.name,
      type: inferTypeFromSqlType(field.type),
    }));

    // Create virtual explore
    const explore = createVirtualView(
      'sql_runner_viz',
      sql,
      columns,
      warehouseClient,
      'SQL Runner Visualization'
    );

    // Generate chart based on explore
    const chartData = generateChartData(explore, results.rows, chartType);

    res.json({
      explore,
      chartData,
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

import { DimensionType } from '@lightdash/common';

function createColumnsFromSqlResults(
  results: SqlQueryResults
): VizColumn[] {
  return results.fields.map(field => {
    let type: DimensionType;

    // Map SQL types to Lightdash dimension types
    const sqlType = field.type.toLowerCase();

    if (sqlType.includes('int') || sqlType.includes('numeric') ||
        sqlType.includes('decimal') || sqlType.includes('float') ||
        sqlType.includes('double')) {
      type = DimensionType.NUMBER;
    } else if (sqlType.includes('date') || sqlType.includes('timestamp')) {
      type = DimensionType.DATE;
    } else if (sqlType.includes('bool')) {
      type = DimensionType.BOOLEAN;
    } else {
      type = DimensionType.STRING;
    }

    return {
      reference: field.name,
      type,
    };
  });
}

import { createTemporaryVirtualView, createVirtualView } from '@lightdash/common';

describe('Virtual views', () => {
  it('should create temporary virtual view', () => {
    const columns = [
      { reference: 'category', type: DimensionType.STRING },
      { reference: 'total', type: DimensionType.NUMBER },
    ];

    const explore = createTemporaryVirtualView(
      'test_view',
      'SELECT category, SUM(amount) as total FROM sales GROUP BY category',
      columns
    );

    expect(explore.name).toBe('test_view');
    expect(explore.tables['test_view']).toBeDefined();
  });

  it('should create virtual view with warehouse client', () => {
    const columns = [
      { reference: 'month', type: DimensionType.DATE },
      { reference: 'revenue', type: DimensionType.NUMBER },
    ];

    const explore = createVirtualView(
      'revenue_view',
      'SELECT * FROM revenue',
      columns,
      mockWarehouseClient,
      'Revenue View'
    );

    expect(explore.label).toBe('Revenue View');
    expect(explore.tables['revenue_view'].dimensions['month'].type).toBe(DimensionType.DATE);
  });
});

import { createVirtualView } from '@lightdash/common';

// Create virtual view with pre-aggregated data
async function createAggregatedView(
  baseTable: string,
  groupBy: string[],
  metrics: { name: string; expression: string }[]
) {
  const sql = `
    SELECT
      ${groupBy.join(', ')},
      ${metrics.map(m => `${m.expression} as ${m.name}`).join(', ')}
    FROM ${baseTable}
    GROUP BY ${groupBy.map((_, i) => i + 1).join(', ')}
  `;

  const columns: VizColumn[] = [
    ...groupBy.map(field => ({
      reference: field,
      type: DimensionType.STRING, // Infer from schema in production
    })),
    ...metrics.map(m => ({
      reference: m.name,
      type: DimensionType.NUMBER,
    })),
  ];

  return createVirtualView(
    'aggregated_view',
    sql,
    columns,
    warehouseClient,
    'Aggregated Results'
  );
}

// Usage
const explore = await createAggregatedView(
  'orders',
  ['customer_segment', 'product_category'],
  [
    { name: 'total_revenue', expression: 'SUM(revenue)' },
    { name: 'order_count', expression: 'COUNT(*)' },
    { name: 'avg_order_value', expression: 'AVG(order_value)' },
  ]
);