Slide 12
Slide 12 text
BigQuery によるデータ ウェアハウスの構築と最適化: チャレンジラボ
タスク 1: 日付でテーブルをパーティション分割する
1. データセット作成 (covid_***)
2. Publicテーブルから無駄データ
(GBRとか)を弾き出しつつ、日付分割テーブルを作成
(oxford_policy_tracker_***)
3. 以下、無茶なクエリ
CREATE OR REPLACE TABLE
`qwiklabs-gcp-04-0ba24a4a122b.covid_YOURDATASET.oxford_policy_tracker_YOURTABLE`
(
country_name STRING OPTIONS(description="Name of the country"),
alpha_3_code STRING OPTIONS(description="3-letter alpha code abbreviation of the country/region. See `bigquery-public-data.utility_us.country_code_iso` for more details"),
region_name STRING OPTIONS(description="Name of the region within the country"),
region_code STRING OPTIONS(description= "Code of the region within the country"),
date DATE OPTIONS(description="Date of the measured policy action status"),
school_closing STRING OPTIONS(description="C1 - Ordinal scale record closings of schools and universities; 0 - No measures 1 - recommend closing 2 - Require closing (only some levels or categories eg just high school or just public schools) 3 - Require closing all levels No data -
blank"),
school_closing_flag STRING OPTIONS(description="Are C1 actions targeted at specific areas or general:0 - Targeted 1- General No data - blank"),
school_closing_notes STRING OPTIONS(description="Additional details about C1 policy actions"),
workplace_closing STRING OPTIONS(description="C2 - Ordinal scale record closings of workplace; 0 - No measures 1 - recommend closing (or work from home) 2 - require closing (or work from home) for some sectors or categories of workers 3 - require closing (or work from home)
all-but-essential workplaces (eg grocery stores doctors) No data - blank"),
workplace_closing_flag STRING OPTIONS(description="Are C2 actions targeted at specific areas or general:0 - Targeted 1- General No data - blank"),
workplace_closing_notes STRING OPTIONS(description="Additional details about C2 policy actions"),
cancel_public_events STRING OPTIONS(description="C3 - Ordinal scale record cancellations of public events;0- No measures 1 - Recommend cancelling 2 - Require cancelling No data - blank"),
cancel_public_events_flag STRING OPTIONS(description="Are C3 actions targeted at specific areas or general:0 - Targeted 1- General No data - blank"),
cancel_public_events_notes STRING OPTIONS(description="Additional details about C3 policy actions"),
restrictions_on_gatherings STRING OPTIONS(description="C4 - Ordinal scale to record the cut-off size for bans on private gatherings; 0 - No restrictions 1 - Restrictions on very large gatherings (the limit is above 1000 people) 2 - Restrictions on gatherings between 100-1000 people
3 - Restrictions on gatherings between 10-100 people 4 - Restrictions on gatherings of less than 10 people No data - blank"),
restrictions_on_gatherings_flag STRING OPTIONS(description="Are C4 actions targeted at specific areas or general:0 - Targeted 1- General No data - blank"),
restrictions_on_gatherings_notes STRING OPTIONS(description="Additional details about C4 policy actions"),
close_public_transit STRING OPTIONS(description="C5 - Ordinal scale to record closing of public transportation; 0 - No measures 1 - Recommend closing (or significantly reduce volume/route/means of transport available) 2 - Require closing (or prohibit most citizens from using it)"),
close_public_transit_flag STRING OPTIONS(description="Are C5 actions targeted at specific areas or general:0 - Targeted 1- General No data - blank"),
close_public_transit_notes STRING OPTIONS(description="Additional details about C5 policy actions"),
stay_at_home_requirements STRING OPTIONS(description="C6 - Ordinal scale record of orders to \u201cshelter-in- place\u201d and otherwise confine to home."),
stay_at_home_requirements_flag STRING OPTIONS(description="Are C6 actions targeted at specific areas or general:0 - Targeted 1- General No data - blank\"\\"),
stay_at_home_requirements_notes STRING OPTIONS(description="Additional details about C6 policy actions"),
restrictions_on_internal_movement STRING OPTIONS (description="C7 - Ordinal scale of restrictions on internal movement; 0 - No measures 1 - Recommend closing (or significantly reduce volume/route/means of transport) 2 - Require closing (or prohibit most people from using
it)"),
restrictions_on_internal_movement_flag STRING OPTIONS(description="Are C7 actions targeted at specific areas or general:0 - Targeted 1- General No data - blank"),
restrictions_on_internal_movement_notes STRING OPTIONS(description="Additional details about C7 policy actions"),
international_travel_controls STRING OPTIONS(description="C8 - Ordinal scale record of restrictions on international travel; 0 - No measures 1 - Screening 2 - Quarantine arrivals from high-risk regions 3 - Ban on high-risk regions 4 - Total border closure No data - blank"),
international_travel_controls_notes STRING OPTIONS(description="Additional details about C8 policy actions"),