SQL学習が捗るBig Queryのサンプルデータセット

SQLを書ける人が増加中

最近の弊社木田の口癖が「SQL」なのですが、ふと周りを見渡すと私以上にSQLを書ける人ばかりでした。社内の関わりのあるメンバー然り、お客様然り、です。

以前木田がTwitterでとったアンケートによると、「SQLは必須スキル」と思っている人は2割足らずとのことでしたが、私の周りで必須と思っている人はほぼ100％。。。気づけば自分が一番理解が足りないのではないか？という課題感から、改めてSQLのいろはを学び直すべく、木田の本「集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析」を手に取りました。

この記事では、この本で学習する方をサポートすべく、5章以降のサンプルデータをご提供します。

集中演習 SQL入門 （木田和廣 著）

この本でいうSQLのスキルは「自分でDBから好きなデータを抽出できるようになる」ことです。ですから、頭を使い、手を動かして、自分の書いたSQL文が実際にどのようなデータを抽出してくるのかやってみないと、身についた感が非常に薄くなってしまいます。

この「集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析」（以下、「SQL本」と省略）では、タイトル通りGoogle BigQuery（以下、GBQと略）を利用するわけですが、GBQはGoogleアカウントさえあれば誰でもアクセス可能なSQL実行環境です。

更にこのSQL本にはサンプルデータがあり、これにSQLを実行した際の結果も載っているため、「手を動かす学習」に非常に適したものになっています…少なくとも第4章までは。

SQL本の課題：第5章の壁

この本を手にして実際にSQLを書いてみた人はお気付きかと思いますが、この本には「第5章の壁」として、サンプルデータが途中から無くなるという壁が待ち受けています。

第4章までは手とり足取り、「CSVファイルはここからDLしてください」「GBQにデータを入れるときはこういう注意点があります」「エラーが起きたらこのように対処してください」…と至れり尽くせりなのですが、第5章以降は「ここまで教えたらこの先は自分でできるでしょう？テーブルもそんなに多くないし」とでも言いたげです。

ライオンが子供を崖から突き落とすとはこのことか…。
「自力で這い上がれ！」という木田の声が幻聴となって聞こえてきました。
（※後に木田に確認したところ、「提供したかったけど、退っ引きならぬ事情により断念した」とのことでした。）

私がサンプルデータをご提供します

しかし木田の弟子としては転んでもただでは這い上がれません。学習でお困りの皆さんに、私がサンプルデータをご提供します！

同じく第5章の壁に挑む皆さんにも届く形でサンプルデータを提供するにはどうするのがベストなのか…ふとGBQの画面右上を見ると「Cloud Shellをアクティブにする」という文字が。このCloud Shellからコマンドラインツールを使えば、プログラムでデータを挿入することができます。（GBQにデータを入れるスクリプトは社内に転がっていますから、第5章以降にある大量の「small_xxx」のテーブルをスクリプトで作成するのは造作もありません。）

ということで以下この記事では、Cloud Shell ターミナルを使って第5章以降のサンプルデータをコピペだけで使えるようにするスクリプトとその使い方を紹介します。

手順は以下のとおりです。

• 手順１．使用するスクリプトをコピーする
• 手順２．Cloud Shell Terminalを開いて、スクリプトをペーストする

手順１．使用するスクリプトをコピーする

まずはスクリプト（この記事の最下部に記載）をコピーします。スクリプトの内容はスクリプト中のコメントにもありますが、大きく分けて2つのブロックがあります。

• テーブルを作成し、スキーマを定義する（bq mkコマンド）
• テーブルにデータを挿入する（bq insertコマンド）

既にある程度自分でテーブルを作成した、という方は、テーブル名を検索の上、

1. テーブルを作成する行を1行削除する
2. テーブルにデータを挿入する行を、テーブルの行数分削除する

という処理をした上でコピーしてください。

手順２．Cloud Shell Terminalを開いて、スクリプトをペーストする

次にGBQのコンソールでCloud Shell Terminalを開きます。sql-bookのプロジェクトを選択した状態で、画面右上にある「Cloud Shellをアクティブにする」ボタンをクリックして、コンソールを開きます（画面下部）。

このとき開かれたコマンドラインには、英文で下記のように注意書きがあります。

ポイントとしては、ご自身がSQL本の学習に利用しているプロジェクトが選択されているか、確認してください。プロジェクトが異なる場合は、Cloud Shellターミナルのタブから、（「+」ボタンの隣の「▼」ボタン）プロジェクトを指定して新規タブを開くと良いでしょう。

コンソールにコマンドをコピー＆ペーストすると、実行の承認が求められますので、これを承諾すると各行が実行されていきます。（全行の実行完了まで2分少々かかるようです）

注意点：実行する前に第2章までは演習を終えてください

SQL本の第2章まで演習している方は、GBQのコンソールを触ったことがあると思いますので、上の画面までたどり着くのは造作もないことだと思います。またここまで演習している方は、プロジェクトとデータセット「sample」ができている状態だと思います。

このページで紹介するスクリプトは、演習用プロジェクトにあるデータセット「sample」の配下に、第5章以降で登場する「small_xxx」のようなテーブルを作成するスクリプトです。つまり、データセット「sample」が作成されていることが前提となっています。

よって、少なくとも第2章までは演習を終えている状態でなければエラーが出るものになっていますので、まずは第2章までたどり着きましょう。

スクリプト

# CHAPTER 5-2 販売・商品マスタ・販売実績・販売目標テーブルの作成
bq mk sample.small_sales_1 product_id:STRING,qty:INTEGER
bq mk sample.small_master product_id:STRING,product_name:STRING
bq mk sample.small_sales_2 year_month:DATE,product_category:STRING,sales:INTEGER
bq mk sample.small_target month:DATE,category:STRING,target:INTEGER

# CHAPTER 5-3 販売・ショップマスタ・商品マスタ・地域マスタテーブルの作成
bq mk sample.small_sales_3 product_id:INTEGER,shop_id:INTEGER,sales:INTEGER
bq mk sample.small_s_master shop_id:INTEGER,shop_area_id:INTEGER,shop_name:STRING
bq mk sample.small_p_master product_id:INTEGER,product_name:STRING
bq mk sample.small_a_master shop_area_id:INTEGER,area_name:STRING

# CHAPTER 5-4 販売テーブル・LTV・顧客・注文テーブルの作成
bq mk sample.small_year year:STRING,qty:INTEGER
bq mk sample.small_ltv user_id:INTEGER,ltv:INTEGER
bq mk sample.small_customers user_id:INTEGER,registration_year:STRING,first_purchase_year:STRING
bq mk sample.small_order order_id:INTEGER,product_name:STRING

# CHAPTER 5-5 small_jan,feb,marのテーブルを作成
bq mk sample.small_jan date:DATE,product_name:STRING,qty:INTEGER
bq mk sample.small_feb date:DATE,product_name:STRING,qty:INTEGER
bq mk sample.small_mar date:DATE,product_name:STRING,qty:INTEGER

# CHAPTER 6-1 small_wageのテーブルを作成
bq mk sample.small_wage pref:STRING,year:STRING,min_wage:INTEGER
bq mk sample.small_wage_reiwa1 pref_id:INTEGER,pref:STRING,min_wage:INTEGER

# CHAPTER 6-2 small_revenue,small_ad,small_order_date,small_item,small_workdate,small_calのテーブルを作成
bq mk sample.small_revenue order_id:INTEGER,fashion_revenue:INTEGER,zakka_revenue:INTEGER,food_revenue:INTEGER
bq mk sample.small_ad ad_id:STRING,phase:STRING,bounce_rate:FLOAT
bq mk sample.small_order_date user_id:STRING,order_id:INTEGER,order_date:DATE,item_cat:STRING,revenue:INTEGER
bq mk sample.small_item order_id:INTEGER,item:STRING,revenue:INTEGER
bq mk sample.small_workdate room:INTEGER,start_date:DATE,end_date:DATE
bq mk sample.small_cal date:DATE,day_of_week:STRING

# CHAPTER 7-4 small_ordertime,small_orderdateのテーブルを作成
bq mk sample.small_ordertime order_time:DATETIME,qty:INTEGER
bq mk sample.small_orderdate order_date:DATE,qty:INTEGER

# CHAPTER 7-5 small_populationのテーブルを作成
bq mk sample.small_population pref:STRING,population:INTEGER,min_wage:INTEGER

#================================================================================================================================================

# CHAPTER 5-2 販売・商品マスタ・販売実績・販売目標テーブルのデータを挿入
echo '{"product_id":"D", "qty":3}' | bq insert sample.small_sales_1
echo '{"product_id":"B", "qty":4}' | bq insert sample.small_sales_1
echo '{"product_id":"C", "qty":2}' | bq insert sample.small_sales_1

echo '{"product_id":"A", "product_name":"アジ"}' | bq insert sample.small_master
echo '{"product_id":"B", "product_name":"サバ"}' | bq insert sample.small_master
echo '{"product_id":"C", "product_name":"タコ"}' | bq insert sample.small_master

echo '{"year_month":"2019-01-01","product_category":"Men'\''s", "sales":134}' | bq insert sample.small_sales_2
echo '{"year_month":"2019-01-01","product_category":"Lady'\''s", "sales":122}' | bq insert sample.small_sales_2
echo '{"year_month":"2019-02-01","product_category":"Men'\''s", "sales":155}' | bq insert sample.small_sales_2
echo '{"year_month":"2019-02-01","product_category":"Lady'\''s", "sales":116}' | bq insert sample.small_sales_2
echo '{"year_month":"2019-03-01","product_category":"Men'\''s", "sales":152}' | bq insert sample.small_sales_2
echo '{"year_month":"2019-03-01","product_category":"Lady'\''s", "sales":139}' | bq insert sample.small_sales_2

echo '{"month":"2019-01-01","category":"Men'\''s", "target":130}' | bq insert sample.small_target
echo '{"month":"2019-01-01","category":"Lady'\''s", "target":120}' | bq insert sample.small_target
echo '{"month":"2019-02-01","category":"Men'\''s", "target":160}' | bq insert sample.small_target
echo '{"month":"2019-02-01","category":"Lady'\''s", "target":120}' | bq insert sample.small_target
echo '{"month":"2019-03-01","category":"Men'\''s", "target":160}' | bq insert sample.small_target
echo '{"month":"2019-03-01","category":"Lady'\''s", "target":130}' | bq insert sample.small_target

# CHAPTER 5-3 販売・ショップマスタ・商品マスタ・地域マスタテーブルのデータを挿入
echo '{"product_id":1, "shop_id":1, "sales":134}' | bq insert sample.small_sales_3
echo '{"product_id":1, "shop_id":2, "sales":122}' | bq insert sample.small_sales_3
echo '{"product_id":1, "shop_id":3, "sales":155}' | bq insert sample.small_sales_3
echo '{"product_id":2, "shop_id":1, "sales":116}' | bq insert sample.small_sales_3
echo '{"product_id":2, "shop_id":2, "sales":152}' | bq insert sample.small_sales_3
echo '{"product_id":2, "shop_id":3, "sales":139}' | bq insert sample.small_sales_3

echo '{"shop_id":1, "shop_area_id":1, "shop_name":"築地"}' | bq insert sample.small_s_master
echo '{"shop_id":2, "shop_area_id":1, "shop_name":"銀座"}' | bq insert sample.small_s_master
echo '{"shop_id":3, "shop_area_id":2, "shop_name":"豊洲"}' | bq insert sample.small_s_master

echo '{"product_id":1, "product_name":"アジ"}' | bq insert sample.small_p_master
echo '{"product_id":2, "product_name":"タコ"}' | bq insert sample.small_p_master

echo '{"shop_area_id":1, "area_name":"中央区"}' | bq insert sample.small_a_master
echo '{"shop_area_id":2, "area_name":"江東区"}' | bq insert sample.small_a_master

# CHAPTER 5-4 販売・LTV・顧客・注文テーブルのデータを挿入
echo '{"year":"2017", "qty":272}' | bq insert sample.small_year
echo '{"year":"2018", "qty":309}' | bq insert sample.small_year
echo '{"year":"2019", "qty":310}' | bq insert sample.small_year

echo '{"user_id":1, "ltv":3400}' | bq insert sample.small_ltv
echo '{"user_id":2, "ltv":8200}' | bq insert sample.small_ltv
echo '{"user_id":3, "ltv":1500}' | bq insert sample.small_ltv
echo '{"user_id":4, "ltv":1600}' | bq insert sample.small_ltv
echo '{"user_id":5, "ltv":5100}' | bq insert sample.small_ltv
echo '{"user_id":6, "ltv":3900}' | bq insert sample.small_ltv

echo '{"user_id":1, "registration_year":null, "first_purchase_year":"2017"}' | bq insert sample.small_customers
echo '{"user_id":2, "registration_year":"2017", "first_purchase_year":"2017"}' | bq insert sample.small_customers
echo '{"user_id":3, "registration_year":null, "first_purchase_year":"2018"}' | bq insert sample.small_customers
echo '{"user_id":4, "registration_year":"2017", "first_purchase_year":"2018"}' | bq insert sample.small_customers
echo '{"user_id":5, "registration_year":"2018", "first_purchase_year":"2019"}' | bq insert sample.small_customers
echo '{"user_id":6, "registration_year":"2019", "first_purchase_year":"2019"}' | bq insert sample.small_customers

echo '{"order_id":1, "product_name":"アジ"}' | bq insert sample.small_order
echo '{"order_id":1, "product_name":"サバ"}' | bq insert sample.small_order
echo '{"order_id":1, "product_name":"タコ"}' | bq insert sample.small_order
echo '{"order_id":2, "product_name":"キス"}' | bq insert sample.small_order
echo '{"order_id":2, "product_name":"タコ"}' | bq insert sample.small_order
echo '{"order_id":3, "product_name":"アジ"}' | bq insert sample.small_order
echo '{"order_id":3, "product_name":"タコ"}' | bq insert sample.small_order

# CHAPTER 5-5 small_jan,feb,marのデータを挿入
echo '{"date":"2020-01-10", "product_name":"アジ", "qty":3}' | bq insert sample.small_jan
echo '{"date":"2020-01-11", "product_name":"タコ", "qty":1}' | bq insert sample.small_jan
echo '{"date":"2020-01-15", "product_name":"サバ", "qty":3}' | bq insert sample.small_jan
echo '{"date":"2020-01-18", "product_name":"キス", "qty":2}' | bq insert sample.small_jan
echo '{"date":"2020-01-20", "product_name":"タイ", "qty":1}' | bq insert sample.small_jan

echo '{"date":"2020-02-09", "product_name":"アジ", "qty":3}' | bq insert sample.small_feb
echo '{"date":"2020-02-12", "product_name":"ブリ", "qty":3}' | bq insert sample.small_feb
echo '{"date":"2020-02-15", "product_name":"キス", "qty":3}' | bq insert sample.small_feb
echo '{"date":"2020-02-19", "product_name":"タイ", "qty":2}' | bq insert sample.small_feb
echo '{"date":"2020-02-28", "product_name":"イカ", "qty":1}' | bq insert sample.small_feb

echo '{"date":"2020-03-01", "product_name":"アジ", "qty":1}' | bq insert sample.small_mar
echo '{"date":"2020-03-03", "product_name":"エビ", "qty":3}' | bq insert sample.small_mar
echo '{"date":"2020-03-16", "product_name":"サバ", "qty":2}' | bq insert sample.small_mar
echo '{"date":"2020-03-19", "product_name":"タイ", "qty":2}' | bq insert sample.small_mar
echo '{"date":"2020-03-22", "product_name":"タコ", "qty":1}' | bq insert sample.small_mar

# CHAPTER 6-1 small_wage,small_wage_reiwa1のデータを挿入
echo '{"pref":"東京", "year":"H29", "min_wage":958}' | bq insert sample.small_wage
echo '{"pref":"東京", "year":"H30", "min_wage":985}' | bq insert sample.small_wage
echo '{"pref":"東京", "year":"R1", "min_wage":1013}' | bq insert sample.small_wage
echo '{"pref":"北海道", "year":"H29", "min_wage":810}' | bq insert sample.small_wage
echo '{"pref":"北海道", "year":"H30", "min_wage":835}' | bq insert sample.small_wage
echo '{"pref":"北海道", "year":"R1", "min_wage":861}' | bq insert sample.small_wage
echo '{"pref":"千葉", "year":"H29", "min_wage":868}' | bq insert sample.small_wage
echo '{"pref":"千葉", "year":"H30", "min_wage":895}' | bq insert sample.small_wage
echo '{"pref":"千葉", "year":"R1", "min_wage":923}' | bq insert sample.small_wage

echo '{"pref_id":1,"pref":"北海道","min_wage":861}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":2,"pref":"青森","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":3,"pref":"岩手","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":4,"pref":"宮城","min_wage":824}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":5,"pref":"秋田","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":6,"pref":"山形","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":7,"pref":"福島","min_wage":798}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":8,"pref":"茨城","min_wage":849}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":9,"pref":"栃木","min_wage":853}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":10,"pref":"群馬","min_wage":835}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":11,"pref":"埼玉","min_wage":926}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":12,"pref":"千葉","min_wage":923}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":13,"pref":"東京","min_wage":1013}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":14,"pref":"神奈川","min_wage":1011}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":15,"pref":"新潟","min_wage":830}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":16,"pref":"富山","min_wage":848}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":17,"pref":"石川","min_wage":832}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":18,"pref":"福井","min_wage":829}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":19,"pref":"山梨","min_wage":837}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":20,"pref":"長野","min_wage":848}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":21,"pref":"岐阜","min_wage":851}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":22,"pref":"静岡","min_wage":885}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":23,"pref":"愛知","min_wage":926}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":24,"pref":"三重","min_wage":873}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":25,"pref":"滋賀","min_wage":866}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":26,"pref":"京都","min_wage":909}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":27,"pref":"大阪","min_wage":964}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":28,"pref":"兵庫","min_wage":899}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":29,"pref":"奈良","min_wage":837}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":30,"pref":"和歌山","min_wage":830}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":31,"pref":"鳥取","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":32,"pref":"島根","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":33,"pref":"岡山","min_wage":833}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":34,"pref":"広島","min_wage":871}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":35,"pref":"山口","min_wage":829}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":36,"pref":"徳島","min_wage":793}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":37,"pref":"香川","min_wage":818}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":38,"pref":"愛媛","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":39,"pref":"高知","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":40,"pref":"福岡","min_wage":841}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":41,"pref":"佐賀","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":42,"pref":"長崎","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":43,"pref":"熊本","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":44,"pref":"大分","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":45,"pref":"宮崎","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":46,"pref":"鹿児島","min_wage":790}' | bq insert sample.small_wage_reiwa1
echo '{"pref_id":47,"pref":"沖縄","min_wage":790}' | bq insert sample.small_wage_reiwa1

# CHAPTER 6-2 small_revenue,small_ad,small_order_date,small_item,small_workdate,small_calのデータを挿入
echo '{"order_id":123,"fashion_revenue":18600,"zakka_revenue":null, "food_revenue":5800}' | bq insert sample.small_revenue
echo '{"order_id":124,"fashion_revenue":null,"zakka_revenue":2400, "food_revenue":8800}' | bq insert sample.small_revenue
echo '{"order_id":125,"fashion_revenue":6900,"zakka_revenue":2900, "food_revenue":11200}' | bq insert sample.small_revenue
echo '{"order_id":126,"fashion_revenue":4200,"zakka_revenue":3800, "food_revenue":4500}' | bq insert sample.small_revenue
echo '{"order_id":127,"fashion_revenue":null,"zakka_revenue":9800, "food_revenue":null}' | bq insert sample.small_revenue

echo '{"ad_id":"A","phase":"before","bounce_rate":0.68}' | bq insert sample.small_ad
echo '{"ad_id":"A","phase":"after","bounce_rate":0.62}' | bq insert sample.small_ad
echo '{"ad_id":"B","phase":"before","bounce_rate":0.52}' | bq insert sample.small_ad
echo '{"ad_id":"B","phase":"after","bounce_rate":0.55}' | bq insert sample.small_ad

echo '{"user_id":"ABC","order_id":123,"order_date":"2020-03-05","item_cat":"ファッション", "revenue":10000}' | bq insert sample.small_order_date
echo '{"user_id":"ABC","order_id":124,"order_date":"2020-04-10","item_cat":"ファッション", "revenue":12000}' | bq insert sample.small_order_date
echo '{"user_id":"ABC","order_id":124,"order_date":"2020-04-10","item_cat":"雑貨", "revenue":3900}' | bq insert sample.small_order_date
echo '{"user_id":"ABC","order_id":125,"order_date":"2020-05-12","item_cat":"グルメ", "revenue":5800}' | bq insert sample.small_order_date
echo '{"user_id":"STU","order_id":126,"order_date":"2020-05-13","item_cat":"グルメ", "revenue":8600}' | bq insert sample.small_order_date
echo '{"user_id":"STU","order_id":127,"order_date":"2020-05-27","item_cat":"ファッション", "revenue":3900}' | bq insert sample.small_order_date
echo '{"user_id":"STU","order_id":127,"order_date":"2020-05-27","item_cat":"雑貨", "revenue":6600}' | bq insert sample.small_order_date
echo '{"user_id":"XYZ","order_id":128,"order_date":"2020-06-01","item_cat":"ファッション", "revenue":2900}' | bq insert sample.small_order_date
echo '{"user_id":"XYZ","order_id":128,"order_date":"2020-06-01","item_cat":"ファッション", "revenue":6900}' | bq insert sample.small_order_date
echo '{"user_id":"XYZ","order_id":129,"order_date":"2020-06-19","item_cat":"雑貨", "revenue":38000}' | bq insert sample.small_order_date

echo '{"order_id":123,"item":"A","revenue":5800}' | bq insert sample.small_item
echo '{"order_id":123,"item":"B","revenue":5800}' | bq insert sample.small_item
echo '{"order_id":124,"item":"A","revenue":7200}' | bq insert sample.small_item
echo '{"order_id":124,"item":"D","revenue":7200}' | bq insert sample.small_item
echo '{"order_id":124,"item":"E","revenue":7200}' | bq insert sample.small_item
echo '{"order_id":125,"item":"C","revenue":4800}' | bq insert sample.small_item
echo '{"order_id":125,"item":"E","revenue":4800}' | bq insert sample.small_item
echo '{"order_id":126,"item":"B","revenue":3200}' | bq insert sample.small_item
echo '{"order_id":126,"item":"C","revenue":3200}' | bq insert sample.small_item

echo '{"room":101, "start_date":"2020-06-01", "end_date":"2020-06-05"}' | bq insert sample.small_workdate
echo '{"room":102, "start_date":"2020-06-08", "end_date":"2020-06-11"}' | bq insert sample.small_workdate
echo '{"room":103, "start_date":"2020-06-12", "end_date":"2020-06-16"}' | bq insert sample.small_workdate
echo '{"room":104, "start_date":"2020-06-19", "end_date":"2020-06-23"}' | bq insert sample.small_workdate
echo '{"room":105, "start_date":"2020-06-24", "end_date":"2020-06-30"}' | bq insert sample.small_workdate

echo '{"date":"2020-06-01","day_of_week":"Mon"}' | bq insert sample.small_cal
echo '{"date":"2020-06-02","day_of_week":"Tue"}' | bq insert sample.small_cal
echo '{"date":"2020-06-03","day_of_week":"Wed"}' | bq insert sample.small_cal
echo '{"date":"2020-06-04","day_of_week":"Thu"}' | bq insert sample.small_cal
echo '{"date":"2020-06-05","day_of_week":"Fri"}' | bq insert sample.small_cal
echo '{"date":"2020-06-06","day_of_week":"Sat"}' | bq insert sample.small_cal
echo '{"date":"2020-06-07","day_of_week":"Sun"}' | bq insert sample.small_cal
echo '{"date":"2020-06-08","day_of_week":"Mon"}' | bq insert sample.small_cal
echo '{"date":"2020-06-09","day_of_week":"Tue"}' | bq insert sample.small_cal
echo '{"date":"2020-06-10","day_of_week":"Wed"}' | bq insert sample.small_cal
echo '{"date":"2020-06-11","day_of_week":"Thu"}' | bq insert sample.small_cal
echo '{"date":"2020-06-12","day_of_week":"Fri"}' | bq insert sample.small_cal
echo '{"date":"2020-06-13","day_of_week":"Sat"}' | bq insert sample.small_cal
echo '{"date":"2020-06-14","day_of_week":"Sun"}' | bq insert sample.small_cal
echo '{"date":"2020-06-15","day_of_week":"Mon"}' | bq insert sample.small_cal
echo '{"date":"2020-06-16","day_of_week":"Tue"}' | bq insert sample.small_cal
echo '{"date":"2020-06-17","day_of_week":"Wed"}' | bq insert sample.small_cal
echo '{"date":"2020-06-18","day_of_week":"Thu"}' | bq insert sample.small_cal
echo '{"date":"2020-06-19","day_of_week":"Fri"}' | bq insert sample.small_cal
echo '{"date":"2020-06-20","day_of_week":"Sat"}' | bq insert sample.small_cal
echo '{"date":"2020-06-21","day_of_week":"Sun"}' | bq insert sample.small_cal
echo '{"date":"2020-06-22","day_of_week":"Mon"}' | bq insert sample.small_cal
echo '{"date":"2020-06-23","day_of_week":"Tue"}' | bq insert sample.small_cal
echo '{"date":"2020-06-24","day_of_week":"Wed"}' | bq insert sample.small_cal
echo '{"date":"2020-06-25","day_of_week":"Thu"}' | bq insert sample.small_cal
echo '{"date":"2020-06-26","day_of_week":"Fri"}' | bq insert sample.small_cal
echo '{"date":"2020-06-27","day_of_week":"Sat"}' | bq insert sample.small_cal
echo '{"date":"2020-06-28","day_of_week":"Sun"}' | bq insert sample.small_cal
echo '{"date":"2020-06-29","day_of_week":"Mon"}' | bq insert sample.small_cal
echo '{"date":"2020-06-30","day_of_week":"Tue"}' | bq insert sample.small_cal

# CHAPTER 7-4 small_ordertime,small_orderdateのデータを挿入
echo '{"order_time":"2020-01-11 20:18:12", "qty":6}' | bq insert sample.small_ordertime
echo '{"order_time":"2020-01-18 05:11:10", "qty":2}' | bq insert sample.small_ordertime
echo '{"order_time":"2020-01-25 17:07:12", "qty":4}' | bq insert sample.small_ordertime
echo '{"order_time":"2020-02-02 09:00:05", "qty":9}' | bq insert sample.small_ordertime
echo '{"order_time":"2020-02-25 10:47:59", "qty":5}' | bq insert sample.small_ordertime
echo '{"order_time":"2020-02-28 22:48:06", "qty":6}' | bq insert sample.small_ordertime

echo '{"order_date":"2020-01-01", "qty":6}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-01-02", "qty":2}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-01-03", "qty":4}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-01-04", "qty":6}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-01-05", "qty":2}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-01-06", "qty":8}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-01-07", "qty":6}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-02-01", "qty":5}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-02-02", "qty":9}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-02-03", "qty":5}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-02-04", "qty":6}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-02-05", "qty":2}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-02-06", "qty":4}' | bq insert sample.small_orderdate
echo '{"order_date":"2020-02-07", "qty":6}' | bq insert sample.small_orderdate

# CHAPTER 7-5 small_populationのデータを挿入
echo '{"pref":"北海道","population":5320,"min_wage":810}' | bq insert sample.small_population
echo '{"pref":"青森","population":1278,"min_wage":738}' | bq insert sample.small_population
echo '{"pref":"岩手","population":1255,"min_wage":738}' | bq insert sample.small_population
echo '{"pref":"宮城","population":2323,"min_wage":772}' | bq insert sample.small_population
echo '{"pref":"秋田","population":996,"min_wage":738}' | bq insert sample.small_population
echo '{"pref":"山形","population":1102,"min_wage":739}' | bq insert sample.small_population
echo '{"pref":"福島","population":1882,"min_wage":748}' | bq insert sample.small_population
echo '{"pref":"茨城","population":2892,"min_wage":796}' | bq insert sample.small_population
echo '{"pref":"栃木","population":1957,"min_wage":800}' | bq insert sample.small_population
echo '{"pref":"群馬","population":1960,"min_wage":783}' | bq insert sample.small_population
echo '{"pref":"埼玉","population":7310,"min_wage":871}' | bq insert sample.small_population
echo '{"pref":"千葉","population":6246,"min_wage":868}' | bq insert sample.small_population
echo '{"pref":"東京","population":13724,"min_wage":958}' | bq insert sample.small_population
echo '{"pref":"神奈川","population":9159,"min_wage":956}' | bq insert sample.small_population
echo '{"pref":"新潟","population":2267,"min_wage":778}' | bq insert sample.small_population
echo '{"pref":"富山","population":1056,"min_wage":795}' | bq insert sample.small_population
echo '{"pref":"石川","population":1147,"min_wage":781}' | bq insert sample.small_population
echo '{"pref":"福井","population":779,"min_wage":778}' | bq insert sample.small_population
echo '{"pref":"山梨","population":823,"min_wage":784}' | bq insert sample.small_population
echo '{"pref":"長野","population":2076,"min_wage":795}' | bq insert sample.small_population
echo '{"pref":"岐阜","population":2008,"min_wage":800}' | bq insert sample.small_population
echo '{"pref":"静岡","population":3675,"min_wage":832}' | bq insert sample.small_population
echo '{"pref":"愛知","population":7525,"min_wage":871}' | bq insert sample.small_population
echo '{"pref":"三重","population":1800,"min_wage":820}' | bq insert sample.small_population
echo '{"pref":"滋賀","population":1413,"min_wage":813}' | bq insert sample.small_population
echo '{"pref":"京都","population":2599,"min_wage":856}' | bq insert sample.small_population
echo '{"pref":"大阪","population":8823,"min_wage":909}' | bq insert sample.small_population
echo '{"pref":"兵庫","population":5503,"min_wage":844}' | bq insert sample.small_population
echo '{"pref":"奈良","population":1348,"min_wage":786}' | bq insert sample.small_population
echo '{"pref":"和歌山","population":945,"min_wage":777}' | bq insert sample.small_population
echo '{"pref":"鳥取","population":565,"min_wage":738}' | bq insert sample.small_population
echo '{"pref":"島根","population":685,"min_wage":740}' | bq insert sample.small_population
echo '{"pref":"岡山","population":1907,"min_wage":781}' | bq insert sample.small_population
echo '{"pref":"広島","population":2829,"min_wage":818}' | bq insert sample.small_population
echo '{"pref":"山口","population":1383,"min_wage":777}' | bq insert sample.small_population
echo '{"pref":"徳島","population":743,"min_wage":740}' | bq insert sample.small_population
echo '{"pref":"香川","population":967,"min_wage":766}' | bq insert sample.small_population
echo '{"pref":"愛媛","population":1364,"min_wage":739}' | bq insert sample.small_population
echo '{"pref":"高知","population":714,"min_wage":737}' | bq insert sample.small_population
echo '{"pref":"福岡","population":5107,"min_wage":789}' | bq insert sample.small_population
echo '{"pref":"佐賀","population":824,"min_wage":737}' | bq insert sample.small_population
echo '{"pref":"長崎","population":1354,"min_wage":737}' | bq insert sample.small_population
echo '{"pref":"熊本","population":1765,"min_wage":737}' | bq insert sample.small_population
echo '{"pref":"大分","population":1152,"min_wage":737}' | bq insert sample.small_population
echo '{"pref":"宮崎","population":1089,"min_wage":737}' | bq insert sample.small_population
echo '{"pref":"鹿児島","population":1626,"min_wage":737}' | bq insert sample.small_population
echo '{"pref":"沖縄","population":1443,"min_wage":737}' | bq insert sample.small_population