-
-
Notifications
You must be signed in to change notification settings - Fork 86
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Bigquery Query Like ORDER BY GROUP BY FORMAT_DATE count and subquery not working on bigquery emulator. #59
Comments
I wrote a test code and verified it, and the result is different from what you reported.
First, the result of executing query2 in the above test code is as follows. this seems to work as expected.
Then I ran
|
Yes @goccy 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. AS PER YOUR FIRST QUERY, IT WILL WORK FINE FOR ME ALSO Thank you @goccy FOR YOUR QUICK RESPONSE CAN YOU PLEASE GUIDE ME FOR THE SAME ? |
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 " |
Hi @goccy Yes, I get exactly the same error as you get. 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 ? |
Hi @goccy 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 |
After correcting the casting process for
|
Hey @goccy
|
@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. |
Hey |
I confirmed this problem was fixed with #67 . 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 TestIssue59 |
I will close this once. If you still have problems, let me know. |
@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) |
I've never encountered that error, so I need the complete code to reproduce. |
This is my code
|
@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? |
The code you provided is incomplete so I can't try it. |
Hey @goccy let me share you full code. |
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. |
|
Thank you so much for your quick response |
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. |
Okay @goccy sure. |
The your pasted code is invalid at this time. Support will be finished. |
|
Thank you so much @goccy |
Hey @goccy
I have written one query regarding BIG QUERY Like the below for inserting data and fetching data
Whenever insert data it will work fine but when I tried to fetch data using group by or count or order by or using subquery it will not give me a proper record and it will give me error.
This is below code I tried.
While I try to fetch the record using the below query it is not working
HERE THIS QUERY WILL NOT WORKING
It will give me the error panic: runtime error: invalid memory address or nil pointer dereference [recovered]
Whenever I run this SIMPLE query it will give me 11 records and its works fine but whenever I run the above query it would not work yet.
// OUTPUT : TOTAL ROWS COUNT 11
Please suggest me proper solution for the above query
Thank you @goccy
The text was updated successfully, but these errors were encountered: