11. Wildcard Tables

11.1. Wildcard Table 개요

11.1.1. Wildcard Table의 사용

• user_blog_202212 (2022년 12월)

• user_blog_202301 (2023년 01월)

• user_blog_202302 (2023년 02월)

• user_blog_202303 (2023년 03월)

11.1.2. 데이터 불러오기

1. 탐색기의 “+추가” 버튼 클릭 후, 공개 데이터셋을 선택합니다.





 

2. “google analytics sample”을 검색하고 데이터를 선택합니다.



 

3. 데이터세트 보기를 클릭하여 공개 데이터셋을 불러옵니다.

4. 데이터셋 내 테이블의 스키마와 미리 보기 등 정보를 확인할 수 있으며, 쿼리를 통한 작업이 가능합니다.

11.1.3. Wildcard Table 문법

SELECT *
FROM `<project-id>.<dataset-id>.<table-prefix>*`
WHERE bool_expression

• <project-id>: BigQuery에서 사용자의 데이터와 관련 설정을 저장하는 고유한 식별자입니다. GCP(Google Cloud Platform) 프로젝트에는 고유한 ID가 있으며, 이 ID를 통해 특정 데이터나 리소스에 액세스할 수 있습니다.

• <dataset-id>: 데이터셋의 이름 또는 고유 ID입니다.

• <table-prefix>: 테이블 이름의 접두사 또는 패턴을 지정하는 부분입니다. 여기에서 와일드카드 문자 '*'를 사용하여 특정 패턴을 가진 여러 테이블을 선택합니다.

• * (Wildcard Character): 여러 테이블을 동시에 참조하기 위해 이름의 일부를 대체하는 문자입니다. 와일드카드 문자는 테이블 이름의 마지막 문자로만 사용될 수 있으며, 이를 통해 이름이 특정 패턴으로 시작하는 여러 테이블을 동시에 참조할 수 있습니다. '*'는 특수 문자로 간주하므로 와일드카드 테이블 이름을 백틱(`) 문자로 묶어야 합니다.

SELECT
      fullVisitorId,
      date,
      visitNumber,
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170701`;

WITH GA AS (
  SELECT
        fullVisitorId,
        date,
        visitNumber,
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170101`
     )

SELECT
      MIN(date) AS min_date
    , MAX(date) AS max_date
FROM GA;

WITH GA AS (
  SELECT
        fullVisitorId
      , date
      , visitNumber
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
     )

SELECT
      MIN(date) AS min_date
    , MAX(date) AS max_date
FROM GA;

SELECT
		  fullVisitorId
		, COUNT(DISTINCT visitId) AS visitCount
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
GROUP BY fullVisitorId
ORDER BY visitCount DESC
LIMIT 10;

1. 쿼리 범위 설정: 와일드카드를 사용할 때는 _TABLE_SUFFIX와 같은 필터를 사용하여 조회할 테이블의 범위를 명확하게 설정해야 합니다. 그렇지 않으면 예상치 못한 데이터양과 비용이 발생할 수 있습니다.

2. 비용 관리: 와일드카드 쿼리는 많은 양의 데이터를 스캔할 수 있으므로 쿼리 비용이 상승할 수 있습니다. BigQuery의 가격 정책을 이해하고 쿼리 전에 예상 비용을 계산하는 것이 중요합니다.

3. 성능 최적화: 가능한 한 필요한 데이터만 스캔하도록 쿼리를 정밀하게 조정합니다. 불필요한 데이터 스캔은 비용 증가뿐만 아니라 쿼리 성능 저하로도 이어질 수 있습니다.

4. 패턴 일치: 와일드카드는 매우 유연하기 때문에 예상치 못한 테이블이 포함될 수 있습니다. 따라서 쿼리를 실행하기 전에 매칭되는 테이블 목록을 확인하는 것이 좋습니다.

11.2. 테이블 필터링

11.2.1. _TABLE_SUFFIX

FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`

WITH GA AS (
  SELECT
        fullVisitorId,
        date,
        visitNumber,
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
  WHERE _TABLE_SUFFIX BETWEEN "10" AND "20"
     )

SELECT
      MIN(date) AS min_date
    , MAX(date) AS max_date
FROM GA;

SELECT
		  fullVisitorId AS customer_id
		, MAX(totals.totalTransactionRevenue) AS max_revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
WHERE _TABLE_SUFFIX BETWEEN '10' AND '20'
	AND totals.transactions IS NOT NULL
GROUP BY customer_id
ORDER BY max_revenue DESC
LIMIT 1;

11.2.2. 모든 테이블 스캔

FROM `bigquery-public-data.google_analytics_sample.*`

# Prefix 사용
SELECT
		  fullVisitorId AS customer_id
		, MAX(totals.totalTransactionRevenue) AS max_revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
WHERE _TABLE_SUFFIX BETWEEN '10' AND '20'
	AND totals.transactions IS NOT NULL
GROUP BY customer_id
ORDER BY max_revenue DESC
LIMIT 1;

#-----------------------------------------------------------------------------

# Prefix 미사용 (빈 접두어를 사용하기 때문에 일반적으로 성능이 더 낮음)
SELECT
		  fullVisitorId AS customer_id
		, MAX(totals.totalTransactionRevenue) AS max_revenue
FROM `bigquery-public-data.google_analytics_sample.*`
WHERE _TABLE_SUFFIX BETWEEN 'ga_sessions_20170710' AND 'ga_sessions_20170720'
	AND totals.transactions IS NOT NULL
GROUP BY customer_id
ORDER BY max_revenue DESC
LIMIT 1;