Instantly share code, notes, and snippets.

View goodcase1.sql
SELECT * FROM (VALUES (1, 'apple'), (2, 'banana')) as fruit(id, name);
# このクエリでは, 以下のデータセットがクエリ内で生成され使用されています。
# id | name
#-----+--------
# 1 | apple
# 2 | banana
View badcase1'.sql
-- 元のクエリ
col1 < 100 OR col2 is TRUE AND TD_TIME_RANGE(time, '2015-11-01')
-- 上のクエリは AND が優先的に考慮されるため, 以下のクエリと同義になります。
-- OR によって左右に分解されたこのクエリでは, TD_TIME_RANGE があるにも関わらず, (col1 < 100) の判定のために全件スキャンになります!
(col1 < 100) OR (col2 is TRUE AND TD_TIME_RANGE(time, '2015-11-01')) 
-- クエリ全体に TIME_RANGE 制約を利かす場合は明示的に () を使いましょう!
-- 以下のクエリでは TIME_RANGE 制約内のデータセットで (col1 < 100 OR col2 is TRUE) が判定されます。
(col1 < 100 OR col2 is TRUE) AND TD_TIME_RANGE(time, '2015-11-01')
View badcase1.sql
SELECT * FROM table1
WHERE
col1 < 100
OR col2 is TRUE
AND TD_TIME_RANGE(time, '2015-11-01')
View cohort.yml
in:
type: google_analytics
json_key_content: |
{
// from Google API Service Account's Private Key
"type": "service_account",
"project_id": "ga-to-td",
"private_key_id": "18f4c*****",
"private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvgI*****",
...
View digdag1.sh
doryokujin :: ~/mydag » digdag run mydag.dig 1 ↵
2017-01-10 10:38:06 +0900: Digdag v0.9.1
2017-01-10 10:38:08 +0900 [WARN] (main): Using a new session time 2017-01-10T00:00:00+00:00.
2017-01-10 10:38:08 +0900 [INFO] (main): Using session /Users/doryokujin/mydag/.digdag/status/20170110T000000+0000.
2017-01-10 10:38:08 +0900 [INFO] (main): Starting a new session project id=1 workflow name=mydag session_time=2017-01-10T00:00:00+00:00
2017-01-10 10:38:09 +0900 [INFO] (0018@+mydag+setup): echo>: start 2017-01-10T00:00:00+00:00
start 2017-01-10T00:00:00+00:00
2017-01-10 10:38:09 +0900 [INFO] (0018@+mydag+disp_current_date): echo>: 2017-01-10 00:00:00 +00:00
2017-01-10 00:00:00 +00:00
2017-01-10 10:38:09 +0900 [INFO] (0018@+mydag+repeat): for_each>: {order=[first, second, third], animal=[dog, cat]}
View slemma_uniques.sql
SELECT
TD_TIME_FORMAT(time,'yyyy-MM-dd') AS d,
COUNT(1) AS pv,
COUNT(DISTINCT td_client_id) AS uu
FROM
pageviews
GROUP BY
TD_TIME_FORMAT(time,'yyyy-MM-dd')
ORDER BY
d
View td_ec_goods_sales_monthly.csv
month member_id goods_id sub_category category amount sales
2015-02-01 2084822 593901 Musical Instruments Movies and Music and Games 1 2839
2015-02-01 1400295 594055 Baby Clothing and Shoes and Jewelry 1 1000
2015-02-01 2084837 586278 Books Books and Audible 1 2839
2015-02-01 111442 593933 Wedding Registry Home and Garden and Tools 1 2848
2016-04-01 2068068 668145 Car/Vehicle Electronics and GPS Automotive and Industrial 2 2000
2016-04-01 591009 664872 Video Games Movies and Music and Games 1 2839
2015-12-01 1932731 646845 Trade In Your Electronics Electronics and Computers 1 1000
2015-12-01 905489 645071 Power and Hand Tools Home and Garden and Tools 1 2362
2015-12-01 745362 621351 Digital Games Movies and Music and Games 1 3791
View td_js_log_monthly.csv
We can make this file beautiful and searchable if this error is corrected: It looks like row 7 should actually have 8 columns, instead of 3.
month,td_client_id,td_url,td_title,td_referrer,td_browser,td_language,cnt
2016-08-01,831ca1,http://get.treasuredata.com/JP-Download-Corporate-Overview.html,Treasure Data - データ分析をクラウドで、シンプルに。 - Treasure Data,,IE,ja-jp,2
2016-08-01,09240e,https://www.treasuredata.com/jp/partnerships,パートナー企業 - Treasure Data,https://www.treasuredata.com/jp/,IE,ja-jp,1
2016-08-01,a435df,https://www.treasuredata.com/jp/jp_download_corporate_overview_str/,TREASURE DMPの概要 - Treasure Data,https://www.facebook.com,Firefox,ja,1
2016-08-01,58df61,http://get.treasuredata.com/JP-Download-Corporate-Overview.html,Treasure Data - データ分析をクラウドで、シンプルに。 - Treasure Data,http://nt-lsoxap.housefoods.co.jp/mail/MailPreView.do,IE,ja-jp,2
2016-08-01,8f4fd4,https://www.treasuredata.com/jp/press_release/20160407_private_dmp_solution_treasure_dmp,プライベートDMPソリューション”TREASURE(トレジャー) DMP(ディーエムピー)”を 4月より提供開始 - プレスリリース - Treasure Data,https://www.treasuredata.com/jp/about,IE,ja-jp,1
2016-08-01,4edea6,http://get.treasuredata.com/JP-Download-Corporate-Overview.html
View dc_client_id.sql
+---------------------------+------------------+-------------------------+----------------+----------------+
| date_hour:timestamp | page_path:string | client_id:string | pageviews:long | view_id:string |
+---------------------------+------------------+-------------------------+----------------+----------------+
| "2016-08-19 07:00:00 UTC" | "/" | "1108439470.1471592890" | 4 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/" | "1224794560.1471584000" | 1 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/" | "1369494787.1462782000" | 1 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/" | "1389102700.1471592860" | 3 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/" | "1521310483.1469457390" | 1 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/" | "1690037376.1461813950" | 2 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/"
View GA to TD-18f4c65b19b3.json
{
"type": "service_account",
"project_id": "ga-to-td",
"private_key_id": "18f4c65b19b3*****",
"private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvg*****",
"client_email": "treasure-data@ga-to-td.iam.gserviceaccount.com",
"client_id": "11147*****",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://accounts.google.com/o/oauth2/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",