I would like to create an API host server which will hit BigQuery and display query result on the webpage
I have managed to create the actual server, which displays information by using the GET method (Based on dates). In addition, I have a separate code which could get the data from BigQuery. But unfortunately, I do not have enough knowledge to combine them together.
This is the current code:
Server with attempts to combine:
package main
import (
"fmt"
"io"
"net/http"
"os"
"cloud.google.com/go/civil"
)
type bqStructure struct {
SQLDate civil.Date `bigquery:"SQLDate"`
LocalStoreID string `bigquery:"LocalStoreID"`
LocalStoreName string `bigquery:"LocalStoreName"`
Longitude float64 `bigquery:"Longitude"`
Latitude float64 `bigquery:"Latitude"`
LONG_LAT string `bigquery:"lonLat"`
PLU string `bigquery:"PLU"`
PluProductName string `bigquery:"PluProductName"`
ORDERED string `bigquery:"Ordered"`
SOLD string `bigquery:"Sold"`
IMAGE_URL string `bigquery:"ImageURL"`
}
func queryParamDisplayHandler(res http.ResponseWriter, req *http.Request) {
io.WriteString(res, "StartDate: "+req.FormValue("start"))
io.WriteString(res, "
EndDate: "+req.FormValue("end"))
}
func main() {
proj := os.Getenv("GOOGLE_CLOUD_PROJECT")
if proj == "" {
fmt.Println("GOOGLE_CLOUD_PROJECT environment variable must be set.")
os.Exit(1)
}
http.HandleFunc("/map", func(res http.ResponseWriter, req *http.Request) {
queryParamDisplayHandler(res, req)
})
println("Enter this in your browser: http://localhost:80/example?start=20180101&end=20190101")
http.ListenAndServe(":80", nil)
}
And this is how I hit BigQuery:
package main
import (
"context"
"fmt"
"io"
"log"
"os"
"cloud.google.com/go/bigquery"
"cloud.google.com/go/civil"
"google.golang.org/api/iterator"
)
func main() {
proj := os.Getenv("GOOGLE_CLOUD_PROJECT")
if proj == "" {
fmt.Println("GOOGLE_CLOUD_PROJECT environment variable must be set.")
os.Exit(1)
}
rows, err := query(proj)
if err != nil {
log.Fatal(err)
}
if err := printResults(os.Stdout, rows); err != nil {
log.Fatal(err)
}
}
// query returns a slice of the results of a query.
func query(proj string) (*bigquery.RowIterator, error) {
ctx := context.Background()
client, err := bigquery.NewClient(ctx, proj)
if err != nil {
return nil, err
}
query := client.Query(`SELECT * FROM ` + "`XXXXX.XXXX.XXXX`" + `LIMIT 1000`)
return query.Read(ctx)
}
type StackOverflowRow struct {
SQLDate civil.Date `bigquery:"SQLDate"`
LocalStoreID string `bigquery:"LocalStoreID"`
LocalStoreName string `bigquery:"LocalStoreName"`
Longitude float64 `bigquery:"Longitude"`
Latitude float64 `bigquery:"Latitude"`
LONG_LAT string `bigquery:"lonLat"`
PLU string `bigquery:"PLU"`
PluProductName string `bigquery:"PluProductName"`
ORDERED string `bigquery:"Ordered"`
SOLD string `bigquery:"Sold"`
IMAGE_URL string `bigquery:"ImageURL"`
}
func printResults(w io.Writer, iter *bigquery.RowIterator) error {
for {
var row StackOverflowRow
err := iter.Next(&row)
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
fmt.Fprintf(w, "SQLDate: %s LocalStoreID: %s LocalStoreName: %s Longitude: %f Latitude: %f LONG_LAT: %s PLU: %s PluProductName: %s ORDERED: %s SOLD: %s IMAGE_URL: %s
", row.SQLDate, row.LocalStoreID, row.LocalStoreName, row.Longitude, row.Latitude, row.LONG_LAT, row.PLU, row.PluProductName, row.ORDERED, row.SOLD, row.IMAGE_URL)
}
}
I would like to find a way to host the server and get the information from BigQuery based on START and END variables which have to be displayed on a webpage.
Link to display result from the server - http://localhost:80/map?start=20180101&end=20190101
Everything working separately, so each code does work
LATEST UPDATE:
Does work, BUT information from BQ displays in terminal, need to send it somehwo to webpage.
package main
import (
"context"
"fmt"
"io"
"log"
"net/http"
"os"
"cloud.google.com/go/bigquery"
"cloud.google.com/go/civil"
"google.golang.org/api/iterator"
)
type bqStructure struct {
SQLDate civil.Date `bigquery:"SQLDate"`
LocalStoreID string `bigquery:"LocalStoreID"`
LocalStoreName string `bigquery:"LocalStoreName"`
Longitude float64 `bigquery:"Longitude"`
Latitude float64 `bigquery:"Latitude"`
LONG_LAT string `bigquery:"lonLat"`
PLU string `bigquery:"PLU"`
PluProductName string `bigquery:"PluProductName"`
ORDERED string `bigquery:"Ordered"`
SOLD string `bigquery:"Sold"`
IMAGE_URL string `bigquery:"ImageURL"`
}
func query(proj string) (*bigquery.RowIterator, error) {
ctx := context.Background()
client, err := bigquery.NewClient(ctx, proj)
if err != nil {
return nil, err
}
query := client.Query(`SELECT * FROM ` + "`XXX`" + `LIMIT 1000`)
return query.Read(ctx)
}
func printResults(w io.Writer, iter *bigquery.RowIterator) error {
for {
var row bqStructure
err := iter.Next(&row)
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
fmt.Fprintf(w, "SQLDate: %s LocalStoreID: %s LocalStoreName: %s Longitude: %f Latitude: %f LONG_LAT: %s PLU: %s PluProductName: %s ORDERED: %s SOLD: %s IMAGE_URL: %s
", row.SQLDate, row.LocalStoreID, row.LocalStoreName, row.Longitude, row.Latitude, row.LONG_LAT, row.PLU, row.PluProductName, row.ORDERED, row.SOLD, row.IMAGE_URL)
}
}
func queryParamDisplayHandler(proj string, res http.ResponseWriter, req *http.Request) {
io.WriteString(res, "StartDate: "+req.FormValue("start"))
io.WriteString(res, "
EndDate: "+req.FormValue("end"))
rows, err := query(proj)
if err != nil {
log.Fatal(err)
}
if err := printResults(os.Stdout, rows); err != nil {
log.Fatal(err)
}
}
func main() {
proj := os.Getenv("GOOGLE_CLOUD_PROJECT")
if proj == "" {
fmt.Println("GOOGLE_CLOUD_PROJECT environment variable must be set.")
os.Exit(1)
}
http.HandleFunc("/map", func(res http.ResponseWriter, req *http.Request) {
queryParamDisplayHandler(proj, res, req)
})
println("Enter this in your browser: http://localhost:80/map?start=20180101&end=20190101")
http.ListenAndServe(":80", nil)
}
Need to find how to convert this
fmt.Fprintf(w, "SQLDate: %s LocalStoreID: %s LocalStoreName: %s Longitude: %f Latitude: %f LONG_LAT: %s PLU: %s PluProductName: %s ORDERED: %s SOLD: %s IMAGE_URL: %s
", row.SQLDate, row.LocalStoreID, row.LocalStoreName, row.Longitude, row.Latitude, row.LONG_LAT, row.PLU, row.PluProductName, row.ORDERED, row.SOLD, row.IMAGE_URL)
TO be able to display on web page???