Skip to content

Instantly share code, notes, and snippets.

@ernestoongaro
Last active July 30, 2020 13:57
Show Gist options
  • Save ernestoongaro/41430b056fae9bf60d36e4c07d57e247 to your computer and use it in GitHub Desktop.
Save ernestoongaro/41430b056fae9bf60d36e4c07d57e247 to your computer and use it in GitHub Desktop.
### This should only run if the session facts table is empty
view: session_facts_full_load {
derived_table: {
sql_trigger_value: select case when count(distinct partition_date) < 1 then "full re-build" else "daily incremental load" end from ${session_facts_pdt.SQL_TABLE_NAME} ;;
explore_source: ga_sessions {
column: id {}
column: partition_raw {}
column: keyword_count { field: trafficSource.keyword_count }
column: add_to__cart { field: hits_eventInfo.add_to__cart }
filters: {
field: ga_sessions.partition_date
value: "" #no filter on partition date, build it all!
}
}
}
}
### This should every day
view: session_facts_yesterday {
derived_table: {
sql_trigger_value: select CURRENT_DATE() ;; #every day build yesterday's table
explore_source: ga_sessions {
column: id {}
column: partition_raw {}
column: keyword_count { field: trafficSource.keyword_count }
column: add_to__cart { field: hits_eventInfo.add_to__cart }
filters: {
field: ga_sessions.partition_date
value: "1 day ago"
}
}
}
}
###
view: session_facts_pdt {
derived_table: {
publish_as_db_view: yes # make it stable!
sql_trigger_value: select max(partition_date) from ${session_facts_yesterday.SQL_TABLE_NAME} ;;
create_process: {
sql_step: CREATE TABLE IF NOT EXISTS ${SQL_TABLE_NAME} PARTITION BY DATE(partition_raw) as select * from ${session_facts_yesterday.SQL_TABLE_NAME} WHERE 1=2 ;;
### to do - skip the next step if we're doing incremental load
#sql_step: INSERT INTO ${SQL_TABLE_NAME} SELECT * FROM ${session_facts_full_load.SQL_TABLE_NAME} ;;
sql_step: INSERT INTO ${SQL_TABLE_NAME}
SELECT yesterday.* FROM ${session_facts_yesterday.SQL_TABLE_NAME} yesterday
LEFT JOIN ${SQL_TABLE_NAME} full_table ON yesterday.id = full_table.id
WHERE full_table.id IS NULL;; #only values not matched by join
}
}
dimension: id {
label: "Session ID"
}
dimension: keyword_count {
label: "Session: Traffic Source Keyword Count"
type: number
}
dimension: add_to__cart {
label: "Session: Hits: Events Info Add to Cart"
type: number
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment