Looker가 SQL을 생성하는 방법

SQL 배경에서 Looker를 알게 되면 Looker에서 SQL을 생성하는 방법이 궁금해질 수 있습니다. 기본적으로 Looker는 SQL 쿼리를 생성하여 데이터베이스 연결에 대해 제출하는 도구입니다. Looker는 데이터베이스에서 테이블과 열 간의 관계를 설명하는 LookML 프로젝트를 기반으로 SQL 쿼리를 작성합니다. Looker에서 쿼리를 생성하는 방법을 이해하면 LookML 코드가 효율적인 SQL 쿼리로 변환하는 방법을 이해할 수 있습니다.

모든 LookML 매개변수는 쿼리의 구조, 콘텐츠 또는 동작을 변경하여 Looker에서 SQL을 생성하는 방법의 일부를 제어합니다. 이 페이지에서는 Looker에서 SQL을 생성하는 방법의 원칙을 설명하지만 모든 LookML 요소를 자세히 다루지는 않습니다. LookML 매개변수는 LookML 빠른 참조 문서 페이지를 참조하세요.

쿼리 보기

저장된 Look 또는 Explore에서 SQL 패널의 SQL 탭을 사용하여 Looker에서 데이터를 가져오기 위해 데이터베이스로 보내는 항목을 확인할 수 있습니다. SQL Runner 탭 하단에 있는 SQL Runner에서 열기 및 SQL Runner에서 설명 링크를 사용하여 SQL Runner에서 쿼리를 보거나 데이터베이스의 쿼리 설명 계획을 확인할 수 있습니다.

SQL Runner에 대한 자세한 내용은 SQL Runner 기본사항 문서 페이지를 참조하세요. SQL Runner를 사용하여 쿼리를 최적화하는 방법에 대한 자세한 내용은 EXPLAIN으로 SQL을 최적화하는 방법 커뮤니티 게시물을 참조하세요.

Looker 쿼리의 표준 양식

Looker의 SQL 쿼리는 항상 다음 형식을 사용합니다.

SELECT
   <dimension>, <dimension>, ...
   <measure>, <measure>, ...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>, <dimension>, <dimension>, ...
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
ORDER BY <dimension> | <measure>
LIMIT <limit>

LookML 프로젝트는 SQL 쿼리에서 참조되는 모든 측정기준, 측정값, Explore, 뷰를 정의합니다. 필터 표현식은 사용자가 임시 쿼리를 형성하기 위해 Looker에서 지정합니다. LookML에서 필터 표현식을 직접 선언하여 모든 쿼리에 적용할 수도 있습니다.

Looker 쿼리의 기본 구성요소

모든 Looker 쿼리는 이전 예시 쿼리와 같이 LookML 프로젝트에 적용되는 이러한 기본 매개변수로 표현됩니다.

Looker는 다음 매개변수를 사용하여 전체 SQL 쿼리를 생성합니다.

• model: 대상으로 지정할 LookML 모델의 이름으로, 대상 데이터베이스를 지정합니다.
• explore: 쿼리할 Explore의 이름으로, SQL FROM 절을 채웁니다.
• 필드: 쿼리에 포함할 dimension 및 measure 매개변수로, SQL SELECT 절을 채웁니다.
• filter: 0개 이상의 필드에 적용할 Looker 필터 표현식으로, SQL WHERE 및 HAVING 절을 채웁니다.
• 정렬 순서: 정렬할 필드와 정렬 순서로, SQL ORDER BY 절을 채웁니다.

이러한 매개변수는 사용자가 Looker Explore 페이지에서 쿼리를 빌드할 때 지정하는 요소입니다. 생성된 SQL, 쿼리를 나타내는 URL, Looker API와 같이 Looker를 사용하여 쿼리를 실행하는 모든 모드에 동일한 요소가 표시됩니다.

LEFT JOIN 절에서 지정한 뷰는 어떤가요? JOIN 절은 뷰가 Explore에 조인되는 방법을 지정하는 LookML 모델의 구조에 따라 채워집니다. SQL 쿼리를 생성할 때 필요한 경우에만 Looker에 JOIN 절이 포함됩니다. 사용자가 Looker에서 쿼리를 빌드할 때 테이블이 함께 조인되는 방식을 지정할 필요가 없습니다. 이 정보는 모델에서 인코딩되기 때문이며 이는 비즈니스 사용자에게 Looker의 가장 강력한 이점 중 하나입니다.

쿼리 예시 및 결과 SQL

이전 패턴에 따라 쿼리가 생성되는 방법을 보여주기 위해 Looker에서 쿼리를 빌드해 보겠습니다. 주문과 사용자라는 테이블 2개가 있는 데이터베이스가 있는 전자상거래 스토어에서 사용자와 주문을 추적한다고 고려해 보겠습니다.

Looker Explore에서 상태(USERS 상태)별로 그룹화되고 주문 생성 날짜(ORDERS 생성 날짜)로 필터링된 주문 수(ORDERS 수)를 찾아보겠습니다.

Looker에서 생성하고 실행하는 SQL 쿼리를 보려면 SQL 패널에서 SQL 탭을 클릭합니다.

SELECT COALESCE(users.state, ' ') AS "_g1",
   users.state AS 'users.state',
   COUNT(DISTINCT orders.id) AS 'orders.count'
FROM orders
LEFT JOIN users ON orders.user_id = users.id

WHERE
  orders.created_at BETWEEN (CONVERT_TZ(DATE_ADD(CURDATE(), INTERVAL -29 day), 'America/Los_Angeles', 'UTC',)) AND (CONVERT_TZ(DATE_ADD(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -29 day), INTERVAL 30 day), INTERVAL -1 second), 'America/Los_Angeles', 'UTC'))
GROUP BY 1
ORDER BY COUNT(DISTINCT orders.id) DESC
LIMIT 500

표준 쿼리 수식과의 유사성에 유의하세요. Looker SQL은 머신 생성 코드의 일부 특성(예: COALESCE(users.state,'') AS "_g1")을 제공하지만 항상 수식에 적합합니다.

Looker에서 더 많은 쿼리를 실험하여 쿼리 구조가 항상 동일함을 증명합니다.

Looker의 SQL Runner에서 원시 SQL 실행

Looker에는 Looker에서 설정한 데이터베이스 연결에 대해 원하는 SQL을 실행할 수 있는 SQL Runner라는 기능이 포함되어 있습니다.

Looker에서 생성되는 모든 쿼리로 완벽하게 작동하는 SQL 명령어를 만들 수 있으므로 SQL Runner를 사용하여 쿼리로 조사하거나 실행할 수 있습니다.

SQL Runner에서 실행되는 원시 SQL 쿼리는 동일한 결과 세트를 생성합니다. SQL에 오류가 있으면 SQL Runner는 SQL 명령어의 첫 번째 오류 위치를 강조표시하고 오류 메시지에 오류 위치를 포함합니다.

확장 URL에서 쿼리 구성요소 검사

Looker에서 쿼리를 실행한 후 확장된 URL을 검사하여 Looker 쿼리의 기본 구성요소를 확인할 수 있습니다. Explore의 톱니바퀴 메뉴에서 공유를 선택하여 URL 공유 메뉴를 엽니다.

확장된 URL은 쿼리를 다시 만들기에 충분한 정보를 제공합니다. 예를 들어 이 확장된 URL 예시에서는 다음 정보를 제공합니다.

https://<Looker instance URL>.cloud.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500

Looker에서 JOIN을 구조화하는 방법

이전 쿼리 예시에서 orders Explorerk 기본 FROM 절에 표시되고 조인된 뷰가 LEFT JOIN 절에 표시되는지 확인합니다. Looker 조인을 다양한 방법으로 작성할 수 있습니다. 자세한 내용은 LookML에서 조인으로 작업하기 페이지를 참조하세요.

SQL 블록에서 커스텀 SQL 절 지정

Looker 쿼리의 모든 요소가 머신에서 생성되지는 않습니다. 특정 시점에서 데이터 모델이 Looker가 기본 테이블에 액세스하고 파생된 값을 계산하기 위해 특정 세부정보를 제공해야 합니다. LookML에서 SQL 블록은 Looker에서 전체 SQL 표현식을 합성하는 데 사용하는 데이터 모델러가 제공하는 SQL 코드 스니펫입니다.

가장 일반적인 SQL 블록 매개변수는 측정기준 및 측정 정의에 사용되는 sql입니다. sql 매개변수는 기본 열을 참조하거나 집계 함수를 수행하도록 SQL 절을 지정합니다. 일반적으로 sql_로 시작하는 모든 LookML 매개변수는 일부 형식의 SQL 표현식을 사용합니다. 예를 들면 sql_always_where, sql_on, sql_table_name입니다. 각 매개변수에 대한 자세한 내용은 LookML 참조를 확인하세요.

측정기준 및 측정값에 대한 SQL 블록 예

다음 코드 샘플은 측정기준 및 측정값에 대한 SQL 블록의 몇 가지 예를 제공합니다. LookML 대체 연산자($)를 사용하면 이러한 sql 선언이 SQL과 달리 기만적으로 표시됩니다. 하지만 대체가 발생하면 결과 문자열은 순수 SQL이며 Looker가 쿼리의 SELECT 절에 이 문자열을 삽입합니다.

dimension: id {
  primary_key: yes
  sql: ${TABLE}.id ;;  # Specify the primary key, id
}
measure: average_cost {
  type: average
  value_format: "0.00"
  sql: ${cost} ;;      # Specify the field that you want to average
                       # The field 'cost' is declared elsewhere
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: number
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

이 예시의 마지막 두 측정기준과 같이 SQL 블록은 기본 데이터베이스에서 지원하는 함수(예: 이 경우 MySQL 함수 CONCAT 및 DATEDIFF)를 사용할 수 있습니다. SQL 블록에서 사용하는 코드는 데이터베이스에서 사용하는 SQL 언어와 일치해야 합니다.

파생 테이블의 SQL 블록 예시

또한 파생된 테이블은 SQL 블록을 사용하여 테이블을 파생하는 쿼리를 지정합니다. 다음은 SQL 기반 파생 테이블의 예시입니다.

view: user_order_facts {
  derived_table: {
    sql:
      SELECT
        user_id
        , COUNT(*) as lifetime_orders
      FROM orders
      GROUP BY 1 ;;
  }

  # later, dimension declarations reference the derived column(s)…
  dimension: lifetime_orders {
    type: number
  }
}

Explore 필터링용 SQL 블록 예시

sql_always_where 및 sql_always_having LookML 매개변수를 사용하면 SQL 블록을 SQL WHERE 또는 HAVING 절에 삽입하여 쿼리에 사용할 수 있는 데이터를 제한할 수 있습니다. 이 예시에서는 LookML 대체 연산자 ${view_name.SQL_TABLE_NAME}이 파생된 테이블을 참조하는 데 사용됩니다.

explore: trips {
  view_label: "Long Trips"
  # This will ensure that we only see trips that are longer than average!
  sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}