Big Query 기본

Console 창

• gcloud: GCP를 위한 command-line tool . Cloud Shell에 설치되어 있다.
  • 명령
    • gcloud auth list
      • 현재 active 한 account name을 출력한다.
    • gcloud config list project
      • project Id를 list 출력할 수 있다.
• gsutil: 각종 util 명령을 위한 tool이다.
  • 명령
    • gsutil cp gs://.../.../ .
      • 특정 경로에서 데이터를 복사 후 현재 경로에 저장한다.

• bq: bigquery 내 있는 Dataset, Table, 데이터를 조회하기 위한 명령어 (DML, DDL 등)

  • 명령

    • bq show bigquery-public-data:samples.shakespeare

      • bigquery-public-data Dataset의 samples.shakespear Table에 대한 스키마 정보를 볼 수 있다.

    • bq help query

      • query를 하는데 필요한 명령어들을 볼 수 있다.
      • 아래는 query의 예시이다.

      bq query --use_legacy_sql=false \
      'SELECT
         word,
         SUM(word_count) AS count
       FROM
         `bigquery-public-data`.samples.shakespeare
       WHERE
         word LIKE "%raisin%"
       GROUP BY
         word'
      

      • SQL문을 사용해 질의를 날릴 수 있다.

    • bq ls

      • 현재 내 Project에 있는 dataset을 볼 수 있다.
      • bq ls bigquery-public-data:
      • 이처럼 뒤에 Project Id를 쓰면 특정 Project Id의 dataset들을 볼 수 있다.
      • 뿐만 아니라, 뒤에 dataset의 이름을 쓰면 dataset 내 있는 Table들을 볼수도 있다.

    • bq mk babynames

      • babynames 라는 dataset을 만든다.

    • bq load babynames.names2010 yob2010.txt name:string,gender:string,count:integer

      • babynames에 특정 데이터를 load 할 수 있다. 사용법은 아래와 같다.
      • bq load <dataset.table> <file> <schema>

    • bq rm -r babynames

      • table 이나 dataset을 지울 수 있다.

Date-partitioned Table

• partitiononed table은 table을 여러 segments들로 나누며 이를 partition이라 부른다.

  • 파티션은 쉽게 관리할 수 있으며, 쿼리를 효율적으로 수행하며 비용을 낮출 수 있다.
  • 보통은 날짜를 기준으로 partition을 많이 한다.

• 파티션이 없다면…

  #standardSQL
  SELECT DISTINCT
    fullVisitorId,
    date,
    city,
    pageTitle
  FROM `data-to-insights.ecommerce.all_sessions_raw`
  WHERE date = '20180708'
  LIMIT 5
  

  • 단순 위와같은 Query를 수행했을 때, date 중 20180708이 없더라도 data-to-insights.ecommerce.all_sessions_raw에 있는 모든 데이터를 Scan 한다.

• Date-partitioned Table 만드는 방법

  • #standardSQL
     CREATE OR REPLACE TABLE ecommerce.partition_by_day
     PARTITION BY date_formatted
     OPTIONS(
       description="a table partitioned by date"
     ) AS
        
     SELECT DISTINCT
     PARSE_DATE("%Y%m%d", date) AS date_formatted,
     fullvisitorId
     FROM `data-to-insights.ecommerce.all_sessions_raw`
    

  • data-to-insights.ecommerce.all_sessions_raw에 데이터를 partition_by_day 이름을 가진 Table로 만든다.

  • Table을 만들 때 PARTITION By 를 통해 날짜 정보를 기반으로 Partition 한다.

  • partition 된 테이블은 Detail에 들어가 어떤 Field를 기준으로 Partition 되었는지 볼 수 있다.

자동 만료되는 partitioned table

#standardSQL
 CREATE OR REPLACE TABLE ecommerce.days_with_rain
 PARTITION BY date
 OPTIONS (
   partition_expiration_days=60,
   description="weather stations with precipitation, partitioned by day"
 ) AS


 SELECT
   DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date,
   (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations
    WHERE stations.usaf = stn) AS station_name,  -- Stations may have multiple names
   prcp
 FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather
 WHERE prcp < 99.9  -- Filter unknown values
   AND prcp > 0      -- Filter
   AND CAST(_TABLE_SUFFIX AS int64) >= 2018

• Option에 partition_expiration_days을 기록한다.
• 위 예제는 60이므로, 60일 까지만 Date Partition을 유지한다.