Skip to content

Instantly share code, notes, and snippets.

View sinmetal's full-sized avatar

sinmetal sinmetal

96 followers · 25 following
View GitHub Profile
@sinmetal
sinmetal / select_json_from_syslog.sql
Created July 31, 2015 10:59
syslogにjson出力して、bigqueryでview作るためのsql
SELECT
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.id') AS id,
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.kind') AS kind,
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.selfLink') AS selfLink,
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.name') AS name,
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.bucket') AS bucket,
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.generation') AS generation,
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.metageneration') AS metageneration,
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.contentType') AS contentType,
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE_
@sinmetal
sinmetal / select_syslog_from_cloudlogging.sql
Created August 8, 2015 01:22
Cloud Logging経由で出力したjsonをViewに構築し直すためのクエリ
SELECT
SUBSTR(
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.id'),
2, LENGTH(JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.id')) - 2)AS id,
SUBSTR(
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.kind'),
2, LENGTH(JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.kind')) - 2)AS kind,
SUBSTR(
JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.selfLink'),
2, LENGTH(JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.selfLink')) - 2)AS selfLink,
@sinmetal
sinmetal / main.go
Created August 13, 2015 06:45
EpochTimeで入っているjsonをstructに読み込むためのサンプル
package main
import (
"encoding/json"
"fmt"
"strconv"
"time"
)
type Hoge struct {
@sinmetal
sinmetal / SearchStorageMonitorController.java
Created September 3, 2015 07:35
App Engine Search API のStaticsを見るサンプル
package org.sinmetal.controller;
import java.io.*;
import java.util.*;
import org.slim3.controller.*;
import com.google.appengine.api.search.*;
public class SearchStorageMonitorController extends SimpleController {
@sinmetal
sinmetal / select-from-auditlog-where-billing.sql
Created February 12, 2016 04:58
BigQuery Audit Logから現在のBillingと、NewBillingの値を算出するQuery
SELECT
protoPayload.authenticationInfo.principalEmail,
protoPayload.serviceData.jobCompletedEvent.job.jobName.jobId,
protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.billingTier,
protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes,
ROUND(((protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes*5)/1000000000000),2) Cost_In_Dollars_Now,
ROUND(((protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes*(5*protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.billingTier))/1000000000000),2) Cost_In_Dollars_New,
protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query,
FROM TABLE_DATE_RANGE(bigquery.cloudaudit_googleapis_com_data_access_, DATE_ADD(CURRENT_TIMESTAMP(), -3, 'DAY'), CURRENT_TIMESTAMP())
WHERE
@sinmetal
sinmetal / gaereverseproxy.go
Created February 23, 2016 06:26 — forked from pokstad/gaereverseproxy.go
Google App Engine reverse proxy in Golang
// Copyright 2011 The Go Authors. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.
// HTTP reverse proxy handler
package goengine
import (
"io"
@sinmetal
sinmetal / goonchk.go
Created March 28, 2016 05:30
package goonchk
import (
"fmt"
"net/http"
"github.com/mjibson/goon"
"google.golang.org/appengine/datastore"
)
@sinmetal
sinmetal / matchArray.bjs
Created May 27, 2016 09:41
keyword, arrayを渡して、arrayに含まれる文字を正規表現で探してマッチしたら、マッチした文字列を返すBQのUDF
function passthroughExample(row, emit) {
var output = [];
row.keyword. forEach(function(element, index, array) {
var match = row.data.match(element);
if (match) {
output.push(match[0]);
}
})
emit({output:output})
}
@sinmetal
sinmetal / gcs_sample.go
Created June 24, 2016 05:32
example google.DefaultClient()
package gcs_sample
import (
"net/http"
"fmt"
"google.golang.org/appengine"
"google.golang.org/appengine/log"
"golang.org/x/oauth2/google"
@sinmetal
sinmetal / letsencypt.go
Created July 10, 2016 22:51
package main
import (
"net/http"
)
func init() {
http.HandleFunc("/.well-known/acme-challenge/", handlerLetsencypt)
}