%bq --help

"""
Explanation: BigQuery Commands
Google Cloud Datalab provides a first class experience for working with Google BigQuery for both adhoc, exploratory work as well as pipeline development. In addition to authoring and executing SQL queries, you'll likely want to inspect Datasets, Tables, and their Schema, and also sample data.
This notebook introduces various BigQuery commands that Datalab brings into the notebook environment.
You've already seen a BigQuery command in the Hello BigQuery notebook, they are invoked using %%bq.
Commands, literally
You can think of %%bq as your command line interface to BigQuery. These commands take arguments, including --help (or -h), in the same manner as other shell commands. Lets start by looking at help.
End of explanation
"""


%bq tables --help

"""
Explanation: You can get help on any of the individual commands as follows:
End of explanation
"""


%bq datasets list --project cloud-datalab-samples

%bq tables list --dataset cloud-datalab-samples.httplogs

"""
Explanation: Listing BigQuery Resources
The first thing you might decide to do is list datasets and tables contained within a specified project or the default project you're running this Datalab environment in.
Note that you will be able to list and use any BigQuery dataset that has been shared with the project (its service account) that you are running Datalab in.
End of explanation
"""


%bq tables describe --name cloud-datalab-samples.httplogs.logs_20140615

"""
Explanation: Exploring Data
Once you've found a table or set of tables to work with, its simple to retrieve its schema or some sampling of rows to understand the data you're working with.
Schema
End of explanation
"""


%bq sample --help

"""
Explanation: Sampling Data
End of explanation
"""


%bq sample --table cloud-datalab-samples.httplogs.logs_20140615 --count 5

"""
Explanation: As you can see, Datalab allows you to sample a specified count of rows using various sampling strategies, including random sampling or hashing. Let's try retrieving a couple of samples.
End of explanation
"""


%bq sample --table cloud-datalab-samples.httplogs.logs_20140615 --count 10 --fields timestamp,latency --method hashed --key-field latency

"""
Explanation: You can also specify a list of fields to project.
End of explanation
"""


%%bq query 
SELECT timestamp, latency
FROM `cloud-datalab-samples.httplogs.logs_20140615`
ORDER BY latency DESC
LIMIT 10

"""
Explanation: Querying Data
Of course, since, querying BigQuery data using SQL is the primary scenario, it's always handy to have the BigQuery SQL reference. Datalab makes this easy to access by providing a direct link to the query reference via the Help Links button on the top toolbar.
Lets see some more query examples.
End of explanation
"""


%%bq query
SELECT
  quantiles[SAFE_ORDINAL(50)] AS latency_50th_percentile,
  quantiles[SAFE_ORDINAL(95)] AS latency_95th_percentile,
  quantiles[SAFE_ORDINAL(99)] AS latency_99th_percentile
FROM (
  SELECT APPROX_QUANTILES(latency, 100) AS quantiles
  FROM `cloud-datalab-samples.httplogs.logs_20140615`
)

"""
Explanation: That was a simple query. Now, let's do something a bit more interesting: let's compute the 50th percentile, 95th percentile, and 99th percentile latencies for request processing.
BigQuery makes this effortless with its built-in statistical and analytics functions, such as QUANTILES.
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: Visualizing Data
Beyond tables, it almost always interesting to be able to visualize the data to get a more meaningful view of aggregates, trends and patterns.
Let's write another query, also using QUANTILES. This time, the ROW_NUMBER function will be used to also include a row number in the output data representing the percentile, using the number as the chart's x-axis value. In order to reference this SQL query later in the notebook, it is given a name via the --name/-n argument. This query object can then be passed to a chart by referencing its name. The chart will execute the contained query.
End of explanation
"""


