Big Query 데이터 타입

16 Feb 2020 • Posts

전통적인 방식 (MySQL)의 저장 방법은 fruit, person에 대한 정보를 저장하려면 2개의 table이 필요하다.

  • 이러한 프로세스를 normlization이라 한다.

하지만, data warehousing (BigQuery)에서는 방향을 반대로(?) 해서 2개 테이블을 하나의 거대한 테이블로 만든다.

  • 이는 denormalization이라 한다.
  • 이는 table row size가 전통적인 방식보다 더 거대해진다. (중복이 발생하기 때문)

repeated field

  • array 타입으로 데이터를 저장할 수 있다.
    • 다른 level로 데이터를 저장한다.
      • 1 level - Row (1, 2), Fruit (Array), Person (sally, frederick)
      • 2 level - Fruit의 array ([raspberry, blackberry, strawberry, cherry], [orange, apple])들
    • fruit는 array 형태로 fruit 내부에 여러 데이터들이 들어가 있기 때문에 이를 repeated field라 한다.

1

  • 특징
    • array 타입은 모두 같은 형식 (string, integer 등)을 가져야 한다.
    • array 타입은 아래와 같이 가져올 수 있다.
    • BigQuery에서는 array를 flatted 하게 표현한다. 즉, vertically한 array로 표현함.
#standardSQL
SELECT
['raspberry', 'blackberry', 'strawberry', 'cherry'] AS fruit_array
  • 정리
    • BigQuery는 array 타입을 지원하며, 같은 데이터 형식을 가져야 한다.
  • JSON 데이터를 읽게 되면, JSON 내부에 다른 level을 가진 field가 있는데 이는 array 형태로 저장된다.

Array 관련 함수

  • ARRAY_AGG()를 통해 string을 array 형태로 만든다.
    • 일반적인 sql를 사용한다면?
SELECT
  fullVisitorId,
  date,
  v2ProductName,
  pageTitle
  FROM `data-to-insights.ecommerce.all_sessions`
WHERE visitId = 1501570398
ORDER BY date

return 되는 row 수는 111개

  • ARRAY_AGG()를 사용한다면?
SELECT
  fullVisitorId,
  date,
  ARRAY_AGG(v2ProductName) AS products_viewed,
  ARRAY_AGG(pageTitle) AS pages_viewed
  FROM `data-to-insights.ecommerce.all_sessions`
WHERE visitId = 1501570398
GROUP BY fullVisitorId, date
ORDER BY date
  • return 되는 row 수는 2개

  • v2ProductName이랑 PageTitle은 array 타입이므로, string을 array 형태로 aggregation 하여 반환하게 된다.

  • ARRAY_LENGTH()를 통해 array field의 크기를 알 수 있다.
SELECT
  fullVisitorId,
  date,
  ARRAY_AGG(v2ProductName) AS products_viewed,
  ARRAY_LENGTH(ARRAY_AGG(v2ProductName)) AS num_products_viewed,
  ARRAY_AGG(pageTitle) AS pages_viewed,
  ARRAY_LENGTH(ARRAY_AGG(pageTitle)) AS num_pages_viewed
  FROM `data-to-insights.ecommerce.all_sessions`
WHERE visitId = 1501570398
GROUP BY fullVisitorId, date
ORDER BY date
  • ARRAY_AGG()를 할 때 DISTINCT를 사용함으로써, 중복 값을 제거할 수 있다.
SELECT
  fullVisitorId,
  date,
  ARRAY_AGG(DISTINCT v2ProductName) AS products_viewed,
  ARRAY_LENGTH(ARRAY_AGG(DISTINCT v2ProductName)) AS distinct_products_viewed,
  ARRAY_AGG(DISTINCT pageTitle) AS pages_viewed,
  ARRAY_LENGTH(ARRAY_AGG(DISTINCT pageTitle)) AS distinct_pages_viewed
  FROM `data-to-insights.ecommerce.all_sessions`
WHERE visitId = 1501570398
GROUP BY fullVisitorId, date
ORDER BY date
  • 정리
    • ARRAY_LENGTH()
    • ARRAY_AGG(DISTINCT )
    • ARRAY_AGG( ORDER BY )
    • ARRAY_AGG( LIMIT 5)

이미 Array를 가지는 데이터셋

  • Array로 저장된 데이터는 일반적인 Query로 조회할 수 없으며, 이를 조회하기 위해 array를 각 row들로 쪼개야 한다.
SELECT
  visitId,
  hits.page.pageTitle
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
WHERE visitId = 1501570398

위 SQL은 에러가 발생한다.

  • 이때 UNNEST() 함수를 사용한다.

    • Before: ['value1', 'value2', 'value3']

    • After:

      [
'value1',
'value2',
'value3'
]

  • 아래와 같이 사용한다.

SELECT DISTINCT
  visitId,
  h.page.pageTitle
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) AS h
WHERE visitId = 1501570398
LIMIT 10

STRUCT

  • 1개 또는 여러 field로 이루어져 있다.

  • 서로 다른 데이터 형식(string, integer 등)을 가질 수 있다.

  • STRUCT 타입은 RECORD 형식으로 표현한다.

  • 아래 SQL을 통해 STRUCT 타입을 불러올 수 있다.
SELECT
  visitId,
  totals.*,
  device.*
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
WHERE visitId = 1501570398
LIMIT 10

STRUCT 예제

  • STRUCT() 타입을 만드는 방법
#standardSQL
SELECT STRUCT("Rudisha" as name, 23.4 as split) as runner

#standardSQL
SELECT STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner

JSON 예제

  • Json을 저장한 Table을 만들고 아래 SQL 수행
#standardSQL
SELECT * FROM racing.race_results
  • 만약 partitipants.name list를 가져오고 싶을때? 아래와 같이 사용하면 에러 발생.
#standardSQL
SELECT race, participants.name
FROM racing.race_results
  • CROSS JOIN을 사용해야하는데, 아래와 같이 사용하면 또 에러가 발생한다.
#standardSQL
SELECT race, participants.name
FROM racing.race_results
CROSS JOIN
participants  # this is the STRUCT (it is like a table within a table)
  • 아래와 같이 사용한다.
#standardSQL
SELECT race, participants.name
FROM racing.race_results
CROSS JOIN
race_results.participants # full STRUCT name
  • 같은 결과를 나타낸다
#standardSQL
SELECT race, participants.name
FROM racing.race_results AS r, r.participants

추가 사항

1.Lab Question: STRUCT()

#standardSQL
SELECT COUNT(participants.name) AS racer_count
FROM racing.race_results

#standardSQL
SELECT COUNT(p.name) AS racer_count
FROM racing.race_results AS r, UNNEST(r.participants) AS p

2.Lab Question: Unpacking ARRAYs with UNNEST( )

#standardSQL
SELECT
  p.name,
  SUM(split_times) as total_race_time
FROM racing.race_results AS r
, r.participants AS p
, p.splits AS split_times
WHERE
GROUP BY
ORDER BY
;

#standardSQL
SELECT
  p.name,
  SUM(split_times) as total_race_time
FROM racing.race_results AS r
, UNNEST(r.participants) AS p
, UNNEST(p.splits) AS split_times
WHERE p.name LIKE 'R%'
GROUP BY p.name
ORDER BY total_race_time ASC;

3.Filtering within ARRAY values

#standardSQL
SELECT
  p.name,
  split_time
FROM racing.race_results AS r
, r.participants AS p
, p.splits AS split_time
WHERE split_time = ;

#standardSQL
SELECT
  p.name,
  split_time
FROM racing.race_results AS r
, UNNEST(r.participants) AS p
, UNNEST(p.splits) AS split_time
WHERE split_time = 23.2;
 bigquery

Comments

Related Posts