Database Reference
In-Depth Information
BigQuery features and functionality. These queries build upon each other
to compute word usage analysis for Shakespeare's plays. If you understand
these five queries, you can use the same techniques to answer questions
about your data.
Source Table Introduction
The queries in this section reference the public Shakespeare sample table
(
publicdata:samples.shakespeare
). Although it is not a “big data”
table—it weighs in at only 6.1 MB—it is useful for trying out queries because
it is almost free to query it. You can run more than 170,000 queries against
it and still be under your monthly “free query” quota.
The Shakespeare table contains the breakdown of word usage in
Shakespeare plays and sonnets. The fields are described in
Table 7.4
.
Table 7.4
Shakespeare Table Schema
Field Type Description
word STRING
Word used in a play. If the same word is used in
multiple plays, it will have an entry for each
play it appears in.
word_count INTEGER
Number of times the word appears in the play
corpus STRING
Name of the play, with spaces removed and in
all lowercase. The sonnets get a single entry
(“sonnets”), as do Shakespeare's other writings
(“various”).
corpus_date INTEGER
Year the play was written, or 0 for “sonnets”
and “various” because they were written across
multiple years.
Query #1: Field Projection with Filter
SELECT LOWER(word) AS word, word_count AS frequency,
corpus
FROM [publicdata:samples.shakespeare]
WHERE corpus CONTAINS 'king' AND LENGTH(word) > 5
ORDER BY frequency DESC
LIMIT 10
