console + data

gcloud config set project qwiklabs-gcp-02-806cc13914e8

gcloud config list project

bq show bigquery-public-data:samples.shakespeare



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'


Use the bq ls command to list any existing datasets in your project:



# 현재 내 dataset
bq ls

# 오픈 데이터셋
bq ls bigquery-public-data:


# make dataset
bq mk babynames

curl -LO http://www.ssa.gov/OACT/babynames/names.zip

unzip names.zip

# 스키마 지정해서 업로드
bq load babynames.names2010 yob2010.txt name:string,gender:string,count:integer

# 확인
bq show babynames.names2010


bq query "SELECT name,count FROM babynames.names2010 WHERE gender = 'F' ORDER BY count DESC LIMIT 5"

# 지우는 것도 가능
bq rm -r babynames

bq mk covid19_open_data

bq rm -r covid19_open_data

#$(gcloud config get-value core/project)

bq query --use_legacy_sql=false "CREATE OR REPLACE TABLE \`qwiklabs-gcp-00-5bbeff605d3f.covid19_open_data.covid19_open_data\` AS SELECT * FROM \`bigquery-public-data.covid19_open_data.covid19_open_data\`"



bq query --use_legacy_sql=false "select sum(cumulative_confirmed) from  \`bigquery-public-data.covid19_open_data.covid19_open_data\` where date = '2021-11-09'"


bq query --use_legacy_sql=false "select country_name, subregion1_name, sum(cumulative_deceased) from  \`bigquery-public-data.covid19_open_data.covid19_open_data\` group by country_name, subregion1_name"

country_name
subregion1_name (for state information)
cumulative_deceased




bq query --use_legacy_sql=false "select subregion1_name, sum(cumulative_deceased) from  \`bigquery-public-data.covid19_open_data.covid19_open_data\` where country_name = 'United States of America' group by subregion1_name"

bq query --use_legacy_sql=false "select distinct(country_name) from  \`bigquery-public-data.covid19_open_data.covid19_open_data\`"