<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 02. Connect datalab
datalab은 Google Cloud에서 서비스하는 Jupyter notebook이라고 보면 됩니다
최근 20170818 버전부터 python2, python3 커널을 사용할 수 있습니다 (기존에는 python2만 해당)
datalab은 Google Cloud Storage / BigQuery 등에서 IO 속도가 매우 빠릅니다
datalab install
datalab connect [datalab instance 명]
<img src="../images/007_connect_datalab.png" width="700" height="700">
- Google Datalab의 모습
Step1:  pandas dataframe으로 변환
Step2:  %bq magic command를 이용한 project sample 확인
Step3:  %%bq query를 사용해 바로 query문을 날림
Step4:  매직커맨드를 활용해 pandas data frame 생성
Step5:  google chart api를 활용해 바로 chart 그리기 가능
<ASSISTANT_TASK:>
Python Code:
import google.datalab.bigquery as bq
# Query 생성
query_string = '''
              #standardSQL
              SELECT corpus AS title, COUNT(*) AS unique_words
              FROM `publicdata.samples.shakespeare`
              GROUP BY title
              ORDER BY unique_words DESC
              LIMIT 10
              '''
query = bq.Query(query_string)
output_options = bq.QueryOutput.table(use_cache=True)
result = query.execute(output_options=output_options).result() # query 실행
result
Explanation: 02. Connect datalab
datalab은 Google Cloud에서 서비스하는 Jupyter notebook이라고 보면 됩니다
최근 20170818 버전부터 python2, python3 커널을 사용할 수 있습니다 (기존에는 python2만 해당)
datalab은 Google Cloud Storage / BigQuery 등에서 IO 속도가 매우 빠릅니다
datalab install
datalab connect [datalab instance 명]
<img src="../images/007_connect_datalab.png" width="700" height="700">
- Google Datalab의 모습
End of explanation
pandas_df = result.to_dataframe()
pandas_df
sample_dataset = bq.Dataset('bigquery-public-data.samples')
# dataset이 존재하는지 유무
sample_dataset.exists() 
Explanation: pandas dataframe으로 변환
End of explanation
%bq datasets list --project cloud-datalab-samples
Explanation: %bq magic command를 이용한 project sample 확인
End of explanation
%%bq query
#standardSQL
SELECT corpus AS title, COUNT(*) AS unique_words
FROM `publicdata.samples.shakespeare`
GROUP BY title
ORDER BY unique_words DESC
LIMIT 10
Explanation: %%bq query를 사용해 바로 query문을 날림
End of explanation
%%bq query -n requests
SELECT timestamp, latency, endpoint
FROM `cloud-datalab-samples.httplogs.logs_20140615`
WHERE endpoint = 'Popular' OR endpoint = 'Recent'
df = requests.execute(output_options=bq.QueryOutput.dataframe()).result()
len(df)
df.head()
Explanation: 매직커맨드를 활용해 pandas data frame 생성
End of explanation
%%bq query --name data
WITH quantiles AS (
  SELECT APPROX_QUANTILES(LOG10(latency), 50) AS timearray
  FROM `cloud-datalab-samples.httplogs.logs_20140615`
  WHERE latency <> 0
)
select row_number() over(order by time) as percentile, time from quantiles cross join unnest(quantiles.timearray) as time
order by percentile
%chart columns --data data --fields percentile,time
Explanation: google chart api를 활용해 바로 chart 그리기 가능
End of explanation
<END_TASK>