Big Query_ARRAY & STRUCT

BigQuery Unnest | Array | Struct

• Google Analytics 및 Firebase 데이터 작업시 Cannot access field name on a value with type ARRAY 에러가 종종 발발함
• BigQuery는 비정규화되었을 때 성능이 가장 뛰어남
• 비정규화를 위해 중첩(Nested) 및 반복(Repeated) 열을 사용함
• 중첩된 레코드를 사용할 때 장점
  • 빅쿼리 퍼포먼스 개선
  • 데이터 저장 용량의 효율
  • 스키마가 바뀌어도 유연하게 대응 가능

BigQuery Data 유형

1. ARRAY(배열)

1. 설명

• 데이터 유형이 동일한 값으로 구성된 목록
• LIST와 비슷한 특성
• 간단한 데이터 유형(INT64)와 복합 데이터 유형(ex. STRUCT)
• Array의 Array는 생성할 수 없음으로 복합 데이터는 SELECT AS STRUCT를 이용하여 STRUCT를 ARRAY에 넣어야 함
• 하나의 행에 동일한 데이터 타입 but 다른 값이 저장됨
• 한 행에 NEST된 형식으로 여러 값을 가지는 행태
• SELECT [a,b,c] AS X or SELECT ARRAY<INT64>[1,2,3]

Row	ARRAY_값	ARRAY_num
1	a	1
	b
	c

2. 관련 함수

• GENERATE_ARRAY(start, end, step)
• GENERATE_DATE_ARRAY
• GENERATE_TIMESTAMP_ARRAY
• ARRAY_LENGTH

3. UNNEST

• ARRAY의 요소를 테이블의 행으로 변환
• ARRAY 순서를 무시하기 때문에 순서가 arrange 되어야 한다면 WITH OFFSET(인덱스) 이용하여 ORDER BY 진행
• 여러 Row가 존재할 때, 행별 ROW의 모든 값을 유지하고 싶으면 CROSS JOIIN을 이용
• UNNEST는 CROSS JOIN
• CROSS JOIN은 하위 테이블에 값이 없으면 상위 테이블의 값도 사라짐
• 그러니까 event name을 기준으로 items를 하위 테이블로 unnest 할 경우, items가 존재하는 event만 남아있는다는 것
• 만약 SELECT 구문에서 (SELECT ARRAY_AGG(item.item_category) FROM UNNEST(items) AS item)AS category 할 경우, items와 엮이지 않는 event_name의 다른 select 값들은 문제 없이 나옴

WITH sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers

FROM sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers;
--FROM sequences, sequences.some_numbers AS flattened_numbers; 한 줄로도 가능함 

id	flattened_numbers
1	0
1	1
1	1
1	2
1	3
1	5
2	2
2	4
2	8
2	16
2	32
3	5
3	10

OFFSET vs ORDINAL

• 0부터 시작하는 Index OFFSET
• 1부터 시작하는 Index ORDINAL

4. ARRAY_AGG

• 여러 값을 하나의 ARRAY로 합치는 역할(List와 같이 생성)
• 집계함수 중 하나
  • 집계함수란 그룹의 행을 단일 값으로 요약하는 함수
  • 집계함수에 OVER를 함께 사용할 경우 행 그룹에 대해 값을 계산 한 후, 각 행별로 결과를 반환하는 WINDOW 함수로 됨
• 배열 안에 NULL이 하나라도 있는 경우, ARRAY_AGG의 결과는 NULL이 나오기 때문에, IGNORE NULLS를 추가하여 NULL을 제외하고 연산함

SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS LIMIT 5) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

-- ORDER BY도 사용 가능함
SELECT ARRAY_AGG(DISTINCT x ORDER BY x) AS array_agg

array_agg
[2, 1, -2, 3]

배열의 N번째 값을 가져오고 싶은 경우 OFFSET, ORDINAL을 사용할 수 있음
OFFSET : 0부터 시작
ORDINAL : 1부터 시작
존재하지 않는 N을 지정하면 에러가 발생하는데, 이럴 경우 SAFE_를 앞에 붙여주면(SAFE_OFFSET, SAFE_ORDINAL) 에러가 발생하지 않고 NULL이 return됨

5. ARRAY_LENGTH

ARRAY 참고페이지

6. ARRAY에 특정 조건이 일치하는 값을 검색하고 싶은 경우
   EXISTS 사용
   IN 조건이 여러개 있는 경우에도 사용 가능

  WHERE EXISTS 
    (
      SELECT * 
      FROM UNNEST(example_data.preferred_language) as prefer_lang
      WHERE prefer_lang IN ('Go','Scala')
    )

2. STRUCT

1. 소괄호() 사용
   SELECT (1,2,3) AS struct_test

2.STRUCT<> 사용

• <> 안에 타입을 지정해서 사용
  - Column명 & Type 설정 가능
  SELECT STRUCT<지정이름1 INT64, 지정이름2 INT64, 지정이름3 STRING>(10, 20, 'BQ') AS new_struct
  • Column명 설정 가능

    SELECT STRUCT<10 AS 지정이름1, 20 AS 지정이름2, 'BQ' AS 지정이름3) AS new_struct

3. ARRAY 안 STRUCT 가능

1. hits.eCommerceAction.action_type

• Click through of product lists = 1, Product detail views = 2, Add product(s) to cart = 3, Remove product(s) from cart = 4, Check out = 5, Completed purchase = 6, Refund of purchase = 7, Checkout options = 8, Unknown = 0.

SELECT
    COUNT(hits.product.v2ProductName),
FROM
    [foo-160803:123456789.ga_sessions_20170101]
WHERE
    hits.ecommerceaction.action_type = '2'
    AND ( BOOLEAN(hits.product.isImpression) IS NULL OR BOOLEAN(hits.product.isImpression) == FALSE )

2. hits.eCommerceAction.step

• This field is populated when a checkout step is specified with the hit.

3. hits.hour
4. hits.minute
5. hits.time

• The number of milliseconds after the visitStartTime when this hit was registered. The first hit has a hits.time of 0

6. hits.product

• This row and nested fields will be populated for each hit that contains Enhanced Ecommerce PRODUCT data.

7. hits.product.productPrice

• The price of the product, expressed as the value passed to Analytics multiplied by 10^6 (e.g., 2.40 would be given as 2400000).

8. hits.product.productQuantity

• The quantity of the product purchased.