func handlerContainer2(w http.ResponseWriter, r *http.Request) {
ctx := appengine.NewContext(r)
client := &http.Client{
Transport: &oauth2.Transport{
Source: google.AppEngineTokenSource(ctx, bigquery.BigqueryScope),
Base: &urlfetch.Transport{Context: ctx},
},
}
bq, err := bigquery.New(client)
if err != nil {
fmt.Errorf("%v", err)
}
key := datastore.Key{}
c := Container2{
Hoge: Hoge{Name: "hoge", Age: 28},
Key: &key,
}
schema, err := ironmole.BuildTableSchema(&c)
if err != nil {
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
err = ironmole.CreateTable(bq, "cp300demo1", "go2bq", "Container2", schema)
if err != nil {
log.Errorf(ctx, "%v", err)
}
}
// connect - opens a new connection to bigquery,
// reusing the token if possible or regenerating a new auth token if required
func (c *Client) connect() (*bigquery.Service, error) {
if c.token != nil {
if !c.token.Valid() && c.service != nil {
return c.service, nil
}
}
// generate auth token and create service object
//authScope := bigquery.BigqueryScope
pemKeyBytes, err := ioutil.ReadFile(c.pemPath)
if err != nil {
panic(err)
}
t, err := google.JWTConfigFromJSON(
pemKeyBytes,
"https://www.googleapis.com/auth/bigquery")
//t := jwt.NewToken(c.accountEmailAddress, bigquery.BigqueryScope, pemKeyBytes)
client := t.Client(oauth2.NoContext)
service, err := bigquery.New(client)
if err != nil {
return nil, err
}
c.service = service
return service, nil
}
func newBigqueryService(client *http.Client) (*bigqueryService, error) {
s, err := bq.New(client)
if err != nil {
return nil, fmt.Errorf("constructing bigquery client: %v", err)
}
return &bigqueryService{s: s}, nil
}
func newBigqueryService(client *http.Client, endpoint string) (*bigqueryService, error) {
s, err := bq.New(client)
if err != nil {
return nil, fmt.Errorf("constructing bigquery client: %v", err)
}
s.BasePath = endpoint
return &bigqueryService{s: s}, nil
}
// NewBigQueryService returns a new BigQuery service (client), authenticated via OAuth2/JWT.
//
// NOTE: This function authenticates with Google OAuth2 service,
// thus susceptible to network delays and blocks.
func NewBigQueryService(c *jwt.Config) (service *bigquery.Service, err error) {
// Create *http.Client.
client := c.Client(oauth2.NoContext)
// Create authenticated BigQuery service.
service, err = bigquery.New(client)
// No need to check if err != nil since we return anyways.
return
}
// Helper method to create an authenticated connection.
func connect() (*oauth2.Token, *bigquery.Service, error) {
if *clientId == "" {
return nil, nil, fmt.Errorf("no client id specified")
}
if *serviceAccount == "" {
return nil, nil, fmt.Errorf("no service account specified")
}
if *projectId == "" {
return nil, nil, fmt.Errorf("no project id specified")
}
authScope := bigquery.BigqueryScope
if *pemFile == "" {
return nil, nil, fmt.Errorf("no credentials specified")
}
pemBytes, err := ioutil.ReadFile(*pemFile)
if err != nil {
return nil, nil, fmt.Errorf("could not access credential file %v - %v", pemFile, err)
}
jwtConfig := &jwt.Config{
Email: *serviceAccount,
Scopes: []string{authScope},
PrivateKey: pemBytes,
TokenURL: "https://accounts.google.com/o/oauth2/token",
}
token, err := jwtConfig.TokenSource(oauth2.NoContext).Token()
if err != nil {
return nil, nil, err
}
if !token.Valid() {
return nil, nil, fmt.Errorf("invalid token for BigQuery oauth")
}
config := &oauth2.Config{
ClientID: *clientId,
ClientSecret: *clientSecret,
Scopes: []string{authScope},
Endpoint: oauth2.Endpoint{
AuthURL: "https://accounts.google.com/o/oauth2/auth",
TokenURL: "https://accounts.google.com/o/oauth2/token",
},
}
client := config.Client(oauth2.NoContext, token)
service, err := bigquery.New(client)
if err != nil {
fmt.Printf("Failed to create new service: %v\n", err)
return nil, nil, err
}
return token, service, nil
}
// BuildBigQueryTables is a utility to configure BigQuery table for logging.
func BuildBigQueryTables(req *wcg.Request) error {
if !LogSinkConfig.IsBigQueryEnabled() {
return fmt.Errorf("BigQuery is not enabled, please check bigquery_project and bigquery_dataset configuration.")
}
client, err := bq.NewHTTPClient(NewContext(req))
if err != nil {
return err
}
service, err := bigquery.New(client)
if err != nil {
return err
}
projectID := LogSinkConfig.BigqueryProject
datasetID := LogSinkConfig.BigqueryDataset
for tableID, tbl := range LogSinkConfig.schema {
tbl.TableReference.ProjectId = projectID
tbl.TableReference.DatasetId = datasetID
tbl1, err := service.Tables.Get(projectID, datasetID, tableID).Do()
if err == nil {
req.Logger.Debugf("Table %q already exists on \"%s/%s\"", tableID, projectID, datasetID)
if len(tbl.Schema.Fields) < len(tbl1.Schema.Fields) {
req.Logger.Fatalf("You cannot remove fields from table %q!!", tableID)
continue
}
if len(tbl.Schema.Fields) == len(tbl1.Schema.Fields) {
req.Logger.Infof("No filed changes on %q", tableID)
continue
}
req.Logger.Infof("Trying to patch the exising table %q in %s/%s", tableID, LogSinkConfig.BigqueryProject, LogSinkConfig.BigqueryDataset)
upd := service.Tables.Patch(LogSinkConfig.BigqueryProject, LogSinkConfig.BigqueryDataset, tableID, tbl)
_, err = upd.Do()
if err != nil {
req.Logger.Errorf("Could not patch %q in %s/%s: %v", tableID, LogSinkConfig.BigqueryProject, LogSinkConfig.BigqueryDataset, err)
continue
}
req.Logger.Infof("Table %q has been successfully updated in %s/%s", tableID, LogSinkConfig.BigqueryProject, LogSinkConfig.BigqueryDataset)
} else {
req.Logger.Infof("Trying to create a new table %q in %s/%s", tableID, LogSinkConfig.BigqueryProject, LogSinkConfig.BigqueryDataset)
ins := service.Tables.Insert(LogSinkConfig.BigqueryProject, LogSinkConfig.BigqueryDataset, tbl)
_, err = ins.Do()
if err != nil {
req.Logger.Errorf("Could not create a new table %q in %s/%s: %v", tableID, LogSinkConfig.BigqueryProject, LogSinkConfig.BigqueryDataset, err)
continue
}
req.Logger.Infof("Table %q has been successfully created in %s/%s", tableID, LogSinkConfig.BigqueryProject, LogSinkConfig.BigqueryDataset)
}
}
return nil
}
// NewLogSinkWithContext creates a new *LogSink for GAE
func NewLogSinkWithContext(format string, ctx context.Context) *LogSink {
var _bq *bigquery.Service
if LogSinkConfig.IsBigQueryEnabled() {
client, err := bq.NewHTTPClient(ctx)
if err == nil {
_bq, _ = bigquery.New(client)
}
}
return &LogSink{
formatter: wcg.NewLogRecordFormatter(format),
ctx: ctx,
bigquery: _bq,
}
}
func NewGAELogSinkWithContext(format string, ctx appengine.Context) *GAELogSink {
var bq *bigquery.Service
if LogSinkConfig.IsBigQueryEnabled() {
client, err := serviceaccount.NewClient(ctx, bigquery.BigqueryScope)
if err == nil {
bq, _ = bigquery.New(client)
}
}
return &GAELogSink{
formatter: NewLogRecordFormatter(format),
ctx: ctx,
bigquery: bq,
}
}
func deleteDataset(t *testing.T, ctx context.Context, datasetID string) {
tc := testutil.SystemTest(t)
hc, err := google.DefaultClient(ctx, rawbq.CloudPlatformScope)
if err != nil {
t.Errorf("DefaultClient: %v", err)
}
s, err := rawbq.New(hc)
if err != nil {
t.Errorf("bigquery.New: %v", err)
}
call := s.Datasets.Delete(tc.ProjectID, datasetID)
call.DeleteContents(true)
call.Context(ctx)
if err := call.Do(); err != nil {
t.Errorf("deleteDataset(%q): %v", datasetID, err)
}
}
// NewClient ...
func NewClient(email, pemKeyPath string) (*Client, error) {
pemKeyBytes, err := ioutil.ReadFile(pemKeyPath)
if err != nil {
return nil, err
}
conf := jwt.Config{
Email: email,
PrivateKey: pemKeyBytes,
Scopes: []string{bigquery.BigqueryScope},
TokenURL: google.JWTTokenURL,
}
client := conf.Client(oauth2.NoContext)
service, err := bigquery.New(client)
if err != nil {
return nil, err
}
return &Client{BigQueryService: service}, nil
}
func (w *Writer) Connect(email string, pem []byte) error {
cfg := jwt.Config{
Email: email,
PrivateKey: pem,
Scopes: []string{bq.BigqueryScope},
TokenURL: "https://accounts.google.com/o/oauth2/token",
}
ctx := context.Background()
client := cfg.Client(ctx)
bq, err := bq.New(client)
if err != nil {
w.warnf("connect error %v", err)
return err
}
w.service = &bigqueryService{bq}
w.debugf("connected")
return nil
}
func NewService(req *wcg.Request) (*bigquery.Service, error) {
var ctx appengine.Context
tmp := req.Local("__gaetest__context")
if tmp != nil {
ctx = tmp.(appengine.Context)
} else {
ctx = appengine.NewContext(req.HttpRequest())
}
client, err := serviceaccount.NewClient(ctx, bigquery.BigqueryScope)
if err != nil {
return nil, err
}
svc, err := bigquery.New(client)
if err != nil {
return nil, err
}
return svc, nil
}
// NewService returns a new bigquery service accessor instance
func NewService(req *wcg.Request) (*bigquery.Service, error) {
var ctx context.Context
tmp := req.Local("__gaetest__context")
if tmp != nil {
ctx = tmp.(context.Context)
} else {
ctx = appengine.NewContext(req.HTTPRequest())
}
client, err := NewHTTPClient(ctx)
if err != nil {
return nil, err
}
svc, err := bigquery.New(client)
if err != nil {
return nil, err
}
return svc, nil
}
func newBQDataset(client *http.Client, dsProj string, dsId string) (*bqDataset,
error) {
service, err := bigquery.New(client)
if err != nil {
log.Fatalf("Unable to create BigQuery service: %v", err)
}
return &bqDataset{
project: dsProj,
id: dsId,
bq: service,
dataset: &bigquery.Dataset{
DatasetReference: &bigquery.DatasetReference{
DatasetId: dsId,
ProjectId: dsProj,
},
},
jobsets: make(map[string]*list.List),
}, nil
}
func newBQDataset(client *http.Client, dsProj string, dsId string) (*bqDataset,
error) {
service, err := bigquery.New(client)
if err != nil {
return nil, err
}
return &bqDataset{
project: dsProj,
id: dsId,
bq: service,
dataset: &bigquery.Dataset{
DatasetReference: &bigquery.DatasetReference{
DatasetId: dsId,
ProjectId: dsProj,
},
},
jobsets: make(map[string]*list.List),
}, nil
}
func handlerInsert(w http.ResponseWriter, r *http.Request) {
ctx := appengine.NewContext(r)
client := &http.Client{
Transport: &oauth2.Transport{
Source: google.AppEngineTokenSource(ctx, bigquery.BigqueryScope),
Base: &urlfetch.Transport{Context: ctx},
},
}
bq, err := bigquery.New(client)
if err != nil {
fmt.Errorf("%v", err)
}
key := datastore.Key{}
c := Container2{
Hoge: Hoge{Name: "hoge", Age: 28},
Key: &key,
}
jsonValue, err := ironmole.BuildJsonValue(&c)
if err != nil {
log.Errorf(ctx, "%v", err)
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
res, err := ironmole.Insert(bq, "cp300demo1", "go2bq", "Container2", jsonValue)
if err != nil {
log.Errorf(ctx, "%v", err)
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
for _, insertError := range res.InsertErrors {
for _, err := range insertError.Errors {
log.Errorf(ctx, "Insert Error = %v", err)
}
}
}
func handlerTableMoge(w http.ResponseWriter, r *http.Request) {
ctx := appengine.NewContext(r)
client := &http.Client{
Transport: &oauth2.Transport{
Source: google.AppEngineTokenSource(ctx, bigquery.BigqueryScope),
Base: &urlfetch.Transport{Context: ctx},
},
}
bq, err := bigquery.New(client)
if err != nil {
log.Errorf(ctx, "%v", err)
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
table := "Moge"
tableParam := r.FormValue("table")
if len(tableParam) > 0 {
table = tableParam
}
moge := Moge{}
schema, err := ironmole.BuildTableSchemaWithContext(ctx, &moge)
if err != nil {
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
err = ironmole.CreateTable(bq, "cp300demo1", "go2bq", table, schema)
if err != nil {
log.Errorf(ctx, "%v", err)
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
w.WriteHeader(http.StatusOK)
w.Write([]byte("done"))
}
func initBigQueryService(credentialsFileName string) error {
var client *http.Client
credentialsData, err := ioutil.ReadFile(credentialsFileName)
if err != nil {
return err
}
var conf *jwt.Config
conf, err = google.JWTConfigFromJSON(credentialsData, bigQueryScope)
if err != nil {
return err
}
client = conf.Client(oauth2.NoContext)
if bq, err = bigquery.New(client); err != nil {
return err
}
return nil
}
func createTables(writer http.ResponseWriter, req *http.Request) {
context := appengine.NewContext(req)
client, err := google.DefaultClient(context, scope)
if err != nil {
writer.WriteHeader(http.StatusInternalServerError)
log.Criticalf(context, "Unable to get default client: %v", err)
return
}
service, err := bigquery.New(client)
if err != nil {
log.Criticalf(context, "tables/tables.go: Unable to create bigquery service: %v", err)
writer.WriteHeader(http.StatusUnauthorized)
return
}
t := time.Now()
for i := 1; i < 3; i++ {
t = t.AddDate(0, 0, 1)
log.Infof(context, "tables/tables.go: Creating tables for %v-%v-%v", t.Year(), int(t.Month()), t.Day())
insertTablesForTime(t, service, errorHandler(writer, context))
}
}
// Initializes all the bigquery data needed
func NewBqUploader(pkey []byte, projectId string, datasetId string, serviceEmail string) *BqUploader {
conf := &jwt.Config{
Email: serviceEmail,
PrivateKey: pkey,
Scopes: []string{
bigquery.BigqueryScope,
},
TokenURL: google.JWTTokenURL,
}
// Initiate an http.Client, the following GET request will be
client := conf.Client(oauth2.NoContext)
// "wego-cloud", "analytics-golang"
bq, errBq := bigquery.New(client)
if errBq != nil {
log.Fatalf("Unable to create BigQuery service: %v", errBq)
}
return &BqUploader{
bq: bq,
projectId: projectId,
datasetId: datasetId,
}
}
func main() {
flag.Parse()
// setEnvVars()
client, err := GoogleClient()
if err == nil {
bq, _ := bigquery.New(client)
dsr := new(bigquery.DatasetReference)
dsr.DatasetId = *bqSourceDataset
dsr.ProjectId = *bqSourceProject
// request := new(bigquery.QueryRequest)
// request.DefaultDataset = dsr
// request.Query = "SELECT count(*) FROM []"
// call := bq.Jobs.Query("", request)
// resp, err := call.Do()
// jobs := new(bigquery.JobsService)
// job := jobs.Query("sapient-catbird-547", request)
// resp, err := job.Do()
// fmt.Print(resp.CacheHit, resp.JobReference, err)
// jobId := resp.JobReference.JobId
// s, _ := bq.Jobs.GetQueryResults("", jobId).Do()
// buf, _ := json.Marshal(s)
// fmt.Println(s, string(buf), "\n\n\n")
tabr := new(bigquery.TableReference)
tabr.DatasetId = *bqDestDataset
tabr.ProjectId = *bqDestProject
tabr.TableId = "temp_grouped_v2"
// jcq := new(bigquery.JobConfigurationQuery)
// jcq.DestinationTable = tabr
// jcq.Priority = "BATCH"
// jcq.WriteDisposition = "WRITE_TRUNCATE"
// jcq.Query = "SELECT ap_mac, COUNT(DISTINCT(client_mac)), DATE(TIMESTAMP(first_seen)) date FROM [dev_sense_v1.sensev4_ct] GROUP BY ap_mac, date"
// jc := new(bigquery.JobConfiguration)
// jc.Query = jcq
// job := new(bigquery.Job)
// job.Configuration = jc
// aa, err := bq.Jobs.Insert(*bqSourceProject, job).Do()
// if err == nil {
// fmt.Print(aa.Id)
// } else {
// fmt.Print(err)
// }
jce := new(bigquery.JobConfigurationExtract)
jce.DestinationFormat = "csv"
jce.DestinationUri = "gs://ct_temp/151028.csv"
jce.SourceTable = tabr
extractJc := new(bigquery.JobConfiguration)
extractJc.Extract = jce
extractJob := new(bigquery.Job)
extractJob.Configuration = extractJc
aa, err := bq.Jobs.Insert(*bqSourceProject, extractJob).Do()
if err == nil {
fmt.Print(aa.Id)
} else {
fmt.Print(err)
}
}
}
func queryIntoDatastore(w http.ResponseWriter, r *http.Request, m map[string]interface{}) {
limitUpper := util.MonthsBack(1)
limitLower := util.MonthsBack(25)
var q bq.QueryRequest = bq.QueryRequest{}
q.Query = `
SELECT
repository_language
, LEFT(repository_pushed_at,7) monthx
, CEIL( count(*)/1000) Tausend
FROM githubarchive:github.timeline
where 1=1
AND LEFT(repository_pushed_at,7) >= '` + limitLower + `'
AND LEFT(repository_pushed_at,7) <= '` + limitUpper + `'
AND repository_language in ('Go','go','Golang','golang','C','Java','PHP','JavaScript','C++','Python','Ruby')
AND type="PushEvent"
group by monthx, repository_language
order by repository_language , monthx
;
`
c := appengine.NewContext(r)
// The following client will be authorized by the App Engine
// app's service account for the provided scopes.
// "https://www.googleapis.com/auth/bigquery"
// "https://www.googleapis.com/auth/devstorage.full_control"
// 2015-06: instead of oauth2.NoContext we get a new type of context
var ctx context.Context = appengine.NewContext(r)
oauthHttpClient, err := google.DefaultClient(
ctx, "https://www.googleapis.com/auth/bigquery")
if err != nil {
log.Fatal(err)
}
bigqueryService, err := bq.New(oauthHttpClient)
loghttp.E(w, r, err, false)
fmt.Fprint(w, "s1<br>\n")
// Create a query statement and query request object
// query_data = {'query':'SELECT TOP(title, 10) as title, COUNT(*) as revision_count FROM [publicdata:samples.wikipedia] WHERE wp_namespace = 0;'}
// query_request = bigquery_service.jobs()
// Make a call to the BigQuery API
// query_response = query_request.query(projectId=PROJECT_NUMBER, body=query_data).execute()
js := bq.NewJobsService(bigqueryService)
jqc := js.Query("347979071940", &q)
fmt.Fprint(w, "s2 "+util.TimeMarker()+" <br>\n")
resp, err := jqc.Do()
loghttp.E(w, r, err, false)
rows := resp.Rows
var vVDest [][]byte = make([][]byte, len(rows))
aelog.Errorf(c, "%#v", rows)
for i0, v0 := range rows {
cells := v0.F
b_row := new(bytes.Buffer)
b_row.WriteString(fmt.Sprintf("r%0.2d -- ", i0))
for i1, v1 := range cells {
val1 := v1.V
b_row.WriteString(fmt.Sprintf("c%0.2d: %v ", i1, val1))
}
vVDest[i0] = []byte(b_row.Bytes())
}
key_combi, _ := dsu.BufPut(c, dsu.WrapBlob{Name: "bq_res1", VVByte: vVDest}, "bq_res1")
dsObj, _ := dsu.BufGet(c, key_combi)
printPlaintextTable(w, r, dsObj.VVByte)
fmt.Fprint(w, "s3 "+util.TimeMarker()+" <br>\n")
}
func NewBigQueryService(c *jwt.Config) (service *bigquery.Service, err error) {
client := c.Client(oauth2.NoContext)
service, err = bigquery.New(client)
return
}
func handlerInsertMoge(w http.ResponseWriter, r *http.Request) {
ctx := appengine.NewContext(r)
client := &http.Client{
Transport: &oauth2.Transport{
Source: google.AppEngineTokenSource(ctx, bigquery.BigqueryScope),
Base: &urlfetch.Transport{Context: ctx},
},
}
bq, err := bigquery.New(client)
if err != nil {
fmt.Errorf("%v", err)
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
keyStr := r.FormValue("key")
key, err := datastore.DecodeKey(keyStr)
if err != nil {
http.Error(w, err.Error(), http.StatusBadRequest)
return
}
table := "Moge"
tableParam := r.FormValue("table")
if len(tableParam) > 0 {
table = tableParam
}
var moge Moge
err = datastore.Get(ctx, key, &moge)
if err == datastore.ErrNoSuchEntity {
http.Error(w, err.Error(), http.StatusNotFound)
return
}
if err != nil {
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
moge.Key = key
moge.KeyStr = key.Encode()
jsonValue, err := ironmole.BuildJsonValueWithContext(ctx, &moge)
if err != nil {
log.Errorf(ctx, "%v", err)
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
res, err := ironmole.Insert(bq, "cp300demo1", "go2bq", table, jsonValue)
if err != nil {
log.Errorf(ctx, "%v", err)
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
for _, insertError := range res.InsertErrors {
for _, error := range insertError.Errors {
log.Errorf(ctx, "Insert Error = %v", error)
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
}
w.WriteHeader(http.StatusOK)
w.Write([]byte("done"))
}
