TravelLight AI 어시스턴트: 자연어를 SQL로 변환하는 데이터 분석 시스템 구축기

여행 짐 보관 서비스인 TravelLight의 관리자 대시보드에 AI 어시스턴트 기능을 구현했습니다. 이 글에서는 Google Gemini API를 활용해 자연어 질문을 SQL 쿼리로 변환하고, 결과를 시각화하는 시스템의 구현 과정과 핵심 기술들을 공유합니다.

🎯 프로젝트 개요

TravelLight는 여행자들이 짐을 안전하게 보관하고 원하는 장소로 배송받을 수 있는 서비스입니다. 서비스 운영 중 관리자들이 비즈니스 데이터를 분석할 때 다음과 같은 어려움이 있었습니다:

복잡한 SQL 쿼리 작성의 어려움
다양한 관점에서의 데이터 분석 요구
실시간 비즈니스 인사이트 필요성

이를 해결하기 위해 자연어를 SQL로 변환하는 AI 어시스턴트를 구현했습니다.

🏗️ 시스템 아키텍처

전체 구조

[관리자] → [React 채팅 UI] → [Spring Boot API] → [Gemini API] → [PostgreSQL]
                ↑                                                      ↑
            [시각화 컴포넌트] ←← [쿼리 실행 서비스] ←← [스키마 분석] ←←

핵심 컴포넌트

Frontend (React TypeScript)
- AdminChatInterface.tsx: 채팅 형태의 사용자 인터페이스
- QueryResultDisplay.tsx: 결과 시각화 (차트, 테이블)
Backend (Spring Boot)
- GeminiQueryService: Gemini API 호출 및 자연어-SQL 변환
- DatabaseSchemaService: 데이터베이스 스키마 분석
- QueryExecutorService: SQL 실행 및 보안 검증
- QueryCacheService: Redis 기반 캐싱

🧠 자연어-SQL 변환 과정

1. 스키마 정보 자동 추출

JPA 엔티티에서 데이터베이스 스키마 정보를 자동으로 추출합니다:

@Service
public class DatabaseSchemaService {

    public String getSchemaForPrompt() {
        Set<EntityType<?>> entities = entityManager.getMetamodel().getEntities();
        StringBuilder promptBuilder = new StringBuilder();

        for (EntityType<?> entity : entities) {
            String tableName = getTableName(entity);
            promptBuilder.append(String.format("테이블: %s\n", tableName));

            for (Attribute<?, ?> attribute : entity.getAttributes()) {
                String dbColumnName = camelCaseToSnakeCase(attribute.getName());
                promptBuilder.append(String.format("  - %s (DB: %s, %s)\n",
                    attribute.getName(), dbColumnName, attribute.getJavaType()));
            }
        }

        // 비즈니스 컨텍스트 추가
        promptBuilder.append("주요 비즈니스 개념:\n");
        promptBuilder.append("- reservations: 짐 보관 예약 정보\n");
        promptBuilder.append("- partnerships: 제휴점 정보\n");
        // ...

        return promptBuilder.toString();
    }
}

2. Gemini API를 통한 쿼리 생성

추출된 스키마 정보를 컨텍스트로 제공하여 정확한 SQL 생성:

@Service
public class GeminiQueryService {

    public String convertNaturalLanguageToSQL(String naturalQuery) {
        String systemPrompt = buildSystemPrompt();
        String fullPrompt = systemPrompt + "\n\n" + String.format("""
            사용자 질문: "%s"

            위 질문을 PostgreSQL 쿼리로 변환해주세요.

            응답 형식:
            {
              "sql": "SELECT ... FROM ...",
              "explanation": "이 쿼리는 ...",
              "chartType": "bar|line|pie|table",
              "title": "결과 제목"
            }

            주의사항:
            - 반드시 유효한 PostgreSQL 문법을 사용하세요
            - 보안상 위험한 쿼리(DELETE, UPDATE, DROP)는 사용하지 마세요
            - 테이블명은 정확히 스키마에 있는 이름을 사용하세요
            """, naturalQuery);

        Client client = new Client(); // Google Gemini API
        GenerateContentResponse response = client.models.generateContent(
            modelName, fullPrompt, null
        );

        return extractJsonFromMarkdown(response.text());
    }
}

3. SQL 보안 검증 및 실행

생성된 SQL을 보안 규칙에 따라 검증 후 실행:

@Service
public class QueryExecutorService {

    public JsonNode executeQuery(String sql, String chartType, String title, String explanation) {
        // 보안 검증
        validateSQLSecurity(sql);

        try {
            List<Map<String, Object>> rawData = jdbcTemplate.queryForList(sql);

            ObjectNode result = objectMapper.createObjectNode();
            result.put("success", true);
            result.put("sql", sql);
            result.put("chartType", chartType);
            result.put("title", title);
            result.put("explanation", explanation);
            result.set("data", objectMapper.valueToTree(rawData));

            return result;
        } catch (Exception e) {
            log.error("SQL 실행 중 오류 발생: {}", sql, e);
            throw new RuntimeException("쿼리 실행 실패: " + e.getMessage());
        }
    }

    private void validateSQLSecurity(String sql) {
        String upperSQL = sql.toUpperCase().trim();

        // SELECT문만 허용
        if (!upperSQL.startsWith("SELECT")) {
            throw new SecurityException("SELECT 문만 허용됩니다.");
        }

        // 위험한 키워드 차단
        String[] forbiddenKeywords = {"DELETE", "UPDATE", "DROP", "TRUNCATE",
                                     "INSERT", "ALTER", "CREATE", "EXEC"};
        for (String keyword : forbiddenKeywords) {
            if (upperSQL.contains(keyword)) {
                throw new SecurityException("허용되지 않은 SQL 키워드: " + keyword);
            }
        }
    }
}

🚀 성능 최적화: Redis 캐싱 시스템

스마트 캐싱 전략

API 호출 비용을 최소화하기 위해 다단계 캐싱을 구현했습니다:

@Service
public class QueryCacheService {

    public Optional<String> getCachedQuery(String naturalQuery) {
        String normalizedQuery = normalizeQuery(naturalQuery);
        String cacheKey = generateCacheKey(normalizedQuery);

        try {
            String cachedResult = redisTemplate.opsForValue().get(cacheKey);
            if (cachedResult != null) {
                updateCacheStats(true); // 캐시 히트
                return Optional.of(cachedResult);
            }
        } catch (Exception e) {
            log.warn("Redis 캐시 조회 실패, API 호출로 진행: {}", e.getMessage());
        }

        updateCacheStats(false); // 캐시 미스
        return Optional.empty();
    }

    public void cacheQuery(String naturalQuery, String result) {
        String normalizedQuery = normalizeQuery(naturalQuery);
        String cacheKey = generateCacheKey(normalizedQuery);

        try {
            redisTemplate.opsForValue().set(cacheKey, result, Duration.ofHours(2));
            log.debug("쿼리 결과 캐싱 완료: {}", normalizedQuery);
        } catch (Exception e) {
            log.warn("Redis 캐시 저장 실패: {}", e.getMessage());
        }
    }

    // 질문 정규화로 캐시 히트율 향상
    private String normalizeQuery(String query) {
        return query.toLowerCase()
                   .replaceAll("\\s+", " ")
                   .trim();
    }
}

캐싱 효과

API 호출 90% 감소: 같은 질문에 대해 즉시 응답
응답 속도 5-10배 향상: Redis에서 직접 조회
비용 10배 절약: Gemini API 사용량 대폭 감소

🎨 React 채팅 인터페이스

사용자 친화적인 UI/UX

Material-UI를 활용한 채팅 형태의 인터페이스:

export const AdminChatInterface: React.FC<AdminChatInterfaceProps> = ({ onClose }) => {
  const [messages, setMessages] = useState<ChatMessage[]>([]);
  const [inputValue, setInputValue] = useState('');
  const [isLoading, setIsLoading] = useState(false);

  const handleSendMessage = async () => {
    if (!inputValue.trim() || isLoading) return;

    const userMessage: ChatMessage = {
      id: `user-${Date.now()}`,
      type: 'user',
      content: inputValue.trim(),
      timestamp: new Date()
    };

    setMessages(prev => [...prev, userMessage]);
    const query = inputValue.trim();
    setInputValue('');
    setIsLoading(true);

    try {
      const response = await fetch('/api/admin/query/natural', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'Authorization': `Bearer ${localStorage.getItem('token')}`
        },
        body: JSON.stringify({ query })
      });

      const result = await response.json();

      const assistantMessage: ChatMessage = {
        id: `assistant-${Date.now()}`,
        type: 'assistant',
        content: result.success
          ? `${result.explanation}\n\n**실행된 쿼리:**\n\`\`\`sql\n${result.sql}\n\`\`\``
          : `죄송합니다. 오류가 발생했습니다: ${result.error}`,
        timestamp: new Date(),
        queryResult: result.success ? result : null
      };

      setMessages(prev => [...prev, assistantMessage]);
    } catch (error) {
      // 에러 처리
    } finally {
      setIsLoading(false);
    }
  };

  return (
    <Paper elevation={8} sx={{ /* 스타일링 */ }}>
      {/* 채팅 메시지 표시 */}
      {/* 결과 시각화 컴포넌트 */}
      {/* 입력 필드 */}
    </Paper>
  );
};

예시 질문 제공

사용자 편의를 위해 비즈니스에 맞는 예시 질문들을 제공:

const examples = [
  "이번달 총 매출은 얼마인가요?",
  "오늘 예약 건수는 몇 건인가요?",
  "지역별 예약 현황을 보여주세요",
  "파트너십 승인 대기 중인 매장은 몇 개인가요?",
  "최근 7일간 일별 매출 추이를 보여주세요"
];

📊 결과 시각화

차트 타입별 자동 선택

Gemini가 질문의 성격에 따라 적절한 차트 타입을 선택:

bar: 카테고리별 수치 비교
line: 시간 흐름에 따른 변화
pie: 전체 대비 비율
table: 상세 데이터 나열

Chart.js를 활용한 시각화

const QueryResultDisplay: React.FC<{ data: any }> = ({ data }) => {
  const renderChart = () => {
    switch (data.chartType) {
      case 'bar':
        return <Bar data={processDataForChart(data.data)} options={chartOptions} />;
      case 'line':
        return <Line data={processDataForChart(data.data)} options={chartOptions} />;
      case 'pie':
        return <Pie data={processDataForChart(data.data)} options={pieOptions} />;
      default:
        return <DataTable data={data.data} />;
    }
  };

  return (
    <Card>
      <CardContent>
        <Typography variant="h6">{data.title}</Typography>
        <Typography variant="body2" color="text.secondary">
          {data.explanation}
        </Typography>
        {renderChart()}
        <Button onClick={() => exportToCSV(data.data)}>
          CSV 다운로드
        </Button>
      </CardContent>
    </Card>
  );
};

🔒 보안 고려사항

다층 보안 시스템

인증/권한: Spring Security로 ADMIN 권한만 접근 허용
SQL 인젝션 방지: 화이트리스트 기반 키워드 필터링
안전한 쿼리만 허용: SELECT문만 실행 가능
테이블 접근 제한: 허용된 테이블만 접근

실제 보안 구현

@RestController
@RequestMapping("/api/admin/query")
@PreAuthorize("hasRole('ADMIN')")  // 관리자만 접근
public class AdminQueryController {

    @PostMapping("/natural")
    @PreAuthorize("hasRole('ADMIN')")
    public ResponseEntity<?> processNaturalQuery(@RequestBody Map<String, String> request) {
        // 입력 검증
        String naturalQuery = request.get("query");
        if (naturalQuery == null || naturalQuery.trim().isEmpty()) {
            return ResponseEntity.badRequest()
                .body(Map.of("success", false, "error", "질문을 입력해주세요."));
        }

        if (naturalQuery.length() > 1000) {
            return ResponseEntity.badRequest()
                .body(Map.of("success", false, "error", "질문이 너무 깁니다."));
        }

        // 안전한 처리 과정...
    }
}

📈 성능 메트릭 및 모니터링

캐시 통계 API

@GetMapping("/cache/stats")
@PreAuthorize("hasRole('ADMIN')")
public ResponseEntity<?> getCacheStats() {
    QueryCacheService.CacheStats stats = queryCacheService.getDetailedCacheStats();

    Map<String, Object> response = new HashMap<>();
    response.put("success", true);
    response.put("cacheSize", stats.getCacheSize());
    response.put("totalRequests", stats.getTotalRequests());
    response.put("hits", stats.getHits());
    response.put("misses", stats.getMisses());
    response.put("hitRate", Math.round(stats.getHitRate() * 100.0) / 100.0);

    return ResponseEntity.ok(response);
}

실제 성능 개선 결과

캐시 히트율: 평균 85-90%
응답 시간: 평균 2-3초 → 0.1-0.3초
API 비용: 월 예상 비용 90% 절감

🎯 실제 사용 예시

질문과 응답 예시

질문: "이번달 지역별 매출을 보여주세요"

생성된 SQL:

SELECT
    place_name AS 지역,
    SUM(total_price) AS 매출,
    COUNT(*) AS 예약건수
FROM reservations
WHERE EXTRACT(month FROM created_at) = EXTRACT(month FROM CURRENT_DATE)
    AND EXTRACT(year FROM created_at) = EXTRACT(year FROM CURRENT_DATE)
GROUP BY place_name
ORDER BY 매출 DESC;

결과: 지역별 매출을 막대 차트로 시각화하여 표시

🔮 향후 개선 계획

1. 고급 분석 기능

시계열 예측 분석
트렌드 분석 및 인사이트 자동 생성
이상치 탐지 알림

2. 사용자 경험 개선

음성 인식 입력 지원
분석 결과 공유 기능
대시보드 위젯 자동 생성

3. 성능 최적화

쿼리 실행 계획 최적화
더 스마트한 캐싱 전략
실시간 데이터 스트리밍

📝 개발 후기

이번 프로젝트를 통해 다음과 같은 인사이트를 얻었습니다:

LLM의 실용적 활용: 단순한 챗봇을 넘어 실제 비즈니스 문제를 해결하는 도구로 활용
캐싱의 중요성: API 비용과 성능 모두를 크게 개선할 수 있는 핵심 기술
보안 우선 설계: AI 시스템에서 보안은 선택이 아닌 필수
사용자 중심 UX: 복잡한 기술을 간단한 인터페이스로 포장하는 것의 중요성

🛠️ 기술 스택 요약

Backend

Spring Boot 3.4.3
Google Gemini API
PostgreSQL
Redis (캐싱)
Spring Security (인증/권한)

Frontend

React 19 + TypeScript
Material-UI
Chart.js
Styled Components

DevOps

Docker
Gradle

TravelLight의 AI 어시스턴트는 현재 실제 서비스에서 활발히 사용되고 있으며, 관리자들의 데이터 분석 업무를 크게 개선했습니다. 자연어로 복잡한 비즈니스 질문을 하면 즉시 시각화된 답변을 받을 수 있어, 의사결정 속도가 획기적으로 향상되었습니다.

이 글이 비슷한 기능을 구현하려는 개발자들에게 도움이 되기를 바랍니다. 궁금한 점이 있다면 언제든 댓글로 남겨주세요!