Comments (26)
Vikas-MI
commented on May 16, 2024
2
Thank you so much @goccy
it's working !!!!!! :)
from bigquery-emulator.
goccy
commented on May 16, 2024
I wrote a test code and verified it, and the result is different from what you reported.
package server_test
import (
"context"
"fmt"
"testing"
"time"
"cloud.google.com/go/bigquery"
"github.com/goccy/bigquery-emulator/server"
"github.com/goccy/bigquery-emulator/types"
"google.golang.org/api/iterator"
"google.golang.org/api/option"
)
type MockUsageEvent struct {
Key string `json:"key" binding:"required" conform:"trim"`
Product string `json:"product" conform:"trim"`
Version string `json:"version" conform:"trim"`
Platform string `json:"platform" conform:"trim"`
Event string `json:"event" binding:"required,oneofCI=activation" conform:"trim,lower"`
InstallId string `json:"installid" conform:"trim"`
Created string `json:"created"`
}
func (i *MockUsageEvent) Save() (map[string]bigquery.Value, string, error) {
return map[string]bigquery.Value{
"key": i.Key,
"product": i.Product,
"version": i.Version,
"platform": i.Platform,
"event": i.Event,
"installid": i.InstallId,
"created": i.Created,
}, bigquery.NoDedupeID, nil
}
func TestHoge(t *testing.T) {
ctx := context.Background()
bqServer, err := server.New(server.TempStorage)
if err != nil {
t.Fatal(err)
}
if err := bqServer.Load(server.StructSource(types.NewProject("test"))); err != nil {
t.Fatal(err)
}
testServer := bqServer.TestServer()
defer func() {
testServer.Close()
bqServer.Close()
}()
client, err := bigquery.NewClient(
ctx,
"test",
option.WithEndpoint(testServer.URL),
option.WithoutAuthentication(),
)
if err != nil {
t.Fatal(err)
}
defer client.Close()
meta := &bigquery.DatasetMetadata{
Location: "US", // See https://cloud.google.com/bigquery/docs/locations
}
if err := client.Dataset("dataset").Create(ctx, meta); err != nil {
t.Fatal(err)
}
sampleSchema := bigquery.Schema{
{Name: "key", Type: bigquery.StringFieldType},
{Name: "product", Type: bigquery.StringFieldType},
{Name: "version", Type: bigquery.StringFieldType},
{Name: "platform", Type: bigquery.StringFieldType},
{Name: "event", Type: bigquery.StringFieldType},
{Name: "installid", Type: bigquery.StringFieldType},
{Name: "created", Type: bigquery.StringFieldType},
}
metaData := &bigquery.TableMetadata{
Schema: sampleSchema,
ExpirationTime: time.Now().Add(time.Duration(1*60) * time.Second), // Table will be automatically deleted in 1 day.
}
tableRef := client.Dataset("dataset").Table("table")
if err := tableRef.Create(ctx, metaData); err != nil {
t.Fatal(err)
}
inserter := client.Dataset("dataset").Table("table").Inserter()
items := []*MockUsageEvent{
{Key: "1bjCljWkq6tinyAMuu0tEAhB80", Product: "Phrased Flintstones0", Version: "Phrased Flintstones0", Platform: "Phrased Flintstones0", Event: "license-request", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB81", Product: "Phrased Flintstones1", Version: "Phrased Flintstones1", Platform: "Phrased Flintstones1", Event: "camera", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB82", Product: "Phrased Flintstones2", Version: "Phrased Flintstones2", Platform: "Phrased Flintstones2", Event: "activation", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB83", Product: "Phrased Flintstones3", Version: "Phrased Flintstones3", Platform: "Phrased Flintstones3", Event: "textsearch", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB84", Product: "Phrased Flintstones4", Version: "Phrased Flintstones4", Platform: "Phrased Flintstones4", Event: "barcode", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB85", Product: "Phrased Flintstones5", Version: "Phrased Flintstones5", Platform: "Phrased Flintstones5", Event: "ocr", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB86", Product: "Phrased Flintstones6", Version: "Phrased Flintstones6", Platform: "Phrased Flintstones6", Event: "fooddetection", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB87", Product: "Phrased Flintstones7", Version: "Phrased Flintstones7", Platform: "Phrased Flintstones7", Event: "classifications", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB88", Product: "Phrased Flintstones8", Version: "Phrased Flintstones8", Platform: "Phrased Flintstones8", Event: "customdetection", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB89", Product: "Phrased Flintstones9", Version: "Phrased Flintstones9", Platform: "Phrased Flintstones9", Event: "detectinimage", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB89", Product: "Phrased Flintstones9", Version: "Phrased Flintstones10", Platform: "Phrased Flintstones10", Event: "packagedfood", InstallId: "1", Created: "2022-10-11T17:31:39"},
}
if err := inserter.Put(ctx, items); err != nil {
t.Fatal(err)
}
query := `
SELECT yearMonth, count, key
FROM (
SELECT key, FORMAT_DATE('%Y-%m', PARSE_DATETIME('%Y-%m-%d %H:%M:%S', STRING(created))) AS yearMonth, count(distinct installid) as count
FROM test.dataset.table
WHERE created BETWEEN '2022-09-14' AND DATE_ADD(DATE '2022-10-14', INTERVAL 1 DAY)
AND installid IS NOT NULL AND length(installid) > 0
GROUP BY key, yearMonth
ORDER BY yearMonth
) AS counts
WHERE key IN ("1bjCljWkq6tinyAMuu0tEAhB80","1bjCljWkq6tinyAMuu0tEAhB82","1bjCljWkq6tinyAMuu0tEAhB86") ORDER BY key
`
query2 := "SELECT * FROM `test.dataset.table`"
_ = query2
it, err := client.Query(query).Read(ctx)
if err != nil {
t.Fatal(err)
}
for {
var row []bigquery.Value
if err := it.Next(&row); err != nil {
if err == iterator.Done {
break
}
t.Fatal(err)
}
fmt.Println("row = ", row)
}
fmt.Println("TOTAL ROWS COUNT", it.TotalRows)
}
First, the result of executing query2 in the above test code is as follows. this seems to work as expected.
row = [1bjCljWkq6tinyAMuu0tEAhB80 Phrased Flintstones0 Phrased Flintstones0 Phrased Flintstones0 license-request 1 2022-10-11T17:31:39]
row = [1bjCljWkq6tinyAMuu0tEAhB81 Phrased Flintstones1 Phrased Flintstones1 Phrased Flintstones1 camera 1 2022-10-11T17:31:39]
row = [1bjCljWkq6tinyAMuu0tEAhB82 Phrased Flintstones2 Phrased Flintstones2 Phrased Flintstones2 activation 1 2022-10-11T17:31:39]
row = [1bjCljWkq6tinyAMuu0tEAhB83 Phrased Flintstones3 Phrased Flintstones3 Phrased Flintstones3 textsearch 1 2022-10-11T17:31:39]
row = [1bjCljWkq6tinyAMuu0tEAhB84 Phrased Flintstones4 Phrased Flintstones4 Phrased Flintstones4 barcode 1 2022-10-11T17:31:39]
row = [1bjCljWkq6tinyAMuu0tEAhB85 Phrased Flintstones5 Phrased Flintstones5 Phrased Flintstones5 ocr 1 2022-10-11T17:31:39]
row = [1bjCljWkq6tinyAMuu0tEAhB86 Phrased Flintstones6 Phrased Flintstones6 Phrased Flintstones6 fooddetection 1 2022-10-11T17:31:39]
row = [1bjCljWkq6tinyAMuu0tEAhB87 Phrased Flintstones7 Phrased Flintstones7 Phrased Flintstones7 classifications 1 2022-10-11T17:31:39]
row = [1bjCljWkq6tinyAMuu0tEAhB88 Phrased Flintstones8 Phrased Flintstones8 Phrased Flintstones8 customdetection 1 2022-10-11T17:31:39]
row = [1bjCljWkq6tinyAMuu0tEAhB89 Phrased Flintstones9 Phrased Flintstones9 Phrased Flintstones9 detectinimage 1 2022-10-11T17:31:39]
row = [1bjCljWkq6tinyAMuu0tEAhB89 Phrased Flintstones9 Phrased Flintstones10 Phrased Flintstones10 packagedfood 1 2022-10-11T17:31:39]
TOTAL ROWS COUNT 11
Then I ran query . The result is as follows. This query has been marked as an invalid query by ZetaSQL Analyzer.
Did it work with BigQuery ?
googleapi: Error 400: INVALID_ARGUMENT: No matching signature for operator BETWEEN for argument types: STRING, STRING, DATE. Supported signatures: (INT64) BETWEEN (UINT64) AND (UINT64); (UINT64) BETWEEN (INT64) AND (UINT64); (UINT64) BETWEEN (UINT64) AND (INT64); (UINT64) BETWEEN (INT64) AND (INT64); (INT64) BETWEEN (UINT64) AND (INT64); (INT64) BETWEEN (INT64) AND (UINT64); (ANY) BETWEEN (ANY) AND (ANY) [at 6:55], jobInternalError
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
Yes @goccy
It will work fine in bigquery
Currently, I am working on a big query test case so I tried your library big query emulator for the implementation of big query test cases (big query emulator)
But this query not giving me any record and it will give me an error.
So I am stuck at that point please guide me for the same!!!
AS PER YOUR FIRST QUERY, IT WILL WORK FINE FOR ME ALSO
BUT SUBQUERY OR ORDER BY GROUP BY FORMAT_DATE count will not work.
Thank you @goccy FOR YOUR QUICK RESPONSE
CAN YOU PLEASE GUIDE ME FOR THE SAME ?
from bigquery-emulator.
jhowliu
commented on May 16, 2024
Hi @Vikas-MI
Could you share how to new bigquery client with me? I'm stuck on create any dateset in my mock server.
I always got some error message like "Post "http://0.0.0.0:55189/projects/test/datasets?alt=json&prettyPrint=false": EOF "
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
I wrote a test code and verified it, and the result is different from what you reported.
package server_test import ( "context" "fmt" "testing" "time" "cloud.google.com/go/bigquery" "github.com/goccy/bigquery-emulator/server" "github.com/goccy/bigquery-emulator/types" "google.golang.org/api/iterator" "google.golang.org/api/option" ) type MockUsageEvent struct { Key string `json:"key" binding:"required" conform:"trim"` Product string `json:"product" conform:"trim"` Version string `json:"version" conform:"trim"` Platform string `json:"platform" conform:"trim"` Event string `json:"event" binding:"required,oneofCI=activation" conform:"trim,lower"` InstallId string `json:"installid" conform:"trim"` Created string `json:"created"` } func (i *MockUsageEvent) Save() (map[string]bigquery.Value, string, error) { return map[string]bigquery.Value{ "key": i.Key, "product": i.Product, "version": i.Version, "platform": i.Platform, "event": i.Event, "installid": i.InstallId, "created": i.Created, }, bigquery.NoDedupeID, nil } func TestHoge(t *testing.T) { ctx := context.Background() bqServer, err := server.New(server.TempStorage) if err != nil { t.Fatal(err) } if err := bqServer.Load(server.StructSource(types.NewProject("test"))); err != nil { t.Fatal(err) } testServer := bqServer.TestServer() defer func() { testServer.Close() bqServer.Close() }() client, err := bigquery.NewClient( ctx, "test", option.WithEndpoint(testServer.URL), option.WithoutAuthentication(), ) if err != nil { t.Fatal(err) } defer client.Close() meta := &bigquery.DatasetMetadata{ Location: "US", // See https://cloud.google.com/bigquery/docs/locations } if err := client.Dataset("dataset").Create(ctx, meta); err != nil { t.Fatal(err) } sampleSchema := bigquery.Schema{ {Name: "key", Type: bigquery.StringFieldType}, {Name: "product", Type: bigquery.StringFieldType}, {Name: "version", Type: bigquery.StringFieldType}, {Name: "platform", Type: bigquery.StringFieldType}, {Name: "event", Type: bigquery.StringFieldType}, {Name: "installid", Type: bigquery.StringFieldType}, {Name: "created", Type: bigquery.StringFieldType}, } metaData := &bigquery.TableMetadata{ Schema: sampleSchema, ExpirationTime: time.Now().Add(time.Duration(1*60) * time.Second), // Table will be automatically deleted in 1 day. } tableRef := client.Dataset("dataset").Table("table") if err := tableRef.Create(ctx, metaData); err != nil { t.Fatal(err) } inserter := client.Dataset("dataset").Table("table").Inserter() items := []*MockUsageEvent{ {Key: "1bjCljWkq6tinyAMuu0tEAhB80", Product: "Phrased Flintstones0", Version: "Phrased Flintstones0", Platform: "Phrased Flintstones0", Event: "license-request", InstallId: "1", Created: "2022-10-11T17:31:39"}, {Key: "1bjCljWkq6tinyAMuu0tEAhB81", Product: "Phrased Flintstones1", Version: "Phrased Flintstones1", Platform: "Phrased Flintstones1", Event: "camera", InstallId: "1", Created: "2022-10-11T17:31:39"}, {Key: "1bjCljWkq6tinyAMuu0tEAhB82", Product: "Phrased Flintstones2", Version: "Phrased Flintstones2", Platform: "Phrased Flintstones2", Event: "activation", InstallId: "1", Created: "2022-10-11T17:31:39"}, {Key: "1bjCljWkq6tinyAMuu0tEAhB83", Product: "Phrased Flintstones3", Version: "Phrased Flintstones3", Platform: "Phrased Flintstones3", Event: "textsearch", InstallId: "1", Created: "2022-10-11T17:31:39"}, {Key: "1bjCljWkq6tinyAMuu0tEAhB84", Product: "Phrased Flintstones4", Version: "Phrased Flintstones4", Platform: "Phrased Flintstones4", Event: "barcode", InstallId: "1", Created: "2022-10-11T17:31:39"}, {Key: "1bjCljWkq6tinyAMuu0tEAhB85", Product: "Phrased Flintstones5", Version: "Phrased Flintstones5", Platform: "Phrased Flintstones5", Event: "ocr", InstallId: "1", Created: "2022-10-11T17:31:39"}, {Key: "1bjCljWkq6tinyAMuu0tEAhB86", Product: "Phrased Flintstones6", Version: "Phrased Flintstones6", Platform: "Phrased Flintstones6", Event: "fooddetection", InstallId: "1", Created: "2022-10-11T17:31:39"}, {Key: "1bjCljWkq6tinyAMuu0tEAhB87", Product: "Phrased Flintstones7", Version: "Phrased Flintstones7", Platform: "Phrased Flintstones7", Event: "classifications", InstallId: "1", Created: "2022-10-11T17:31:39"}, {Key: "1bjCljWkq6tinyAMuu0tEAhB88", Product: "Phrased Flintstones8", Version: "Phrased Flintstones8", Platform: "Phrased Flintstones8", Event: "customdetection", InstallId: "1", Created: "2022-10-11T17:31:39"}, {Key: "1bjCljWkq6tinyAMuu0tEAhB89", Product: "Phrased Flintstones9", Version: "Phrased Flintstones9", Platform: "Phrased Flintstones9", Event: "detectinimage", InstallId: "1", Created: "2022-10-11T17:31:39"}, {Key: "1bjCljWkq6tinyAMuu0tEAhB89", Product: "Phrased Flintstones9", Version: "Phrased Flintstones10", Platform: "Phrased Flintstones10", Event: "packagedfood", InstallId: "1", Created: "2022-10-11T17:31:39"}, } if err := inserter.Put(ctx, items); err != nil { t.Fatal(err) } query := ` SELECT yearMonth, count, key FROM ( SELECT key, FORMAT_DATE('%Y-%m', PARSE_DATETIME('%Y-%m-%d %H:%M:%S', STRING(created))) AS yearMonth, count(distinct installid) as count FROM test.dataset.table WHERE created BETWEEN '2022-09-14' AND DATE_ADD(DATE '2022-10-14', INTERVAL 1 DAY) AND installid IS NOT NULL AND length(installid) > 0 GROUP BY key, yearMonth ORDER BY yearMonth ) AS counts WHERE key IN ("1bjCljWkq6tinyAMuu0tEAhB80","1bjCljWkq6tinyAMuu0tEAhB82","1bjCljWkq6tinyAMuu0tEAhB86") ORDER BY key ` query2 := "SELECT * FROM `test.dataset.table`" _ = query2 it, err := client.Query(query).Read(ctx) if err != nil { t.Fatal(err) } for { var row []bigquery.Value if err := it.Next(&row); err != nil { if err == iterator.Done { break } t.Fatal(err) } fmt.Println("row = ", row) } fmt.Println("TOTAL ROWS COUNT", it.TotalRows) }First, the result of executing query2 in the above test code is as follows. this seems to work as expected.
row = [1bjCljWkq6tinyAMuu0tEAhB80 Phrased Flintstones0 Phrased Flintstones0 Phrased Flintstones0 license-request 1 2022-10-11T17:31:39] row = [1bjCljWkq6tinyAMuu0tEAhB81 Phrased Flintstones1 Phrased Flintstones1 Phrased Flintstones1 camera 1 2022-10-11T17:31:39] row = [1bjCljWkq6tinyAMuu0tEAhB82 Phrased Flintstones2 Phrased Flintstones2 Phrased Flintstones2 activation 1 2022-10-11T17:31:39] row = [1bjCljWkq6tinyAMuu0tEAhB83 Phrased Flintstones3 Phrased Flintstones3 Phrased Flintstones3 textsearch 1 2022-10-11T17:31:39] row = [1bjCljWkq6tinyAMuu0tEAhB84 Phrased Flintstones4 Phrased Flintstones4 Phrased Flintstones4 barcode 1 2022-10-11T17:31:39] row = [1bjCljWkq6tinyAMuu0tEAhB85 Phrased Flintstones5 Phrased Flintstones5 Phrased Flintstones5 ocr 1 2022-10-11T17:31:39] row = [1bjCljWkq6tinyAMuu0tEAhB86 Phrased Flintstones6 Phrased Flintstones6 Phrased Flintstones6 fooddetection 1 2022-10-11T17:31:39] row = [1bjCljWkq6tinyAMuu0tEAhB87 Phrased Flintstones7 Phrased Flintstones7 Phrased Flintstones7 classifications 1 2022-10-11T17:31:39] row = [1bjCljWkq6tinyAMuu0tEAhB88 Phrased Flintstones8 Phrased Flintstones8 Phrased Flintstones8 customdetection 1 2022-10-11T17:31:39] row = [1bjCljWkq6tinyAMuu0tEAhB89 Phrased Flintstones9 Phrased Flintstones9 Phrased Flintstones9 detectinimage 1 2022-10-11T17:31:39] row = [1bjCljWkq6tinyAMuu0tEAhB89 Phrased Flintstones9 Phrased Flintstones10 Phrased Flintstones10 packagedfood 1 2022-10-11T17:31:39] TOTAL ROWS COUNT 11Then I ran
query. The result is as follows. This query has been marked as an invalid query by ZetaSQL Analyzer. Did it work with BigQuery ?googleapi: Error 400: INVALID_ARGUMENT: No matching signature for operator BETWEEN for argument types: STRING, STRING, DATE. Supported signatures: (INT64) BETWEEN (UINT64) AND (UINT64); (UINT64) BETWEEN (INT64) AND (UINT64); (UINT64) BETWEEN (UINT64) AND (INT64); (UINT64) BETWEEN (INT64) AND (INT64); (INT64) BETWEEN (UINT64) AND (INT64); (INT64) BETWEEN (INT64) AND (UINT64); (ANY) BETWEEN (ANY) AND (ANY) [at 6:55], jobInternalError
Hi @goccy
Yes, I get exactly the same error as you get.
Bigquery is working fine but in bigquery emulator, it's not working.
Could you please suggest to me the proper solution for it?
I am stuck here.
googleapi: Error 400: INVALID_ARGUMENT: No matching signature for operator BETWEEN for argument types: STRING, STRING, DATE. Supported signatures: (INT64) BETWEEN (UINT64) AND (UINT64); (UINT64) BETWEEN (INT64) AND (UINT64); (UINT64) BETWEEN (UINT64) AND (INT64); (UINT64) BETWEEN (INT64) AND (INT64); (INT64) BETWEEN (UINT64) AND (INT64); (INT64) BETWEEN (INT64) AND (UINT64); (ANY) BETWEEN (ANY) AND (ANY) [at 6:55], jobInternalError
Could you please suggest me proper query for it @goccy ?
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
Hi @goccy
Could you please help me out with this query solution?
Because I am also getting the same error as you getting.
googleapi: Error 400: INVALID_ARGUMENT: No matching signature for operator BETWEEN for argument types: STRING, STRING, DATE. Supported signatures: (INT64) BETWEEN (UINT64) AND (UINT64); (UINT64) BETWEEN (INT64) AND (UINT64); (UINT64) BETWEEN (UINT64) AND (INT64); (UINT64) BETWEEN (INT64) AND (INT64); (INT64) BETWEEN (UINT64) AND (INT64); (INT64) BETWEEN (INT64) AND (UINT64); (ANY) BETWEEN (ANY) AND (ANY) [at 6:47], jobInternalError
from bigquery-emulator.
goccy
commented on May 16, 2024
After correcting the casting process for PARSE_DATETIME argument and created which is the argument of BETWEEN, the error is gone, but I found that the converted query fails due to a bug in go-zetasqlite.
I'm currently working on fixing that.
query := `
SELECT yearMonth, count, key
FROM (
SELECT key, FORMAT_DATE('%Y-%m', PARSE_DATETIME('%Y-%m-%d %H:%M:%S', created)) AS yearMonth, count(distinct installid) as count
FROM test.dataset.table
WHERE DATE(created) BETWEEN DATE '2022-09-14' AND DATE_ADD(DATE '2022-10-14', INTERVAL 1 DAY)
GROUP BY key, yearMonth
ORDER BY yearMonth
) AS counts
WHERE key IN ("1bjCljWkq6tinyAMuu0tEAhB80","1bjCljWkq6tinyAMuu0tEAhB82","1bjCljWkq6tinyAMuu0tEAhB86") ORDER BY key
`
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
Hey @goccy
Yes Thank you so much for your response
After writing your query
Currently, I get this below error like
googleapi: Error 400: failed to parse statements: failed to parse statement: INVALID_ARGUMENT: Syntax error: Illegal input character "%" [type.googleapis.com/zetasql.ErrorLocation='\x08\x04\x10Y'], jobInternalError
from bigquery-emulator.
goccy
commented on May 16, 2024
@Vikas-MI Based on the error, I would guess that your code is most likely incorrect, so please paste the code you are verifying as is here.
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
After correcting the casting process for
PARSE_DATETIMEargument andcreatedwhich is the argument ofBETWEEN, the error is gone, but I found that the converted query fails due to a bug in go-zetasqlite. I'm currently working on fixing that.query := ` SELECT yearMonth, count, key FROM ( SELECT key, FORMAT_DATE('%Y-%m', PARSE_DATETIME('%Y-%m-%d %H:%M:%S', created)) AS yearMonth, count(distinct installid) as count FROM test.dataset.table WHERE DATE(created) BETWEEN DATE '2022-09-14' AND DATE_ADD(DATE '2022-10-14', INTERVAL 1 DAY) GROUP BY key, yearMonth ORDER BY yearMonth ) AS counts WHERE key IN ("1bjCljWkq6tinyAMuu0tEAhB80","1bjCljWkq6tinyAMuu0tEAhB82","1bjCljWkq6tinyAMuu0tEAhB86") ORDER BY key `
Hey
@goccy
Could you please send me the error which you are getting in this query so i can also verify this as per my error?
from bigquery-emulator.
goccy
commented on May 16, 2024
I confirmed this problem was fixed with #67 .
Test code is here. ( put to server directory )
package server_test
import (
"context"
"fmt"
"testing"
"time"
"cloud.google.com/go/bigquery"
"github.com/goccy/bigquery-emulator/server"
"github.com/goccy/bigquery-emulator/types"
"google.golang.org/api/iterator"
"google.golang.org/api/option"
)
type MockUsageEvent struct {
Key string `json:"key" binding:"required" conform:"trim"`
Product string `json:"product" conform:"trim"`
Version string `json:"version" conform:"trim"`
Platform string `json:"platform" conform:"trim"`
Event string `json:"event" binding:"required,oneofCI=activation" conform:"trim,lower"`
InstallId string `json:"installid" conform:"trim"`
Created string `json:"created"`
}
func (i *MockUsageEvent) Save() (map[string]bigquery.Value, string, error) {
return map[string]bigquery.Value{
"key": i.Key,
"product": i.Product,
"version": i.Version,
"platform": i.Platform,
"event": i.Event,
"installid": i.InstallId,
"created": i.Created,
}, bigquery.NoDedupeID, nil
}
func TestIssue59(t *testing.T) {
ctx := context.Background()
bqServer, err := server.New(server.TempStorage)
if err != nil {
t.Fatal(err)
}
if err := bqServer.Load(server.StructSource(types.NewProject("test"))); err != nil {
t.Fatal(err)
}
testServer := bqServer.TestServer()
defer func() {
testServer.Close()
bqServer.Close()
}()
client, err := bigquery.NewClient(
ctx,
"test",
option.WithEndpoint(testServer.URL),
option.WithoutAuthentication(),
)
if err != nil {
t.Fatal(err)
}
defer client.Close()
meta := &bigquery.DatasetMetadata{
Location: "US", // See https://cloud.google.com/bigquery/docs/locations
}
if err := client.Dataset("dataset").Create(ctx, meta); err != nil {
t.Fatal(err)
}
sampleSchema := bigquery.Schema{
{Name: "key", Type: bigquery.StringFieldType},
{Name: "product", Type: bigquery.StringFieldType},
{Name: "version", Type: bigquery.StringFieldType},
{Name: "platform", Type: bigquery.StringFieldType},
{Name: "event", Type: bigquery.StringFieldType},
{Name: "installid", Type: bigquery.StringFieldType},
{Name: "created", Type: bigquery.StringFieldType},
}
metaData := &bigquery.TableMetadata{
Schema: sampleSchema,
ExpirationTime: time.Now().Add(time.Duration(1*60) * time.Second), // Table will be automatically deleted in 1 day.
}
tableRef := client.Dataset("dataset").Table("table")
if err := tableRef.Create(ctx, metaData); err != nil {
t.Fatal(err)
}
inserter := client.Dataset("dataset").Table("table").Inserter()
items := []*MockUsageEvent{
{Key: "1bjCljWkq6tinyAMuu0tEAhB80", Product: "Phrased Flintstones0", Version: "Phrased Flintstones0", Platform: "Phrased Flintstones0", Event: "license-request", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB81", Product: "Phrased Flintstones1", Version: "Phrased Flintstones1", Platform: "Phrased Flintstones1", Event: "camera", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB82", Product: "Phrased Flintstones2", Version: "Phrased Flintstones2", Platform: "Phrased Flintstones2", Event: "activation", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB83", Product: "Phrased Flintstones3", Version: "Phrased Flintstones3", Platform: "Phrased Flintstones3", Event: "textsearch", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB84", Product: "Phrased Flintstones4", Version: "Phrased Flintstones4", Platform: "Phrased Flintstones4", Event: "barcode", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB85", Product: "Phrased Flintstones5", Version: "Phrased Flintstones5", Platform: "Phrased Flintstones5", Event: "ocr", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB86", Product: "Phrased Flintstones6", Version: "Phrased Flintstones6", Platform: "Phrased Flintstones6", Event: "fooddetection", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB87", Product: "Phrased Flintstones7", Version: "Phrased Flintstones7", Platform: "Phrased Flintstones7", Event: "classifications", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB88", Product: "Phrased Flintstones8", Version: "Phrased Flintstones8", Platform: "Phrased Flintstones8", Event: "customdetection", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB89", Product: "Phrased Flintstones9", Version: "Phrased Flintstones9", Platform: "Phrased Flintstones9", Event: "detectinimage", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB89", Product: "Phrased Flintstones9", Version: "Phrased Flintstones10", Platform: "Phrased Flintstones10", Event: "packagedfood", InstallId: "1", Created: "2022-10-11T17:31:39"},
}
if err := inserter.Put(ctx, items); err != nil {
t.Fatal(err)
}
query := `
SELECT yearMonth, count, key
FROM (
SELECT key, FORMAT_DATE('%Y-%m', PARSE_DATETIME('%Y-%m-%d %H:%M:%S', created)) AS yearMonth, count(distinct installid) as count
FROM test.dataset.table
WHERE DATE(created) BETWEEN DATE '2022-09-14' AND DATE_ADD(DATE '2022-10-14', INTERVAL 1 DAY)
GROUP BY key, yearMonth
ORDER BY yearMonth
) AS counts
WHERE key IN ("1bjCljWkq6tinyAMuu0tEAhB80","1bjCljWkq6tinyAMuu0tEAhB82","1bjCljWkq6tinyAMuu0tEAhB86") ORDER BY key
`
it, err := client.Query(query).Read(ctx)
if err != nil {
t.Fatal(err)
}
for {
var row []bigquery.Value
if err := it.Next(&row); err != nil {
if err == iterator.Done {
break
}
t.Fatal(err)
}
fmt.Println("row = ", row)
}
fmt.Println("TOTAL ROWS COUNT", it.TotalRows)
}go test -v ./server -run TestIssue59from bigquery-emulator.
goccy
commented on May 16, 2024
@Vikas-MI
I released v0.1.20 to fix this problem.
Could you please confirm the above test code ?
from bigquery-emulator.
goccy
commented on May 16, 2024
I will close this once. If you still have problems, let me know.
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
@goccy Thank you for your quick response
But after scanning the result I get this below error could you please look at this issue?
error => googleapi: Error 404: dataset anonymous is not found, notFound
LINE => err := rows.Next(&val)
from bigquery-emulator.
goccy
commented on May 16, 2024
I've never encountered that error, so I need the complete code to reproduce.
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
This is my code
type uses struct {
id *uuid.UUID `json:"id"`
Name string `json:"name"`
Lid *uuid.UUID `json:"lid"`
Key string `json:"key"`
YearMonth string `json:"year_month"`
Count int `json:"count"`
}
var rows *bigquery.RowIterator
rows, err := bigQueryClient.Query(query).Read(ctx)
fmt.Println("rows", rows)
var usesList []*model.uses
for {
uses := model.uses{}
err := rows.Next(&uses)
if err == iterator.Done {
break
}
if err != nil {
fmt.Println("err", err)
//================================================================
//HERE I AM GETTING googleapi: Error 404: dataset anonymous is not found, notFound
//================================================================
return nil, fmt.Errorf("error reading list Data. Err: %v", err)
}
usesList = append(usesList, &uses)
}
return usesList, nil
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
@goccy Could you please run this above code in your editor and could you please let me know if you are getting any errors or not?
Because I am getting googleapi: Error 404: dataset anonymous is not found, notFound ERROR
from bigquery-emulator.
goccy
commented on May 16, 2024
The code you provided is incomplete so I can't try it.
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
Hey @goccy let me share you full code.
from bigquery-emulator.
goccy
commented on May 16, 2024
The code you're providing is incomplete Go code. Please paste here the complete code that works just by pasting it into the editor. Otherwise I cannot investigate.
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
package main
import (
"context"
"fmt"
"github.com/gofrs/uuid"
"testing"
"time"
"cloud.google.com/go/bigquery"
"github.com/goccy/bigquery-emulator/server"
"github.com/goccy/bigquery-emulator/types"
"google.golang.org/api/iterator"
"google.golang.org/api/option"
)
type uses struct {
id *uuid.UUID `json:"id"`
Name string `json:"name"`
Lid *uuid.UUID `json:"lid"`
Key string `json:"key"`
YearMonth string `json:"year_month"`
Count int `json:"count"`
}
type MockUsageEvent struct {
Key string `json:"key" binding:"required" conform:"trim"`
Product string `json:"product" conform:"trim"`
Version string `json:"version" conform:"trim"`
Platform string `json:"platform" conform:"trim"`
Event string `json:"event" binding:"required,oneofCI=activation" conform:"trim,lower"`
InstallId string `json:"installid" conform:"trim"`
Created string `json:"created"`
}
func (i *MockUsageEvent) Save() (map[string]bigquery.Value, string, error) {
return map[string]bigquery.Value{
"key": i.Key,
"product": i.Product,
"version": i.Version,
"platform": i.Platform,
"event": i.Event,
"installid": i.InstallId,
"created": i.Created,
}, bigquery.NoDedupeID, nil
}
func TestIssue59(t *testing.T) {
ctx := context.Background()
bqServer, err := server.New(server.TempStorage)
if err != nil {
t.Fatal(err)
}
if err := bqServer.Load(server.StructSource(types.NewProject("test"))); err != nil {
t.Fatal(err)
}
testServer := bqServer.TestServer()
defer func() {
testServer.Close()
bqServer.Close()
}()
client, err := bigquery.NewClient(
ctx,
"test",
option.WithEndpoint(testServer.URL),
option.WithoutAuthentication(),
)
if err != nil {
t.Fatal(err)
}
defer client.Close()
meta := &bigquery.DatasetMetadata{
Location: "US", // See https://cloud.google.com/bigquery/docs/locations
}
if err := client.Dataset("dataset").Create(ctx, meta); err != nil {
t.Fatal(err)
}
sampleSchema := bigquery.Schema{
{Name: "key", Type: bigquery.StringFieldType},
{Name: "product", Type: bigquery.StringFieldType},
{Name: "version", Type: bigquery.StringFieldType},
{Name: "platform", Type: bigquery.StringFieldType},
{Name: "event", Type: bigquery.StringFieldType},
{Name: "installid", Type: bigquery.StringFieldType},
{Name: "created", Type: bigquery.StringFieldType},
}
metaData := &bigquery.TableMetadata{
Schema: sampleSchema,
ExpirationTime: time.Now().Add(time.Duration(1*60) * time.Second), // Table will be automatically deleted in 1 day.
}
tableRef := client.Dataset("dataset").Table("table")
if err := tableRef.Create(ctx, metaData); err != nil {
t.Fatal(err)
}
inserter := client.Dataset("dataset").Table("table").Inserter()
items := []*MockUsageEvent{
{Key: "1bjCljWkq6tinyAMuu0tEAhB80", Product: "Phrased Flintstones0", Version: "Phrased Flintstones0", Platform: "Phrased Flintstones0", Event: "license-request", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB81", Product: "Phrased Flintstones1", Version: "Phrased Flintstones1", Platform: "Phrased Flintstones1", Event: "camera", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB82", Product: "Phrased Flintstones2", Version: "Phrased Flintstones2", Platform: "Phrased Flintstones2", Event: "activation", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB83", Product: "Phrased Flintstones3", Version: "Phrased Flintstones3", Platform: "Phrased Flintstones3", Event: "textsearch", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB84", Product: "Phrased Flintstones4", Version: "Phrased Flintstones4", Platform: "Phrased Flintstones4", Event: "barcode", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB85", Product: "Phrased Flintstones5", Version: "Phrased Flintstones5", Platform: "Phrased Flintstones5", Event: "ocr", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB86", Product: "Phrased Flintstones6", Version: "Phrased Flintstones6", Platform: "Phrased Flintstones6", Event: "fooddetection", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB87", Product: "Phrased Flintstones7", Version: "Phrased Flintstones7", Platform: "Phrased Flintstones7", Event: "classifications", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB88", Product: "Phrased Flintstones8", Version: "Phrased Flintstones8", Platform: "Phrased Flintstones8", Event: "customdetection", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB89", Product: "Phrased Flintstones9", Version: "Phrased Flintstones9", Platform: "Phrased Flintstones9", Event: "detectinimage", InstallId: "1", Created: "2022-10-11T17:31:39"},
{Key: "1bjCljWkq6tinyAMuu0tEAhB89", Product: "Phrased Flintstones9", Version: "Phrased Flintstones10", Platform: "Phrased Flintstones10", Event: "packagedfood", InstallId: "1", Created: "2022-10-11T17:31:39"},
}
if err := inserter.Put(ctx, items); err != nil {
t.Fatal(err)
}
query := `
SELECT yearMonth, count, key
FROM (
SELECT key, FORMAT_DATE('%Y-%m', PARSE_DATETIME('%Y-%m-%d %H:%M:%S', created)) AS yearMonth, count(distinct installid) as count
FROM test.dataset.table
WHERE DATE(created) BETWEEN DATE '2022-09-14' AND DATE_ADD(DATE '2022-10-14', INTERVAL 1 DAY)
GROUP BY key, yearMonth
ORDER BY yearMonth
) AS counts
WHERE key IN ("1bjCljWkq6tinyAMuu0tEAhB80","1bjCljWkq6tinyAMuu0tEAhB82","1bjCljWkq6tinyAMuu0tEAhB86") ORDER BY key
`
var rows *bigquery.RowIterator
rows, err = client.Query(query).Read(ctx)
if err != nil {
t.Fatal(err)
}
//var rows *bigquery.RowIterator
//rows, err := bigQueryClient.Query(query).Read(ctx)
if err != nil {
return
}
fmt.Println("rows", rows.TotalRows)
usesss, err := scanTestWithoutSize(rows)
fmt.Println("usesss", usesss)
return
}
func scanTestWithoutSize(rows *bigquery.RowIterator) ([]*uses, error) {
fmt.Println("rows", rows)
var usesList []*uses
for {
uses := uses{}
err := rows.Next(&uses)
if err == iterator.Done {
break
}
if err != nil {
fmt.Println("err", err)
return nil, fmt.Errorf("error reading report Data. Err: %v", err)
}
usesList = append(usesList, &uses)
}
return usesList, nil
}
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
Thank you so much for your quick response
I have update the updated code here could you please check it again ? @goccy
from bigquery-emulator.
goccy
commented on May 16, 2024
Did you run this test code even once ? Go compiler can't compile this test code.
I'm sorry, but I will not investigate if you do not give me a code that I can compile next time, because the way you have been asking me to investigate in the past has not been good.
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
Okay @goccy sure.
could you please check the updated code ?
from bigquery-emulator.
goccy
commented on May 16, 2024
The your pasted code is invalid at this time. Support will be finished.
#59 (comment)
from bigquery-emulator.
Vikas-MI
commented on May 16, 2024
The your pasted code is invalid at this time. Support will be finished. #59 (comment)
@goccy
I am setting up the code that's why it will take some time to format the code.
from bigquery-emulator.
Related Issues (20)
- Replace SQLite with DuckDB
- Unmarshal fails to a struct with time.Time type when using the cloud.google.com/go/bigquery library HOT 5
- Queries on TIMESTAMP columns are not handled correctly when no timezone is specified HOT 1
- failed to add query result to dynamic destination table (on a simple query) HOT 4
- How to install and run this project locally on my computer? HOT 6
- JSON_EXTRACT_SCALAR nor JSON_QUERY does not work as expected HOT 1
- Connection error when creating a dataset HOT 8
- Does the emulator work with Pandas GBQ? HOT 3
- Error when executing bq command from google-cloud-cli version 469.0.0
- Response from table update is incompatible with Google's Python SDK HOT 1
- Improve emulator performance for large projects HOT 7
- Failes to exec merge statement UPDATE
- Cannot create a view with a query that ends in semicolon
- Cannot materialize job results when table does not yet exist
- [Golang] Errors from zetasql parser HOT 3
- The BigQuery emulator doesn't support CREATE TABLE AS SELECT with column definition list HOT 2
- Error querying a nested struct using UNNEST HOT 1
- Support of UNPIVOT operator HOT 3
- `UPDATE ... FROM ( SELECT ... )` results in the error: `Update with joins not supported`
- Unable to use UploadCsvAsync HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from bigquery-emulator.