# 梅竹黑客松
## ML on GCP
https://cloud.google.com/blog/products/data-analytics/ml-inference-in-dataflow-pipelines

https://cloud.google.com/dataflow/docs/samples/molecules-walkthrough

## Data Studio
* [新手教學](https://datastudio.google.com/u/0/reporting/0B5FF6JBKbNJxOWItcWo2SVVVeGc/page/KjD)
* [Auto refresh](https://chrome.google.com/webstore/detail/data-studio-auto-refresh/inkgahcdacjcejipadnndepfllmbgoag/related?hl=en)
* 1 MWh = 1000 度
* 左上 bending, 右上 robot_welter, 左下 laser_cutting, 右下 laser_shaping
工業用電 1 度約 2.5 元
### Presentation tips
* 把 publish rate 調高，讓 data studio 更新的頻率高一點
### Moving Average
* 在圖中加入參考線，當成是 threshold

WITH sub AS (
SELECT time_stamp, power_consumption, AVG(power_consumption)
  OVER(
    ORDER BY time_stamp
    ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  ) AS moving_average
FROM `duckbubi.machine_power_consumption.realtime`
ORDER BY time_stamp DESC
LIMIT 100 )
SELECT * FROM sub
ORDER BY time_stamp ASC;

### Data Aggregation
* 依照小時，將同一個小時的 data aggregate 起來
* 減少 data 的量，就可以呈現歷史紀錄

SELECT DATETIME_TRUNC(time_stamp, HOUR) AS hour , SUM(power_consumption) AS power_consumption
 FROM `duckbubi.machine_power_consumption.bending_raw`
 GROUP BY hour;

### Threshold

SELECT power_consumption Median FROM
(SELECT a1.time_stamp, a1.power_consumption, COUNT(a1.power_consumption) Rank
FROM `duckbubi.machine_power_consumption.realtime` a1, `duckbubi.machine_power_consumption.realtime` a2
WHERE a1.power_consumption < a2.power_consumption OR (a1.power_consumption=a2.power_consumption AND a1.time_stamp <= a2.time_stamp)
GROUP BY a1.time_stamp, a1.power_consumption
ORDER BY a1.power_consumption desc) a3
WHERE Rank = (SELECT DIV( (COUNT(*)+1)*1 ,4) FROM `duckbubi.machine_power_consumption.realtime`);

### Total Factory Power Consumption by hour

SELECT DATETIME_TRUNC(`duckbubi.machine_power_consumption.bending_raw`.time_stamp, HOUR) AS hour,
        SUM(`duckbubi.machine_power_consumption.bending_raw`.power_consumption) AS power_consumption
FROM (((`duckbubi.machine_power_consumption.bending_raw`
INNER JOIN `duckbubi.machine_power_consumption.laser_cutting_raw`
    ON `duckbubi.machine_power_consumption.bending_raw`.time_stamp = `duckbubi.machine_power_consumption.laser_cutting_raw`.time_stamp)
INNER JOIN `duckbubi.machine_power_consumption.laser_shaping_raw`
    ON `duckbubi.machine_power_consumption.bending_raw`.time_stamp = `duckbubi.machine_power_consumption.laser_shaping_raw`.time_stamp)
INNER JOIN `duckbubi.machine_power_consumption.robot_welter_raw`
    ON `duckbubi.machine_power_consumption.bending_raw`.time_stamp = `duckbubi.machine_power_consumption.robot_welter_raw`.time_stamp)
GROUP BY hour
ORDER BY hour;

### Total Factory electricity cost by month

SELECT DATETIME_TRUNC(`duckbubi.machine_power_consumption.bending_raw`.time_stamp, MONTH) AS month,
        SUM(`duckbubi.machine_power_consumption.bending_raw`.power_consumption)*1000*2.5 AS power_consumption
FROM (((`duckbubi.machine_power_consumption.bending_raw`
INNER JOIN `duckbubi.machine_power_consumption.laser_cutting_raw`
    ON `duckbubi.machine_power_consumption.bending_raw`.time_stamp = `duckbubi.machine_power_consumption.laser_cutting_raw`.time_stamp)
INNER JOIN `duckbubi.machine_power_consumption.laser_shaping_raw`
    ON `duckbubi.machine_power_consumption.bending_raw`.time_stamp = `duckbubi.machine_power_consumption.laser_shaping_raw`.time_stamp)
INNER JOIN `duckbubi.machine_power_consumption.robot_welter_raw`
    ON `duckbubi.machine_power_consumption.bending_raw`.time_stamp = `duckbubi.machine_power_consumption.robot_welter_raw`.time_stamp)
GROUP BY month
ORDER BY month;

## BigQuery

dataset ID : machine_power_consumption

schema

time_stamp:string,
power_consumption:float

{
    "time_stamp":      ,
    "power_consumption": 


}

### Console command

bq mk \
--schema time_stamp:DATETIME,power_consumption:float -t machine_power_consumption.bending_raw

bq mk \
--schema time_stamp:DATETIME,power_consumption:float -t machine_power_consumption.laser_cutting_raw

bq mk \
--schema time_stamp:DATETIME,power_consumption:float -t machine_power_consumption.laser_shaping_raw

bq mk \
--schema time_stamp:DATETIME,power_consumption:float -t machine_power_consumption.robot_welter_raw

## Select closest time
https://stackoverflow.com/questions/14023145/find-closest-date-in-sql-server

## DataFlow

gcloud dataflow jobs run fetch-bending --gcs-location gs://dataflow-templates-asia-east1/latest/PubSub_to_BigQuery --region asia-east1 --staging-location gs://duckbubi/bending/ --parameters inputTopic=projects/duckbubi/topics/bending,outputTableSpec=duckbubi:machine_power_consumption.realtime --enable-streaming-engine

### bending

gcloud dataflow jobs run fetch-bending --gcs-location gs://dataflow-templates-asia-east1/latest/PubSub_to_BigQuery --region asia-east1 --staging-location gs://duckbubi/bending/ --parameters inputTopic=projects/duckbubi/topics/bending,outputTableSpec=duckbubi:machine_power_consumption.bending_raw --enable-streaming-engine

### laser_cutting

gcloud dataflow jobs run fetch-laser_cutting --gcs-location gs://dataflow-templates-asia-east1/latest/PubSub_to_BigQuery --region asia-east1 --staging-location gs://duckbubi/laser_cutting/ --parameters inputTopic=projects/duckbubi/topics/laser_cutting,outputTableSpec=duckbubi:machine_power_consumption.laser_cutting_raw --enable-streaming-engine

### laser_shaping

gcloud dataflow jobs run fetch-laser_shaping --gcs-location gs://dataflow-templates-asia-east1/latest/PubSub_to_BigQuery --region asia-east1 --staging-location gs://duckbubi/laser_shaping/ --parameters inputTopic=projects/duckbubi/topics/laser_shaping,outputTableSpec=duckbubi:machine_power_consumption.laser_shaping_raw --enable-streaming-engine

### robot_welter

gcloud dataflow jobs run fetch-robot_welter --gcs-location gs://dataflow-templates-asia-east1/latest/PubSub_to_BigQuery --region asia-east1 --staging-location gs://duckbubi/robot_welter/ --parameters inputTopic=projects/duckbubi/topics/robot_welter,outputTableSpec=duckbubi:machine_power_consumption.robot_welter_raw --enable-streaming-engine

### enable streaming engine

--enable-streaming-engine